tabcount_nds.sf

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
   RETURN PLS_INTEGER AUTHID CURRENT_USER
IS
   str      VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || tab;
   retval   PLS_INTEGER;
BEGIN
   IF whr IS NOT NULL
   THEN
      str := str || ' WHERE ' || whr;
   END IF;

   EXECUTE IMMEDIATE str
                INTO retval;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('TABCOUNT ERROR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      DBMS_OUTPUT.put_line (str);
      RETURN NULL;
END;
/