存储过程和函数
本节要点：
- 了解和使用子程序
- l 存储过程
- l 函数
- l 存储过程和函数的比较
1 了解和使用子程序
子程序是命名的PL/SQL块，编译并存储在数据库中。
子程序的各个部分：
- 声明部分
- 可执行部分
- 异常处理部分(可选)
子程序的分类：
- 存储过程 － 执行某些操作
- 函数 － 执行操作并返回值
子程序的优点：
- 模块化：将程序分解为逻辑模块
- 可重用性：可以被任意数目的程序调用
- 可维护性：简化维护操作
- 安全性：通过设置权限，使数据更安全
1) 存储过程
存储过程是用于完成特定任务的子程序，通俗可以认为是封装了一段或多段SQL语句的PL/SQL代码块。
1) 创建存储过程
语法：
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]/*创建存储过程，可指定运行过程需传递的参数*/
IS|AS
<local variable declaration>/*定义本地变量*/
BEGIN
<executable statements>/*执行语句*/
[EXCEPTION
<exception handlers>]/*异常处理*/
END;
示例：创建存储过程
create or replace procedure proc_stu(v_id in varchar2)
Is
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
dbms_output.put_line('学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class);
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end;
2) 执行存储过程
语法：
EXECUTE procedure_name(parameters_list);
示例：执行存储过程
EXECUTE proc_stu(‘007');
3) 存储过程参数的三种模式
- IN
- 用于接受调用程序的值
- 默认的参数模式
- OUT
- 用于向调用程序返回值
- IN OUT
- 用于接受调用程序的值，并向调用程序返回更新的值
示例：in和out的使用
创建存储过程
create or replace procedure proc_avgscore(stuid In varchar2,avgscore out number)
as
begin
select avg(f_grade) into avgscore from t_grade where f_stuid=stuid;
exception
when no_data_found then
dbms_output.put_line('未找到相应记录');
end;
调用存储过程
declare
avgscore number;
begin
proc_avgscore('001',avgscore);
dbms_output.put_line('学号为001的学生的平均成绩为：'||to_char(avgscore));
end;
示例：in out的使用
创建存储过程
create or replace procedure p_swap(p1 In Out number,p2 In Out number)
As
v_temp number;
begin
v_temp := p1;
p1 := p2;
p2 := v_temp;
end;
调用存储过程
declare
num1 number := 100;
num2 number := 500;
begin
p_swap(num1,num2);
dbms_output.put_line('num1='||num1);
dbms_output.put_line('num2='||num2);
end;
4) 将存储过程的执行权限授予其他用户
GRANT EXECUTE ON proc_stu TO SCOTT;
GRANT EXECUTE ON proc_swap TO PUBLIC;
5) 删除存储过程
DROP PROCEDURE proc_swap;
2) 函数
函数是可以返回值的命名的 PL/SQL 子程序。
1) 创建函数
语法：
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
定义函数的限制：
- 函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
- 形参必须是数据库类型，不能是 PL/SQL 类型
- 函数的返回类型也必须是数据库类型
访问函数的两种方式：
- 使用 PL/SQL 块
- 使用 SQL 语句
示例：
创建函数
CREATE OR REPLACE FUNCTION func_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友，您好';
END;
使用SQL访问
SELECT func_hello FROM DUAL;
示例：
创建函数
create or replace function func_stu(v_id varchar2)
return varchar2 As
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
return '学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class;
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end;
使用 PL/SQL 块访问
DECLARE
v_id char(3) := '001';
v_stuinfo VARCHAR2(100);
BEGIN
v_stuinfo := func_stu(v_id);
DBMS_OUTPUT.PUT_LINE(v_stuinfo);
END;
3) 存储过程和函数的比较
|
存储过程 |
函数 |
|
用于在数据中完成特定的操作或者任务 |
用于特定的数据 |
|
程序头部申明procedure |
程序头部申明function |
|
程序头部申明时不需要描述返回类型 |
程序头部申明时需要描述返回类型，而且PL/SQL至少要包含一个有效的return语句 |
|
可以使用in/out/ in out |
可以使用in/out/in out |
|
可作为一个独立的PL/SQL语句来执行 |
不能立即执行，必须作为表达式的一部分 |
|
可以通过out.int out |
通过return语句返回一个值 |
|
SQL语句中不得调用任何存储过程 |
SQL语句中可以调用函数 |
