6.1 存储过程
存储过程是一种命名的PL/SQL程序块，既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作输入又作输出的参数，但它通常没有返回值。存储过程被保存在数据库中，他不可以被SQL语句直接执行或调用，只能通过EXECUTE命令执行或在PL/SQL程序块内部调用。
语法格式：
create [or replace] procedure pro_name [(parameter1[,parameter2]...)] is|as
begin
  plsql_sentences;
[exception]
  [dowith_sentences;]
end [pro_name];

create or replace procedure pro_insertDept is
begin
  insert into dept values(77,'市场拓展部','JILIN');
  commit;
  dbms_output.put_line('插入新纪录成功！');
end;
/

SQL>
Procedure created
SQL> execute pro_insertDept;
插入新纪录成功！
PL/SQL procedure successfully completed

SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON
    77 市场拓展部 JILIN

create or replace procedure pro_insertDept is
begin
  delete from dept where deptno=77;
  insert into dept values(77,'市场拓展部','JILIN');
  commit;
  dbms_output.put_line('插入新纪录成功！');
end;
/

SQL>
Procedure created

begin
  pro_insertDept;
end;
/

SQL>
插入新纪录成功！
PL/SQL procedure successfully completed

6.2 存储过程的参数
1、IN模式参数
默认参数模式，参数的类型不能指定长度

create or replace procedure insert_dept(
num_deptno in number,
var_ename in varchar2,
var_loc in varchar2) is
begin
  insert into dept
  values(num_deptno,var_ename,var_loc);
  commit;
end insert_dept;

（1）指定名称传递
向存储过程传递参数时需要指定参数名称，即参数名称在左侧，中间是赋值符号“=>”，右侧是参数值。
传递参数值与参数的定义顺序无关，但与参数个数有关。
语法格式：
pro_name(parameter1=>values1[,parameter2=>value2]...)

begin
  insert_dept(var_ename=>'采购部',var_loc=>'成都',num_deptno=>15);
end;
/

SQL>
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON
    77 市场拓展部 JILIN
    15 采购部 成都
6 rows selected

（2）按位置传递
begin
  insert_dept(28,'工程部','洛阳');
end;
/

SQL>
PL/SQL procedure successfully completed

SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON
    77 市场拓展部 JILIN
    15 采购部 成都
    28 工程部 洛阳
7 rows selected

SQL> desc insert_dept;
Parameter Type Mode Default?
---------- -------- ---- --------
NUM_DEPTNO NUMBER IN
VAR_ENAME VARCHAR2 IN
VAR_LOC VARCHAR2 IN

3、混合方式传递
SQL> exec insert_dept(38,var_loc=>'济南',var_ename=>'测试部');
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON
    38 测试部 济南
    77 市场拓展部 JILIN
    15 采购部 成都
    28 工程部 洛阳
8 rows selected

如果某个位置使用了“指定名称传递”方式传入参数值后，其后面的参数值也要使用“指定名称传递”。

2、out模式参数
表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中。
创建存储过程：
create or replace procedure select_dept(
num_deptno in number,
var_dname out dept.dname%type,
var_loc out dept.loc%type) is
begin
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno=num_deptno;
exception
  when no_data_found then
    dbms_output.put_line('该部门编号不存在');
end select_dept;

（1）在PL/SQL块中调用OUT模式的存储过程：这种方式需要在PL/SQL块得DECLARE部分定义与存储过程中out参数兼容的若干变量。

SQL> update dept
  2 set deptno=99
  3 where dname='市场拓展部';
1 row updated
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON
    38 测试部 济南
    99 市场拓展部 JILIN
    15 采购部 成都
    28 工程部 洛阳
8 rows selected

调用存储过程：
declare
  var_dname dept.dname%type;
  var_loc dept.loc%type;
begin
  select_dept(99,var_dname,var_loc);
  dbms_output.put_line(var_dname||'位于： '||var_loc);
end;
/

执行结果：
SQL>
市场拓展部位于： JILIN
PL/SQL procedure successfully completed

（2）使用exec命令执行out模式的存储过程：使用exec命令需要在sql*plus环境中使用variable关键字声明两个变量，用以存储out参数的返回值。
SQL> variable var_dname varchar2(50);
SQL> variable var_loc varchar2(50);
SQL> exec select_dept(15,:var_dname,:var_loc);
PL/SQL procedure successfully completed
var_dname
---------
采购部
var_loc
---------
成都

3、in out模式参数
可以从外界向该类型的参数传入值，在执行完存储过程后，可以将该参数的返回值传给外界。
创建存储过程：
create or replace procedure pro_square(
num in out number,
flag in boolean) is
i int:=2;
begin
  if flag then
    num:=power(num,i);
  else
    num:=sqrt(num);
  end if;
end pro_square;

调用存储过程：
declare
  var_number number;
  var_temp number;
  boo_flag boolean;
begin
  var_temp:=3;
  var_number:=var_temp;
  boo_flag:=true;
  pro_square(var_number,boo_flag);
  if boo_flag then
    dbms_output.put_line(var_temp||'的平方是：'||var_number);
  else
    dbms_output.put_line(var_temp||'的平方根是：'||var_number);
  end if;
end;
/

执行结果：
SQL>
3的平方是：9
PL/SQL procedure successfully completed

6.1.3 in参数的默认值
声明in参数的同时给其初始化默认值
创建存储过程：
create or replace procedure insert_dept(
num_deptno in number,
var_ename in varchar2 default '综合部',
var_loc in varchar2 default '北京') is
begin
  insert into dept
  values(num_deptno,var_ename,var_loc);
end insert_dept;

调用存储过程：
declare
  row_dept dept%rowtype;
begin
  insert_dept(57,var_loc=>'太原');
  commit;
  select * into row_dept from dept where deptno=57;
  dbms_output.put_line('部门名称是：'||row_dept.dname||'，位置是：'||row_dept.loc);
end;
/

执行结果：
SQL>
部门名称是：综合部，位置是：太原
PL/SQL procedure successfully completed

6.2 函数
6.2.1 创建函数
函数可以接受零个或者多个输入参数，并且函数必须有返回值。
语法格式：
create [or replace] function fun_name[(parameter1[,parameter2]...) return data_type is
  [inner_variable]
begin
  plsql_sentence;
[exception]
  [dowith_sentences;]
end [fun_name];
由于函数有返回值，所以在函数主体部分（即begin部分）必须使用return语句返回函数值，并且要求返回值的类型要与函数声明时的返回值类型（即data_type）相同。
创建函数：
create or replace function get_avg_pay(num_deptno number) return number is
  num_avg_pay number;
begin
  select avg(sal)
  into num_avg_pay
  from emp
  where deptno=num_deptno;
  return(round(num_avg_pay,2));
exception
  when no_data_found then
    dbms_output.put_line('该部门编号不存在');
    return(0);
end get_avg_pay;

6.2.2 调用函数
declare
  avg_pay number;
begin
  avg_pay:=get_avg_pay(10);
  dbms_output.put_line('平均工资是：'||avg_pay);
end;
/ 

SQL>
平均工资是：2916.67
PL/SQL procedure successfully completed

6.2.3 删除函数
语法格式：
drop function fun_name;

SQL> drop function get_avg_pay;
Function dropped


6.3 触发器
语法格式：
create [or replace] trigger tri_name
  [before|after|instead of] tri_event
  on table_name|view_name|user_name|db_name
  [for each row][when tri_condition]
begin
  plsql_sentences:
end tri_name;

触发器的组成部分：

1、触发器名称

2、触发语句

3、触发器限制

4、触发操作

触发器类型：

1、语句触发器

2、行触发器

3、INSTEAD OF触发器

4、系统条件触发器

5、用户事件触发器


6.3.2 语句级触发器
针对 一条DML语句而引起的触发器执行。无论数据操作影响多少行，触发器都只会执行一次。
（1）创建日志表dept_log
在scott模式下创建dept_log数据表，定义两个字段，存储操作种类信息和操作日期
SQL> create table dept_log
  2 (
  3 operate_tag varchar2(10),
  4 operate_time date);
Table created

（2）创建一个关于emp表的语句级触发器，将用户对dept表的操作信息保存到dept_log表中。
create or replace trigger tri_dept
  before insert or update or delete
  on dept
declare
  var_tag varchar2(10);
begin
  if inserting then
    var_tag:='插入';
  elsif updating then
    var_tag:='修改';
  elsif deleting then
    var_tag:='删除';
  end if;
  insert into dept_log
  values(var_tag,sysdate);
end tri_dept;

条件谓词通用的语法格式：
if inserting then
  do something about insert;
elsif updating then
  do something about update;
elsif deleting then
  do something about delete;
end if;

判断特定列是否被更新：
if updating(dname) then
  do something about update dname
end if;

（3）执行触发器
在数据表dept中实现插入、修改、删除3种操作，以便引起触发器tri_dept的执行。
SQL> insert into dept values(66,'业务咨询部','长春');
1 row inserted
SQL> update dept set loc='沈阳' where deptno=66;
1 row updated
SQL> delete from dept where deptno=66;
1 row deleted

（4）查看dept_log日志
SQL> select * from dept_log;
OPERATE_TAG OPERATE_TIME
----------- ------------
插入 2013/12/8 17
修改 2013/12/8 17
删除 2013/12/8 17

6.3.3 行级触发器
必须在语法中使用for each row，典型应用是给数据表生成主键值。
（1）创建一个带有主键列的数据表
SQL> create table goods
  2 ( id int primary key,
  3 good_name varchar2(50));
Table created

（2）为了给goods表的id列生成不能重复的有序值，需要创建一个序列
SQL> create sequence seq_id;
Sequence created

（3）创建一个触发器，为goods表的id列赋值
create or replace trigger tri_insert_good
  before insert on goods
  for each row
begin
  select seq_id.nextval
  into :new.id
  from dual;
end tri_insert_good;

列标识符：
原值标识符：用于标识当前行某个列的原始值，记作“:old.column_name”，通常在update和delete语句中使用。
新值标识符：用于标识当前行某个列的新值，记作“:new.column_name”，通常在insert和update语句中使用。

（4）向goods表中插入数据
SQL> insert into goods(good_name) values('苹果');
1 row inserted
SQL> insert into goods(id,good_name) values( 9,'葡萄');
1 row inserted

（5）验证触发器
SQL> select * from goods;
                                     ID GOOD_NAME
--------------------------------------- --------------------------------------------------
                                      1 苹果
2 葡萄

6.3.4 替换触发器
关键字：instead of
替换触发器定义在视图上。
（1）创建一个视图
SQL> conn sysdba/123456
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@stone AS SYSDBA
SQL> grant create view to scott;
Grant succeeded
SQL> conn SCOTT/TIGER
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SCOTT

SQL> create view view_emp_dept
  2 as select empno,ename,dept.deptno,dname,job,hiredate
  3 from emp,dept
  4 where emp.deptno=dept.deptno;

在没有创建替换触发器前向该视图插入数据，则报错。

（2）创建view_emp_dept视图在insert事件中的替换触发器。
create or replace trigger tri_insert_view
  instead of insert on view_emp_dept
  for each row
declare
  row_dept dept%rowtype;
begin
  select * into row_dept from dept where deptno=:new.deptno;
  if sql%notfound then
    insert into dept(deptno,dname)
    values(:new.deptno,:new.dname);
  end if;
  insert into emp(empno,ename,deptno,job,hiredate)
  values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end tri_insert_view;

（3）当触发器tri_insert_view成功创建后，再向view_emp_dept视图中插入数据时，oracle就不会产生错误信息，而是引起触发器tri_insert_view的运行，从而实现向emp表和dept表中插入两行数据。
SQL> insert into view_emp_dept(empno,ename,deptno,dname,job,hiredate)
  2 values(8888,'东方',10,'ACCOUNTING','CASHIER',sysdate);
1 row inserted
SQL> select * from view_emp_dept where empno=8888;
EMPNO ENAME DEPTNO DNAME JOB HIREDATE
----- ---------- ------ -------------- --------- -----------
 8888 东方 10 ACCOUNTING CASHIER 2013/12/8 1

6.3.5 用户事件触发器
引起用户事件触发器的常见事件DDL操作或者用户登录，退出等。包括：CREATE,ALTER,DROP,ANALYZE,COMMIT,GRANT,REVOKE,RENAME,TRUNCATE,SUSPEND,LOGON和LOGOFF等
（1）创建一个日志信息表，保存DDL操作的信息
create table ddl_oper_log
(
  db_obj_name varchar2(20),
  db_obj_type varchar2(20),
  oper_action varchar2(20),
  oper_user varchar2(20),
  oper_date date
);
SQL>
Table created

（2）创建一个用户触发器，将当前模式下的DDL操作信息保存到上面创建的ddl_oper_log日志信息表中。
create or replace trigger tri_ddl_oper
  before create or alter or drop
  on scott.schema
begin
  insert into ddl_oper_log values(
    ora_dict_obj_name,
    ora_dict_obj_type,
    ora_sysevent,
    ora_login_user,
    sysdate);
end tri_ddl_oper;

ora_dict_obj_name：获取DDL操作所对应的数据库对象
ora_dict_obj_type：获取DDL操作所对应的数据库对象的类型
ora_sysevent：获取触发器的系统事件名
ora_login_user：获取登录用户名

（3）进行DDL操作，引发触发器的执行。
SQL> create table tb_test(id number);
Table created
SQL> create view view_test as select empno,ename from emp;
View created
SQL> alter table tb_test add(name varchar2(10));
Table altered
SQL> drop view view_test;
View dropped
SQL> select * from ddl_oper_log;
DB_OBJ_NAME DB_OBJ_TYPE OPER_ACTION OPER_USER OPER_DATE
-------------------- -------------------- -------------------- -------------------- -----------
TB_TEST TABLE CREATE SCOTT 2013/12/8 1
VIEW_TEST VIEW CREATE SCOTT 2013/12/8 1
TB_TEST TABLE ALTER SCOTT 2013/12/8 1
VIEW_TEST VIEW DROP SCOTT 2013/12/8 1

6.4 程序包
程序包由PL/SQL程序元素（如变量，类型）和匿名PL/SQL块（如游标）、命名PL/SQL块（如存储过程和函数）组成。
程序包通常由规范和包主体组成。
6.4.1 程序包的规范
程序包规范一定要在包主体之前被创建
语法格式：
create [or replace] package pack_name is
[declare_variable];
[declare_type];
[declare_cursor];
[declare_funciton];
[declare_procedure];
end [pack_name];

create or replace package pack_emp is
function fun_avg_sal(num_deptno number)return number;
procedure pro_regulate_sal(var_job varchar2,num_proportion number);
end pack_emp;

6.4.2 程序包的主体
语法格式：
create [or replace] package body pack_name is
[inner_variable]
[cursor_body]
[function_title]
{begin
    fun_plsql;
[exception]
  [dowith_sentences;]
end [fun_name]}
[procedure_title]
{begin
    pro_plsql;
[exception]
    [dowith_sentences;]
end [pro_name]}
...
end [pack_name];

创建程序包pack_emp主体，实现对应规范中声明的函数和存储过程
create or replace package body pack_emp is
  function fun_avg_sal(num_deptno number) return number is
    num_avg_sal number;
  begin
    select avg(sal)
    into num_avg_sal
    from emp
    where deptno=num_deptno;
    return(num_avg_sal);
  exception
    when no_data_found then
    dbms_output.put_line('该部门编号不存在雇员记录');
    return 0;
  end fun_avg_sal;
  procedure pro_regulate_sal(var_job varchar2,num_proportion number)is
    begin
      update emp
      set sal=sal*(1+num_proportion)
      where job=var_job;
    end pro_regulate_sal;
end pack_emp;

创建一个匿名的PL/SQL块，通过程序包pack_emp调用其中的函数fun_avg_sal和存储过程pro_regulate_sal，并输出函数的返回结果。
declare
  num_deptno emp.deptno%type;
  var_job emp.job%type;
  num_avg_sal emp.sal%type;
  num_proportion number;
begin
  num_deptno:=10;
  num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);
  dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);
  var_job:='SALESMAN';
  num_proportion:=0.1;
  pack_emp.pro_regulate_sal(var_job,num_proportion);
end;
/

使用程序包的过程：

  1. 创建程序包规范

  2. 创建程序包主体

  3. 调用程序包中的子程序