Call a stored procedure in dynamic script
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, 'Music Building', 200, 1000, 'Concert Room');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E');
1 row created.
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, 'Mar', 'Wells','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, 'Man', 'Kyte','Economics', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, 'Pat', 'Poll','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, 'Tim', 'Viper','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, 'Barbara', 'Blues','Economics', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, 'David', 'Large','Music', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, 'Rose', 'Bond','Music', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE printLecturer
2 (v_Numlecturer IN OUT NUMBER) AS
3 v_LocalCount NUMBER := 0;
4 myLecturerName VARCHAR2(100);
5 CURSOR c_StudentNames IS
6 SELECT first_name || ' ' || last_name
7 FROM lecturer
8 ORDER BY ID;
9 BEGIN
10 OPEN c_StudentNames;
11 FOR v_Count IN 1..v_Numlecturer LOOP
12 FETCH c_StudentNames INTO myLecturerName;
13 IF c_StudentNames%NOTFOUND THEN
14 EXIT;
15 END IF;
16
17 DBMS_OUTPUT.PUT_LINE(' ' || myLecturername);
18 v_LocalCount := v_LocalCount + 1;
19 END LOOP;
20
21 CLOSE c_StudentNames;
22 v_Numlecturer := v_LocalCount;
23 END printLecturer;
24 /
Procedure created.
SQL>
SQL> DECLARE
2 codeBlock VARCHAR2(1000);
3
4 v_Building place.building%TYPE;
5 v_RoomNum place.room_number%TYPE;
6 v_RoomID place.room_ID%TYPE := 20006;
7 v_Numlecturer NUMBER;
8 BEGIN
9 EXECUTE IMMEDIATE
10 'DELETE FROM place WHERE room_id = :ID RETURNING building, room_number INTO :building, :room_num'
11 USING v_RoomID, OUT v_Building, OUT v_RoomNum;
12
13 DBMS_OUTPUT.PUT_LINE('Deleted Room ' || v_RoomNum || ' in ' || v_Building);
14
15 codeBlock := 'BEGIN printLecturer(:num); END;';
16
17 v_Numlecturer := 4;
18 EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
19 DBMS_OUTPUT.PUT_LINE('Returned value is ' || v_Numlecturer);
20
21 v_Numlecturer := 20;
22 EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
23 DBMS_OUTPUT.PUT_LINE('Returned value is ' || v_Numlecturer);
24 END;
25 /
Deleted Room 100 in Music Building
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Returned value is 4
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Pat Poll
Tim Viper
Barbara Blues
David Large
Chris Elegant
Rose Bond
Rita Johnson
Sharon Clear
Returned value is 12
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
SQL>
SQL>