Oracle 存储过程以及存储函数
以下的一些例子是基于scott用户下的emp表的数据,一和二使用的均为in,out参数,最后一个综合练习使用了 in out参数
一.存储过程
1.创建无参的存储过程示例 ------ hello
注意存储过程中的PLSQL部分没有了declare
1 create or replace procedure hello
2 as
3 begin
4 dbms_output.put_line('ok');
5 end;
6 /
2.如何调用?
第一种方式 ----- exec(或者是execute) hello;
第二种方式 ----- 使用PLSQL调用
begin
hello;
end;
/
3.创建有参存储过程raiseSalary(编号)，为指定员工涨10%的工资(员工编号从键盘接收)(in的用法)
注意:1.涉及到10%这种带有百分号的运算时一定要转换成相应的数字,例如:0.1
2.in 表示是何种参数,为默认值,可省略
3.&符号的使用,输入时如果是字符串不要忘记' ',输完不要多加分号
1 --创建
2 create or replace procedure raiseSalary(pempno in number)
3 as
4 begin
5 update emp set sal = sal * 1.1 where empno = pempno;
6 end;
7 /
8 --调用(exec raiseSalary(&empno);)
9 begin
10 raiseSalary(&empno);
11 end
4.创建有参存储过程findEmpNameAndSalAndJob(编号)，查询指定编号员工的的姓名，职位，月薪，返回多个值(out的用法)
注意:用 || 连接字符串,以及使用 ' '表示字符串
1 --创建
2 create or replace procedure findEmpNameAndSalAndJob(pempno emp.empno%type,pname out emp.ename%type,pjob out emp.job%type,psal out emp.sal%type)
3 as
4 begin
5 select ename,job,sal into pname,pjob,psal from emp where empno = pempno;
6 end;
7 /
8 --调用
9 declare
10 ---创建这三个变量去接收传出来的参数
11 pname emp.ename%type;
12 pjob emp.job%type;
13 psal emp.sal%type;
14 begin
15 findEmpNameAndSalAndJob(&empno,pname,pjob,psal);
16 dbms_output.put_line(pname || '----' || pjob || '-----' || psal );
17 end;
18 /
总结:从上面的几个例子可以看出如果有存储过程有参数传出的话,那么使用PLSQL调用较好,如果无传出参数的话用exec调用
二.存储函数
存储函数必须有且只有一个返回值和参数类型
1.创建并调用无参的存储过程示例
注意:1.可以看到创建存储函数时的PLSQL部分仍然没有declare
2.调用存储函数时由于函数有返回值所以声明了一个变量去接收
1 --创建
2 create or replace function getName return varchar2
3 as
4 begin
5 return 'wyc';
6 end;
7 /
8
9 --调用
10 declare
11 name varchar2(10);
12 begin
13 name := getName();
14 dbms_output.put_line(name);
15 end;
16 /
2.创建有参存储函数findEmpIncome(编号)，查询指定员工编的年收入(年薪+佣金)(in的用法,默认是in)
1 --创建
2 create or replace function findEmpIncome(pempno in number) return number
3 as
4 income emp.sal%type;
5 begin
6 -- 考虑到emp表中一些员工的佣金为null
7 select sal + NVL(comm,0) into income from emp where empno = pempno;
8 return income;
9 end;
10 /
11 --调用
12 declare
13 income emp.sal%type;
14 begin
15 income := findEmpIncome(&empno);
16 dbms_output.put_line('收入是' || income);
17 end;
18 /
3.创建有参存储函数findEmpNameAndJobAndSal(编号)，查询指定编号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值
前面说过存储函数只能有一个返回值,如果有多个返回值的话要结合out参数进行处理
1 --创建函数
2 create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out varchar2)
3 return varchar2
4 as
5 pname emp.ename%type;
6 begin
7 select ename,job,sal into pname,pjob,psal from emp where empno = pempno;
8 return pname;
9 end;
10 /
11 --调用函数
12 declare
13 pjob emp.job%type;
14 psal emp.sal%type;
15 pname emp.ename%type; --- 接收返回值
16 begin
17 pname :=findEmpNameAndJobAndSal(&empno,pjob,psal);
18 dbms_output.put_line(pname || '-----' || pjob || '-----' || psal);
19 end;
20 /
总结:存储函数与存储过程有些类似但又有不同,只有一个返回值时适合使用存储函数,有多个返回值或者没有返回值时适合使用存储过程
3.综合练习 --- 分别使用存储过程及存储函数计算个人税收(存储函数使用到了in out参数)
1.存储过程计算个人所得税
1 --创建存储过程 (注意10%一定要写成0.1)
2 create or replace procedure get_rax(psal in number,rax out number)
3 as
4 money number(6);
5 begin
6 money := psal - 3500;
7 if money <= 1500 then
8 rax := money * 0.03 - 0;
9 elsif money <= 4500 then
10 rax := money * 0.1 - 105;
11 elsif money <= 9000 then
12 rax := money * 0.2 - 555;
13 elsif money <= 35000 then
14 rax := money * 0.25 - 1005;
15 elsif money <= 55000 then
16 rax := money * 0.3 - 2755;
17 elsif money <= 80000 then
18 rax := money * 0.35 - 5505;
19 else
20 rax := money * 0.45 - 13505;
21 end if;
22 end;
23 /
24 --调用
25 declare
26 rax number(20);
27 begin
28 get_rax(&sal,rax);
29 dbms_output.put_line('您要交的税为 : ' || rax);
30 end;
31 /
2.存储函数
pname要回传给调用者,从而输出姓名
1 create or replace function get_rax2(pname in out varchar2,psal in number)
2 return number
3 as
4 money number(6);
5 rax number(20);
6 begin
7 money := psal - 3500;
8 if money <= 1500 then
9 rax := money * 0.03 - 0;
10 elsif money <= 4500 then
11 rax := money * 0.1 - 105;
12 elsif money <= 9000 then
13 rax := money * 0.2 - 555;
14 elsif money <= 35000 then
15 rax := money * 0.25 - 1005;
16 elsif money <= 55000 then
17 rax := money * 0.3 - 2755;
18 elsif money <= 80000 then
19 rax := money * 0.35 - 5505;
20 else
21 rax := money * 0.45 - 13505;
22 end if;
23 return rax;
24 end;
25 /
26
27 --调用存储函数
28 declare
29 rax number(20);
30 name varchar2(10);
31 begin
32 name := &name;
33 rax := get_rax2(name,&sal);
34 dbms_output.put_line('尊敬的' || name || ',您应交的税为 :' || rax);
35 end;
36 /
如果还有问题的话,后续再进行补充 ^_^
