oracle存储过程的用法

oracle存储过程的用法 精典

create table stuInfo
(
stuID int primary key,
stuName varchar2(20)
)


create or replace procedure proc1
is
begin
  insert into stuInfo values(1,'liheng');
end;



create or replace procedure proc2
(
  v_ID int,
  v_Name varchar2
)
is
begin
  insert into stuInfo values(v_ID,v_Name);
  commit;---------记得要提交
end;


create or replace procedure proc3
(
v_ID int,
v_Name out varchar2
)
is
varName stuInfo.Stuname%type;
begin
 select stuName into varName from stuInfo where stuID=v_ID;
 v_Name:=varName;
end;


=====================返回全部记录=======================================

create or replace package PKG_STUINFO is
  type stuInfoCursorType is ref cursor;
  procedure getStuInfo (stuInfoCursor out stuInfoCursorType);
end;



create or replace package body PKG_STUINFO is
  procedure getStuInfo (stuInfoCursor out stuInfoCursorType)
  is
  var_cursor stuInfoCursorType;
  begin
     open var_cursor for select * from stuInfo;
     stuInfoCursor:=var_cursor;
  end;
end;

=====================根据编号返回记录==============================

create or replace package PKG_STUINFO is
  type stuInfoCursorType is ref cursor;
  procedure getStuInfo (v_ID int,stuInfoCursor out stuInfoCursorType);
end;


create or replace package body PKG_STUINFO is
  procedure getStuInfo (v_ID int,stuInfoCursor out stuInfoCursorType)
  is
  var_cursor stuInfoCursorType;
  begin
     if v_ID=0 then
        open var_cursor for select * from stuInfo;
     else
        open var_cursor for select * from stuInfo where stuID=v_ID;
     end if;
     stuInfoCursor:=var_cursor;
  end;
end;

=========================根据姓名返回记录===========================

create or replace package PKG_STUINFO is
  type stuInfoCursorType is ref cursor;
  procedure getStuInfo (v_Name varchar2,stuInfoCursor out stuInfoCursorType);
end;


create or replace package body PKG_STUINFO is
  procedure getStuInfo (v_Name varchar2,stuInfoCursor out stuInfoCursorType)
  is
  var_cursor stuInfoCursorType;
  begin
    if v_Name =' ' then
      open var_cursor for select * from stuInfo;
    else
      open var_cursor for select * from stuInfo where stuName like '%'||v_Name||'%';
    end if;
    stuInfoCursor:=var_cursor;
  end;
end;