我这是模仿一本书上来写的,编译是通过了,可不是知对不对。刚学,作个笔记吧。
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;
/
