So verwenden Sie den Cursor in Oracle

Dieser Artikel stellt hauptsächlich die grundlegende Verwendung von Cursor in Oracle vor. Ich hoffe, dass Sie nach dem Lesen dieses Artikels die Verwendung von Cursor in Oracle beherrschen.

Abfrage

Die SELECT-Anweisung wird verwendet, um Daten aus der Datenbank abzufragen. Bei Verwendung der SELECT-Anweisung in PL/SQL. Bei Verwendung mit der INTO-Klausel wird der
-Rückgabewert der Abfrage der Variablen in der INTO-Klausel zugewiesen und die Variablendeklaration erfolgt in DELCARE. Die SELECT INTO-Syntax lautet wie folgt:

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

Die SELECT-Anweisung in PL/SQL gibt nur eine Datenzeile zurück. Wenn mehr als eine Datenzeile vorhanden ist, muss ein expliziter Cursor verwendet werden (wir werden Cursor später besprechen) und die INTO-Klausel muss die gleiche Anzahl von Spalten wie die SELECT-Klausel haben. Datensatzvariablen können auch in die INTO-Klausel einbezogen werden.

%TYPE-Attribut

In PL/SQL können Variablen und Konstanten als integrierte oder benutzerdefinierte Datentypen als Referenz deklariert werden Ein Spaltenname erbt den Typ und die Größe seiner Datenklasse

. Diese dynamische Zuweisungsmethode ist sehr nützlich, wenn sich beispielsweise der Datentyp und die Größe der Spalte, auf die die Variable verweist, ändern, wenn %TYPE,
verwendet wird, muss der Benutzer den Code nicht ändern, andernfalls muss der Code geändert werden geändert werden.

Beispiel:

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

Nicht nur Spaltennamen können %TYPE verwenden, sondern auch Variablen, Cursor, Datensätze usw Deklarationen Konstanten können %TYPE verwenden. Dies ist nützlich, um Variablen desselben Datentyps

zu definieren.

　　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);
　　END
　　SQL>/
　　V_A=10 V_B=15 V_C=
　　PL/SQL procedure successfully completed.
　　SQL>

Weitere DML-Anweisungen

Weitere DML-Anweisungen für Betriebsdaten sind: INSERT, UPDATE , DELETE und LOCK TABLE, die Syntax dieser Anweisungen in PL/SQL ist dieselbe wie die Syntax von

in SQL. Wir haben die Verwendung von DML-Anweisungen

bereits zuvor besprochen und werden sie hier nicht wiederholen. Jede im DECLARE-Abschnitt deklarierte Variable kann in einer DML-Anweisung verwendet werden. Wenn es sich um einen verschachtelten

-Block handelt, achten Sie auf den Gültigkeitsbereich der Variablen.

Beispiel:

　　CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
　　AS
　　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

Ergebnisse der DML-Erklärung

Wenn eine DML-Anweisung ausgeführt wird, werden die Ergebnisse der DML-Anweisung in vier Cursorattributen gespeichert. Diese Attribute werden verwendet, um den Programmablauf zu steuern oder den Status des Programms zu verstehen

. Wenn eine DML-Anweisung ausgeführt wird, öffnet PL/SQL einen integrierten Cursor und verarbeitet die Ergebnisse. Ein Cursor ist ein Bereich im Speicher, der Abfrageergebnisse verwaltet.
Ein Cursor wird geöffnet, wenn eine DML-Anweisung ausgeführt wird, und geschlossen . Implizite Cursor verwenden nur drei Attribute: SQL%FOUND,

SQL%NOTFOUND und SQL%ROWCOUNT und SQL%NOTFOUND sind boolesche Werte und SQL%ROWCOUNT ist ein ganzzahliger Wert.

SQL%FOUND und SQL%NOTFOUND

Die Werte von SQL%FOUND und SQL%NOTFOUND sind NULL, bevor eine DML-Anweisung ausgeführt wird . Nach der Ausführung der DML-Anweisung lautet der Attributwert von SQL%FOUND:

, mindestens eine Zeile ist DELETE oder UPDATE.

. TRUE: SELECT INTO gibt mindestens eine Zeile zurück


Wenn SQL%FOUND TRUE ist, ist SQL%NOTFOUND FALSE.

SQL%ROWCOUNT

Vor der Ausführung einer DML-Anweisung ist der Wert von SQL%ROWCOUNT NULL für die SELECT INTO-Anweisung, wenn die Ausführung erfolgreich ist

, SQL Der Wert von %ROWCOUNT ist 1. Wenn kein Erfolg vorliegt, ist der Wert von SQL%ROWCOUNT 0 und es wird eine Ausnahme NO_DATA_FOUND generiert

SQL%ISOPEN

SQL%ISOPEN ist ein boolescher Wert, der TRUE ist, wenn der Cursor geöffnet ist, und FALSE, wenn der Cursor geschlossen ist. SQL%

ISOPEN ist für implizite Cursor immer FALSE, da implizite Cursor

Die Markierung wird geöffnet, wenn die DML-Anweisung ausgeführt wird, und sofort geschlossen, wenn sie endet.

Transaktionskontrollanweisung

Eine Transaktion ist eine logische Arbeitseinheit, die eine oder mehrere DML-Anweisungen enthalten kann Benutzer stellen Datenkonsistenz sicher. Wenn eine DML-
-Anweisung in der Transaktionssteuerungslogikeinheit fehlschlägt, wird die gesamte Transaktion zurückgesetzt. In PL/SQL können Benutzer explizit COMMIT-, ROLLBACK-, SAVEPOINT- und

SET TRANSACTION-Anweisungen verwenden.

Die COMMIT-Anweisung beendet die Transaktion, speichert die Änderungen dauerhaft in der Datenbank und gibt alle LOCKs frei. ROLLBACK beendet die aktuelle Transaktion und gibt alle LOCKs frei,
speichert jedoch keine Änderungen in der Datenbank, SAVEPOI

NT wird zum Festlegen des Zwischenpunkts verwendet. Wenn die Transaktion zu viele Datenbankoperationen aufruft, ist der Zwischenpunkt sehr nützlich, um die Transaktionsattribute festzulegen, z -Schreib- und Isolationsstufe.

Expliziter Cursor

Wenn die Abfrage mehr als eine Zeile zurückgibt, ist zu diesem Zeitpunkt ein expliziter Cursor erforderlich , der Benutzer Die select into-Anweisung kann nicht verwendet werden. PL/SQL verwaltet implizite -Cursor. Der implizite Cursor wird geöffnet, wenn die Abfrage beginnt, und wird automatisch geschlossen, wenn die Abfrage endet. Explizite Cursor werden im Deklarationsteil des PL/SQL-Blocks deklariert, im Ausführungsteil oder Ausnahmebehandlungsteil geöffnet, Daten abrufen und geschlossen.

Cursor verwenden

这里要做一个声明，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的
游标都是指显式游标。要在程序中使用游标，必须首先声明游标。

声明游标

语法：

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;
end;
--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;
end;
--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;
end;
--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;
end;
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;
end;
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;
END;
--7:编写一个PL/SQL程序块，对名字以‘A&#39;或‘S&#39;开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare 
   cursor
   csr_AddSal
   is
   select * from emp1 where ENAME LIKE &#39;A%&#39; OR ENAME LIKE &#39;S%&#39; 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||&#39;原来的工资:&#39;||r_AddSal.SAL);
     saleInfo:=r_AddSal.SAL*1.1;
     UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
   end loop;
end;
--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(&#39;SALESMAN&#39;) LOOP
    commInfo:=r_AddComm.COMM+500;
     UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
  END LOOP;
END;
--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(&#39;员工姓名：&#39;||r_testComput.ENAME||&#39; 工作时间：&#39;||r_testComput.HIREDATE);
       --计速器减一
       top_two:=top_two-1;
       FETCH crs_testComput INTO r_testComput;
      end loop;
   close crs_testComput;
end;
--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||&#39;: 加薪失败。&#39;||&#39;薪水维持在：&#39;||r_UpdateSal.SAL);
       else 
       salInfo:=r_UpdateSal.SAL+salAdd;
       dbms_output.put_line(r_UpdateSal.ENAME||&#39;: 加薪成功.&#39;||&#39;薪水变为：&#39;||salInfo);
      end if;
      update emp1 set SAL=salInfo where current of crs_UpadateSal;
    end loop;
end;
--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||&#39;已经工作了&#39;||r_WorkDay.SPANDYEARS||&#39;年,零&#39;||r_WorkDay.months||&#39;月,零&#39;||r_WorkDay.days||&#39;天&#39;);
  end loop;
end;
--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;
end;
--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;

相关推荐：

关于Oracle 中Contains 函数的用法总结

oracle中decode函数的如何使用

详解Oracle中的translate函数和replace函数

Das obige ist der detaillierte Inhalt vonSo verwenden Sie den Cursor in Oracle. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!