作用:
1.复用;2.一次编译,多次运行,提高效率;3.打包多条sql.
语法:
or replace可以不写,表示:如果存储过程不存在,就创建;如果存储过程已经存在了,就替换
procedure 存储过程的关键字
存储过程的名字是自定义的;
过程的名字后面,如果有传入的或者返回的参数,那么必须要有小括号;如果没有传入和返回的参数,就不写小括号;
小括号中,可以写多个参数;
每个参数都应该用in或者out来修饰:
in : 表示这个参数是一个传入的参数
out : 表示这个参数是用来返回的
参数的数据类型,不能写小括号和长度;
多个参数之间,使用都好隔开
create or replace procedure 存储过程的名字(
参数1 in/out 数据类型(不允许写长度),
参数2 in/out 数据类型(不允许写长度),
参数3 in/out 数据类型(不允许写长度)
)is
变量1 数据类型(可以有长度);
变量2 数据类型(可以有长度);
变量3 数据类型(可以有长度);
begin
语句块;
exception
处理异常;
end;
例子1:给定员工编号,查询员工工资
形参:形式参数
create or replace procedure findSalByEmpno(
v_no in number,
v_sal out number
)is
begin
select sal into v_sal from emp where empno=v_no;
end;
创建成功之后,如果要执行,需要再调用:
set serveroutput on;
调用测试:
实参:实际参数,里面是存放有实际的值的
declare
eno number(8):=7369;
esal number(8);
begin
findSalByEmpno(eno,esal);
dbms_output.put_line('esal:'||esal);
end;
存储过程第二种调用方式:
这里定义了一个会话级的变量;
var esal number;
先打印会话级变量的值
print esal;--现在值为空
调用存储过程,传入7369,并把结果返回到esal变量中
因为这里用到了会话级变量esal,会话级变量在使用时需要再变量前,加冒号
exec findSalByEmpno(7369,:esal);
再次打印esal,此时esal已经有值了..
print esal;
select * from emp;
第三种方式调用:
var v_sal number;
call findSalByEmpno(7499,:v_sal);
例子2:给定一个名字,计算该员工应缴纳的税金
create or replace procedure computeFax(
v_name in emp.ename%type,
v_fax out emp.sal%type
)is
v_sal emp.sal%type;
mid_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=v_name;
mid_sal:=v_sal - 3500;
if mid_sal<=0 then v_fax:=0;
elsif mid_sal<=1500 then v_fax:=mid_sal*0.03-0;
elsif mid_sal<=4500 then v_fax:=mid_sal*0.1-105;
else v_fax:=mid_sal*0.45-13505;
end if;
end;
测试
declare
v_fax emp.sal%type;
begin
computeFax('KING',v_fax);
dbms_output.put_line(v_fax);
end;
例子3:给定一个员工编号,查询员工所有信息
create or replace procedure findEmpByEmpno(
v_no in emp.empno%type,
v_emp out emp%rowtype
)is
begin
select * into v_emp from emp where empno=v_no;
end;
测试
declare
v_emp emp%rowtype;
begin
findEmpByEmpno(7369,v_emp);
dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||v_emp.sal);
end;
例子4:返回多行数据
cursor不能用于返回数据到存储过程以外的地方
sys_refcursor : 系统游标,用于从存储过程或函数中返回多行数据
create or replace procedure findallemp(
allemp out sys_refcursor
)is
--cursor empinfo is select * from emp where empno=7369;
begin
open allemp for select * from emp;
end;
调用
declare
allemp sys_refcursor;
empinfo emp%rowtype;
begin
findallemp(allemp);
loop
fetch allemp into empinfo;
if allemp%notfound then exit;end if;
dbms_output.put_line(empinfo.empno||','||empinfo.ename||','||empinfo.sal);
end loop;
if allemp%isopen then close allemp;end if;
end;