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;