你的位置:首页 > 数据库

[数据库]PL/SQL编程_子程序设计


利用PL/SQL可以进行模块化程序设计

在一个PL/SQL块中,可以定义若干个子程序

把一些功能相对独立、需要经常执行的代码定义为一个子程序,在需要时根据子程序的名字进行调用。
这样不仅便于程序设计和编码,而且利于程序的调试。
PL/SQL有两种形式的子程序,即过程和函数。

在子程序中也可以定义变量、类型、游标等,也可以进行异常处理。
在调用于程序时,可以向子程序传递参数。

过程与函数的区别在于函数具有返回值,可以向调用者返回执行结果,而过程没有返回值。

如何使用过程

子程序的定义出现在PL/SQL块的声明部分,而它的调用则出现在PL/SQL块的可执行部分。
过程的定义格式如下:

PROCEDURE 过程名称 (参数1 定义, 参数2 定义 ...)

AS 或 IS

变量声明部分

BEGIN

可执行部分

EXCEPTION

异常处理部分

END;

在过程中可以定义参数,在调用该过程时,可以向过程传递实际参数。

如果没有参数,则过程名后面的圆括号及参数列表可以省略。

参数的定义形式为:

参数名 参数传递模式 数据类型 := 默认值

其中参数名和数据类型是必不可少的部分,其他两部分是可以省略的。

参数传递模式包括INOUT以及IN OUT三种形式,其中IN是默认的传递模式,如果没有指定,则默认为IN ,它指的是从调用者向过程中传递一个实际参数。

OUT是指从过程中向调用者传递参数,如果要使用这种传递模式,则需要明确指定。

在调用过程时,过程的执行情况会影响这个变量的值。
“ IN OUT”是一种双向传递模式,一方面从调用者向过程传递参数,另一方面从过程向调用者传递结果,如果要使用这种形式,则需要明确指定。

          IN      OUT        IN OUT
形式参数的作用  一个常量  没有初始化的变量  经过初始化的变量

实际参数的形式  常量、表达式、变量  必须是一个变量  必须是一个变量

参数默认值的作用是在调用过程时,如果没有提供实际参数,则将此默认值作为实际参数传递给过程。
数据类型用来指定参数的类型,在参数定义中不能指定对参数的约束条件,即不能指定参数的长度和是否为空等属性。
在下面的PL/SQL块中定义了两个过程,其中prompt过程用于对某部门的员工增加工资和奖金,而total_income过程用于计算某部门员工的总收入和应缴的税。

DECLARE
dno number;
procedure promption(salary IN integer, commiss IN integer, d_no IN integer:=0)
IS
BEGIN
if d_no=0 then --表示所有部门
UPDATE emp
set sal=sal+salary,comm=comm+commiss;
else --仅表示指定的部门
UPDATE emp
set sal=sal+salary,comm=comm+commiss
WHERE deptno=d_no;
END if;
END;
procedure total_income(d_no IN integer:=0)
IS
empno integer;
total number;
tax number;
BEGIN
if d_no=0 then --表示所有部门
SELECT sum(sal+nvl(comm, 0)), sum(sal*0.03) INTO total,tax FROM emp;
else --仅表示指定的部门
SELECT sum(sal+nvl(comm, 0)), sum(sal*0.03) INTO total,tax
FROM emp
WHERE deptno=d_no;
END if;
dbms_output.put_line ('总收入:' || total || '总税款: ' || tax) ;
END;
BEGIN --PL/SQL块的可执行部分
dno:=10;
promption(100,0,dno);
total_income(dno);
END;

在过程promption 中定义了三个参数,其中参数d_no带有默认值,这样在调用该过程时,如果没有为该参数提供实际参数,则使用默认值。
例如,在上述例子中,为部门10 中的员工增加了100元工资。
如果采用下面的调用形式,则为所有部门的员工增加100元工资:

promption(100, 0);

在调用过程时,需要为过程中的参数提供实际参数,它们在顺序上是对应的。

为了保证将参数正确地传给过程,要求在定义过程时,将所有带默认值的参数集中放在参数列表的右边。
因为只有这样才能将其他实际参数一对一地赋给前面的不带默认值的参数。
如果过程有多个参数,在调用过程时,也可以不按照参数列表的顺序提供实际参数,这时需要两种参数之间的对应关系。

例如,过程promption可以采用以下调用形式:
promption(d_no=>dno,commiss=>0, salary=>100);

过程total_inco1ne 中的参数d_no也带有默认值,这样在调用该过程时,如果提供了实际参数,则计算指定部门的员工总收入和总税款,如果采用以下调用形式:

total_income ( ) ;

则计算所有部门的员工总收入和总税款。
在上述两个过程中,所有参数的传递模式都是IN ,即把实际参数从调用者传递给过程。

这种形式是默认的,可以省略的关键字。
如果需要把过程的执行情况反映给调用者,则需要使用OUT形式,或者“ IN OUT”形式。
例如,为了计算员工总收入和总税款,并把结果反映到主程序中,对过程total_ income进行了一些改造,增加了两个参数,传递模式都是OUT 。
改造后的PL/SQL块代码如下:

DECLARE
dno number;
abed number;
xyz number;
procedure total_income(d_no IN integer,total OUT number,tax OUT number)
AS
empno integer;
BEGIN
if d_no=0 then --表示所有部门
SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total,tax FROM emp;
else --仅表示指定的部门
SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total,tax
FROM emp
WHERE deptno=d_no;
END if;
END;
BEGIN
dno:=10;
--PL/SQL块的可执行部分
total_income(dno,abed,xyz);
dbms_output.put_line ('总收入: ' || abed || ' 总税款: ' || xyz ) ;
END;

在调用过程total_income时,提供了三个实际参数,其中参数abed和xyz没有实际的值即使有,在这里也不起任何作用,因为这两个参数的传递模式是OUT过程在执行时,将参数total和tax的值传分别赋给abed和xyz ,这样就将过程中的数据传给了调用者。
注:IN类型参数是不能作为变量赋值的。

如何使用函数

函数是另一种形式的子程序,它不仅可以像过程那样定义数据和类型,传递参数,还可以向调用者返回执行结果。
函数的定义语法格式如下:

FUNCTION 函数名 (参数1, 参数2 ...)RETURN 数据类型

IS

  声明部分

BEGIN

  可执行部分

EXCEPTION

  异常处理部分

END;

其中参数的定义、传递模式都与在过程中的情况是相同的。

例如,下面这个函数的功能是根据参数n的值,计算1+2+3 +…+n的值,最后将结果返回。

FUNCTION total(n IN integer) RETURN integer
IS
result integer:=0;
i integer;
BEGIN
FOR i in 1 .. n LOOP
result:=result+i;
END LOOP;
RETURN result;
END;

从函数中应当向调用者传递一个返回值。

在函数参数列表之后的RETURN语句规定了函数的返回值类型,它可以是简单类型,也可以是记录、集合等复杂类型
在函数的可执行部分应当至少包含一个RETURN语句,用于向调用者返回执行结果。
任何一条RETURN语句的执行都将导致函数的执行结束,并返回调用者。
除了具有返回值外,函数在其他方面的用法与过程基本相同。

在调用函数时,可以将函数的返回值赋给一个变量,变量的类型必须与函数的返回值相同。
例如,考虑下面的PL/SQL块的代码:

DECLARE
total_income number;
total_tax number;
--PL/SQL块中的变量
function income(d_no emp.deptno%type) RETURN number --函数income
is
total number;
BEGIN
SELECT sum(sal+nvl(comm, 0)) INTO total
FROM emp
WHERE deptno=10;
RETURN total;
END;
function tax(d_no emp.deptno%type) RETURN number --函数tax
is
total number;
BEGIN --函数income的可执行部分
SELECT sum(sal+nvl(comm, 0))* 0.03 INTO total
FROM emp
WHERE deptno=10;
RETURN total;
END;
BEGIN --PL/SQL块的可执行部分
total_income:=income(10);
dbms_output.put_line ('总收入为2 ' || total_income) ;
total_tax:=tax( 10);
dbms_output.put_line ('总共应交税:' || total_tax);
END;

在这个PL/SQL块中定义了两个函数income和tax ,分别用于计算指定部门的员工的总收入和应当交纳的所得税。
两个函数都使用了一个参数,代表需要处理的部门号,由于没有指定参数的传递模式,故采用默认的IN方式。
在两个函数中经过计算后返回计算结果。
在PL/SQL块的可执行部分,分别调用了这两个函数,并将函数的返回值赋给了变量total_income和total_ tax ,然后进行打印。

在PL/SQL块的主程序和函数中都可以定义变量,主程序的变量定义在该块的声明部分,变量的作用范围是整个块,包括各个函数。
函数的变量定义在函数的声明部分,其作用范围仅限于该函数。
如果PL/SQL块的变量与函数的变量同名,那么主程序的变量在该函数中不起作用。
这个规则也适用于过程。
为了说明PL/SQL块的变量和函数变量的关系,我们再看看下面的PL/SQL块:

DECLARE
total number:=100;
procedure fun1
is
total number:=0;
BEGIN
dbms_output.put_line ('在函数fun1中total 的值为:' || total) ;
END;
procedure fun2
IS
BEGIN
dbms_output.put_line ( '在函数fun2 中total 的值为:' || total) ;
END;
BEGIN
total:=total+100;
fun1;
fun2;
dbms_output.put_line ('在块中total 的值为:' || total) ;
END;

在块的声明部分定义了一个变量total ,并赋初值为100 。

在函数fun1 中也定义了一个同名的变量,并赋初值为0 ,在该函数中起作用是自己的变量,所以调用函数fun1时变量total的值为0。
在函数fun2 中没有定义同名的变量,该函数中的变量total就是主程序中的变量,在调用该函数之前已经使这个变量增加了100 ,所以调用函数fun2时变量total的值为200 。
在主程序中当然使用的是自己的变量,而不可能是函数中的变量。

函数与过程的重载

函数与过程是一段具有特定功能的程序段,在PL/SQL块中根据它们的名字进行调用。

函数和过程的名字原则上可以由用户任意指定,只要满足命名规则即可,但是最好能够体现这段代码的功能。
在调用函数或过程时,根据它们的定义可能需要提供实际参数。

如果实际参数的类型或数目与形式参数不一致,子程序将出现错误信息,并停止执行。
如果需要对不同类型或不同数目的参数进行相似的处理,我们可以定义多个名字相同的函数,这就是子程序的重载。

重载子程序的名字相同,但是参数的类型或数目不同,返回值也可能不同。

如果完全相同,就不是重载了,而是重复定义,这是不允许的。
在调用重载子程序时,主程序将根据实际参数的类型和数目,自动确定调用哪个子程序。
例如,要对整数和浮点数分别求整数次幕,可以编写两个重载子程序,参数的类型分别是整数和浮点数。
这样在调用时,如果提供的实际参数是浮点数,则自动调用对浮点数求幕的子程序。
如果提供的实际参数是整数,则自动调用对整数求幕的子程序。

例如,在下面的代码中,定义了两个重载函数,分别对整数8和浮点数8.8求10次幕。

DECLARE
m integer;
int_number integer;
int_result integer;
float_number float;
float_result float;
function power(x integer,n integer) RETURN integer --对整数x求n次幂
is
result integer:=1;
BEGIN
for i in 1 .. n loop
result:=result*x;
END loop;
RETURN result;
END;
function power(x float,n integer) RETURN float --对浮点数x求n次幂
is
result float:=1;
BEGIN
for i in 1 .. n loop
result:=result*x;
END loop;
RETURN result;
END;
BEGIN
m:=10;
int_number:=8;
int_result:=power(int_number,m);
dbms_output.put_line ('对整数求幂的结果为:'|| int_result); --调用第一个power 函数
float_number:=0.8;
float_result:=power ( float_number, m) ; --调用第二个power 函数
dbms_output.put_line ('对浮点数求幂的结果为: '|| float_result);
END;

注:根据实际参数的类型,可以自动确定调用那个子程序,这个例子不好,float和integer类型相近,没法区分。

上面的PL/SQL块提供了两个重载函数,在调用时根据参数的类型不同自动确定调用哪个函数。
下面再看一个例子,根据参数的数目不同自动确定调用哪个函数。

DECLARE
procedure increase_salary(d_no emp.deptno%type, amount float)
IS
BEGIN
UPDATE emp set sal=sal+amount WHERE deptno=d_no;
END;
procedure increase_salary(amount float)
IS
BEGIN
UPDATE emp set sal=sal+amount;
END;
BEGIN
increase_salary(10,100.50); --调用第一个increase_salary过程
increase_salary(200); --调用第二个increase_salary过程
END;

在这个例子中定义了两个重载过程,用于为员工增加工资。

第一个过程有两个参数,分别是部门号和增加的额度。
第二个过程只布一个参数,即增加的额度。

在调用过程时,如果提供了部门号和增加额度两个实际参数,则调用第一个increase salary过程,为指定部门的员工增加工资。
如果只提供了增加额度这一个参数, 则调用第二个increase_salary过程,为所有员工增加工资。

函数和过程的递归调用

子程序定义好以后,需要在主程序或其他子程序中调用后才能执行,执行完后返回到调用者。
在有些情况下,子程序在执行过程中还可能要调用自己,调用结束后返回当前调用的地方。
子程序自己调用自己的现象称为递归调用。
考虑求整数n的阶乘的情况。

n !的值为n*( n- 1 ) ! ,为了求n的阶乘,首先要求出( n -1 ) ! 。
同样,要计算( n-1 ) !,首先要计算( n-2) !的值,一直到1 的阶乘,而 1的阶乘的值是已知的。
如果编写一个函数fact ,这个函数可以求得任何整数的阶乘,那么这个函数就是一个递归函数。
下面是求阶乘的递归过程:

fact(n)=n*fact( n-1)
=n*(n-1)*fact{n- 2)
...
= n*(n-1) * (n-2)* ... *fact(1)

在调用函数fact求n的阶乘时,首先要求n-1的阶乘,这时需要调用函数自己,不过这次传递的参数是n-1 。
同样,求n-l 的阶乘时,需要再次调用函数本身,求得n-2的阶乘,这次传递的参数是n-2 。
依此类推,最后要调用fact 函数求1的阶乘,而1的阶乘是已知的,这是递归返回的条件
求得1 的阶乘后,便可返回到调用fact ( 1 )的地方,求得2的阶乘。

求得2的阶乘后再返回到调用fact ( 2 )的地方,求得3的阶乘。

这样每返回一次,就可求得上一个数的阶乘,直到求得n的阶乘。
下面是一个求整数m的阶乘的PL/SQL块。

DECLARE
m integer;
result integer;
function fact(n integer)
RETURN integer
is
BEGIN
if n=1 then
RETURN 1;
else
RETURN n*fact(n-1); --递归调用
END if;
END;
BEGIN
m:=10;
result:=fact(m); --调用函数,求整数10 的阶乘
dbms_output.put_line(m|| '的阶乘为:' || result);
END;

再来看一个递归调用的例子。

表emp中存放的是公司员工的信息,其中包括员工号、员工姓名以及经理编号等信息。
以下是表emp中这三个列的数据:

SQL> SELECT empno, ename, mgr FROM emp;
EMPNO ENAME MGR
----- ---------- -----
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782

从查询的结果可以看出,除员工KING外,其他人都有一个经理,而经理同时也是一个员工,其中KING是公司的最高领导。
如果指定任何一个员工号,希望得到这个员工的经理,以及这个经理的经理,一直到最高领导这样的垂直、直接领导关系。
借助于子程序的递归调用,可以完成这样的要求。
以下是用过程递归的方法编写的一个PL/SQL块:

DECLARE
procedure manager(employee_no emp.empno%type)
IS
name emp.ename%type; --员工姓名
manager_no emp.empno%type; --员工经理的编号
manager_name emp.ename%type; --员工经理的姓名
BEGIN
SELECT ename,mgr INTO name,manager_no
FROM emp WHERE empno=employee_no;
if manager_no is not null then --如果员工的经理编号不为空,则查询其姓名
SELECT ename INTO manager_name FROM emp WHERE empno=manager_no;
dbms_output.put_line(name || '->' || manager_name);
manager(manager_no); --递归调用,查询该经理的经理
else --如果员工的经理编号为空,说明该员工即为最高领导
dbms_output.put_line(name|| '是最高层领导');
END if;
END;
BEGIN
manager(7369);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line ('没有这样的员工');
END;

KING是最高层领导
过程manager以人一个员工号为参数,首先查询该员工的经理编号。

如果经理编号为空,则说明该员工为公司的最高领导,这时打印相应的信息,并结束过程的执行。
否则查询该经理的姓名,并打印他们之间的领导关系,然后递归调用过程本身,以该经理的编号作为参数,继续查询他的经理的信息。
从程序的运行结果可以看出,编号为7369 的员工姓名为SMITH ,他的经理为FORD ,而
FORD的经理是JONES ,这样可以-直向上追溯到最高领导KINGO 。

为了防止在程序中指定一个不存在的员工号而导致程序执行出错,在程序中增加了异常处理,如果没有查询到任何信息,则打印相应的出错信息。