rowtype index by binary_integer : rowtype « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. rowtype

rowtype index by binary_integer

  
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6));

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'YourName@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Dillon', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> set serverout on
SQL> declare
  2    type myTextTableType is table of varchar2(200)
  3      index by binary_integer;
  4
  5    type my_emp_table_type is table of employees%rowtype
  6      index by binary_integer;
  7
  8    myText_table myTextTableType;
  9    myEmpTable my_emp_table_type;
 10  begin
 11    myText_table(1) := 'S';
 12    myText_table(2) := 'A';
 13
 14    myEmpTable(10).employee_id := 10;
 15    myEmpTable(10).last_name   := 'D';
 16    myEmpTable(10).email       := 's@q.com';
 17    myEmpTable(10).hire_date   := to_date('01-NOV-1996');
 18    myEmpTable(10).job_id      := 'CLERK';
 19
 20    myEmpTable(20).employee_id := 20;
 21    myEmpTable(20).last_name   := 'B';
 22    myEmpTable(20).email       := 'c@q.com';
 23    myEmpTable(20).hire_date   := to_date('01-JAN-1996');
 24    myEmpTable(20).job_id      := 'CLERK';
 25
 26    dbms_output.put     ('We have ' ||myText_table.count|| ' varchar2''s ');
 27    dbms_output.put_line('and ' ||myEmpTable.count|| ' employees.');
 28    dbms_output.put_line('-');
 29    dbms_output.put_line('vc2(1)='||myText_table(1));
 30    dbms_output.put_line('vc2(2)='||myText_table(2));
 31    dbms_output.put_line('-');
 32    dbms_output.put_line('myEmpTable(10)='||myEmpTable(10).last_name);
 33    dbms_output.put_line('myEmpTable(20)='||myEmpTable(20).last_name);
 34
 35  end;
 36  /
We have 2 varchar2's and 2 employees.
-
vc2(1)=S
vc2(2)=A
-
myEmpTable(10)=D
myEmpTable(20)=B

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL>

   
  

Related examples in the same category