PLSQL游标使用实例

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ibmpcclient/article/details/52235522

一、定义:

游标:指向查询结果集的指针,指向哪一行,提取哪一行的数据(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 ;












































































































































































































































































































































































































































































阅读更多
换一批

没有更多推荐了,返回首页