在包中设置一个变量 PUB_PZNM,在存储过程A中生成,如何在存储过程b和C中都能调用到这个生成的变量呀?CREATE OR REPLACE Package pkg_addpz
as
--定义公用的最大内码号 pub_pznm varchar2(20); --试过放在这里也不行.都显示的是空值
function F_Get_PZNM return varchar2;
procedure P_Add_ZWPZK(KJND in varchar2,KJQJ in varchar2,
PZZ in varchar2,PZRQ in varchar2,
FJZS in integer,ZDR in varchar2,PZFLNM out varchar2);
procedure P_Add_ZWPZFL
(KMBH in varchar2,JSFS in varchar2,JSDH IN VARCHAR2,ZY in varchar2,
JE in number,JZFX in char,
YWRQ in varchar2,BMBH in varchar2,DWBH in varchar2,ZGBH in varchar2);
end pkg_addpz;
create or replace package body pkg_addpz as
--定义公用的最大内码号
pub_pznm varchar2(20);
--处理凭证编号,使最大凭证编号+1
--定义攻取最大凭证内码的函数
function F_Get_PZNM return varchar2
as
v_pznm varchar2(10) :='';
begin
SELECT MAX(to_number(ZWPZK_PZNM))+1 AS PZNM into v_pznm FROM ZWPZK;
return v_pznm;
exception
when no_data_found then
v_pznm:='1';
return v_pznm;end F_Get_PZNM;
procedure P_Add_ZWPZK
(KJND in varchar2,KJQJ in varchar2,PZZ in varchar2,PZRQ in varchar2,FJZS in integer,ZDR in varchar2,PZFLNM out varchar2)
is
V_pzbh varchar2(4):='';
V_PZZ VARCHAR2(10):='';
V_SJPZBH VARCHAR2(14):='';
V_pzbh_1 varchar2(10):='';--凭证编号+1
V_PZNM VARCHAR2(9):='';
begin
--取凭证内码,凭证编号
SELECT F_GET_PZNM INTO pub_pznm FROM DUAL;--将凭证内码函数取出的值赋予PUB_PZNM
SELECT f_get_pzbh(KJND,KJQJ,PZZ) INTO V_SJPZBH FROM DUAL; V_PZZ:=SUBSTR(V_SJPZBH,1,2);
V_PZBH:=SUBSTR(V_SJPZBH,3,LENGTH(V_SJPZBH));
V_PZBH_1:=F_DEAL_BH(V_PZBH);
--输出凭证内码,用于凭证分录调用.
pzflnm:=pub_pznm;
insert into ZWPZK (ZWPZK_PZNM,ZWPZK_KJND,ZWPZK_KJQJ,ZWPZK_PZRQ,ZWPZK_PZBH,
ZWPZK_LXBH,ZWPZK_FJZS,ZWPZK_ZDR) VALUES
(pub_pznm,KJND,KJQJ,PZRQ,V_SJPZBH,'01',FJZS,ZDR);
--//更改内部编码
update LSNBBM set lsnbbm_dqnm=(SELECT MAX(to_number(ZWPZK_PZNM)) FROM ZWPZK ) where LSNBBM_xtbh='ZW' AND
LSNBBM_NMBH='ZWPZNM'; --//更改凭证编号(要处理最大凭证号+1) UPDATE ZWPZBH SET ZWPZBH_PZBH=V_PZBH_1 WHERE ZWPZBH_KJND=KJND AND ZWPZBH_KJQJ=KJQJ AND ZWPZBH_PZZ=PZZ;
end P_ADD_ZWPZK;procedure P_Add_ZWPZFL(KMBH in varchar2,JSFS in varchar2,
JSDH IN VARCHAR2,ZY in varchar2,JE in number,JZFX in char,
YWRQ in varchar2,BMBH in varchar2,DWBH in varchar2,ZGBH in varchar2)
is
nm varchar2(20):='';
begin
--在这里直接调用显示PUB_PZNM是空值??????????????????????????
nm:=pub_pznm;
end P_Add_ZWPZFL;
end PKG_ADDPZ;
用PL/SQL 调试,先运行P_ADD_ZWPZK可以产生PUB_PZNM的值.
再运行P_ADD_ZWPZFL后显示的值就变成NULL了.
as
--定义公用的最大内码号 pub_pznm varchar2(20); --试过放在这里也不行.都显示的是空值
function F_Get_PZNM return varchar2;
procedure P_Add_ZWPZK(KJND in varchar2,KJQJ in varchar2,
PZZ in varchar2,PZRQ in varchar2,
FJZS in integer,ZDR in varchar2,PZFLNM out varchar2);
procedure P_Add_ZWPZFL
(KMBH in varchar2,JSFS in varchar2,JSDH IN VARCHAR2,ZY in varchar2,
JE in number,JZFX in char,
YWRQ in varchar2,BMBH in varchar2,DWBH in varchar2,ZGBH in varchar2);
end pkg_addpz;
create or replace package body pkg_addpz as
--定义公用的最大内码号
pub_pznm varchar2(20);
--处理凭证编号,使最大凭证编号+1
--定义攻取最大凭证内码的函数
function F_Get_PZNM return varchar2
as
v_pznm varchar2(10) :='';
begin
SELECT MAX(to_number(ZWPZK_PZNM))+1 AS PZNM into v_pznm FROM ZWPZK;
return v_pznm;
exception
when no_data_found then
v_pznm:='1';
return v_pznm;end F_Get_PZNM;
procedure P_Add_ZWPZK
(KJND in varchar2,KJQJ in varchar2,PZZ in varchar2,PZRQ in varchar2,FJZS in integer,ZDR in varchar2,PZFLNM out varchar2)
is
V_pzbh varchar2(4):='';
V_PZZ VARCHAR2(10):='';
V_SJPZBH VARCHAR2(14):='';
V_pzbh_1 varchar2(10):='';--凭证编号+1
V_PZNM VARCHAR2(9):='';
begin
--取凭证内码,凭证编号
SELECT F_GET_PZNM INTO pub_pznm FROM DUAL;--将凭证内码函数取出的值赋予PUB_PZNM
SELECT f_get_pzbh(KJND,KJQJ,PZZ) INTO V_SJPZBH FROM DUAL; V_PZZ:=SUBSTR(V_SJPZBH,1,2);
V_PZBH:=SUBSTR(V_SJPZBH,3,LENGTH(V_SJPZBH));
V_PZBH_1:=F_DEAL_BH(V_PZBH);
--输出凭证内码,用于凭证分录调用.
pzflnm:=pub_pznm;
insert into ZWPZK (ZWPZK_PZNM,ZWPZK_KJND,ZWPZK_KJQJ,ZWPZK_PZRQ,ZWPZK_PZBH,
ZWPZK_LXBH,ZWPZK_FJZS,ZWPZK_ZDR) VALUES
(pub_pznm,KJND,KJQJ,PZRQ,V_SJPZBH,'01',FJZS,ZDR);
--//更改内部编码
update LSNBBM set lsnbbm_dqnm=(SELECT MAX(to_number(ZWPZK_PZNM)) FROM ZWPZK ) where LSNBBM_xtbh='ZW' AND
LSNBBM_NMBH='ZWPZNM'; --//更改凭证编号(要处理最大凭证号+1) UPDATE ZWPZBH SET ZWPZBH_PZBH=V_PZBH_1 WHERE ZWPZBH_KJND=KJND AND ZWPZBH_KJQJ=KJQJ AND ZWPZBH_PZZ=PZZ;
end P_ADD_ZWPZK;procedure P_Add_ZWPZFL(KMBH in varchar2,JSFS in varchar2,
JSDH IN VARCHAR2,ZY in varchar2,JE in number,JZFX in char,
YWRQ in varchar2,BMBH in varchar2,DWBH in varchar2,ZGBH in varchar2)
is
nm varchar2(20):='';
begin
--在这里直接调用显示PUB_PZNM是空值??????????????????????????
nm:=pub_pznm;
end P_Add_ZWPZFL;
end PKG_ADDPZ;
用PL/SQL 调试,先运行P_ADD_ZWPZK可以产生PUB_PZNM的值.
再运行P_ADD_ZWPZFL后显示的值就变成NULL了.
解决方案 »
- 求教一条LIKE SQL语句的优化
- oracle Fatal NI connect error 12170.错误
- ora817建立ora734上的表的同义词
- ORACLE字符串截取成数组
- 有用OCI开发过oracle georaster的吗
- 纵表转横表问题,不是有小数据。
- 弟弟妹妹们,大姐求助,我从SQLSERVER转到ORACLE,这段代码在ORACLE里如何写?谢谢!
- 急用:用sql语句怎样获取s.t表中头5个所有的数据,用select,top,order by
- 怎樣得到oracle,procedure中返回的結果集??
- 各位大虾清赐教一简单问题,一定给分
- 急求救一个ebs的问题
- 对两个表中的记录进行比较
PROCEDURE SET_VAR(TMPVALUE VARCHAR);
PROCEDURE PRINT_VAR;
END ABC;CREATE OR REPLACE PACKAGE BODY ABC AS
GLOBAL_VAR VARCHAR(100);
PROCEDURE SET_VAR(TMPVALUE VARCHAR) IS
BEGIN
GLOBAL_VAR := TMPVALUE;
END;
PROCEDURE PRINT_VAR IS
BEGIN
DBMS_OUTPUT.PUT_LINE(GLOBAL_VAR);
END;
END ABC;
下面是实际执行语句:SET SERVEROUTPUT ON
BEGIN
ABC.SET_VAR('HELLO GLOBAL');
ABC.PRINT_VAR;
END;
/