PLSQL游标使用实例

落日映苍穹つ 2022-05-29 11:54 329阅读 0赞

一、定义：

游标：指向查询结果集的指针，指向哪一行，提取哪一行的数据(PLSQL的游标默认指向结果集的第一行)

游标的四个属性  
游标变量 %found: 当最近一次读入记录成功时返回true  
游标变量 %notfound:同上 相反  
游标变量 %isopen:判断游标是否已经打开

游标变量 %rowcount:返回已从游标中读取的记录数

隐示游标：固定名称sql  
游标的四个属性  
SQL%FOUND:如果操作有影响行，就为true，否则为false  
SQL%NOTFOUND :求反  
SQL%ISOPEN:在隐示游标中，取值永远为false  
SQL%ROWCOUNT:操作影响的行数

必须在事务结束之前读取游标属性，只能读取最近的一次DML操作的游标状态

二、使用步骤  
使用游标分为4个步骤  
1，声明游标 cursor 游标变量 is 查询语句  
2，打开游标 open 游标变量（不能重复打开游标）  
3，提取数据 fetch 游标变量 into 变量1，变量2,.  
4，关闭游标释放系统资源 close 游标变量

请参考以下实例：

(1)查询输出所有员工的编号，姓名，工资|  
DECLARE  
    v\_empid employees.employee\_id%TYPE;  
    v\_name  employees.last\_name%TYPE;           
    v\_salary employees.salary%TYPE;

CURSOR  emp\_cursor IS  
    SELECT  employee\_id,last\_name,salary  
    FROM    employees  
    WHERE   department\_id = 90;  
BEGIN  
    OPEN    emp\_cursor;  
    FETCH   emp\_cursor INTO v\_empid ,v\_name, v\_salary;  
    dbms\_output.put\_line('编号'|| v\_empid);  
    dbms\_output.put\_line('姓名'|| v\_name);  
    dbms\_output.put\_line('工资'|| v\_salary);  
    CLOSE emp\_cursor;  
END;

(2)查询输出所有员工的编号，姓名，工资

DECLARE  
    v\_empid employees.employee\_id%TYPE;  
    v\_name employees.last\_name%TYPE;  
    v\_salary employees.salary%TYPE;  
      
    CURSOR emp\_cursor IS  
    SELECT employee\_id,last\_name,salary  
    FROM employees  
    WHERE department\_id = 90;  
  
  
BEGIN  
    OPEN emp\_cursor;  
    FETCH  emp\_cursor INTO v\_empid ,v\_name, v\_salary;  
    WHILE emp\_cursor%FOUND LOOP  
    dbms\_output.put\_line('编号'|| v\_empid);  
    dbms\_output.put\_line('姓名'|| v\_name);  
    dbms\_output.put\_line('工资'|| v\_salary);  
    dbms\_output.put\_line('-------------------');  
    FETCH  emp\_cursor INTO v\_empid ,v\_name, v\_salary;  
    END LOOP;  
    CLOSE emp\_cursor;  
END;

(3)查询输出所有员工编号，姓名，工资（记录类型）  
  
DECLARE  
    e employees%ROWTYPE;  
    CURSOR emp\_cursor IS  
    SELECT \*  
    FROM employees;  
BEGIN  
     OPEN   emp\_cursor;  
      FETCH emp\_cursor INTO e;  
      WHILE emp\_cursor%FOUND LOOP  
      dbms\_output.put\_line('编号' || e.employee\_id);  
      dbms\_output.put\_line('姓名' || e.last\_name);  
      dbms\_output.put\_line('工资' || e.salary);  
      dbms\_output.put\_line('职务' || e.job\_id);  
      dbms\_output.put\_line('入职日期' || e.hire\_date);  
      dbms\_output.put\_line('-----------------------');  
      FETCH emp\_cursor INTO e;  
      END LOOP;  
      CLOSE emp\_cursor;  
END;

(4)查询输出所有员工编号，姓名，工资（PLSQL表类型）  
  
DECLARE  
     TYPE emp\_table\_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY\_INTEGER;  
  
  
     e emp\_table\_type;  
  
  
    CURSOR emp\_cursor IS  
    SELECT \* FROM employees;  
BEGIN  
    OPEN emp\_cursor;  
    FETCH emp\_cursor BULK COLLECT  
    INTO e;  
    FOR i IN 1 .. e.count LOOP  
    dbms\_output.put\_line('编号' || e(i).employee\_id);  
    dbms\_output.put\_line('姓名' || e(i).last\_name);  
    dbms\_output.put\_line('工资' || e(i).salary);  
    dbms\_output.put\_line('----------------------------');  
    END LOOP;  
    CLOSE emp\_cursor;  
END;

(5)查询输出所有员工编号，姓名，工资（通过limit控制提取的数据量）

DECLARE  
  TYPE emp\_table\_type IS TABLE OF employees%ROWTYPE  
  INDEX BY BINARY\_INTEGER;  
  e emp\_table\_type;  
  CURSOR emp\_cursor IS  
  SELECT \* FROM employees;  
BEGIN   
   OPEN emp\_cursor;  
   FETCH emp\_cursor BULK COLLECT INTO e LIMIT 6 ;  
     
   FOR i IN 1..e.count LOOP  
    dbms\_output.put\_line('编号' || e(i).employee\_id);  
    dbms\_output.put\_line('姓名' || e(i).last\_name);  
    dbms\_output.put\_line('工资' || e(i).salary);  
    dbms\_output.put\_line('----------------------------');  
     END LOOP;  
CLOSE emp\_cursor;  
END;

(6)查询输出某个部门的员工的编号，姓名，工资（参数化游标）  
  
DECLARE  
  v\_empid employees.employee\_id%TYPE;  
  v\_name employees.last\_name%TYPE;  
  v\_salary employees.salary%TYPE;  
  CURSOR emp\_cursor(p\_deptid employees.employee\_id%TYPE) IS  
  SELECT employee\_id,last\_name,salary  
  FROM employees  
 WHERE department\_id = p\_deptid;   
BEGIN  
   OPEN emp\_cursor(60);  
   LOOP   
   FETCH emp\_cursor  INTO v\_empid,v\_name,v\_salary ;  
   EXIT WHEN emp\_cursor%NOTFOUND;  
   dbms\_output.put\_line('编号：'||v\_empid||'姓名：'||v\_name||'工资：'||v\_salary);  
   END LOOP;  
  CLOSE emp\_cursor;  
  dbms\_output.put\_line('----------------------------');  
  OPEN emp\_cursor(50);  
  LOOP  
    FETCH emp\_cursor  INTO v\_empid,v\_name,v\_salary ;  
    EXIT WHEN emp\_cursor%NOTFOUND;  
    dbms\_output.put\_line('编号：'||v\_empid||'姓名：'||v\_name||'工资：'||v\_salary);  
    END LOOP;  
    CLOSE emp\_cursor;  
END;

(7)查询输出所有的员工编号，姓名，工资(游标的for循环)  
  
DECLARE  
    CURSOR emp\_cursor IS  
    SELECT \*   
    FROM employees;  
BEGIN   
  FOR e IN emp\_cursor LOOP  
    dbms\_output.put\_line('编号：'||e.employee\_id||'姓名：'||e.last\_name||'工资：'||e.salary);  
  END LOOP;  
END;  
  
(8)查询输出所有的员工编号，姓名，工资（带参数）

DECLARE  
    CURSOR emp\_cursor(p\_depid employees.employee\_id%TYPE)IS  
    SELECT \*   
    FROM employees  
    WHERE department\_id=p\_depid;  
BEGIN   
  FOR e IN emp\_cursor(50) LOOP  
    dbms\_output.put\_line('编号：'||e.employee\_id||'姓名：'||e.last\_name||'工资：'||e.salary);  
  END LOOP;  
END;

(9)查询输出所有的员工编号，姓名，工资（最精简的写法）  
  
DECLARE  
  
  
BEGIN   
  FOR e IN (SELECT \* FROM employees) LOOP  
    dbms\_output.put\_line('编号：'||e.employee\_id||'姓名：'||e.last\_name||'工资：'||e.salary);  
  END LOOP;  
  
  
END;

(10)输出每个部门的部门编号，部门名称以及这个部门的下属员工的编号，姓名，工资

DECLARE  
      CURSOR dept\_cursor IS  
      SELECT \* FROM departments;  
        
      CURSOR emp\_cursor(p\_deptid NUMBER) IS  
      SELECT \* FROM employees  
      WHERE department\_id = p\_deptid;  
BEGIN   
     FOR d IN dept\_cursor LOOP  
     dbms\_output.put\_line(d.department\_id|| '  '||d.department\_name);  
     FOR e IN emp\_cursor(d.department\_id) LOOP  
     dbms\_output.put\_line('   '||e.employee\_id||', '||e.last\_name|| ', '|| e.salary);  
     END LOOP;  
     END LOOP;  
END;  
  
(11)用户输入一个任意部门的编号，更新这个部门的员工工资  
     如果有员工更新，输出‘更新成果，有XX个员工被更新’  
     如果没有员工更新，输出‘这个部门不存在，没有任何员工被更新’  
  
DECLARE  
      v\_deptid NUMBER :=&input;  
BEGIN  
  UPDATE employees  
  SET salary = salary +1  
  WHERE department\_id = v\_deptid;  
   
  IF SQL%FOUND THEN  
    dbms\_output.put\_line('更新成！有'||SQL%ROWCOUNT ||'个员工被更新');  
    ELSE  
    dbms\_output.put\_line('这个部门不存在，没有任何员工被更新');  
    END IF;  
    COMMIT;  
END;

(12)通过游标去更新员工的工资，如果低于5000块，则把工资改为5000  
  
DECLARE  
    CURSOR emp\_cursor IS  
    SELECT \* FROM new\_emp FOR UPDATE;  
BEGIN  
  FOR e IN emp\_cursor LOOP  
    IF e.salary<8300 THEN  
      UPDATE new\_emp SET salary =8300 WHERE CURRENT OF emp\_cursor;  
    END IF;  
    
  END LOOP;  
    
END;  
  
  
(13)通过游标删除工资=5000的员工  
  
DECLARE  
    CURSOR emp\_cursor IS  
    SELECT \* FROM new\_emp FOR UPDATE;  
BEGIN  
  FOR e IN emp\_cursor LOOP  
    IF e.salary = 6000 THEN  
      DELETE FROM new\_emp WHERE CURRENT OF emp\_cursor;  
      END IF;  
  END LOOP;  
  
  
END;

(14)用户输入一个字母，输入E，输出所有员工姓名，如果输出D，输出所有部门名称（游标变量（动态游标））  
  
DECLARE  
      v\_cmd CHAR(1):='&input';  
     v\_name VARCHAR2(50);  
     //声明自定义的游标变量类型  
     TYPE c\_type IS REF CURSOR;  
    //声明变量  
    c c\_type;  
    c SYS\_REFCURSOR;--相当于 c\_type is ref cursor  
BEGIN  
  IF v\_cmd = 'E' THEN  
     dbms\_output.put\_line('员工姓名');  
      OPEN c FOR  
      SELECT last\_name FROM employees;  
  ELSIF v\_cmd = 'D' THEN  
     dbms\_output.put\_line('部门名称');  
     O PEN c FOR  
      SELECT department\_name FROM departments;  
  ELSE  
    dbms\_output.put\_line('请正确输入');  
    RETURN;  
    END IF;  
  LOOP  
    FETCH  c  
     INTO  v\_name;  
     EXIT  WHEN c%NOTFOUND;  
     dbms\_output.put\_line(v\_name);  
   END ;