Mega Code Archive
Insert values using PLSQL literals and variables
<![CDATA[
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date,
10 gender_id number);
Table created.
SQL>
SQL> create table gender (
2 id number not null,
3 code varchar2(30) not null,
4 description varchar2(80) not null,
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, 'M', 'Male' );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' );
1 row created.
SQL>
SQL> create table employee_type (
2 id number not null,
3 code varchar2(30) not null,
4 description varchar2(80) not null,
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,'C','Contractor' );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,'E','Employee' );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,'U','Unknown' );
1 row created.
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2 n_id employee.id%TYPE;
3 n_employee_type_id employee.employee_type_id%TYPE;
4 v_external_id employee.external_id%TYPE;
5 n_gender_id employee.gender_id%TYPE;
6 n_count number;
7 begin
8 begin
9 select id into n_employee_type_id from employee_type where code = 'C';
10 exception
11 when OTHERS then
12 raise_application_error(-20002, SQLERRM||' on select employee_type'||' in filename insert.sql');
13 end;
14 begin
15 select id into n_gender_id from gender where code = 'M';
16 exception
17 when OTHERS then
18 raise_application_error(-20004, SQLERRM||' on select gender'||' in filename insert.sql');
19 end;
20
21 begin
22 select 12 into n_id from SYS.DUAL;
23 exception
24 when OTHERS then
25 raise_application_error(-20001, SQLERRM||' on select 12'||' in filename insert.sql');
26 end;
27
28 begin
29 select lpad(to_char('12'), 9, '0') into v_external_id from SYS.DUAL;
30 exception
31 when OTHERS then
32 raise_application_error(-20003, SQLERRM||' on select 12'||' in filename insert.sql');
33 end;
34
35 begin
36 insert into employee (
37 id,
38 employee_type_id,
39 external_id,
40 first_name,
41 middle_name,
42 last_name,
43 name,
44 birth_date,
45 gender_id )
46 values (
47 n_id,
48 n_employee_type_id,
49 v_external_id,
50 'JOHN',
51 'J.',
52 'DOE',
53 'AAA J.',
54 to_date('19800101', 'YYYYMMDD'),
55 n_gender_id );
56
57 n_count := sql%rowcount;
58 exception
59 when OTHERS then
60 raise_application_error(-20005, SQLERRM||' on insert employee'||' in filename insert.sql');
61 end;
62
63 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||' row(s) inserted.');
64 end;
65 /
1 row(s) inserted.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>
]]>