函数与存储过程调用方式的区别

对于函数与存储过程，其调用方式不同。函数可以通过select or call 方式调用，而存储过程只能通过call 方式调用。以下具体举例说明。

1、创建调用函数和过程

函数：

create or replace function func01 returns integer as
  cnt integer;
begin
  select count(*) into cnt from t1;
  return cnt;                                
end;

过程：

create or replace procedure proc01 as
  cnt integer;
begin
  select count(*) into cnt from t1;
exception
  when others then
     commit;                                         
end;

包：

create or replace package pkg_test as
  function func01() return integer;
  procedure proc01;
end;
 
create or replace package body pkg_test as
  procedure proc01 as
    cnt integer;
  begin
    select count(*) into cnt from t1;
  exception
    when others then
       commit;                                         
  end proc01;
   
  function func01 returns integer as
    cnt integer;
  begin
    select count(*) into cnt from t1;
    return cnt;                                
  end func01;
end;

2、具体调用例子

函数调用：可以用select or call

test=# select func01(); 
 func01 
--------
      0
(1 row)
 
test=# call func01();
 func01 
--------
      0
(1 row)
 
test=# call pkg_test.func01(); 
 func01 
--------
      0
(1 row)
 
test=# select pkg_test.func01();
 func01 
--------
      0
(1 row)

过程调用：只能用call

test=# call pkg_test.proc01();
CALL
 
test=# select pkg_test.proc01();
ERROR:  pkg_test.proc01() is a procedure
LINE 1: select pkg_test.proc01();
               ^
HINT:  To call a procedure, use CALL.
 
test=# call proc01();
CALL
 
test=# select proc01();
ERROR:  proc01() is a procedure
LINE 1: select proc01();
               ^
HINT:  To call a procedure, use CALL.