Oracle資料庫遊標,序列,儲存過程,儲存函式,觸發器
摘要：
遊標的概念:
遊標是SQL的一個記憶體工作區，由系統或使用者以變數的形式定義。遊標的作用就是用於臨時儲存從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁碟的表中調到計算機記憶體中進行處理，最後將處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁碟...
遊標的概念:
遊標是SQL的一個記憶體工作區，由系統或使用者以變數的形式定義。遊標的作用就是用於臨時儲存從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁碟的表中調到計算機記憶體中進行處理，最後將處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁碟資料交換會降低效率。
遊標有兩種型別：顯式遊標和隱式遊標。在前述程式中用到的SELECT...INTO...查詢語句，一次只能從資料庫中提取一行資料，對於這種形式的查詢和DML操作，系統都會使用一個隱式遊標。但是如果要提取多行資料，就要由程式設計師定義一個顯式遊標，並通過與遊標有關的語句進行處理。顯式遊標對應一個返回結果為多行多列的SELECT語句。
遊標一旦開啟，資料就從資料庫中傳送到遊標變數中，然後應用程式再從遊標變數中分解出需要的資料，並進行處理。
隱式遊標
如前所述，DML操作和單行SELECT語句會使用隱式遊標，它們是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 刪除操作：DELETE。
* 單行查詢操作：SELECT ... INTO ...。
當系統使用一個隱式遊標時，可以通過隱式遊標的屬性來了解操作的狀態和結果，進而控制程式的流程。隱式遊標可以使用名字SQL來訪問，但要注意，通過SQL遊標名總是隻能訪問前一個DML操作或單行SELECT操作的遊標屬性。所以通常在剛剛執行完操作之後，立即使用SQL遊標名來訪
動手敲o~~~
/*
遊標:是用來操作查詢結果集,相當於是JDBC中的ResultSet
語法:cursor 遊標名[(引數名 引數型別)] is 查詢結果集
開發步驟:
1.宣告遊標
2.開啟遊標
3.從遊標中取資料 fetch 遊標名 into 變數
遊標名%found :找到資料
遊標名%notfound :沒找到資料
4.關閉遊標close 遊標名
系統引用遊標
1. 宣告遊標 : 遊標名 sys_refcursor
2. 開啟遊標: open 遊標名 for 結果集
3. 從遊標中取資料
4. 關閉遊標
for迴圈遍歷遊標:
不需要宣告額外變數
不需要開啟遊標
不需要關閉遊標
*/
--輸出員工表中所有的員工姓名和工資(不帶引數遊標)
/*
遊標:所有員工
宣告一個變數,用來記錄一行資料%rowtype
*/
declare
--遊標
cursor vrows is select * from emp;
--宣告變數,記錄一行資料
vrow emp%rowtype;
begin
--1.開啟遊標
open vrows;
--2.從遊標提取資料
--迴圈取資料
loop
fetch vrows into vrow ;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
--3.關閉遊標
close vrows;
end;
--輸出指定部門下的員工姓名和工資帶引數
/*
遊標:指定部門所有的員工
宣告一個變數記錄一行資料
*/
declare
cursor vrows(dno number) isselect * from emp where deptno = dno;
--宣告變數
vrow emp%rowtype;
begin
--1.開啟遊標,指定10號部門
open vrows(10);
--2.迴圈遍歷,取資料
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
close vrows;
end;
--系統引用遊標
--輸出員工表中所有的員工姓名和工資
declare
--宣告系統引用遊標
vrows sys_refcursor;
--宣告變數
vrow emp%rowtype;
begin
--1.開啟遊標
open vrows for select * from emp;
--2.迴圈得到資料
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
close vrows ;
end;
--使用for迴圈遍歷遊標
declare
cursor vrows is select * from emp;
begin
for vrow in vrows loop
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal || '工作:'|| vrow.job);
end loop;
end;
--按照員工工作給所有員工漲工資,總裁漲1000,經理漲800,其他人漲400
/*
遊標所有員工
宣告一個記錄一行資料
*/
declare
cursor vrows is select * from emp;
vrow emp%rowtype;
begin
open vrows;
--迴圈
loop
--取資料
fetch vrows into vrow;
--退出條件
exit when vrows%notfound ;
--根據不同的職位,漲工資 總裁漲1000,經理漲800,其他人漲400
if vrow.job = 'PRESIDENT' then
update emp set sal = sal + 1000 where empno = vrow.empno;
elsifvrow.job = 'MANAGER' then
update emp set sal = sal + 800 where empno = vrow.empno;
else
update emp set sal = sal + 400 where empno = vrow.empno;
end if;
end loop;
--關閉遊標
close vrows;
--提交事務
commit;
end;
/*
例外:(意外)程式執行的過程發生異常,相當於是JAVA中的異常
declare
--宣告變數
begin
--業務邏輯
exception
--處理異常
when 異常1 then
...
when 異常2 then
...
when others then
...處理其它異常
end;
zero_divide : 除零異常
value_error : 型別轉換異常
too_many_rows : 查詢出多行記錄,但是賦值給了rowtype記錄一行資料變數
no_data_found : 沒有找到資料
自定義異常:
異常名exception;
raise 異常名
*/
declare
vi number;
vrow emp%rowtype;
begin
--vi := 8/0;
--vi := 'aaa';
--select * into vrow from emp;
select * into vrow from emp where empno=1234567;
exception
when zero_divide then
dbms_output.put_line('發生了除零異常');
when value_error then
dbms_output.put_line('發生了型別轉換異常');
when too_many_rows then
dbms_output.put_line(' 查詢出多行記錄,但是賦值給了rowtype記錄一行資料變數');
when no_data_found then
dbms_output.put_line('沒有找到資料異常');
when others then
dbms_output.put_line('發生了其它異常' || sqlerrm);
end;
--查詢指定編號的員工,如果沒有找到,則丟擲自定義的異常
/*
--錯誤的演示
1.宣告一個變數 %rowtype
2.查詢員工資訊,儲存起來
3.判斷員工資訊是否為空
4. 如果是 則丟擲異常
*/
declare
--1.宣告一個變數 %rowtype
vrow emp%rowtype;
--2 .宣告一個自定義的異常
no_emp exception;
begin
--查詢員工資訊,儲存起來
select * into vrow from emp where empno = 8888;--丟擲異常
if vrow.sal is null then
raise no_emp; --丟擲自定義的異常
end if;
exception
when no_emp then
dbms_output.put_line('輸出了自定義的異常');
when others then
dbms_output.put_line('輸出了其它異常'||sqlerrm);
end;
--查詢指定編號的員工,如果沒有找到,則丟擲自定義的異常
/*
遊標來判斷
%found %notfound
宣告一個遊標
宣告一個變數,記錄資料
從遊標中取記錄
如果有,則不管它
如果沒有就丟擲自定義的異常
*/
declare
--宣告遊標
cursor vrows is select * from emp where empno=8888;
--宣告一個記錄型變數
vrow emp%rowtype;
--宣告一個自定義異常
no_emp exception;
begin
--1.開啟遊標
open vrows;
--2.取資料
fetch vrows into vrow;
--3.判斷遊標是否有資料
if vrows%notfound then
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line('發生了自定義的異常');
end;
/*
儲存過程: 實際上是封裝在伺服器上一段PLSQL程式碼片斷,已經編譯好了的程式碼
1.客戶端取呼叫儲存過程,執行效率就會非常高效
語法:
create [or replace] procedure 儲存過程的名稱(引數名 in|out 引數型別,引數名 in|out 引數型別)
is | as
--宣告部分
begin
--業務邏輯
end;
*/
--給指定員工漲薪,並列印漲薪前和漲薪後的工資
/*
引數 : in 員工編號
引數 : in 漲多少
宣告一個變數 : 儲存漲工資前的工資
查詢出當前是多少
列印漲薪前的工資
更新工資
列印漲薪後的工資
*/
create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
--宣告變數.記錄當前工資
vsal number;
begin
--查詢當前的工資
select sal into vsal from emp where empno = vempno;
--輸出漲薪前的工資
dbms_output.put_line('漲薪前:'||vsal);
--更新工資
update emp set sal = vsal + vnum where empno = vempno;
--輸出漲薪後的工資
dbms_output.put_line('漲薪後:'||(vsal+vnum));
--提交
commit;
end;
select * from emp;
--方式1
call proc_updatesal(7788,10);
declare
begin
proc_updatesal(7788,-10);
end;
/*
儲存函式: 實際上是一段封裝是Oracle伺服器中的一段PLSQL程式碼片斷,它是已經編譯好了的程式碼片段
語法:
create [or replace] function 儲存函式的名稱(引數名 in|out 引數型別,引數名 in|out 引數型別) return 引數型別
is | as
begin
end;
儲存過程和函式的區別:
1.它們本質上沒有區別
2.函式存在的意義是給過程呼叫儲存過程裡面呼叫儲存函式
3.函式可以在sql語句裡面直接呼叫
4.儲存過程能實現的,儲存函式也能實現,儲存函式能實現的,過程也能實現
預設是 in
*/
--查詢指定員工的年薪
/*
引數 : 員工的編號
返回 : 年薪
*/
create or replace function func_getsal(vempno number) return number
is
--宣告變數,儲存年薪
vtotalsal number;
begin
select sal*12+nvl(comm,0) into vtotalsal from emp where empno = vempno;
return vtotalsal;
end;
--呼叫儲存函式
declare
vsal number;
begin
vsal := func_getsal(7788);
dbms_output.put_line(vsal);
end;
--查詢員工的姓名,和他的年薪
select ename,func_getsal(empno) from emp;
--查詢指定員工的年薪--儲存過程來實現
--引數: 員工編號
--輸出: 年薪
create or replace procedure proce_getsal(vempno in number ,vtotalsal out number)
is
begin
select sal*12+nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;
--呼叫
declare
vtotal number;
begin
proce_getsal(7788,vtotal);
dbms_output.put_line('年薪:'||vtotal);
end;
/*
JAVA呼叫儲存過程
JDBC的開發步驟:
1.匯入驅動包
2.註冊驅動
3.獲取連線
4.獲取執行SQL的statement
5.封裝引數
6.執行SQL
7.獲取結果
8.釋放資源
*/
/*
封裝一個儲存過程 : 輸出所有表中的記錄
輸出型別 : 遊標
*/
create or replace procedure proc_getemps(vrows out sys_refcursor)
is
begin
--1.開啟遊標, 給遊標賦值
open vrows for select * from emp;
end;
/*
觸發器: 當用戶執行了 insert | update | delete 這些操作之後, 可以觸發一系列其它的動作/業務邏輯
作用 :
在動作執行之前或者之後,觸發業務處理邏輯
插入資料,做一些校驗
語法:
create [or replace] trigger 觸發器的名稱
before | after
insert | update | delete
on 表名
[for each row]
declare
begin
end;
觸發器的分類:
語句級觸發器:不管影響多少行, 都只會執行一次
行級觸發器:影響多少行,就觸發多少次
:old代表舊的記錄, 更新前的記錄
:new代表的是新的記錄
*/
--新員工入職之後,輸出一句話: 歡迎加入大少家庭
create or replace trigger tri_test1
after
insert
on emp
declare
begin
dbms_output.put_line('歡迎加入大少家庭');
end;
insert into emp(empno,ename) values(9527,'dashao');
--資料校驗, 星期六老闆不在, 不能辦理新員工入職
--在插入資料之前
--判斷當前日期是否是週六
--如果是週六,就不能插入
create or replace trigger tri_test2
before
insert
on emp
declare
--宣告變數
vday varchar2(10);
begin
--查詢當前
select trim(to_char(sysdate,'day')) into vday from dual;
--判斷當前日期:
if vday = 'saturday' then
dbms_output.put_line('老闆不在,不能辦理入職');
--丟擲系統異常
raise_application_error(-20001,'老闆不在,不能辦理入職');
end if;
end;
insert into emp(empno,ename) values(9528,'dashao2');
--更新所有的工資 輸出一句話
create or replace trigger tri_test3
after
update
on emp
for each row
declare
begin
dbms_output.put_line('更新了資料');
end;
update emp set sal = sal+10;
--判斷員工漲工資後的工資一定要大於漲工資前的工資
/*
200 --> 100
觸發器 : before
舊的工資
新的工資
如果舊的工資大於新的工資 , 丟擲異常,不讓它執行成功
觸發器中不能提交事務,也不能回滾事務
*/
create or replace trigger tri_updatesal
before
update
on emp
for each row
declare
begin
if :old.sal > :new.sal then
raise_application_error(-20002,'舊的工資不能大於新的工資');
end if;
end;
update emp set sal = sal + 10;
select * from emp;
update emp set sal = sal - 100;
/*
模擬mysql中ID的自增屬性 auto_increment
insert into person(null,'張三');
觸發器:
pid=1insertpid=1
序列 : create sequence seq_person_pid;
*/
create table person(
pid number primary key,
pname varchar2(20)
);
insert into person values(null,'張三');
create sequence seq_person_pid;
--觸發器
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare
begin
dbms_output.put_line(:new.pname);
--給新記錄 pid 賦值
select seq_person_pid.nextval into :new.pid from dual;
end;
insert into person values(null,'張三');
select * from person;