Column type parameters : TYPE « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. TYPE

SQL>
SQL> CREATE TABLE employee_locker (
  2    emp_id        NUMBER        NOT NULL  PRIMARY KEY,
  3    name          VARCHAR2(30)  NOT NULL,
  4    room_number   VARCHAR2(30)  NOT NULL,
  5    occupied_dt   DATE,
  6    checkout_date DATE );

Table created.

SQL>
SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt, checkout_date )VALUES( 1, 'Java', '10A', TRUNC(SYSDATE), TRUNC(SYSDATE) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 2, 'SQL', '12A', TRUNC(SYSDATE) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt, checkout_date )VALUES( 3, 'Oracle', '12B', TRUNC(SYSDATE), TRUNC(SYSDATE) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 4, 'PC', '10A', TRUNC(SYSDATE+1) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 5, 'JavaScript', '12A', TRUNC(SYSDATE+1) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 6, 'C', '12B', TRUNC(SYSDATE+1) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 7, 'C++', '10A', TRUNC(SYSDATE+2) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 8, 'Python', '12A', TRUNC(SYSDATE+2) );

1 row created.

SQL> INSERT INTO employee_locker( emp_id, name, room_number, occupied_dt )VALUES( 9, 'C#', '12B', TRUNC(SYSDATE+2) );

1 row created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE update_bill(
  2    emp_id_in  employee_locker.emp_id%TYPE,
  3    room_number_in  employee_locker.room_number%TYPE )
  4  IS
  5  BEGIN
  6    dbms_output.put_line('bill updated for emp_id = '||emp_id_in||', room_number = '||room_number_in);
  7  END update_bill;
  8  /

Procedure created.

SQL>
SQL> set serveroutput on size 500000
SQL>
SQL> DECLARE
  2     CURSOR employee_locker_cur IS
  3        SELECT emp_id, room_number
  4        FROM employee_locker WHERE occupied_dt = TRUNC(SYSDATE);
  5     employee_locker_rec employee_locker_cur%ROWTYPE;
  6  BEGIN
  7     OPEN employee_locker_cur;
  8     LOOP
  9        FETCH employee_locker_cur INTO employee_locker_rec;
 10        EXIT WHEN employee_locker_cur%NOTFOUND;
 11        update_bill (employee_locker_rec.emp_id, employee_locker_rec.room_number);
 12     END LOOP;
 13     CLOSE employee_locker_cur;
 14  END;
 15  /
bill updated for emp_id = 1, room_number = 10A
bill updated for emp_id = 2, room_number = 12A
bill updated for emp_id = 3, room_number = 12B

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> DROP TABLE employee_locker;

Table dropped.

SQL>