PL/SQL

1PL/SQL基本概念

PL/SQL是Oracle对标准 数据库语言SQL的过程化扩充，它将数据库技术和 过程化程序设计语言联系起来，是一种应用开发语言，可使用循环，分支处理数据，将SQL的数据操纵功能与过程化语言 数据处理功能结合起来.PL/SQL的使用，使SQL成为一种高级程序设计语言，支持高级语言的块操作，条件判断， 循环语句，嵌套等，与数据库核心的 数据类型集成，使SQL 的程序设计效率更高.

⒈1 PL/SQL的作用

使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句可能也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的一些优点：

⒈能够使一组SQL语句的功能更具模块化程序特点；

⒉采用了过程性语言控制程序的结构；

⒊可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；

⒋具有较好的可移植性，可以移植到另一个 Oracle数据库中；

⒌集成在数据库中，调用更快；

⒍减少了网络的交互，有助于提高程序性能。

通过多条SQL语句实现功能时，每条语句都需要在 客户端和 服务端传递，而且每条语句的执行结果也需要在网络中进行交互，占用了大量的网络带宽，消耗了大量网络传递的时间，而在网络中传输的那些结果，往往都是中间结果，而不是我们所关心的。

而使用PL/SQL程序是因为程序代码存储在数据库中，程序的分析和执行完全在数据库内部进行，用户所需要做的就是在 客户端发出调用PL/SQL的执行命令，数据库接收到执行命令后，在数据库内部完成整个PL/SQL程序的执行，并将最终的执行结果返馈给用户。在整个过程中网络里只传输了很少的数据，减少了 网络传输占用的时间，所以整体程序的执行性能会有明显的提高。

⒈2 PL/SQL程序的基本结构

PL/SQL块由四个基本部分组成：声明、执行体开始、 异常处理、执行体结束。

下面是四个部分的基本结构：

DECLARE —— 可选部分

变量、 常量、 游标、用户定义异常的声明

……

BEGIN —— 必要部分

SQL语句和PL/SQL语句构成的执行程序

……

EXCEPTION —— 可选部分

程序出现异常时，捕捉异常并处理异常

……

END；—— 必须部分

在数据库执行PL/SQL程序时，PL/SQL语句和SQL语句是分别进行解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oracle的SQL引擎处理，两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互、处理过程。

折叠编辑本段2PL/SQL的变量

就像其他的程序语言一样，变量是在程序中出现最频繁的名词，在PL/SQL中的学习中首先需要了解变量的一些基本概念和使用方法。

PL/SQL程序包括了四个部分，在四个部分中，声明部分主要用来声明 变量并且初始化变量，在执行部分可以为变量赋新值，或者在 表达式中 引用变量的值，在 异常处理部分同样可以按执行部分的方法使用变量。另外，在PL/SQL程序使用时可以通过参数变量把值传递到PL/SQL块中，也可以通过输出变量或者参数变量将值传出PL/SQL块。

在定义变量、 常量 标识符时需要注意下面的一些基本规则：

⒈定义的 标识符名称应该遵循命名规则，在后面将会提到主要的命名规则；

⒉在声明 常量和 变量的时候可以为其设置初始化值，也可以强制设置not null；

⒊可以使用 赋值运算符（：=）或DEFAULT 保留字来初始化 标识符，为标识符赋初始值；

⒋在声明 标识符时，每行只能声明一个标识符。

在PL/SQL中主要使用下面三种类型的 变量（或者 常量）：

⒈简单 变量；

⒉复合（组合） 变量；

⒊ 外部变量。

三种 变量分别用于存放不同特性的数据。

折叠编辑本段3PL/SQL的基本语法

在写PL/SQL语句时，必须遵循一些基本的语法，下面是PL/SQL程序代码的基本语法要求：

⒈语句可以写在多行，就像SQL语句一样；

⒉各个关键字、字段名称等等，通过空格分隔；

⒊每条语句必须以分号结束，包括PL/SQL结束部分的END关键字后面也需要分号；

⒋ 标识符需要遵循相应的命名规定；

⑴名称最多可以包含30个字符；

⑵不能直接使用 保留字，如果需要，需要使用双引号括起来；

⑶第一个 字符必须以字母开始；

⑷不要用数据库的表或者科学计数法表示；

还有一些语法相关的规则：

⒈在PL/SQL程序中出现的字符值和日期值必须用单引号括起；

⒉数字值可以使用简单数字或者科学计数法表示；

⒊在程序中最好养成添加注释的习惯，使用注释可以使程序更清晰，使开发者或者其他人员能够很快的理解程序的含义和思路。在程序中添加注释可以采用：

⑴/*和*/之间的多行注释；

⑵以--开始的单行注释。

折叠编辑本段4过程，函数与包

过程:执行特定操作

函数:用于返回特定数据

折叠4.1过程

语法:create [orreplace] procedure procedure_name(argument1 [model]datatype1,argment2 [mode2],...)

is [as]

pl/sql block;

1.建立过程:不带任何参数

create or replaceprocecdure out_time

is

begin

dbms_output.put_line(systimestemp);

end;

2.调用过程

set serveroutputon

exec out_time

set serveroutputon

call out_time();

3.建立过程:带有IN参数

create or replaceprocedure add_employee

(eno number,namevarchar2,sal number,job varchar2 default 'clerk',dno number)

is

e_integrityexception;

pragmaexception_init(e_integrity,-2291);

begin

insert intoimp(empno,ename,sal,job,deptno) valres(eno,name,sal,job,dno);

exception

whendup_val_on_index then

raise_application_error(-20000,'雇员号不能重复');

whene_integrity then

raise_application_error(-20001,'部门不存在');

end;

exec add_employee(1111,'clark',2000,'manager',10)

4.建立过程:带有OUT参数

create or replaceprocedure qry_employee

(eno number,name outvarchar2,salary out number)

is

begin

selectename,sal into name,salary from emp where empno=eno;

exception

whenno_date_found then

raise_application_error(-20000,'该雇员不存在');

end;

当在应用程序中调用该过程时,必须要定义变量接受输出参数的数据

sql>var name varchar2(10)

var salary number

exec qry_employee(7788,:name,:salary)

print name salary

5.建立过程:带有INOUT参数(输入输出参数)

create or replaceprocedure compute

(num1 in outnumber,num2 in out number)

is

v1number;

v2number;

begin

v1:num1/num2;

v2:mod(num1,num2);

num1:=v1;

num2:=v2;

end;

sql>var n1 number

var n2 number

exec :n1:=100

exec :n2:=30

exec ecmpute(:n1,:n2)

print n1 n2

6.为参数传递变量和数据

位置传递,名称传递,组合传递三种

1.位置传递:在调用子程序时按照参数定义的顺序为参数指定相应的变量或数值

exec add_dept(40,'sales','new york');

exec add_dept(10);

2.名称传递:在调用子程序时指定参数名,并使用关联符号=>为其提供相应的数值或变量

execadd_dept(dname=>'sales',dno=>50);

exec add_dept(dno=>30);

3.组合传递:同时使用位置传递和名称传递

exec add_dept(50,loc=>'new york');

execadd_dept(60,dname=>'sales',loc=>'newyork');

7.查看过程原代码

oracle会将过程名,源代码以及其执行代码存放到数据字典中.执行时直接按照其执行代码执行

可查询数据字典(user_source)

select textfrom user_source where name='add_dept';

删除过程

dropprocedure add_dept;

折叠4.2函数

用于返回特定函数

语法:create [orreplace] function function_name

(argument1 [mode1] datatype1,

argument2 [mode2] datatype2,

.....)

returndatatype --函数头部必须要带有RETURN子句,至少要包含一条RETURN语句

is|as pl/sql block;

1.建立函数:比带任何参数

create or replacefunction get_user

return varchar2

is

v_uservarchar2(100);

begin

selectusername into v_user from user_users;

returnv_user;

end;

2.使用变量接受函数返回值

sql>var v1 varchar2(100)

exec :v1:=get_user

print v1

在SQL语句中直接调用函数

selectget_user from d l;

使用DBMS_OUTPUT调用函数

setserveroutput on

execdbms_output.put_line('当前数据库用户:'||ger_user)

3.建立函数:带有IN参数

create orreplace function get_sal(name in varchar2)

returnnumber

as

v_sal emp.sal%type;

begin

select sal into v_sal from emp where upper(ename)=upper(name);

return v_sal;

exception

when no_data_found then

raise_application_error(-20000,'该雇员不存在');

end;

4.建立函数:带有out参数

create or replacefunction get_info(name varchar2,title out varchar2)

return varchar2

as

deptnamedept.dname%type;

begin

selecta.job,b.dname into title,deptname from emp a,dept b anda.deptno=b.deptno

andupper(a.ename)=upper(name);

returndeptname

exception

whenno_data_found then

raise_application_error(-20000,'该雇员不存在');

end;

sql>var job varchar2(20)

var dname varchar2(20)

exec :dname:=get_info('scott',:job)

print danme job

5.建立函数:带有INOUT参数

create or replacefunction result(num1 number,num2 in out number)

return number

as

v_resultnumber(6);

v_remaindernumber;

begin

v_result:=num1/num2;

v_remainder:=mod(num1,num2);

num2:=v_remainder;

returnv_result;

exception

whenzero_divide then

raise_application_error(-20000,'不能除0');

end;

sql>var result1 number

var result2 number

exec :result2:=30

exec :result1:=result(100,:result2)

print result result2

6.函数调用限制

SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数

SQL只能调用带有输入参数,不能带有输出,输入输出函数

SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)

SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

7.查看函数院源代码

oracle会将函数名及其源代码信息存放到数据字典中user_source

set pagesize 40

select text fromuser_source where name='result';

8.删除函数

drop functionresult;

折叠4.3管理子程序

1.列出当前用户的子程序

数据字典视图USER_OBJECTS用于显示当前用户所包含的所有对象.(表,视图,索引,过程,函数,包)

sql>col object_name format a20

select object_name,created,status from user_objects whereobject_type in ('procedure','function')

2.列出子程序源代码

select text fromuser_source where name='raise_salsry';

3.列出子程序编译错误

使用SHOWERRORS命令确定错误原因和位置

show errorsprocedure raise_salary

使用数据字典视图USER_ERRORS确定错误原因和位置

col text formata50

selectline||'/'||position as "line/col",text error from user_errors wherename='raise_salary';

4.列出对象依赖关系

使用数据字典视图USER_DEPENDENCIES确定直接依赖关系

select name,typefrom user_dependencies where referenced_name='emp';

使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系

先运行SQL脚本UTLDTREE.SQL来建立这两个视图和过程DEPTREE_FILL,然后调用DEPTREE_FILL填充这两个视图

sql>@%oracle_home%\rdbms\admin\utldtree

exec deptree_fill('TABLE','scott','emp')

执行后会将直接或间接依赖于SCOTT.EMP表的所有对象填充到视图DEPTREE和IDEPTREE中.

select nested_level,name,type from deptree;

select * from ideptree

5.重新编译子程序

当修改了被引用对象的结构时,就会将相关依赖对象转变为无效(INVALID)状态。

alter table emp addremark varchar2(10);

selectobject_name,object_type from user_objects wherestatus='invalid';

为了避免子程序的运行错误,应该重新编译这些存储对象

alter procedureadd_employee compile;

alter view dept10compile;

alter functionget_info compile;

折叠4.4.开发包

包用于逻辑组合相关的PL/SQL类型,项和子程序,由包规范和包体组成

1.建立包规范:包规范是包与应用程序之间的接口,用于定义包的公用组件,包括常量,变量,游标,过程,函数等

create [or replace]package package_name

is|as

p lic type and item declarations

s program specificationsend package_name;

create or replacepackage emp_package is

g_deptnonumber(3):=30;

procedureadd_employee(eno number,name varchar2,salary number,dno numberdefault g_deptno);

procedurefire_employee(eno number);

functionget_sal(eno number) return number;

end emp_package;

2.建立包体:用于实现包规范所定义的过程和函数

create [or replace]package body package_name

is|as

private type and item declarations

s program bodies

endpackage_name;

create or repalce package body emp_package is

functionvalidate_deptno(v_deptno number)

return boolean

is

v_temp int;

begin

select 1 into v_temp from dept where deptno=v_deptno;

return tr;

exception

when no_date_found then

return false;

end;

procedure add_employee(eno number,name varchar2,salary number,dnonumber default g_deptno)

is

begin

if validate_deptno(dno) then

insert into emp(empno,ename,sal,deptno)vals(eno,name,salsry,dno);

else

raise_application_error(-20010,'不存在该部门');

end if;

exception

when dup_val_on_index then

raise_application_error(-20012,'该雇员已存在');

end;

procedure fire_employee(eno number) is

begin

delete from emp where empno=eno;

if sql%notfound then

raise_application_error(-20012,'该雇员不存在');

end if;

end;

functionget_sal(eno number) return number

is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=eno;

return v_sal;

exception

whenno_data_found then

raise_application_error(-20012,'该雇员不存在');

end;

end emp_package;

3.调用包组件

3.1在同一个包内调用包组件

create or replacepackage body emp_package is

procedure add_employee(eno number,name va har2,salary number,dnonumber default g_deptno)

is

begin

ifvalidate_deptno(dno) then

insert into emp(empno,ename,sal,deptno)vals(eno,name,salary,dno);

else

raise_application_error(-20010,'该部门不存在')

end if;

exception

when dup_val_on_index then

raise_application_error(-20011,'该雇员已存在')

end;

.........

3.2调用包公用变量

execemp_package.g_deptno:=20

3.3调用包公用过程

execemp_package.add_employee(1111,'mary',2000)

3.4调用包公用函数

var salarynumber

exec:salary:=emp_package.get_sal(7788)

print salary

3.5以其他用户身份调用包公用组件

connsystem/manager

execscott.emp_package.add_employee(1115,'scott',1200)

execscott.emp_package.fire_employee(1115)

3.6调用远程数据库包的公用组件

execemp_package.add_employee@orasrv(1116,'scott',1200)

4.查看源代码:存放在数据字典USER_SCOURCE中

select text fromuser_source where name='emp-package' and type='package';

5.删除包

drop packageemp_package;

6.使用包重载

重载(overload)是指多个具有相同名称的子程序

1.建立包规范

同名的过程和函数必须具有不同的输入参数,同名函数返回值的数据类型必须完全相同

create or replacepackage overload is

functionget_sal(eno number) return number;

functionget_sal(name varchar2) return number;

procedurefile_employee(eno number);

procedurefile_employee(name varchar2);

end;

2.建立包体

必须要给不同的重载过程和重载函数提供不同的实现代码

create or replacepackage body overload is

function get_sal(eno number) return number

is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=eno;

return v_sal;

exception

when no_data_found then

raise_application_error(-20020,'该雇员不存在');

end;

function get_sal(name varchar2) return number

is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where upper(ename)=upper(name);

return v_sal;

exception

when no_data_found then

raise_application_error(-20020,'该雇员不存在');

end;

procedure fire_employee(eno number) is

begin

delete from emp where empno=no;

if sql%notfound then

raise_application_error(-20020,'该雇员不存在');

end if;

end;

procedurefire_employee(name varchar2) is

begin

delete from emp where upper(ename)=upper(name);

if sql%notfound then

raise_application_error(-20020,'该雇员不存在');

end if;

end;

end;

3.调用重载过程和重载函数

var sal1 number

var sal2 number

exec:sal1:=overload.get_sal('scott')

exec:sal2:=overload.get_sal(7685)

execoverload.fire_employee(7369)

execoverload.fire_employee('scott')

7.使用包构造过程

类似于高级语言中的构造函数和构造方法

1.建立包规范

包的构造过程用于初始化包的全局变量.

create or replacepackage emp_package is

minsalnumber(6,2);

maxsalnumber(6,2);

procedureadd_employee(eno number,name varchar2,salary number,dnonumber);

procedureupd_sal(eno number,salary number);

procedureupd_sal(name varchar2,salary number);

end;

2.建立包体

包的构造过程没有任何名称,它是实现了包的其他过程后,以BEGIN开始,END结束的部分

create or replacepackage body emp_package is

procedureadd_employee(eno number,name varchar2,salary number,dno number)

is

begin

if salarybetween minsal and maxsal then

insert into emp (empno,ename,sal,deptno)vals(eno,name,salary,dno);

else

raise_application_error(-20001,'工资不在范围内');

end if;

exception

when dup_val_on_index then

raise_application_error(-20002,'该雇员已经存在');

end;

procedureupd_sal(eno number,salary number) is

begin

if salary between minsal and maxsal then

update emp set sal=salary where empno =eno;

if sql%notfound then

raise_application_error(-20003,'不存在雇员号');

end if;

else

raise_application_errpr(-20001,'工资不在范围内');

end if;

end;

procedure upd_sal(name varchar2,salary number) is

begin

if salary between minsal and maxsal then

update emp set sal=salary where upper(ename)=upper(name);

if sql%notfound then

raise_application_error(-20004,'不存在该雇员名');

end if;

else

raise_application_error(-20001,'工资不在范围内');

end if;

end;

begin

selectmi(sal),max(sal) into minsal,maxsal from emp ;

end;

调用包公用组件:构造过程只调用一次

execemp_package.add_employee(1111,'mary',3000,20)

execemp_package.upd_sal('mary',2000)

8.使用纯度级别

在SQL中引用包的公用函数，该公用函数不能包含DML语句(insert,update,delete),也不能读写远程包的变量

为了对包的公用函数加以限制,在定义包规范时,可以使用纯度级别(purity level)限制公用函数

语法:pragmarestrict_references (function_name,wnds[,wnps][,rnds][,rnps]);

wnds:用于限制函数不能修改数据库数据(禁止DML)

wnps:用于限制函数不能修改包变量(不能给包变量赋值)

rnds:用于限制函数不能读取数据库数据(禁止SELECT操作)

rnps:用于限制函数不能读取包变量(不能将包变量赋值给其他变量)

1.建立包规范

create or replacepackage purity is

minsalnumber(6,2);

maxsalnumber(6,2);

functionmax_sal return number;

functionmin_sal return number;

pragmarestrict_references(max_sal,wnps);--不能修改

pragmarestrict_references(min_sal,wnps);

end;

2.建立包体

create or replacepackage body purity is

function max_sal return number

is

begin

return maxsal;

end;

functionmin_sal return number

is

begin

return minsal;

end;

begin

select min(sal),max(sal) into minsal,maxsal from emp;

end;

3.调用包的公用函数

var minsal number

var maxsal number

exec :minsal:=purity.minsal()

exec :maxsal:=purity.maxsal()

print minsal maxsal

折叠编辑本段5Example

DECLARE

salary emp.sal%TYPE := 0;

mgr_num emp.mgr%TYPE;

last_name emp.ename%TYPE;

starting_empno emp.empno%TYPE := 7499;

BEGIN

SELECT mgr INTO mgr_num FROM emp

WHERE empno = starting_empno;

WHILE salary <= 2500 LOOP

SELECT sal,mgr,ename INTO salary,mgr_num,last_name

FROM emp WHERE empno = mgr_num;

END LOOP;

INSERT INTO temp VALUES (NULL,salary,last_name);

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO temp VALUES (NULL,NULL,'Not found');

COMMIT;

END;

/*Please View The Example Code Reference*/