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