应用程序结构__过程

 子程序包括过程和函数。这里的所谓函数是指用户自定义函数。
从pl/sql程序设计的角度，也可以把子程序认为是pl/sql命名块，它存放在数据字典中，
可以在应用程序中进行多次调用。
 子程序存放在数据库服务器中，以编译方式运行，执行速度快。子程序一般是完成特定
功能的pl/sql程序块，具有一定的通用性，可以被不同应用程序多次调用，这样就简化了
应用程序的开发与维护，并能提高应用程序的性能。让用户程序通过调用子程序访问数据库，
而不是让用户程序直接访问数据库，这样做可以确保数据库的安全。

一、过程
 如果在用户应用中经常要执行某些操作，那么就可以将这些操作构造为一个过程。默认情况下，
用户定义的过程为该用户所拥有，数据库管理员可以把过程的使用权限授予其他用户。
1.定义过程
语法格式：
 

create [or replace] procedure procedure_name
  [(argument_name [in | out | in out] argument_type [, ...])]
 is | as
 begin
  procedure_body
 end [procedure_name];

--argument_name指定参数的名字；argument_type指定参数的数据类型
--[in | out | in out]指定参数的模式，其中in表示参数是输入给过程的，out表示参数在过程中将被赋值
--in out表示该类型的参数既可以向过程体传递值，也可以在过程体中赋值，可以传给过程体的外部。
--关键字is和as可任选其一

create or replace procedure display_teacher(
 v_no teacher.department_id%type
)
as
 v_wage teachers.wage%type;
 v_maxwage teachers.wage%type;
 v_minwage teachers.wage%type;
begin
 select avg(wage) into v_wage
  from teachers where department_id = v_no;
 select max(wage) into v_wage
  from teachers where department_id = v_no;
 select min(wage) into v_wage
  from teachers where department_id = v_no;
 dbms_output.put_line('该系平均工资为：' || v_wage);
 dbms_output.put_line('该系最高工资为：' || v_maxwage);
 dbms_output.put_line('该系最低工资为：' || v_minwage);
exception
 when no_data_found then
  dbms_output.put_line('该系不存在。');
end display_teacher;

 

2.调用过程
语法格式：
 call | execute procedure_name(argument_list);
--关键字call和execute任选其一；procedure_name指定调用过程的名称；
--argument_list指定调用过程所需要传递的参数列表。
set serveroutput on
--使用CALL语句
call display_teacher(101);
--使用execute语句
execute display_teacher(101);
3.过程的管理
 过程的管理包括查看已建立过程的有关信息、查看过程中的错误、修改过程中的错误及删除过程等。
（1）查看已建立过程的有关信息

 --通过数据字典中的user_objects视图，可以查看过程（对象）名object_name、过程建立时间created、
过程状态图status等信息。
select object_name, created, status
 from user_objects
 where object_name = 'display_teacher';
 --通过数据字典中的user_source视图，可以查看过程的源程序。
select text from user_source
 where name = 'display_teacher';

（3）查看与修改过程中的错误
 在建立过程时，如果oracle系统报告错误，可以通过sql中的命令show errors查看错误信息，
通过sql中的命令edit修改错误。

create or replace procedure display_teacher(
 v_no teacher.department_id%type
)
as
 v_wage teachers.wage%type;
 v_maxwage teachers.wage%type;
 v_minwage teachers.wage%type;
begin
 select avg(wage) into v_wage
  from teachers wheree department_id = v_no;
 select max(wage) into v_wage
  from teachers where department_id = v_no;
 select min(wage) into v_wage
  from teachers where department_id = v_no;
 dbms_output.put_line('该系平均工资为：' || v_wage);
 dbms_output.put_line('该系最高工资为：' || v_maxwage);
 dbms_output.put_line('该系最低工资为：' || v_minwage);
exception
 when no_data_found then
  dbms_output.put_line('该系不存在。');
end display_teacher;

 

--警告：创建的过程带有编译错误。
show errors
******
edit
（3）删除过程
语法格式：
 drop procedure procedure_name;
drop procedure display_teacher;
4.参数及其传递
 建立过程时，传递的参数为可选项。如果省略参数选项，则过程为无参数过程——定义时
不指定参数，调用时也不需要参数；如果指定参数选项，则过程为有参数过程——定义时需
要指定参数名字、模式、数据类型，调用时需要给出对应参数。定义过程时指定的参数称为
形参，调用过程时给出的参数称为实参。
（1）无参数过程

create or replace procedure display_systime
as
begin
 dbms_output.out_line('系统时间为：' || sysdate);
end display_system;
set serveroutput on
call display_systime();

 

（2）有参数过程
 带有参数的过程定义时需要指定参数名字、模式、数据类型，调用时需要给出对应参数。
--使用in参数

create or replace procedure app_student(
 v_no in students.student_id%type,
 v_monitor_id in students.monitor_id%type,
 v_name in students.name%type,
 v_sex in students.sex%type,
 v_dob in students.dob%type,
 v_specialty in students.specialty%type
)
as
begin
 insert into students values(
  v_no, v_monitor_id, v_name, v_sex, v_dob, v_specialty
 );
exception
 when dup_val_on_index then
  dbms_output.put_line('插入学生信息时，学生号不能重复');
end;
call app_student(101,102,'飞鸿','男','03-4月-1987','自动化');
--使用in和out参数
create or replace procedure display_edited(
 v_id in teachers.teacher_id%type,
 v_name out teachers.name%type,
 v_wage out teachers.wage%type
)
as
 v_title teachers.title%type;
begin
 select title into v_title
   from teachers where teacher_id = v_id;
 case
  when v_title = '教授' then
   update teachers
     set wage = 1.1 * wage where teacher_id = v_id;
  when v_title = '高工' or v_title = '副教授' then
   update teachers
     set wage = 1.1 * wage where teacher_id = v_id;
  else
   update teachers
     set wage = wage + 100 where teacher_id = v_id;
 end case;
 select name, wage into v_name, v_wage
  from teacher where teacher_id = v_id;
end display_edited;
variable v_name varchar2(10)
variable v_wage number
call display_edited(10101, :v_name, :v_wage);
--使用In out参数
create or replace procedure app_disp(
 v_id in out departments.department_id%type,
 v_name in out departments.department_name%type,
 v_address in out departments.address%type
)  
as
begin
 insert into departments
  values(v_id, v_name, v_address);
 v_id := v_id - 1;
 select department_id, department_name, address
  into v_id, v_name, v_address
   from departments
  where department_id = v_id;
exception
 when dup_val_on_index then
  dbms_output.put_line('插入系部信息时，系部号不能重复'。);
 when no_data_found then
  dbms_output.put_line('查询系部信息时，该系不存在。');
end;
variable v_id number
variable v_name varchar2(8)
variable v_address varchar2(40)
execute :v_id := 111
execute :v_name := '地理'
execute :v_address := 'X号教学楼';
call app_disp(:v_id, :v_name, :v_address);

 

（3）参数传递方式
 前面的例子，在调用带有参数的过程时，实参都是按照位置与形参进行对应传递的。
pl/sql同时也提供另外一种参数传递方式——按照参数名称传递。由于在传递多个参数时，
一些参数可以按照参数位置传递，另一些参数可以按照参数名称传递。因此，又有了混合传递参数的方式。
--参数传递使用名字传递方式
execute app_student(v_no => 10166, v_monitor_id => 10101,
 v_name => '张三', v_sex => '男', v_dob => '21-7月-1989', v_specialty => '计算机');
--参数传递使用混合传递方式
execute app_student(10177, 10101,
 v_name => '老四', v_sex => '男', v_dob => '22-5月-1989', v_specialty => '计算机');

 

过程和函数的区别：
过程：
    作为 PL/SQL 语句执行；
    在规范中不包含 RETURN 子句；
    不返回任何值（只有输入/输出参数，结果集）；
    可以包含 RETURN 语句，但是与函数不同，它不能用于返回值。
函数：
    作为表达式的一部分调用；
    必须在规范中包含 RETURN 子句；
    必须返回单个值；
    必须包含至少一条 RETURN 语句。