PL/SQL的执行操作
过程:执行特定操作
函数:用于返回特定数据 语法: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(systimestamp);
end;
2.调用过程
set serveroutput on
exec out_time
set serveroutput on
call out_time();
3.建立过程:带有IN参数
CREATE OR REPLACE PROCEDURE add_employee(eno NUMBER, NAME VARCHAR2, sal NUMBER, job VARCHAR2 DEFAULT 'clerk', dno NUMBER) IS e_integrity EXCEPTION; PRAGMA EXCEPTION_INIT(e_integrity, -2291); BEGIN INSERT intoimp (empno, ename, sal, job, deptno) VALUES (eno, NAME, sal, job, dno); EXCEPTION WHEN dup_val_on_index THEN raise_application_error(-20000, '雇员号不能重复'); WHEN e_integrity THEN raise_application_error(-20001, '部门不存在'); END add_employee;
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; 用于返回特定函数
语法: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; 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; 包用于逻辑组合相关的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 PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。
PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。当发生错误时，程序无条件转到异常处理部分，这就要求代码要非常干净并把错误处理部分和程序的其它部分分开。oracle允许声明其他异常条件类型以扩展错误/异常处理。这种扩展使PL/SQL的异常处理非常灵活。
当一个运行时错误发生时，称为一个异常被抛出。PL/SQL程序编译时的错误不是能被处理得异常，只有在运行时的异常能被处理。在PL/SQL程序设计中异常的抛出和处理是非常重要的内容。
