|
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>
|