我觉得你这并不是ORACLE的PACKAGE,而是希望得到一个程序名,然后 执行这个程序即可, 这样就好了,先建立存储过程名(程序名)TEST,然后执行 exec test; create or replace procedure test as m_item CHAR(20); m_loc char(10); m_onhand float; CURSOR rad_cursor IS select * from tmp_weeklysku; rad_val rad_cursor%rowtype; begin open rad_cursor; loop fetch rad_cursor into rad_val; exit when rad_cursor%Notfound; m_item := rad_val.item; m_loc := rad_val.loc; m_onhand := rad_val.oh_hand; update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc); end loop; close rad_cursor; commit; end test; /
有必要做成个package吗?做成个存储过程或函数就可以了。 create or replace procedure test as m_item CHAR(20); m_loc char(10); m_onhand float; CURSOR rad_cursor IS select * from tmp_weeklysku; rad_val rad_cursor%rowtype; begin open rad_cursor; loop fetch rad_cursor into rad_val; exit when rad_cursor%Notfound; m_item := rad_val.item; m_loc := rad_val.loc; m_onhand := rad_val.oh_hand; update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc); end loop; close rad_cursor; commit; end;
如果非要做成包,这样。然后用exec user_utils.test就可以了 CREATE OR REPLACE PACKAGE USER_UTILS AS PROCEDURE TEST; END USER_UTILS; /CREATE OR REPLACE PACKAGE BODY USER_UTILS AS procedure test as m_item CHAR(20); m_loc char(10); m_onhand float; CURSOR rad_cursor IS select * from tmp_weeklysku; rad_val rad_cursor%rowtype; begin open rad_cursor; loop fetch rad_cursor into rad_val; exit when rad_cursor%Notfound; m_item := rad_val.item; m_loc := rad_val.loc; m_onhand := rad_val.oh_hand; update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc); end loop; close rad_cursor; commit; end; END USER_UTILS; /
执行这个程序即可,
这样就好了,先建立存储过程名(程序名)TEST,然后执行 exec test;
create or replace procedure test
as
m_item CHAR(20);
m_loc char(10);
m_onhand float;
CURSOR rad_cursor IS
select * from tmp_weeklysku;
rad_val rad_cursor%rowtype;
begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%Notfound;
m_item := rad_val.item;
m_loc := rad_val.loc;
m_onhand := rad_val.oh_hand;
update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc);
end loop;
close rad_cursor;
commit;
end test;
/
create or replace procedure test
as
m_item CHAR(20);
m_loc char(10);
m_onhand float; CURSOR rad_cursor IS
select * from tmp_weeklysku;
rad_val rad_cursor%rowtype; begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%Notfound;
m_item := rad_val.item;
m_loc := rad_val.loc;
m_onhand := rad_val.oh_hand;
update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc);
end loop;
close rad_cursor;
commit;
end;
CREATE OR REPLACE PACKAGE USER_UTILS
AS
PROCEDURE TEST;
END USER_UTILS;
/CREATE OR REPLACE PACKAGE BODY USER_UTILS
AS
procedure test
as
m_item CHAR(20);
m_loc char(10);
m_onhand float; CURSOR rad_cursor IS
select * from tmp_weeklysku;
rad_val rad_cursor%rowtype; begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%Notfound;
m_item := rad_val.item;
m_loc := rad_val.loc;
m_onhand := rad_val.oh_hand;
update inw_weeklysku set weeklyoh = m_onhand where trim(item) = trim(m_item) and trim(loc) = trim(m_loc);
end loop;
close rad_cursor;
commit;
end;
END USER_UTILS;
/
这些每周要更新一次,现在要把好几个存储过程及PL/SQL打包成一个,以供外部接口调用,而如果调用存储会增加很多开销,不过谢谢各位的好意,分数我还是会送的,不过有谁能打包成PACKAGE的话,500分照送,绝不实言 我的目的是帮我把这个做成PACKAGE之后其它的我就可以照做了
ATCG(ATCG) 50分
zhaoyongzhu(zhaoyongzhu):等下我开新贴你进来领分,要开5个,把你的QQ告诉我好吗,我的是347106