5,891
社区成员
发帖
与我相关
我的任务
分享存储过程
这段时间在研究通过Lotus Notes实时访问AS/400上的DB2数据库，并对PF进行Query操作，其间有涉及到如何通过Lotus Notes的ExecProcedure方法调用DB2的存储过程。
在经过熬夜了好几个晚上，对“存储过程”以及“Lotus Notes如何调用DB2的存储过程”的理解有感而发，本文将从2个篇幅进行阐述，仅供参考。
（一）存储过程的入门篇
（二）如何编写DB2存储过程以及Lotus Notes调用DB2存储过程篇
==============================================================
（一）存储过程的入门篇
★什么是存储过程呢？
定义：
将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
讲到这里,可能有人要问：这么说存储过程就是一堆SQL语句而已啊？
那么存储过程与一般的SQL语句有什么区别呢?
★存储过程的优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
★存储过程的种类：
1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
2.扩展存储过程 以XP_开头,用来调用操作系统提供的功能，如 exec master..xp_cmdshell 'ping 172.31.193.111' 。
3.用户自定义的存储过程,这是我们所指的存储过程 。
常用格式
Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
解释:
output：表示此参数是可传回的
with {recompile|encryption}
recompile:表示每次执行此存储过程时都重新编译一次
encryption:所创建的存储过程的内容会被加密
★应用举例：
如:
表book的内容如下
编号 书名 价格
001 C语言入门 $30
002 PowerBuilder报表开发 $52
实例1:查询表Book的内容的存储过程
create proc query_book
as
select * from book
go
exec query_book
实例2:加入一笔记录到表book,并查询此表中所有书籍的总金额
Create proc insert_book
@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
with encryption ---→加密
as
insert book(编号,书名，价格） Values(@param1,@param2,@param3)
select @param4=sum(价格) from book
go
执行例子:
declare @total_price money
exec insert_book '003','Delphi 控件开发指南',$100,@total_price
print '总金额为'+convert(varchar,@total_price)
go
存储过程的3种传回值:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中
实例3：设有两个表为Product,Order,其表内容如下：
Product
产品编号 产品名称 客户订数
001 钢笔 30
002 毛笔 50
003 铅笔 100
Order
产品编号 客户名 客户订金
001 南山区 $30
002 罗湖区 $50
003 宝安区 $4
请实现按编号为连接条件,将两个表连接成一个临时表,该表只含编号.产品名.客户名.订金.总金额,
总金额=订金*订数,临时表放在存储过程中。
代码如下:
Create proc temp_sale
as
select a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金 as总金额
into #temptable from Product a inner join Order b on a.产品编号=b.产品编号
if @@error=0
print 'Good'
else
print 'Fail'
go
（二）编写DB2存储过程和Lotus Notes调用DB2存储过程篇
待叙。。。
在经过熬夜了好几个晚上，对“存储过程”以及“Lotus Notes如何调用DB2的存储过程”的理解有感而发，本文将从2个篇幅进行阐述，仅供参考。
（一）存储过程的入门篇
（二）如何编写DB2存储过程以及Lotus Notes调用DB2存储过程篇
==============================================================
（一）存储过程的入门篇
★什么是存储过程呢？
定义：
将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
讲到这里,可能有人要问：这么说存储过程就是一堆SQL语句而已啊？
那么存储过程与一般的SQL语句有什么区别呢?
★存储过程的优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
★存储过程的种类：
1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
2.扩展存储过程 以XP_开头,用来调用操作系统提供的功能，如 exec master..xp_cmdshell 'ping 172.31.193.111' 。
3.用户自定义的存储过程,这是我们所指的存储过程 。
常用格式
Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
解释:
output：表示此参数是可传回的
with {recompile|encryption}
recompile:表示每次执行此存储过程时都重新编译一次
encryption:所创建的存储过程的内容会被加密
★应用举例：
如:
表book的内容如下
编号 书名 价格
001 C语言入门 $30
002 PowerBuilder报表开发 $52
实例1:查询表Book的内容的存储过程
create proc query_book
as
select * from book
go
exec query_book
实例2:加入一笔记录到表book,并查询此表中所有书籍的总金额
Create proc insert_book
@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
with encryption ---→加密
as
insert book(编号,书名，价格） Values(@param1,@param2,@param3)
select @param4=sum(价格) from book
go
执行例子:
declare @total_price money
exec insert_book '003','Delphi 控件开发指南',$100,@total_price
print '总金额为'+convert(varchar,@total_price)
go
存储过程的3种传回值:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中
实例3：设有两个表为Product,Order,其表内容如下：
Product
产品编号 产品名称 客户订数
001 钢笔 30
002 毛笔 50
003 铅笔 100
Order
产品编号 客户名 客户订金
001 南山区 $30
002 罗湖区 $50
003 宝安区 $4
请实现按编号为连接条件,将两个表连接成一个临时表,该表只含编号.产品名.客户名.订金.总金额,
总金额=订金*订数,临时表放在存储过程中。
代码如下:
Create proc temp_sale
as
select a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金 as总金额
into #temptable from Product a inner join Order b on a.产品编号=b.产品编号
if @@error=0
print 'Good'
else
print 'Fail'
go
（二）编写DB2存储过程和Lotus Notes调用DB2存储过程篇
待叙。。。
...全文
请发表友善的回复…
发表回复
zfk120 2006-08-28
- 打赏
- 举报
http://dev.wl668.com/DB2/dbdev/20055276003_3680002.shtml
http://dev.wl668.com/DB2/dbdev/20055276003_3593940.shtml
http://www.chinaunix.net/jh/22/116142.html
http://db.bibts.com/db2-t476011.htm
http://db.bibts.com/db2-t476011.htm
http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/0306haungs/0306haungs.html
http://bbs.tech.ccidnet.com/simple/index.php?t20651.html
http://dev.wl668.com/DB2/dbdev/20055276003_3153509.shtml
http://dev.wl668.com/DB2/dbdev/20055276003_3593940.shtml
http://www.chinaunix.net/jh/22/116142.html
http://db.bibts.com/db2-t476011.htm
http://db.bibts.com/db2-t476011.htm
http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/0306haungs/0306haungs.html
http://bbs.tech.ccidnet.com/simple/index.php?t20651.html
http://dev.wl668.com/DB2/dbdev/20055276003_3153509.shtml
zfk120 2006-08-28
- 打赏
- 举报
在进行DB2存储过程开发时，我们可以利用很多DB2自带的工具，例如开发中心，控制中心等。但有时使用脚本能带给开发人员更大的灵活性和更高的工作效率。
zfk120 2006-08-28
- 打赏
- 举报
（二）编写DB2存储过程和Lotus Notes调用DB2存储过程篇
使用存储过程的应用程序必须写成两个独立的过程。主程序包含在客户应用程序中，在客户机上执行。主调程序可以用任何被支持的宿主语言编写。存储过程在数据库服务器上执行，必须用一种支持数据库服务器的编程语言编写。
在下面的例子中，客户应用程序是一个用LotusScript编写的代理。我们也可以用Java编写代理或用DECS、NotesPump实时Notes作业去调用存储过程，如COBOL、REXX或Java 。
两部分程序必须分别构建。
★Lotus Notes调用DB2存储过程
我们用ExecProcedure方法去调用命名为testProce存储过程。首先要在运行存储过程的数据库中为存储过程编目，这使用CREATE PROCEDURE SQL语句。可以给存储过程传送多达30个参数（这是LotusScript的限制）。这些参数用于输入还是输出，或者是既用于输入、又用于输出，取决于为存储过程编目时对参数的定义。
客户程序例如LoadEmployee代理在客户机上运行，执行以下动作：
1.为可选数据结构和主机变量声明、分配和初始化存储空间：
Dim SalaryMedian As Long
Dim WorkDept As String
其中：
SalaryMedian 主机变量
WorkDept 主机变量
2.连接到数据库：
retcode%=Con.ConnectTo("DEMO","db2admin","db2admin")
其中：
DEMO 创建的访问DB2的ODBC名称
db2admin OS/400 user profile
db2admin Login password
3.通过ExecProcedure调用存储过程：
rc=res.ExecProcedure("testProce",SalaryMedian,workdept)
其中：
testProcedure 存储过程的注册名
SalaryMedian 一个输出变量，接收存储过程传递的值
WorkDept 存储过程的一个输出参数
4.从存储过程接收数据：
doc.SalaryMedian = SalaryMedian
5.断开同数据库的连接，隐式执行提交：
If Not Res Is Nothing Then Res.Close(DB_CLOSE)
If Not Con Is Nothing Then Con.Disconnect
★编写DB2存储过程
当存储过程被调用时,它执行以下动作:
1.从客户应用程序中接收SQLDA数据结构。当ExecProcedure方法执行时，主机变量通过SQLDA数据结构进行传递，SQLDA数据结构由数据库管理程序生成。
SQL_API_RC SQL_API_FN_testProcedure(void *reserved1,
void *reserved2,
struct sqlda * inout_sqlda,
struct sqlca *ca)
2.将数据由SQLDA赋值给局部变量,检查Domino程序送来数据的SQLTYPE和NULL指示。
if ((inout_sqlda->;sqlvar[1].sqltype==449) /*with null indicator*/
|(inout_sqlda->;sqlvar[1].sqltype=448)) /*w/o null indicator*/
strcpy(WorkDept,(char*) iinout_sqlvar[1].sqldata+2);
3.在数据库服务器上执行完整的过程。
存储过程返回一组职员的平均薪水(cl是字符串s1的指针)。
/*prepare a statement to obtain and order all salaries*/
strcpy(stmt,"SELECT salary FROM LIU.EMPLOYEE WHERE WORKDEPT='");
strcat(stmt,WorkDept);
strcat(stmt," ' ORDER BY salary");
EXEC SQL PREPARA s1 FROM :stmt;
/*determine the total number of records*/
EXEC SQL SELECT COUNT(*) INTO :num_records FROM LIU.EMPLOYEE WHERE WORKDEPT=:WorkDept;
/*fetch salaries until the median salary is obtained*/
EXEC SQL OPENc1;
while (counter++ <num_recorders/2+1)
EXEC SQL FETCH c1 USING DESCRIPTION :inout_sqlda
EXEC SQL CLOSE c1
EXEC SQL COMMIT c1
4.返回SQLCA信息。用SQLDA将输出数据返回到客户程序。
/*return sqlca to the calling program*/
memcpy (ca,&sqlca,sizeof(struct sqlca));
return(SQLZ_DISSCONNECT_PROC);
存储过程在被客户程序调用时执行。当服务器过程结束处理时，控制重新返回给客户程序。所以有许多存储过程，放于一个类库中。
上面举例的是为应用程序开发的C语言存储过程，存储过程运行在DB2服务器上，由Lotus Notes的LoadEmployee代理调用。整个过程比较复杂。整个C语言编写的代码由于版权问题所以没有全部摘录出来，但已基本贴出主要代码内容，仅供参考。
使用存储过程的应用程序必须写成两个独立的过程。主程序包含在客户应用程序中，在客户机上执行。主调程序可以用任何被支持的宿主语言编写。存储过程在数据库服务器上执行，必须用一种支持数据库服务器的编程语言编写。
在下面的例子中，客户应用程序是一个用LotusScript编写的代理。我们也可以用Java编写代理或用DECS、NotesPump实时Notes作业去调用存储过程，如COBOL、REXX或Java 。
两部分程序必须分别构建。
★Lotus Notes调用DB2存储过程
我们用ExecProcedure方法去调用命名为testProce存储过程。首先要在运行存储过程的数据库中为存储过程编目，这使用CREATE PROCEDURE SQL语句。可以给存储过程传送多达30个参数（这是LotusScript的限制）。这些参数用于输入还是输出，或者是既用于输入、又用于输出，取决于为存储过程编目时对参数的定义。
客户程序例如LoadEmployee代理在客户机上运行，执行以下动作：
1.为可选数据结构和主机变量声明、分配和初始化存储空间：
Dim SalaryMedian As Long
Dim WorkDept As String
其中：
SalaryMedian 主机变量
WorkDept 主机变量
2.连接到数据库：
retcode%=Con.ConnectTo("DEMO","db2admin","db2admin")
其中：
DEMO 创建的访问DB2的ODBC名称
db2admin OS/400 user profile
db2admin Login password
3.通过ExecProcedure调用存储过程：
rc=res.ExecProcedure("testProce",SalaryMedian,workdept)
其中：
testProcedure 存储过程的注册名
SalaryMedian 一个输出变量，接收存储过程传递的值
WorkDept 存储过程的一个输出参数
4.从存储过程接收数据：
doc.SalaryMedian = SalaryMedian
5.断开同数据库的连接，隐式执行提交：
If Not Res Is Nothing Then Res.Close(DB_CLOSE)
If Not Con Is Nothing Then Con.Disconnect
★编写DB2存储过程
当存储过程被调用时,它执行以下动作:
1.从客户应用程序中接收SQLDA数据结构。当ExecProcedure方法执行时，主机变量通过SQLDA数据结构进行传递，SQLDA数据结构由数据库管理程序生成。
SQL_API_RC SQL_API_FN_testProcedure(void *reserved1,
void *reserved2,
struct sqlda * inout_sqlda,
struct sqlca *ca)
2.将数据由SQLDA赋值给局部变量,检查Domino程序送来数据的SQLTYPE和NULL指示。
if ((inout_sqlda->;sqlvar[1].sqltype==449) /*with null indicator*/
|(inout_sqlda->;sqlvar[1].sqltype=448)) /*w/o null indicator*/
strcpy(WorkDept,(char*) iinout_sqlvar[1].sqldata+2);
3.在数据库服务器上执行完整的过程。
存储过程返回一组职员的平均薪水(cl是字符串s1的指针)。
/*prepare a statement to obtain and order all salaries*/
strcpy(stmt,"SELECT salary FROM LIU.EMPLOYEE WHERE WORKDEPT='");
strcat(stmt,WorkDept);
strcat(stmt," ' ORDER BY salary");
EXEC SQL PREPARA s1 FROM :stmt;
/*determine the total number of records*/
EXEC SQL SELECT COUNT(*) INTO :num_records FROM LIU.EMPLOYEE WHERE WORKDEPT=:WorkDept;
/*fetch salaries until the median salary is obtained*/
EXEC SQL OPENc1;
while (counter++ <num_recorders/2+1)
EXEC SQL FETCH c1 USING DESCRIPTION :inout_sqlda
EXEC SQL CLOSE c1
EXEC SQL COMMIT c1
4.返回SQLCA信息。用SQLDA将输出数据返回到客户程序。
/*return sqlca to the calling program*/
memcpy (ca,&sqlca,sizeof(struct sqlca));
return(SQLZ_DISSCONNECT_PROC);
存储过程在被客户程序调用时执行。当服务器过程结束处理时，控制重新返回给客户程序。所以有许多存储过程，放于一个类库中。
上面举例的是为应用程序开发的C语言存储过程，存储过程运行在DB2服务器上，由Lotus Notes的LoadEmployee代理调用。整个过程比较复杂。整个C语言编写的代码由于版权问题所以没有全部摘录出来，但已基本贴出主要代码内容，仅供参考。
