oracle 存储过程用法,oracle存储过程的用法

oracle存储过程的用法以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！

create table stuInfo

(

stuID int primary key

stuName varchar ( )

)

create or replace procedure proc

is

begin

insert into stuInfo values( liheng );

end;

create or replace procedure proc

(

v_ID int

v_Name varchar

)

is

begin

insert into stuInfo values(v_ID v_Name);

mit; 记得要提交

end;

create or replace procedure proc

(

v_ID int

v_Name out varchar

)

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= 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 varchar stuInfoCursor out stuInfoCursorType);

end;

create or replace package body PKG_STUINFO is

procedure getStuInfo (v_Name varchar 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; lishixinzhi/Article/program/Oracle/201311/18557

分页：123