PL/SQL 基础

最新推荐文章于 2024-06-06 17:58:45 发布

cuishan5183

最新推荐文章于 2024-06-06 17:58:45 发布

阅读量289

点赞数

一、PL/SQL编程

1.1 PL/SQL概述

1.1.1 PL/SQL块结构

PL/SQL程序都是以块(BLOCK）为基本单位
语法结构：
[DECLEAR]
--声明部分，可选
BEGIN
--执行部分，必须
[EXCEPTION]
--异常处理部分，可选
END;
每一条语句必须以分号结束。

SQL> set serveroutput on
SQL> declare
2 a int:=100;
3 b int:=200;
4 c number;
5 begin
6 c:=(a+b)/(a-b);
7 dbms_output.put_line(c);
8 exception
9 when zero_divide then
10 dbms_output.put_line('除数不许为零!');
11 end;
12 /
-3
PL/SQL procedure successfully completed.

1.1.2 代码注释和标识符

1、单行注释
由两个连字符“--”开始
SQL> declare
2 Num_sal number; --声明一个数值变量
3 Var_ename varchar2(20); --声明一个字符串变量
4 begin
5 select ename,sal into Var_ename,Num_sal from emp
6 where empno=7369; --检索指定的值并存储到变量中
7 dbms_output.put_line(Var_ename||'的工资是'||Num_sal);
8 end;

2、多行注释
由/*开头，由*/结尾

3、PL/SQL字符集

1.2 数据类型和定义变量和常量

1.2.1 基本数据类型

1、数值类型
number,pls_integer,binary_integer
number(p,s)
Num_Money number(9,2);
2、字符类型
varchar2,char,long,nchar,nvarchar2
varchar2(maxlength)
char(maxlength)
3、日期类型
date
存储空间是7个字节，分别使用一个字节存储世纪，年，月，天，小时，分钟和秒。
4、布尔类型
boolean
变量值可以是TRUE,FALSE和NULL

1.2.2 特殊数据类型

1、%type类型
声明一个与指定列名称相同的数据类型，紧跟在指定列名的后面。
var_job emp.job%type;
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> declare
2 var_ename emp.ename%type;
3 var_job emp.job%type;
4 begin
5 select ename,job into var_ename,var_job
6 from emp
7 where empno=7369;
8 dbms_output.put_line(var_ename||'的职务是
9 '||var_job);
10 end;
11 /
SMITH的职务是
CLERK
PL/SQL procedure successfully completed.
由于into子句中的变量只能存储一个单独的值，所以要求select子句只能返回一行数据。

2、record类型
定义record类型的语法格式：
type record_type_name is record
(
var_member1 data_type [not null][:=default_value],
...
var_member date_type [not null][:=default_value])

declare
type emp_type is record
(
var_name varchar2(20),
var_job varchar2(20),
var_sal number
);
empinfo emp_type;
begin
select ename,job,sal
info empinfo
from emp
where empno=7369;
dems_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);
end;

SQL> set serveroutput on
SQL> declare
2 type emp_type is record
3 (
4 var_ename varchar2(20),
5 var_job varchar2(20),
6 var_sal number
7 );
8 empinfo emp_type;
9 begin
10 select ename,job,sal
11 into empinfo
12 from emp
13 where empno=7369;
14 dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);
15 end;
16 /
雇员SMITH的职务是CLERK,工资是2758.33
PL/SQL procedure successfully completed.

3、%rowtype类型
存储从数据表中检索到的一行数据。
语法结构：
rowVar_name table_name%rowtype;
SQL> set serveroutput on
SQL> declare
2 rowvar_emp emp%rowtype;
3 begin
4 select *
5 into rowvar_emp
6 from emp
7 where empno=7369;
8 dbms_output.put_line('雇员'||rowvar_emp.ename||'的编号是'||rowvar_emp.empno||',职务是'||rowVar_emp.job);
9 end;
10 /
雇员SMITH的编号是7369,职务是CLERK
PL/SQL procedure successfully completed.

1.2.3 定义变量和常量

1、定义变量
语法格式：
<变量名><数据类型>[(长度）:=<初始值>];
var_countryname varchar2(50):='中国'；

2、定义常量
语法格式：
<常量名>constant<数据类型>:=<常量值>;
con_day constant integer:=365;

1.3 流程控制语句

1.3.1 选择语句

1、if...then语句
语法格式：
ifthen
plsql_sentence
end if;

if last_name is null then
...
end if;

编辑器中编辑如下：
declare
var_name1 varchar2(50);
var_name2 varchar2(50);
begin
var_name1:='east';
var_name2:='xiaoke';
if length(var_name1)<LENGTH(VAR_NAME2) then
dbms_output.put_line('字符串"'||var_name1||'"的长度比字符串"'||var_name2||'"的长度小');
end if;
end;
/

SQL> set serveroutput on
SQL>

字符串"east"的长度比字符串"xiaoke"的长度小

PL/SQL procedure successfully completed

2、if...then...else语句
语法格式：
ifthen
plsql_sentence1；
else
plsql_sentence2；
end if;

declare
age int:=55;
begin
if age>=56 then
dbms_output.put_line('您可以申请退休了！');
else
dbms_output.put_line('您小于56岁，不可以申请退休了！');
end if;
end;
/
SQL>

您小于56岁，不可以申请退休了！

PL/SQL procedure successfully completed

3、if...then...elsif语句
语法格式：
ifthen
plsql_sentence1；
elsifthen
plsql_sentence2；
...
else
plsql_sentencen；
end if;

declare
month int:=10;
begin
if month>=0 and month <=3 then
    dbms_output.put_line('这是春季');
elsif month>=4 and month <=6 then
    dbms_output.put_line('这是夏季');
elsif month>=7 and month <=9 then
    dbms_output.put_line('这是秋季');
elsif month>=10 and month <=12 then
    dbms_output.put_line('这是冬季');
else
    dbms_output.put_line('对不起，月份不合法！');
end if;
end;
/

SQL>

这是冬季

PL/SQL procedure successfully completed

4、case语句
语法格式：
case
whenthen plsql_sentence_1;
whenthen plsql_sentence_2;
...
whenthen plsql_sentence_n;
[else plsql_sentence;]
end case;

declare
season int:=3;
aboutinfo varchar2(50);
begin
case season
when 1 then
    aboutinfo:=season||'季度包括1,2,3月份';
when 2 then
    aboutinfo:=season||'季度包括4,5,6月份';
when 3 then
    aboutinfo:=season||'季度包括7,8,9月份';
when 4 then
    aboutinfo:=season||'季度包括10,11,12月份';
else
    aboutinfo:=season||'季节不合法';
end case;
dbms_output.put_line(aboutinfo);
end;
/

SQL>

3季度包括7,8,9月份

PL/SQL procedure successfully completed

1.3.2 循环语句

1、loop语句
程序至少执行一次循环体
语法格式：
loop
plsql_sentence;
exit when end_condition_exp;
end loop;

declare
sum_i int:=0;
i int:=0;
begin
loop
    i:=i+1;
    sum_i:=sum_i+i;
    exit when i=100;
end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;
/

SQL>

前100个自然数的和是：5050

PL/SQL procedure successfully completed

2、while语句
语法格式：
while condition_expression loop
plsql_sentence;
end loop;

declare
sum_i int:=0;
i int:=0;
begin
while i<=99 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;
/

SQL>

前100个自然数的和是：5050

PL/SQL procedure successfully completed

3、for语句
语法格式：
for variable_counter in [reverse] lower_limit..upper_limit loop
plsql_sentence;
end loop;

declare
sum_i int:=0;
begin
for i in 1..100 loop
    if mod(i,2)=0 then
      sum_i:=sum_i+i;
    end if;
end loop;
dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);
end;
/

SQL>

前100个自然数中偶数之和是：2550

PL/SQL procedure successfully completed

1.4 PL/SQL游标

游标的作用相当于指针
显式游标和隐式游标

1.4.1 显式游标

由用户声明和操作的一种游标，通常用于操作查询结果集。
处理数据的步骤：声明游标，打开游标，读取游标和关闭游标。
游标每次只能读取一行数据
1、声明游标
语法格式：
cursor cur_name[(input_parameter[,input_parameter2]...)]
[return ret_type]
is select_sentence;

ret_type：执行游标操作后的返回值类型。
select_sentence：游标所使用的select语句，为游标的反复读取提供结果集。
input_parameter1：作为游标的输入参数，指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：
para_name [in] datatype[{:=|default}para_value]

定义游标应该放在PL/SQL块得declare部分。
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;

2、打开游标
语法格式：
open cur_name[(para_value1[,para_value2]...)]'

3、读取游标
语法格式：
fetch cur_name into {variable};

declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
    from emp
    where job=var_job;
type record_emp is record
(
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;
begin
open cur_emp('MANAGER');
fetch cur_emp into emp_row;
while cur_emp%found loop
    dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'，工资是'||emp_row.var_sal);
    fetch cur_emp into emp_row;
end loop;
close cur_emp;
end;
/

SQL>

JONES的编号是7566，工资是2975
BLAKE的编号是7698，工资是2850
CLARK的编号是7782，工资是2450

PL/SQL procedure successfully completed

4、关闭游标
语法格式：
close cur_name;

1.4.2 游标的属性

%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为false。
%notfound：布尔型属性，与%fonud相反。
%rowcount：数字型属性，返回受SQL语句影响的行数。
%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

declare
var_ename varchar2(50);
var_job varchar2(50);
cursor cur_emp
is select ename,job
     from emp
     where empno=7499;
begin
    open cur_emp;
    fetch cur_emp into var_ename,var_job;
    if cur_emp%found then
      dbms_output.put_line('编号是7499的雇员名称为：'||var_ename||'，的职务是：'||var_job);
    else
      dbms_output.put_line('无数据记录');
    end if;
end;
/

SQL>

编号是7499的雇员名称为：ALLEN，的职务是：SALESMAN

PL/SQL procedure successfully completed

1.4.3 隐式游标

begin
update emp
set sal=sal*(1+0.2)
where job='SALESMAN';
if sql%notfound then
dbms_output.put_line('没有雇员需要上调工资');
else
dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
end if;
end;
/

SQL>

有4个雇员工资上调20%

PL/SQL procedure successfully completed

1.4.4 通过for语句循环游标

（1）在for语句中遍历隐式游标中的数据时，通常在关键字“in”的后面提供由select语句检索的结果集，在检索结果集的过程中，oracle系统会自动提供一个隐式的游标sql
begin
for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
loop
    dbms_output.put('雇员编号： '||emp_record.empno);
    dbms_output.put('；雇员名称： '||emp_record.ename);
    dbms_output.put_line('；雇员工资： '||emp_record.sal);
end loop;
end;
/

SQL>

雇员编号： 7499；雇员名称： ALLEN；雇员工资： 1920
雇员编号： 7521；雇员名称： WARD；雇员工资： 1500
雇员编号： 7654；雇员名称： MARTIN；雇员工资： 1500
雇员编号： 7844；雇员名称： TURNER；雇员工资： 1800

PL/SQL procedure successfully completed

（2）在for语句中遍历显式游标中的数据时，通常在关键字“in”的后面提供游标的名称
语法格式：
for var_auto_record in cur_name
loop
plsql sentence;
end loop;

declare
cursor cur_emp is
select * from emp
where deptno=30;
begin
for emp_record in cur_emp
loop
    dbms_output.put('雇员编号： '||emp_record.empno);
    dbms_output.put('；雇员名称： '||emp_record.ename);
    dbms_output.put_line('；雇员职务： '||emp_record.job);
end loop;
end;
/

SQL>

雇员编号： 7499；雇员名称： ALLEN；雇员职务： SALESMAN
雇员编号： 7521；雇员名称： WARD；雇员职务： SALESMAN
雇员编号： 7654；雇员名称： MARTIN；雇员职务： SALESMAN
雇员编号： 7698；雇员名称： BLAKE；雇员职务： MANAGER
雇员编号： 7844；雇员名称： TURNER；雇员职务： SALESMAN
雇员编号： 7900；雇员名称： JAMES；雇员职务： CLERK

PL/SQL procedure successfully completed

在使用游标（包括显式和隐式）的for循环中，可以声明游标，但不要进行打开游标，读取游标和关闭游标等操作，这些由oracle系统内部自动完成。

1.5 PL/SQL异常处理

oracle系统异常分为以下两大类：
预定义异常
自定义异常

1.5.1 预定义异常

命名的系统异常	产生原因
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游标已经打开
DUP_VAL_ON_INDEX	唯一索引对应的列上有重复的值
INVALID_CURSOR	在不合法的游标上进行操作
INVALID_NUMBER	内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND	使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为 0
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED	PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON	PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR	PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待资源时超时

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已处于打开状态的游标
ORA-6530	Access-INTO-null	试图为null 对象的属性赋值
ORA-6531	Collection-is-null	试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray 索引得引用大于集合中元素的个数

declare
var_empno number;
var_ename varchar2(50);
begin
select empno,ename into var_empno,var_ename
from emp
where deptno=10;
if sql%found then
    dbms_output.put_line('雇员编号： '||var_empno||'；雇员名称： '||var_ename);
end if;
exception
when too_many_rows then
    dbms_output.put_line('返回记录超过一行');
when no_data_found then
    dbms_output.put_line('无数据记录');
end;
/

SQL>

返回记录超过一行

PL/SQL procedure successfully completed

1.5.2 自定义异常

1、错误编号异常

declare
primary_iterant exception;
pragma exception_init(primary_iterant,-00001);
begin
insert into dept values(10,'软件开发部','深圳');
exception
when primary_iterant then
dbms_output.put_line('主键不允许重复！');
end;
/

SQL>

主键不允许重复！

PL/SQL procedure successfully completed

2、业务逻辑异常

declare
null_exception exception;
dept_row dept%rowtype;
begin
dept_row.deptno:=66;
dept_row.dname:='公关部';
insert into dept
values(dept_row.deptno,dept_row.dname,dept_row.loc);
if dept_row.loc is null then
    raise null_exception;
end if;
exception
when null_exception then
    dbms_output.put_line('loc字段的值不许为null');
    rollback;
end;
/

SQL>

loc字段的值不许为null

PL/SQL procedure successfully completed

二、过程、函数、触发器和包

2.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

2.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

4、 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

2.3 函数

2.3.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;

2.3.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

2.3.3 删除函数

语法格式：
drop function fun_name;

SQL> drop function get_avg_pay;

Function dropped

2.4 触发器

语法格式：
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、用户事件触发器

2.4.1 语句级触发器

针对一条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

2.4.2 行级触发器

必须在语法中使用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 葡萄

2.4.3 替换触发器

关键字：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

2.4.4 用户事件触发器

引起用户事件触发器的常见事件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

2.5 程序包

程序包由PL/SQL程序元素（如变量，类型）和匿名PL/SQL块（如游标）、命名PL/SQL块（如存储过程和函数）组成。
程序包通常由规范和包主体组成。

2.5.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;

2.5.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;
/