Oracle資料庫小知識點整理-有解無憂

-- 資料庫存盤資料

-- 市面上主流的資料庫有哪些

-- 甲骨文 oracle mysql

-- IBM db2 金融

-- 微軟 sqlserver

--這些是關系型資料庫，

--NOSQL 不僅僅是sql,典型 mongodb.

----------------- ----------------------------------------

--資料庫的語言有哪些分類

-- **************************************

/***

1.資料定義語言(DDL),create,alter,drop

2.資料操縱語言(DML),select,update,insert,delete

3.資料事務語言, commit,savepoint,rollback

事務特性:原子性，一致性，持久性，隔離型，

...........后面補充.............

在關系型資料庫中資料是以table

(二維資料結構，行和列的形式)

來組織資料

table 就是表來組織資料，這個table的設計的規范是什么?

--三范式 *****************

--第一范式:資料庫表中的欄位應該最小單位，

是不能夠分割(本地化)

--第二范式:資料庫表中的一個欄位

不能由另外一個欄位通過計算得出，

age

birthday

工資

養老保險費用

***/

-------------------------------------------------------

-- 創建表 oracle 列名和列的型別來定義的

--1.資料型別：

--number 數值型別 number(長度),number(10)

--number(長度，小數點位數) number(7,2)

--2.char型別 char(長度)，定長 char(6) 固定長度為6位

--3.varchar2型別 varchar2(20)

--4.date型別日期型別

--5.int型別，表示一個正整數

-- 大文本型別 blob ,clob

-- 路徑

-- 首先我們來創建一張表命名規范 t_

create table t_students

(

sn number(7) primary key ,--學號 -- 主鍵（唯一不能重復），主鍵約束

sname varchar2(20) , --學生姓名

spwd char(6), -- 學生密碼

sbirthday date,--出生年月

smoney number(7,2), --賬戶金額

job varchar2(20), --學生職位

saddress varchar2(20),--學生籍貫

sphone char(11) --學生手機號碼

)

--查詢表

select * from t_students

-- 插入資料

insert into t_students values(1, '趙蓉','12345',

to_date('1992-12-03','yyyy-MM-dd'),500.65,'學生會委員',

'鹽城','13913321089');

commit;

insert into t_students values(2, '秦日霞','123456',

to_date('1992-11-03','yyyy-MM-dd'),1500.65,'學生會委員',

'鹽城','13913321085');

commit;

-- 修改表

-- 表增加一個欄位

alter table t_students add ssex char(4);

--表欄位型別修改

alter table t_students modify saddress varchar2(15)

--洗掉表的欄位

alter table t_students drop column ssex ;

-- 洗掉整個表

drop table t_students;

select length(s.spwd)

from t_students s where s.sname='趙蓉'

-- 創建表

create table t_employees

(

eid number(6) primary key, -- 員工編號主鍵

ename varchar2(20), --員工姓名

epwd char(5) , -- 員工密碼

ebirthday date, --出生年月

esalary number(7,2), --工資

ejob varchar2(20), --職位

esex char(4) --性別

)

-- oracle序列機制，自增長機制 sequence

create sequence seq_employees

start with 100 --開始編號

increment by 2 --步長

nocache --沒有快取，立即生成

--查看表結構

desc t_employees

select * from t_employees for update

select * from t_employees where

--資料操作語言（select ,insert,update,delete）DML

--select 操作 ,不需要commit 70%

--insert,update,delete 都需要commit 30%

--查詢

select * from t_employees

--插入

-- 插入資料

insert into t_employees values(seq_employees.nextval,

'胡瓜','1234',to_date('1992-12-30','yyyy-MM-dd'),10000.88,

'職工','男');

insert into t_employees values(seq_employees.nextval,

'李欣','11111',to_date('1992/08/30','yyyy/MM/dd'),12000.88,'組長','男');

commit;

--更新資料

update t_employees e set e.epwd='12345' ;

commit;

update t_employees e set e.epwd='00000' where e.ename='李欣';

commit;

--洗掉資料

delete from t_employees

delete from t_employees e where e.eid=100;

commit;

--約束

--1.主鍵約束，唯一不能重復，資料唯一性 primary key

--2.非空約束這個欄位的取值不能為空 not null

--3.默認約束這個欄位取值如果不給值，取默認值 default

--4.檢查約束這個欄位取值是一定范圍內 check(欄位取值范圍)

--5.外鍵約束一個欄位取值從屬于另外一個的值

drop table t_stus

create table t_stus

(

sid number(3) primary key,

sname varchar2(20) not null

)

insert into t_stus values(seq_stus.nextval,null);

commit;

insert into t_stus values(seq_stus.nextval,'李欣',to_date('1976-12-30','yyyy-MM-dd'));

commit;

insert into t_stus values(seq_stus.nextval,'李欣1',default);

commit;

insert into t_stus values(seq_stus.nextval,'李欣1',default,'男');

commit;

select * from t_stus

--default 是默認，sysdate是oracle默認時間的關鍵字

alter table t_stus add birthday date default sysdate;

alter table t_stus add sex char(4)

check(sex in('男','女'));

-- 創建一張表，樹狀選單，自身關聯表一對多

create table t_menu

(

fid number(4) primary key, --父id

fmenuname varchar2(20) not null, --父選單名稱

cmenuuname varchar2(20) not null,-- 子選單名稱

cid number(4) references t_menu(fid) --子id 外鍵約束

)

select * from t_menu

-- oracle序列機制自增長機制

create sequence seq_stus

start with 1

increment by 1

nocache;

--查詢序列的當前值 dual是個系統表

select seq_stus.currval from dual;

drop sequence seq_stus

insert into t_stus values(seq_stus.nextval,'李欣');

commit;

select * from t_stus

delete from t_stus

--mysql自增長 auto_increment 從起始編號為1，每次增加1.

--sqlserver自增長 identity(10,1)

-- oracle內置函式

--to_date(引數1，引數2)，把一個字串型別的日期轉換成date型別，

--引數2，按照什么日期格式

--length() 獲取結果的長度

--to_char()轉換成字串型別

select to_char(sysdate,'yyyy')

-to_char(e.ebirthday,'yyyy') age

from t_employees e

-- 拼接字串concat()

-- 輸出一段描述"胡瓜的職位是什么"

select concat(concat(e.ename,'職位是:'),e.ejob) 描述

from t_employees e where e.ename='胡瓜';

-- ||連接

select e.ename||',職位是'||e.ejob from t_employees e

--nvl()函式,取值為null,給默認值

select * from t_employees e for update

--增加獎金欄位

alter table t_employees add ecomm number(5,2)

-- 算出每個員工的收入

select e.ename,e.esalary+e.ecomm from t_employees e

select e.ename,e.esalary+nvl(e.ecomm,0) from t_employees e

--substr()截取

alter table t_employees add ephone char(11)

--號段

select substr(e.ephone,0,3) from

t_employees e where e.ename='胡瓜'

--to_number()轉換成數值的函式

--聚合函式 count(),max(),min(),sum(),avg()

select count(*) from t_employees

select max(e.esalary) from t_employees e

select min(e.esalary) from t_employees e

select avg(e.esalary) from t_employees e

--事務？？？？？

--手動事務，自動事務，可以設定

-- 3查詢技術 70%

-- 3.1 條件查詢 where關鍵字

-- 查詢姓名是李欣的所有資訊

select * from t_employees e where e.ename='李欣'

--查詢姓名是李欣，密碼是12345的這個用戶是不是合法用戶

select count(*) from t_employees e where e.ename='李欣'

and e.epwd='1111'

select * from t_employees e where e.esex='男'

or e.ename='李欣'

--3.2 比較查詢 >,<,>=,<=,!= <>

select * from t_employees e where e.esalary!=5000

select * from t_employees e where e.esalary<>5000

--3.3 模糊查詢

--查詢姓李的人員的資訊

select * from t_employees e where e.ename like '李%'

--查詢姓李的人員的，但是是兩個字的人員資訊

select * from t_employees e where e.ename like '李__'

--3.4 排序

--asc desc

select * from t_employees e order by e.esalary asc

select * from t_employees e order by e.esalary desc

--3.5 分組

--group by 統計就是和聚合函式在一起使用，

--統計這個單位的男女性別數量分組條件是明確

select e.esex,count(e.esex)

from t_employees e group by e.esex

select * from t_employees for update

-- 統計每個年齡員工的數量 select count(*), 分組條件是計算的

select count(*),

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age

from t_employees e group by

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')

--統計每個職位的數量

select e.ejob,count(*)

from t_employees e group by e.ejob

--統計學歷，統計籍貫

--統計每個年齡數量大于1 分組后帶有限制條件

select count(*),

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age

from t_employees e group by

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')

having count(*)>1

--統計每個年齡數量,數量按升序(先分組，后排序)分組帶排序

select count(*),

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age

from t_employees e group by

to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy')

order by count(*)

-- 子查詢一個查詢的結果作為另外一個查詢的條件

-- 找出工資最高的人的姓名

select * from t_employees

select * from t_employees e where e.esalary=

(select max(e.esalary) from t_employees e)

--3.6 in ,not in ,between and ...any all關鍵字

-- =只能等于一個值

select * from t_employees e where e.esalary=10000.88

-- in可以是多個值

select * from t_employees e where e.esalary

in(10000.88,5000.00)

select * from t_employees e where e.esalary

not in(10000.88,5000.00)

select * from t_employees e where e.esalary between 4000

and 20000;

select * from t_employees e where e.esalary >=4000

and e.esalary <=20000

--any >是比最小的大的資訊，<是比最大的小資訊

select e.esalary,e.ename from t_employees e where e.esalary<any

(select e.esalary from t_employees e where e.ejob='職工')

--all >是比最大的大的資訊，<是比最小的小資訊

select e.esalary,e.ename from t_employees e where e.esalary < all

(select e.esalary from t_employees e where e.ejob='職工')

select * from t_employees for update

-- 查詢1991年到1999年出生的員工的資訊

select * from t_employees

select * from t_employees e

where to_number(to_char(e.ebirthday,'yyyy'))

between 1991 and 1999

--****

insert into t_stus values(seq_stus.nextval,

'李二',default,'男')

select * from t_employees for update

--- oracle高級查詢技術

-- 1.子查詢

-- 邏輯，就是從一個條件出發去找關聯的條件，

-- 一個查詢的結果作為另一個查詢的條件

-- 找出員工工資最高人的資訊

select * from t_employees e where e.esalary in

(select max(e.esalary) from t_employees e)

--找出和李欣職位不同的員工的資訊

select * from t_employees e where e.ejob!=

(select e.ejob from t_employees e where e.ename='李欣')

--找出比員工平均工資高的員工的資訊

select * from t_employees e where e.esalary>

(select avg(e.esalary) from t_employees e)

--找出收入最高的員工的資訊

select * from t_employees e where e.esalary+nvl(e.ecomm,0)=

(select max(e.esalary+nvl(e.ecomm,0)) from t_employees e )

--oracle分頁查詢局限性，一條sql陳述句對應一張表，造成SQL冗余

--oracle特殊的列，偽列 rownum ,它始終在第一行，不能移動

select rownum, e.* from t_employees e

--查詢前三條的資料

select rownum, e.* from t_employees e where rownum<=3

select rownum, e.* from t_employees e where rownum<=2

-- 查詢第2條到第4條之間的資料，查詢出是空的

select rownum, e.* from t_employees e where rownum>=2

and rownum<=4;

select rownum, e.* from t_employees e where rownum

between 2 and 4

--rownum始終在第一行，導致你查詢出來的是空的，

-- 查詢第2條到第4條之間的資料

--通過臨時結果集過渡查詢

select * from

(select rownum rm,e.* from t_employees e where rownum<=4) tmp

where tmp.rm>=2

select rownum, e.* from t_employees e

--分頁查詢

-- 最大的編號:<=第幾頁*每頁條數

--起始編號:>（第幾頁-1）*每頁條數

select * from t_employees e

--每頁的條數是2條

--第一頁資料第一頁，2條 out:結果集

select * from

(select rownum rm,e.* from t_employees e where rownum<=1*2)

tmp where tmp.rm>(1-1)*2

--第二頁資料

select * from

(select rownum rm,e.* from t_employees e where rownum<=2*2)

tmp where tmp.rm>(2-1)*2

--第三頁資料

select * from

(select rownum rm,e.* from t_employees e where rownum<=3*2)

tmp where tmp.rm>(3-1)*2

-- 在實際的專案中的一個

--局限性，一條sql陳述句對應一張表，造成SQL冗余，

--比如;100張表需要100個sql嗎?

--2.集合查詢

--集合查詢

-- 多個結果集的查詢

-- 工資大于3000的和職位是組長的兩個查詢結果的合并，過濾重復

-- 合并不過濾重復，查詢的兩個結果合并

select e.ename,e.esalary from

t_employees e where e.ejob='組長'

union all

select e.ename,e.esalary from t_employees e

where e.esalary>3000

--合并過濾重復

select e.ename,e.esalary from

t_employees e where e.ejob='組長'

union

select e.ename,e.esalary from t_employees e

where e.esalary>3000

--交集，兩個查詢結果集都有的

select e.ename,e.esalary from

t_employees e where e.ejob='組長'

intersect

select e.ename,e.esalary from t_employees e

where e.esalary>3000

--差集

select e.ename,e.esalary from

t_employees e where e.ejob='組長'

minus

select e.ename,e.esalary from t_employees e

where e.esalary>3000

--3.decode()函式查詢分支查詢

select * from

t_employees

--科長工資加1.2倍，組長工資加1.1倍,其它職位保持原有，

--請輸出加薪后的所有的員工的

--資訊

select e.ename,e.ejob,e.esalary,

decode(e.ejob,'科長',e.esalary*1.2,

'組長',e.esalary*1.1,

esalary) 加薪后的工資

from t_employees e

-- 等同于case..when

select e.ename,e.ejob,e.esalary,

case e.ejob when '科長' then e.esalary*1.2

when '組長' then e.esalary*1.1

else e.esalary end

加薪后的工資

from t_employees e

--4.多表查詢(2表查詢，第三范式)

--之前講的都是單表查詢，進入到多表(>1)查詢

--第三范式

-- 表中的列只能參照一個主鍵欄位

-- 員工表員工id，員工姓名，部門名稱，部門id, 拆分形成兩個物體表

--物體表之間的關系，主要有三種:

--1.一對一關系員工表和角色表

--2.一對多關系部門表和員工表商品分類表和商品表

--3.多對多關系學生和課程表（中間關系表，第三方表，成績表）

--物體表之間的關系圖，ER圖

--這個關系的定義是根據現實的業務來決定的，

select * from t_employees

--建立一個部門表部門和員工表之間的關系是1對多

create table t_depts

(

did int primary key,

dname varchar2(20) not null,

daddress varchar2(50),

dphone char(11) not null

)

create sequence seq_depts

start with 10

increment by 2

nocache;

insert into t_depts

values(seq_depts.nextval,'質量部','南京江北新區','13913321089');

insert into t_depts

values(seq_depts.nextval,'技術部','南京江寧區','13913321086');

insert into t_depts

values(seq_depts.nextval,'人力資源部','南京鼓樓區','13913321085');

commit;

select * from t_depts for update

select * from t_employees for update

-- 員工和部門之間是有關系的

alter table t_employees add deptid int;

--外鍵約束？？？？？？？？？？？？

alter table t_employees add constraints fk_emp_depts

-- 表的設計，軟體核心來自于資料

-- 查詢這兩張表的資料笛卡爾積表1*表2

select * from t_employees,t_depts

--等值連接

select * from t_employees e,t_depts d

where e.deptid=d.did

--內連接查詢inner join

select * from t_employees e inner join t_depts d

on e.deptid=d.did

-- 查詢員工李欣的部門名稱

select d.dname,e.ename from t_employees e inner join

t_depts d

on e.deptid=d.did where e.ename='李欣'

--子查詢的效率比內連接查詢要低

select d.dname from t_depts d where d.did=

(select e.deptid from t_employees e where e.ename='李欣')

--左連接

select d.dname,e.ename from t_employees e left join

t_depts d

on e.deptid=d.did

--右連接

select d.dname,e.ename from t_employees e right join

t_depts d

on e.deptid=d.did

--全連接

select d.dname,e.ename from t_employees e full join

t_depts d

on e.deptid=d.did

select * from t_depts

-- 統計技術部人員的工資的總和

select sum(e.esalary) 工資總和 from t_depts d inner join t_employees e on d.did

=e.deptid where d.dname='技術部'

-- 統計每個部門的人員的數量

select count(e.ename) 數量, d.dname from t_depts d

left join t_employees e on d.did=e.deptid

group by d.dname

=e.deptid

--統計每個部門的人員的數量大于1的資訊 group by 限制條件 having

select count(e.ename) 數量, d.dname from t_depts d

left join t_employees e on d.did=e.deptid

group by d.dname

having count(e.ename)>1

--統計每個部門的人員的數量按降序排列

select count(e.ename) 數量, d.dname from t_depts d

left join t_employees e on d.did=e.deptid

group by d.dname order by count(e.ename) desc

------------------------------------------------------------------

---pl-sql塊

--塊:一組SQL陳述句在一起運行，解決復雜的業務邏輯，

-- 是不能夠被編程語言所呼叫 java,python,c#

--塊的基本結構

/**

declare

---定義的變數

begin

-- 一組sql陳述句

end;

**/

/**

變數v_

變數賦值:=

**/

-- 計算兩個數值型別的變數的和并輸出

declare

v_num1 number:=100;

v_num2 number:=10;

v_sum number;

begin

v_sum:=v_num1+v_num2;

dbms_output.put_line('計算這兩個數的和為'||v_sum);

end;

-- 例外處理計算兩個數值型別的變數的商并輸出

declare

v_num1 number:=100;

v_num2 number:=0;

v_sum number;

begin

v_sum:=v_num1/v_num2;

dbms_output.put_line('計算這兩個數的和為'||v_sum);

--例外處理塊

exception --捕獲例外

when others then

dbms_output.put_line('v_num2這個數作為除數不能為零');

end;

-- 流程控制陳述句

-- 一個數判斷是奇數還是偶數,并輸出資訊

declare

v_num number:=22;

begin

-- oracle不支持%取模，取模函式mod()

if mod(v_num,2)=0 then

dbms_output.put_line('v_num這個數是偶數');

else

dbms_output.put_line('v_num這個數是奇數');

end if;

end;

--優化

declare

v_num number:=22;

v_str varchar2(100);

begin

-- oracle不支持%取模，取模函式mod()

if mod(v_num,2)=0 then

v_str:='v_num這個數是偶數';

else

v_str:='v_num這個數是奇數';

end if;

dbms_output.put_line(v_str);

end;

-- 查詢李欣這個員工的性別，如果是男的，獎金加1000，

--如果是女的，獎金加500，

-- 輸出他現在的收入輸出

/**

1.賦值:= 直接給變數賦值

2.從sql陳述句查詢的結果進行賦值，2.1查詢出來的是一個值，into

2.2 如果是多個值，不能用into，要用游標遍歷

**/

declare

v_esex t_employees.esex%type; --這個變數的型別參照表中的欄位型別

v_usaraly t_employees.esalary%type;

v_sum number(10,2);

begin

--1.查詢李欣這個員工的性別

select e.esex into v_esex

from t_employees e where e.ename='李欣';

--2.判斷是男還是女

if v_esex='男' then

v_usaraly:=1000;

else

v_usaraly:=500;

end if;

--執行更新

update t_employees e set e.esalary=e.esalary+v_usaraly

where e.ename='李欣';

commit;

-- 查詢出現在的收入

select e.esalary+nvl(e.ecomm,0) into v_sum from t_employees e where e.ename='李欣';

dbms_output.put_line('李欣這個員工的性別是:'||v_esex||',他現在的收入為:'||v_sum);

--select e.ename from t_depts d inner join t_employees e

-- on d.did=e.deptid where d.dname='質量部'

end;

-- 查詢李欣的部門名稱,如果是質量部，加1000，如果是技術部加2000，

--如果是人力資源部加500，其它部門加100

declare

v_dname t_depts.dname%type;

v_ecomm t_employees.ecomm%type;

begin

-- 李欣的部門名稱

select d.dname into v_dname from t_employees e inner join t_depts d

on e.deptid=d.did where e.ename='李欣';

if v_dname='質量部' then

v_ecomm:=500;

elsif v_dname='技術部' then

v_ecomm:=600;

elsif v_dname='人力資源部' then

v_ecomm:=200;

else

v_ecomm:=100;

end if;

update t_employees e set e.ecomm=e.ecomm+v_ecomm

where e.ename='李欣';

commit;

end;

-- 回圈結構

--1.100之和并判斷是奇數還是偶數

-- for loop

declare

v_sum number:=0;

begin

for v_i in 0..100 loop

v_sum:=v_sum+v_i;

end loop;

dbms_output.put_line('和為'||v_sum);

end;

--while loop

declare

v_i number:=0;

v_sum number:=0;

begin

while v_i<=100 loop

v_sum:=v_sum+v_i;

v_i:=v_i+2; --改變回圈變數的值

end loop;

dbms_output.put_line('和為'||v_sum);

end;

--回圈結構使用

create table t_users

(

id int primary key,

tname varchar2(20),

tsex char(6)

)

create sequence seq_users

start with 1

increment by 1

nocache;

select * from t_users

--for回圈批量資料

declare

begin

for v_i in 1..10000 loop

if mod(v_i,2)=0 then

insert into t_users values(seq_users.nextval,'李'||v_i,'男');

else

insert into t_users values(seq_users.nextval,'王'||v_i,'女');

end if;

end loop;

commit;

end;

--while回圈批量資料

declare

v_i number:=1;

begin

while v_i<=1000 loop

insert into t_users values(seq_users.nextval,'李'||v_i,'男');

v_i:=v_i+1;

end loop;

commit;

end;

---into是只能附一個值

-- 如果是多個值，就是游標，就是結果集，分為;cursor(顯示游標),隱式游標

-- 查詢員工表的中的資料，并輸出每個人的姓名和性別

-- while..loop

declare

--定義一個顯示游標

cursor v_datas is select * from t_employees;

-- 定義一行

v_linedatas t_employees%rowtype;

begin

--打開這個游標

open v_datas;

--遍歷游標

fetch v_datas into v_linedatas; --遍歷第一行

while v_datas%found loop -- 如果有資料進入回圈體

dbms_output.put_line(v_linedatas.ename||',性別是:'||v_linedatas.esex);

fetch v_datas into v_linedatas;--移動到下一行

end loop;

-- 關閉游標

close v_datas;

end;

-- for..loop

declare

--定義一個顯示游標

cursor v_datas is select * from t_employees;

begin

for v_linedatas in v_datas loop

dbms_output.put_line(v_linedatas.ename||',性別是:'||v_linedatas.esex);

end loop;

-- 關閉游標

end;

-- 隱式游標

-- 查詢質量部員工的性別，是男加1000，是女加5000

declare

v_sex t_employees.esex%type;

v_comm t_employees.ecomm%type;

begin

--質量部的員工的資訊

for v_datas in ( select e.esex ,e.ename from t_employees e right join t_depts d

on e.deptid=d.did where d.dname='質量部') loop

dbms_output.put_line(v_datas.esex||v_datas.ename);

if v_datas.esex='男' then

v_comm:=200;

else

v_comm:=50;

end if;

--執行更新

update t_employees e set e.ecomm=e.ecomm+v_comm

where e.ename=v_datas.ename;

commit;

end loop;

end;

select * from t_employees

---存盤程序

-- 存盤(是以一個名字來存盤)+程序(程序化的陳述句塊)

-- pl-sql塊是不能夠被編程語言直接呼叫,只能運行在資料庫端

-- 以一個名字命名，這個名字被編程語言call，這樣形成互動

--預編譯，編譯一次，下次呼叫的話不需要再次編譯，性能好，

--能夠處理復雜的業務邏輯,

--可以有傳入和輸出引數,缺點:占用存盤空間

--sql(dml語言，呼叫一次編譯一次)，性能沒有存盤程序好

--基本結構

create or replace procedure 存盤程序的名字

(

--傳入和輸出引數

)

begin

end;

---檢查登錄

create or replace procedure p_checklogin

(

v_uname in varchar2, --傳入引數不需要長度

v_pwd in varchar2,

v_msg out varchar2

)

v_count int;

begin

--檢查登錄

select count(*) into v_count from t_employees e where e.ename=v_uname

and trim(e.epwd)=v_pwd;

--進行判斷

if v_count>0 then

v_msg:='登錄成功';

else

v_msg:='登錄失敗';

end if;

--記錄

insert into t_userlog values(seq_userlog.nextval,v_uname,default,v_msg);

commit;

end;

-- 變更存盤程序

create table t_userlog

(

ulid int primary key,

uname varchar2(20), -- 登錄的人

logintime date default sysdate, --登錄的時間

loginresult varchar2(20) --登錄的結果

)

create sequence seq_userlog

start with 1

increment by 1

nocache;

select * from t_userlog

-- 直接回傳一個結果集(不在存盤程序內部遍歷)

--**部門的員工的數量和員工的姓名

create or replace procedure p_queryempDatas

(

v_dname in varchar2, --部門名稱

v_count out int, --部門員工數量

v_namedatas out sys_refcursor --部門員工姓名

)

begin

--1.通過部門名稱得到部門的員工的數量

select count(e.ename) into v_count from t_employees e

right join t_depts d

on e.deptid=d.did where d.dname=v_dname;

--2.員工姓名(不在存盤程序內部遍歷)

open v_namedatas for select e.ename from t_employees e

right join t_depts d

on e.deptid=d.did where d.dname=v_dname;

end;

----**部門的員工的數量和屬于這個部門的員工的性別，

--如果是男，工資加250；

--女加100；

create or replace procedure p_querydeptupdatesalary

(

v_dname in varchar2,

v_count out int

)

v_salary t_employees.esalary%type;

begin

--1.通過部門名稱得到部門的員工的數量

--select count(e.ename) into v_count from t_employees e

--right join t_depts d

--on e.deptid=d.did where d.dname=v_dname;

--呼叫函式，減少冗余代碼

v_count:=f_querydeptempnum(v_dname);

--2.這個部門的所有員工的資訊

for v_linedatas in (select * from t_employees e

right join t_depts d

on e.deptid=d.did where d.dname=v_dname) loop

if v_linedatas.esex='男' then

v_salary:=250;

else

v_salary:=150;

end if;

--執行更新

update t_employees e set e.esalary=e.esalary+v_salary

where e.ename=v_linedatas.ename;

commit;

end loop;

end;

select * from t_employees

-- 自定義函式特殊的存盤程序

-- 自定義函式 1.關鍵字function 2.只能回傳一個值

create or replace function f_querydeptempnum

(

v_dname in varchar2

)

return int --切記這個地方不能加;

v_count int;

begin

select count(e.ename) into v_count from t_employees e

right join t_depts d on e.deptid=d.did

where d.dname=v_dname;

return v_count;

end;

-- 查詢一個表t_employees的條數如果系統中的表很多，這樣會造成冗余

create or replace function f_queryempcount

return int

v_count int;

begin

select count(*) into v_count from t_employees ;

return v_count;

end;

--動態sql

-- 一個值

create or replace function f_querytablecount

(

v_tableName in varchar2

)

return int

v_sql varchar2(1000);

v_count int;

begin

v_sql:='select count(*) from ' ||v_tableName;

-- 執行這個動態sql,是一個值

execute immediate v_sql into v_count;

return v_count;

end;

---多個值

create or replace function f_querytabledatas

(

v_tableName in varchar2

)

return sys_refcursor

v_sql varchar2(1000);

v_datas sys_refcursor;

begin

v_sql:='select * from ' ||v_tableName;

-- 執行這個動態sql,是一個結果集

open v_datas for v_sql;

return v_datas;

end;

--sql陳述句分頁固定2條

select * from t_employees

-- sql分頁，系統大了，會造成SQL冗余

select * from (select rownum rm,

e.* from t_employees e where rownum<=4) tmp

where tmp.rm>2

--存盤程序分頁一個存盤程序可以對系統中的表都可以分頁

-- tablename in 表名

--pagenum in 每頁幾條

--currentnum in 第幾頁

--datas out 每頁的結果集

--count out 總條數

--pagesize out 總頁數

create or replace procedure p_pagemodel

(

v_tablename in varchar2,

v_pagenum in int,

v_currentnum in int,

v_datas out sys_refcursor,

v_count out int,

v_pagesize out int

)

v_endindex int:= v_currentnum*v_pagenum;

v_startindex int:=(v_currentnum-1)*v_pagenum;

v_sql varchar2(1000);

begin

--1.構建總的條數動態sql

v_sql:='select count(*) from '||v_tablename;

--2.執行動態sql

execute immediate v_sql into v_count;

--3.獲取總頁數

if mod(v_count,v_pagenum)=0 then

v_pagesize:=v_count/v_pagenum;

else

--除不盡

v_pagesize:=floor(v_count/v_pagenum)+1;

end if;

--獲取分頁結果集

v_sql:='select * from '||'(select rownum rm, e.* from '||v_tablename||

' e where rownum<='||v_endindex||') tmp' ||' where tmp.rm>'||v_startindex;

dbms_output.put_line(v_sql);

--執行動態sql，回傳的是一個結果集

open v_datas for v_sql;

end;

-- 面試的時候，什么是事務

--jdbc 事務自動事務

--hiernate事務

--mybatis事務

--spring事務

--python事務手動事務

-- rollback回滾事務 commit提交事務 savepoint 設定事務保存點

--資料庫事務

--事務就是一個作業單元，所謂的作業單元，就是不可分割的一個或多個SQL

-- (insert ,update,delete)

--秦日霞借錢1000給李欣，并記錄日志，

select * from t_employees

--秦日霞借錢1000給李欣

--記錄日志

create or replace procedure p_operatorsalary

(

v_rname in varchar2,

v_tname in varchar2,

v_money in number,

v_msg out varchar2

)

begin

-- 1.先把借錢人的錢扣掉

update t_employees e set

e.esalary=e.esalary-v_money where e.ename=v_rname;

--2.加上給借錢人

update t_employees e set

e.esalary=e.esalary+v_money where e.ename=v_tname;

--設定一個事務保存點給個名字

savepoint a;

--3.記錄日志

insert into t_userlog

values(seq_userlog.nextval,v_tname,'1997-12-30','借錢');

commit;

exception

when others then

rollback to a; --回滾到事務保存點a，提交

commit;

end;

select * from t_userlog

-------------

--索引，job(定時任務)，視圖，觸發器

-- 索引

-- 怎么來提高查詢的性能?

-- 1.索引 2. SQL優化

-- 索引就是來提高查詢的性能，

-- 打個比方來說:書的目錄聚集索引；

--書的頁碼:聚集索引

-- 70W條的資料以上我們才考慮建立索引

-- 建立一張表

create table t_stuinfo

(

suid number(7) primary key,

sname varchar2(100),

sbirthday date default sysdate,

ssex char(4)

)

--加載80W條資料

declare

begin

for v_i in 1..1200000 loop

if mod(v_i,2)=0 then

insert into t_stuinfo

values(v_i,'李'||v_i,default,'男');

else

insert into t_stuinfo

values(v_i,'投訓'||v_i,default,'女');

end if;

end loop;

commit;

end;

delete from t_stuinfo

select count(*) from t_stuinfo

select * from t_stuinfo

-- 白盒測驗查詢投訓67001 查這個人的資訊

create or replace procedure p_queryinfo

(

v_name in varchar2

)

v_begintime varchar2(1000);

v_endtime varchar2(1000);

v_birthday t_stuinfo.sbirthday%type;

begin

v_begintime:=to_char(systimestamp,'yyyy-MM-dd hh24:mi:ss.ff');

select s.sbirthday into v_birthday

from t_stuinfo s where s.sname=v_name;

v_endtime:=to_char(systimestamp,'yyyy-MM-dd hh24:mi:ss.ff');

dbms_output.put_line('開始時間:'||v_begintime);

dbms_output.put_line('接受時間:'||v_endtime);

end;

/**

開始時間:2018-03-23 09:29:31.490000000

接受時間:2018-03-23 09:29:31.537000000 --47000000

開始時間:2018-03-23 09:32:05.235000000

接受時間:2018-03-23 09:32:05.250000000 --15000000

開始時間:2018-03-23 09:32:52.942000000

接受時間:2018-03-23 09:32:52.958000000 16000000

開始時間:2018-03-23 09:33:26.529000000

接受時間:2018-03-23 09:33:26.576000000 47

**/

-- 建立索引

create index snameindex on t_stuinfo(sname);

drop index snameindex;

call p_queryinfo('李966002');

-- job定時任務資料庫定時任務

--自動化定時任務對于表的歷史資料的一個定時清理

-- 1-10

-- t_stuinfo ，每隔1分鐘自動插入一條資料

delete from t_stuinfo;

commit;

create sequence seq_stuinfo

start with 1

increment by 1

nocache;

--建立一個存盤程序

create or replace procedure p_timetaskadddata

begin

insert into t_stuinfo

values(seq_stuinfo.nextval,'李欣',default,'男');

commit;

end;

--建立一個任務

variable job2018 number;

-- 命令列視窗提交這個定時任務

SQL> variable job2018 number;

SQL> begin

2 dbms_job.submit(:job2018,'p_timetaskadddata;',

sysdate,'sysdate+1/1440');

3 end;

4 /

select * from t_stuinfo

SQL> begin

2 dbms_job.remove(:job2018);

3 end;

4 /

**PL/SQL procedure successfully completed

job2018

---------**

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/232441.html

標籤：其他

上一篇：[20191113]oracle共享連接模式埠2.txt

下一篇：oracle 修改密碼