- 如何在PLSQL 的存储过程中执行SQL 语句呢?
用到动态执行SQL:EXECUTE IMMEDIATE
- 表信息
select * from emp_temp;
- 存储过程
CREATE OR REPLACE PROCEDURE F_DELETE_ROWS(table_name IN VARCHAR2,condition IN VARCHAR2 DEFAULT NULL) AS where_clause VARCHAR2(100) := ' WHERE ' || condition; v_table VARCHAR2(30); V_SQL VARCHAR2(100); /**NAME : F_CALCULATE_DATAITEM *PURPOSE :动态删除表中数据 *IMPUT : table_name 即将删除数据所属的表 condition 删除条件 *Author : -- CICI *CreateDate : -- 2012、12、30 *UpdateDate : -- ************************************************************/ BEGIN -- first make sure that the table actually exists; if not, raise an exception SELECT o.OBJECT_NAME INTO v_table FROM SYS.all_objects o WHERE o.OBJECT_NAME = UPPER(table_name) AND o.OBJECT_TYPE = 'TABLE'; IF condition IS NULL THEN where_clause := NULL; END IF; IF table_name IS NOT NULL THEN v_table := table_name; END IF; --拼写即将执行的动态SQL V_SQL := 'DELETE FROM ' || v_table || where_clause; --执行删除语句 EXECUTE IMMEDIATE V_SQL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid table: ' || table_name); END;