Cursor如何在 Oracle中使用

135次閱讀

共計 16760 個字符，預計需要花費 42 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

今天就跟大家聊聊有關 Cursor 如何在 Oracle 中使用，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

查詢

SELECT 語句用于從數據庫中查詢數據，當在 PL/SQL 中使用 SELECT 語句時，要與 INTO 子句一起使用，查詢的
返回值被賦予 INTO 子句中的變量，變量的聲明是在 DELCARE 中。SELECT INTO 語法如下：

SELECT [DISTICT|ALL]{*|column[,column,...]}
 INTO (variable[,variable,...] |record)
 FROM {table|(sub-query)}[alias]
 WHERE............

PL/SQL 中 SELECT 語句只返回一行數據。如果超過一行數據，那么就要使用顯式游標（對游標的討論我們將
在后面進行），INTO 子句中要有與 SELECT 子句中相同列數量的變量。INTO 子句中也可以是記錄變量。

%TYPE 屬性

在 PL/SQL 中可以將變量和常量聲明為內建或用戶定義的數據類型，以引用一個列名，同時繼承他的數據類
型和大小。這種動態賦值方法是非常有用的，比如變量引用的列的數據類型和大小改變了，如果使用了 %TYPE,
那么用戶就不必修改代碼，否則就必須修改代碼。

例：

v_empno SCOTT.EMP.EMPNO%TYPE;
 v_salary EMP.SALARY%TYPE;

不但列名可以使用 %TYPE, 而且變量、游標、記錄，或聲明的常量都可以使用 %TYPE。這對于定義相同數據類
型的變量非常有用。

DELCARE
 V_A NUMBER(5):=10;
 V_B V_A%TYPE:=15;
 V_C V_A%TYPE;
 BEGIN
 DBMS_OUTPUT.PUT_LINE
 (V_A= ||V_A|| V_B= ||V_B|| V_C= ||V_C);
 SQL /
 V_A=10 V_B=15 V_C=
 PL/SQL procedure successfully completed.
 SQL

其他 DML 語句

其它操作數據的 DML 語句是:INSERT、UPDATE、DELETE 和 LOCK TABLE, 這些語句在 PL/SQL 中的語法與在 SQL 中
的語法相同。我們在前面已經討

論過 DML 語句的使用這里就不再重復了。在 DML 語句中可以使用任何在 DECLARE 部分聲明的變量，如果是嵌套
塊，那么要注意變量的作用范圍。

例：

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
 v_ename EMP.ENAME%TYPE;
 BEGIN
 SELECT ename INTO v_ename
 FROM emp
 WHERE empno=p_empno;
 INSERT INTO FORMER_EMP(EMPNO,ENAME)
 VALUES (p_empno,v_ename);
 DELETE FROM emp
 WHERE empno=p_empno;
 UPDATE former_emp
 SET date_deleted=SYSDATE
 WHERE empno=p_empno;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE( Employee Number Not Found! 
 END

DML 語句的結果

當執行一條 DML 語句后，DML 語句的結果保存在四個游標屬性中，這些屬性用于控制程序流程或者了解程序
的狀態。當運行 DML 語句時，PL/SQL 打開一個內建游標并處理結果，游標是維護查詢結果的內存中的一個區域，
游標在運行 DML 語句時打開，完成后關閉。隱式游標只使用 SQL%FOUND,

SQL%NOTFOUND,SQL%ROWCOUNT 三個屬性.SQL%FOUND,SQL%NOTFOUND 是布爾值，SQL%ROWCOUNT 是整數值。

SQL%FOUND 和 SQL%NOTFOUND

在執行任何 DML 語句前 SQL%FOUND 和 SQL%NOTFOUND 的值都是 NULL, 在執行 DML 語句后，SQL%FOUND 的屬性值將是：

. TRUE :INSERT
. TRUE :DELETE 和 UPDATE，至少有一行被 DELETE 或 UPDATE.
. TRUE :SELECT INTO 至少返回一行

當 SQL%FOUND 為 TRUE 時,SQL%NOTFOUND 為 FALSE。

SQL%ROWCOUNT

在執行任何 DML 語句之前，SQL%ROWCOUNT 的值都是 NULL, 對于 SELECT INTO 語句，如果執行成功
，SQL%ROWCOUNT 的值為 1, 如果沒有成功，SQL%ROWCOUNT 的值為 0，同時產生一個異常 NO_DATA_FOUND.

SQL%ISOPEN

SQL%ISOPEN 是一個布爾值，如果游標打開，則為 TRUE, 如果游標關閉，則為 FALSE. 對于隱式游標而言 SQL%
ISOPEN 總是 FALSE，這是因為隱式游

標在 DML 語句執行時打開，結束時就立即關閉。

事務控制語句

事務是一個工作的邏輯單元可以包括一個或多個 DML 語句，事物控制幫助用戶保證數據的一致性。如果事務控制邏輯單元中的任何一個 DML
語句失敗，那么整個事務都將回滾，在 PL/SQL 中用戶可以明確地使用 COMMIT、ROLLBACK、SAVEPOINT 以及
SET TRANSACTION 語句。

COMMIT 語句終止事務，永久保存數據庫的變化，同時釋放所有 LOCK,ROLLBACK 終止現行事務釋放所有 LOCK，
但不保存數據庫的任何變化,SAVEPOI

NT 用于設置中間點，當事務調用過多的數據庫操作時，中間點是非常有用的，SET TRANSACTION 用于設置事
務屬性，比如 read-write 和隔離級等。

顯式游標

查詢返回結果超過一行時，就需要一個顯式游標，此時用戶不能使用 select into 語句。PL/SQL 管理隱式
游標，當查詢開始時隱式游標打開，查詢結束時隱式游標自動關閉。顯式游標在 PL/SQL 塊的聲明部分聲明，在執行部分或異常處理部分打開，取數據, 關閉。

使用游標

這里要做一個聲明，我們所說的游標通常是指顯式游標，因此從現在起沒有特別指明的情況，我們所說的
游標都是指顯式游標。要在程序中使用游標，必須首先聲明游標。

聲明游標

語法：

CURSOR cursor_name IS select_statement;

在 PL/SQL 中游標名是一個未聲明變量，不能給游標名賦值或用于表達式中。

例：

DELCARE
 CURSOR C_EMP IS SELECT empno,ename,salary
 FROM emp
 WHERE salary 2000
 ORDER BY ename;
 ........
 BEGIN

在游標定義中 SELECT 語句中不一定非要表可以是視圖，也可以從多個表或視圖中選擇的列，甚至可以使用 *
來選擇所有的列。

打開游標

使用游標中的值之前應該首先打開游標，打開游標初始化查詢處理。打開游標的語法是：

OPEN cursor_name

cursor_name 是在聲明部分定義的游標名。

例：

OPEN C_EMP;

關閉游標

語法：

CLOSE cursor_name

例：

CLOSE C_EMP;

從游標提取數據

從游標得到一行數據使用 FETCH 命令。每一次提取數據后，游標都指向結果集的下一行。語法如下：

FETCH cursor_name INTO variable[,variable,…]

對于 SELECT 定義的游標的每一列，FETCH 變量列表都應該有一個變量與之相對應，變量的類型也要相同。

例：

SET SERVERIUTPUT ON
 DECLARE
 v_ename EMP.ENAME%TYPE;
 v_salary EMP.SALARY%TYPE;
 CURSOR c_emp IS SELECT ename,salary FROM emp;
 BEGIN
 OPEN c_emp;
 FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(Salary of Employee || v_ename || is || v_salary);
 FETCH c_emp INTO v_ename,v_salary;
 DBMS_OUTPUT.PUT_LINE(Salary of Employee || v_ename || is || v_salary);
 FETCH c_emp INTO v_ename,v_salary;
 DBMS_OUTPUT.PUT_LINE(Salary of Employee || v_ename || is || v_salary);
 CLOSE c_emp;
 END

這段代碼無疑是非常麻煩的，如果有多行返回結果，可以使用循環并用游標屬性為結束循環的條件，以這
種方式提取數據，程序的可讀性和簡潔性都大為提高，下面我們使用循環重新寫上面的程序：

SET SERVERIUTPUT ON
 DECLARE
 v_ename EMP.ENAME%TYPE;
 v_salary EMP.SALARY%TYPE;
 CURSOR c_emp IS SELECT ename,salary FROM emp;
 BEGIN
 OPEN c_emp;
 LOOP
 FETCH c_emp INTO v_ename,v_salary;
 EXIT WHEN c_emp%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(Salary of Employee || v_ename || is || v_salary);
 END

記錄變量

定義一個記錄變量使用 TYPE 命令和 %ROWTYPE，關于 %ROWsTYPE 的更多信息請參閱相關資料。

記錄變量用于從游標中提取數據行，當游標選擇很多列的時候，那么使用記錄比為每列聲明一個變量要方
便得多。

當在表上使用 %ROWTYPE 并將從游標中取出的值放入記錄中時，如果要選擇表中所有列，那么在 SELECT 子句
中使用 * 比將所有列名列出來要安全得多。

例：

SET SERVERIUTPUT ON
 DECLARE
 R_emp EMP%ROWTYPE;
 CURSOR c_emp IS SELECT * FROM emp;
 BEGIN
 OPEN c_emp;
 LOOP
 FETCH c_emp INTO r_emp;
 EXIT WHEN c_emp%NOTFOUND;
 DBMS_OUT.PUT.PUT_LINE(Salary of Employee ||r_emp.ename|| is || r_emp.salary);
 END LOOP;
 CLOSE c_emp;
 END;

%ROWTYPE 也可以用游標名來定義，這樣的話就必須要首先聲明游標：

SET SERVERIUTPUT ON
 DECLARE
 CURSOR c_emp IS SELECT ename,salary FROM emp;
 R_emp c_emp%ROWTYPE;
 BEGIN
 OPEN c_emp;
 LOOP
 FETCH c_emp INTO r_emp;
 EXIT WHEN c_emp%NOTFOUND;
 DBMS_OUT.PUT.PUT_LINE(Salary of Employee ||r_emp.ename|| is || r_emp.salary);
 END LOOP;
 CLOSE c_emp;
 END;

帶參數的游標

與存儲過程和函數相似，可以將參數傳遞給游標并在查詢中使用。這對于處理在某種條件下打開游標的情
況非常有用。它的語法如下：

CURSOR cursor_name[(parameter[,parameter],…)] IS select_statement;

定義參數的語法如下：

Parameter_name [IN] data_type[{:=|DEFAULT} value]

與存儲過程不同的是，游標只能接受傳遞的值，而不能返回值。參數只定義數據類型，沒有大小。

另外可以給參數設定一個缺省值，當沒有參數值傳遞給游標時，就使用缺省值。游標中定義的參數只是一個占位符，在別處引用該參數不一定可靠。

在打開游標時給參數賦值，語法如下：

OPEN cursor_name[value[,value]….];

參數值可以是文字或變量。

例：

DECALRE
 CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
 CURSOR c_emp (p_dept VARACHAR2) IS
 SELECT ename,salary
 FROM emp
 WHERE deptno=p_dept
 ORDER BY ename
 r_dept DEPT%ROWTYPE;
 v_ename EMP.ENAME%TYPE;
 v_salary EMP.SALARY%TYPE;
 v_tot_salary EMP.SALARY%TYPE;
 BEGIN
 OPEN c_dept;
 LOOP
 FETCH c_dept INTO r_dept;
 EXIT WHEN c_dept%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(Department: || r_dept.deptno|| - ||r_dept.dname);
 v_tot_salary:=0;
 OPEN c_emp(r_dept.deptno);
 LOOP
 FETCH c_emp INTO v_ename,v_salary;
 EXIT WHEN c_emp%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(Name: || v_ename||  salary: ||v_salary);
 v_tot_salary:=v_tot_salary+v_salary;
 END LOOP;
 CLOSE c_emp;
 DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept: || v_tot_salary);
 END LOOP;
 CLOSE c_dept;
 END;

游標 FOR 循環

在大多數時候我們在設計程序的時候都遵循下面的步驟：

1、打開游標
2、開始循環
3、從游標中取值
4、檢查那一行被返回
5、處理
6、關閉循環
7、關閉游標

可以簡單的把這一類代碼稱為游標用于循環。但還有一種循環與這種類型不相同，這就是 FOR 循環，用于
FOR 循環的游標按照正常的聲明方式聲明，它的優點在于不需要顯式的打開、關閉、取數據，測試數據的存在、定義存放數據的變量等等。

游標 FOR 循環的語法如下：

FOR record_name IN
 (corsor_name[(parameter[,parameter]...)]
 | (query_difinition)
 LOOP
 statements
 END LOOP;

下面我們用 for 循環重寫上面的例子：

DECALRE
 CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
 CURSOR c_emp (p_dept VARACHAR2) IS
 SELECT ename,salary
 FROM emp
 WHERE deptno=p_dept
 ORDER BY ename
 v_tot_salary EMP.SALARY%TYPE;
BEGIN
 FOR r_dept IN c_dept LOOP
 DBMS_OUTPUT.PUT_LINE(Department: || r_dept.deptno|| - ||r_dept.dname);
 v_tot_salary:=0;
 FOR r_emp IN c_emp(r_dept.deptno) LOOP
 DBMS_OUTPUT.PUT_LINE(Name:  || v_ename ||  salary:  || v_salary);
 v_tot_salary:=v_tot_salary+v_salary;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept: || v_tot_salary);
 END LOOP;
 END;

在游標 FOR 循環中使用查詢

在游標 FOR 循環中可以定義查詢，由于沒有顯式聲明所以游標沒有名字，記錄名通過游標查詢來定義。

DECALRE
 v_tot_salary EMP.SALARY%TYPE;
 BEGIN
 FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
 DBMS_OUTPUT.PUT_LINE(Department: || r_dept.deptno|| - ||r_dept.dname);
 v_tot_salary:=0;
 FOR r_emp IN (SELECT ename,salary
 FROM emp
 WHERE deptno=p_dept
 ORDER BY ename) LOOP
 DBMS_OUTPUT.PUT_LINE(Name: || v_ename||  salary: ||v_salary);
 v_tot_salary:=v_tot_salary+v_salary;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(Toltal Salary for dept: || v_tot_salary);
 END LOOP;
 END;

游標中的子查詢

語法如下：

CURSOR C1 IS SELECT * FROM emp
 WHERE deptno NOT IN (SELECT deptno
 FROM dept
 WHERE dname!= ACCOUNTING

可以看出與 SQL 中的子查詢沒有什么區別。

游標中的更新和刪除

在 PL/SQL 中依然可以使用 UPDATE 和 DELETE 語句更新或刪除數據行。顯式游標只有在需要獲得多行數據的情
況下使用。PL/SQL 提供了僅僅使用游標就可以執行刪除或更新記錄的方法。
UPDATE 或 DELETE 語句中的 WHERE CURRENT OF 子串專門處理要執行 UPDATE 或 DELETE 操作的表中取出的最近的
數據。要使用這個方法，在聲明游標時必須使用 FOR UPDATE 子串，當對話使用 FOR UPDATE 子串打開一個游標時，所有返回集中的數據行都將處于行級（ROW-LEVEL)獨占式鎖定，其
他對象只能查詢這些數據行，不能進行 UPDATE、DELETE 或 SELECT…FOR UPDATE 操作。

語法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]

在多表查詢中，使用 OF 子句來鎖定特定的表, 如果忽略了 OF 子句，那么所有表中選擇的數據行都將被鎖定。
如果這些數據行已經被其他會話鎖定，那么正常情況下 ORACLE 將等待，直到數據行解鎖。

在 UPDATE 和 DELETE 中使用 WHERE CURRENT OF 子串的語法如下：

WHERE{CURRENT OF cursor_name|search_condition}

例：

DELCARE
 CURSOR c1 IS SELECT empno,salary
 FROM emp
 WHERE comm IS NULL
 FOR UPDATE OF comm;
 v_comm NUMBER(10,2);
 BEGIN
 FOR r1 IN c1 LOOP
 IF r1.salary 500 THEN
 v_comm:=r1.salary*0.25;
 ELSEIF r1.salary 1000 THEN
 v_comm:=r1.salary*0.20;
 ELSEIF r1.salary 3000 THEN
 v_comm:=r1.salary*0.15;
 ELSE
 v_comm:=r1.salary*0.12;
 END IF;
 UPDATE emp;
 SET comm=v_comm
 WHERE CURRENT OF c1l;
 END LOOP;
 END

 - 聲明游標
-- 宗地表的調查日期 LANDINFO_RESEARCHDATE
-- 復制到流程表的權屬調查時間 FLOW_REASEARCHTIME
DECLARE 
cursor cur_sel_all is select LANDINFO_RESEARCHDATE,LANDINFO_LANDNO from t_leoa_landinfo; -- 定義游標
 l_date t_leoa_landinfo.landinfo_researchdate%type; -- 聲明變量分別保存 t_leoa_landinfo 的各列
 l_landNo t_leoa_landinfo.landinfo_landno%type; 
begin
open cur_sel_all;
 loop -- 循環取數，并將游標數據填充到返回紀錄集合中
 fetch cur_sel_all into l_date,l_landNo;
 exit when cur_sel_all%NOTFOUND; -- 循環退出條件
 if cur_sel_all%FOUND then -- 獲取數據  
 update T_LEOA_BOOKFLOW t2 set FLOW_REASEARCHTIME = l_date where l_landNo = t2.landinfo_landno; 
 end if;
 end loop;
close cur_sel_all;
end;

下面再分享一下另外一則游標使用方法的代碼，具體如下：

--  聲明游標；CURSOR cursor_name IS select_statement
--For  循環游標
--（1）定義游標
--（2）定義游標變量
--（3）使用 for 循環來使用這個游標
declare
 -- 類型定義
 cursor c_job
 is
 select empno,ename,job,sal
 from emp
 where job= MANAGER 
 -- 定義一個游標變量 v_cinfo c_emp%ROWTYPE ，該類型為游標 c_emp 中的一行數據類型
 c_row c_job%rowtype;
begin
 for c_row in c_job loop
 dbms_output.put_line(c_row.empno|| - ||c_row.ename|| - ||c_row.job|| - ||c_row.sal);
 end loop;
--Fetch 游標
-- 使用的時候必須要明確的打開和關閉
declare 
 -- 類型定義
 cursor c_job
 is
 select empno,ename,job,sal
 from emp
 where job= MANAGER 
 -- 定義一個游標變量
 c_row c_job%rowtype;
begin
 open c_job;
 loop
 -- 提取一行數據到 c_row
 fetch c_job into c_row;
 -- 判讀是否提取到值，沒取到值就退出
 -- 取到值 c_job%notfound  是 false 
 -- 取不到值 c_job%notfound  是 true
 exit when c_job%notfound;
 dbms_output.put_line(c_row.empno|| - ||c_row.ename|| - ||c_row.job|| - ||c_row.sal);
 end loop;
 -- 關閉游標
 close c_job;
--1：任意執行一個 update 操作，用隱式游標 sql 的屬性 %found,%notfound,%rowcount,%isopen 觀察 update 語句的執行情況。 begin
 update emp set ENAME= ALEARK  WHERE EMPNO=7469;
 if sql%isopen then
 dbms_output.put_line( Openging 
 else
 dbms_output.put_line( closing 
 end if;
 if sql%found then
 dbms_output.put_line( 游標指向了有效行 -- 判斷游標是否指向有效行
 else
 dbms_output.put_line( Sorry 
 end if;
 if sql%notfound then
 dbms_output.put_line( Also Sorry 
 else
 dbms_output.put_line( Haha 
 end if;
 dbms_output.put_line(sql%rowcount);
 exception 
 when no_data_found then
 dbms_output.put_line( Sorry No data 
 when too_many_rows then
 dbms_output.put_line( Too Many rows 
 end;
declare
 empNumber emp.EMPNO%TYPE;
 empName emp.ENAME%TYPE;
 begin
 if sql%isopen then
 dbms_output.put_line( Cursor is opinging 
 else
 dbms_output.put_line( Cursor is Close 
 end if;
 if sql%notfound then
 dbms_output.put_line( No Value 
 else
 dbms_output.put_line(empNumber);
 end if;
 dbms_output.put_line(sql%rowcount);
 dbms_output.put_line( ------------- 
 select EMPNO,ENAME into empNumber,empName from emp where EMPNO=7499;
 dbms_output.put_line(sql%rowcount);
 if sql%isopen then
 dbms_output.put_line( Cursor is opinging 
 else
 dbms_output.put_line( Cursor is Closing 
 end if;
 if sql%notfound then
 dbms_output.put_line( No Value 
 else
 dbms_output.put_line(empNumber);
 end if;
 exception 
 when no_data_found then
 dbms_output.put_line( No Value 
 when too_many_rows then
 dbms_output.put_line( too many rows 
 end;
--2, 使用游標和 loop 循環來顯示所有部門的名稱
-- 游標聲明
declare 
 cursor csr_dept
 is
 --select 語句
 select DNAME
 from Depth;
 -- 指定行指針, 這句話應該是指定和 csr_dept 行類型相同的變量
 row_dept csr_dept%rowtype;
begin
 --for 循環
 for row_dept in csr_dept loop
 dbms_output.put_line(部門名稱: ||row_dept.DNAME);
 end loop;
--3, 使用游標和 while 循環來顯示所有部門的的地理位置（用 %found 屬性）declare
 -- 游標聲明
 cursor csr_TestWhile
 is
 --select 語句
 select LOC
 from Depth;
 -- 指定行指針
 row_loc csr_TestWhile%rowtype;
begin
 -- 打開游標
 open csr_TestWhile;
 -- 給第一行喂數據
 fetch csr_TestWhile into row_loc;
 -- 測試是否有數據，并執行循環
 while csr_TestWhile%found loop
 dbms_output.put_line(部門地點：||row_loc.LOC);
 -- 給下一行喂數據
 fetch csr_TestWhile into row_loc;
 end loop;
 close csr_TestWhile;
end; 
select * from emp
 
--4, 接收用戶輸入的部門編號，用 for 循環和游標，打印出此部門的所有雇員的所有信息(使用循環游標)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
-- 定義參數的語法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value] 
declare 
 CURSOR 
 c_dept(p_deptNo number)
 is
 select * from emp where emp.depno=p_deptNo;
 r_emp emp%rowtype;
begin
 for r_emp in c_dept(20) loop
 dbms_output.put_line(員工號：||r_emp.EMPNO|| 員工名：||r_emp.ENAME|| 工資：||r_emp.SAL);
 end loop;
select * from emp 
--5：向游標傳遞一個工種，顯示此工種的所有雇員的所有信息(使用參數游標)
declare 
 cursor
 c_job(p_job nvarchar2)
 is 
 select * from emp where JOB=p_job;
 r_job emp%rowtype;
begin 
 for r_job in c_job(CLERK) loop
 dbms_output.put_line(員工號 ||r_job.EMPNO||   || 員工姓名 ||r_job.ENAME);
 end loop;
SELECT * FROM EMP
--6：用更新游標來為雇員加傭金：(用 if 實現, 創建一個與 emp 表一摸一樣的 emp1 表，對 emp1 表進行修改操作), 并將更新前后的數據輸出出來  
--http://zheng12tian.iteye.com/blog/815770 
 create table emp1 as select * from emp;
declare
 cursor
 csr_Update
 is
 select * from emp1 for update OF SAL;
 empInfo csr_Update%rowtype;
 saleInfo emp1.SAL%TYPE;
begin
 FOR empInfo IN csr_Update LOOP
 IF empInfo.SAL 1500 THEN
 saleInfo:=empInfo.SAL*1.2;
 elsif empInfo.SAL 2000 THEN
 saleInfo:=empInfo.SAL*1.5;
 elsif empInfo.SAL 3000 THEN
 saleInfo:=empInfo.SAL*2;
 END IF;
 UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
 END LOOP;
--7: 編寫一個 PL/SQL 程序塊，對名字以‘A 或‘S 開始的所有雇員按他們的基本薪水 (sal) 的 10% 給他們加薪(對 emp1 表進行修改操作)
declare 
 cursor
 csr_AddSal
 is
 select * from emp1 where ENAME LIKE  A%  OR ENAME LIKE  S%  for update OF SAL;
 r_AddSal csr_AddSal%rowtype;
 saleInfo emp1.SAL%TYPE;
begin
 for r_AddSal in csr_AddSal loop
 dbms_output.put_line(r_AddSal.ENAME|| 原來的工資: ||r_AddSal.SAL);
 saleInfo:=r_AddSal.SAL*1.1;
 UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
 end loop;
--8：編寫一個 PL/SQL 程序塊，對所有的 salesman 增加傭金(comm)500
declare
 cursor
 csr_AddComm(p_job nvarchar2)
 is
 select * from emp1 where JOB=p_job FOR UPDATE OF COMM;
 r_AddComm emp1%rowtype;
 commInfo emp1.comm%type;
begin
 for r_AddComm in csr_AddComm(SALESMAN) LOOP
 commInfo:=r_AddComm.COMM+500;
 UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
 END LOOP;
--9：編寫一個 PL/SQL 程序塊，以提升 2 個資格最老的職員為 MANAGER（工作時間越長，資格越老）--（提示：可以定義一個變量作為計數器控制游標只提取兩條數據；也可以在聲明游標的時候把雇員中資格最老的兩個人查出來放到游標中。）declare
 cursor crs_testComput
 is
 select * from emp1 order by HIREDATE asc;
 -- 計數器
 top_two number:=2;
 r_testComput crs_testComput%rowtype;
begin
 open crs_testComput;
 FETCH crs_testComput INTO r_testComput;
 while top_two 0 loop
 dbms_output.put_line(員工姓名：||r_testComput.ENAME||   工作時間：||r_testComput.HIREDATE);
 -- 計速器減一
 top_two:=top_two-1;
 FETCH crs_testComput INTO r_testComput;
 end loop;
 close crs_testComput;
--10：編寫一個 PL/SQL 程序塊，對所有雇員按他們的基本薪水 (sal) 的 20% 為他們加薪，-- 如果增加的薪水大于 300 就取消加薪(對 emp1 表進行修改操作，并將更新前后的數據輸出出來) 
declare
 cursor
 crs_UpadateSal
 is
 select * from emp1 for update of SAL;
 r_UpdateSal crs_UpadateSal%rowtype;
 salAdd emp1.sal%type;
 salInfo emp1.sal%type;
begin
 for r_UpdateSal in crs_UpadateSal loop
 salAdd:= r_UpdateSal.SAL*0.2;
 if salAdd 300 then
 salInfo:=r_UpdateSal.SAL;
 dbms_output.put_line(r_UpdateSal.ENAME|| :  加薪失敗。|| 薪水維持在：||r_UpdateSal.SAL);
 else 
 salInfo:=r_UpdateSal.SAL+salAdd;
 dbms_output.put_line(r_UpdateSal.ENAME|| :  加薪成功. || 薪水變為：||salInfo);
 end if;
 update emp1 set SAL=salInfo where current of crs_UpadateSal;
 end loop;
--11: 將每位員工工作了多少年零多少月零多少天輸出出來  
 --CEIL(n)函數：取大于等于數值 n 的最小整數
 --FLOOR(n)函數：取小于等于數值 n 的最大整數
 --truc 的用法  http://publish.it168.com/2005/1028/20051028034101.shtml
declare
 cursor
 crs_WorkDay
 is
 select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
 trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
 trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
 from emp1;
 r_WorkDay crs_WorkDay%rowtype;
begin
 for r_WorkDay in crs_WorkDay loop
 dbms_output.put_line(r_WorkDay.ENAME|| 已經工作了 ||r_WorkDay.SPANDYEARS|| 年, 零 ||r_WorkDay.months|| 月, 零 ||r_WorkDay.days|| 天 
 end loop;
--12: 輸入部門編號，按照下列加薪比例執行(用 CASE 實現，創建一個 emp1 表，修改 emp1 表的數據), 并將更新前后的數據輸出出來
-- deptno raise(%)
-- 10 5%
-- 20 10%
-- 30 15%
-- 40 20%
--  加薪比例以現有的 sal 為標準
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
 cursor
 crs_caseTest
 is
 select * from emp1 for update of SAL;
 r_caseTest crs_caseTest%rowtype;
 salInfo emp1.sal%type;
 begin
 for r_caseTest in crs_caseTest loop
 case 
 when r_caseTest.DEPNO=10
 THEN salInfo:=r_caseTest.SAL*1.05;
 when r_caseTest.DEPNO=20
 THEN salInfo:=r_caseTest.SAL*1.1;
 when r_caseTest.DEPNO=30
 THEN salInfo:=r_caseTest.SAL*1.15;
 when r_caseTest.DEPNO=40
 THEN salInfo:=r_caseTest.SAL*1.2;
 end case;
 update emp1 set SAL=salInfo where current of crs_caseTest;
 end loop;
--13: 對每位員工的薪水進行判斷，如果該員工薪水高于其所在部門的平均薪水，則將其薪水減 50 元，輸出更新前后的薪水，員工姓名，所在部門編號。--AVG([distinct|all] expr) over (analytic_clause)
--- 作用：-- 按照 analytic_clause 中的規則求分組平均值。 -- 分析函數語法:
 --FUNCTION_NAME(argument , argument ...)
 --OVER
 --(Partition-Clause Order-by-Clause Windowing Clause)
 --PARTITION 子句
 -- 按照表達式分區(就是分組), 如果省略了分區子句, 則全部的結果集被看作是一個單一的組
 select * from emp1
DECLARE
 CURSOR 
 crs_testAvg
 IS
 select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
 FROM EMP1 for update of SAL;
 r_testAvg crs_testAvg%rowtype;
 salInfo emp1.sal%type;
 begin
 for r_testAvg in crs_testAvg loop
 if r_testAvg.SAL r_testAvg.DEP_AVG then
 salInfo:=r_testAvg.SAL-50;
 end if;
 update emp1 set SAL=salInfo where current of crs_testAvg;
 end loop;
end;

看完上述內容，你們對 Cursor 如何在 Oracle 中使用有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。

向 AI 問一下細節

正文完