今天在 ORACLE 到 MogDB 存储过程改造中遇到 存储过程调用多个存储过程/函数的场景发现，call 存储过程之后，他只执行了第一个存储过程，然后主存储过程就抛出异常退出了。下面手根据业务逻辑简单写了下存储过程和函数，具体如下：

p_create_table_task

在 p_create_table_task 中执行 p_mes_cre_tbs01不成功，但是单独执行 p_mes_cre_tbs01 可以正常执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE PROCEDURE mes.p_create_table_task(
                       f_date TIMESTAMP(0) WITHOUT TIME ZONE
                  )
 PACKAGE
AS 
DECLARE
  l_sql VARCHAR(5000);
  l_date         TIMESTAMP(0) WITHOUT TIME ZONE;
  f_error2         NUMERIC;
  f_error          NUMERIC;
BEGIN
  l_date := f_date;
  while  l_date <= f_date + 7 loop
    call mes.p_mes_cre_tbs01(l_date,f_error2);
    RAISE notice 'AFTER A BEFORB p_create_table_task value is: %',l_date;
  --call mes.p_mes_cre_tbs02(l_date,f_error2);
    l_date := l_date+1;
    RAISE notice 'last l_date is: %',l_date;
  end loop;
  f_error := 1;
  exception
   when others then
     f_error := 0;
   RAISE notice 'p_create_table_task of SQLERRM is: %',SQLERRM;
end;
/;

f_tab_if_cre

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION mes.f_tab_if_cre(p_tablename character varying)
 RETURNS numeric
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE PACKAGE
AS $function$
DECLARE
 result NUMERIC;
BEGIN
  SELECT COUNT(*) into result FROM information_schema.tables
  WHERE TABLE_NAME=lower(p_tablename);
  return result;
exception
  when others then
  return -1;
end;
$function$;

p_mes_cre_tbs01

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE PROCEDURE mes.p_mes_cre_tbs01(
       f_date TIMESTAMP(0) WITHOUT TIME zone, 
       f_error  out NUMERIC
)
 PACKAGE
AS 
DECLARE
  l_sql VARCHAR(5000);
  l_strdate VARCHAR(10);
  l_tablename varchar(50);
  l_table_if_cre_res numeric; 
BEGIN
  l_strdate := to_char(f_date,'YYYYMMDD');
  l_tablename := 'mestbs01_' || trim(l_strdate);
  select mes.f_tab_if_cre(l_tablename) into  l_table_if_cre_res;
  if l_table_if_cre_res=0 then
    l_sql := 'create table '||l_tablename||'
      (
        ID                  NUMBER(11)
      )';
    execute  l_sql;
    RAISE notice 'the l_sql of mes_cretbs01 is: %',l_sql;
  end if;
  f_error := 1;
  exception
    when others then
      f_error := 0;
RAISE notice 'mes_cretbs01 is error: %',sqlerrm;
end;
/;

执行调用过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
MogDB=# \c mesdb mes
Password for user mes:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "mesdb" as user "mes".
mesdb=> call mes.p_create_table_task(sysdate);
NOTICE:  the l_sql of mes_cretbs01 is: create table mestbs01_20240523
      (
        ID                  NUMBER(11)
      )
NOTICE:  p_create_table_task of SQLERRM is: query has no destination for result data
 p_create_table_task
---------------------
(1 row)

mesdb=> \d mestbs01_20240523
Did not find any relation named "mestbs01_20240523".
mesdb=> call mes.p_mes_cre_tbs01(sysdate,',');
NOTICE:  the l_sql of mes_cretbs01 is: create table mestbs01_20240523
      (
        ID                  NUMBER(11)
      )
 f_error
---------
       1
(1 row)
mesdb=>  \d mestbs01_20240523
   Table "mes.mestbs01_20240523"
 Column |     Type      | Modifiers
--------+---------------+-----------
 id     | numeric(11,0) |
mesdb=>

通过 Mogeaver 来 debug 也是执行完 第一个存储过程p_mes_cre_tbs01之后，就转到主存储过程抛异常了：NOTICE: p_create_table_task of SQLERRM is: query has no destination for result data
他的报错就是 query has no destination for result data ，也就是没有返回结果，p_mes_cre_tbs01存储过程是判断表是否存在，如果不存在就创建，就是创建成功之后他也没有返回结果呢，所以没有结果是正常的呢。这里百思不得其解，无奈把上面的数据 复制给大模型。

image-20240525212931479

果然，将 call 修改为 PERFORM 发现竟然可以了。

然后我又问了下 call 和 PERFORM 的区别：

在 PostgreSQL 中，”PERFORM” 是一个用于在函数或存储过程中执行一个 SQL 查询，但忽略任何返回结果的关键字。当你不关心查询的返回结果，只是想要查询的副作用（例如，更新，插入或删除）时，可以使用 “PERFORM”。

例如，如果你在一个函数中需要执行一个 UPDATE 查询，但你并不关心实际更新了多少行，你可以这样使用：

1
PERFORM UPDATE table SET column = value WHERE condition;

这样，UPDATE 查询会被执行，但其返回的行数将被忽略。这对于触发器和其他PL/pgSQL存储过程特别有用，你只关心查询的执行，而不是它的结果。如果你试图只执行一个查询并忽略结果，但没有使用 “PERFORM”，PostgreSQL 会给出一个错误，告诉你查询返回了一个结果，但没有地方去存储它。

也就是 PERFORM 会忽略所有的返回结果，很显然第一个存储过程是判断并创建表，即使创建了表也没有返回的。这就导致了 query has no destination for result data ，主存储过程就退出了。

后来经过 https://www.anbob.com/archives/8001.html 的验证，其实在存储过程调用当中，是不需要加 call的。

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/ca5b5260/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议