以下的一些例子是基于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参数)
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;
/
如果还有问题的话,后续再进行补充 ^_^