1.什麼是pl/sql（Procedure Language & Structured Query Language）：
pl/sql是一種進階資料庫程式設計語言，能夠在各種環境下對oracle 進行通路，能夠高效的處理資料。
2.pl/sql結構
declare
聲名部分
begin
執行部分
exception
異常處理部分
end；
例1：輸入一個雇員号，得出該雇員号的名字，工作，薪水
declare
v_emp emp%rowtype;--聲名變量接收值
begin
--根據輸入條件，将emp表中符合條件所有列的資料into到v_emp表中
select * into v_emp from emp where empno = &輸入雇員号;
--輸出，需要||拼接
dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.sal);
end; 3.變量類型
| 類型 | 子類 | 說 明 | 範 圍 | ORACLE限制 |
| CHAR | Character String Rowid Nchar | 定長字元串 民族語言字元集 | 0à32767 可選,确省=1 | 2000 |
| VARCHAR2 | Varchar, String NVARCHAR2 | 可變字元串 | 4000 | |
| BINARY_INTEGER | 帶符号整數,為整數計算優化性能 | |||
| NUMBER(p,s) | Dec Double precision Integer Int Numeric Real Small int | 小數, NUMBER 的子類型 高精度實數 整數, NUMBER 的子類型 與NUMBER等價 整數, 比 integer 小 | ||
| LONG | 變長字元串 | 0->2147483647 | 32,767位元組 | |
| DATE | 日期型 | 公元前4712年1月1日至公元後4712年12月31日 | ||
| BOOLEAN | 布爾型 | TRUE, FALSE,NULL | 不使用 | |
| ROWID | 存放資料庫行号 | |||
| UROWID | 通用行辨別符，字元類型 | |||
3.1直接定義類型
v_job varchar2(20) --直接定義一個變長字元串類型，跟sql類型全部通用，上表列出了一些
3.2 type類型
簡單來說就是利用一個現成的表的一個列的類型
v_sal emp.sal%type --v_sal用的就是emp表的sal列一摸一樣的類型
3.3 rowtype 類型
為了友善，将一個表的所有列的類型全部用于現在定義的表中 ，就可以用這個
v_emp emp%rowtype --上面也用過
3.4記錄類型record
格式：
type 類型名 is record (
變量 資料類型,
...
);
變量名 類型名；--變量名是聲名的 類型名就是上面的記錄類型
DECLARE
type rec is record(
empno emp.empno%TYPE,
ename emp.ename%TYPE,
job varchar2(20)
);
v rec;
BEGIN
SELECT empno,ename,job INTO v FROM emp WHERE empno = &EMPNO;
dbms_output.put_line(v.empno||','||v.ename||','||v.job);
END; 4.變量指派
變量名 ：= 值 或 & 鍵盤輸入的值
例2：
v_ename emp.ename%type := 'SMITH';
v_empno emp.empno%type := &雇員号； （如果輸入是字元串的話 需要改加 引号！）
5.begin執行塊操作
begin内可以進行sql的增、删、改、查的基本操作
declare
v_emp emp%rowtype;
begin
--将emp值傳入v_emp
select * into v_emp from emp where empno = &empno;
--更新sal值，給該員工漲薪10%
update emp set sal = v_emp.sal*1.1 where empno = v_emp.empno;
--插入一個行，當然這個插入操作跟輸入的empno就沒啥關系啦
insert into emp (empno,sal) values(8888,2222);
delete from emp where empno = v_emp.empno;
dbms_output.put_line(v_emp.job||','||v_emp.sal);
end; 5.1 execute immediate
declare
--聲名一個變量儲存sql語句
v_sql varchar2(255);
--聲名一個rowtype類型變量儲存員工資訊
v emp%rowtype;
begin
v_sql:='select * from emp where empno=:1';
--列印sql語句
dbms_output.put_line(v_sql);
--使用execute immediate 執行select語句
execute immediate v_sql into v using 7369;
--列印員工資訊
dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.sal||','||v.deptno);
end; plsql中直接寫sql和使用execute immediate的差別：直接寫sql時，表名不能使用變量的值，
execute immediate:可以使用變量裡儲存的表名
如果資料庫中沒有表，那麼寫sql語句在建立存儲過程或者函數時，會直接報錯
execute immediate:中可以執行的sql語句中的表，資料庫中是可以沒有
6.流程控制語句
每種語言沒有流程控制就等于沒有靈魂，就算不上是個語言！
6.1 條件語句
1.if語句
條件判斷語句
文法：
if 條件表達式 then
plsql代碼;
end if;
條件表達式的寫法和在sql部分的條件表達式相同
語義：表示目前條件表達式成立進，執行then 和end if之間的語句
declare
--聲名一個變量
v_n number(8):=&n;
begin
if v_n >10 then
dbms_output.put_line(v_n);
end if;
end; 2.if else語句
plsql語句1;
else
plsql語句2;
語義：表示條件表達式成立時，執行第1個plsql語句，如果條件不成立執行第2個plsql語句
3.if elsif語句
if 條件表達式1 then
plsql語句;
elsif 條件表達式2 then
...
語義：表示當某個條件表達式成立時，執行相應then後面的plsql語句，如果所有條件不成立會執行else後面plsql語句,其中else部分可以省略;
declare
--聲名一個變量用來儲存從鍵盤輸出的一個成績
score number(4,1):=&成績;
begin
--當成績小于60時輸出不及格，成績在60~70之間時輸出及格，成績在70~80之間輸出良好，成績在80~90之間輸出優秀，成績>90時輸出非常好
if score<60 then
dbms_output.put_line('不及格');
elsif score<70 then
dbms_output.put_line('及格');
elsif score<80 then
dbms_output.put_line('良好');
elsif score<90 then
dbms_output.put_line('優秀');
else
dbms_output.put_line('非常好');
end if;
end; 4.case when語句
文法1：
case
when 條件表達式 then
值;
...
else
預設值;
end [case];
--該方式跟if一個樣
declare
--聲名一個變量用來儲存從鍵盤輸出的一個成績
score number(4,1):=&成績;
begin
--當成績小于60時輸出不及格，成績在60~70之間時輸出及格，成績在70~80之間輸出良好，成績在80~90之間輸出優秀，成績>90時輸出非常好
case
when score<60 then
dbms_output.put_line('不及格');
when score<70 then
dbms_output.put_line('及格');
when score<80 then
dbms_output.put_line('良好');
when score<90 then
dbms_output.put_line('優秀');
else
dbms_output.put_line('非常好');
end case;
end; 文法2：
case 表達式
when 值1 then
plsql代碼;
when 值2 then
...
else
end case;
--該種方式，表達式需要得出确切的結果才可以用,這裡瞎舉個無意義的例子
declare
v_empno emp.empno%type :=&empno;
v_ename emp.ename%type;
begin
select empno,ename into v_empno,v_ename from emp where empno = v_empno;
case v_empno
when 7369 then
dbms_output.put_line(v_ename);
when 7499 then
dbms_output.put_line(v_ename);
when 7521 then
dbms_output.put_line(v_ename);
when 7566 then
dbms_output.put_line(v_ename);
when 7788 then
dbms_output.put_line(v_ename);
end case；
end; 5.loop循環
loop
循環體語句;
exit when 退出循環條件;
循環控制語句;
end loop;
--循環列印1~9
declare
--聲名一個變量，作為循環變量
n number(2);
begin
--給循環變量指派初始值
n:=1;
loop
--循環體語句
dbms_output.put_line(n);
--退出循環條件
exit when n=9;
--循環控制語句
n:=n+1;
end loop;
end; --循環9-1
declare
--聲名一個變量，作為循環變量
n number(2);
begin
--給循環變量指派初始值
n:=9;
loop
--循環體語句
dbms_output.put_line(n);
--退出循環條件
exit when n=1;
--循環控制語句
n:=n-1;
end loop;
end; 6.while循環
while 循環條件 loop
循環體語句;
循環控制語句;
--列印1~9
declare
--聲名一個變量
n number(3);
begin
--給循環變量賦初始值
n:=1;
while n<10 loop
--循環體語句
dbms_output.put_line(n);
--循環控制語句
n:=n+1;
end loop;
end; while循環：先判斷循環條件，如果循環條件不成立就不會執行循環體，如果條件成立執行循環體和循環控制語句
7.for循環
for 循環變量 in [reverse] 集合（連續數字集合|查詢語句|遊标變量） loop
循環體語句;
循環變量：是不需要在declare中聲名的，除了周遊數字集合（循環變量就是number類型）外，其它情況下循環變量是一個記錄類型變量
數字集合的表示： 最小值..最大值
--列印1~9
begin
for i in 1..9 loop
dbms_output.put_line(i);
end loop;
end;
--列印9~1
begin
for i in reverse 1..9 loop
dbms_output.put_line(i);
end loop;
end; --列印10号門下所有員工的詳細資訊
begin
for v in (select * from emp where deptno=&dno) loop
dbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);
end loop;
end; for循環：它用來周遊一個有限的集合（數字集合，select結果集，遊标等），它不需要控制循環變量和退出循環條件,隻需要寫循環體就可以。
loop循環：退出循環條件,先執行循環體後判斷退出條件
while循環：循環條件，先判斷循環條件後執行循環體
for循環：不需要聲名循環變量，不需要去控制循環條件和循環控制語句，周遊一個有限集合
1.編寫一個程式塊，從emp表中顯示名為“SMITH”的雇員的薪水和職位
DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE lower(ename) = 'smith';
dbms_output.put_line(v_emp.sal||','||v_emp.job);
END;
2.編寫一個程式塊，接受使用者輸入一個部門号，從dept表中顯示該部門的名稱與所在位置
DECLARE
vdeptno dept.deptno%TYPE := &部門号;
vloc dept.loc%TYPE;
vdname dept.dname%TYPE;
BEGIN
SELECT loc,dname INTO vloc,vdname FROM dept WHERE deptno = vdeptno;
dbms_output.put_line(vdname||','||vloc);
END;
3.編寫一個程式塊，利用%type屬性，接受一個雇員号，從emp表中顯示該雇員的整體薪水(即，薪水加傭金)
DECLARE
vempno emp.empno%TYPE := &雇員号;
vsal emp.sal%TYPE;
BEGIN
SELECT sal+NVL(comm,0) INTO vsal FROM emp WHERE empno = vempno;
dbms_output.put_line(vsal);
END;
4.編寫一個程式塊，利用%rowtype屬性，接受一個雇員号，從emp表中顯示該雇員的整體薪水
DECLARE
vemp emp%ROWTYPE;
wole emp.sal%TYPE;
BEGIN
SELECT * INTO vemp FROM emp WHERE empno = &雇員号;
wole := vemp.sal+nvl(vemp.comm,0);
dbms_output.put_line(wole);
END;
5.某公司要根據雇員的職位來加薪，公司決定按下列加薪結構處理：
Designation Raise
------------ --------
clerk 500
salseman 1000
analyst 1500
otherwise 2000
編寫一個程式塊，接受一個雇員名，從emp表中實作上述加薪處理
--
DECLARE
vemp emp%ROWTYPE;
BEGIN
SELECT * INTO vemp FROM emp WHERE ename='&ename';
IF lower(vemp.job) = 'clerk' THEN
UPDATE emp SET sal=sal+500 WHERE ename = vemp.ename;
ELSIF lower(vemp.job)= 'salseman' THEN
UPDATE emp SET sal=sal+1000 WHERE ename = vemp.ename;
ELSIF lower(vemp.job)= 'analyst' THEN
UPDATE emp SET sal=sal+1500 WHERE ename = vemp.ename;
ELSE
UPDATE emp SET sal=sal+2000 WHERE ename = vemp.ename ;
END IF;
END;
----
BEGIN
FOR i IN (SELECT * FROM emp) LOOP
IF lower(i.job) = 'clerk' THEN
UPDATE emp SET sal=sal+500 WHERE empno = i.empno;
ELSIF lower(i.job)= 'salseman' THEN
UPDATE emp SET sal=sal+1000 WHERE empno = i.empno;
ELSIF lower(i.job)= 'analyst' THEN
UPDATE emp SET sal=sal+1500 WHERE empno = i.empno;
ELSE
UPDATE emp SET sal=sal+2000 WHERE empno = i.empno;
END IF;
END LOOP;
END;
6.編寫一個程式塊，将emp表中雇員名全部顯示出來
BEGIN
FOR I IN (SELECT * FROM EMP ) LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
END;
7.編寫一個程式塊，将emp表中前5人的名字顯示出來
---1
BEGIN
FOR I IN (SELECT * FROM emp WHERE ROWNUM <=5) LOOP
dbms_output.put_line(I.ename);
END LOOP;
END;
----2
DECLARE
N NUMBER(2):=0;
BEGIN
FOR I IN (SELECT * FROM emp) LOOP
dbms_output.put_line(I.ename);
N:=N+1;
IF N =5 THEN
RETURN;
END IF;
END LOOP;
END;
8.接受兩個數相除并且顯示結果，如果第二個數為0，則顯示消息“除數不能為0”
DECLARE
A FLOAT(20):=&輸入被除數;
B FLOAT(20):=&輸入除數;
C FLOAT(50);
BEGIN
IF B =0 THEN
dbms_output.put_line('除數不能為0');
RETURN;
ELSE
C:=A/B;
DBMS_OUTPUT.PUT_LINE(C);
END IF;
END;
9、計算下面級數當末項小于0.001時的部分和。
1/(1*2)+1/(2*3)+1/(3*4)+…+1/(n*(n+1))+ ……
DECLARE
n NUMBER(20):=1;
poi FLOAT(20):=0;
BEGIN
LOOP
poi := poi+1/(n*(n+1));
EXIT WHEN 1/(n*(n+1))<0.001;
n:=n+1;
END LOOP;
dbms_output.put_line(poi);
END;
10、計算s=1*2+2*3+…+N*(N+1),當N=50的值。
DECLARE
s NUMBER :=0;
n NUMBER :=1;
BEGIN
LOOP
s := s+N*(n+1);
EXIT WHEN n = 50;
n:=n+1;
END LOOP;
dbms_output.put_line(s);
END;
11.編寫一個PL/SQL程式塊，從emp表中對名字以“A”或"S"開始的所有雇員按他們基本薪水的10%給他們加薪
DECLARE
vemp emp%ROWTYPE;
BEGIN
FOR i IN (SELECT * INTO vemp FROM emp WHERE ename LIKE 'A%' OR ename LIKE 'S%') LOOP
UPDATE emp SET sal = sal+sal*0.01;
END LOOP;
END;
12、兩重循環，計算S=1!+2!+…+10!。
--------for
DECLARE
s NUMBER :=0;
n NUMBER :=1;
BEGIN
FOR i IN 1..10 LOOP
n:=1;---初始值需要更改為1
FOR j IN 1..i LOOP
n:=n*j;
END LOOP;
s:=s+n;
END LOOP;
dbms_output.put_line(s);
END;
-------loop
DECLARE
s NUMBER:=0;
n NUMBER:=1;
x NUMBER:=1;
BEGIN
LOOP
x:=x*n;
s:=s+x;
EXIT WHEN n=10;
n:=n+1;
END LOOP;
dbms_output.put_line(s);
END;
--------while
DECLARE
s NUMBER:=0;
n NUMBER:=1;
x NUMBER:=1;
BEGIN
WHILE n<=10 LOOP
x:=x*n;
s:=s+x;
n:=n+1;
END LOOP;
dbms_output.put_line(s);
END;
13.程式設計式求滿足不等式 1+3^2+5^2+…+N^2>2000的最小N值。
DECLARE
n NUMBER:=1;
s NUMBER:=1;
m NUMBER:=1;
BEGIN
LOOP
s := s+n*n;
EXIT WHEN s>2000;
n:=n+2;
m:=m+1;
END LOOP;
dbms_output.put_line(m);
END;
14.将雇員表中的所有工資小于3000增加400，統計出增加工資的人數及增加的工資數量。
DECLARE
vemp emp%ROWTYPE;
m NUMBER:=0;
total NUMBER :=0;
BEGIN
FOR i IN (SELECT * INTO vemp FROM emp WHERE sal<3000) LOOP
i.sal := i.sal+400;
UPDATE emp SET sal = i.sal WHERE ename = i.ename;
dbms_output.put_line(i.ename||','||i.sal);
m:=m+1;
total:=total+400;
END LOOP;
dbms_output.put_line('增加工資總人數為：'||m);
dbms_output.put_line('增加總工資為：'||total);
END;
------------------
DECLARE
n NUMBER(10):=0;
BEGIN
FOR i IN (SELECT * FROM emp WHERE sal<3000) LOOP
UPDATE emp SET sal = sal+400 WHERE empno = i.empno;
n:=n+1;
END LOOP;
dbms_output.put_line('人數：'||n||',總工資'||n*400);
END;
15.從雇員表中顯示工資最高的前五個人的姓名，部門和工資。
DECLARE
vemp emp%ROWTYPE;
BEGIN
FOR i IN (SELECT * INTO vemp FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM<=5) LOOP
dbms_output.put_line(i.ename||','||i.deptno||','||i.sal);
END LOOP;
END;
1-鍵盤接入兩個值，列印比較大的值
DECLARE
a NUMBER:=&a;
b NUMBER :=&b;
BEGIN
IF a > b THEN
dbms_output.put_line(a);
ELSE
dbms_output.put_line(b);
END IF ;
END ;
2-鍵盤介入三個值，并按照從大到小依次列印
DECLARE
a NUMBER :=&a;
b NUMBER :=&b;
c NUMBER :=&c;
BEGIN
IF a>b AND b>c THEN
dbms_output.put_line(a||'>'||b||'>'||c);
ELSIF a>c AND c>b THEN
dbms_output.put_line(a||'>'||c||'>'||b);
ELSIF b>a AND a>c THEN
dbms_output.put_line(b||'>'||a||'>'||c);
ELSIF b>c AND c>a THEN
dbms_output.put_line(b||'>'||c||'>'||a);
ELSIF c>a AND a>b THEN
dbms_output.put_line(c||'>'||a||'>'||b);
ELSIF c>b AND b>a THEN
dbms_output.put_line(c||'>'||b||'>'||a);
END IF;
END ;
4-判斷一個年份是不是閏年
DECLARE
YEAR NUMBER(4):=&年份;
BEGIN
IF mod(YEAR,4)=0 THEN
IF MOD(YEAR ,100) !=0 THEN
dbms_output.put_line(YEAR||'是閏年');
ELSIF MOD(YEAR,400)=0 THEN
dbms_output.put_line(YEAR||'是閏年');
ELSE
dbms_output.put_line(YEAR||'是平年');
END IF;
ELSE
dbms_output.put_line(YEAR||'是平年');
END IF;
END;
-------------------------
DECLARE
YEAR NUMBER(4) :=&年份;
BEGIN
IF MOD(YEAR,4)=0 AND MOD(YEAR,100)!=0 OR MOD(YEAR,100)=0 AND MOD(YEAR,400)=0 THEN
dbms_output.put_line(YEAR||'是閏年');
ELSE
dbms_output.put_line(YEAR||'是平年');
END IF;
END;
5-體質指數（BMI）=體重（kg）÷身高^2（m）
偏瘦 <= 18.4
正常 18.5 ~ 23.9
過重 24.0 ~ 27.9
肥胖 >= 28.0
現要求輸入體重和身高，求出體質指數所在範圍
DECLARE
hight NUMBER:=&身高米;
weight NUMBER:=&體重千克;
bmi NUMBER;
BEGIN
bmi:=weight/(hight*hight);
dbms_output.put_line(bmi);
IF bmi<=18.4 THEN
dbms_output.put_line('偏瘦');
ELSIF bmi<=23.9 THEN
dbms_output.put_line('正常');
ELSIF bmi<=27.9 THEN
dbms_output.put_line('過重');
ELSE
dbms_output.put_line('肥胖');
END IF;
END;
6-輸入一個數，判斷是奇數還是偶數
DECLARE
a NUMBER:=&整數;
BEGIN
IF MOD(a,2) = 0 THEN
dbms_output.put_line(a||'是偶數');
else
dbms_output.put_line(a||'是奇數');
END IF;
END;
7-隻含有一個未知數（一進制），并且未知數項的最高次數是2（二次）的整式
方程叫做一進制二次方程。标準形式為：ax2+bx+c=0（a≠0）。
現輸入a b c三個值，求一進制二次方程的解
注：平方根函數為 SQRT
DECLARE
a NUMBER(5,1):=&a;
b NUMBER(5,1):=&b;
c NUMBER(5,1):=&c;
x1 NUMBER(10,3);
x2 NUMBER(10,3);
BEGIN
x1:=(-b+SQRT(b*b-4*a*c))/(2*a);
x2:=(-b-SQRT(b*b-4*a*c))/(2*a);
IF x1=x2 THEN
dbms_output.put_line('x1='||x1);
ELSE
dbms_output.put_line('x1='||x1||','||'x2='||x2);
END IF;
END;
8-99乘法表
----1..9
DECLARE
n NUMBER:=1;
s NUMBER:=0;
x NUMBER;
BEGIN
FOR i IN 1..9 LOOP
n:=i;
FOR j IN 1..i LOOP
x:=n*j;
s:=x;
dbms_output.put(j||'*'||n||'='||s||CHR(9));
END LOOP;
dbms_output.new_line();
END LOOP;
END;
---9..1
DECLARE
n NUMBER:=1;
s NUMBER:=0;
x NUMBER;
BEGIN
FOR i IN REVERSE 1..9 LOOP
n:=i;
FOR j IN i..9 LOOP
x:=n*j;
s:=x;
dbms_output.put(j||'*'||n||'='||s||' ');
END LOOP;
dbms_output.new_line();
END LOOP;
END;
-------
DECLARE
n NUMBER:=1;
s NUMBER:=0;
x NUMBER;
BEGIN
FOR i IN REVERSE 1..9 LOOP
n:=i;
FOR j IN 1..i LOOP
x:=n*j;
s:=x;
dbms_output.put(j||'*'||n||'='||s||' ');
END LOOP;
dbms_output.new_line();
END LOOP;
END;
------------
DECLARE
n NUMBER:=1;
s NUMBER:=0;
x NUMBER;
BEGIN
FOR i IN REVERSE 1..9 LOOP
n:=i;
FOR j IN REVERSE 1..i LOOP
x:=n*j;
s:=x;
dbms_output.put(j||'*'||n||'='||s||' ');
END LOOP;
dbms_output.new_line();
END LOOP;
END; 控制語句練習
7.遊标
遊标比較簡單，主要就是定義cursor 然後在begin中open 遊标，用fetch into 傳個值，再用控制語句做一些事，最後close一下就好了
cursor 遊标名 is select 語句；
變量 --聲名接收遊标值
open 遊标名；
循環語句
fetch 遊标 into 變量；--這個根據循環語句的不同變換位置
結束循環；
close 遊标
1、定義遊标：列出每個員工的姓名、部門名稱并程式設計顯示第10個到第20個記錄。
---loop-----------
DECLARE
--定義cursor
CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
--定義接收變量
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;
BEGIN
--open 遊标
OPEN cur;
--循環控制
LOOP
--導入變量
FETCH cur INTO v_ename,v_dname;
--控制器
EXIT WHEN cur%NOTFOUND;
--循環體
dbms_output.put_line(v_ename||'-'||v_dname);
--結束循環
END LOOP;
--close cursor
CLOSE cur;
END;
------------------while------------------------
DECLARE
CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;
BEGIN
OPEN cur;
FETCH cur INTO v_ename,v_dname; --讓cur%found識别第一條記錄
WHILE cur%FOUND LOOP
dbms_output.put_line(v_ename||'-'||v_dname);
FETCH cur INTO v_ename,v_dname;---需要放在下面
END LOOP;
CLOSE cur;
END;
------------------for -------------------------------
DECLARE
CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
BEGIN----for循環不用開啟遊标，直接循環
FOR i IN cur LOOP
dbms_output.put_line(i.ename||'-'||i.dname);
END LOOP;
END;
2、定義遊标：從雇員表中顯示工資大于3000的記錄，隻要姓名、部門編号和工資。程式設計顯示其中的奇數記錄。
--------------loop---------------
DECLARE
--定義遊标
CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
--定義變量
v_emp emp%ROWTYPE;
BEGIN
--打開遊标
OPEN cur;
---先傳cur%rowcount初始值
FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;
--循環
LOOP
FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;---給變量傳值
IF mod(cur%ROWCOUNT,2)=1 THEN --判斷為奇數時列印
dbms_output.put_line(v_emp.ename||','||v_emp.deptno||','||v_emp.sal);
END IF;
EXIT WHEN cur%NOTFOUND;---找不到時結束
END LOOP;
--關閉遊标
CLOSE cur;
END;
-------------while----------------
DECLARE
CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
v_emp emp%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;---賦予cur%rowcount初始值
WHILE cur%FOUND LOOP
FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;
IF mod(cur%ROWCOUNT,2)=1 THEN --------判斷奇數
dbms_output.put_line(v_emp.ename||','||v_emp.deptno||','||v_emp.sal);
END IF;
END LOOP;
CLOSE cur;
END;
-------------for--------------
DECLARE
CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
v NUMBER(2):=1;
BEGIN
FOR i IN cur LOOP
IF MOD(v,2)=1 THEN -------判斷奇數
dbms_output.put_line(i.ename||','||i.deptno||','||i.sal);
v:=v+1;
END IF;
END LOOP;
END;
3、用遊标顯示所有部門編号與名稱，以及其所擁有的員勞工數。
---------------------loop-----------------
DECLARE
--定義cursor
CURSOR cur IS SELECT d.deptno,dname,COUNT(*) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,dname;
--定義變量
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
v_count NUMBER(10);
BEGIN
--打開遊标
OPEN cur;
FETCH cur INTO v_deptno,v_dname,v_count;
LOOP
dbms_output.put_line(v_deptno||','||v_dname||','||v_count);
FETCH cur INTO v_deptno,v_dname,v_count;
EXIT WHEN cur%NOTFOUND;
END LOOP;
--關閉遊标
CLOSE cur;
END;
--------------------------------while-----------------------
DECLARE
--定義cursor
CURSOR cur IS SELECT d.deptno,dname,COUNT(*) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,dname;
--定義變量
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
v_count NUMBER(10);
BEGIN
--打開遊标
OPEN cur;
FETCH cur INTO v_deptno,v_dname,v_count;
WHILE cur%FOUND LOOP
dbms_output.put_line(v_deptno||','||v_dname||','||v_count);
FETCH cur INTO v_deptno,v_dname,v_count;
END LOOP;
--關閉遊标
CLOSE cur;
END;
4、用遊标屬性%rowcount實作輸出前十個員工的資訊
--------------loop-----------------
DECLARE
--定義遊标
CURSOR cur IS SELECT * FROM emp;
vemp emp%ROWTYPE;
BEGIN
--打開遊标
OPEN cur;
--循環
LOOP
--傳值
FETCH cur INTO vemp;
IF cur%ROWCOUNT<=10 THEN ---判斷前十個并輸出
dbms_output.put_line(vemp.ename||','||vemp.empno||','||vemp.deptno||','||vemp.sal||','||vemp.comm);
END IF;
EXIT WHEN cur%NOTFOUND;
END LOOP;
--關閉遊标
CLOSE cur;
END;
-------------------------------while--------------------------
DECLARE
--定義遊标
CURSOR cur IS SELECT * FROM emp;
vemp emp%ROWTYPE;
BEGIN
--打開遊标
OPEN cur;
--第一次傳值保證while 後判斷條件有值
FETCH cur INTO vemp;
--循環
WHILE cur%FOUND loop
IF cur%ROWCOUNT<=10 THEN ---判斷前十個并輸出
dbms_output.put_line(vemp.ename||','||vemp.empno||','||vemp.deptno||','||vemp.sal||','||vemp.comm);
END IF;
FETCH cur INTO vemp; -----while循環下必須要将傳值放在後面，讓第一次的遊标輸出
END LOOP;
--關閉遊标
CLOSE cur;
END;
--------------------------for--------------------------
DECLARE
CURSOR cur IS SELECT ename,empno,sal FROM emp;
BEGIN
OPEN cur;
fe
FOR i IN cur LOOP
dbms_output.put_line(i.ename||','||i.empno||','||i.sal);
END LOOP;
END;
5、通過使用遊标來顯示dept表中的部門名稱，及其相應的員工清單（提示：可以使用雙重循環）。
DECLARE
CURSOR cur IS SELECT dname,e.ename FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno;
BEGIN
FOR i IN cur LOOP
dbms_output.put_line(i.dname||','||i.ename);
END LOOP;
END;
6、接受一個部門号，使用For循環，從emp表中顯示該部門的所有雇員的姓名，工作和薪水。
----------------------for---------------------------
DECLARE
CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部門号;
BEGIN
FOR i IN cur LOOP
dbms_output.put_line(i.ename||','||i.job||','||i.sal);
END LOOP;
END;
-----------------------loop-----------------------
DECLARE
CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部門号;
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(vemp.ename||','||vemp.job||','||vemp.sal);
END LOOP;
CLOSE cur;
END;
-----------------------while--------------------------
DECLARE
CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部門号;
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
WHILE cur%FOUND LOOP
dbms_output.put_line(vemp.ename||','||vemp.job||','||vemp.sal);
FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
END LOOP;
CLOSE cur;
END;
7、編寫一個程式塊，将emp表中前5人的名字，及其出的工資等級（salgrade）顯示出來。
----------------loop----------------
DECLARE
CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
vname emp.ename%TYPE;
vgrade salgrade.grade%TYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vname,vgrade;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(vname||','||vgrade);
END LOOP;
CLOSE cur;
END;
----------------while----------------
DECLARE
CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
vname emp.ename%TYPE;
vgrade salgrade.grade%TYPE;
BEGIN
OPEN cur;
FETCH cur INTO vname,vgrade;
WHILE cur%FOUND LOOP
dbms_output.put_line(vname||','||vgrade);
FETCH cur INTO vname,vgrade;
END LOOP;
CLOSE cur;
END;
-----------------for————————————————
DECLARE
CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
BEGIN
FOR i IN cur LOOP
dbms_output.put_line(i.ename||','||i.grade);
END LOOP;
END;
-------------loop2 ---------------其他同樣可以用此種方法，不多寫了
DECLARE
CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal;
vname emp.ename%TYPE;
vgrade salgrade.grade%TYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vname,vgrade;
EXIT WHEN cur%NOTFOUND;
IF cur%ROWCOUNT<=5 THEN ----------------其他同樣可以用此種方法
dbms_output.put_line(vname||','||vgrade);
END IF;
END LOOP;
CLOSE cur;
END;
8.emp表中對所有雇員按他們基本薪水的10%給他們加薪，如果所增加後的薪水大于5000，則取消加薪。
---------------------loop-------------------
DECLARE
CURSOR cur IS SELECT empno,ename,sal FROM emp;
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
EXIT WHEN cur%NOTFOUND;
--判斷加10%之後是否超過5000，如果超過5000，則把元值賦給sal，否則加上基礎薪資的10%
IF (vemp.sal+vemp.sal*0.1)>5000 THEN
UPDATE emp SET sal = vemp.sal WHERE empno = vemp.empno;
ELSE
UPDATE emp SET sal = vemp.sal+vemp.sal*0.1 WHERE empno = vemp.empno;
END IF;
END LOOP;
CLOSE cur;
END;
---------------------------------while------------------------
DECLARE
CURSOR cur IS SELECT empno,ename,sal FROM emp;
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
WHILE cur%FOUND LOOP
--判斷加10%之後是否超過5000，如果超過5000，則把元值賦給sal，否則加上基礎薪資的10%
IF (vemp.sal+vemp.sal*0.1)>5000 THEN
UPDATE emp SET sal = vemp.sal WHERE empno = vemp.empno;
ELSE
UPDATE emp SET sal = vemp.sal+vemp.sal*0.1 WHERE empno = vemp.empno;
END IF;
FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
END LOOP;
CLOSE cur;
END;
-----------------------for --------------------------------
DECLARE
CURSOR cur IS SELECT empno,ename,sal FROM emp;
vemp emp%ROWTYPE;
BEGIN
FOR i IN cur LOOP
--判斷加10%之後是否超過5000，如果超過5000，則把元值賦給sal，否則加上基礎薪資的10%
IF (i.sal+i.sal*0.1)>5000 THEN
UPDATE emp SET sal = i.sal WHERE empno = i.empno;
ELSE
UPDATE emp SET sal = i.sal+i.sal*0.1 WHERE empno = i.empno;
END IF;
END LOOP;
END;
9.按照salgrade表中的标準，給員工加薪，1：5%，2：4%，3：3%，4：2%，5：1%，并列印輸出每個人，加薪前後的工資。
-----------------------loop--------------------其他都一樣 不多寫了
DECLARE
CURSOR cur IS SELECT empno,grade,sal FROM emp e,salgrade s WHERE e.sal BETWEEN losal AND hisal;
vemp emp%ROWTYPE;
vsal salgrade%ROWTYPE;
newsal emp.sal%TYPE; --聲明一個newsal能接收新的sal值
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vemp.empno,vsal.grade,vemp.sal;
EXIT WHEN cur%NOTFOUND;
IF vsal.grade = 1 THEN -----判讀每種等級改增加多少
newsal:=vemp.sal+vemp.sal*0.05;---------------通過newsal來提供輸出的新sal
ELSIF vsal.grade = 2 THEN
newsal:=vemp.sal+vemp.sal*0.04;
ELSIF vsal.grade = 3 THEN
newsal:=vemp.sal+vemp.sal*0.03;
ELSIF vsal.grade = 4 THEN
newsal:=vemp.sal+vemp.sal*0.02;
ELSIF vsal.grade = 5 THEN
newsal:=vemp.sal+vemp.sal*0.01;
END IF;
UPDATE emp SET sal = newsal WHERE empno = vemp.empno;
dbms_output.put_line(vemp.sal||','||newsal);
END LOOP;
CLOSE cur;
END;
10、用遊标擷取所有收入超過2000的 salesman.
-------------------------loop---------------1
DECLARE
CURSOR cur IS SELECT ename,job,sal FROM emp WHERE job = 'SALESMAN' AND sal+NVL(comm,0)>2000;
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(vemp.ename||','||vemp.job||','||vemp.sal);
END LOOP;
CLOSE cur;
END;
-----------------------------while -----------------2
DECLARE
CURSOR cur IS SELECT ename,job,sal,comm FROM emp WHERE job = 'SALESMAN';
vemp emp%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO vemp.ename,vemp.job,vemp.sal,vemp.comm;
WHILE cur%FOUND LOOP
IF vemp.sal+NVL(vemp.comm,0)>2000 THEN
dbms_output.put_line(vemp.ename||','||vemp.job||','||vemp.sal);
END IF;
FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
END LOOP;
CLOSE cur;
END; 遊标練習