以下的文章是通過介紹Oracle存儲過程的相關實際應用代碼,來剖析Oracle存儲過程的實際應用,以下就是相關內容的詳細介紹。以下是文章的具體介紹,望你浏覽完以下的內容會有所收獲。
Oracle存儲過程的用法
- create table stuInfo
- (
- stuID int primary key,
- stuName varchar2(20)
- )
- create or replace procedure proc1
- is
- begininsert 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);
在Oracle存儲過程中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;