updnval.sql

Oracle PLSQL for DBAs 源代码

CREATE OR REPLACE FUNCTION updNVal (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   val IN NUMBER,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE ' || NVL (sch, USER) || '.' || tab ||
      '   SET ' || col || ' = :the_value 
        WHERE ' || NVL (whr, '1=1')
     USING val;
   RETURN SQL%ROWCOUNT;
END;
/