oracle-扫盲贴：存储过程实现增删改查

为公司一个项目没有接触过oracle的程序员准备的一个oracle如何使用proc实现增删改查，简单示例:

create table t1
(
sid number not null primary key,
sname varchar2(10)
)
tablespace test;


declare
a number :=1;
begin
loop
insert into t1 values(a,'snow');
a:=a+1;
exit when a=100;
end loop;
end;


----1.insert


create or replace procedure proc_insert
(
sid number,
sname varchar2
)
is
begin
  insert into scott.t1(sid,sname) values(sid,sname);
   dbms_output.put_line(' 影响的行数：   '||sql%rowcount);
  commit;
end
;


set serveroutput on
exec proc_insert(101,'snow');

----2.update

create or replace procedure proc_update
(
isid in number ,
nsname in varchar2
)
is
begin
  update scott.t1 set sname=nsname where sid=isid;
If  SQL%Found  Then
    DBMS_OUTPUT.PUT_LINE('更新成功！');
Else
    DBMS_OUTPUT.PUT_LINE('更新失败！');
End  If;
  commit;
end
;


set serveroutput on
exec proc_update(101,'ocpyang');


----3.delete


create or replace procedure proc_delete
(
isid in number
)
is
begin
  delete scott.t1  where sid=isid;
If  SQL%Found  Then
    DBMS_OUTPUT.PUT_LINE('删除成功！');
Else
    DBMS_OUTPUT.PUT_LINE('删除失败！');
End  If;
  commit;
end
;


set serveroutput on
exec proc_delete(101);

--------------4.select

--4.1变量(select ....into)：单行查询操作


create or replace procedure proc_select0
(isid in t1.sid%type )  --输入参数
as
osid t1.sid%type;  --变量
osname  t1.sname%type;   --变量
begin
select sid,sname into osid, osname from t1 where sid=isid;
dbms_output.put_line(' 编号为'||osid|| ' , 的职工姓名为  '||osname );
exception
when no_data_found then
dbms_output.put_line('没有符合条件的记录！');
when too_many_rows then
dbms_output.put_line('返回的行数太多！');
when others then
dbms_output.put_line('发生意外错误！');
end;


set serveroutput on
exec proc_select0 (101);


---4.2显示游标：返单行单列记录

create or replace procedure proc_select1
(isid in t1.sid%type )  --输入参数
as
cursor a is select sname from t1 where sid=isid;
osname t1.sname%type;
begin
open a;
fetch a into osname;
if a%found then
dbms_output.put_line( '职工姓名为:'||osname );  --游标结果集中只有一列
else
dbms_output.put_line('没有符合条件的记录！');
end if;
close a;
end;
       
set serveroutput on
exec proc_select1 (101);

--4.3显示游标：返回单行多列记录
create or replace procedure proc_select2
(isid in t1.sid%type )  --输入参数
as
cursor a is select * from t1 where sid=isid ;
osname t1%rowtype;
begin
open a;
fetch a into osname;
if a%found then
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
else
dbms_output.put_line('没有符合条件的记录！');
end if;
close a;
end;
 
     
set serveroutput on
exec proc_select2 (101);

---4.4显示游标(loop循环)：返回多行多列记录

/*

exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。
处理逻辑需要跟在exit when之后。这一点需要多加小心。
循环结束后要记得关闭游标。

*/

--方法1：基于表的记录变量接收游标数据


create or replace procedure proc_select31
--(isid in t1.sid%type )  --输入参数
as
cursor a is select * from t1 ;
osname t1%rowtype;
begin
open a;
loop
fetch a into osname;
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
end loop;
close a;
end;
 
       
set serveroutput on
exec proc_select31 ;


--方法2：基于游标的记录变量接收游标数据


create or replace procedure proc_select32
as
cursor a is select * from t1 ;
cur_record a%rowtype;
begin
open a;
loop
fetch a into cur_record;
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||cur_record.sid||';'||'的职工姓名为  '||cur_record.sname );
end loop;
close a;
end;
 
   
set serveroutput on
exec proc_select32 ;


--方法3：基于集合变量的接收游标数据


create or replace procedure proc_select33
as
cursor a is select * from t1 ;
type cur_table_type is table of a%rowtype index by binary_integer;
cur_table cur_table_type;
i int;
begin
open a;
loop
i:=a%rowcount+1;
fetch a into cur_table(i);
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||cur_table(i).sid||';'||'的职工姓名为  '||cur_table(i).sname );
end loop;
close a;
end;
     
set serveroutput on
exec proc_select33 ;

---4.5显示游标(while....loop循环)：返回多行多列记录


/*

游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while 循环时，
就需要在循环之前进行一次fetch动作。
而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。
while循环是游标里最复杂的一种.

*/

create or replace procedure proc_select4
--(isid in t1.sid%type )  --输入参数
as
cursor a is select * from t1 ;
osname t1%rowtype;
begin
open a;
fetch a into osname;
while a%found loop  --循环之前做个fetch
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
end loop;
close a;
end;
        
set serveroutput on
exec proc_select4 ;


---4.6显示游标(for循环)(适合多个记录)：返回多行多列记录


游标使用for循环不用open、fetch、close关闭游标.


--方法1：典型for循环


create or replace procedure proc_select5
as
cursor a is select * from t1 ;
begin
for  res in a loop
dbms_output.put_line( '职工的编号为:'||res.sid||';'||'的职工姓名为  '||res.sname );
end loop;
end;

set serveroutput on
exec proc_select5 ;


--方法2：简单for循环


create or replace procedure proc_select6
as
begin
for  res in ( select * from t1 ) loop
dbms_output.put_line( '职工的编号为:'||res.sid||';'||'的职工姓名为  '||res.sname );
end loop;
end;


set serveroutput on
exec proc_select6 ;


----4.7 ref游标(loop循环)


/***

怎么使用  REF游标 ？
①声明REF 游标类型，确定REF 游标类型；
  ⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
   语法：Type   REF游标名   IS   Ref Cursor Return  结果集返回记录类型；
  ⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
   语法：Type   REF游标名   IS   Ref Cursor；
②声明Ref 游标类型变量；
  语法：变量名  已声明Ref 游标类型；
 
③打开REF游标，关联结果集 ；
  语法：Open   Ref 游标类型变量   For   查询语句返回结果集；
 
④获取记录，操作记录；
  语法：Fetch    REF游标名 InTo   临时记录类型变量或属性类型变量列表；
 
⑤关闭游标，完全释放资源；
  语法：Close   REF游标名；


能够使用ref弱类型REF游标就不要使用强类型REF游标

***/

--案例1：ref弱类型游标:loop循环


create or replace procedure proc_select8
(
choice in varchar2
)
as
TYPE cur IS REF CURSOR;  --声明游标类型为ref
a cur;     --声明变量为ref游标类型
osname t1%rowtype;
begin
if  choice='full' then
open a for select * from t1;
loop
fetch a into osname;
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
end loop;
elsif choice='top' then
open a for select * from t1 where rownum<10;
loop
fetch a into osname;
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
end loop;
else
  dbms_output.put_line('请输入正确值full或top!谢谢配合!');
return;
end if;
close a;
end;
 
     
set serveroutput on
exec proc_select8('full') ;
exec proc_select8('top') ;


--案例2：ref强类型游标:loop循环


create or replace procedure proc_select9
as
TYPE cur IS REF CURSOR RETURN t1%RowType;  --声明游标类型为ref
a cur;     --声明变量为ref游标类型
osname t1%rowtype;
begin
open a for select * from t1;
loop
fetch a into osname;
exit when a%notfound;
dbms_output.put_line( '职工的编号为:'||osname.sid||';'||'的职工姓名为  '||osname.sname );
end loop;
close a;
end;


  
set serveroutput on
exec proc_select9 ;