lightdb22.3 plorasql存储过程使用游标补充（open游标时与定义时参数不一致问题）

背景

游标定义形参设置默认值。例如：
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;
游标定义完成后，需要使用游标相关函数进行操作，如open、fetch、close.
怎样使用便成了大问题。下面开始介绍这部分的类容。
# 一级标题

cursor形参语法

parameter_name [IN] data_type [:= | default expression]
应用场景是存储过程、函数、匿名块和包。与Oracle相比，还是有一些区别的。比如data_type，Oracle支持在游标参数字符串中声明char，但是lightdb的char长度默认为1，所以对于长度超过1的字符串，需要指定char长度，char(100)。但是对于变长字符类型，比如varchar、varchar2等，用法和oracle一样。并且不支持oracle类型：string、long、raw、long raw、rowid、timestamp with local time等。包中不支持constant定义的常量类型。
与 Oracle 相比，表达式也有一些差异：
（1）布尔表达式
1、包支持返回值为布尔类型的函数、布尔表达式1<2)和布尔字面量的函数（oracle支持的NULL不支持)；
2、函数、存储过程和匿名块支持返回值为布尔类型、布尔常量、布尔变量、布尔表达式和布尔文字的函数（oracle 支持的 NULL 不支持)；
（2）字符表达式
1、包支持返回值为字符类型的函数和字符字面；
2、函数、存储过程和匿名块支持返回值为字符类型、字符常量、字符变量和布尔文字的函数。
（3）日期表达式
1、包支持返回值为日期类型的函数和日期字面值；
2、函数、存储过程和匿名块支持返回值为字符类型、日期常量、日期变量和日期字面值。
（4）数值表达式
1、包支持返回值为数值类型的函数和数值字面值；
2、函数、存储过程和匿名块支持返回值为数值类型、数值常量、数值变量和数值字面值。
（5）case表达式
1、函数、存储过程、匿名块支持 search_case_expression 和 simple_case_expression；
2、包支持 search_case_expression 和 simple_case_expression（expression不支持变量、常量）。

举一反三

声明一个游标带参数的用例
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;
/
多个默认参数值，缺省情况
DECLARE
CURSOR c (job VARCHAR2, max_sal NUMBER,
hired DATE DEFAULT TO_DATE(‘31-DEC-1999’, ‘DD-MON-YYYY’)) IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job
AND salary > max_sal
AND hire_date > hired
ORDER BY salary;
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE (‘--------------------------------’);
DBMS_OUTPUT.PUT_LINE (‘Overpaid Sales Representatives Hired After 2004:’);
DBMS_OUTPUT.PUT_LINE (‘--------------------------------’);
OPEN c(‘SA_REP’, 10000);
LOOP
FETCH c INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (last_name_ || ', (by ’ || overpayment_ || ‘)’);
END LOOP;
CLOSE c;
END;
/

总结

站在巨人的肩膀开发。