Oracle快速入門（游標，拋出自定義例外資訊，存盤程序）

一、游標：

1、游標的概念：

是用來操作查詢結果集的，相當于JDBC中的ResultSet

2、查詢結果集（理解游標的作用和概念）

select * from emp;

查詢emp表

3、游標的語法

cursor 游標的名稱 is 查詢的結果集 

4、游標的開發步驟

（1）宣告游標

cursor 游標的名稱 is 查詢的結果集 

（2）打開游標

open 游標名 

（3）從游標當中取資料fetch 從游標 當中 取一條記錄

fetch 游標名 into 變數

判斷游標是否取完資料

游標名：%found : 

游標名%notfound :沒有找到資料

（4）關閉游標

語法

close 游標名

二、案例

1、不帶引數的游標

cursor 游標的名稱 is 查詢的結果集 

（1）輸出員工表中所有的員工姓名和工資(不帶引數游標)

結果集(游標)：所有員工
宣告一個變數，用來記錄一行資料 %rowtype，

declare
       -- 游標：宣告vrows游標 指向emp表的表頭
       cursor vrows is select * from emp;     
       -- 宣告變數，記錄一行資料
       vrow emp%rowtype;--存盤的是emp表第一行的資料
begin
     -- 1、打開游標
     open vrows;    
     -- 2、從游標當中提取資料
     -- 回圈取資料     
     loop       
        fetch vrows into vrow; -- 通過游標vrows取vrow當中的資料，將vrows 當中的資料放置到vrow
        exit when vrows%notfound;  --當vrows游標找不到的時候退出回圈
        dbms_output.put_line('姓名：'|| vrow.ename || ' 工資:' || vrow.sal);   
     end loop;
     --  3、關閉游標
     close vrows;       
end;

2、帶引數的游標

cursor 游標的名稱[(引數名 引數型別)] is 查詢的結果集 

（1）輸出指定部門下的員工姓名和工資

游標：指定部門的所有員工
宣告一個變數記錄一行資料

declare

     -- 宣告游標
     cursor vrows(dno number) is select * from emp where deptno = dno;
     -- 宣告變數
     vrow emp%rowtype;
begin
    -- 1、打開游標,指定10號部門
    open vrows(10);   
    -- 2、回圈遍歷，取資料
    loop 
       fetch vrows into vrow;  -- 通過游標vrows取vrow當中的資料，將vrows 當中的資料放置到vrow
       exit when vrows%notfound;  --當找不到的時候退出   
       dbms_output.put_line('姓名：'|| vrow.ename || ' 工資:' || vrow.sal);    
    end loop;    
    -- 關閉游標
    close vrows;
  
end;

三、系統參考游標

1、系統參考游標語法

（1）宣告游標 ： 游標名 游標型別 sys_refcursor
（2）打開游標：open 游標名 for 結果集
（3）從游標當中取資料
（4）關閉游標

2、案例演示

select * from emp;
declare 
	-- 宣告系統參考游標
	vrows sys_refcursor;
	-- 宣告一個變數來用于記錄資料
	vrow emp%rowtype;--指向emp表的表頭
begin
	--1、打開游標
	open vrows for select * from emp;
	-- 2、取資料
	loop 
		fetch vrows into vrow;--將vrows 當中的資料放置到vrow，vrow會不斷回圈向下走
		exit when vrows%notfound;
    dbms_output.put_line('姓名：'|| vrow.ename || ' 工資:' || vrow.sal);    
	end loop; 
	close vrows;
end;

四、擴展內容，，使用for回圈遍歷游標

for回圈遍歷游標：不需要宣告額外變數，不需要打開游標，不需要關閉游標 if

-- 使用for回圈來遍歷游標
declare 
   -- 宣告一個游標
   cursor vrows is select * from emp;
begin
   for vrow in vrows loop
       dbms_output.put_line('姓名：'|| vrow.ename || ' 工資:' || vrow.sal || vrow.sal || '作業：' || vrow.job);    
   end loop;                         
end;

五、例外（意外）：程式運行的程序當中發生例外，相當于是Java當中的例外

1、捕獲例外的語法

declare 
	-- 宣告變數
begin 
	-- 業務邏輯 判斷和回圈
exception  -- 捕獲例外 
	-- 處理例外
	when 例外一 then 
	  ...
	when 例外二 then
	  ...
	when others then
	  ...處理其他例外
end;

2、Oracle當中常見的例外

zero_divide : 除零例外

value_error : 型別轉換例外

too_many_rows : 查詢出多行記錄，但是賦值給了一行記錄，rowtype記錄一行資料的變數

no_data_fount : 沒有找到資料

（1）發生了除零例外

declare
	vi number;
begin
	vi := 8/0;
exception 
	when zero_divide then 
		dbms_output.put_line('發生了除零例外');
	when others then 
		dbms_output.put_line('發生了其他例外');
end;

（2）其他例外：型別轉換例外

給數字型別的變數賦值字串，發送例外

declare
	vi number;
begin
	vi := 'aaa';
exception 
	when zero_divide then 
		dbms_output.put_line('發生了除零例外');
	when others then 
		dbms_output.put_line('發生了其他例外');
end;

（3）捕獲型別轉換例外

declare
	vi number;
begin
	vi := 'aaa';
exception 
	when zero_divide then 
		dbms_output.put_line('發生了除零例外');
  when value_error then
    dbms_output.put_line('發生了型別轉換例外');
	when others then 
		dbms_output.put_line('發生了其他例外');
end;

（4）too_many_rows : 查詢出多行記錄，但是賦值給了一行記錄，rowtype記錄一行資料的變數

declare
	vi number;
   -- 記錄一行變數
   vrow emp%rowtype;
begin
	 select * into vrow from emp;
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 others then 
		dbms_output.put_line('發生了其他例外');
end;

（5） 沒有找到資料的例外

declare
	vi number;
  -- 記錄一行變數
  vrow emp%rowtype;
begin
	 select * into vrow from emp where empno = 123456; -- 查詢資料并賦值給vrow
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 others then 
		dbms_output.put_line('發生了其他例外' ||  sqlerrm);
end;

詳細的捕獲例外

declare
	vi number;
  -- 記錄一行變數
  vrow emp%rowtype;
begin
	 select * into vrow from emp where empno = 123456; -- 查詢資料并賦值給vrow
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、語法

例外名 exception;
rasie 例外;

2、查詢指定編號的員工，如果沒有找到，則拋出自定義的例外，

a、宣告一個變數 % rowtype ，
b、查詢員工資訊資訊，保存起來，
c、判斷員工資訊是否為空，
d、如果是則拋出例外，

（1）拋出其他例外

select * from emp;
-- 查詢指定編號的員工，如果沒有找到，則拋出自定義的例外
declare
   -- 1、宣告一個變數 %rowtype
   vrow emp%rowtype;
   -- 2、宣告一個自定義的例外
   no_emp exception;
begin
    -- 3、查詢員工資訊，保存起來
   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;

（2）錯誤演示，拋出指定例外

%found % notfound 

宣告一個游標
宣告一個變數，記錄資料
從游標當中取記錄，如果取到了
如果有，則不管
如果沒有就拋出自定義例外

declare 
      -- 宣告游標vrows是查詢表當中的empno = 8888
      cursor vrows is select * from emp where empno = 8888;
      --宣告記錄型的變數，宣告記錄型，指向emp表的表頭
      vrow emp%rowtype;  
      -- 宣告一個自定義例外
      no_emp exception;
begin
      -- 1、打開游標
      open vrows;    
      -- 2、取資料
      fetch vrows into vrow;
      -- 3、判斷游標是否有資料
      if vrows%notfound then 
           raise no_emp;--raise申請這個例外
      end if;
      close vrows;
-- 捕獲例外
exception 
   when no_emp then
     dbms_output.put_line('發生了自定義的例外');
end;

七、存盤程序 ：實際上是封裝在服務器上一段PLSQL代碼片段，已經編譯好了的代碼

1、客戶端去呼叫存盤程序，執行效率就會非常高高效

2、語法 ：

create [or replace] procedure 存盤程序的名稱（引數名 in|out 引數型別）is | as 
is | as
 --宣告部分
begin 
 -- 業務邏輯
end;

3、給指定員工漲薪，并列印漲薪前和漲薪后的工資

引數：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_ouput.put_line('漲薪后：' || (vsal+vnum));
	--提交事務
	commit;
end;

-- 方式1
call proc_updatesal(7788,10);

-- 方式2  用的最多的一種方式
declare 
begin
   proc_updatesal(7788,-100);
end;

八、存盤函式：實際上是一段封裝在Oracle服務器中的一段PLSQL代碼片段，它是已經編譯好了的代碼片段

1、語法

create {or replace} function 存盤函式的名稱（引數名 in|out 引數型別,引數名 in|out 引數型別） 
is | as

begin

end;

2、存盤程序和函式的區別；

（1）他們本質上沒有區別，
（2）函式存在的意義是給程序呼叫的 存盤程序里面呼叫存盤函式 ，
（3）函式可以在sql陳述句里面直接呼叫，
（4）存盤程序能實作的，存盤函式也能實作，存盤函式能實作的，存盤程序也能實作
默認就是in，

3、相關案例

（1）查詢指定員工的年薪

引數：員工的編號
回傳：年薪

a、創建存盤函式（回傳值的方式得到對應的值）

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;

b、呼叫存盤函式:查詢某個員工編號對應的年薪

呼叫上面創建的函式，查詢7788員工編號對應的員工資訊

declare 
	vsal number;
begin
	vsal := func_getsal(7788);
	dbms_output.put_line(vsal);
end;

（2）查詢員工的姓名，和他的年薪

select ename,func_getsal(empno) from emp;

（3）查詢指定員工的年薪–使用存盤程序來實作（傳入引數，將值存盤到引數當中）

引數：是員工編號
輸出：年薪

create or replace procedure proc_gettotalsal(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
  proc_gettotalsal(7788,vtotal);-- 呼叫函式查詢年薪和工資
  dbms_output.put_line('年薪：'||vtotal);輸出年薪
end;