创建oracle job时遇害到的问题

在pl/sql的command window中键入。SQL> CREATE OR REPLACE PROCEDURE PROC_CITY_DATE2T_CITY_ORDERS IS
  2    l_maxOper_date date;
  3    l_maxId number;
  4  
  5    CURSOR c_maxOper_date IS SELECT max(oper_date) FROM city_day;
  6    CURSOR c_maxId IS select count(cityid) from t_city_orders;
  7   BEGIN
  8     open  c_maxOper_date;
  9     fetch c_maxOper_date into l_maxOper_date;
 10     close c_maxOper_date;
 11     open  c_maxId;
 12     fetch c_maxId into l_maxId;
 13     close c_maxId;
 14  
 15  --插入
 16   INSERT INTO t_city_orders(
 17    oper_date,
 18    stat_type,
 19    cityid,
 20    city,
 21    excellent_days,
 22    fine_days,
 23    nogood_days,
 24    orders,
 25    l_excellent_days,
 26    l_fine_days,
 27    l_nogood_days,
 28    l_orders,
 29    r_excellent_days,
 30    r_fine_days,
 31    r_nogood_days,
 32    r_orders
 33  )
 34  SELECT l_maxOper_date,
 35  '30天内',
 36  l_maxId + 1,
 37  aaa.city,
 38  aaa.excellent_days,
 39  aaa.fine_days,
 40  aaa.nogood_days,
 41  aaa.orders,
 42  ccc.l_excellent_days,
 43  ccc.l_fine_days,
 44  ccc.l_nogood_days,
 45  ccc.l_orders,
 46  bbb.r_excellent_days,
 47  bbb.r_fine_days,
 48  bbb.r_nogood_days,
 49  bbb.r_orders
 50    FROM (SELECT aa.*,
 51                 ROW_NUMBER () OVER (ORDER BY aa.excellent_days DESC) orders
 52            FROM (SELECT city, SUM (DECODE (status,'优',1,0)) excellent_days,
 53                         SUM (DECODE (status,'良',1,0)) fine_days,
 54                         SUM (DECODE (status,'优',0,'良',0,1)) nogood_days
 55                    FROM city_day a
 56                   WHERE oper_date BETWEEN TRUNC (l_maxOper_date) - 30
 57                                       AND TRUNC (l_maxOper_date) + 0.99999 group by city) aa) aaa,
 58         (SELECT aa.*,
 59                 ROW_NUMBER () OVER (ORDER BY aa.r_excellent_days DESC)
 60                                                                       r_orders
 61            FROM (SELECT city,
 62                         SUM (DECODE (status,'优',1,0)) r_excellent_days,
 63                         SUM (DECODE (status,'良',1,0)) r_fine_days,
 64                         SUM (DECODE (status,'优',0,'良',0,1)) r_nogood_days
 65                    FROM city_day a
 66                   WHERE oper_date BETWEEN ADD_MONTHS (TRUNC (l_maxOper_date), -1) - 30
 67                                       AND ADD_MONTHS (TRUNC (l_maxOper_date) + 0.99999,
 68                                                       -1
 69                                                      ) group by city) aa) bbb,
 70         (SELECT aa.*,
 71                 ROW_NUMBER () OVER (ORDER BY aa.l_excellent_days DESC)
 72                                                                       l_orders
 73            FROM (SELECT city,
 74                         SUM (DECODE (status, '优', 1, 0)) l_excellent_days,
 75                         SUM (DECODE (status, '良', 1, 0)) l_fine_days,
 76                         SUM (DECODE (status,'优',0,'良',0,1)) l_nogood_days
 77                    FROM city_day a
 78                   WHERE oper_date BETWEEN ADD_MONTHS (TRUNC (l_maxOper_date), -12)
 79                                           - 30
 80                                       AND ADD_MONTHS (TRUNC (l_maxOper_date) + 0.99999,
 81                                                       -12
 82                                                      ) group by city) aa) ccc
 83   WHERE aaa.city = bbb.city(+) AND aaa.city = ccc.city(+);
 84   commit;
 85   END;
 86  /
 
Procedure created
 
SQL> 
SQL> declare
  2    job_city_day2t_city_orders number;
  3  begin
  4    sys.dbms_job.submit(
  5    :job_city_day2t_city_orders,
  6    'proc_city_date2t_city_orders',
  7    sysdate,
  8    'trunc(sysdate + 1)'
  9  );
 10    sys.dbms_output.put_line('Job Number is :' || to_char(job_city_day2t_city_orders));
 11    commit;
 12  end;
 13  /
 
declare
  job_city_day2t_city_orders number;
begin
  sys.dbms_job.submit(
  :job_city_day2t_city_orders,
  'proc_city_date2t_city_orders',
  sysdate,
  'trunc(sysdate + 1)'
);
  sys.dbms_output.put_line('Job Number is :' || to_char(job_city_day2t_city_orders));
  commit;
end;
 
ORA-01008: 并非所有变量都已绑定
 请指点．