plsql 循环语句_plsql调用存储过程

13PLSQL–游标 　　2.游标 　　定义： 　　–游标是存储SQL语句影响的数据（虚拟的表） 　　–游标指针是指向查询结果集的一个指针，它是一个通过定义语句与一条Select语句相关联的一组SQL语句。 　　–游标结果集，执行其中的Select语句所得到的结果集。 　　–游标位置，指向游标结果集内的某一条记录的指针 　　–利用游标可以单独操纵结果集中的每一行，即游标可以把集合操作转换成对单个记录进行不同处理的方式。 　　–游标实际上作为面向集合的RDBMS和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来 　　2.1 隐式游标 　　说明： 　　非查询语句 –隐式游标 　　结果是单行的查询语句 –隐式游标或者显示游标 　　结果是多行的查询语句 –显示游标 　　–判断游标是否被打开,布尔值。 　　sql%isopen 　　–是否找到符合条件的记录,布尔值。 　　sql%found –while循环取数据 　　或 　　sql%notfound –loop循环取数据 　　–当前指针到的行计数，即行号 　　sql%rowcount 　　—————–sql%isopen—————————————- 　　begin 　　if sql%isopen then 　　dbms_output.put_line(‘隐式游标打开了’); 　　else 　　dbms_output.put_line(‘隐式游标关闭了’); 　　end if; 　　update emp set sal=sal+555 where empno=7788; 　　if sql%isopen then 　　dbms_output.put_line(‘隐式游标打开了’); 　　else 　　dbms_output.put_line(‘隐式游标关闭了’); 　　end if; 　　end; 　　–隐式游标被Oracle自动关闭（初始时为关闭状态），执行增删改游标自动打开，执行完毕又自动关闭。 　　所以输出结果为： 　　隐式游标关闭了 　　隐式游标关闭了 　　PL/SQL procedure successfully completed 　　——————–sql%found或sql%notfound—————————– 　　begin 　　update emp set sal=sal+550 where empno=7788; 　　if sql%found then 　　dbms_output.put_line(‘更新了数据’); 　　else 　　dbms_output.put_line(‘没有数据更新到’); 　　end if; 　　end; 　　SQL%FOUND是增删改查语句影响到有行时为真，否则为假 　　————————————sql%rowcount—————————– 　　begin 　　update emp set sal=sal+555 where empno=; 　　if sql%rowcount>0 then 　　dbms_output.put_line(‘更新到了’); 　　else 　　dbms_output.put_line(‘没有更新到数据’); 　　end if; 　　end; 　　sql%rowcount影响的记录行数，游标执行前该属性值为NULL，执行后为一个非0数字。 　　2.2 显示游标 　　–声明游标 　　CURSOR cursor_name is select_statement –关联SQL语句 　　–打开游标 　　OPEN cursor_name 　　–通过游标数据，fetch会自动移动游标指针 　　FETCH cursor_name INTO var_name [, var_name] …–执行SQL语句 　　–关闭游标 　　CLOSE cursor_name 　　–使用hr/登录，employees表数据 　　–查询前10名员工的信息 　　方法一：隐式游标 　　BEGIN 　　FOR c IN (SELECT * FROM employees WHERE ROWNUM<=10) LOOP 　　DBMS_OUTPUT.PUT_LINE(c.employee_id||’ ‘||c.first_name||’ ‘||c.last_name); 　　END LOOP; 　　END; 　　方法二：定义游标 　　————————使用while循环—————————————– 　　DECLARE 　　–定义游标:就是定义一个游标名，以及与其相对应的 SELECT 语句。 　　CURSOR C_CURSOR IS SELECT employee_id , first_name FROM EMPLOYEES WHERE ROWNUM<=10; 　　–定义变量 　　v_name employees.first_name %TYPE; 　　v_id employees.employee_id %TYPE; 　　BEGIN 　　–打开游标:就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。 　　OPEN C_CURSOR; 　　–提取游标数据:就是检索结果集合中的数据行，放入指定的输出变量中。 　　FETCH C_CURSOR INTO v_id,v_name; 　　–while循环取数据 　　WHILE C_CURSOR %FOUND LOOP 　　DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_name); 　　FETCH C_CURSOR INTO v_id,v_name; 　　–循环结束 　　END LOOP; 　　–关闭游标 　　CLOSE C_CURSOR; 　　END; 　　———————使用LOOP循环———————– 　　DECLARE 　　–定义游标:就是定义一个游标名，以及与其相对应的 SELECT 语句。 　　CURSOR C_CURSOR IS SELECT employee_id , first_name FROM EMPLOYEES WHERE ROWNUM<=10; 　　–定义变量 　　v_name employees.first_name %TYPE; 　　v_id employees.employee_id %TYPE; 　　BEGIN 　　–打开游标:就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。 　　OPEN C_CURSOR; 　　–使用LOOP循环 　　LOOP 　　–提取游标数据:就是检索结果集合中的数据行，放入指定的输出变量中。 　　FETCH C_CURSOR INTO v_id,v_name; 　　–打印输出结果 　　DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_name); 　　–设定退出条件 　　EXIT WHEN C_CURSOR %NOTFOUND; 　　–循环结束 　　END LOOP; 　　–关闭游标 　　CLOSE C_CURSOR; 　　END; 　　———————–使用for 循环 ———————- 　　–PL/SQL 语言提供了游标 FOR 循环语句，自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能； 　　注意事项; 　　如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标 FOR 循环语句中的索引变量来访问这些列数据 　　DECLARE 　　–定义游标:就是定义一个游标名，以及与其相对应的 SELECT 语句。 　　CURSOR C_CURSOR IS SELECT employee_id , first_name FROM EMPLOYEES WHERE ROWNUM<=10; 　　BEGIN 　　FOR C IN C_CURSOR LOOP 　　–打印输出结果 　　DBMS_OUTPUT.PUT_LINE(C_CURSOR%ROWCOUNT||’ ‘||C.employee_id||’ ‘||C.first_name); 　　–循环结束 　　END LOOP; 　　END; 　　–游标参数传递，在指定数据类型时，不能使用长度约束。 　　DECLARE 　　–定义游标:就是定义一个游标名，以及与其相对应的 SELECT 语句。 　　–游标参数只能为输入参数，在指定数据类型时，不能使用长度约束。 　　CURSOR C_CURSOR(emp_no number default 10) IS SELECT employee_id , first_name FROM EMPLOYEES WHERE ROWNUM<=emp_no; 　　BEGIN 　　FOR C IN C_CURSOR LOOP 　　–打印输出结果 　　DBMS_OUTPUT.PUT_LINE(C.employee_id||’ ‘||C.first_name); 　　–循环结束 　　END LOOP; 　　END; 　　– 　　DECLARE 　　CURSOR C_CURSOR (dept_no NUMBER) IS SELECT * FROM EMP WHERE deptno=dept_no; 　　BEGIN 　　FOR C IN C_CURSOR(20) LOOP 　　DBMS_OUTPUT.PUT_LINE(C.ename||’ ‘||c.job||’ ‘||c.sal); 　　END LOOP; 　　END; 　　2.3游标更新 　　定义：游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中 　　必须使用 FOR UPDATE 选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列 　　语法：SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT] 　　–从 EMPLOYEES 表中查询某部门的员工情况，将其工资最低定为 3000； 　　DECLARE 　　v_dept emp.deptno%TYPE :=&deptno; 　　CURSOR C_CURSOR IS SELECT * FROM emp WHERE deptno= v_dept FOR UPDATE; –FOR UPDATE 　　BEGIN 　　FOR C IN C_CURSOR LOOP 　　IF C.sal <3000 THEN 　　UPDATE EMP SET SAL=3000 WHERE CURRENT OF C_CURSOR; 　　END IF; 　　END LOOP; 　　END; 　　– 将10号部门的员工按先后到公司的时间进行涨薪，每早来一个多加100，第一个加100。 　　DECLARE 　　– 定义更新游标: 　　CURSOR curemp 　　IS 　　SELECT * FROM emp 　　WHERE deptno=10 　　FOR UPDATE — 通过FOR UPDATE，将查询出的记录进行锁定，不允许其他用户修改游标所在的记录 　　ORDER BY hiredate DESC; 　　– 工资涨幅变量 　　addvision NUMBER(7,2):=100; 　　BEGIN 　　– 使用游标FOR循环更新游标当前记录 　　FOR m IN curemp LOOP 　　– 更新游标当前记录 　　UPDATE emp SET sal=sal+addvision WHERE CURRENT OF curemp; 　　addvision:=addvision+100; 　　END LOOP; 　　– 提交事务：释放锁 　　COMMIT; 　　END; 　　–案例2：根据入职日期先后顺序，将偶数行删除。 　　– 删除游标行 　　– 删除 　　DECLARE 　　– 定义更新游标: 　　CURSOR curemp 　　IS 　　SELECT * FROM emp 　　FOR UPDATE — 通过FOR UPDATE，将查询出的记录进行锁定，不允许其他用户修改游标所在的记录 　　ORDER BY hiredate DESC; 　　BEGIN 　　– 使用游标FOR循环更新游标当前记录 　　FOR m IN curemp 　　LOOP 　　IF MOD(curemp%ROWCOUNT,2)=0 THEN 　　– 删除游标当前记录 　　DELETE FROM emp WHERE CURRENT OF curemp; 　　END IF; 　　END LOOP; 　　– 提交事务：释放锁 　　COMMIT; 　　END; 　　/ 　　–删除指定部门的偶数行 　　DECLARE 　　CURSOR C_CURSOR IS SELECT * FROM emp where deptno=&deptno FOR UPDATE; 　　BEGIN 　　FOR C IN C_CURSOR LOOP 　　IF MOD(C_CURSOR%ROWCOUNT,2)=0 THEN 　　DELETE FROM emp WHERE CURRENT OF C_CURSOR; 　　END IF; 　　END LOOP; 　　END; 　　2.4 REF引用游标 　　先定义游标数据类型，然后再定义基于这种类型的游标变量 　　分类： 　　强型游标：定义游标类型时，强制性指定返回某个表的行集 　　弱型游标：定义时未指定行集，可以是任意表的行集，较灵活 　　案例：编写PL/SQL程序块，通过REF游标，输出员工表和部门表的明细信息。 　　– 弱型游标 　　DECLARE 　　– 定义游标类型 　　TYPE curtype IS REF CURSOR; 　　– 定义基于游标类型的变量 　　curcom CURTYPE; 　　– 定义行集变量 　　vemp emp%ROWTYPE; 　　vdept dept%ROWTYPE; 　　BEGIN 　　– 打开基于emp的引用游标，并输出结果 　　OPEN curcom FOR SELECT * FROM emp; 　　– 遍历游标并输出结果 　　LOOP 　　–提取游标 　　FETCH curcom INTO vemp; 　　EXIT WHEN curcom%NOTFOUND; — 退出循环判断 　　– 输出游标记录行 　　DBMS_OUTPUT.PUT_LINE(curcom%ROWCOUNT || ‘ ‘|| vemp.ename || ‘ ‘ || vemp.sal); 　　END LOOP; 　　– 关闭游标 　　CLOSE curcom; 　　– 打开基于dept部门表的引用游标，并输出结果 　　OPEN curcom FOR SELECT * FROM dept; 　　– 遍历游标并输出结果 　　LOOP 　　–提取游标 　　FETCH curcom INTO vdept; 　　EXIT WHEN curcom%NOTFOUND; — 退出循环判断 　　– 输出游标记录行 　　DBMS_OUTPUT.PUT_LINE(curcom%ROWCOUNT || ‘ ‘|| vdept.dname || ‘ ‘ || vdept.loc); 　　END LOOP; 　　– 关闭游标 　　CLOSE curcom; 　　END; 　　/ 　　– 强型游标 　　DECLARE 　　– 定义游标类型 　　TYPE curtype IS REF CURSOR RETURN emp%ROWTYPE; —指定返回表的行集 　　– 定义基于游标类型的变量 　　curcom CURTYPE; 　　– 定义行集变量 　　vemp emp%ROWTYPE; 　　vdept dept%ROWTYPE; 　　BEGIN 　　– 打开基于emp的引用游标，并输出结果 　　OPEN curcom FOR SELECT * FROM emp; 　　– 遍历游标并输出结果 　　LOOP 　　–提取游标 　　FETCH curcom INTO vemp; 　　EXIT WHEN curcom%NOTFOUND; — 退出循环判断 　　– 输出游标记录行 　　DBMS_OUTPUT.PUT_LINE(curcom%ROWCOUNT || ‘ ‘|| vemp.ename || ‘ ‘ || vemp.sal); 　　END LOOP; 　　– 关闭游标 　　CLOSE curcom; 　　/* 　　– 打开基于dept部门表的引用游标，并输出结果 　　OPEN curcom FOR SELECT * FROM dept; 　　– 遍历游标并输出结果 　　LOOP 　　–提取游标 　　FETCH curcom INTO vdept; 　　EXIT WHEN curcom%NOTFOUND; — 退出循环判断 　　– 输出游标记录行 　　DBMS_OUTPUT.PUT_LINE(curcom%ROWCOUNT || ‘ ‘|| vdept.dname || ‘ ‘ || vdept.loc); 　　END LOOP; 　　– 关闭游标 　　CLOSE curcom; 　　*/ 　　END; 　　/ 　　————————–使用强型游标输出EMP表信息——————————– 　　DECLARE 　　–定义强型游标类型 　　TYPE cur_type IS REF CURSOR RETURN emp%ROWTYPE; 　　–定义游标变量 　　emp_cur cur_type; 　　–定义行集变量 　　v_emp emp%ROWTYPE; 　　BEGIN 　　–打开基于emp的引用游标，并输出结果 　　OPEN emp_cur FOR SELECT * FROM emp; 　　–提取游标 　　FETCH emp_cur into v_emp; 　　–WHILE循环遍历游标 　　WHILE emp_cur %found LOOP 　　DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ ‘||v_emp.job); 　　FETCH emp_cur into v_emp; 　　END LOOP; 　　–关闭游标 　　CLOSE emp_cur; 　　END; 　　—————————–使用弱型游标输出表EMP,表DEPT信息——————- 　　DECLARE 　　–定义游标类型 　　TYPE cur_type IS REF CURSOR; 　　–定义游标变量 　　v_cur cur_type; 　　–定义行集变量 　　v_emp emp%ROWTYPE; 　　v_dept dept%ROWTYPE; 　　–声明个错误类型 　　ERROR EXCEPTION; 　　–与错误代码绑定 　　pragma exception_init(ERROR,-1400); 　　BEGIN 　　–打开游标 　　OPEN v_cur FOR SELECT * FROM emp; 　　–使用LOOP循环，提取数据 　　LOOP 　　–提取游标 　　FETCH v_cur into v_emp; 　　–DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ ‘||v_emp.job||’ ‘||v_emp.sal ); 　　–退出循环判断 　　EXIT WHEN v_cur %notfound ; 　　DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ ‘||v_emp.job||’ ‘||v_emp.sal ); 　　–结束循环 　　END LOOP; 　　–关闭游标 　　CLOSE v_cur; 　　DBMS_OUTPUT.PUT_LINE(‘*’); 　　–打开游标，dept数据 　　OPEN v_cur FOR SELECT * FROM dept; 　　–使用while循环遍历数据 　　–提取游标 　　FETCH v_cur into v_dept; 　　WHILE v_cur %found loop 　　DBMS_OUTPUT.PUT_LINE(v_cur%ROWCOUNT||’ ‘||v_dept.deptno||’ ‘||v_dept.dname||’ ‘||v_dept.loc); 　　FETCH v_cur into v_dept; 　　END LOOP; 　　–关闭游标 　　CLOSE v_cur; 　　EXCEPTION 　　WHEN ERROR THEN 　　DBMS_OUTPUT.PUT_LINE(SQLCODE||’ ‘||SQLERRM); 　　WHEN OTHERS THEN 　　DBMS_OUTPUT.PUT_LINE(‘出现其他错误’); 　　END; 　　———————————-问题——————————————— 　　DECLARE 　　–定义游标类型 　　TYPE cur_type IS REF CURSOR; 　　–定义游标变量 　　v_cur cur_type; 　　–定义行集变量 　　v_emp emp%ROWTYPE; 　　v_dept dept%ROWTYPE; 　　–声明个错误类型 　　ERROR EXCEPTION; 　　–与错误代码绑定 　　pragma exception_init(ERROR,-1400); 　　BEGIN 　　–打开游标 　　OPEN v_cur FOR SELECT * FROM emp; 　　–使用LOOP循环，提取数据 　　LOOP 　　–提取游标 　　FETCH v_cur into v_emp; 　　–DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ ‘||v_emp.job||’ ‘||v_emp.sal );–最后一条记录被多记录一条 　　–退出循环判断 　　EXIT WHEN v_cur %notfound ; 　　DBMS_OUTPUT.PUT_LINE(v_emp.ename||’ ‘||v_emp.job||’ ‘||v_emp.sal ); 　　–结束循环 　　END LOOP; 　　–关闭游标 　　CLOSE v_cur; 　　EXCEPTION 　　WHEN ERROR THEN 　　DBMS_OUTPUT.PUT_LINE(SQLCODE||’ ‘||SQLERRM); 　　WHEN OTHERS THEN 　　DBMS_OUTPUT.PUT_LINE(‘出现其他错误’); 　　END;