随笔 - 109  文章 - 0  评论 - 72  阅读 - 17万 

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

http://www.dezai.cn/blog/article.asp?id=256
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;
/

posted on   德仔  阅读(336)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示