Oracle系列：（29）存储过程和存储函数

1、存储过程【procedure】

什么是存储过程？

事先运用oracle语法写好的一段具有业务功能的程序片段，长期保存在oracle服务器中，供oracle客户端（例如，sqlplus）和程序语言远程访问，类似于Java中的函数。

为什么要用存储过程？

（1）PLSQL每次执行都要整体运行一遍，才有结果

（2）PLSQL不能将其封装起来，长期保存在oracle服务器中

（3）PLSQL不能被其它应用程序调用，例如：Java

存储过程与PLSQL是什么关系？

存储过程是PLSQL的一个方面的应用，而PLSQL是存储过程的基础。

即存储过程需要用到PLSQL。

--------------------------------------------------------存储过程

语法：

create[orreplace]procedure过程名[(参数列表)]
as
PLSQL程序体；

注意：存储过程中有【begin…end;/】，无declare

创建无参存储过程hello，无返回值，语法：create or replace procedure 过程名 as PLSQL程序

createorreplaceprocedurehello
as
begin
dbms_output.put_line('这是我的第一个存储过程');
end;
/

删除存储过程hello，语法：drop procedure 过程名

dropprocedurehello;

调用存储过程方式一，exec 存储过程名

exechello;

调用存储过程方式二，PLSQL程序

begin
hello;
end;
/

调用存储过程方式三，Java程序

JDBC中讲过一个对象：CallableStatement

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

--定义过程
createorreplaceprocedureraiseSalary(pempnonumber)
as
begin
updateempsetsal=sal*1.2whereempno=pempno;
end;
/

--调用过程
execraiseSalary(7369);

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

--定义过程
createorreplaceprocedurefindEmpNameAndSalAndJob(pempnoinnumber,penameoutvarchar2,pjoboutvarchar2,psaloutnumber)
as
begin
selectename,job,salintopename,pjob,psalfromempwhereempno=pempno;
end;
/
--调用过程
declare
penameemp.ename%type;
pjobemp.job%type;
psalemp.sal%type;
begin
findEmpNameAndSalAndJob(7369,pename,psal);
dbms_output.put_line('7369号员工的姓名是'||pename||'，职位是'||pjob||'，月薪是'||psal);
end;
/

什么情况下用exec调用，什么情况下用PLSQL调用存储过程？

exec适合于调用存储过程无返回值

plsql适合于调用存储过程有返回值，不管多少个

用存储过程，写一个计算个人所得税的功能

--定义存储过程
createorreplaceprocedureget_rax(salinnumber,raxoutnumber)
as
--sal表示收入
--bal表示需要交税的收收入
balnumber;
begin
bal:=sal-3500;
ifbal<=1500then
rax:=bal*0.03-0;
elsifbal<=4500then
rax:=bal*0.1-105;
elsifbal<=9000then
rax:=bal*0.2-555;
elsifbal<=35000then
rax:=bal*0.25-1005;
elsifbal<=55000then
rax:=bal*0.3-2755;
elsifbal<=80000then
rax:=bal*0.35-5505;
else
rax:=bal*0.45-13505;
endif;
end;
/


--调用存储过程
declare
--需要交的税
raxnumber;
begin
get_rax(&sal,rax);
dbms_output.put_line('你需要交税'||rax);
end;
/

2、存储函数

创建无参存储函数getName，有返回值，语法：create or replace function 函数名 return 返回类型 as PLSQL程序段

createorreplacefunctionget_namereturnvarchar2
as
begin
return'hello你好';
end;
/

删除存储函数getName，语法：drop function 函数名

dropfunctionget_name;

调用存储函数方式一，PLSQL程序

declare
namevarchar2(20);
begin
name:=get_name();
dbms_output.put_line(name);
end;
/

调用存储函数方式二，Java程序

创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

--定义存储函数
createorreplacefunctionfindEmpIncome(pempnoinnumber)returnnumber
as
incomenumber;
begin
selectsal*12+NVL(comm,0)intoincomefromempwhereempno=pempno;
returnincome;
end;
/
--调用存储函数
declare
incomenumber;
begin
income:=findEmpIncome(&income);
dbms_output.put_line('该员工的年收入为'||income);
end;
/

创建有参存储函数findEmpNameAndJobAndSal(编号)，查询7788号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值

--定义存储函数
createorreplacefunctionfindEmpNameAndJobAndSal(pempnoinnumber,psaloutnumber)returnvarchar2
as
penameemp.ename%type;
begin
selectename,psalfromempwhereempno=pempno;
returnpename;
end;
/

--调用存储函数
declare
penameemp.ename%type;
pjobemp.job%type;
psalemp.sal%type;
begin
pename:=findEmpNameAndJobAndSal(&empno,psal);
dbms_output.put_line('7369号员工的姓名是'||pename||'，职位是'||pjob||'，月薪是'||psal);
end;
/

3、存储过程和存储函数的适合场景

注意：适合不是强行要使用，只是优先考虑

什么情况下【适合使用】存储过程？什么情况下【适合使用】存储函数？

【适合使用】存储过程：无返回值 或 有多个返回值时，适合用过程

【适合使用】存储函数：有且只有一个返回值时，适合用函数

什么情况【适合使用】过程函数，什么情况【适合使用】SQL？

【适合使用】过程函数：

》需要长期保存在数据库中

》需要被多个用户重复调用

》业务逻辑相同，只是参数不一样

》批操作大量数据，例如：批量插入很多数据

【适合使用】SQL：

》凡是上述反面，都可使用SQL

》对表，视图，序列，索引，等这些还是要用SQL

批量添加操作示例：

--定义过程
createorreplaceprocedurebatchInsert
as
inumber(4):=1;
begin
foriin1..999
loop
insertintoemp(empno,ename)values(i,'员工'||i);
endloop;
end;
/

--调用过程
execbatchInsert;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!