Oracle包操作:增删改除查

我这是模仿一本书上来写的,编译是通过了,可不是知对不对。刚学,作个笔记吧。


create or replace package UBS_USER_PKG is


  type user_record_type is record
  (
  userID number(4),
  username varchar2(20),
  createdate date,
  deptID number(4),
  gender number(1),
  job varchar2(30),
  remarks varchar2(200),
  birthday date,
  jobexperience varchar2(200),
  education varchar2(200),
  relationship varchar2(200)
  );
  
  type user_ref_cursor is ref cursor return user_record_type;
  type user_table_type is table of user_record_type index by binary_integer;
  
  
  procedure user_query(dmlset in out user_ref_cursor);
  procedure user_lock(dmlset in out user_table_type);
  procedure user_insert(dmlset in out user_table_type);
  procedure user_update(dmlset in out user_table_type);
  procedure user_delete(dmlset in out user_table_type);
  

end UBS_USER_PKG;
/
create or replace package body UBS_USER_PKG is

procedure user_query(dmlset in out user_ref_cursor) is
begin

open dmlset for

select

userid,
username,
createdate,
deptid,
gender,
job,
remarks,
birthday,
jobexperience,
education,
relationship


from ubs_user;

end;




procedure user_lock(dmlset in out user_table_type) is
userID_holder number;
begin

select userID into userID_holder from ubs_user where userID=dmlset(1).userID for update;
  
  
end;
  
  
procedure user_insert(dmlset in out user_table_type) is
begin
  
insert into ubs_user
(
userid,
username,
createdate,
deptid,
gender,
job,
remarks,
birthday,
jobexperience,
education,
relationship
)
values
(
dmlset(1).userid,
dmlset(1).username,
dmlset(1).createdate,
dmlset(1).gender,
dmlset(1).job,
dmlset(1).remarks,
dmlset(1).birthday,
dmlset(1).jobexperience,
dmlset(1).education,
dmlset(1).education,
dmlset(1).relationship
);

end;
  
  
procedure user_update(dmlset in out user_table_type) is

begin

update ubs_user set

username=dmlset(1).username,
createdate=dmlset(1).createdate,
deptid=dmlset(1).deptid,
gender=dmlset(1).gender,
job=dmlset(1).job,
remarks=dmlset(1).remarks,
birthday=dmlset(1).birthday,
jobexperience=dmlset(1).jobexperience,
education=dmlset(1).education,
relationship=dmlset(1).relationship

where
userID=dmlset(1).userID;
end;



procedure user_delete(dmlset in out user_table_type) is
begin
delete from ubs_user where userid=dmlset(1).userID;

end;


  

end UBS_USER_PKG;
/

上一篇: Web开发电子期刊2009年第6期(总第34期)
下一篇: Oracle学习:包
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: