存储过程
优点:预编译 ,提高执行速度;减少了网络流量;减少开发的代码;安全性高;
缺点:移植性差,占用服务端资源;代码可读性差,维护难
语法:
Create or replace procedure pro_name(
Var1 in | out | in out number ,
Var2 in varchar2(20),
Var3 out varchar2(20)
) is
Begin
Sql 语句块;
End pro_name;
如:create or replace procedure pro_xiaoming(
v_name out emp_5.ename%type,
v_empno in emp_5.empno%type
) is
begin
select ename into v_name from emp_5 where empno=v_name;
exception
when no_data_found then
dbms_output.put_line('没有找到相应的数据');
when others then
dbms_output.put_line('查询出错11');
end pro_xiaoming;
游 标:
处理多行结果集的查询 可以使用游标;
步骤:声明 --- 打开 --- 取行 --- 关闭
游标的属性:%isopen :测试游标是否打开;
%found :测试前一个fetch 语句是否有值;
%rowcount : 返回游标的行数;
在pl/sql 块中声明:
declare
temp emp_5.sal%type;
cursor cur_xm is select * from emp_5 where sal>temp;
cur_db cur_xm%rowtype;
begin
temp:=&temp;
open cur_xm;
if cur_xm%isopen then
fetch cur_xm into cur_db;
while cur_xm%found loop
dbms_output.put_line(to_char(cur_db.empno||' '||cur_db.ename||' '||cur_db.sal));
fetch cur_xm into cur_db;
end loop;
else
dbms_output.put_line('游标没有打开');
end if;
close cur_xm;
end;
declare
temp emp_5.sal%type;
cursor cur_xm is select * from emp_5 where sal>temp;
--cur_db cur_xm%rowtype;
begin
temp:=&temp;
for cur_temp in cur_xm loop
dbms_output.put_line(to_char(cur_temp.empno||' '||cur_temp.ename||' '||cur_temp.sal));
end loop;
end;
declare
cursor cur_xm is select * from emp_5 for update;
cur_db cur_xm%rowtype;
begin
open cur_xm;
loop
fetch cur_xm into cur_db;
exit when cur_xm%notfound;
if cur_db.empno=7788 then
delete from emp_5 where current of cur_xm;
end if;
end loop;
close cur_xm;
end;
用存储过程返回一个游标:
首先声明一个包:
Create package pack_name
As type tempcursor is ref cursor
End pack_name;
创建 存储过程:
Create procedure pro_name(
cursorrecord out pack_name.tempcursor
) is
Begin
Open cursorrecord for select * from emp where deptno = 10;
End pro_name