Oracle讲解
4.4 REF游标
因为隐式游标和显示游标都是静态定义的，就是sql语句是固定的，如果需要动态确定执行查询，就可以使用REF游标和游标变量。
创建游标变量需要声明REF_CURSOR类型和声明REF_CURSOR类型的游标变量。 TYPE ref_cursor_name IS REF CURSOR [RETURN record_type];
其中RETURN是可选子句，用于指定游标提取结果集的返回类型。.包含RETURN语句表示是强类型的游标，不包含RETURN是弱类型游标 在PL/SQL执行部分打开游标
OPEN cursor_name FOR 动态select语句 [USING bind_argument_list]; 绑定参数列表
动态SQL的用法:
SET SERVEROUTPUT ON; DECLARE
cur_row emp%ROWTYPE; TYPE c_type IS REF CURSOR; cur c_type;
p_salary NUMBER; BEGIN
p_salary :=2500;
OPEN cur FOR 'SELECT * FROM emp WHERE sal>:1 ORDER BY sal DESC' USING p_salary; DBMS_OUTPUT.PUT_LINE('薪水大于'||p_salary||'的用户有:'); FETCH cur INTO cur_row; WHILE cur%FOUND LOOP FETCH cur INTO cur_row; DBMS_OUTPUT.PUT_LINE('编号:'||cur_row.empno|| ' 姓名'||cur_row.ename); END LOOP; CLOSE cur; END; / /* LOOP
FETCH cur INTO cur_row;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号:'||cur_row.empno|| ' 姓名'||cur_row.ename); END LOOP; CLOSE cur; */
游标变量(如上题的: c_type)的优点： 可用于从不同的结果集中提取记录
21
Oracle讲解
可以作为过程的参数进行传递 可以引用游标的所有属性 可以用于赋值运算
但是游标变量也有一定的限制
FOR UPDATE子句不能与游标变量一起使用。 不允许在程序包中声明游标变量
另外一台服务器的远程子过程不能接受游标变量参数的值 不能将NULL值赋给游标变量 游标变量不能使用比较运算符 数据库的列不能存储游标变量
子程序和程序包
5.1过程：
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_list)] --参数列表 {IS|AS} --类似于DECLARE [local_declarations] --局部声明部分 BEGIN
executable_statements --可执行语句 [EXCEPTION
exception_handlers] --异常处理程序 END [procedure_name]; --可以用名称结束 例子：
CREATE OR REPLACE PROCEDURE findemp ( emp_no NUMBER ) AS empname VARCHAR(20); BEGIN SELECT ename INTO empname FROM emp where empno=emp_no; DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('没有找到指定的雇员'); END; /
以上过程是根据雇员编号为参数查找相应的雇员。其中参数列表不能用;结尾
22
Oracle讲解
过程参数模式
调用程序是通过参数向被调用的过程传递值的，参数传递模式有3种：IN、OUT即输入、输出、输入输出。
创建带IN模式参数的过程
CREATE OR REPLACE PROCEDURE Itemdesc(item_code IN VARCHAR2) IS v_itemdesc VARCHAR2(5); BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode=item_code; END;
执行带参数的过程: BEGIN
EXECUTE itemdesc(‘i201’); END;
创建带OUT参数的过程
CREATE OR REPLACE PROCEDURE
test(value1 IN VARCHAR2,value2 OUT NUMBER) IS
identity NUMBER; BEGIN
SELECT itemRate INTO identity FROM itemFile where itemCode=value1; IF identity<200 THEN value2:=100; ELSE value2:=50; END IF; END; /
调用带OUT参数的过程 DECLARE value2 NUMBER; BEGIN test(‘i201’,value2); DBMS_OUTPUT.PUT_LINE(‘value2的值是:’||TO_CHAR(value2)); END;
创建带IN OUT参数的过程
CREATE OR REPLACE PROCEDURE swap ( p1 IN OUT NUMBER,
23
Oracle讲解
p2 IN OUT NUMBER ) AS temp NUMBER; BEGIN temp:=p1; p1:=p2; p2:=temp; END; /
调用带IN OUT参数的过程 SET SERVEROUTPUT ON; DECLARE p1 NUMBER:=1; p2 NUMBER:=2; BEGIN swap(p1,p2); DBMS_OUTPUT.PUT_LINE(p1||' '||p2); END; /
将过程findemp的权限授予用户MARTIN: GRANT EXECUTE ON findemp TO MARTIN; 删除过程
DROP PROCEDURE findemp;
5.2 函数：
CREATE [OR REPLACE] FUNCTION function_name [(parameter1,parameter2…)] RETURN datatype {IS|AS} [location_declarations] BEGIN executable_Statements; [EXCEPTION exception_Handlers;] END; /
函数只能带有IN参数，而不能带有IN OUT或OUT参数。 形式参数必须只能使用数据库类型，不得使用PL/SQL类型。 函数的返回类型也必须是数据库类型。
24
Oracle讲解
创建函数:
CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN '朋友，您好'; END; /
与过程不同，函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。 SELECT fun_hello FROM dual;
创建带有业务功能的函数
CREATE OR REPLACE FUNCTION my_fun(sal NUMBER) RETURN VARCHAR2 AS
sal1 NUMBER; sal2 NUMBER; BEGIN
SELECT MAX(sal),MIN(sal) INTO sal1,sal2 FROM emp; IF(sal BETWEEN sal2 AND sal1) THEN RETURN '价格介于最大价格与最小价格之间'; ELSE RETURN '超过范围'; END IF; END; /
调用此函数
SET SERVEROUTPUT ON; DECLARE
sal NUMBER:=&输入价格; msg VARCHAR2(50); BEGIN msg:=my_fun(sal); DBMS_OUTPUT.PUT_LINE(msg); END; /
过程 作为PL/SQL语句执行 在规范中不能包含RETURN子句 不返回任何值 可包含RETURN但不返回值
函数 作为表达式的一部分调用 必须在规范中包含RETURN子句 必须返回单个值 必须包含至少一条RETURN 25