執行環境 Oracle11g
動態SQL是存儲過程及函數中常用的實現查詢操作手段，以下記錄幾種常用形式以供自查
CREATE TABLE EMPLOYEE_T(
EMPLOYEE_ID VARCHAR2(20) NOT NULL,
EMPLOYEE_NAME VARCHAR2(20)
);
1、直接執行動態SQL
CREATE OR REPLACE PROCEDURE SP_DEAL
IS
vs_sql VARCHAR2(4000);
BEGIN
vs_sql := 'UPDATE EMPLOYEE_T SET EMPLOYEE_NAME='||CHR(39)||'zsj'||CHR(39)||' WHERE EMPLOYEE_ID = 001';
EXECUTE IMMEDIATE vs_sql;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end SP_DEAL;
2、執行有輸出結果的動態SQL
CREATE OR REPLACE PROCEDURE SP_DEAL_OUT
IS
vn_num NUMBER(2);
vs_sql VARCHAR2(4000);
BEGIN
vs_sql := 'SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE_T';
EXECUTE IMMEDIATE vs_sql INTO vn_num;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end SP_DEAL_OUT;
3、執行有輸入參數與輸出結果的動態SQL --select時使用
CREATE OR REPLACE PROCEDURE SP_DEAL_IN_OUT
IS
vn_num NUMBER(2);
vs_sql VARCHAR2(4000);
vs_employee_id EMPLOYEE_T.EMPLOYEE_ID%TYPE;
vs_employee_name EMPLOYEE_T.EMPLOYEE_NAME%TYPE;
vs_out_employee_id EMPLOYEE_T.EMPLOYEE_ID%TYPE;
vs_msg VARCHAR2(4000);
BEGIN
vs_employee_id := '001';
vs_employee_name := 'znp';
vs_sql := 'INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME) VALUES (:1,:2) RETURNING EMPLOYEE_ID INTO :3';
EXECUTE IMMEDIATE vs_sql USING vs_employee_id,vs_employee_name RETURN INTO vs_out_employee_id ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
vs_msg :=SQLERRM;
ROLLBACK;
end SP_DEAL_IN_OUT;
1、通過動態SQL直接提取查詢結果,返回查詢結果集
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE(
cur OUT SYS_REFCURSOR
)
IS
vs_sql VARCHAR2(4000);
BEGIN
vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T ';
OPEN cur FOR vs_sql;
end SP_EMPLOYEE;
2、通過動態SQL提取查詢結果集,通過顯式游標方式進行處理
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE
IS
vs_sql VARCHAR2(4000);
vs_id EMPLOYEE_T.EMPLOYEE_ID%TYPE;
vs_name EMPLOYEE_T.EMPLOYEE_NAME%TYPE;
TYPE cur_cursor IS REF CURSOR;
cur cur_cursor;
BEGIN
vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T ';
OPEN cur FOR vs_sql;
LOOP
FETCH cur INTO vs_id,vs_name;
UPDATE EMPLOYEE_T
SET EMPLOYEE_NAME = vs_name
WHERE EMPLOYEE_ID = vs_id;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end SP_EMPLOYEE;
3、通過動態SQL直接調用存儲過程
CREATE OR REPLACE PROCEDURE SP_DEAL(
is_name VARCHAR2
)
IS
vs_sql VARCHAR2(1000); --動態SQL描述
BEGIN
--拼接動態調用哪一個存儲過程(指定三個調用形參)
vs_sql := 'BEGIN SP_'||is_name||'_BACKUP(:V1,:V2,:V3,:v4); END;';
--執行動態SQL,同時指定一個入參與兩個出差
EXECUTE IMMEDIATE vs_sql USING IN is_id, IN is_para,OUT on_flag,OUT os_msg;
EXCEPTION
WHEN OTHERS THEN
on_flag := -1;
os_msg := 'SP_'||is_name||':'||SUBSTR(SQLERRM,0,200);
END SP_DOUBT_BACKUP;
4、為動態SQL指定入參