form中用存储过程作为数据源

转自：http://blog.itpub.net/24627116/viewspace-754498/

创建后台程序包，构造数据源并对FORMS数据相关操作进行定义，包括：Execute/Insert/Update/Delete/Update和查询返回记录数

/***********************************************脚本举例*******************************************************/
------------------------------------------------
--创建测试数据表
------------------------------------------------
create table Bonus(
empno number primary key,
ename varchar2(50),
job varchar2(20),
sal number,
comm number);

------------------------------------------------
--创建存储过程程序包
------------------------------------------------
create or replace package Bonus_pkg
is
------------------------------------------------
--创建全局游标变量类型，用于返回记录操作
------------------------------------------------
type Bonus_rec is record(
empno Bonus.empno%type,
ename Bonus.ename%type,
job Bonus.job%type,
sal Bonus.sal%type,
comm Bonus.comm%type);

------------------------------------------------
--创建全局CURSOR类型，用于构造数据源
------------------------------------------------
type b_cursor is ref cursor return Bonus_rec;

------------------------------------------------
--创建全局数据表类型，用于执行查询
------------------------------------------------
type bontab is table of Bonus_rec index by binary_integer;

------------------------------------------------
--创建数据源程序包，用于前台数据源
------------------------------------------------
procedure Bonus_refcur(Bonus_data in out b_cursor);

------------------------------------------------
--创建数据查询程序包，用于前台Execute_Query
------------------------------------------------
procedure Bonus_query(Bonus_data in out bontab);

----------------------------------------------------------------
--创建数据操作程序包，用于前台Insert/Update/Delete/Lock
----------------------------------------------------------------
procedure Bonus_insert(r in Bonus_rec);
procedure Bonus_lock(s in Bonus.empno%type);
procedure Bonus_update(t in Bonus_rec);
procedure Bonus_delete(t in Bonus_rec);

----------------------------------------------------------------
--创建返回记录条数函数
----------------------------------------------------------------
function count_query
return number;

end Bonus_pkg;
/

create or replace package body Bonus_pkg
is 
----------------------------------------------------------------
--构造数据源
----------------------------------------------------------------
procedure Bonus_refcur(Bonus_data in out b_cursor)
is
begin
--打开游标，由于参数是In Out类型，所以直接返回
open Bonus_data for
select empno, ename, job, sal, comm
from Bonus;
end Bonus_refcur;


----------------------------------------------------------------
--执行FORMS的Execute_Query
----------------------------------------------------------------
procedure Bonus_query(Bonus_data in out bontab)
is
ii Number:=1;
--定义游标
cursor bonselect is
select empno, ename, job, sal, comm
from Bonus;
begin
--打开游标
open bonselect;
--在循环中，将所有数据读入数据表变量
loop
fetch bonselect into
Bonus_data(ii).empno,
Bonus_data(ii).ename,
Bonus_data(ii).job,
Bonus_data(ii).sal,
Bonus_data(ii).comm;
exit when bonselect%notfound;
ii:=ii+1
end loop;
end Bonus_query;

----------------------------------------------------------------
--执行FORMS的Insert
----------------------------------------------------------------
procedure Bonus_insert(r in Bonus_rec)
is
begin
--插入数据，注意没有Commit，Commit的动作可由前台Commit_Form完成
insert into Bonus
values(r.empno, r.ename, r.job, r.sal, r.comm);
end Bonus_insert;

----------------------------------------------------------------
--执行FORMS的Lock
----------------------------------------------------------------
procedure Bonus_lock(s in Bonus.empno%type)
is
v_rownum number;
begin
--锁定数据，利用Select For Update完成，注意：V_RowNum没有起实际的作用，只是配合Select For Update语句正确执行而已。
select empno
into v_rownum
from Bonus
where empno=s
for update of ename;
end Bonus_lock;

----------------------------------------------------------------
--执行FORMS的Update
----------------------------------------------------------------
procedure Bonus_update(t in Bonus_rec)
is
begin
--更新数据，注意没有Commit，Commit的动作可由前台Commit_Form完成
update Bonus
set ename=t.ename, job=t.job, sal=t.sal, comm=t.comm
where empno=t.empno;
end Bonus_update;

----------------------------------------------------------------
--执行FORMS的Delete
----------------------------------------------------------------
procedure Bonus_delete(t in Bonus_rec)
is
begin
--删除数据，注意没有Commit，Commit的动作可由前台Commit_Form完成
delete from Bonus
where empno=t.empno;
end Bonus_delete;

----------------------------------------------------------------
--获取返回记录条数
----------------------------------------------------------------
function count_query
return number
is
r number;
begin
select count(0)
into r
from Bonus;
return r;
end count_query;

end Bonus_pkg;
/