先建一个包.
--包头
create or replace package emp_pkg is
-- Public type declarations
type emp_record_type is record(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
type emp_ref_cursor is ref cursor return emp_record_type;
type emp_table_type is table of emp_record_type index by binary_integer;
-- Public function and procedure declarations
procedure emp_query(dmlset in out emp_ref_cursor,p_ename in emp.ename%type) ;
procedure emp_lock(dmlset in out emp_table_type);
procedure emp_insert(dmlset in out emp_table_type);
procedure emp_update(dmlset in out emp_table_type);
procedure emp_delete(dmlset in out emp_table_type);
end emp_pkg;
--包体
create or replace package body emp_pkg is
-- Function and procedure implementations
-- 参数 p_ename 用于在表单查询时作为参数.否则表单在查询时输入ename值不起作用.
procedure emp_query(dmlset in out emp_ref_cursor,p_ename in emp.ename%type) is
begin
open dmlset for
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from emp
where ename like p_ename || '%';
end;
procedure emp_lock(dmlset in out emp_table_type) is
empno_holder number;
begin
FOR emp_ct IN 1..dmlset.count
LOOP
select empno into empno_holder
from emp
where empno = dmlset(emp_ct).empno for update;
END LOOP;
end;
procedure emp_insert(dmlset in out emp_table_type) is
v_message varchar(100);
begin
FOR emp_ct IN 1..dmlset.count
LOOP
v_message := 'Insert of EMP';
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (dmlset(emp_ct).empno,dmlset(emp_ct).ename,dmlset(emp_ct).job,dmlset(emp_ct).mgr,dmlset(emp_ct).hiredate,dmlset(emp_ct).sal,dmlset(emp_ct).comm,dmlset(emp_ct).deptno);
END LOOP;
exception
when others then
raise_application_error(-20002,'Error: Insertion record' ||
'using EMP_PKG.EMP_INSET. Inform. Technical Support. ' ||
v_message);
end;
procedure emp_update(dmlset in out emp_table_type) is
v_message varchar(100);
begin
FOR emp_ct IN 1..dmlset.count
LOOP
v_message := 'Update of EMP';
update emp set
ename = upper(dmlset(emp_ct).ename),
job = upper(dmlset(emp_ct).job),
mgr = dmlset(emp_ct).mgr,
hiredate = dmlset(emp_ct).hiredate,
sal =dmlset(emp_ct).sal,
comm = dmlset(emp_ct).comm,
deptno = dmlset(emp_ct).deptno
where empno = dmlset(emp_ct).empno;
END LOOP;
exception
when others then
raise_application_error(-20002,'Error: Updating record' ||
'using EMP_PKG.EMP_UPDATE. Inform. Technical Support. ' ||
v_message);
end;
procedure emp_delete(dmlset in out emp_table_type) is
v_message varchar(100);
begin
FOR emp_ct IN 1..dmlset.count
LOOP
v_message := 'Delete of EMP';
delete from emp where empno = dmlset(emp_ct).empno;
END LOOP;
exception
when others then
raise_application_error(-20002,'Error: deleting record' ||
'using EMP_PKG.EMP_DELETE. Inform. Technical Support. ' ||
v_message);
end;
end emp_pkg;
建立数据块
1.JPG来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/143412/viewspace-591930/ ，如需转载，请注明出处，否则将追究法律责任。
