[oracle自定义函数]查询表格的行数

假如要查的表为emp（员工表），在scott用户下默认有这张表。当前有14条记录。

只需要执行

select count(*) from emp;

需求：现在要把表名变成动态传入的，想查哪张表的行数只要传入表名就行了

一、准备工作

在写这个之前，我们先来写一个根据员工id（empno）查询员工姓名（ename）的自定义函数（getEmpName）

create or replace function getEmpName(empId in number)
return varchar2 is
  z varchar2(200);
begin
  select ename into z from emp where empno = empId;
  return z;
end;

调用该函数：

select getEmpName(7369) from dual;

符合预期（另外再测几个员工id，都得到预期结果），说明编写正确。

注：a.在oracle可以将查询条件（where）中的参数值作为变量，能够直接使用自定义函数中传入的参数值

b.创建的函数“return varchar2 is”后面的varchar2需要给出确定的值，这里是200

此处特别说明这两点，是因为在动态传表名的时候略有不同。

二、开始实现需求

1.创建自定义函数的写法

仿照前面的思路，可以写出如下函数

create or replace function getTableCount(tableName in varchar2)
return number is
  z number;
begin
  select count(*) into z from tableName;
  return z;
end;

但是编译后会发现，

这样写出的函数是有问题的。我们换个思路，动态拼接sql语句，然后执行并返回结果

这里需要再添加一个变量v_sql（varchar2类型）专门用来存储sql语句，如果拼出的语句很长，可以把字符长度设大一些（最多32767，

这里是plsql变量中的varchar2类型；如果是oracle数据库中的varchar2类型，最多是4000。参考http://blog.csdn.net/jackpk/article/details/49663137）

create or replace function getTableCount(tableName in varchar2)
return number is
  z number;
  v_sql varchar2(2000);
begin
  v_sql:='select count(*) from '||tableName;
  execute immediate v_sql into z;
  return z;
end;

调用该函数：

select getTableCount('emp') from dual;

结果正确

再总结两点：

a.在oracle不能将表名为变量，通过自定义函数中传入的参数值得到表名直接执行sql语句；

而需要将得到的表名先拼成一条sql，然后执行完把值传给变量，最后返回该变量

（参考链接：https://www.cnblogs.com/linbo3168/p/6043427.html  https://www.cnblogs.com/hanruyue/p/5974036.html）

b.对比varchar2类型，我们看到在返回number时可以不用特意标注变量的位数

在“return number is”后面接的是“z number”。因为number这种类型不填位数时，自带默认精度38

（参考链接：https://zhidao.baidu.com/question/2266874365488550348.html）

三、补充

如果只想测试下函数功能，并不想创建函数（执行一次就没了）。可以这样写

declare
  tableNum number;
function getTableCount(tableName in varchar2)
return number is
  z number;
  v_sql varchar2(2000);
begin
  v_sql:='select count(*) from '||tableName;
  execute immediate v_sql into z;
  return z;
end;
begin
  tableNum:=getTableCount('emp');
  dbms_output.put_line(tableNum);
end;

直接在pl/sql的sql window中执行，可以看到同样的结果