学步园

• 如何在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;