熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle存儲過程的用法

2022-06-13   來源: Oracle 

  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_IDv_Name);

  commit;記得要提交

  end;

  create or replace procedure proc

  (

  v_ID int

  v_Name out varchar

  )

  is

  varName stuInfoStuname%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 intstuInfoCursor out stuInfoCursorType);

  end;

  create or replace package body PKG_STUINFO is

  procedure getStuInfo (v_ID intstuInfoCursor 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 varcharstuInfoCursor out stuInfoCursorType);

  end;

  create or replace package body PKG_STUINFO is

  procedure getStuInfo (v_Name varcharstuInfoCursor 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;


From:http://tw.wingwit.com/Article/program/Oracle/201311/18557.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.