Oracle带参数的自定义函数

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.

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

转载于:http://blog.itpub.net/29485627/viewspace-1246532/