存储过程循环大家帮忙调上

2012-12-21

存储过程循环大家帮忙调下create or replace procedure tj_count_couponasanumberv_limitid numberv_li

存储过程循环大家帮忙调下
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;

[最优解释]

引用:

以前没写过存储过程！比较初级！刚刚调通了。但是测试的时候

这报错！

返回多条了呗，还能是什么错误。

[其他解释]

引用:

以前没写过存储过程！比较初级！刚刚调通了。但是测试的时候

这报错！

为啥会返回多条呢加了 AND ROWNUM=I 不是应该返回一条么？
[其他解释]

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;

[其他解释]
还是不对啊！
这是错误信息
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 --俩点。。。 晕死。

引用:

还是不对啊！
这是错误信息
Compilation errors for PROCEDURE ASZK820.TJ_COUNT_COUPON_2
Error: PLS-00103: Encountered the symbol "." when expecting one of the following:
* & - + / at ……

[其他解释]


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;

[其他解释]
CREATE OR REPLACE PROCEDURE TJ_COUNT_COUPON_2

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
      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_2

Error: 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 T

Error: 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')),

[其他解释]

引用:

SQL code123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657CREATE OR REPLACE PROCEDURE TJ_COUNT_COUPON AS A NUMBER; V……

where 那行报错！错误在上边！

[其他解释]
rownum=I，当I>=2时能取到值么？
[其他解释]

WHERE ROWNUM = I; -- 这个跟谁的，我擦

引用:

CREATE OR REPLACE PROCEDURE TJ_COUNT_COUPON_2

AS
  A              NUMBER;
  V_LIMITID      NUMBER;
  V_LIMITNAME    VARCHAR2;
  V_MERCHANTNAME VARCHAR2;

BEGIN
  SELECT COUNT(*……

[其他解释]
以前没写过存储过程！比较初级！刚刚调通了。但是测试的时候

这报错！
[其他解释]


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;

这是调通的存储过程！
[其他解释]

引用:

以前没写过存储过程！比较初级！刚刚调通了。但是测试的时候

这报错！

你这不是没写过，就是不会写，不要强调自己其他方面的经验，有开发经验的人表现成这个样子，我忍不了了。

热点排行

oracle

存储过程循环 大家帮忙调上

存储过程循环大家帮忙调上