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 ;