执行环境 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指定入参