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;