Comment utiliser le curseur dans Oracle

Cet article présente principalement l'utilisation de base du curseur dans Oracle. Il est relativement complet après avoir lu cet article, j'espère que vous pourrez maîtriser comment utiliser le curseur dans Oracle.

Requête

L'instruction SELECT est utilisée pour interroger les données de la base de données. Lors de l'utilisation de l'instruction SELECT en PL/SQL, vous devez Utilisé avec la clause INTO, la valeur de retour
de la requête est affectée à la variable dans la clause INTO et la déclaration de variable est dans DELCARE. La syntaxe SELECT INTO est la suivante :

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

L'instruction SELECT en PL/SQL ne renvoie qu'une seule ligne de données. S'il y a plus d'une ligne de données, alors un curseur explicite doit être utilisé (nous parlerons des curseurs plus tard) et la clause INTO doit avoir le même nombre de colonnes que la clause SELECT. Les variables d'enregistrement peuvent également être incluses dans la clause INTO.

Attribut %TYPE

En PL/SQL, les variables et les constantes peuvent être déclarées comme types de données intégrés ou définis par l'utilisateur pour référence Un nom de colonne hérite de sa classe de données

type et taille. Cette méthode d'affectation dynamique est très utile. Par exemple, si le type de données et la taille de la colonne référencée par la variable changent, si %TYPE,
est utilisé, l'utilisateur n'a pas à modifier le code, sinon le code doit le faire. être modifié.

Exemple :

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

Non seulement les noms de colonnes peuvent utiliser %TYPE, mais également les variables, curseurs, enregistrements ou déclarations Les constantes peuvent utiliser %TYPE. Ceci est utile pour définir des variables du même type de données

.

　　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>

Autres instructions DML

Les autres instructions DML pour les données d'exploitation sont : INSERT, UPDATE , DELETE et LOCK TABLE, la syntaxe de ces instructions en PL/SQL est la même que la syntaxe de

en SQL. Nous avons déjà discuté de l'utilisation des instructions DML

auparavant et ne les répéterons pas ici. Toute variable déclarée dans la section DECLARE peut être utilisée dans une instruction DML. S'il s'agit d'un bloc

imbriqué, faites attention à la portée de la variable.

Exemple :

　　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

Résultats de la déclaration DML

Lorsqu'une instruction DML est exécutée, les résultats de l'instruction DML sont enregistrés dans quatre attributs de curseur. Ces attributs sont utilisés pour contrôler le déroulement du programme ou comprendre l'état du programme

. Lorsqu'une instruction DML est exécutée, PL/SQL ouvre un curseur intégré et traite les résultats. Un curseur est une zone en mémoire qui conserve les résultats de la requête
Un curseur est ouvert lorsqu'une instruction DML est exécutée et fermé une fois terminé. . Les curseurs implicites n'utilisent que trois attributs : SQL%FOUND,

SQL%NOTFOUND et SQL%ROWCOUNT SQL%FOUND et SQL%NOTFOUND sont des valeurs booléennes et SQL%ROWCOUNT est une valeur entière.

SQL%FOUND et SQL%NOTFOUND

Les valeurs de SQL%FOUND et SQL%NOTFOUND sont NULL avant d'exécuter une instruction DML . Après avoir exécuté l'instruction DML, la valeur de l'attribut SQL%FOUND sera :

TRUE : INSERT

 : DELETE et UPDATE, au moins une ligne est DELETE ou UPDATE.<. 🎜>. TRUE : SELECT INTO renvoie au moins une ligne


Lorsque SQL%FOUND est TRUE, SQL%NOTFOUND est FALSE.

SQL%ROWCOUNT

Avant d'exécuter une instruction DML, la valeur de SQL%ROWCOUNT est NULL Pour l'instruction SELECT INTO, si l'exécution réussit

. , SQL La valeur de %ROWCOUNT est 1. S'il n'y a pas de succès, la valeur de SQL%ROWCOUNT est 0 et une exception NO_DATA_FOUND est générée

SQL%ISOPEN

<. 🎜>SQL%ISOPEN est une valeur booléenne qui est VRAIE si le curseur est ouvert et FALSE si le curseur est fermé. SQL%
ISOPEN est toujours FAUX pour les curseurs implicites car les curseurs implicites

<🎜 Le >. La marque est ouverte lorsque l'instruction DML est exécutée et fermée immédiatement à la fin.

Déclaration de contrôle des transactions

Une transaction est une unité de travail logique qui peut inclure une ou plusieurs instructions DML d'aide au contrôle des transactions. utilisateurs Assurer la cohérence des données. Si une instruction DML dans l'unité logique de contrôle de transaction échoue, la transaction entière sera annulée. En PL/SQL, les utilisateurs peuvent explicitement utiliser les instructions COMMIT, ROLLBACK, SAVEPOINT et
SET TRANSACTION.

L'instruction COMMIT termine la transaction, enregistre définitivement les modifications dans la base de données et libère tous les LOCK. ROLLBACK met fin à la transaction en cours et libère tous les LOCK,
mais n'enregistre aucune modification dans le base de données, SAVEPOI

NT est utilisé pour définir le point intermédiaire Lorsque la transaction appelle trop d'opérations de base de données, le point intermédiaire est très utile pour définir les attributs de la transaction, tels que la lecture. -niveau d'écriture et d'isolement.

Curseur explicite

Lorsque la requête renvoie plus d'une ligne, un curseur explicite est requis à ce moment. , l'utilisateur L'instruction select into ne peut pas être utilisée. PL/SQL gère les curseurs implicites. Le curseur implicite est ouvert au démarrage de la requête et se ferme automatiquement à la fin de la requête. Les curseurs explicites sont déclarés dans la partie déclaration du bloc PL/SQL, ouverts dans la partie exécution ou dans la partie gestion des exceptions, récupèrent les données et sont fermés.

Utiliser des curseurs

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

声明游标

语法：

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函数

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!