背景
结构化查询语言(Structured Query Language，简称SQL)是用来访问关系型数据库一种通用语言，属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果即可。显然，这种不关注任何实现细节的语言对于开发者来说有着极大的便利。然而，有些复杂的业务流程要求相应的程序来描述，这种情况下4GL就有些无能为力了。PL/SQL的出现正是为了解决这一问题，PL/SQL是一种过程化语言，属于第三代语言，它与C、 C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。
主要特性
PL/SQL编辑器，功能强大——该编辑器具有语法加强、SQL和PL/SQL帮助、对象描述、代码助手、编译器提示、PL/SQL完善、代码内容、代码分级、浏览器按钮、超链接导航、宏库等许多智能特性，能够满足要求性最高的用户需求。当您需要某个信息时，它将自动出现，至多单击即可将信息调出。
集成调试器（要求Oracle 7.3.4或更高）——该调试器提供您所需要的全部特性：跳入（Step In）、跳过（Step Over）、跳出（Step Out）、异常时停止运行、断点、观察和设置变量、观察全部堆栈等。基本能够调试任何程序单元（包括触发器和Oracle8 对象类型），无需作出任何修改。
PL/SQL完善器——该完善器允许您通过用户定义的规则对SQL和PL/SQL代码进行规范化处理。在编译、保存、打开一个文件时，代码将自动被规范化。该特性提高了您编码的生产力，改善了PL/SQL代码的可读性，促进了大规模工作团队的协作。
SQL 窗口——该窗口允许您输入任何SQL语句，并以栅格形式对结果进行观察和编辑，支持按范例查询模式，以便在某个结果集合中查找特定记录。另外，还含有历史缓存，您可以轻松调用先前执行过的SQL语句。该SQL编辑器提供了同PL/SQL编辑器相同的强大特性。
命令窗口——使用PL/SQL Developer 的命令窗口能够开发并运行SQL脚本。该窗口具有同SQL*Plus相同的感观，另外还增加了一个内置的带语法加强特性的脚本编辑器。这样，您就可以开发自己的脚本，无需编辑脚本/保存脚本/转换为SQL*Plus/运行脚本过程，也不用离开PL/SQL Developer集成开发环境。
报告——PL/SQL Developer提供内置的报告功能，您可以根据程序数据或Oracle字典运行报告。PL/SQL Developer本身提供了大量标准报告，而且您还可以方便的创建自定义报告。自定义报告将被保存在报告文件中，进而包含在报告菜单内。这样，运行您自己经常使用的自定义报告就非常方便。
您可以使用Query Reporter免费软件工具来运行您的报告，不需要PL/SQL Developer，直接从命令行运行即可。
工程——PL/SQL Developer内置的工程概念可以用来组织您的工作。一个工程包括源文件集合、数据库对象、notes和选项。PL/SQL Developer允许您在某些特定的条目集合范围之内进行工作，而不是在完全的数据库或架构之内。这样，如果需要编译所有工程条目或者将工程从某个位置或数据库移动到其他位置时，所需工程条目的查找就变得比较简单，
To-Do条目——您可以在任何SQL或PL/SQL源文件中使用To-Do条目快速记录该文件中那些需要进行的事项。以后能够从To-Do列表中访问这些信息，访问操作可以在对象层或工程层进行。
对象浏览器——可配置的树形浏览能够显示同PL/SQL开发相关的全部信息，使用该浏览器可以获取对象描述、浏览对象定义、创建测试脚本以便调试、使能或禁止触发器或约束条件、重新编译不合法对象、查询或编辑表格、浏览数据、在对象源中进行文本查找、拖放对象名到编辑器等。
此外，该对象浏览器还可以显示对象之间的依存关系，您可以递归的扩展这些依存对象（如包参考检查、浏览参考表格、图表类型等）。
性能优化——使用PL/SQL Profiler，可以浏览每一执行的PL/SQL代码行的时序信息（Oracle8i或更高），从而优化您SQL和PL/SQL的代码性能。
更进一步，您还可以自动获取所执行的SQL语句和PL/SQL程序统计信息。该统计信息包括CPU使用情况、块I/O、记录I/O、表格扫描、分类等。
HTML指南——Oracle目前支持HTML格式的在线指南。您可以将其集成到PL/SQL Developer工作环境中，以便在编辑、编译出错或运行时出错时提供内容敏感帮助。
非PL/SQL对象——不使用任何SQL，您就可以对表格、序列、符号、库、目录、工作、队列、用户和角色进行浏览、创建和修改行为。PL/SQL Developer提供了一个简单易用的窗体，只要将信息输入其中，PL/SQL Developer就将生成相应的SQL，从而创建或转换对象。
模板列表——PL/SQL Developer的模板列表可用作一个实时的帮助组件，协助您强制实现标准化。只要点击相应的模板，您就可以向编辑器中插入标准的SQL或PL/SQL代码，或者从草稿出发来创建一个新程序。
查询构建器——图形化查询构建器简化了新选择语句的创建和已有语句的修改过程。只要拖放表格和视窗，为区域列表选择专栏，基于外部键约束定义联合表格即可。
比较用户对象——对表格定义、视图、程序单元等作出修改后，将这些修改传递给其他数据库用户或检查修改前后的区别将是非常有用的。这也许是一个其他的开发环境，如测试环境或制作环境等。而比较用户对象功能则允许您对所选对象进行比较，将不同点可视化，并运行或保存应用必要变动的SQL脚本。
导出用户对象——该工具可以导出用户所选对象的DDL（数据定义语言）语句。您可以方便的为其他用户重新创建对象，也可以保存文件作为备份。
工具——PL/SQL Developer为简化日常开发专门提供了几种工具。使用这些工具，您可以重新编译全部不合法对象、查找数据库源中文本、导入或导出表格、生成测试数据、导出文本文件、监控dbms_alert和dbms_pipe事件、浏览会话信息等。
授权——大多数开发环境中，您不希望所有数据库都具备PL/SQL Developer的全部功能性。例如，数据库开发中您可以允许PL/SQL Developer的全部功能性，而数据库测试中您可以仅允许数据查询/编辑和对象浏览功能，而数据库制作中您甚至根本不希望PL/SQL Developer访问。利用PL/SQL Developer授权功能，您可以方便的定义特定用户或规则所允许使用的功能。
插件扩展——可以通过插件对PL/SQL Developer功能进行扩展。Add-ons页面提供插件可以免费下载。Allround Automations或其他用户均可提供插件（如版本控制插件或plsqldoc插件）。如果您具备创建DLL的编程语言，您还可以自己编写插件。
多线程IDE——PL/SQL Developer是一个多线程IDE。这样，当SQL查询、PL/SQL程序、调试会话等正在运行时，您依然可以继续工作。而且，该多线程IDE还意味着出现编程错误时不会中止：您在任何时间都可以中断执行或保存您的工作。
易于安装——不同于SQL*Net，无需中间件，也无需数据库对象安装。只需点击安装程序按钮，您就可以开始安装从而使用软件了。
过程与函数
PL/SQL中的过程和函数与其他语言的过程和函数一样，都是为了执行一定的任务而组合在一起的语句。过程无返回值，函数有返回值。其语法结构为：
过程：Create or replace procedure procname(参数列表) as PL/SQL语句块
函数：Create or replace function funcname(参数列表) return 返回值 as PL/SQL语句块
为便于理解，举例如下：
问题：假设有一张表t1，有f1和f2两个字段，f1为number类型，f2为varchar2类型，要往t1里写两条记录，内容自定。
Create or replace procedure test_procedure as
V_f11 number :=1; /*声明变量并赋初值*/
V_f12 number :=2;
V_f21 varchar2(20) :='first';
V_f22 varchar2(20) :='second';
Begin
Insert into t1 values (V_f11, V_f21);
Insert into t1 values (V_f12, V_f22);
End test_procedure; /*test_procedure可以省略*/
至此，test_procedure存储过程已经完成，经过编译后就可以在其他PL/SQL块或者过程中调用了。函数与过程具有很大的相似性，此处不再详述。
游标
游标的定义为：用游标来指代一个DML SQL操作返回的结果集。即当一个对数据库的查询操作返回一组结果集时，用游标来标注这组结果集，以后通过对游标的操作来获取结果集中的数据信息。这里特别提出游标的概念，是因为它在PL/SQL的编程中非常的重要。定义游标的语法结构如下：
cursor cursor_name is SQL语句;
在本文第一段代码中有一句话如下：
cursor c_emp is select * from employee where emp_id=3;
其含义是定义一个游标c_emp，代表employee表中所有emp_id字段为3的结果集。当需要操作该结果集时，必须完成三步：打开游标、使用fetch语句将游标里的数据取出、关闭游标。
游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。
为了处理SQL语句，Oracle将在内存中分配一个区域，这就是上下文区。这个区包含了已经处理完的行数、指向被分析语句的指针，整个区是查询语句返回的数据行集。游标就是指向上下文区句柄或指针。
显式游标
显示游标被用于处理返回多行数据的SELECT 语句，游标名通过CURSOR….IS 语句显示地赋给SELECT 语句。
在PL/SQL中处理显示游标所必需的四个步骤：
1）声明游标；CURSOR cursor_name IS select_statement
2）为查询打开游标；OPEN cursor_name
3）取得结果放入PL/SQL变量中；
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4）关闭游标。CLOSE cursor_name
注意：在声明游标时，select_statement不能包含INTO子句。当使用显示游标时，INTO子句是FETCH语句的一部分。
隐式游标
所有的隐式游标都被假设为只返回一条记录。
使用隐式游标时，用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理，然后关掉游标。
例如：
…….
SELECT studentNo,studentName
INTO curStudentNo,curStudentName
FROM StudentRecord
WHERE name=’gg’;
上述游标自动打开，并把相关值赋给对应变量，然后关闭。执行完后，PL/SQL变量curStudentNo,curStudentName中已经有了值。
PL/SQL的变量
就像其他的程序语言一样，变量是在程序中出现最频繁的名词，在PL/SQL中的学习中首先需要了解变量的一些基本概念和使用方法。
PL/SQL程序包括了四个部分，在四个部分中，声明部分主要用来声明 变量并且初始化变量，在执行部分可以为变量赋新值，或者在 表达式中 引用变量的值，在 异常处理部分同样可以按执行部分的方法使用变量。另外，在PL/SQL程序使用时可以通过参数 变量把值传递到PL/SQL块中，也可以通过输出变量或者参数变量将值传出PL/SQL块。
在定义变量、 常量 标识符时需要注意下面的一些基本规则：
⒈定义的 标识符名称应该遵循命名规则，在后面将会提到主要的命名规则；
⒉在声明 常量和 变量的时候可以为其设置初始化值，也可以强制设置not null；
⒊可以使用 赋值运算符（：=）或DEFAULT 保留字来初始化 标识符，为标识符赋初始值；
⒋在声明 标识符时，每行只能声明一个标识符。
在PL/SQL中主要使用下面三种类型的 变量（或者 常量）：
⒈简单 变量；
⒉复合（组合） 变量；
⒊ 外部变量。
三种 变量分别用于存放不同特性的数据。
PL/SQL的基本语法
在写PL/SQL语句时，必须遵循一些基本的语法，下面是PL/SQL程序代码的基本语法要求：
⒈语句可以写在多行，就像SQL语句一样；
⒉各个关键字、字段名称等等，通过空格分隔；
⒊每条语句必须以分号结束，包括PL/SQL结束部分的END关键字后面也需要分号；
⒋ 标识符需要遵循相应的命名规定；
⑴名称最多可以包含30个字符；
⑵不能直接使用 保留字，如果需要，需要使用双引号括起来；
⑶第一个 字符必须以字母开始；
⑷不要用数据库的表或者科学计数法表示；
还有一些语法相关的规则：
⒈在PL/SQL程序中出现的字符值和日期值必须用单引号括起；
⒉数字值可以使用简单数字或者科学计数法表示；
⒊在程序中最好养成添加注释的习惯，使用注释可以使程序更清晰，使开发者或者其他人员能够很快的理解程序的含义和思路。在程序中添加注释可以采用：
⑴/*和*/之间的多行注释；
⑵以--开始的单行注释。
过程，函数与包
过程:执行特定操作
函数:用于返回特定数据
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;
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;
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%rdbmsadminutldtree
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
Example
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*/
