dynamic PL/SQL study 小例子

/*********************************************
 Functional: 根据提供的表的列来更新薪资
 Parameter :column_vlaue  部门或员工的ID
            emp_column    员工的部门或员工对应的列名
            amount        更新的薪资
History :   2010-01-14 created by williams     
********************************************/

CREATE OR REPLACE PROCEDURE raise_emp_salary(column_value NUMBER      --列值
                                            ,emp_column   VARCHAR2    --表的列
                                            ,amount       NUMBER) IS  --更新的金额
   v_column VARCHAR2(30);
   sql_stmt VARCHAR2(200);
BEGIN
   -- determine if a valid column name has been given as input
   SELECT column_name
   INTO v_column
   FROM user_tab_cols
   WHERE table_name = upper('EMPLOYEES_all')
   AND column_name = emp_column;
  
   sql_stmt := 'UPDATE EMPLOYEES_all SET salary = salary + :1 WHERE ' || v_column || ' = :2';
   EXECUTE IMMEDIATE sql_stmt
      USING amount, column_value;
    ---检查是否有数据 
   IF SQL%ROWCOUNT > 0 THEN
      dbms_output.put_line('Salaries have been updated for: ' || emp_column || ' = ' || column_value);
   END IF;
  
  
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Invalid Column: ' || emp_column);
     
END raise_emp_salary;

-------------------------------------------------------------

/*******************************************
 Functional: 根据提供的表名称和条件删除数据
 Parameter : table_name  表的名称
             condition   查询条件
 History :   2010-01-14 created by williams    
********************************************/

CREATE OR REPLACE PROCEDURE delete_rows(table_name IN VARCHAR2
                                       ,condition  IN VARCHAR2 DEFAULT NULL) AS
   where_clause VARCHAR2(100) := ' WHERE ' || condition;
   v_table      VARCHAR2(30);
BEGIN
   -- 检查表是否存在
   SELECT object_name
   INTO v_table
   FROM user_objects
   WHERE object_name = upper(table_name)
   AND object_type = 'TABLE';
   IF condition IS NULL THEN
      where_clause := NULL;
   END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Invalid table: ' || table_name);
END;

  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13996372/viewspace-625180/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13996372/viewspace-625180/