环境
- 操作系统：Ubuntu 20.04
- Db2：11.5.0.0
简介
存储过程（ Stored Procedure ）也称过程（ Procedure ），是例程（ Routine ）的一种。下面我们先来看一下例程。
例程（Routine）
例程是可以封装编程和数据库逻辑的数据库对象，而编程和数据库逻辑可以像编程子例程一样从各种 SQL 接口调用。
例程有如下好处：
- 封装与抽象：封装可以从 SQL 接口调用的应用程序逻辑，简化代码复用和代码维护以及标准化代码。
- 安全性：控制数据库对象的访问。比如一个用户可能没有权限运行
create table语句，但是可以给他授权运行包含create table的例程，从而达到目的：既能够创建所需的table，又没有滥用权限。 - 降低网络开销。如果逻辑是包含在应用程序端，那显然应用程序要多次与数据库交互，相互传输命令与数据。例程是驻于数据库端的，从而节省了网络开销。
- 更快更高效的执行SQL。
- 不同编程语言实现的逻辑交互性。
例程可分为：
- 过程（
Procedure，也称为存储过程，Stored Procedure） - 函数（
Function，用户自定义函数也称为UDF，User-Defined Function） - 方法（
Method）
Db2内置了很多例程，比如聚集函数 min max avg sum 等，还有 ADMIN_CMD 存储过程、 MON_GET_CONNECTION 表函数等。用户也可以定义并使用自己的例程。无论是系统内置例程还是用户自定义例程，都可以在 syscat.routines catalog view里查询。比如：
select * from syscat.routines
where routinename in('ADMIN_CMD', 'MON_GET_CONNECTION', 'HELLO')
其中：
routinetype：P表示存储过程（procedure），F表示函数（function），M表示方法（method）；ownertype：S表示系统（system），U表示用户（user）；
从实现上看，例程可分为：
- 内置例程实现：可直接使用；
- 有源例程实现：没仔细研究……；
- SQL例程实现：只用SQL语句。可以使用SQL和SQL PL来创建SQL过程、函数、触发器以及复合SQL语句。SQL 过程语言 (SQL PL) 是基本 SQL 的语言扩展，基本 SQL 包含可用来以 SQL 实现编程逻辑的语句和语言元素，比如IF、WHILE、FOR、GOTO、LOOP 和 SIGNAL 等；
- 外部例程实现：由非SQL的编程语言（比如Java、C、COBOL等）实现；
从使用上，自定义例程可以调用内置例程和其它自定义例程。
下图是来自IBM官网的例程分类（https://www.ibm.com/docs/en/db2/11.5?topic=routines-types）：
存储过程
过程（ Procedure ）也称为存储过程（ Stored Procedure ）。过程可以封装逻辑和SQL语句，并可用作对客户机应用程序、例程、触发器和动态复合语句的子例程扩展。
通过 call 语句来调用过程。过程可以具有输入、输出和输入/输出参数，可以执行各种SQL语句并将多个结果集返回给调用者。
存储过程和函数的一大区别在于调用方式。存储过程是通过 call 来调用，而函数是在SQL语句里调用。
HelloWorld存储过程
命令行下运行db2命令的几种方式
在创建和运行存储过程之前，我们先来看一下在命令行下运行db2命令的几种方式。
直接运行db2命令
通常我们在命令行下，切换到 db2inst1 账号，然后就可以通过 db2 命令来与Db2直接交互，比如：
➜ ~ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
➜ ~ db2 "select * from sysibm.sysdummy1"
IBMREQD
-------
Y
1 record(s) selected.
➜ ~ db2 connect reset
DB20000I The SQL command completed successfully.
db2交互式命令行
在命令行下，也可以直接输入 db2 ，进入db2交互式命令行：
➜ ~ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.0.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 =>
然后就可以连接DB，运行各种SQL语句：
db2 => connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2 => select * from sysibm.sysdummy1
IBMREQD
-------
Y
1 record(s) selected.
db2 => connect reset
DB20000I The SQL command completed successfully.
db2 =>
最后，用 quit 命令退出db2交互式命令行。
运行db2 clp脚本
比如，创建文件 test1.sql ，如下：
connect to sample;
select * from sysibm.sysdummy1;
connect reset;
然后通过 db2 命令来运行该脚本：
➜ ~ db2 -tvf test.sql
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
select * from sysibm.sysdummy1
IBMREQD
-------
Y
1 record(s) selected.
connect reset
DB20000I The SQL command completed successfully.
对于本例中所用到的db2命令选项：
t：表示设置语句结束符，默认没有设置，所以要显式设置一下，默认结束符是;，所以不用显式指定；v：回显命令，这里v应该是verbose的缩写；f：指定文件，后面跟一个文件名，所以要把f放在最后面
注：可以用选项 d 来显式指定结束符，比如：
db2 -td@ -vf test.sql
当然，这就需要先把 test.sql 文件里每行末尾的 ; 换成 @ 才行。
注：可用 db2 ? options 查看 db2 命令选项的帮忙。
本文将使用db2 clp脚本来创建并运行存储过程。
不带参数的HelloWorld示例
创建文件 hello.sql ，内容如下：
connect to sample@
create or replace procedure hello()
begin
call dbms_output.put('hello world');
call dbms_output.new_line;
end@
set serveroutput on@
call hello@
drop procedure hello@
set serveroutput off@
connect reset@
在db2命令行运行 db2 -td@ -f hello.sql ，如下：
➜ temp0522 db2 -td@ -f hello.sql
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
Return Status = 0
hello world
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
DB20000I The SQL command completed successfully.
注：本例中省略了 v 选项，因为屏幕上东西太多了。如果想回显每一条命令，就加上 v 选项。
hello.sql 脚本可分为以下几部分：
- 连接DB；
- 创建存储过程
hello； - 打开
serveroutput； - 调用存储过程
hello； - 删除存储过程
hello； - 关闭
serveroutput； - 断开DB连接；
注： serveroutput 默认是关闭的，看不到 dbms_output 的输出，所以要显式打开它，用完之后再还原默认设置。
-
create or replace procedure hello()：使用create or replace procedure来创建/替换存储过程，本例中不涉及任何参数。 -
begin...end：包含了代码主体。本例只是简单的输出了hello world。
注意：在存储过程代码中，是用;作为语句结束符的。如果脚本本身也用;作为语句结束符，显然会造成冲突，所以我们选择使用@来作为脚本本身的语句结束符，这也是开发db2存储过程的惯例。 -
call hello：使用call命令来调用存储过程。本例中因为没有参数，所以直接call hello就行。 -
drop procedure hello：同理，删除存储过程时，因为没有参数，所以直接drop procedure hello就行。
带参数的HelloWorld示例
创建 hello2.sql 如下：
connect to sample@
create or replace procedure hello(in p1 varchar(100), out p2 varchar(100))
begin
set p2 = 'Hello ' || p1 || '!';
end@
call hello('Tom and Jerry', ?)@
drop procedure hello(varchar(100), varchar(100))@
connect reset@
然后运行脚本，如下：
➜ temp0522 db2 -td@ -f hello2.sql
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
DB20000I The SQL command completed successfully.
Value of output parameters
--------------------------
Parameter Name : P2
Parameter Value : Hello Tom and Jerry!
Return Status = 0
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
本例和前面例子的差异在于参数的使用。
create or replace procedure hello(in p1 varchar(100), out p2 varchar(100))：本例中有两个参数p1和p2，其中p1是in参数，p2是out参数（此外还有一种inout参数）。call hello('Tom and Jerry', ?)：在调用存储过程时，in参数需要指定值，而out参数用?来占位，其值将会被输出。本例中，p2的值如下：
Value of output parameters
--------------------------
Parameter Name : P2
Parameter Value : Hello Tom and Jerry!
drop procedure hello(varchar(100), varchar(100))：在删除存储过程时，必须指定参数的类型（无需指定in/out和具体的参数名字）。
在clp脚本中设置结束符
当我们写好了 hello.sql 脚本文件，交付使用时，还需要告诉使用者，用 -td@ 选项来运行。为了避免麻烦，可以在脚本中设置结束符。
创建 hello3.sql 文件如下：
connect to sample;
--#SET TERMINATOR @
create or replace procedure hello()
begin
call dbms_output.put('hello world');
call dbms_output.new_line;
end@
--#SET TERMINATOR ;
set serveroutput on;
call hello;
drop procedure hello;
set serveroutput off;
connect reset;
运行脚本，如下：
➜ temp0522 db2 -tf hello3.sql
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
Return Status = 0
hello world
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
DB20000I The SQL command completed successfully.
本例中，我们在创建存储过程前，使用 --#SET TERMINATOR @ 来设置结束符为 @ ，在创建存储过程之后使用 --#SET TERMINATOR ; 将结束符还原为 ; 。
注意：这里 SET TERMINATOR 必须使用大写字母！我一开始用了小写字母，运行结果一直报错，费了半天劲才找到原因。
运行脚本时，直接 db2 -tf hello3.sql 就行了。不需要指定语句结束符。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
