六、循環語句：
1. 基本循環：
LOOP
?????? 執行語句；
EXIT WHEN 條件成立；
END LOOP;

例：
Declare
???????? v_deptno emp.deptno%type:=&deptno;
???????? i number(2):=0;
???? Begin
???????? loop
?????????? i := i +1;
?????????? insert into emp(empno,hiredate,deptno)
?????????? values(i+7200,sysdate,v_deptno);
?????????? dbms_output.put_line('i的當前值為:'||i);
????????? exit when i=10;
????????? end loop;
??? End;

2. WHILE循環:

WHILE 條件成立
LOOP 執行語句；
END LOOP;

Declare
???????? v_deptno emp.deptno%type:=&deptno;
???????? i number(2):=0;
???? Begin
???????? while i? < 10?? loop
?????????? i := i +1;
?????????? insert into emp(empno,hiredate,deptno)
?????????? values(i+7200,sysdate,v_deptno);
?????????? dbms_output.put_line('i的當前值為:'||i);
?????? --?? exit when i=10;
????????? end loop;
??? End;
???
3. FOR循環:
FOR 計數器 IN 低界..高界
LOOP 執行語句;
END LOOP;

例：
Declare
???????? v_deptno emp.deptno%type:=&deptno;
???????? i number(2):=0;
???? Begin
???????? while i? < 10?? loop
?????????? i := i +1;
?????????? insert into emp(empno,hiredate,deptno)
?????????? values(i+7200,sysdate,v_deptno);
?????????? dbms_output.put_line('i的當前值為:'||i);
?????? --?? exit when i=10;
????????? end loop;
??? End;

七、光標設計(Cursor):

1、什么是光標？在PL/SQL中，當查詢語句執行結果超過一行時，為處理每一行，必須定義一個cursor，叫光標。

2、光標使用方法：
(1) 定義光標：
語法：光標名 is select 語句;

(2) 打開光標：
語法：open 光標名;

(3) 取數據：
語法：Fetch 光標名 into 變量;

(4) 光標下移：使用loop循環

(5) 關閉光標：close 光標名;

例：
Declare
??????? v1 emp.empno%type;
??????? v2 emp.ename%type;
??????? v3 emp.sal%type;
??????? cursor c is select empno,ename,sal from emp;
???? Begin
???????? open c;
???????? loop
???????? fetch? c into v1,v2,v3;
???????? if v3<3000 then Begin
?????????????????????????? update emp set sal=sal+100
?????????????????????????? where empno=v1;
??????????????????? dbms_output.put_line('員工'||v2||'工資已經更新!');
??????????????????????????????????? End;
???????? end if;
???????? exit when c%NOTFOUND;
???????? end loop;
???????? close c;
???? End;
/

-- //要求從emp表中取出按用戶輸入要求的前幾位工資最高的人員及其工資放入topsalary表中：
-- // 我的方法：
SQL>create table topsalary (
?name varchar2(20),
?sal number(7,2));
?
SQL>Declare
? vcount number(7) := &n;
? i number(7) :=0;
??????? vname emp.ename%type;
??????? vsal emp.sal%type;
??????? cursor c is select ename,sal from emp order by nvl(sal,0) desc; --// 使用nvl函數防止工資出現空值的情況，老師補充
???? Begin
????? delete topsalary;? -- // 先清空topsalary表
???????? open c;
????????
???????? for i in 1 .. vcount loop
???????? fetch? c into vname, vsal;
????????
???????? insert into topsalary values(vname,vsal);
????????
???????? end loop;
???????? close c;
???? End;
?
--//教師的方法
Declare
??? i number(3) :=&i;
??? j number(3) :=0;
?? cursor c is select ename,sal from emp order by nvl(sal,0) desc;
?? v1 emp.ename%type;
?? v2 emp.sal%type;
Begin
?? open c;
?? loop
?? j := j+1;
?? fetch c into v1,v2;
?? insert into topsalary? values(v1,v2);
?? exit when j=i;
?? end loop;
?? close c;
End;

3. 光標屬性:? 每一個光標有四種屬性
%FOUND???????? 查詢語句(FETCH語句)返回記錄
%NOTFOUND????? 查詢語句(FETCH語句)無返回記錄，用于循環退出條件
%ROWCOUNT???? FETCH已獲取的記錄數
%ISOPEN??????? 光標已打開標記

例：
Declare
??????? v1 emp.empno%type;
??????? v2 emp.ename%type;
??????? v3 emp.sal%type;
??????? cursor c is select empno,ename,sal from emp;
???? Begin
???????? open c;
???????? loop
???????? fetch? c into v1,v2,v3;
???????? if v3<3000 then Begin
?????????????????????????? update emp set sal=sal+100
?????????????????????????? where empno=v1;
??????????????????? dbms_output.put_line('員工'||v2||'工資已經更新!');
??????????????????????????????????? End;
???????? end if;
???????? exit when c%NOTFOUND;
???????? end loop;
???????? dbms_output.put_line('光標處理的行數：'||C%ROWCOUNT);
???????? close c;
???? End;
????
4. 隱式光標處理: 隱式光標是指在處理SQL時,不需定義光標，所使用的SQL語句包括：INSERT ,UPDATE,DELETE子句。

隱式光標屬性:?????? SQL%FOUND??????? SQL%NOTFOUND????????????? SQL%ROWCOUNT

隱式光標的使用:
Declare
?v_empno emp.empno%type:=&empno;
Begin
?delete from emp where empno=v_empno;
?if SQL%NOTFOUND then
? dbms_output.put_line('你的刪除失敗,數據庫無此人:'||v_empno);
?end if;
End;

[例外處理Exception]
Declare
?????? v_empno emp.empno%type :=&empno;
?????? v_ename emp.ename%type;
?????? v_sal emp.sal%type;
Begin
????? select sal,ename into v_sal,v_ename from emp
????? where empno=v_empno;
?????? if v_sal<2000 then Begin
????????????????? update emp set sal=sal+100
????????????????? where empno=v_empno;
??????? dbms_output.put_line('員工'||v_ename||'工資已經修改!');
?????????????????????????????????????? End;
??????? elsif v_sal<2500 then Begin
????????????????? update emp set sal=sal+50
????????????????? where empno=v_empno;
??????? dbms_output.put_line('員工'||v_ename||'工資已經修改!');
?????????????????????????????????????? End;
??????? elsif v_sal<3000 then Begin
????????????????? update emp set sal=sal+10
????????????????? where empno=v_empno;
??????? dbms_output.put_line('員工'||v_ename||'工資已經修改!');
?????????????????????????????????????? End;
??????? else dbms_output.put_line('員工'||v_ename||'的工資已經超過規定值,不予更新!');
??????? End if;
Exception?? -- // 例外處理
?when NO_DATA_FOUND then dbms_output.put_line('數據庫中沒有編碼為'||v_empno||'的員工。');
?when TOO_MANY_ROWS then
? dbms_output.put_line('你的查詢語句返回結果出現多行，請定義光標后重試！');
?when OTHERS then
? dbms_output.put_line('你的程序是錯誤的，請仔細檢查后重試！');
End;

[用戶定義的例外]
(1)在Declare段定義
(2)在Begin段中用Raise引起。
(3)在Exception段中使用。

Declare
?????? v_empno emp.empno%type :=&empno;
?????? no_result exception;
Begin
?delete from emp where empno = v_empno;
?if SQL%NOTFOUND then raise no_result;
?end if;
?
Exception?? -- // 例外處理
?when NO_DATA_FOUND then dbms_output.put_line('數據庫中沒有編碼為'||v_empno||'的員工。');
?when NO_RESULT then dbms_output.put_line('數據庫中沒有編碼為'||v_empno||'的員工。');
?when TOO_MANY_ROWS then
? dbms_output.put_line('你的查詢語句返回結果出現多行，請定義光標后重試！');
?when OTHERS then
? dbms_output.put_line('你的程序是錯誤的，請仔細檢查后重試！');
End;

[存儲過程(Storage Procedure)與函數(Function)設計]

一、什么是存儲過程？
?存儲過程（函數）是把一個PL/SQL塊存儲到數據庫中，作為一個數據庫實體，可以在其它存儲過程、函數、應用程序中調用。
1、存儲過程的調用方法：
(1) 在SQL*Plus中調用方法：SQL> Execute 存儲過程名稱; // execute 可以簡寫為Exec。
(2) 在其它存儲過程、函數、應用程序中調用方法：存儲過程名稱;

2、存儲過程的設計方法：
編寫程序->在SQL*PLUS中編譯->修改錯誤->調用執行。

二、創建存儲過程的語法：[參考PowerPoint教程:存儲過程1.ppt(P3)]
說明：
?IN: 調用者向過程傳遞參數
?OUT: 過程向調用者傳遞參數
?IN OUT: 雙向傳遞參數
?無Declare

[例1：]IN: 刪除數據
Create or Replace procedure DelEmp(v_empno in emp.empno%type) is
Begin
?delete from emp where empno=v_empno;
?dbms_output.put_line('編碼為'||v_empno||'的員工已被除名！');
End DelEmp;

調用方法：
SQL> Exec DelEmp(7788);
SQL> Exec DelEmp(7934);

[例2：]IN: 插入數據
Create or Replace procedure InsertEmp( v_empno in emp.empno%type,
??????? v_ename in emp.ename%type,
??????? v_deptno in emp.deptno%type) is
Begin
?insert into emp(empno, ename, hiredate,deptno)
? values(v_empno, v_ename, sysdate, v_deptno);
?dbms_output.put_line('新員工“'||v_ename||'”錄入成功！');
End InsertEmp;

[例3：]IN、OUT:數據查詢
Create or Replace procedure QueryEmp(v_empno in emp.empno%type,
???????????????????????????????????? v_ename out emp.ename%type,
???????????????????????????????????? v_job out emp.job%type) is
Begin
? select ename,job into v_ename,v_job from emp
? where empno=v_empno;
End QueryEmp;

調用方法：
Declare
? v1 emp.ename%type;
? v2 emp.job%type;
?? v? emp.empno%type:=&empno;
Begin
??? QueryEmp(v,v1,v2);
?? dbms_output.put_line('編碼為'||v||'的員工姓名是：'||v1||'職業是：'||v2);
End;

[* 查詢存儲過程源代碼：]
SQL> select text from user_source where name = 'DELEMP';? // 名字要大寫

三、創建函數語法：
例1：
Create or replace Function GetSalary( v empno%type) return numbwr is
?v_sal emp.sal%type;
Begin
?select sal into v_sal from emp
?where empno=v;
?return v_sal;
End GetSalary;
?
?[調用方法：]
?(1) SQL> Exec dbms_output.put_line(GetSalary(7788));
?(2) SQL> select GetSalary(7788) from dual;
?(3) 使用PL/SQL的方法：
?Begin
? dbms_output.put_line(GetSalary(7788));
?End;

[函數例2]
Create or replace function getmaxsal
return number
is
?v_maxsal emp.sal%type;
?begin
?? select max(sal) into v_maxsal from emp;
?? return v_maxsal;
?end getmaxsal;
?
? [調用方法：]
?(1) SQL> Exec dbms_output.put_line(GetMaxSal);
?(2) SQL> select GetMaxSal from dual;
?(3) 使用PL/SQL的方法：
?Begin
? dbms_output.put_line(GetMaxSal);
?End;
?
[練習題]: 求n!函數（n的階乘)。

[我的答案]
Create or replace function nj(n in number)
?return number
?is
?? numtmp number;
?Begin
?? if n = 0 then
??? numtmp := 1;
?? else
??? numtmp := n * nj(n - 1);
?? end if;
??
?? return numtmp;
?End nj;
?
?[教師的答案]// 高，　實在是高！！！
Create or replace function fn(n in number)
return number
is
?Begin
? if n=0 then return 1;
?else return n*fn(n-1);
?end if;
End fn;

四、存儲過程及函數管理：
1、查詢存儲過程及函數的源代碼：
SQL> select * from user_source;
SQL> select * from dba_source;
SQL> select * form all_source;

2、存儲過程及函數的權限管理：
授權命令：
SQL> grant execute on 過程（或函數） to 用戶名;
SQL> grant execute on 過程（或函數） to public;? --//授權給全體用戶

權限回收：
SQL> Revoke execute on 過程（或函數） from 用戶名;
SQL> Revoke execute on 過程（或函數） from public;

3、查詢錯誤信息：
SQL> select * from user_errors;
SQL> show errors;

4、查詢依賴性信息：
依賴性：實體的結構定義修改時，對創建在這些實體上的存儲過程及函數的影響叫依賴性。

顯示依賴性關系：
SQL> select name,type, referenced_owner, referenced_name, referenced_type from user_dependencies;

5、刪除存儲過程及函數：
SQL> drop procedure 過程名;
SQL> drop function 函數名;

轉載于:https://blog.51cto.com/globle/642466

總結

以上是生活随笔為你收集整理的Oracle笔记(四)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。