10.2.7　有参存储过程

存储过程允许带有参数，参数的使用将增加存储过程的灵活性，给数据库编程带来极大的方便。本小节将介绍如何使用输入类型参数、输出类型参数和输入输出类型参数。

存储过程中如果使用了参数，在执行存储过程时必须为其指定参数。总的来说，参数可以是常量（也可以是经过初始化的变量）、变量、表达式等。而参数的赋值方式也比较灵活，下面的示例演示了在存储过程中如何传递参数。

过程有输入、输出、输入输出三种参数（这里不是指参数的数据类型）。其中，输入参数是默认的参数，也叫IN类型的参数。下面通过示例演示在存储过程中如何使用输入参数。

【示例7】使用输入参数的存储过程

该示例将根据输入的产品类型从表PRODUCTINFO中搜索符合要求的数据，并将其打印到屏幕。本示例中的参数为输入类型。具体脚本如下：

01 CREATE PROCEDURE PRODUCT_INTYPE_PRC(parm_ctgyname IN VARCHAR2)

02 AS

03 cur_ctgyid categroyinfo.categroyid%TYPE;—存放产品类型编码

04 cur_prtifo productinfo%ROWTYPE;—存放表productinfo的行记录

05

06 BEGIN

07 SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid

08 FROM CATEGROYINFO

09 WHERE CATEGROYINFO.CATEGROYNAME=parm_ctgyname;

10 —根据类型编码得到产品类型名称

11

12 IF SQL%FOUND THEN

13 DBMS_OUTPUT.PUT_LINE('————————————————');

14 DBMS_OUTPUT.PUT_LINE(parm_ctgyname||':');

15 END IF;

16 FOR my_prdinfo_rec IN

17 (

18 SELECT*FROM PRODUCTINFO WHERE CATEGORY=cur_ctgyid

19 )

20 LOOP

21 DBMS_OUTPUT.PUT_LINE(

22 '产品名称：'||my_prdinfo_rec.PRODUCTNAME

23 ||'产品价格：'||my_prdinfo_rec.PRODUCTPRICE

24 ||'产品数量：'||my_prdinfo_rec.QUANTITY

25 );

26 END LOOP;

27

28 EXCEPTION

29 WHEN NO_DATA_FOUND THEN

30 DBMS_OUTPUT.PUT_LINE('没有数据！');

31 WHEN TOO_MANY_ROWS THEN

32 DBMS_OUTPUT.PUT_LINE('数据过多！');

33 END;

34 /

【代码解析】

❑第1～2行创建存储过程。存储过程包括IN类型的参数，表示该参数为输入类型。此时可以省略关键字IN。

❑第3～4行表示声明过程内部变量。其中，cur_ctgyid表示产品类型编码，而cur_prtifo表示行类型的记录。

❑第7～9行表示用传进的参数parm_ctgyname作为查询条件从表CATEGROYINFO中找出对应的编码类型，并把该类型编码放到变量cur_ctgyid中。

❑第12～15行表示利用隐式游标的属性进行判断：如果有记录则把该类型名称输出到屏幕；如果没有记录或根据条件查询的记录过多，则会进入异常块部分。

❑第16～26行表示根据产品类型编码从表PRODUCTINFO中查询数据，并把得到的数据输出到屏幕。

❑第28～32行表示异常块部分。当出现NO_DATA_FOUND或TOO_MANY_ROWS异常时会中断操作进入异常部分。

【执行效果】

在SQL*Plus中创建该存储过程，并执行，得到的效果如图10.6所示。

图　10.6　示例7执行效果

从图10.6可以看到，当执行存储过程时输入了参数“洗衣机”，下面列出了3条相关数据。这是一个简单的示例，相信大家能很快掌握。

这里需要注意的是，如果存储过程有使用参数，那么调用该过程时一定要为其指定参数，否则会出现错误提示。不过可以采取一些措施避免此类错误的发生，例如使用默认值。接下来的示例将展示存储中的参数如何使用默认值。

【示例8】使用参数的默认值

本示例提供了根据输入的产品类型查询对应产品类型编码的功能。与上个示例不同的是，该示例中的输入参数使用了默认值功能。具体实现见如下脚本。

1）创建函数。函数将返回“雨具”字符串。

01 CREATE OR REPLACE FUNCTION DEFT RETURN VARCHAR2

02 IS

03 BEGIN

04 DBMS_Output.PUT_LINE('——已进入到函数——');

05 DBMS_Output.PUT_LINE('默认类型是雨具');

06 RETURN'雨具';

07 END DEFT;

08 /

【代码解析】

❑第1～2行表示创建一个函数，函数名为DEFT，返回值是VARCHAR2类型。

❑第4～5行表示简单输出。当调用时会输出这些提示语句。

❑第6行表示返回的值，这里返回“雨具”。也就是说，该函数返回值是“雨具”。

在SQL*Plus中执行该函数，并检验是否成功。函数的创建以及执行过程如图10.7所示。

图　10.7　函数DEFT的创建执行过程

从图10.7中可以看出，该函数已经创建完成并成功返回设定值。除此之外，在SQL*Plus中继续执行如下创建过程脚本。

2）创建存储过程。该过程输入产品类型，在表CATEGROYINFO中查询符合该产品类型的编码并输出到屏幕。

01 CREATE PROCEDURE PRODUCT_INTYPE_DEFT_PRC(parm_ctgyname

02 IN VARCHAR2 DEFAULT DEFT())

03 AS

04 cur_ctgyid categroyinfo.categroyid%TYPE;

05

06 BEGIN

07 SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid

08 FROM CATEGROYINFO

09 WHERE CATEGROYINFO.CATEGROYNAME=parm_ctgyname;

10

11 IF SQL%FOUND THEN

12 DBMS_OUTPUT.PUT_LINE('————————————————');

13 DBMS_OUTPUT.PUT_LINE(parm_ctgyname||'对应的编码是：'||cur_ctgyid);

14 END IF;

15

16 EXCEPTION

17 WHEN NO_DATA_FOUND THEN

18 DBMS_OUTPUT.PUT_LINE('没有数据！');

19 WHEN TOO_MANY_ROWS THEN

20 DBMS_OUTPUT.PUT_LINE('数据过多！');

21 END;

22 /

【代码解析】

❑第1～2行表示创建存储过程，该过程是带有IN类型的参数，并且有定义输入类型参数的默认值。该默认值为DEFT()函数。

❑第7～9行根据输入的参数值（产品类型）查询对应的产品类型编码，并放入变量cur_ctgyid中。

❑第11～14行表示如果有符合要求的记录则输出到屏幕。

❑第16～20行表示异常块，其中定义了两个异常，这里不再解释，读者可以参考前面的章节。

本示例为输入参数指定了默认值。也就是说，调用该存储过程时，如果没有为其指定参数，则该过程使用自定义的默认值，这里会执行DEFT函数，利用它的返回值作为参数值。

【执行效果】

在SQL*Plus中执行该过程，效果如图10.8所示。

图　10.8　示例8执行效果

从图10.8中可看到一共执行了两次该存储过程。第一次没有指定参数，但从效果中可以看出该过程没有提示错误，而是直接执行了DEFT函数的内容，并根据该函数返回的产品类型查询出了对应的产品类型编码。而第二次执行该过程指定了参数为“电视”，这次执行过程中并没有执行函数DEFT。由此可以看出，默认值只能在没有输入参数值的时候起作用。

提示　该过程使用了函数作为参数的默认值。其实，这里完全可以写一个确切的字符串作为默认值，如“雨具”。

存储过程中OUT类型的参数也被称为输出类型的参数。这样的存储过程会有相关返回值给调用它的程序，在程序中几乎可以把它当做一个变量使用，利用它给调用者中的变量初始化。下面的示例演示了如何使用该类型的参数。

【示例9】输出类型参数的使用

完成该示例需要两个步骤，第一部分就是被调用的存储过程，它里面包含OUT类型参数；第二部分就是调用者，这里也使用存储过程。详细实现过程如下：

1）创建被调用的存储过程。该过程提供根据输入的产品类型查询出对应的产品类型编码功能，并将得到的编码放到输出参数中。相关脚本如下：

01 CREATE PROCEDURE PRODUCT_OUTTYPE_PRC(parm_ctgyname IN VARCHAR2,

02 parm_ctgyid OUT VARCHAR2)

03 AS

04 BEGIN

05 SELECT CATEGROYINFO.CATEGROYID INTO parm_ctgyid

06 FROM CATEGROYINFO

07 WHERE CATEGROYINFO.CATEGROYNAME=parm_ctgyname;

08 IF SQL%FOUND THEN

09 DBMS_OUTPUT.PUT_LINE('传出参数是：'||parm_ctgyid);

10 END IF;

11

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

14 DBMS_OUTPUT.PUT_LINE('没有数据！');

15 WHEN TOO_MANY_ROWS THEN

16 DBMS_OUTPUT.PUT_LINE('数据过多！');

17 END PRODUCT_OUTTYPE_PRC;

18 /

【代码解析】

❑第1～3行表示创建存储过程，该过程有两个参数，其中第一个parm_ctgyname为输入参数，表示输入的是产品类型名称，而parm_ctgyid是输出参数，表示的是产品类型编码。

❑第5～7行表示根据产品名称查询产品编码。

❑第12～16行表示异常部分。

2）创建调用存储过程。该过程根据输入的产品类型以及价格从表PRODUCTINFO中查询符合要求的数据并输出到屏幕。详细脚本如下：

01 CREATE PROCEDURE PRODUCT_CLOUTTYPE_PRC(parm_ctgyname IN VARCHAR2,

02 parm_pric NUMBER)

03 AS

04 cur_ctgyid categroyinfo.categroyid%TYPE;—存放产品类型编码

05 cur_prtifo productinfo%ROWTYPE;—存放表productinfo的行记录

06

07 BEGIN

08 PRODUCT_OUTTYPE_PRC(parm_ctgyname,cur_ctgyid);

09 IF SQL%FOUND THEN

10 DBMS_OUTPUT.PUT_LINE('————————————————-');

11 DBMS_OUTPUT.PUT_LINE(parm_ctgyname||'对应的编码是：'||cur_ctgyid);

12 END IF;

13 FOR my_prdinfo_rec IN

14 (

15 SELECT*FROM PRODUCTINFO

16 WHERE CATEGORY=cur_ctgyid

17 AND PRODUCTINFO.PRODUCTPRICE＜parm_pric

18 )

19 LOOP

20 DBMS_OUTPUT.PUT_LINE(

21 '产品名称：'||my_prdinfo_rec.PRODUCTNAME

22 ||'产品价格：'||my_prdinfo_rec.PRODUCTPRICE

23 ||'产品数量：'||my_prdinfo_rec.QUANTITY

24 );

25 END LOOP;

26 END;

27 /

【代码解析】

❑第1～3行是创建过程，该过程有两个参数，分别为parm_ctgyname和parm_ctgyid，这两个参数都是输入类型的参数。

❑第4～5行表示声明两个变量。

❑第8行表示调用过程PRODUCT_OUTTYPE_PRC，该过程需要两个参数。其中，cur_ctgyid占用了输出参数的位置。也就是说，该过程的返回值将初始化给变量cur_ctgyid。

❑第9～12行判断并输出产品类型名称对应编码。

❑第13～25行表示循环输出符合记录的数据。其中，第15～17行表示查询指定产品类型以及低于指定价格的数据。

【执行效果】

在SQL*Plus中成功创建以上两个存储过程，并执行如下脚本调用PRODUCT_CLOUTTYPE_PRC过程。查询价格在3000以下的洗衣机相关记录。脚本如下：

EXEC PRODUCT_CLOUTTYPE_PRC('洗衣机',3000);

执行效果见图10.9。

图　10.9　示例9执行效果

使用OUT类型参数时需要注意以下两点：

1）OUT类型的参数需要用变量填充，而不能用常量或表达式填充。

2）如果过程中有被调用的过程因发生未处理的异常而退出，那么调用者通常得不到任何OUT参数的值（包括发生异常之前已经得到的OUT类型的参数值），而在发生的异常被处理的情况下退出，那么之前得到的OUT的参数值将会被调用者获得。

存储过程还有一种参数类型，是输入输出类型，也叫IN OUT类型参数。此类型的参数同OUT类型参数性质相似，不过它既支持输入也支持输出。以下就是该类型参数的示例。

【示例10】使用输入输出类型的参数

下面演示如何使用输入输出类型参数，完成该示例同样需要两个步骤。详细实现过程如下：

1）创建被调用的存储过程。该过程提供根据输入的产品类型编码和降价比例对表PRODUCTINFO的数据进行降价修改，并返回修改的记录数。脚本如下：

01 CREATE PROCEDURE PRODUCT_INOUTTYPE_PRC(parm_ctgyid IN VARCHAR2,

02 parmparm_pric IN OUT NUMBER)

03 AS

04 BEGIN

05 UPDATE PRODUCTINFO

06 SET PRODUCTPRICE=PRODUCTINFO.PRODUCTPRICE*(1-parmparm_pric)

07 WHERE PRODUCTINFO.CATEGORY=parm_ctgyid;

08 IF SQL%FOUND THEN

09 parmparm_pric:=SQL%ROWCOUNT;

10 END IF;

11 END PRODUCT_INOUTTYPE_PRC;

【代码解析】

❑第1～3行表示创建存储过程，其中包含两个参数：parm_ctgyid表示产品类型编码是输入参数，parmparm_pric用来输入时表示降价比例，而用来输出时则表示修改语句修改的记录数。

❑第5～7行表示根据提供的产品类型编码和降价比例修改PRODUCTINFO的价格记录。

❑第9行表示利用游标的属性得到修改的记录数。该记录数实际赋值给了该存储过程的输入输出参数parmparm_pric。

2）创建调用存储过程。该过程输入产品类型名称，根据产品类型名称查询出产品类型编码并调用PRODUCT_INOUTTYPE_PRC存储过程。最终输出修改的记录数。脚本如下：

01 CREATE PROCEDURE PRODUCT_CLINOUTTYPE_PRC(parm_ctgyname IN VARCHAR2)

02

03 AS

04 cur_ctgyid categroyinfo.categroyid%TYPE;—存放产品类型编码

05 cur_pric number;

06

07 BEGIN

08 SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid

09 FROM CATEGROYINFO

10 WHERE CATEGROYINFO.CATEGROYNAME=parm_ctgyname;

11

12 PRODUCT_INOUTTYPE_PRC(cur_ctgyid,cur_pric);

13 IF cur_pric＞0 THEN

14 DBMS_OUTPUT.PUT_LINE('共修改'||cur_pric||'条记录。');

15 END IF;

16

17 EXCEPTION

18 WHEN NO_DATA_FOUND THEN

19 DBMS_OUTPUT.PUT_LINE('没有数据！');

20 WHEN TOO_MANY_ROWS THEN

21 DBMS_OUTPUT.PUT_LINE('数据过多！');

22 END;

23 /

【代码解析】

❑第1～3行创建存储过程，参数表示产品类型名称。

❑第4～5行声明变量。cur_ctgyid用于存放产品类型编码，cur_pric用于存储在存储过程PRODUCT_INOUTTYPE_PRC返回的记录数。

❑第8～10行表示根据产品类型查询产品编码。

❑第12行表示调用存储过程PRODUCT_INOUTTYPE_PRC，此时利用返回值初始化cur_pric变量。

❑第13～15行表示利用条件语句判断后输出修改的记录数。

❑第17～21行表示异常块。

该示例同示例9的操作方式差别不大，接下来查看执行效果。

【执行效果】

在SQL*Plus中创建以上两个存储过程，确认没有错误提示后执行该过程。效果如图10.10所示。

图　10.10　示例10执行效果

给存储过程的参数赋值时，除了以上接触到的按照存储过程的参数顺序赋值之外，还有另外一种赋值的方式。这种方式可以看做显式地为某个变量赋值，它指定了赋值的参数对象，因此这种方式不用考虑原存储过程中参数的顺序。例如，在该示例中PRODUCT_CLINOUTTYPE_PRC过程的第12行，利用如下的赋值方式可以达到同样的效果：

PRODUCT_INOUTTYPE_PRC(parmparm_pric=＞cur_pric,parm_ctgyid=＞cur_ctgyid);

这段脚本表示的是参数parmparm_pric的值是变量cur_pric，而参数parm_ctgyid的值是变量cur_ctgyid。这样赋值的结果和以前没有差异，读者可以根据实际需求自行把握。