分类： Oracle
2011-07-29 21:11:25
存储过程与函数特点
1.存放在服务器端，代码共享，增加了代码开发效率
2.存储过程能隐藏表的基本信息，可以简化语句
3.减少网络流量
存储过程创建语法
create or repalce procedure procedurename[(参数列表)]
as
//声明变量或自定以数据类型
begin
//语句代码
exception
end;
create or replace procedure emp_pro1
as
vename emp.ename%type;
vsal emp.sal%type;
begin
select ename,sal into vename,vsal
from emp
where empno=7521;
dbms_output.put_line(vename||vsal);
end;
调用存储过程代码
begin
emp_pro1;
end;
有参数存储过程。参数的数据类型不能指定大小
create or replace procedure emp_pro2(vdeptno emp.deptno%type)
as
cursor emp_cur is select * from emp where deptno=vdeptno;
begin
for vrow in emp_cur loop
dbms_output.put_line(vrow.ename);
end loop;
end;
begin
emp_pro2(20);
end;
存储过程参数的模式
in 输入 默认为模式
out 输出
inout 输入输出
create or replace procedure emp_pro3(vdeptno in emp.deptno%type)
as
vename emp.ename%type;
begin
-- vdeptno:=7521;//错误 in模式参数是只读参数
select ename into vename from emp where empno=vdeptno;
dbms_output.put_line(vename);
end;
create or replace procedure emp_pro4(vsal out emp.sal%type)
as
begin
select sal into vsal from emp where empno=7521;
end;
declare
vsal number(4);
begin
emp_pro4(vsal);
dbms_output.put_line(vsal);
end;
create or replace procedure emp_pro5(vsal in out emp.sal%type)
as
begin
select sal into vsal from emp where empno=vsal;
end;
declare
vsal emp.sal%type;
begin
vsal:=7521;
emp_pro5(vsal);
dbms_output.put_line(vsal);
end;
函数
create or repalce function funname[(参数列表)]
return 数据类型
as
begin
return ;
end;
create or replace function emp_fun1
return varchar2
as
vename varchar2(10);
begin
select ename into vename from emp where empno=7521;
return vename;
end;
select emp_fun1 from dual
declare
ret varchar2(10);
begin
ret:=emp_fun1;
dbms_output.put_line(ret);
end;
create or replace function emp_fun2(vempno number)
return varchar2
as
vename varchar2(10);
begin
select ename into vename from emp where empno=vempno;
return vename;
end;
select emp_fun2(7521) from dual
函数也支持三种参数模式in out
create or replace function emp_fun3(vempno in out emp.empno%type)
as
vdeptno emp.deptno%type;
begin
select sal,deptno into vempno,vdeptno from emp where empno=vempno;
return vdeptno;
end;
declare
vempno emp.empno%type;
ret number(4);
begin
vempno:=7521;
ret:=emp_fun3(vempno);
dbms_output.put_line(ret);
dbms_output.put_line(vempno);
end;
存储过程与函数区别
1.函数一定要有return 返回，存储过程不需要
删除存储过程与函数
drop procedure procedurename
drop function funcname
