pkg_load_data

Package Head

1
2
3
4
5
6
7
CREATE OR REPLACE PACKAGE LoadData
AS
    -- ##############################################################
    -- # PL/SQL package specification
    -- ##############################################################
    PROCEDURE LoadData;
END LoadData;

Package Body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE OR REPLACE PACKAGE BODY LoadData
AS
    -- ###################################################################################
    -- # PL/SQL package body
    -- ###################################################################################
    PROCEDURE LoadData
    AS
    nextIDPassanger integer;
    nextIDReceivedPassanger integer;
    nextPassIDReceived bigint;
     
    BEGIN
     
    if(((select count(*) from received) = 0) and (select count(*) from received_passanger)=0)
    then
    nextIDPassanger := 0;
    nextIDReceivedPassanger := 0;
    nextPassIDReceived := 0;
     
    ELSE
    nextIDPassanger := (select max(received_id) from received);
    nextIDReceivedPassanger := (select max(received_id) from received_passanger);
    nextPassIDReceived := (select max(received_passenger_id) from received_passanger);
     
    end if;
     
    for i in 1 .. 5000 LOOP
    nextIDPassanger := nextIDPassanger + 1;
    nextIDReceivedPassanger := nextIDReceivedPassanger + 1;
    nextPassIDReceived := nextPassIDReceived + 1;
     
    insert into received(received_id,received_dt,inter_number,message_type) values (nextIDPassanger,(select current timestamp(8) from SYSIBM.SYSDUMMY1),(select SMALLINT(RAND() * 10000+1) from sysibm.sysdummy1),2);
    insert into received_passanger(received_passenger_id,carrier_code,flight_number,received_id) values(nextPassIDReceived,(select SMALLINT(RAND() * 10000+1) from sysibm.sysdummy1),(select SMALLINT(RAND() * 10000+1) from sysibm.sysdummy1),nextIDReceivedPassanger);
     
    if(mod(i,1000)=0) THEN
        commit work;
    end if;
     
     
    end loop;
 
 
 
 
    END LoadData;
 
END LoadData;

  • pkg_load_data.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1