一 存储过程
存储过程是Oracle中最常用的程序块。它可以被赋予参数,存储在数据库中,可以被用户反复调用。由于存储过程保存在数据库时已经被编译过,因此在执行时无需再编译,运行的效率比普通PL/SQL块更高。同时,可以将一部分业务通过存储过程的方式放在数据库中,实现程序的模块化设计。
--注意:1.参数类型后面不要加精度 比如theName varchar2(20)是错误的
2. 在命名的存储过程中不要用&符号输入
--第一个存储过程
create or replace procedureproc_find_emp(empno in number)
is
theRow employee%rowtype;
begin
select * into theRow from employee where empid=empno;
dbms_output.put('职工编号:'||empno);
dbms_output.put(' 职工名称:'||theRow.Empname);
dbms_output.put_line(' 职工邮箱:'||theRow.Email);
exception
when no_data_found then
dbms_output.put_line('没有找到编号为'||empno||'的职工信息');
end proc_find_emp;
--执行
--1.直接在命令行执行
exec proc_find_emp(1005);
--利用匿名块
begin
proc_find_emp(1005);
end;
--给员工涨工资
create or replace procedureproc_add_sal(theId in int, amount in number)
is
old_ename varchar2(20);
old_sal number;
begin
select empname,sal into old_ename,old_sal from employee
where empid=theId;
update employee set sal=sal+amount whereempid=theid;
commit;
dbms_output.put_line('员工'||old_ename||'的工资达到'||(old_sal+amount));
exception
when others then
dbms_output.put_line('发生了异常,请检查代码');
rollback;
end proc_add_sal;
--可以不按顺序来传递参数
exec proc_add_sal(amount=>-500,theId=> 1005);
--out参数的存储过程
create or replace procedure
proc_out_test(amount out number) is
begin
select sum(sal) into amount from employee;
end proc_out_test;
--out 参数执行必须采用匿名块
declare
out_amount number;
begin
proc_out_test(out_amount);
dbms_output.put_line('每月需要支出的工资总额为:'||out_amount);
end;
-- in out类型的参数,既要传进去使用,又要传出来
create or replace procedure
proc_inout_test(phone in out varchar2) is
begin
phone:='0731-'||phone;
end proc_inout_test;
--执行
declare
thephone varchar2(20);
begin
thephone:='&tt';
proc_inout_test(thephone);
dbms_output.put_line('新的号码为:'||thephone);
end;
--子过程(存储过程里面嵌套存储过程)
--查询比【最晚入职员工的最低工资】还要低的老员工信息
create or replace procedure proc_deal_sal
as
minSal float;
theDate date;
--子过程start---
procedure proc_old_sal(theSal out float,maxDate out date)
as
begin
select max(hiredate) into maxDate from emp;
select min(sal) into theSal from emp where hiredate=maxDate;
end proc_old_sal;
--子过程end--
begin
proc_old_sal(minSal,theDate);
for theRow in (select * from emp where sal<minSal and hiredate<theDate)
loop
dbms_output.put('员工名称'||theRow.eName);
dbms_output.put(' 员工岗位'||theRow.job);
dbms_output.put_line(' 员工工资'||theRow.sal);
end loop;
end proc_deal_sal;
二 自定义函数
存储函数和存储过程非常的相似,也是一种存储在数据库中的命名的程序块,也可以接收输入参数,进行逻辑处理以后将处理结果返回给调用者。函数和过程之间的区别主要在于,函数必须有返回值,并且可以作为一个表达式的一部分,但不能象存储过程一样作为一个单独的语句使用。
create or replace functionfunc_getEmpById(theId number)
return number
is
Result number;
begin
select sal into Result from employee where empId=theId;
return (Result);
exception
when no_data_found then
dbms_output.put_line('没有找到相关数据');
return (NULL);
WHEN others then
dbms_output.put_line('发生了其他异常');
return (NULL);
end func_getEmpById;
--函数的调用
exec dbms_output.put_line('调用结果'||func_getEmpById(1005));
--补充 decode函数
select empname,
decode(deptidd,10,'人事部',11,'财务部',12,'研发部',13,'销售部',14,'后勤部')
from employee;
三 程序包
Oracle中的程序包是一组相关的过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合,它存储于数据字典中。在PL/SQL程序设计中,使用程序包来组织过程、函数等的结构,可使程序设计实现模块化,从而提高程序的编写效率,也可以提高程序的执行效率。因为在程序中第一次调用包中的函数或者存储过程是,程序会将整个包引入内存,那么再次调用包中的成员时,将直接从内存中引用,从而加快程序的运行效率。
一个程序包由两个部分组成:包头(PACKAGE)和包体(PACKAGE BODY。其中包头用于声明包内的数据类型、变量、常量、游标、过程、函数等元素。而包体是对规范部分声明的过程、函数的具体实现。
--包头
create or replace package pkg_myemp is
--先申明一个函数
function func_getEmpById(theId number) return number;
--获得工资最低的员工的信息
procedure proc_getMinSalEmp;
--获得工资最高的员工信息
procedure proc_getMaxSalEmp;
end pkg_myemp;
--包体
create or replace package body pkg_myemp
is
--定义函数,根据编号获得工资
function func_getEmpById(theId number)
return number
is
Result number;
begin
select sal into Result from employee where empId=theId;
return(Result);
exception
when no_data_found then
dbms_output.put_line('没有找到相关数据');
return (NULL);
WHEN others then
dbms_output.put_line('发生了其他异常');
return (NULL);
endfunc_getEmpById;
--定义过程,获得工资最低的员工的信息
procedure proc_getMinSalEmp
is
theRow employee%rowtype;
theSal number;
begin
select min(sal) into theSal from employee;
select * into theRow from employee where sal=theSal;
dbms_output.put(' 员工编号'||theRow.Empid);
dbms_output.put(' 员工姓名'||theRow.Empname);
dbms_output.put_line(' 员工工资'||theRow.Sal);
exception
when too_many_rows then
dbms_output.put_line('有多人工资并列最低');
endproc_getMinSalEmp;
--定义过程,获得工资最高的员工的信息
procedure proc_getMaxSalEmp
is
theRow employee%rowtype;
theSal number;
begin
select max(sal) into theSal from employee;
select * into theRow from employee where sal=theSal;
dbms_output.put(' 员工编号'||theRow.Empid);
dbms_output.put(' 员工姓名'||theRow.Empname);
dbms_output.put_line(' 员工工资'||theRow.Sal);
exception
when too_many_rows then
dbms_output.put_line('有多人工资并列最高');
endproc_getMaxSalEmp;
end pkg_myemp;
执行函数:dbms_output.put_line(‘9527的工资:’|| pkg_myemp.func_getEmpById(9527));
执行过程:exec pkg_myemp.proc_getMinSalEmp
Exec pkg_myemp. proc_getMaxSalEmp
--子程序与数据库对象的依赖性
Select object_name,status from user_objects ;