orale存储过程

---先创建序列，然后建立存储过程。
    
        CREATE SEQUENCE  
       "CEBMALL"."DATA_ITEM_ID_SEQ"  
       MINVALUE 1 MAXVALUE 9999999999999999999999999999
        INCREMENT BY 1 
        START WITH 1 
        CACHE 100 
        NOORDER  NOCYCLE ;       
 
        CREATE SEQUENCE  
       "CEBMALL"."ORDER_CD_SEQ"  
       MINVALUE 1 MAXVALUE 90000000000000000
        INCREMENT BY 1 
        START WITH 10000000000000000 
        CACHE 100 
        NOORDER  NOCYCLE ;    
 
================================
create or replace
PROCEDURE PRO_MOI
AS
BEGIN
  DECLARE
    CONT   NUMBER;
    CT     NUMBER;
    CONT_2 VARCHAR2(200);
    CONT_3 VARCHAR2(200);
    CONT_4 VARCHAR2(200);
    ADDRESS  VARCHAR2(200);
    NAMETABLE  VARCHAR2(200);
    DATETABLE  VARCHAR2(200);
    TIMETABLE  VARCHAR2(200);
    EMAIL  VARCHAR2(200);
    YEAR_ROUND VARCHAR2(10);
    SIX_ROUND VARCHAR2(10);
 
    TELEPHONE VARCHAR2(30);
    USERID_ROUND VARCHAR2(30); 
    AMT_ROUND VARCHAR2(10);
 
BEGIN
 
    CONT := 1;
    CONT_2 := 1;
 
    FOR CONT IN 1..10 LOOP
    
      FOR CONT_2 IN 1..9  LOOP
        select round(dbms_random.value(2015, 2017), 0) into YEAR_ROUND from dual;--随机日期，2015 2016 2017
        select round(dbms_random.value(100000, 999999), 0) into SIX_ROUND  from dual;--随机数字，6位 流水号
        select round(dbms_random.value(100, 9999), 0) into USERID_ROUND  from dual;--随机USERID ，3,4位
        select round(dbms_random.value(100, 9999), 2) into AMT_ROUND  from dual;--随机金额 ， 2位小数
        CONT_3 := TO_TIMESTAMP( '12-'||CONT_2||'月-15 04.00.43.000000000 下午','DD-MON-RR HH.MI.SS.FF AM') ;
        CONT_4 := TO_TIMESTAMP('13-'||CONT_2||'月-17 04.00.43.000000000 下午','DD-MON-RR HH.MI.SS.FF AM') ;
        ADDRESS := '河南省郑州市金水区花园北路１４０号（农行培训学校旁）郑州北人印刷'||CONT ;
        NAMETABLE := '孟献军'||CONT ;
        DATETABLE := YEAR_ROUND||'0'||CONT_2||'14' ; 
        TIMETABLE := '100'||CONT_2||'10' ;
        EMAIL := CONT||'MENGJUN04929@SOHU.COM' ;
        TELEPHONE := '13613'||SIX_ROUND ;  --随机电话号码，后6位随机变化
        
INSERT INTO M_ORDER 
(ID, ORDER_CD, REWARD_ORDER_CD, USER_ID, TYPES, SUM_PRICE, SUM_REWARD, ORDER_DESC, PAYMENT_ID, DELIVERY_ID, EDITOR, CREATED_DATE, EDIT_DATE, STATE, STATE_DESC, DISP_ID, INVOICE_ID, EXT_PARA, DELIVERY_STATE, DEL_DATE) 
VALUES 
(M_ORDER_SEQ.NEXTVAL, ORDER_CD_SEQ.NEXTVAL, null, 1, 1, 0, 0, null, 0, 26, 0, CONT_3, CONT_4, 2, null, 0, '0                   ', null, null, null);
 
 INSERT INTO M_DELIVERY_INFO 
(ID, PROVINCE, CITY, ADDRESS, ZIP_CODE, RECV_NAME, RECV_PHONE, RECV_EMAIL, DLY_DESC, AREA, PROVINCEID, CITYID, AREAID) 
VALUES 
(M_DELIVERY_INFO_SEQ.NEXTVAL, null, null, ADDRESS, SIX_ROUND, NAMETABLE, TELEPHONE, EMAIL, null, null, null, null, null);
 
INSERT INTO M_ORDER_ITEM 
(ID, ORDER_ID, DEALER_ID, ITEM_ID, ITEM_TYPE, PRICE, QUANTITY, COST, REWARD_POINTS, EDITOR, CREATED_DATE, EDIT_DATE, 
STATE, STATE_DESC, ITEM_DESC, INSTALMENT, EXT_PARA, USER_ID, OI_CD, PROMOTION_ID, GIFT_ID, COLOR_ID, SPECS_ID, PAY_DATE, PAY_TIME, 
CLT_JOUR_NO, PAY_NO, FEE_AMT, AMT3, SRCCHNLNO, CUSTOMER_LEVEL, DUTY, DEALER_OI_CD, SEQ_NO, CLR_DATE)
 VALUES 
 (M_ORDER_ITEM_SEQ.NEXTVAL, M_ORDER_SEQ.CURRVAL, M_DELIVERY_INFO_SEQ.CURRVAL, DATA_ITEM_ID_SEQ.NEXTVAL, 2, 0, 1,AMT_ROUND, 0, 0, CONT_3 , CONT_4, 
 2, null, null, 0, null,  USERID_ROUND, '955441010', 0, 0, 0, 0, DATETABLE, TIMETABLE,
 SIX_ROUND, '1', '0.00', '0', null, null, null, null, null, null);
 
INSERT INTO M_PAYMENT_INFO (ID, CARD_NO, CARD_TYPE, PAYMENT_DESC) 
VALUES 
(M_PAYMENT_INFO_SEQ.NEXTVAL, CARD_NO_TEST_SEQ.nextval, 1, null);
 
      END LOOP;
    END LOOP;
    COMMIT;
  END;
END PRO_MOI;
 
 
==============================
CREATE OR REPLACE PROCEDURE "CEBMALL"."PRO_PAYCARDCHECK"
 
AS
begin 
  DECLARE
    cardno   NUMBER;
BEGIN
cardno:=1062540308868324;
FOR CONT IN 1..10000000  LOOP
cardno:=cardno+1;
  INSERT INTO PAY_CARD_CHECK (ID, CARD_NO, LAST_DATE, TRY_TIMES, LAST_PAYDATE, SUC_TIMES) VALUES 
  ( M_ORDER_ITEM_SEQ.NEXTVAL,  cardno, '20140715', 1, null, 0);
END LOOP;
    COMMIT;
 
END;
END PRO_PAYCARDCHECK;
===============================

 
 
---调用存储过程
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;
CALL PRO_MOI();
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;