1.知識點：可以對照下面的錄屏進行閱讀
PL/SQL程序結構
declare
說明部分 （變量說明，光標申明，例外說明 〕
begin
語句序列 （DML語句〕…
exception
例外處理語句
End;
/
------------------------------------------------------------------
--第一個PL/SQL程序：HelloWorld
set serveroutput on --如果要在屏幕上輸出信息，需要將serveroutput開關打開
declare
--變量的說明
begin
--程序體
--程序包package
dbms_output.put_line('Hello World');
end;
/
------------------------------------------------------------------
--引用型變量: 查詢並打印7839的姓名和薪水
set serveroutput on
declare
--定義變量，
pename emp.ename%type; --將pename的類型設置為emp表中ename的類型
psal emp.sal%type; --將psal的類型設置為emp表中sal的類型
begin
--查詢：
--ename,sal into pename,psal：將查詢的ename，sal分別賦值給pename，psal按順序賦值
select ename,sal into pename,psal from emp where empno=7839;
--打印
dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
------------------------------------------------------------------
--記錄型變量 查詢並打印7839的姓名和薪水
set serveroutput on
declare
--定義變量,代表一行
emp_rec emp%rowtype;
begin
--得到一行，賦值給emp_rec
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
------------------------------------------------------------------
--if語句：判斷用戶輸入的數字
set serveroutput on
/*
1.提示信息
2. 接收鍵盤輸入
num 是一個地址值
SQL優化: num綁定變量(盡量使用綁定變量)
select * from emp where deptno=10; --> 執行計劃
select * from emp where deptno=20; --> 執行計劃
-->
select * from emp where deptno=#
*/
accept num prompt '請輸入一個數字';
declare
--變量保存輸入的數字
pnum number := #
begin
--判斷
if pnum = 0 then
dbms_output.put_line('您輸入的是0');
elsif pnum = 1 then
dbms_output.put_line('您輸入的是1');
elsif pnum = 2 then
dbms_output.put_line('您輸入的是2');
else
dbms_output.put_line('其他數字');
end if;
end;
/
------------------------------------------------------------------
--循環： 打印1~10，有3中方法，見下圖
set serveroutput on
declare
pnum number := 1; --給pnum賦值1，pnum類型為number
begin
loop
--退出：成立退出，不成立循環
exit when pnum > 10;
--隱式轉換，number轉varchar2
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
/
------------------------------------------------------------------
光標（Cursor）==ResultSet
說明光標語法：
CURSOR 光標名 [ (參數名 數據類型[,參數名 數據類型]...)]
IS SELECT 語句;
用於存儲一個查詢返回的多行數據
例如：
cursor c1 is select ename from emp;
打開光標： open c1; (打開光標執行查詢)
取一行光標的值：fetch c1 into pjob; (取一行到變量中)
關閉光標： close c1;(關閉游標釋放資源)
注意： 上面的pjob必須與emp表中的job列類型一致：
定義：pjob emp.empjob%type;
------------------------------------------------------------------
--光標: 使用游標查詢員工姓名和工資，並打印
/*
光標的3個屬性：
%isopen 是否被打開：打開true，關閉false；
%rowcount 行數
%notfound 是否有值：沒有值true，有值false；
*/
set serveroutput on
declare
--光標
cursor cemp is select ename,sal from emp;
pename emp.ename%type; --將用來接收的變量類型設置為和查詢的類型相同
psal emp.sal%type;
begin
open cemp; --打開光標執行查詢
loop
--從集合中取值
fetch cemp into pename,psal;
--光標中沒有值時退出
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close cemp;
end;
/
------------------------------------------------------------------
--光標練習：給員工漲工資 總裁1000 經理800 其他400
--光標同時能打開的最大數量默認為300個
/*
SQL> show parameters cursor
NAME TYPE VALUE
------------------------------------ ----------- --------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
*/
set serveroutput on
declare
--光標代表員工
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判斷
if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; --總裁漲1000
elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --經理漲800
else update emp set sal=sal+400 where empno=pempno; --其他漲400
end if;
end loop;
close cemp;
--提交： 隔離級別
commit;
dbms_output.put_line('完成');
end;
/
------------------------------------------------------------------
--帶參數的光標：查詢某個部門的員工姓名
set serveroutput on
declare
--定義一個帶參數的光標
cursor cemp(pdno number) is select ename from emp where deptno=pdno;
pename emp.ename%type;
begin
open cemp(20); --打開光標，並傳遞參數
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
------------------------------------------------------------------
Oracle的異常處理
1.系統定義例外
No_data_found (沒有找到數據)
Too_many_rows (select …into語句匹配多個行)
Zero_Divide ( 被零除)
Value_error (算術或轉換錯誤)
Timeout_on_resource (在等待資源時發生超時)
--舉例 Zero_Divide ( 被零除)
set serveroutput on
declare
pnum number;
begin
pnum := 1/0;
exception
when Zero_Divide then dbms_output.put_line('1: 0不能做被除數');
dbms_output.put_line('2: 0不能做被除數');
when Value_error then dbms_output.put_line('算術錯');
when others then dbms_output.put_line('其他例外');
end;
/
用戶自己定義的例外
--自定義例外： 查詢50號部門的員工姓名
set serveroutput on
declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定義例外
no_emp_found exception;
begin
open cemp;
--取一個員工
fetch cemp into pename;
if cemp%notfound then
raise no_emp_found;
end if;
/*
if cemp%isopen then
close no_emp_found;
end if;
*/
close cemp;
exception
when no_emp_found then dbms_output.put_line('沒有找到員工');
when others then dbms_output.put_line('其他例外');
end;
/
圖：循環的3種寫法，文中采用右上角的方法。
2.在Sqlplus下實際執行的結果錄屏
SQL> --第一個PL/SQL程序：HelloWorld
SQL> set serveroutput on --如果要在屏幕上輸出信息，需要將serveroutput開關打開
SQL> declare
2 --變量的說明
3 begin
4 --程序體
5 --程序包package
6 dbms_output.put_line('Hello World');
7 end;
8 /
Hello World
PL/SQL procedure successfully completed
SQL>
SQL> --引用型變量: 查詢並打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
2 --定義變量，
3 pename emp.ename%type; --將pename的類型設置為emp表中ename的類型
4 psal emp.sal%type; --將psal的類型設置為emp表中sal的類型
5
6 begin
7
8 --查詢：
9 --ename,sal into pename,psal：將查詢的ename，sal分別賦值給pename，psal按順序賦值
10 select ename,sal into pename,psal from emp where empno=7839;
11
12 --打印
13 dbms_output.put_line(pename||'的薪水是'||psal);
14
15 end;
16 /
KING的薪水是5000
PL/SQL procedure successfully completed
SQL>
SQL> -記錄型變量 查詢並打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
2 --定義變量,代表一行
3 emp_rec emp%rowtype;
4 begin
5 --得到一行，賦值給emp_rec
6 select * into emp_rec from emp where empno=7839;
7
8 dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
9
10 end;
11 /
KING的薪水是5000
PL/SQL procedure successfully completed
SQL>
SQL> --if語句：判斷用戶輸入的數字
SQL> set serveroutput on
SQL> /*
2 1.提示信息
3 2. 接收鍵盤輸入
4 num 是一個地址值
5
6 SQL優化: num綁定變量(盡量使用綁定變量)
7 select * from emp where deptno=10; --> 執行計劃
8 select * from emp where deptno=20; --> 執行計劃
9 -->
10 select * from emp where deptno=#
11
12 */
13 accept num prompt '請輸入一個數字';
SQL> declare
2 --變量保存輸入的數字
3 pnum number := #
4 begin
5 --判斷
6 if pnum = 0 then
7 dbms_output.put_line('您輸入的是0');
8 elsif pnum = 1 then
9 dbms_output.put_line('您輸入的是1');
10 elsif pnum = 2 then
11 dbms_output.put_line('您輸入的是2');
12 else
13 dbms_output.put_line('其他數字');
14 end if;
15 end;
16 /
您輸入的是2
PL/SQL procedure successfully completed
SQL>
SQL> --循環： 打印1~10
SQL> set serveroutput on
SQL> declare
2 pnum number := 1; --給pnum賦值1，pnum類型為number
3 begin
4 loop
5 --退出：成立退出，不成立循環
6 exit when pnum > 10;
7
8 --隱式轉換，number轉varchar2
9 dbms_output.put_line(pnum);
10
11 pnum := pnum + 1;
12
13 end loop;
14 end;
15 /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed
SQL>
SQL> -光標: 使用游標查詢員工姓名和工資，並打印
SQL> /*
2 光標的3個屬性：
3 %isopen 是否被打開：打開true，關閉false；
4 %rowcount 行數
5 %notfound 是否有值：沒有值true，有值false；
6 */
SQL> set serveroutput on
SQL> declare
2 --光標
3 cursor cemp is select ename,sal from emp;
4 pename emp.ename%type; --將用來接收的變量類型設置為和查詢的類型相同
5 psal emp.sal%type;
6 begin
7 open cemp; --打開光標執行查詢
8 loop
9 --從集合中取值
10 fetch cemp into pename,psal;
11 --光標中沒有值時退出
12 exit when cemp%notfound;
13
14 dbms_output.put_line(pename||'的薪水是'||psal);
15
16 end loop;
17 close cemp;
18 end;
19 /
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975
MARTIN的薪水是1250
BLAKE的薪水是2850
CLARK的薪水是2450
SCOTT的薪水是3000
KING的薪水是5000
TURNER的薪水是1500
ADAMS的薪水是1100
JAMES的薪水是950
FORD的薪水是3000
MILLER的薪水是1300
PL/SQL procedure successfully completed
SQL> --光標練習：給員工漲工資 總裁1000 經理800 其他400
SQL> --先查詢沒漲之前的
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> --執行PLSQL語句
SQL>
SQL> set serveroutput on
SQL> declare
2 --光標代表員工
3 cursor cemp is select empno,job from emp;
4 pempno emp.empno%type;
5 pjob emp.job%type;
6 begin
7 open cemp;
8 loop
9 fetch cemp into pempno,pjob;
10 exit when cemp%notfound;
11
12 --判斷
13 if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; --總裁漲1000
14 elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --經理漲800
15 else update emp set sal=sal+400 where empno=pempno; --其他漲400
16 end if;
17 end loop;
18 close cemp;
19
20 --提交： 隔離級別
21 commit;
22
23 dbms_output.put_line('完成');
24 end;
25 /
完成
PL/SQL procedure successfully completed
SQL> --查詢執行PLSQL語句後的薪水
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 2000.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1650.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3775.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1650.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 3650.00 30
7782 CLARK MANAGER 7839 1981/6/9 3250.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7839 KING PRESIDENT 1981/11/17 6000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1900.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 1350.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1700.00 10
14 rows selected
SQL> --對比漲之前，可以看出漲工資成功
SQL>
SQL> --帶參數的光標：查詢某個部門的員工姓名
SQL> set serveroutput on
SQL> declare
2 --定義一個帶參數的光標
3 cursor cemp(pdno number) is select ename from emp where deptno=pdno;
4 pename emp.ename%type;
5 begin
6 open cemp(20); --打開光標，並傳遞參數
7 loop
8 fetch cemp into pename;
9 exit when cemp%notfound;
10
11 dbms_output.put_line(pename);
12
13
14 end loop;
15 close cemp;
16 end;
17 /
SMITH
JONES
SCOTT
ADAMS
FORD
PL/SQL procedure successfully completed
SQL>
SQL> --舉例 Zero_Divide ( 被零除)
SQL> set serveroutput on
SQL> declare
2 pnum number;
3 begin
4
5 pnum := 1/0;
6
7 exception
8 when Zero_Divide then dbms_output.put_line('1: 0不能做被除數');
9 dbms_output.put_line('2: 0不能做被除數');
10 when Value_error then dbms_output.put_line('算術錯');
11 when others then dbms_output.put_line('其他例外');
12 end;
13 /
1: 0不能做被除數
2: 0不能做被除數
PL/SQL procedure successfully completed
SQL>
SQL> --自定義例外： 查詢50號部門的員工姓名
SQL> set serveroutput on
SQL> declare
2 cursor cemp is select ename from emp where deptno=50;
3 pename emp.ename%type;
4
5 --自定義例外
6 no_emp_found exception;
7 begin
8 open cemp;
9 --取一個員工
10 fetch cemp into pename;
11 if cemp%notfound then
12 raise no_emp_found;
13 end if;
14
15 close cemp;
16
17 exception
18 when no_emp_found then dbms_output.put_line('沒有找到員工');
19 when others then dbms_output.put_line('其他例外');
20
21 end;
22 /
沒有找到員工
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to c:\PLSQL.txt
