db2 存储过程创建行为 [英] db2 stored procedures creation behavior

问题描述

我正在使用 db2 存储过程，我在努力理解以下概念.当我创建如下简单的存储过程时

I am playing with db2 stored procedures and I am struggling in understading the following concept. When I create a stored procedure like the simple below

create or replace procedure test()
begin
insert into mytable(a) values ('a');
insert into mytable(a) values ('b');
insert into mytable(a) values ('c');
end

我可以看到 mytable 在此创建过程中被填满.我期待使用'call test()'来插入我的数据，但我可以看到它不是我想的那样.我在这里做错了什么或者它确实像这样工作?

I can see that the mytable gets filled during this creation. I was expecting to use 'call test()' to insert my data, but I can see it is not what I thought. What I am doing wrong here or it does work like this?

为了避免这种奇怪的行为，我发现我需要将所有内容放在同一行，并且我有我所期望的，即当我调用任何创建或替换过程"时没有插入数据

To avoid this odd behavior I found that I need to put everything on the same line and I have what I expect i.e. no data inserted when I call any "create or replace procedure"

create or replace procedure test() begin insert into mytable(a) values ('a'); insert  into mytable(a) values ('b');  insert into mytable(a) values ('c');     end

推荐答案

问题是每条指令的终止字符.

The problem is the termination character for each instruction.

例如，如果您有一个名为 test.sql 的脚本文件，其中包含插入和存储过程定义，那么您的定义将是:

For example, if you have a script file called test.sql with inserts and a stored procedure definition, then your defintion will be:

test.sql

insert into mytable (a) values ('a')@
create procedure test ()
  begin
    insert into mytable (a) values ('b');
  end@

您将通过此命令执行脚本

And you will execute the script via this command

 db2 -td@ -f test.sql

@ 符号表示终止符(选项 -td@ 表示终止符分隔符).请注意，这与 ;(分号)因为 ;在例程(存储过程、udf、触发器)中用作终止字符.

The @ sign indicates the termination character (option -td@ to indicate the Termination character Delimiter). Note that this is different that ; (semi colon) because the ; is used as termination character inside a routine (stored procedure, udf, trigger).

如您所见，a"值将被插入到第一个插入中.'b' 值将从存储过程中插入.

As you can see, the 'a' value will be inserted in the first insert. The 'b' value will be inserted from the stored procedure.

为了确定您插入的内容，您可以查询 DB2 目录以查看已定义的存储过程和主体".

In order o be sure what you inserted, ou can query the DB2 catalog in order to see the defined stored procedures, and the 'bodies'.

这篇关于db2 存储过程创建行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！