oracle带参数,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/，如需转载，请注明出处，否则将追究法律责任。