过程和函数

skywx

过程的说明
除了匿名块之外，还可以说明PL/SQL过程和函数。一个过程的语法是：
PROCEDURE  过程名[(参数1,参数2，……参数n)] is
[局部变量说明部分]
BEGIN
可执行部分
[例外处理部分]
END;
说明:
<参数1>到<参数n>是任选的参数说明部分,其组成是:
参数名 [in | out] 数据类型;
其中可选项[in|out]说明参加的形式，它可说明的形式有：
（1）        IN ：参数的值传递给过程或函数，但是没有值返回给调用它的PL/SQL子程序。在一个过程或函数内部，不能给说明为IN的参数赋值，只能引用这种类型参数的值。
（2）        OUT：过程和函数不能使用参数传递的值，但给调用它的PL/SQL子程序一个返回值。在一个过程或函数内部，不能引用说明为OUT的参数值，只能给这种类型参数赋值。
（3）        IN OUT：参数的值传递给过程或函数，并且给调用它的PL/SQL子程序一个返回值。也即既可以引用用这种类型也可以给它赋值。

例: 定义一个过程，求N的阶乘。
Declare
a number;
Procedure fac(n number,p out number) is
begin
P:=1;
For I in 1..n loop
P:=p*I;
End loop;
End;
Begin
A:=7;
Fac(5,a);
Dbms_output.put_line(a);
End;

函数的说明
语法：
  FUNCTION  函数名[(参数1,……参数n)]
  Return  函数数据类型  IS
  [局部变量声明部分]
  BEGIN
   可执行部分
  [例外处理部分]
  END;
说明:参数1到参数n的说明方法与过程相同。
      函数数据类型是函数返回值的类型。
      
例:定义一个函数，求N的阶乘。
  Declare
   A number;
Function fac(n number) return number is
  B number:=1;
Begin
  For I in 1.. n loop
   B:=b*I;
  End loop;
Return b;
End;
Begin
A:=fac(5);
Dbms_output.put_line(a);
End;


再次说明不同参数类型IN、OUT和IN OUT的使用
例 :
declare
this_arg1 number;
this_arg2 number;
this_arg3 number;
procedure  different_arguments
(arg1 in number,arg2 out number, arg3 in out number) is
begin
arg2:=arg1;
arg3:=arg3+1;
end;
begin
this_arg1:=3.14;
this_arg3:=7;
different_arguments(this_arg1,this_arg2,this_arg3);
end;
在此段程序中arg1只能做为输入，arg2只能做为输出,而arg3即可以做为输入又可做为输出。

函数和过程可以嵌套调用。
例: 求组合C(m,n)的值.
   我们知道组合计算公式为: C(m,n)=
那么我们可以定义一个函数用来求阶乘,一个函数用来求组合。求组合的函数多次调用求阶乘函数。
declare
a number;
function  fac(n  in number) return  number is
b number:=1;
begin
for i in 1..n loop
b:=b*i;
end loop;
return b;
end;
function c(m in number,n in number) return number is
begin  
return  fac(m)/(fac(n)*fac(m-n));
end;
begin
a:=c(4,2);
dbms_output.put_line(a);
end;

例:求1－ + － + －
  declare
suma number:=1;
temp number;
a number;
begin
a:=1;
for i in 1..5 loop
a:=a*i*(-1);
temp:=1/a;
suma:=suma+temp;
end loop;
dbms_output.put_line(suma);
end;

分析下面二段PL/SQL程序。
1、
Patient_id        Body_temp_deg_f
declare
new_patient_id patient.patient_id %type;
high_fever constant real:=42.0;
procedure  record_patient_temp_deg_c(patient_id
varchar2,body_temp_deg_c real) is
temp_deg_f real;
begin
temp_deg_f:=(9.0/5.0)*body_temp_deg_c+32.0;
insert into patient
(patient_id,body_temp_deg_f)
values
(patient_id,temp_deg_f);
end;
begin
new_patient_id:='gg9999';
record_patient_temp_deg_c(new_patient_id, high_fever);
end;

2、
Course_id        Additional_fees        Department_id
12        200        20
13        400        20
declare
  course_id  course.course_id%type;
  function  max_additional_fees(dept_id  in varchar2)
  return varchar2 is
  additional_fees course.additional_fees%type;
  units course.additional_fees%type;
  course_id course.course_id%type;
  begin
/*求本部门薪水最高的course_id*/
  select course_id
  into course_id from course
  where department_id=dept_id and
additional_fees=(select max(additional_fees)
from  course
  where department_id=dept_id);
  return course_id;
  end;
  --begining of executable section of  anonymous block
  begin
  dbms_output.enable;
  course_id:=max_additional_fees('39');
  dbms_output.put_line('course_id:'||course_id);
  end;
上次上机作业：
求数列1,1,2,3,5,8……的第100项的值。
方法1:
Declare
A number:=1;
B number:=1;
C number;
Begin
For I in 3..100 loop
   C:=a+b;
A:=b;
B:=c;
End loop;
Dbms_output.put_line(c);
End;
方法2:
Declare
A number:=1;
B number:=1;
Begin
For I in 1..49 loop
   a:=a+b;
   b:=a+b;
End loop;
Dbms_output.put_line(b);
End;

函数或过程可以递归调用。如求1+2+3+100之和
Declare
A number:=0;
function fac(m number) return number is
  n number;
begin
  if m=1 then
   return 1;
  else
  n:=m+fac(m-1);
   return n;
  end if;
end;
Begin
a:=fac(100);
Dbms_output.put_line(a);
End;
建立存储过程或函数
存储过程或函数是存储ORACLE数据库中的PL/SQL程序，可由用户直接或间接调用。使用存储过程和函数主要优越性是:
1、提高了效:在客户/服务器体系结构中，客户机应用向数据库服务器提出对SQL的需求。随着用户数量的增加，SQL        请求也就不断地增加，使网络很快就成为运行的瓶颈。使用存储过程可使运行性能得到显著的改进，因为对储过程的一个调用，即调用了在服务器中执行的多个SQL语句，从而减少了网络的拥挤。
2、可重用性:一个PL/SQL程序只需编写一次，即可用于各种地方.
3、可移植性：可在任何ORACLE数据库中使用存储过程，而不用考虑平台问题。
4、可维护性:一个存储过程用于完成一个特定的任务，如数据库触发器等需要调用该过程的地方均调用同一个存储过程，这样可降低软件维护的成本。
一、        存储过程
1、        建立存储过程的语法。
   CREATE [OR  REPLACE]  PROCEDURE 程储过程名
[（参数1,……参加n）]  IS
[局部变量声明部分]
BEGIN
可执行部分
[例外处理部分]
END;
  说明：OR REPLACE 选项是当此存储过程存在时覆盖此程储过程。
参数部分和过程定义的语法相同。

  例:定义一个存储过程用于删除students表中按学号指定的学生记录。
            CREATE OR REPLACE PROCEDURE  DELE
(STUID VARCHAR2) IS
          BEGIN
          DELETE  FROM  STUDENTS
WHERE  STU_ID =STUID;
END;

2、        调用存储过程
方法: EXECUTE 存储过程名(参数1,参数n);
说明:参数1到参数n的类型与存储过程定义的类型必须一致，且参数的个数必须相同。

  例:调用DELE存储过程删除学号为1的学生记录。
       EXECUTE  DELE(‘1’);

例:建立一个存储过程，在emp表中给按雇员号指定的人员增加工资,如果工资大于2000则增加50，否则如果工资大于1000则增加100，否则增加150。
CREATE  OR  REPLACE  PROCEDURE  ADDSAL
(EMPLOYNO  EMP.EMPNO%TYPE) IS
INCREMENT  NUMBER;
SALARY EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO SALARY FROM EMP
WHERE  EMPNO=EMPLOYNO;
IF SALARY>=2000 THEN
INCREMENT:=50;
ELSIF  SALARY>=1000 THEN
  INCREMENT:=100;
ELSE
  INCREMENT:=150;
END IF;
   UPDATE EMP
   SET  SAL=SAL+INCREMENT
   WHERE  EMPNO=EMPLOYNO;
END;

调用此存储过程,给雇员号为7369的记录增加相应的工资。
Execute   addsal(‘7369’);

二、        存储函数
1、        语法
CREATE  [OR REPLACE]  FUNCTION  函数名
[参数1,……参数n]
RETURN  函数数据类型   IS
[ 局部变量说明]
BEGIN
  可执行部分
  [例外处理部分]
  RETURN  函数的值
  END;
说明:函数数据类型是函数返回值的数据类型；
函数的值是返回给调用程序的数值。

例：建立一个存储函数，统计指定部门的人数。
CREATE  OR REPLACE  FUNCTION  COUNTNUM  (DEPNO  NUMBER)  RETURN  NUMBER  IS
     SUMA NUMBER;
     BEGIN
     SELECT COUNT(*)  INTO SUMA FROM EMP WHERE DEPTNO=DEPNO
RETURN  SUMA;
END;

调用此函数时注意不能把函数单独的写成一行。可写在PL/SQL赋值语句的右端。或写在SELECT语句中等等。
例：写一PL/SQL块统计10号部门和30号部门人数之和。
    Declare
    A  number;
Begin
A:=countnum(10)+countnum(30);
Dbms_output.put_line(a);
End;

   例：建立一个存储过程，将STUDENTS表中按学号指定的学生记录移至HISTORY表中。并在history表中增加移入日期 (删除STUDENTS表中的记录，同时录入到HISTORY表中)。
    Create  or replace procedure move( stuno varchar) is
   Begin
/*将students中学号为stuno的记录插入到history表中*/
  Insert into history(stu_id,name,sex,ldate)  
   select  stu_id,name,sex ,sysdate from students
where stu_id=stuno;
/*将students中学号为stuno的记录删除*/
delete from students
where stu_id=stuno;
end;        

调用此存储过程，将学号为1的记录移入历史表
execute  move(‘1’);

例:编写一存储函数,将华氏温度转换成摄氏温度。
Create or replace  function degf_to_degC(deg_f in number)
Return  number is
Deg_c number;
Begin
Deg_c:=(5.0/9.0)*(deg_f-32);
Return  deg_c;
End;
调用此函数，将PATIENT表中的body_temp以摄氏温度显示。
Select  patient_id, degf_to_degc(body_temp) from patient;

在建立存储过程或存储函数时获取错误信息的方法。
SHOW ERROR

检索存储过程
可以从数据字典视图user_source中查得。
如查看当前用户模式下的存储过程和函数
select  name,type from user_source;
查看一个具体的存储过程定义。例如查看move的定义可以用：
select  text  from user_source where name=’MOVE’;