oracle打开参数游标,ORACLE中带参数、REF游标及动态SQL实例

--===============带参数的游标==================--

DECLARE

dept_code emp.deptno%TYPE; --声明列类型变量三个

emp_code emp.empno%TYPE;

emp_name emp.ename%TYPE;

CURSOR emp_cur(deptparam NUMBER) IS SELECT

empno, ename FROM EMP WHERE deptno = deptparam;

--声明显示游标

BEGIN dept_code := &部门编号; --请用户输入想查看的部门编号

OPEN emp_cur(dept_code); --打开游标

LOOP --死循环

FETCH emp_cur INTO emp_code, emp_name; --提取游标值赋给上面声明的变量

EXIT WHEN emp_cur%NOTFOUND; --如果游标里没有数据则退出循环

DBMS_OUTPUT.PUT_LINE(emp_code || '' ||

emp_name); --输出查询

END LOOP;

CLOSE emp_cur; --关闭游标END;

--=================REF游标==================--

ACCEPT tab FROMPT '你想查看什么信息?员工(E)或部门信息(D):'; --使用ACCEPT命令弹出对话框让用户输入数据

DECLARE

TYPE refcur_t IS REF CURSOR; --声明REF游标类型

refcur refcur_t; --声明REF游标类型的变量

pid NUMBER;

p_name VARCHAR2(100);

selection VARCHAR2(1) :=

UPPER(SUBSTR('&tab', 1, 1)); --截取用户输入的字符串并转换为大写

BEGIN

IF selection = 'E'

THEN --如果输入的是'E',则打开refcurr游标,并将员工表查询出来赋值给此游标

OPEN refcur FOR SELECT EMPNO ID, ENAME NAME

FROM EMP;

DBMS_OUTPUT.PUT_LINE('=====员工信息=====');

ELSIF selection = 'D'

THEN --如果输入是'D',则打开部门表

OPEN refcur FOR SELECT deptno id, dname

name FROM DEPT;

DBMS_OUTPUT.PUT_LINE('=====部门信息======');

ELSE --否则返回结束

DBMS_OUTPUT.PUT_LINE('请输入员工信息(E)或部门信息(D)');

RETURN;

END IF;

FETCH refcur INTO pid, p_name; --提取行

WHILE refcur%FOUND

LOOP

DBMS_OUTPUT.PUT_LINE('#' || pid || ':' ||

p_name);

FETCH refcur INTO pid, p_name; END LOOP;

CLOSE refcur; --关闭游标

END;

--===================动态SQL=================--

VARIABLE maxsal NUMBER; --声明变量

EXECUTE :maxsal := 2500; --执行引用并给变量赋值

DECLARE

r_emp EMP%ROWTYPE; --声明一个行类型变量

TYPE c_type IS REF CURSOR; --声明REF游标类型

cur c_type; --声明REF游标类型的变量

p_salary NUMBER; --声明一个标量变量

BEGIN

p_salary := :maxsal; --引用变量

--使用USING语句将引用到的值传给动态SQL语句'SAL >: 1'中的'1'

OPEN cur FOR 'SELECT * FROM EMP WHERE SAL

>: 1 ORDER BY SAL DESC' USING p_salary;

DBMS_OUTPUT.PUT_LINE('薪水大于' || p_salary || '的员工有:');

LOOP

FETCH cur INTO r_emp;

EXIT WHEN cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('编号:' || r_emp.empno || '姓名:' || r_emp.ename || '薪水:' || r_emp.sal);

END LOOP;

CLOSE cur; --关闭游标

END;