Oracle 10~ PL/SQL函数与存储过程

1.PL/SQL函数：是通过关键字FUNCTION把复杂的逻辑封装进函数中，函数为使用者提供一个返回值。

PL/SQL函数主要由输入、逻辑计算和输出三部分组成，其语法结构为：

create [or replace] function 函数名
([p1,p2,...,pn])
return datatype
is|as
声明部分
begin
PL/SQL程序块
end

例如，编写一个函数计算学生某一门课程在班级内的排名。

create or replace function score_rank
(pm_in_stuid in varchar2, pm_in_courseid in varchar2)
return number
is
out_rank number := 0;
ls_score number :=0;
begin
select t.score into ls_score from score t
where t.stuid = pm_in_stuid and t.courseid = pm_in_courseid;
select count(1) into out_rank from score t
where t.courseid = pm_in_courseid and t.score > ls_score;
out_rank := out_rank + 1;
return out_rank;
exception
when no_data_found then dbms_output.put_line('no score!');
end;

select score_rank('sc201801001','c001') as rank001 from dual;

PL/SQL函数可以通过create or replace关键字进行覆盖，通过drop关键字进行删除。

2. 存储过程：存储在数据库中执行某功能的程序模块，它是由一段或多段PL/SQL语句或SQL语句组成的。创建语法如下：

create [or replace] procedure 过程名
(参数1 in|out 数据类型, 参数2 in|out 数据类型,...,参数n in|out 数据类型,)
is
声明部分
begin
过程体
end;

例，还是查询学生某一课程的成绩排名，用存储过程来实现。 

create or replace procedure score_rank_pro
(pm_in_stuid in varchar2, pm_in_courseid in varchar2,out_rank out number)
is
ls_score number :=0;
ls_pm number :=0;
begin
select t.score into ls_score from score t
where t.stuid = pm_in_stuid and t.courseid = pm_in_courseid;
select count(1) into ls_pm from score t
where t.courseid = pm_in_courseid and t.score > ls_score;
out_rank := ls_pm +1;
exception
 when no_data_found then
  dbms_output.put_line('no score!');
end;

调用存储过程需要赋输入输出值

set serveroutput on;
declare
ls_pm number;
begin
score_rank_pro('sc201801001','c001',ls_pm);
dbms_output.put_line('学号sc201801001的排名为：'||ls_pm);
score_rank_pro('sc201801002','c001',ls_pm);
dbms_output.put_line('学号sc201801002的排名为：'||ls_pm);
end;

存储过程和函数的区别：（1）存储过程的返回值类型和输入参数一块定义，而函数的返回值类型是单独定义的；

                                      （2）函数只能返回一个变量，而存储过程能返回多个变量；

                                      （3）函数可以直接被sql调用，存储过程可以调用函数。