» 游客:  注册 | 登录 | 会员 | 统计 | 帮助
斑竹的菜园子
 

作者:
标题: 存储过程的创建 上一主题 | 下一主题
borneol
管理员




积分 10952
发贴 955
注册 2005-2-23
来自 重庆
状态 离线
#1  存储过程的创建

存储过程实际就是服务器端运行的一个例程,该例程由客户调用执行。

创建存储过程的语法如下:

CREATE PROCEDURE name
[(param <datatype> [, param <datatype> )]
[RETURNS <datatype> [, param <datatype> )]
AS <procedure_body> [terminator]

<procedure_body> =
[<variable_declaration_list>]
<block>
<variable_declaration_list> =
DECLARE VARIABLE var <datatype>;
[DECLARE VARIABLE var <datatype>;
<block> =
BEGIN
<compound_statement>
[<compound_statement>
END
<compound_statement> = {<block> | statement;}

<datatype> = SMALLINT
| INTEGER
| FLOAT
| DOUBLE PRECISION
| {DECIMAL | NUMERIC} [(precision [, scale])]
| {DATE | TIME | TIMESTAMP)
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
[(int)] [CHARACTER SET charname]

| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]


下面我们用 表 来说明一下上面的语法:

语法字 说明
name 指要建立的存存储过程名称。存储过程的名称不能和已有的存储过程名称、数据表名、视图名称相同。
param datatype 输入参数。指用来传递给 Interbase 服务器的参数。Param 指的输入参数名,此参数名在本存储过程中不能有重复。Datatype 指的为 Interbase 数据库规定的数据类型。
RETURNS paramdatatype 输出参数。指存储过程的返回值。Param 指的输出参数名,此参数名在本存储过程中不能有重复。Datatype 指的为 Interbase 数据库规定的数据类型。存储过程在执行过程中遇到一个"SUSPEND"语句时将通过输入参数来返回值。
AS 用来分离存储过程头与存储过程体的一个关健字。
DECLARE VARIABLE var datatype 在一个存储过程中用来定义一个局部变量。每个局部变量必需以 DECLARE VARIABLE 开头,并且以";"结尾。Var 指要定义的局部变量名称。在同一个存储过程中,不能有同名的局部变量存在。
statement
在存储过程中,每一个单一的语句都必需在其尾部加上一个分号";"来分隔,Begin 和 End 语句除外。
terminator 用SET TERM 来标识一个存储过程的结束。


存储过程分两种类型:
一:选择型

选择型的存储过程从某种意义上说其实就相当于一个视图,它反回一个结果集,它的数据可以来自一个或多个表或视图。
一个简单的例子如下:
CREATE PROCEDURE PROC_CUSTOMER_SELE(
PBCITY VARCHAR(20)
)
RETURNS(
PBNAME VARCHAR(36),
PBCOMPANY VARCHAR(60),
PBTEL VARCHAR(30),
PBFAX VARCHAR(20)
)
AS
BEGIN
FOR SELECT
PBNAME,
PBCOMPANY,
PBTEL,
PBFAX
FROM PHONEBOOK
WHERE PBCITY=:PBCITY
INTO
:PBNAME,
:PBCOMPANY,
:PBTEL,
:PBFAX
DO
SUSPEND;
END
上面的存储过程需要我们传入一个PBCITY参数,即它返回所有城市等于:PBCITY的含姓名、公司名称、电话、传真的数据。
注意,上面的存存储过程中使用了一个FOR SELECT...DO 循环语名,用于返回多条记录,如果我们去掉FOR SELECT...DO 语名,则只能返回一条记录。
在Interactive SQL中输入
SELECT * FROM PROC_CUSTOMER_SELE("重庆");
执行上列语名,我们也就提取了所有表中城市为重庆的数据。

 

二:执行型

执行型的存储过程不返回结果集,它只是对表中的数据进行一种逻辑操作,如通过一些参数传递还更新一组数据等,也可对表中数据进行完整性验证。我们后面要讲到的触发器有点象执行型的存储过程。举一个最简单例子:
CREATE PROCEDURE "PROC_ADD_CWSR"
(
KMDM VARCHAR(6),
SRMS VARCHAR(60),
SRJR NUMERIC(16, 2),
SRBZ BLOB
)
AS

BEGIN
/*插入数据*/
Insert into CWSR
VALUES('NOW',:KMDM,:SRMS,:SRJR,:SRBZ);
SUSPEND;
END
此存储过程向表CWSR中插入数据。
【注】在Interbase中/* */之间的数据表示注解,和 C++ 语法一样。
我们再来看一个复杂一点的存储过程:
CREATE PROCEDURE SHIP_ORDER
(
/*参数*/
PO_NUM CHAR(8)
)
AS

/*定义变量*/
DECLARE VARIABLE ord_stat CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no INTEGER;
DECLARE VARIABLE any_po CHAR(8);
BEGIN
/* 提取数据*/
SELECT s.order_status, c.on_hold, c.cust_no
FROM sales s, customer c
WHERE po_number = :po_num
AND s.cust_no = c.cust_no
INTO :ord_stat, :hold_stat, :cust_no;

/* 订单已结单*/
IF (ord_stat = 'shipped') THEN
BEGIN
/*触发异常*/
EXCEPTION order_already_shipped;
SUSPEND;
END

ELSE IF (hold_stat = '*') THEN
BEGIN
EXCEPTION customer_on_hold;
SUSPEND;
END

FOR SELECT po_number
FROM sales
WHERE cust_no = :cust_no
AND order_status = 'shipped'
AND paid = 'n'
AND ship_date < CAST('NOW' AS DATE) - 60
INTO :any_po
DO
BEGIN
EXCEPTION customer_check;

UPDATE customer
SET on_hold = '*'
WHERE cust_no = :cust_no;

SUSPEND;
END

UPDATE sales
SET order_status = 'shipped', ship_date = 'NOW'
WHERE po_number = :po_num;

SUSPEND;
END

执行型的存储过程一般用 EXECUTE PROCEDURE 执行。如:
EXECUTE PROCEDURE SHIP_ORDER("V9333005");



0200 1D 06 00 00 A0 86 01 00 FF FF 00 00 1E 06 00 00
         E-mail:webmaster@chenzhang.com
         MSN:borneol_net@hotmail.com
         Home page:www.chenzhang.com
0210 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
2005-3-10 14:41
查看资料  发送邮件  访问主页  发短消息  QQ   编辑帖子  引用回复
紫罗兰
雇农





积分 1
发贴 1
注册 2005-9-22
状态 离线
#2  

请问带返回参数的SP怎么调用?
怎么在select语句中使用if语句?

2005-9-23 10:11
查看资料  发送邮件  发短消息   编辑帖子  引用回复


可打印版本 | 推荐给朋友 | 订阅主题 | 收藏主题



论坛跳转: