lightdb22.2 plorasql存储过程使用游标

游标

游标分为显式游标和隐式游标。

隐式游标

隐式游标是指通过直接使用dml语言，达到操作游标的作用，常用的形式有两种：

(1)  open for select * from employees_curs where employees_id = 1;
(2)  for item in  select * from employees_curs where employees_id = 1
          loop
          end loop;

隐式游标属性

SQL%ISOPEN：游标是否打开

SQL%ISOPEN 总是返回 FALSE，因为隐式游标总是在其关联语句运行后关闭。

SQL%FOUND：是否有行受到影响

1、如果没有运行 SELECT 或 DML 语句，则为 NULL，
2、如果 SELECT 语句返回一或多行或 DML 语句影响一或多行，则为 TRUE，
3、否则为假。

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
  SELECT * FROM departments;
 
DECLARE
  dept_no NUMBER := 12;
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
 
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Delete succeeded for department number ' || dept_no);
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/

SQL%NOTFOUND：没有行受到影响

1、如果没有运行 SELECT 或 DML 语句，则为 NULL，
2、如果 SELECT 语句返回一或多行或 DML 语句影响一或多行，则为 FALSE。

SQL%ROWCOUNT：收到影响的行数

1、如果没有运行 SELECT 或 DML 语句，则为 NULL，
2、否则，SELECT 语句返回的行数或受 DML 语句影响的行数（INTEGER）。

显式游标

显式游标需要定义，才能在存储过程中使用。

声明：
CURSOR cursor_name [ parameter_list ] RETURN return_type;（包中声明）
定义：
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement;

DECLARE
	CURSOR c1 RETURN departments%ROWTYPE;    -- Declare c1
	CURSOR c2 IS SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000;
	CURSOR c1 RETURN departments%ROWTYPE IS SELECT * FROM departments WHERE department_id = 110;
	CURSOR c3 RETURN locations%ROWTYPE;
	CURSOR c3 IS SELECT * FROM locations WHERE country_id = 'JP';
BEGIN
  NULL;
END;
/

游标使用默认值

当您使用形式参数创建显式游标时，您可以为它们指定默认值。当形参有默认值时，其对应的实参是可选的。如果打开游标而不指定实参，则形参有其默认值。

DECLARE
  CURSOR c (location NUMBER DEFAULT 1700) IS
    SELECT d.department_name,
           e.last_name manager,
           l.city
    FROM departments d, employees e, locations l
    WHERE l.location_id = location
      AND l.location_id = d.location_id
      AND d.department_id = e.department_id
    ORDER BY d.department_id;
    dept_name  departments.department_name%TYPE;
    mgr_name   employees.last_name%TYPE;
    city_name  locations.city%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS AT HEADQUARTERS:');
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  OPEN c;
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS IN CANADA:');
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  OPEN c(1800); -- Toronto
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')');
  END LOOP; 
  CLOSE c;
  OPEN c(1900); -- Whitehorse
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')');
  END LOOP;
  CLOSE c;
END;
/

显式游标属性

%isopen

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11;
  the_name employees.last_name%TYPE;
  the_salary employees.salary%TYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;
  FETCH c1 INTO the_name, the_salary;
   IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
END;
/

%found

1、在显式游标打开之后但在第一次获取之前为 NULL，
2、TRUE f 最近从显式游标中提取返回一行，
3、否则为假.

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;
  my_ename   employees.last_name%TYPE;
  my_salary  employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%FOUND THEN  -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE ('Name = ' || my_ename || ' salary = ' || my_salary);
    ELSE  -- fetch failed
      EXIT;
    END IF;
  END LOOP;
END;
/

%notfound

1、在显式游标打开之后但在第一次获取之前为 NULL，
2、如果从显式游标的最新提取返回一行，则为 FALSE，
3、否则为真。

DECLARE
  CURSOR c1 IS
    SELECT last_name FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;
  name_1  employees.last_name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name_1;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
    DBMS_OUTPUT.PUT_LINE (c1%ROWCOUNT ||'. ' || name_1);
    IF c1%ROWCOUNT = 5 THEN
       DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

%rowcount

1、在显式游标打开之后但在第一次获取之前为零，
2、否则，获取的行数（整数）。

DECLARE
  CURSOR c1 IS
    SELECT last_name FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;
  name_1  employees.last_name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name_1;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
    DBMS_OUTPUT.PUT_LINE (c1%ROWCOUNT || '. ' || name_1);
    IF c1%ROWCOUNT = 5 THEN
       DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

使用游标 FOR LOOP 语句处理查询结果集

隐式游标处理

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

显式游标处理

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

游标变量

创建游标有两种方式：

(1)使用游标变量：sys_refcursor
cursor_name sys_refcursor;
(2)创建游标类型：
TYPE type_name IS REF CURSOR [ RETURN return_type ]
cursor_name   sys_refcursor;

DECLARE
  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE genericcurtyp IS REF CURSOR; 
  cursor1  empcurtyp;
  cursor2  genericcurtyp;
  my_cursor SYS_REFCURSOR;
  TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
  dept_cv deptcurtyp;  -- strong cursor variable
BEGIN
  NULL;
END;
/

游标变量属性

与显式游标属性相同。