在给str :=
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
这条语句赋值的时候,如果只有一个条件的话是可以执行的:
例一:
str :=
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1)
例二:
str :=
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.CATECODE='''||sys_catecode||'''';
以上两个例子,执行存储过程是没有问题的,但是如果两个条件都加上,存储过程就不能往下执行(在给str赋值之后)给str赋值是在存储过程中,运用游标的时候
下面是我写的存储过程:
CREATE OR REPLACE PROCEDURE order_prod_procedure
IS
sys_keyword VARCHAR2 (20);
sys_effecttime DATE;
sys_catecode VARCHAR2 (20);
sys_ordertype NUMBER (1);
sys_buskeyid NUMBER;
sys_memberid NUMBER;
catename_price VARCHAR2 (20);
mtown_price VARCHAR2 (20);
uptime_price DATE;
statepage_price VARCHAR (255);
prodpic_price VARCHAR (255);
mtelcode_price VARCHAR (100);
prodname_price VARCHAR (100);
BEGIN
DECLARE
CURSOR c_bus_key
IS
SELECT *
FROM sys_bus_key; TYPE t_sor IS REF CURSOR; c_prod_info t_sor;
c_prod_price t_sor;
c_orderid t_sor;
str VARCHAR2 (255);
str3 VARCHAR2 (255);
datestr VARCHAR2 (20);
BEGIN
FOR v_sys_bus_key IN c_bus_key
LOOP
sys_keyword := v_sys_bus_key.keyword;
sys_effecttime := v_sys_bus_key.effecttime;
sys_catecode := v_sys_bus_key.catecode;
sys_catename :=v_sys_bus_key.catename;
sys_ordertype := v_sys_bus_key.ordertype;
sys_buskeyid := v_sys_bus_key.keyid;
sys_memberid := v_sys_bus_key.memberid;
datestr := 'yyyy-MM-dd';
IF sys_effecttime >=
TO_DATE (TO_CHAR (SYSDATE, 'yyyy-MM-dd'), 'yyyy-MM-dd')
THEN
if sys_ordertype =1
then
str2:='1';
DBMS_OUTPUT.put_line (str2);
if SUBSTR(sys_catecode,0,4)='c001'
then
str:= 'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
elsif SUBSTR(sys_catecode,0,4)='c002'
then
str:= 'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PLASTIC_PRICE p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
end if;
end if; OPEN c_prod_price FOR str;
LOOP
FETCH c_prod_price
INTO catename_price, prodpic_price, uptime_price, prodname_price, statepage_price,
mtown_price, mtelcode_price;
EXIT WHEN c_prod_price%NOTFOUND;
INSERT INTO sys_order_prod
(ORDERID,buykey, keyid, memberid,
catename, ordertype, orderadd, releasetime,
infopath, infoid, dereqpath, infopic,
releasemeth, infotitle
)
VALUES (seqp_sys_order_prod.nextval,sys_keyword, sys_buskeyid, sys_memberid,
catename_price, sys_ordertype, mtown_price, uptime_price,
statepage, NULL, NULL, prodpic,
mtelcode_price, prodname_price
);
END LOOP;
str:='';
CLOSE c_prod_price;
COMMIT;
end if;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
END order_prod_procedure;
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
这条语句赋值的时候,如果只有一个条件的话是可以执行的:
例一:
str :=
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1)
例二:
str :=
'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.CATECODE='''||sys_catecode||'''';
以上两个例子,执行存储过程是没有问题的,但是如果两个条件都加上,存储过程就不能往下执行(在给str赋值之后)给str赋值是在存储过程中,运用游标的时候
下面是我写的存储过程:
CREATE OR REPLACE PROCEDURE order_prod_procedure
IS
sys_keyword VARCHAR2 (20);
sys_effecttime DATE;
sys_catecode VARCHAR2 (20);
sys_ordertype NUMBER (1);
sys_buskeyid NUMBER;
sys_memberid NUMBER;
catename_price VARCHAR2 (20);
mtown_price VARCHAR2 (20);
uptime_price DATE;
statepage_price VARCHAR (255);
prodpic_price VARCHAR (255);
mtelcode_price VARCHAR (100);
prodname_price VARCHAR (100);
BEGIN
DECLARE
CURSOR c_bus_key
IS
SELECT *
FROM sys_bus_key; TYPE t_sor IS REF CURSOR; c_prod_info t_sor;
c_prod_price t_sor;
c_orderid t_sor;
str VARCHAR2 (255);
str3 VARCHAR2 (255);
datestr VARCHAR2 (20);
BEGIN
FOR v_sys_bus_key IN c_bus_key
LOOP
sys_keyword := v_sys_bus_key.keyword;
sys_effecttime := v_sys_bus_key.effecttime;
sys_catecode := v_sys_bus_key.catecode;
sys_catename :=v_sys_bus_key.catename;
sys_ordertype := v_sys_bus_key.ordertype;
sys_buskeyid := v_sys_bus_key.keyid;
sys_memberid := v_sys_bus_key.memberid;
datestr := 'yyyy-MM-dd';
IF sys_effecttime >=
TO_DATE (TO_CHAR (SYSDATE, 'yyyy-MM-dd'), 'yyyy-MM-dd')
THEN
if sys_ordertype =1
then
str2:='1';
DBMS_OUTPUT.put_line (str2);
if SUBSTR(sys_catecode,0,4)='c001'
then
str:= 'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PROD_INFO p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
elsif SUBSTR(sys_catecode,0,4)='c002'
then
str:= 'select p.CATENAME,p.PROD_PIC,p.UP_TIME ,p.PROD_NAME,p.STATEPAGE,m.MTOWN,m.MTEL_CODE from SYS_PLASTIC_PRICE p join sys_member m on p.MEMBERID=m.MEMBERID where p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1) and p.CATECODE='''||sys_catecode||'''';
end if;
end if; OPEN c_prod_price FOR str;
LOOP
FETCH c_prod_price
INTO catename_price, prodpic_price, uptime_price, prodname_price, statepage_price,
mtown_price, mtelcode_price;
EXIT WHEN c_prod_price%NOTFOUND;
INSERT INTO sys_order_prod
(ORDERID,buykey, keyid, memberid,
catename, ordertype, orderadd, releasetime,
infopath, infoid, dereqpath, infopic,
releasemeth, infotitle
)
VALUES (seqp_sys_order_prod.nextval,sys_keyword, sys_buskeyid, sys_memberid,
catename_price, sys_ordertype, mtown_price, uptime_price,
statepage, NULL, NULL, prodpic,
mtelcode_price, prodname_price
);
END LOOP;
str:='';
CLOSE c_prod_price;
COMMIT;
end if;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
END order_prod_procedure;
另外p.UP_TIME>=p.UP_TIME>=(TO_DATE (TO_CHAR (SYSDATE, ''yyyy-MM-dd''), ''yyyy-MM-dd'')-1)
改成p.UP_TIME>=trunc(sysdate)-1代码看着太累了会不会是因为if...elsif...没有else
导致str是null而报错,你能保证没有其他情况吗