PL学习-curosr

时间：2020-05-17 13:55:21 阅读：81 评论：0 收藏：0 [点我收藏+]

学习文档

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/static-sql.html#GUID-A2E4086F-94DC-4CC7-9E4B-30285BEC3313

cursor分类

隐形cursor

显性cursor

ref cursor(cursor variable)

显性cursor和ref cursor又被称为named cursor

cursor的属性

ISOPEN Attribute: Is the Cursor Open?

FOUND Attribute: Were Any Rows Affected?

NOTFOUND Attribute: Were No Rows Affected?

ROWCOUNT Attribute: How Many Rows Were Affected?

下面两个属性是forall独有的

SQL%BULK_ROWCOUNT

SQL%BULK_EXCEPTIONS

隐形cursor

被plsql管理，跑select或者dml时plsql open
可以通过SQL%xxxxx 获取属性

显性cursor

显性curosr只处理query语句（cursor variable 也是）。

对于dml语句，需要交给隐形cursor，或者execute immeidate

显性cursor支持参数，cursor variable不支持的

申明与定义

申明：

CURSOR cursor_name [ parameter_list ] RETURN return_type;

技术分享图片

这里的return字句不是可选的，也就是只有带return字句显性cursor才能单独声明

可以不用单独申明一个显性cursor

定义：

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
 IS select_statement;

这里return是可选的

技术分享图片

select_statement可以引用前边的变量。

技术分享图片

从语法上看，参数模式只有in

支持默认直

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  CURSOR c1 IS
    SELECT salary, salary*factor FROM employees
    WHERE job_id LIKE ‘AD_%‘;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE(‘factor = ‘ || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘sal          = ‘ || sal);
    DBMS_OUTPUT.PUT_LINE(‘sal_multiple = ‘ || sal_multiple);
  END LOOP;
  CLOSE c1;
 
  factor := factor + 1;
 
  DBMS_OUTPUT.PUT_LINE(‘factor = ‘ || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘sal          = ‘ || sal);
    DBMS_OUTPUT.PUT_LINE(‘sal_multiple = ‘ || sal_multiple);
  END LOOP;
  CLOSE c1;
END;
/

open

技术分享图片

基本上，pl将open语句当作调用子程序来处理

支持default，支持位置传参，名字传参

显性cursor的open语句同游标变量的open不通用

fetch

fetch语句是通用的

技术分享图片

这里看，host_cursor_variable在java里作入参

FETCH cursor_name INTO into_clause

DECLARE
  TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  CURSOR c1 IS
    SELECT employee_id
    FROM employees
    WHERE department_id = 80
    ORDER BY employee_id;

  empids  numtab;
BEGIN
  OPEN c1;
  LOOP  -- Fetch 10 rows or fewer in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT 10;
    DBMS_OUTPUT.PUT_LINE (‘------- Results from One Bulk Fetch --------‘);
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE (‘Employee Id: ‘ || empids(i));
    END LOOP;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;
/

for语句

技术分享图片

BEGIN
  FOR item IN (
    SELECT last_name, job_id
    FROM employees
    WHERE job_id LIKE ‘%CLERK%‘
    AND manager_id > 120
    ORDER BY last_name
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE
      (‘Name = ‘ || item.last_name || ‘, Job = ‘ || item.job_id);
  END LOOP;
END;
/
 

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
    WHERE job_id = job
    AND salary > max_wage;
BEGIN
  FOR person IN c1(‘ST_CLERK‘, 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE (
      ‘Name = ‘ || person.last_name || ‘, salary = ‘ ||
      person.salary || ‘, Job Id = ‘ || person.job_id
    );
  END LOOP;
END;
/

close

技术分享图片

cursor variable

类型定义

技术分享图片

TYPE type_name IS REF CURSOR [ RETURN return_type ]

有return的，称为strong ref cursor

没有return的，成为weak ref cursor

sys_refcurosr

oracle预定语的ref cursor, 没有定义return

申明

技术分享图片

open for

技术分享图片

支持动态sql
支持变量

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := ‘SELECT * FROM employees WHERE job_id = :j‘;

  -- Open cursor & specify bind variable in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING ‘MANAGER‘;

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;
/

fetch

同显性cursor

close

同显性cursor

赋值与传参

赋值

target_cursor_variable := source_cursor_variable;

pl内部传参

CREATE OR REPLACE PACKAGE admin_data AUTHID DEFINER AS
  TYPE gencurtyp IS REF CURSOR;
  PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
/
CREATE OR REPLACE PACKAGE BODY admin_data AS
  PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN generic_cv FOR SELECT * FROM employees;
    ELSIF choice = 2 THEN
      OPEN generic_cv FOR SELECT * FROM departments;
    ELSIF choice = 3 THEN
      OPEN generic_cv FOR SELECT * FROM jobs;
    END IF;
  END;
END admin_data;
/

与外部程序传参

import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
//非oracle可以使用 cstmt.registerOutParameter(1, java.sql.Types.REF_CURSOR）；
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);
//cursor = ((CallableStatement)cstmt).getCursor(1);

// Use the cursor like a standard ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));}

cursor表达式

CURSOR ( subquery )

DECLARE
  TYPE emp_cur_typ IS REF CURSOR;
 
  emp_cur    emp_cur_typ;
  dept_name  departments.department_name%TYPE;
  emp_name   employees.last_name%TYPE;
 
  CURSOR c1 IS
    SELECT department_name,
      CURSOR ( SELECT e.last_name
                FROM employees e
                WHERE e.department_id = d.department_id
                ORDER BY e.last_name
              ) employees
    FROM departments d
    WHERE department_name LIKE ‘A%‘
    ORDER BY department_name;
BEGIN
  OPEN c1;
  LOOP  -- Process each row of query result set
    FETCH c1 INTO dept_name, emp_cur;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘Department: ‘ || dept_name);
 
    LOOP -- Process each row of subquery result set
      FETCH emp_cur INTO emp_name;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(‘-- Employee: ‘ || emp_name);
    END LOOP;
  END LOOP;
  CLOSE c1;
END;
/

for update of与current of

DECLARE
  my_emp_id NUMBER(6);
  my_job_id VARCHAR2(10);
  my_sal    NUMBER(8,2);
  CURSOR c1 IS
    SELECT employee_id, job_id, salary
    FROM employees FOR UPDATE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_emp_id, my_job_id, my_sal;
    IF my_job_id = ‘SA_REP‘ THEN
      UPDATE employees
      SET salary = salary * 1.02
      WHERE CURRENT OF c1;
    END IF;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
END;
/

rowtype与type

named cursor可以直接用rowtype,

curosr variable 支持type

DECLARE
  CURSOR c IS
    SELECT e.job_id, j.job_title
    FROM employees e, jobs j
    WHERE e.job_id = j.job_id AND e.manager_id = 100
    ORDER BY last_name;
 
  -- Record variables for rows of cursor result set:
 
  job1 c%ROWTYPE;
  job2 c%ROWTYPE;
  job3 c%ROWTYPE;
  job4 c%ROWTYPE;
  job5 c%ROWTYPE;
 
BEGIN
  OPEN c;
  FETCH c INTO job1;  -- fetches first row
  FETCH c INTO job2;  -- fetches second row
  FETCH c INTO job3;  -- fetches third row
  FETCH c INTO job4;  -- fetches fourth row
  FETCH c INTO job5;  -- fetches fifth row
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE(job1.job_title || ‘ (‘ || job1.job_id || ‘)‘);
  DBMS_OUTPUT.PUT_LINE(job2.job_title || ‘ (‘ || job2.job_id || ‘)‘);
  DBMS_OUTPUT.PUT_LINE(job3.job_title || ‘ (‘ || job3.job_id || ‘)‘);
  DBMS_OUTPUT.PUT_LINE(job4.job_title || ‘ (‘ || job4.job_id || ‘)‘);
  DBMS_OUTPUT.PUT_LINE(job5.job_title || ‘ (‘ || job5.job_id || ‘)‘);
END;
/

PL学习-curosr

原文：https://www.cnblogs.com/fqguo24/p/12904926.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)