CJ_script

View All Public Scripts Login to Run Script

Script Name CJ_script
Visibility Unlisted - anyone with the share link can access
Description need calendar of activities for employees
Contributor gordan4444@gmail.com
Created Tuesday February 20, 2018

Statement 1

create table CJ_CAL 
  ( 
    cal_id number not null, 
    cal_date date, 
    constraint cj_calendar_pk primary key (cal_id) 
  )

Statement 2
```
commit
```

Statement 3

create sequence cj_cal_seq 
  minvalue 1 
  maxvalue 999999999 
  start with 1 
  increment by 1 
  nocycle

Statement 4
```
  commit
```
Statement 5
```
  / 
```

Statement 6

create table CJ_EMP 
  ( 
    emp_id number not null, 
    first_name varchar2(40), 
    last_name varchar2(40), 
    department varchar2(10), 
    station varchar2(10), 
    manager varchar2(40), 
    work_hours number, 
    schedule varchar2(400), 
    constraint cj_emp_pk primary key (emp_id) 
  )

Statement 7
```
  commit
```
Statement 8
```
  / 
```

Statement 9

create sequence cj_emp_seq 
  minvalue 1 
  maxvalue 999999999 
  start with 1 
  increment by 1 
  nocycle

Statement 10
```
  commit
```
Statement 11
```
  / 
```

Statement 12

create table CJ_STATUS 
  ( 
    stat_id number not null, 
    stat_name varchar2(10), 
    constraint cj_status_pk primary key (stat_id) 
  )

Statement 13
```
  commit
```
Statement 14
```
  / 
```

Statement 15

create table CJ_EMP_CAL 
  ( 
    emp_cal_id number not null, 
    cal_id number not null, 
    emp_id number not null, 
    status number, 
    overtime number, 
    remark varchar2(400), 
    constraint cj_emp_cal_pk primary key (emp_cal_id) 
  )

Statement 16
```
  commit
```
Statement 17
```
  / 
```

Statement 18

  alter table  cj_emp_cal 
  add constraint fk_cj_cal foreign key (cal_id) 
  references  cj_cal (cal_id)

Statement 19
```
  commit
```
Statement 20
```
  / 
```

Statement 21

  alter table  cj_emp_cal 
  add constraint fk_cj_emp foreign key (emp_id) 
  references  cj_emp (emp_id)

Statement 22
```
  commit
```
Statement 23
```
  / 
```

Statement 24

  alter table  cj_emp_cal 
  add constraint fk_cj_status foreign key (status) 
  references  cj_status (stat_id)

Statement 25
```
  commit
```
Statement 26
```
  / 
```

Statement 27

create sequence cj_emp_cal_seq 
  minvalue 1 
  maxvalue 999999999 
  start with 1 
  increment by 1 
  nocycle

Statement 28
```
  commit
```
Statement 29
```
  / 
```

Statement 30

declare 
v_date date := sysdate-60; 
 
begin 
 
  for i in 1..120 loop 
 
    insert into cj_cal 
    values(cj_cal_seq.nextval, v_date); 
    v_date := v_date + 1; 
 
  end loop; 
  commit; 
 
end;

Statement 31

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Brandon', 'Hand', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 32
```
  commit
```
Statement 33
```
  / 
```

Statement 34

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Nicholas', 'Bulthuis', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 35
```
  commit
```
Statement 36
```
  / 
```

Statement 37

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Brian', 'Setchell', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 38
```
  commit
```
Statement 39
```
  / 
```

Statement 40

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Ramon', 'Espada', 
    'GSE', 'YVR', 'Cabral-Kevin')

Statement 41
```
  commit
```
Statement 42
```
  / 
```

Statement 43

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Roberto', 'Santiago', 
    'GSE', 'YVR', 'Cabral-Kevin')

Statement 44
```
  commit
```
Statement 45
```
  / 
```

Statement 46

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Alex', 'Gillespie', 
    'GSE', 'YYC', 'Cabral-Kevin')

Statement 47
```
  commit
```
Statement 48
```
  / 
```

Statement 49

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Ralph', 'Westerterp', 
    'GSE', 'YEG', 'Cabral-Kevin')

Statement 50
```
  commit
```
Statement 51
```
  / 
```

Statement 52

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Bryan', 'Franklin', 
    'GSE', 'YWG', 'Cabral-Kevin')

Statement 53
```
  commit
```
Statement 54
```
  / 
```

Statement 55

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Corey', 'Perry', 
    'GSE', 'YQM', 'Cabral-Kevin')

Statement 56
```
  commit
```
Statement 57
```
  / 
```

Statement 58

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Gingrich', 'Matt', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 59
```
  commit
```
Statement 60
```
  / 
```

Statement 61

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Irvine', 'Jodilynn', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 62
```
  commit
```
Statement 63
```
  / 
```

Statement 64

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Gifford', 'Mackenzie', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 65
```
  commit
```
Statement 66
```
  / 
```

Statement 67

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Kavanaugh', 'Brent', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 68
```
  commit
```
Statement 69
```
  / 
```

Statement 70

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Downey', 'Robert', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 71
```
  commit
```
Statement 72
```
  / 
```

Statement 73

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Armstrong', 'Ross', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 74
```
  commit
```
Statement 75
```
  / 
```

Statement 76

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Gianni', 'Smerilli', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 77
```
  commit
```
Statement 78
```
  / 
```

Statement 79

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Deschamps', 'Kevin', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 80
```
  commit
```
Statement 81
```
  / 
```

Statement 82

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Joshua', 'Beam', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 83
```
  commit
```
Statement 84
```
  / 
```

Statement 85

  insert into cj_emp(emp_id, first_name, last_name, 
    department, station, manager) 
  values(cj_emp_seq.nextval, 'Daniel', 'Linger', 
    'GSE', 'YHM', 'Cabral-Kevin')

Statement 86
```
  commit
```
Statement 87
```
  / 
```

Statement 88

insert into cj_status 
values(1,'PRESENT')

Statement 89
```
commit
```

Statement 90

insert into cj_status 
values(2,'ABSENT')

Statement 91
```
commit
```

Statement 92

insert into cj_status 
values(3,'OVERTIME')

Statement 93
```
commit
```

Statement 94

insert into cj_status 
values(4,'VACATION')

Statement 95
```
commit
```

Statement 96

declare 
v_status number; 
v_overtime number; 
 
begin 
 
  for i in (select cal_id, cal_date from cj_cal) 
  loop 
    for j in (select emp_id from cj_emp) 
    loop 
 
      if trunc(i.cal_date) < trunc(sysdate) then 
        v_status := round(dbms_random.value(1,4)); 
 
        if v_status = 3 then --if overtime 
          -- random overtime hours 
          v_overtime := round(dbms_random.value(1,3)); 
        end if; 
 
        insert into cj_emp_cal(emp_cal_id, cal_id, emp_id, status, overtime) 
        values(cj_emp_cal_seq.nextval, i.cal_id, j.emp_id, v_status, v_overtime); 
        v_overtime := null; 
 
      else 
 
        insert into cj_emp_cal(emp_cal_id, cal_id, emp_id) 
        values(cj_emp_cal_seq.nextval, i.cal_id, j.emp_id); 
 
      end if; 
 
 
    end loop; 
  end loop; 
  commit; 
 
end;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database