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;