- 为公司一个项目没有接触过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 ;
转载于:https://blog.51cto.com/ocpyang/1194469