oracle ref游标带参数,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;