ins_acc.sql

Oracle PLSQL for DBAs 源代码

BEGIN
   FOR l_acc_no IN 1 .. 100000
   LOOP
      INSERT INTO accounts
           VALUES (l_acc_no,

                   -- First Name
                   DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 21)),
                           1, 'Alan',
                           2, 'Alan',
                           3, 'Barbara',
                           4, 'Barbara',
                           5, 'Charles',
                           6, 'David',
                           7, 'Ellen',
                           8, 'Ellen',
                           9, 'Ellen',
                           10, 'Frank',
                           11, 'Frank',
                           12, 'Frank',
                           13, 'George',
                           14, 'George',
                           15, 'George',
                           16, 'Hillary',
                           17, 'Iris',
                           18, 'Iris',
                           19, 'Josh',
                           20, 'Josh',
                           'XXX'
                          ),

                   -- Last Name
                   DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 5)),
                           1, 'Smith',
                           DBMS_RANDOM.STRING ('A'
                                             , DBMS_RANDOM.VALUE (4, 30))
                          ),

                   -- Account Type
                   DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 5)),
                           1, 'S',
                           2, 'C',
                           3, 'M',
                           4, 'D',
                           'X'
                          ),

                   -- Folio ID
                   CASE
                      WHEN DBMS_RANDOM.VALUE (1, 100) < 51
                         THEN NULL
                      ELSE l_acc_no + FLOOR (DBMS_RANDOM.VALUE (1, 100))
                   END,

                   -- Sub Acc Type
                   CASE
                      WHEN DBMS_RANDOM.VALUE (1, 100) < 76
                         THEN NULL
                      ELSE DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 6)),
                                   1, 'S',
                                   2, 'C',
                                   3, 'C',
                                   4, 'C',
                                   5, 'C',
                                   NULL
                                  )
                   END,

                   -- Acc Opening Date
                   SYSDATE - DBMS_RANDOM.VALUE (1, 500),
                   -- Acc Mod Date
                   SYSDATE,

                   -- Account Manager ID
                   DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 11)),
                           1, 1,
                           2, 1,
                           3, 1,
                           4, 1,
                           5, 2,
                           6, 3,
                           7, 4,
                           8, 5,
                           9, 5,
                           10, 5,
                           0
                          ));
   END LOOP;

   COMMIT;
END;
/