当前位置: 代码迷 >> Oracle管理 >> 急 幫小弟我看下oracle 存儲過程
  详细解决方案

急 幫小弟我看下oracle 存儲過程

热度:365   发布时间:2016-04-24 05:25:00.0
急急急 幫我看下oracle 存儲過程
執行報如下錯誤:

ORA-00955: name is already used by an existing object
ORA-06512: at "SMP.SUNSET", line 28
ORA-06512: at line 1

十分感謝!!!!



/* Formatted on 2012/02/16 09:27 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE sunset
AS
  str VARCHAR2 (3000);
BEGIN
  str :=
  'CREATE TABLE SMP.TEMP_DM_defect
  (
  ID VARCHAR2(30 BYTE),
  WORK_MONTH VARCHAR2(2 BYTE),
  WORK_WEEK VARCHAR2(2 BYTE),
  WORK_DATE VARCHAR2(2 BYTE),
  WEEKDAY VARCHAR2(9 BYTE),
  SHIFT VARCHAR2(30 BYTE),
  SUBLINE_NAME VARCHAR2(90 BYTE),
  MODEL VARCHAR2(50 BYTE),
  PART_NO VARCHAR2(50 BYTE),
  WO_NO VARCHAR2(50 BYTE),
  TYPE VARCHAR2(50 BYTE),
  HOUR VARCHAR2(30 BYTE),
  ROUTE_CODE VARCHAR2(50 BYTE),
  PORT VARCHAR2(50 BYTE),
  DEFECT_CODE VARCHAR2(50 BYTE),
  REASON_CODE VARCHAR2(10 BYTE),
  CHINESE VARCHAR2(50 BYTE),
  QTY NUMBER
  )';

  EXECUTE IMMEDIATE str;

  COMMIT;
  str :=
  'insert into TEMP_DM_defect (SELECT 1 AS ID, TO_CHAR (create_time - 23 / 72, ''MM'') work_month,
  TO_CHAR (create_time - 23 / 72, ''WW'') work_week,
  TO_CHAR (create_time - 23 / 72, ''DD'') work_date,
  TO_CHAR (create_time, ''day'') weekday, shift, subline subline_name,
  pro_model model, part_no, ''0'' wo_no, 0 TYPE,
  TO_CHAR (a.create_time, ''hh24'') HOUR, description route_code, port,
  code defect_code, '' reason_code, chinese,
  COUNT (DISTINCT cellsn) qty
  FROM k93_cellerrorcode a, sfcs_time_subarea_apple b
  WHERE TO_CHAR (create_time, ''hh24:mi:ss'') >= begin_t
  AND TO_CHAR (create_time, ''hh24:mi:ss'') <= end_t
  AND SUBSTR (port, 1, 4) = ''port''
  AND create_time BETWEEN TO_DATE (''2012/1/1 08:00:00'',
  ''yyyy-mm-dd hh24:mi:ss''
  )
  AND TO_DATE ( TO_CHAR (SYSDATE, ''yyyy-mm-dd'')
  || '' 7:59:59'',
  ''yyyy-mm-dd hh24:mi:ss''
  )
GROUP BY TO_CHAR (create_time - 23 / 72, ''MM''),
  TO_CHAR (create_time - 23 / 72, ''WW''),
  TO_CHAR (create_time - 23 / 72, ''DD''),
  TO_CHAR (create_time, ''day''),
  shift,
  subline,
  pro_model,x
  part_no,
  TO_CHAR (a.create_time, ''hh24''),
  description,
  port,
  code,
  chinese)';

  EXECUTE IMMEDIATE str;

  COMMIT;
  str :=
  '
  CREATE TABLE SMP.TEMP_SFCS_DEFECTS
  (
  ORG_ID NUMBER NOT NULL,
  SN_KEY NUMBER,
  MODEL VARCHAR2(50 BYTE),
  PART_NO VARCHAR2(20 BYTE),
  WO_KEY NUMBER,
  DEFECT_KEY NUMBER,
  DEFECT_CODE VARCHAR2(10 BYTE),
  QTY NUMBER,
  DEFECT_KIND NUMBER DEFAULT 0,
  CUSTOMER_DEFECT_CODE VARCHAR2(10 BYTE),
  WS_ID NUMBER,
  CHECKER_ID NUMBER,
  I_TIME DATE DEFAULT SYSDATE,
  相关解决方案