Oracle帶引數的自定義函式

(1) 函式編寫
create or replace function getRecordCount(table_name varchar2) return number as
  begin
    declare t_count number;
            query_sql varchar2(200);
    begin
      query_sql := 'select count(1) from ' || table_name;
      execute immediate query_sql into t_count;
      return t_count;
    end;
  end getRecordCount;
/

(2) 函式呼叫
set serverout on
declare table_name varchar2(50);
begin
  table_name := 'dept';
  dbms_output.put_line('The record count of table ' || table_name || ' is ' || getRecordCount(table_name));
end;
/
執行結果：
The record count of table dept is 4
PL/SQL procedure successfully completed.


換個表名試試：
declare table_name varchar2(50);
begin
  table_name := 'emp';
  dbms_output.put_line('The record count of table ' || table_name || ' is ' || getRecordCount(table_name));
end;
/
執行結果：
The record count of table emp is 14
PL/SQL procedure successfully completed.