Oracle 存储过程以及存储函数

以下的一些例子是基于scott用户下的emp表的数据,一和二使用的均为in,out参数,最后一个综合练习使用了 in out参数

一.存储过程

1.创建无参的存储过程示例  ------ hello

注意存储过程中的PLSQL部分没有了declare

 create or replace procedure hello
as
begin
dbms_output.put_line('ok');
end;
/

2.如何调用?

第一种方式 -----  exec(或者是execute) hello;

第二种方式 ----- 使用PLSQL调用

    begin

    hello;
  end;
  /

3.创建有参存储过程raiseSalary(编号),为指定员工涨10%的工资(员工编号从键盘接收)(in的用法)

注意:1.涉及到10%这种带有百分号的运算时一定要转换成相应的数字,例如:0.1

2.in 表示是何种参数,为默认值,可省略

   3.&符号的使用,输入时如果是字符串不要忘记' ',输完不要多加分号

 --创建
create or replace procedure raiseSalary(pempno in number)
as
begin
update emp set sal = sal * 1.1 where empno = pempno;
end;
/
--调用(exec raiseSalary(&empno);)
begin
raiseSalary(&empno);
end

4.创建有参存储过程findEmpNameAndSalAndJob(编号),查询指定编号员工的的姓名,职位,月薪,返回多个值(out的用法)

注意:用 || 连接字符串,以及使用 ' '表示字符串

 --创建
create or replace procedure findEmpNameAndSalAndJob(pempno emp.empno%type,pname out emp.ename%type,pjob out emp.job%type,psal out emp.sal%type)
as
begin
select ename,job,sal into pname,pjob,psal from emp where empno = pempno;
end;
/
--调用
declare
---创建这三个变量去接收传出来的参数
pname emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
findEmpNameAndSalAndJob(&empno,pname,pjob,psal);
dbms_output.put_line(pname || '----' || pjob || '-----' || psal );
end;
/

总结:从上面的几个例子可以看出如果有存储过程有参数传出的话,那么使用PLSQL调用较好,如果无传出参数的话用exec调用

二.存储函数

  存储函数必须有且只有一个返回值和参数类型

1.创建并调用无参的存储过程示例

注意:1.可以看到创建存储函数时的PLSQL部分仍然没有declare

   2.调用存储函数时由于函数有返回值所以声明了一个变量去接收

 --创建
create or replace function getName return varchar2
as
begin
return 'wyc';
end;
/ --调用
declare
name varchar2(10);
begin
name := getName();
dbms_output.put_line(name);
end;
/

2.创建有参存储函数findEmpIncome(编号),查询指定员工编的年收入(年薪+佣金)(in的用法,默认是in)

 --创建
create or replace function findEmpIncome(pempno in number) return number
as
income emp.sal%type;
begin
-- 考虑到emp表中一些员工的佣金为null
select sal + NVL(comm,0) into income from emp where empno = pempno;
return income;
end;
/
--调用
declare
income emp.sal%type;
begin
income := findEmpIncome(&empno);
dbms_output.put_line('收入是' || income);
end;
/

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

前面说过存储函数只能有一个返回值,如果有多个返回值的话要结合out参数进行处理

 --创建函数
create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out varchar2)
return varchar2
as
pname emp.ename%type;
begin
select ename,job,sal into pname,pjob,psal from emp where empno = pempno;
return pname;
end;
/
--调用函数
declare
pjob emp.job%type;
psal emp.sal%type;
pname emp.ename%type; --- 接收返回值
begin
pname :=findEmpNameAndJobAndSal(&empno,pjob,psal);
dbms_output.put_line(pname || '-----' || pjob || '-----' || psal);
end;
/

总结:存储函数与存储过程有些类似但又有不同,只有一个返回值时适合使用存储函数,有多个返回值或者没有返回值时适合使用存储过程

3.综合练习  --- 分别使用存储过程及存储函数计算个人税收(存储函数使用到了in out参数)

Oracle 存储过程以及存储函数

1.存储过程计算个人所得税

 --创建存储过程 (注意10%一定要写成0.1)
create or replace procedure get_rax(psal in number,rax out number)
as
money number(6);
begin
money := psal - 3500;
if money <= 1500 then
rax := money * 0.03 - 0;
elsif money <= 4500 then
rax := money * 0.1 - 105;
elsif money <= 9000 then
rax := money * 0.2 - 555;
elsif money <= 35000 then
rax := money * 0.25 - 1005;
elsif money <= 55000 then
rax := money * 0.3 - 2755;
elsif money <= 80000 then
rax := money * 0.35 - 5505;
else
rax := money * 0.45 - 13505;
end if;
end;
/
--调用
declare
rax number(20);
begin
get_rax(&sal,rax);
dbms_output.put_line('您要交的税为 : ' || rax);
end;
/

2.存储函数

pname要回传给调用者,从而输出姓名

 create or replace function get_rax2(pname in out varchar2,psal in number)
return number
as
money number(6);
rax number(20);
begin
money := psal - 3500;
if money <= 1500 then
rax := money * 0.03 - 0;
elsif money <= 4500 then
rax := money * 0.1 - 105;
elsif money <= 9000 then
rax := money * 0.2 - 555;
elsif money <= 35000 then
rax := money * 0.25 - 1005;
elsif money <= 55000 then
rax := money * 0.3 - 2755;
elsif money <= 80000 then
rax := money * 0.35 - 5505;
else
rax := money * 0.45 - 13505;
end if;
return rax;
end;
/ --调用存储函数
declare
rax number(20);
name varchar2(10);
begin
name := &name;
rax := get_rax2(name,&sal);
dbms_output.put_line('尊敬的' || name || ',您应交的税为 :' || rax);
end;
/

如果还有问题的话,后续再进行补充 ^_^

上一篇:Echart多图联动


下一篇:Asp.Net构架(Http请求处理流程)、Asp.Net 构架(Http Handler 介绍)、Asp.Net 构架(HttpModule 介绍)