`
langgufu
  • 浏览: 2308791 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

oracle游标详解

oraclecursorloop 
阅读更多

Oracle游标循环

 
第一种使用loop 循环
          open c_postype;
    0.    loop   
  1.     fetch c_postype into v_postype,v_description ;   
  2.     exit when c_postype%notfound;   
  3.     ……   
  4. end loop  
  5. colse c_postype;

这里需要注意，exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。 
处理逻辑需要跟在exit when之后。这一点需要多加小心。 
循环结束后要记得关闭游标。

 

第二种使用while循环。

            open c_postype;

  1.    fetch c_postype into v_postype,v_description;   
  2.    while c_postype%found loop   
  3.     ……   
  4.        fetch c_postype into v_postype,v_description ;   
  5.      end loop;  
  6. close c_postype;


我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while 循环时，就需要在循环之前进行一次fetch动作。 
而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。 
总之，使用while来循环处理游标是最复杂的方法。

 

第三种 for循环

          for v_pos in c_postype loop   
  1.     v_postype := v_pos.pos_type;   
  2.     v_description := v_pos.description;   
  3.     …   
  4. end loop;  

可见for循环是比较简单实用的方法。 
首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。 
其它,自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。 
我们需要注意v_pos 这个变量无需要在循环外进行声明，无需要为其指定数据类型。 
它应该是一个记录类型，具体的结构是由游标决定的。 
这个变量的作用域仅仅是在循环体内。 
把v_pos看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。 
如v_pos.pos_type 
由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。 
但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了。

 

oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标

游标的概念:
    游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。 

 

隐式游标 
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是： 
* 插入操作：INSERT。 
* 更新操作：UPDATE。 
* 删除操作：DELETE。 
* 单行查询操作：SELECT ... INTO ...。 
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

Sql代码  
  1. 隐式游标的属性 返回值类型   意    义   
  2. SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数   
  3. SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功   
  4. SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反   
  5. SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  
【训练1】　使用隐式游标的属性，判断对雇员工资的修改是否成功。 
步骤1：输入和运行以下程序： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2.         BEGIN  
  3.         UPDATE emp SET sal=sal+100 WHERE empno=1234;   
  4.          IF SQL%FOUND THEN    
  5.         DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');   
  6.         COMMIT;    
  7.         ELSE  
  8.         DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');   
  9.          END IF;    
  10.         END;  
运行结果为： 
Sql代码  
  1. 修改雇员工资失败！   
  2.         PL/SQL 过程已成功完成。  
步骤2：将雇员编号1234改为7788，重新执行以上程序： 
运行结果为： 
Sql代码  
  1. 成功修改雇员工资！   
  2.         PL/SQL 过程已成功完成。  
说明：本例中，通过SQL%FOUND属性判断修改是否成功，并给出相应信息。 
显式游标 
游标的定义和操作 
游标的使用分成以下4个步骤。 
1．声明游标 
在DECLEAR部分按以下格式声明游标： 
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
IS SELECT语句; 
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。 
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标 
在可执行部分，按以下格式打开游标： 
OPEN 游标名[(实际参数1[，实际参数2...])]; 
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 
3．提取数据 
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。 
FETCH 游标名 INTO 变量名1[，变量名2...]; 
或 
FETCH 游标名 INTO 记录变量; 
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。 
下面对这两种格式进行说明： 
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。 
定义记录变量的方法如下： 
变量名 表名|游标名%ROWTYPE； 
其中的表必须存在，游标名也必须先定义。 
4．关闭游标 
CLOSE 游标名; 
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。 
以下是使用显式游标的一个简单练习。 
【训练1】  用游标提取emp表中7788雇员的名称和职务。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2.         DECLARE    
  3.          v_ename VARCHAR2(10);   
  4.          v_job VARCHAR2(10);   
  5.          CURSOR emp_cursor IS    
  6.          SELECT ename,job FROM emp WHERE empno=7788;   
  7.          BEGIN  
  8.      OPEN emp_cursor;   
  9.     FETCH emp_cursor INTO v_ename,v_job;   
  10.         DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);   
  11.         CLOSE emp_cursor;   
  12.         END;  
执行结果为: 
Sql代码  
  1. SCOTT,ANALYST   
  2.         PL/SQL 过程已成功完成。   
说明：该程序通过定义游标emp_cursor，提取并显示雇员7788的名称和职务。 
作为对以上例子的改进，在以下训练中采用了记录变量。 
【训练2】  用游标提取emp表中7788雇员的姓名、职务和工资。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2.         DECLARE  
  3.          CURSOR emp_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;   
  4.          emp_record emp_cursor%ROWTYPE;   
  5.         BEGIN  
  6. OPEN emp_cursor;       
  7.         FETCH emp_cursor INTO emp_record;   
  8.            DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);   
  9.          CLOSE emp_cursor;   
  10.         END;  
执行结果为: 
Sql代码  
  1. SCOTT,ANALYST,3000   
  2.         PL/SQL 过程已成功完成。   
说明：实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。 
注意：可通过以下形式获得记录变量的内容： 
记录变量名.字段名。 
【训练3】  显示工资最高的前3名雇员的名称和工资。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2.         DECLARE  
  3.          V_ename VARCHAR2(10);   
  4.         V_sal NUMBER(5);   
  5.         CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;   
  6.         BEGIN  
  7.          OPEN emp_cursor;   
  8.          FOR I IN 1..3 LOOP   
  9.            FETCH emp_cursor INTO v_ename,v_sal;   
  10.          DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);   
  11.           END LOOP;   
  12.          CLOSE emp_cursor;   
  13.          END;  
执行结果为: 
Sql代码  
  1. KING,5000   
  2.      SCOTT,3000   
  3.      FORD,3000   
  4.      PL/SQL 过程已成功完成。  
  说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据
 
游标循环 
【训练1】  使用特殊的FOR循环形式显示全部雇员的编号和名称。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2. DECLARE  
  3.   CURSOR emp_cursor IS    
  4.   SELECT empno, ename FROM emp;   
  5. BEGIN  
  6. FOR Emp_record IN emp_cursor LOOP      
  7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);   
  8.     END LOOP;   
  9.     END;  
执行结果为： 
Sql代码  
  1. 7369SMITH   
  2. 7499ALLEN   
  3. 7521WARD   
  4. 7566JONES   
  5.          PL/SQL 过程已成功完成。  
  说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。
【训练2】  另一种形式的游标循环。 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2. BEGIN  
  3.  FOR re IN (SELECT ename FROM EMP)  LOOP   
  4.   DBMS_OUTPUT.PUT_LINE(re.ename)   
  5.  END LOOP;   
  6. END;  
执行结果为： 
Sql代码  
  1. SMITH   
  2. ALLEN   
  3. WARD   
  4. JONES  
    说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。 
显式游标属性 
虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。 
Sql代码  
  1. 游标的属性   返回值类型   意    义   
  2. %ROWCOUNT   整型  获得FETCH语句返回的数据行数   
  3. %FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假   
  4. %NOTFOUND   布尔型 与%FOUND属性返回值相反   
  5. %ISOPEN 布尔型 游标已经打开时值为真，否则为假  
可按照以下形式取得游标的属性： 
游标名%属性 
要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。
【训练1】  使用游标的属性练习。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2. DECLARE  
  3.   V_ename VARCHAR2(10);   
  4.   CURSOR emp_cursor IS    
  5.   SELECT ename FROM emp;   
  6. BEGIN  
  7.  OPEN emp_cursor;   
  8.  IF emp_cursor%ISOPEN THEN  
  9. LOOP   
  10.    FETCH emp_cursor INTO v_ename;   
  11.    EXIT WHEN emp_cursor%NOTFOUND;   
  12.    DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);   
  13.   END LOOP;   
  14.  ELSE  
  15.   DBMS_OUTPUT.PUT_LINE('用户信息：游标没有打开！');   
  16.  END IF;   
  17.  CLOSE  emp_cursor;   
  18. END;  
执行结果为： 
Sql代码  
  1. 1-SMITH   
  2. 2-ALLEN   
  3. 3-WARD   
  4.  PL/SQL 过程已成功完成。  
    说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH语句；使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行，当FETCH语句失败时说明数据已经取完，退出循环。
【练习1】去掉OPEN emp_cursor;语句，重新执行以上程序。 
游标参数的传递 
 【训练1】  带参数的游标。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2.         DECLARE  
  3.             V_empno NUMBER(5);   
  4.             V_ename VARCHAR2(10);   
  5.             CURSOR  emp_cursor(p_deptno NUMBER,     p_job VARCHAR2) IS  
  6.             SELECT  empno, ename FROM emp   
  7.             WHERE   deptno = p_deptno AND job = p_job;   
  8. BEGIN  
  9.      OPEN emp_cursor(10, 'CLERK');   
  10.     LOOP   
  11.      FETCH emp_cursor INTO v_empno,v_ename;   
  12.      EXIT WHEN emp_cursor%NOTFOUND;   
  13.      DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);   
  14.       END LOOP;   
  15.     END;  
执行结果为： 
Sql代码  
  1. 7934,MILLER   
  2.         PL/SQL 过程已成功完成。  
说明：游标emp_cursor定义了两个参数：p_deptno代表部门编号，p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标，即部门为10、职务为CLERK，所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查询的内容。
【练习1】修改Open语句的参数：部门号为20、职务为ANALYST，并重新执行。 
也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值。对以上例子重新改动如下： 
  【训练2】  通过变量传递参数给游标。 
Sql代码  
  1. SET SERVEROUTPUT ON  
  2.         DECLARE  
  3.         v_empno NUMBER(5);   
  4.         v_ename VARCHAR2(10);   
  5.         v_deptno NUMBER(5);   
  6. v_job VARCHAR2(10);   
  7.          CURSOR emp_cursor IS  
  8.             SELECT empno, ename FROM emp   
  9.             WHERE   deptno = v_deptno AND job = v_job;   
  10.         BEGIN  
  11.          v_deptno:=10;   
  12.          v_job:='CLERK';   
  13.          OPEN emp_cursor;   
  14.         LOOP   
  15.          FETCH emp_cursor INTO v_empno,v_ename;   
  16.            EXIT WHEN emp_cursor%NOTFOUND;   
  17. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);   
  18.          END LOOP;   
  19.         END;  
执行结果为： 
Sql代码  
  1. 7934,MILLER   
  2.         PL/SQL 过程已成功完成。  
说明：该程序与前一程序实现相同的功能。 
动态SELECT语句和动态游标的用法 
Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。 
对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行，语法是： 
execute immediate 查询语句字符串 into 变量1[,变量2...]; 
以下是一个动态生成SELECT语句的例子。 
【训练1】  动态SELECT查询。 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2.         DECLARE    
  3.         str varchar2(100);   
  4.         v_ename varchar2(10);   
  5.         begin  
  6.         str:='select ename from scott.emp where empno=7788';   
  7.         execute immediate str into v_ename;    
  8.         dbms_output.put_line(v_ename);   
  9.         END;   
执行结果为： 
Sql代码  
  1. SCOTT   
  2.         PL/SQL 过程已成功完成。  
说明：SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。 

在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。
定义游标类型的语句如下： 
TYPE 游标类型名 REF CURSOR; 
声明游标变量的语句如下： 
游标变量名 游标类型名; 
在可执行部分可以如下形式打开一个动态游标： 
OPEN 游标变量名 FOR 查询语句字符串; 
【训练2】  按名字中包含的字母顺序分组显示雇员信息。 
输入并运行以下程序： 
Sql代码  
  1. declare    
  2.  type cur_type is ref cursor;   
  3.  cur cur_type;   
  4.  rec scott.emp%rowtype;   
  5.  str varchar2(50);   
  6.  letter char:= 'A';   
  7. begin  
  8.         loop           
  9.          str:= 'select ename from emp where ename like ''%'||letter||'%''';   
  10.          open cur for str;   
  11.          dbms_output.put_line('包含字母'||letter||'的名字：');   
  12.           loop   
  13.          fetch cur into rec.ename;   
  14.          exit when cur%notfound;   
  15.         dbms_output.put_line(rec.ename);   
  16. end loop;   
  17.   exit when letter='Z';   
  18.   letter:=chr(ascii(letter)+1);   
  19.  end loop;   
  20. end;  
运行结果为： 
Sql代码  
  1. 包含字母A的名字：   
  2. ALLEN   
  3. WARD   
  4. MARTIN   
  5. BLAKE   
  6. CLARK   
  7. ADAMS   
  8. JAMES   
  9. 包含字母B的名字：   
  10. BLAKE   
  11. 包含字母C的名字：   
  12. CLARK   
  13. SCOTT  
说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。

阶段训练 
【训练1】  将雇员从一个表复制到另一个表。 
步骤1：创建一个结构同EMP表一样的新表EMP1： 
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2; 
步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2. DECLARE  
  3. v_empno NUMBER(5):=7788;   
  4. emp_rec emp%ROWTYPE;   
  5. BEGIN  
  6.  SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;   
  7.  DELETE FROM emp WHERE empno=v_empno;   
  8. INSERT INTO emp1 VALUES emp_rec;   
  9.  IF SQL%FOUND THEN  
  10.   COMMIT;   
  11.   DBMS_OUTPUT.PUT_LINE('雇员复制成功！');   
  12.  ELSE    
  13.   ROLLBACK;   
  14.   DBMS_OUTPUT.PUT_LINE('雇员复制失败！');   
  15.  END IF;   
  16. END;  
[sql] view plaincopyprint?
  1. SET SERVEROUTPUT ON   
  2. DECLARE  
  3. v_empno NUMBER(5):=7788;  
  4. emp_rec emp%ROWTYPE;  
  5. BEGIN  
  6.  SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;  
  7.  DELETE FROM emp WHERE empno=v_empno;  
  8. INSERT INTO emp1 VALUES emp_rec;  
  9.  IF SQL%FOUND THEN  
  10.   COMMIT;  
  11.   DBMS_OUTPUT.PUT_LINE('雇员复制成功！');  
  12.  ELSE   
  13.   ROLLBACK;  
  14.   DBMS_OUTPUT.PUT_LINE('雇员复制失败！');  
  15.  END IF;  
  16. END;  

执行结果为： 
雇员复制成功！ 
PL/SQL 过程已成功完成。 
步骤2：显示复制结果： 
SELECT empno,ename,job FROM emp1; 
执行结果为： 
 
Sql代码  
  1. EMPNO ENAME      JOB   
  2. ------------- -------------- ----------------  
  3.     7788  SCOTT      ANALYST  
[sql] view plaincopyprint?
  1. EMPNO ENAME      JOB  
  2. ------------- -------------- ----------------  
  3.     7788  SCOTT      ANALYST  

说明：emp_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员编号为7902，重新执行以上程序。
【训练2】  输出雇员工资，雇员工资用不同高度的*表示。 
输入并执行以下程序： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2. BEGIN  
  3.  FOR re IN (SELECT ename,sal FROM EMP)  LOOP   
  4.   DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));   
  5.  END LOOP;   
  6. END;  
[sql] view plaincopyprint?
  1. SET SERVEROUTPUT ON   
  2. BEGIN  
  3.  FOR re IN (SELECT ename,sal FROM EMP)  LOOP  
  4.   DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));  
  5.  END LOOP;  
  6. END;  

输出结果为： 
Sql代码  
  1. SMITH       ********   
  2. ALLEN           ****************   
  3. WARD        *************   
  4. JONES           ******************************   
  5. MARTIN      *************   
  6. BLAKE       *****************************   
  7. CLARK           *****************************   
  8. SCOTT           ******************************   
  9. KING            **************************************************   
  10. TURNER      ***************   
  11. ADAMS       ***********   
  12. JAMES           **********   
  13. FORD            ******************************   
  14. MILLER          *************   
  15.          执行结果为：   
  16.         PL/SQL 过程已成功完成。  
[sql] view plaincopyprint?
  1. SMITH       ********  
  2. ALLEN           ****************  
  3. WARD        *************  
  4. JONES           ******************************  
  5. MARTIN      *************  
  6. BLAKE       *****************************  
  7. CLARK           *****************************  
  8. SCOTT           ******************************  
  9. KING            **************************************************  
  10. TURNER      ***************  
  11. ADAMS       ***********  
  12. JAMES           **********  
  13. FORD            ******************************  
  14. MILLER          *************  
  15.          执行结果为：  
  16.         PL/SQL 过程已成功完成。  

  说明：第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。 
【训练3】  编写程序，格式化输出部门信息。 
输入并执行如下程序： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2.         DECLARE  
  3.          v_count number:=0;   
  4.          CURSOR dept_cursor IS SELECT * FROM dept;   
  5.         BEGIN  
  6.           DBMS_OUTPUT.PUT_LINE('部门列表');   
  7. DBMS_OUTPUT.PUT_LINE('---------------------------------');   
  8.          FOR Dept_record IN dept_cursor LOOP      
  9.          DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno);   
  10.          DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname);   
  11.             DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc);   
  12. DBMS_OUTPUT.PUT_LINE('---------------------------------');   
  13.       v_count:= v_count+1;   
  14.         END LOOP;   
  15.          DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！');   
  16.         END;  
[sql] view plaincopyprint?
  1. SET SERVEROUTPUT ON   
  2.         DECLARE  
  3.          v_count number:=0;  
  4.          CURSOR dept_cursor IS SELECT * FROM dept;  
  5.         BEGIN  
  6.           DBMS_OUTPUT.PUT_LINE('部门列表');  
  7. DBMS_OUTPUT.PUT_LINE('---------------------------------');  
  8.          FOR Dept_record IN dept_cursor LOOP     
  9.          DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno);  
  10.          DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname);  
  11.             DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc);  
  12. DBMS_OUTPUT.PUT_LINE('---------------------------------');  
  13.       v_count:= v_count+1;  
  14.         END LOOP;  
  15.          DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！');  
  16.         END;  

输出结果为： 
Sql代码  
  1. 部门列表   
  2. ------------------------------------  
  3. 部门编号：10   
  4. 部门名称：ACCOUNTING   
  5. 所在城市：NEW YORK   
  6. ------------------------------------  
  7. 部门编号：20   
  8. 部门名称：RESEARCH   
  9. 所在城市：DALLAS   
  10. ...   
  11. 共有4个部门！   
  12. PL/SQL 过程已成功完成。  
[sql] view plaincopyprint?
  1. 部门列表  
  2. ------------------------------------  
  3. 部门编号：10  
  4. 部门名称：ACCOUNTING  
  5. 所在城市：NEW YORK  
  6. ------------------------------------  
  7. 部门编号：20  
  8. 部门名称：RESEARCH  
  9. 所在城市：DALLAS  
  10. ...  
  11. 共有4个部门！  
  12. PL/SQL 过程已成功完成。  

  说明：该程序中将字段内容垂直排列。V_count变量记录循环次数，即部门个数。 
【训练4】  已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。 
输入并执行如下程序： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2. DECLARE  
  3.  v_deptno number(8);   
  4.  v_count number(3);   
  5.  v_sumsal number(6);   
  6.  v_dname  varchar2(15);   
  7. v_manager  varchar2(15);   
  8.  CURSOR list_cursor IS  
  9.    SELECT deptno,count(*),sum(sal) FROM emp group by deptno;   
  10. BEGIN  
  11.   OPEN list_cursor;    
  12.   DBMS_OUTPUT.PUT_LINE('----------- 部 门 统 计 表 -----------');   
  13. DBMS_OUTPUT.PUT_LINE('部门名称   总人数  总工资   部门经理');   
  14.   FETCH list_cursor INTO v_deptno,v_count,v_sumsal;    
  15.   WHILE list_cursor%found LOOP     
  16.  SELECT dname INTO v_dname FROM dept   
  17.     WHERE deptno=v_deptno;   
  18.     SELECT ename INTO v_manager FROM emp    
  19.     WHERE deptno=v_deptno and job='MANAGER';   
  20. DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)   
  21.       ||rpad(to_char(v_sumsal),9)||v_manager);   
  22.     FETCH list_cursor INTO v_deptno,v_count,v_sumsal;    
  23.     END LOOP;   
  24.         DBMS_OUTPUT.PUT_LINE('--------------------------------------');   
  25.         CLOSE list_cursor;   
  26.         END;  
[sql] view plaincopyprint?
  1. SET SERVEROUTPUT ON   
  2. DECLARE  
  3.  v_deptno number(8);  
  4.  v_count number(3);  
  5.  v_sumsal number(6);  
  6.  v_dname  varchar2(15);  
  7. v_manager  varchar2(15);  
  8.  CURSOR list_cursor IS  
  9.    SELECT deptno,count(*),sum(sal) FROM emp group by deptno;  
  10. BEGIN  
  11.   OPEN list_cursor;   
  12.   DBMS_OUTPUT.PUT_LINE('----------- 部 门 统 计 表 -----------');  
  13. DBMS_OUTPUT.PUT_LINE('部门名称   总人数  总工资   部门经理');  
  14.   FETCH list_cursor INTO v_deptno,v_count,v_sumsal;   
  15.   WHILE list_cursor%found LOOP    
  16.  SELECT dname INTO v_dname FROM dept  
  17.     WHERE deptno=v_deptno;  
  18.     SELECT ename INTO v_manager FROM emp   
  19.     WHERE deptno=v_deptno and job='MANAGER';  
  20. DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)  
  21.       ||rpad(to_char(v_sumsal),9)||v_manager);  
  22.     FETCH list_cursor INTO v_deptno,v_count,v_sumsal;   
  23.     END LOOP;  
  24.         DBMS_OUTPUT.PUT_LINE('--------------------------------------');  
  25.         CLOSE list_cursor;  
  26.         END;  

输出结果为： 
Sql代码  
  1. -------------------- 部 门 统 计 表 -----------------  
  2.         部门名称     总人数  总工资     部门经理   
  3.         ACCOUNTING    3      8750       CLARK   
  4.         RESEARCH      5     10875       JONES   
  5.         SALES             6      9400       BLAKE   
  6.         -------------------------------------------------------------  
  7.         PL/SQL 过程已成功完成。   
[sql] view plaincopyprint?
  1. -------------------- 部 门 统 计 表 -----------------  
  2.         部门名称     总人数  总工资     部门经理  
  3.         ACCOUNTING    3      8750       CLARK  
  4.         RESEARCH      5     10875       JONES  
  5.         SALES             6      9400       BLAKE  
  6.         -------------------------------------------------------------  
  7.         PL/SQL 过程已成功完成。   

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。 
【训练5】  为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。 
输入并调试以下程序： 
Sql代码  
  1. SET SERVEROUTPUT ON    
  2. DECLARE    
  3.   V_NAME CHAR(10);   
  4.   V_EMPNO NUMBER(5);   
  5.   V_SAL NUMBER(8);   
  6.   V_SAL1 NUMBER(8);   
  7.   V_TOTAL NUMBER(8) := 800;     --增加工资的总额  
  8. V_NUM NUMBER(5):=0;     --增加工资的人数  
  9.          CURSOR emp_cursor IS    
  10.           SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;   
  11.         BEGIN  
  12.          OPEN emp_cursor;   
  13.         DBMS_OUTPUT.PUT_LINE('姓名      原工资  新工资');    
  14.         DBMS_OUTPUT.PUT_LINE('---------------------------');    
  15.          LOOP   
  16.             FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;   
  17. EXIT WHEN emp_cursor%NOTFOUND;   
  18.          V_SAL1:= V_SAL*0.1;   
  19.             IF V_TOTAL>V_SAL1 THEN  
  20.             V_TOTAL := V_TOTAL - V_SAL1;   
  21.             V_NUM:=V_NUM+1;   
  22.     DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||   
  23.         TO_CHAR(V_SAL+V_SAL1,'99999'));   
  24.              UPDATE EMP SET SAL=SAL+V_SAL1   
  25.              WHERE EMPNO=V_EMPNO;   
  26.          ELSE  
  27. DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));   
  28.          END IF;   
  29.         END LOOP;   
  30.         DBMS_OUTPUT.PUT_LINE('---------------------------');   
  31.         DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL);     
  32.          CLOSE emp_cursor;    
  33.          COMMIT;   
  34.          END;  
[sql] view plaincopyprint?
  1. SET SERVEROUTPUT ON   
  2. DECLARE   
  3.   V_NAME CHAR(10);  
  4.   V_EMPNO NUMBER(5);  
  5.   V_SAL NUMBER(8);  
  6.   V_SAL1 NUMBER(8);  
  7.   V_TOTAL NUMBER(8) := 800;     --增加工资的总额  
  8. V_NUM NUMBER(5):=0;     --增加工资的人数  
  9.          CURSOR emp_cursor IS   
  10.           SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;  
  11.         BEGIN  
  12.          OPEN emp_cursor;  
  13.         DBMS_OUTPUT.PUT_LINE('姓名      原工资  新工资');   
  14.         DBMS_OUTPUT.PUT_LINE('---------------------------');   
  15.          LOOP  
  16.             FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;  
  17. EXIT WHEN emp_cursor%NOTFOUND;  
  18.          V_SAL1:= V_SAL*0.1;  
  19.             IF V_TOTAL>V_SAL1 THEN  
  20.             V_TOTAL := V_TOTAL - V_SAL1;  
  21.             V_NUM:=V_NUM+1;  
  22.     DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||  
  23.         TO_CHAR(V_SAL+V_SAL1,'99999'));  
  24.              UPDATE EMP SET SAL=SAL+V_SAL1  
  25.              WHERE EMPNO=V_EMPNO;  
  26.          ELSE  
  27. DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));  
  28.          END IF;  
  29.         END LOOP;  
  30.         DBMS_OUTPUT.PUT_LINE('---------------------------');  
  31.         DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL);    
  32.          CLOSE emp_cursor;   
  33.          COMMIT;  
  34.          END;  

输出结果为： 
Sql代码 复制代码 收藏代码
  1. 姓名        原工资  新工资   
  2.         ---------------------------------------------  
  3. SMITH       1289   1418   
  4. JAMES       1531   1684   
  5. MARTIN      1664   1830   
  6. MILLER          1730   1903   
  7. ALLEN           1760   1936   
  8. ADAMS       1771   1771   
  9. TURNER      1815   1815   
  10. WARD        1830   1830   
  11. BLAKE       2850   2850   
  12. CLARK       2850   2850   
  13. JONES           2975   2975   
  14. FORD            3000   3000   
  15. KING            5000   5000   
  16. -----------------------------------------------  
  17. 增加工资人数：5 剩余工资：3   
  18. PL/SQL 过程已成功完成。  
[sql] view plaincopyprint?
  1. 姓名        原工资  新工资  
  2.         ---------------------------------------------  
  3. SMITH       1289   1418  
  4. JAMES       1531   1684  
  5. MARTIN      1664   1830  
  6. MILLER          1730   1903  
  7. ALLEN           1760   1936  
  8. ADAMS       1771   1771  
  9. TURNER      1815   1815  
  10. WARD        1830   1830  
  11. BLAKE       2850   2850  
  12. CLARK       2850   2850  
  13. JONES           2975   2975  
  14. FORD            3000   3000  
  15. KING            5000   5000  
  16. -----------------------------------------------  
  17. 增加工资人数：5 剩余工资：3  
  18. PL/SQL 过程已成功完成。  
分享到：
| 高性能jdbc封装工具 Apache Commons DbUtil ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle 游标 深入浅出 详解 精析 示例

    Oracle游标是数据库管理系统中的一种重要机制，它允许程序员逐行处理查询结果集，而不仅仅是一次性获取所有数据。游标类似于C语言中的指针，能够灵活、高效地处理多条记录，尤其在需要循环处理或者根据当前行数据做...

    Oracle游标使用详解

    根据提供的标题、描述以及部分代码内容，我们可以详细探讨Oracle游标的使用方法，特别是明确游标（Explicit Cursor）和隐式游标（Implicit Cursor）的区别及其具体应用方式。 ### Oracle游标简介 在Oracle数据库中...

    Oracle 游标 Oracle 游标

    ### Oracle游标详解 在Oracle数据库中，游标（Cursor）是处理数据集的一个关键机制，主要用于执行查询并逐行处理结果集。游标可以被视为一个临时存储区，用于保存SELECT语句的结果集，允许程序对这些结果进行迭代...

    Oracle游标使用大全

    ### Oracle游标使用详解 #### 一、Oracle游标简介 在Oracle数据库中，游标是一种重要的机制，用于处理查询结果集。它允许用户通过PL/SQL编程语言逐行访问和处理查询返回的数据记录。游标可以是显式定义的（即在...

    oracle游标使用及实例

    ### Oracle游标使用及实例详解 #### 一、Oracle游标概述 在Oracle数据库中，游标(Cursor)是一种用于处理SQL查询结果集的方式。它允许用户逐行地读取和处理查询结果，这对于需要对每一行数据进行特定操作的情况非常...

    Oracle游标使用方法及语法大全.doc

    ### Oracle游标使用详解 #### 一、游标概述 游标是Oracle数据库中用于处理查询结果集的强大工具，尤其适用于需要逐行处理查询结果的情况。在Oracle中，游标可以分为两类：**显式游标**和**隐式游标**。 1. **隐式...

    Oracle游标大全

    ### Oracle游标详解 #### 一、概述 在Oracle数据库中，游标是一种重要的机制，用于处理查询结果集。游标允许用户通过编程的方式逐一访问查询结果中的每一行数据，而不是一次性加载所有数据到内存中。这使得游标...

    oracle游标使用大全1.txt

    ### Oracle游标使用详解 #### 一、Oracle游标简介 在Oracle数据库中，游标是一种用于处理查询结果集的强大工具。它允许用户通过逐行访问数据来执行复杂的操作，如更新、删除或插入记录等。游标可以分为显式游标和...

    Oracle存储过程游标详解

    "Oracle存储过程游标详解" Oracle 存储过程游标是指在 Oracle 数据库中使用游标来实现对结果集的处理和操作。游标可以分为静态游标和REF游标两种类型。静态游标是指结果集已经确实（静态定义）的游标，可以进一步...

    ORACLE 游标

    ### ORACLE 游标详解 在Oracle数据库中，游标是一种强大的工具，用于处理查询结果集，特别是当结果集包含多行数据时。游标允许我们一行行地处理查询结果，这在处理大量数据或需要对每行结果进行特定操作时尤为有用...

    oracle游标大全

    ### Oracle游标详解 #### 一、概述 在Oracle数据库中，游标是一种重要的机制，用于处理查询结果集。在PL/SQL存储过程中，游标主要用于处理多行数据记录，这是SELECT INTO语句无法实现的功能。本文将详细介绍Oracle...

    Oracle存储过程、游标、函数的详解

    ### Oracle存储过程、游标、函数的详解 #### 一、概述 在Oracle数据库中，存储过程、游标和函数是非常重要的组成部分，它们为数据库管理提供了强大的编程能力。通过学习这些概念，我们可以更加灵活地管理和操作...

    oracle 隐式游标,显示游标,游标循环

    ### Oracle中的游标详解 #### 一、引言 在Oracle数据库中，游标是一种非常重要的机制，它允许用户在程序中对查询结果进行逐行处理。游标分为两种主要类型：**隐式游标**和**显示游标**。此外，还可以利用游标进行...

    oracle游标和异常处理

    ### Oracle游标与异常处理详解 #### 一、Oracle游标概述 **游标**是Oracle SQL中的一个重要概念，主要用于处理从数据库中提取的数据。它提供了在程序中逐行访问和处理查询结果的能力。 ##### 1.1 游标概念 - **...

    oracle游标介绍

    ### Oracle游标详解 在Oracle数据库的编程环境中，游标是一种强大的工具，用于处理查询结果集，特别是当结果集包含多行数据时。游标允许我们一行行地访问查询结果，进行更细致的数据操作和控制。 #### 游标的基础...

    详解Oracle游标的简易用法

    Oracle游标是数据库操作中的一种重要工具，它允许我们逐行处理查询结果，尤其在进行复杂的业务逻辑处理时，游标显得尤为重要。本篇将详细解释Oracle游标的简易用法，并通过实例代码来演示如何使用。 首先，游标定义...

    存储过程和游标详解

    ### 存储过程和游标详解 #### 一、存储过程概述 存储过程，作为数据库管理系统(DBMS)中的一种重要特性，尤其在Oracle等关系型数据库中被广泛应用。它允许开发者将一系列SQL语句和控制流语句组合在一起，封装为一个...

    Oracle游标的使用实例详解

    Oracle游标是数据库管理系统中的一种重要机制，它允许应用程序按需逐行处理查询结果集，而不是一次性处理所有数据。游标对于交互式和事务性的应用尤其有用，因为它们可以控制数据流并根据需要进行交互。 首先，让...

    Oracle 游标使用大

    本文旨在为初学者提供关于Oracle游标使用的全面指南。 #### 二、基本概念 **1. 显式游标** - **定义**：显式游标是指在PL/SQL块中明确声明并由用户管理的游标。 - **用途**：适用于需要处理多行数据的情况，特别...

    oracle 游标注入

    ### Oracle游标注入详解 #### 引言 随着数据库安全问题日益受到重视，攻击者针对数据库系统的攻击手段也在不断进化。在Oracle数据库中，一种新型的攻击方式——**游标注入**，允许攻击者在无需创建过程或函数的...

Magicbox
Global site tag (gtag.js) - Google Analytics