create or replace procedure tj_count_couponas
a number;
v_limitid number;
v_limitname varchar2;
v_merchantname varchar2;Begin
select count(*) into a
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
for i in 1...a loop
select t2.name into v_merchantname,
t.coupon_id into v_limitid ,
t.coupon_name into v_limitname
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
where rownum=i
insert into count_coupon((id, coupon_id, coupon_name, rec_date, down_count, merchant_name) values
(SEQ_count_coupon_ID.Nextval,v_limitid,v_limitname,to_char(sysdate,'yyyy-mm-dd'),
(select count(*) as t from user_coupon where coupon_id=v_limitid and to_char(reg_date,'yyyy-MM-dd')=to_char(sysdate,'yyyy-mm-dd')),
v_merchantname
);
end loop;
End tj_count_coupon;
a number;
v_limitid number;
v_limitname varchar2;
v_merchantname varchar2;Begin
select count(*) into a
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
for i in 1...a loop
select t2.name into v_merchantname,
t.coupon_id into v_limitid ,
t.coupon_name into v_limitname
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
where rownum=i
insert into count_coupon((id, coupon_id, coupon_name, rec_date, down_count, merchant_name) values
(SEQ_count_coupon_ID.Nextval,v_limitid,v_limitname,to_char(sysdate,'yyyy-mm-dd'),
(select count(*) as t from user_coupon where coupon_id=v_limitid and to_char(reg_date,'yyyy-MM-dd')=to_char(sysdate,'yyyy-mm-dd')),
v_merchantname
);
end loop;
End tj_count_coupon;
A NUMBER;
V_LIMITID NUMBER;
V_LIMITNAME VARCHAR2;
V_MERCHANTNAME VARCHAR2;BEGIN
SELECT COUNT(*)
INTO A
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'); FOR I IN 1 ...A LOOP
SELECT T2.NAME
INTO V_MERCHANTNAME, T.COUPON_ID
INTO V_LIMITID, T.COUPON_NAME
INTO V_LIMITNAME
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')
WHERE ROWNUM = I;
INSERT INTO COUNT_COUPON
(ID, COUPON_ID, COUPON_NAME, REC_DATE, DOWN_COUNT, MERCHANT_NAME)
VALUES
(SEQ_COUNT_COUPON_ID.NEXTVAL,
V_LIMITID,
V_LIMITNAME,
TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
(SELECT COUNT(*) AS T
FROM USER_COUPON
WHERE COUPON_ID = V_LIMITID
AND TO_CHAR(REG_DATE, 'yyyy-MM-dd') =
TO_CHAR(SYSDATE, 'yyyy-mm-dd')),
V_MERCHANTNAME);
END LOOP;END TJ_COUNT_COUPON;
这是错误信息
Compilation errors for PROCEDURE ASZK820.TJ_COUNT_COUPON_2
Error: PLS-00103: Encountered the symbol "." when expecting one of the following:
* & - + / at mod remainder rem .. <an exponent (**)> ||
multiset
Line: 22
Text: FOR I IN 1...A LOOP
FOR I IN 1 ..A LOOP --俩点 晕死。
CREATE OR REPLACE PROCEDURE TJ_COUNT_COUPON AS
A NUMBER;
V_LIMITID NUMBER;
V_LIMITNAME VARCHAR2;
V_MERCHANTNAME VARCHAR2;BEGIN
SELECT COUNT(*)
INTO A
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'); --分号要写 FOR I IN 1 .. A LOOP --俩点
SELECT T2.NAME
INTO V_MERCHANTNAME, T.COUPON_ID
INTO V_LIMITID, T.COUPON_NAME
INTO V_LIMITNAME
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')
WHERE ROWNUM = I; --分号要写
INSERT INTO COUNT_COUPON --多写了个左括号
(ID, COUPON_ID, COUPON_NAME, REC_DATE, DOWN_COUNT, MERCHANT_NAME)
VALUES
(SEQ_COUNT_COUPON_ID.NEXTVAL,
V_LIMITID,
V_LIMITNAME,
TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
(SELECT COUNT(*) AS T
FROM USER_COUPON
WHERE COUPON_ID = V_LIMITID
AND TO_CHAR(REG_DATE, 'yyyy-MM-dd') =
TO_CHAR(SYSDATE, 'yyyy-mm-dd')),
V_MERCHANTNAME);
END LOOP;END TJ_COUNT_COUPON;
A NUMBER;
V_LIMITID NUMBER;
V_LIMITNAME VARCHAR2;
V_MERCHANTNAME VARCHAR2;BEGIN
SELECT COUNT(*)
INTO A
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'); FOR I IN 1..A LOOP SELECT
T2.NAME INTO V_MERCHANTNAME
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss');
SELECT
TC.COUPON_ID INTO V_LIMITID
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss');
SELECT
TC.COUPON_NAME INTO V_LIMITNAME
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss');
WHERE ROWNUM=I;
INSERT INTO COUNT_COUPON
(ID, COUPON_ID, COUPON_NAME, REC_DATE, DOWN_COUNT, MERCHANT_NAME)
VALUES
(SEQ_COUNT_COUPON_ID.NEXTVAL,
V_LIMITID,
V_LIMITNAME,
TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
(SELECT COUNT(*) AS T
FROM USER_COUPON
WHERE COUPON_ID = V_LIMITID
AND TO_CHAR(REG_DATE, 'yyyy-MM-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')),
V_MERCHANTNAME);
END LOOP;END TJ_COUNT_COUPON_2;
WHERE ROWNUM=I; 这行报错
Compilation errors for PROCEDURE ASZK820.TJ_COUNT_COUPON_2Error: PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:
( begin case declare end exit for goto if loop mod null
pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
Line: 57
Text: WHERE ROWNUM=I;
Error: PLS-00103: Encountered the symbol "INSERT"
Line: 58
Text: INSERT INTO COUNT_COUPON
Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternat
Line: 65
Text: (SELECT COUNT(*) AS TError: PLS-00103: Encountered the symbol ")" when expecting one of the following:
. ( * % & - + ; / at for mod remainder rem <an exponent (**)>
and or group having intersect minus order start union where
connect || multiset
Line: 68
Text: AND TO_CHAR(REG_DATE, 'yyyy-MM-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')),
where 那行 报错 !错误在上边!
CREATE OR REPLACE PROCEDURE TJ_COUNT_COUPON AS
A NUMBER;
V_LIMITID NUMBER;
V_LIMITNAME varchar2(50);
V_MERCHANTNAME varchar2(50);BEGIN
SELECT COUNT(*)
INTO A
FROM COUPONINFO T
LEFT JOIN COUPON_MERCHANT T1
ON T.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(T.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(T.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss'); FOR I IN 1..A LOOP SELECT
T2.NAME INTO V_MERCHANTNAME
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')
AND ROWNUM=I;
SELECT
TC.COUPON_ID INTO V_LIMITID
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')
AND ROWNUM=I;
SELECT
TC.COUPON_NAME INTO V_LIMITNAME
FROM COUPONINFO TC
LEFT JOIN COUPON_MERCHANT T1
ON TC.COUPON_ID = T1.PRODUCT_ID
LEFT JOIN MERCHANT T2
ON T1.MERCHANT_ID = TO_CHAR(T2.MERCHANT_ID)
AND T1.PRODUCT_TYPE = 1
WHERE TO_DATE(TC.END_DATE, 'yyyy-mm-dd HH24:mi:ss') >= SYSDATE
AND TO_CHAR(TC.STR_DATE, 'yyyy-mm-dd HH24:mi:ss') <=
TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')
AND ROWNUM=I; INSERT INTO COUNT_COUPON
(ID, COUPON_ID, COUPON_NAME, REC_DATE, DOWN_COUNT, MERCHANT_NAME)
VALUES
(SEQ_COUNT_COUPON_ID.NEXTVAL,
V_LIMITID,
V_LIMITNAME,
(select TO_CHAR(SYSDATE, 'yyyy-mm-dd') as rec_date from dual),
(SELECT COUNT(*) AS T
FROM USER_COUPON
WHERE COUPON_ID = V_LIMITID
AND TO_CHAR(REG_DATE, 'yyyy-MM-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')),
V_MERCHANTNAME);
END LOOP;END TJ_COUNT_COUPON;
这是调通的 存储过程!
为啥会返回多条呢 加了 AND ROWNUM=I 不是应该返回一条么?
为什么看这里http://blog.csdn.net/fw0124/article/details/6692022
你这种情况可以用游标来实现。