Oracle PL/SQL/Stored Procedure Function/Function Call
Содержание
- 1 Call function and store the return value to a variable
- 2 Call function in dbms_output.put_line
- 3 Calling a Function
- 4 Function with insert statement can be called from a DML statement.
- 5 Local Subprograms
- 6 This function can be called from a SQL statement.
- 7 This function cannot be called from a SQL statement: cannot perform a DML operation inside a query
- 8 Use a user-defined function in stored procedure
Call function and store the return value to a variable
<source lang="sql">
SQL> SQL> SQL> create or replace function first_function return varchar2 as
2 begin 3 return "Hello World"; 4 end first_function; 5 /
Function created. SQL> SQL> SQL> SQL> set serverout on SQL> SQL> declare
2 l_str varchar2(100) := null; 3 begin 4 l_str := first_function; 5 dbms_output.put_line( l_str ); 6 end; 7 /
Hello World PL/SQL procedure successfully completed. SQL>
</source>
Call function in dbms_output.put_line
<source lang="sql">
SQL> SQL> SQL> create or replace function ite(
2 p_expression boolean, 3 p_true varchar2, 4 p_false varchar2 ) return varchar2 as 5 begin 6 if p_expression then 7 return p_true; 8 end if; 9 return p_false; 10 end ite; 11 /
Function created. SQL> SQL> SQL> exec dbms_output.put_line( ite( 1=2, "Equal", "Not Equal" ) ); Not Equal PL/SQL procedure successfully completed. SQL> SQL> exec dbms_output.put_line( ite( 2>3, "True", "False" ) ); False PL/SQL procedure successfully completed. SQL>
</source>
Calling a Function
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION AlmostFull (
2 p_Department session.department%TYPE, 3 p_Course session.course%TYPE) 4 RETURN BOOLEAN IS 5 6 studentCount NUMBER; 7 studentMax NUMBER; 8 v_ReturnValue BOOLEAN; 9 v_FullPercent CONSTANT NUMBER := 90; 10 BEGIN 11 SELECT current_lecturer, max_lecturer 12 INTO studentCount, studentMax 13 FROM session 14 WHERE department = p_Department 15 AND course = p_Course; 16 17 IF (studentCount / studentMax * 100) > v_FullPercent THEN 18 v_ReturnValue := TRUE; 19 ELSE 20 v_ReturnValue := FALSE; 21 END IF; 22 23 RETURN v_ReturnValue; 24 END AlmostFull; 25 /
Function created. SQL> SQL> DECLARE
2 CURSOR c_session IS 3 SELECT department, course 4 FROM session; 5 BEGIN 6 FOR v_ClassRecord IN c_session LOOP 7 IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN 8 INSERT INTO MyTable (char_col) VALUES 9 (v_ClassRecord.department || " " || v_ClassRecord.course || 10 " is almost full!"); 11 END IF; 12 END LOOP; 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable; no rows selected SQL> SQL> drop table session; Table dropped. SQL> SQL> drop table MyTable; Table dropped.
</source>
Function with insert statement can be called from a DML statement.
<source lang="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 TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> CREATE OR REPLACE FUNCTION UpdateTemp(p_ID IN lecturer.ID%TYPE)
2 RETURN lecturer.ID%TYPE AS 3 BEGIN 4 INSERT INTO MyTable (num_col, char_col) 5 VALUES(p_ID, "Updated!"); 6 RETURN p_ID; 7 END UpdateTemp; 8 /
Function created. SQL> SQL> UPDATE lecturer
2 SET major = "Nutrition" 3 WHERE UpdateTemp(ID) = ID;
12 rows updated. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
Local Subprograms
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table 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> DECLARE
2 CURSOR myAllLecturer IS 3 SELECT first_name, last_name 4 FROM lecturer; 5 6 v_FormattedName VARCHAR2(50); 7 8 FUNCTION FormatName(p_FirstName IN VARCHAR2, 9 p_LastName IN VARCHAR2) 10 RETURN VARCHAR2 IS 11 BEGIN 12 RETURN p_FirstName || " " || p_LastName; 13 END FormatName; 14 15 BEGIN 16 FOR myLecturerRecord IN myAllLecturer LOOP 17 v_FormattedName := 18 FormatName(myLecturerRecord.first_name, 19 myLecturerRecord.last_name); 20 INSERT INTO MyTable (char_col) 21 VALUES (v_FormattedName); 22 END LOOP; 23 24 COMMIT; 25 END; 26 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Pat Poll
Tim Viper
Barbara Blues
David Large
Chris Elegant
Rose Bond
Rita Johnson
NUM_COL CHAR_COL
------------------------------------------------------------
Sharon Clear
12 rows selected. SQL> SQL> drop table lecturer; Table dropped. SQL> drop table MyTable; Table dropped.
</source>
This function can be called from a SQL statement.
<source lang="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> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION FullName (
2 p_ID lecturer.ID%TYPE) 3 RETURN VARCHAR2 IS 4 5 myResult VARCHAR2(100); 6 BEGIN 7 SELECT first_name || " " || last_name 8 INTO myResult 9 FROM lecturer 10 WHERE ID = p_ID; 11 12 RETURN myResult; 13 END FullName; 14 /
Function created. SQL> SQL> SELECT ID, FullName(ID) "Full Name"
2 FROM lecturer;
ID
Full Name
10001
Scott Lawson
10002
Mar Wells
10003
Jone Bliss
ID
Full Name
10004
Man Kyte
10005
Pat Poll
10006
Tim Viper
ID
Full Name
10007
Barbara Blues
10008
David Large
10009
Chris Elegant
ID
Full Name
10010
Rose Bond
10011
Rita Johnson
10012
Sharon Clear
12 rows selected. SQL> SQL> INSERT INTO MyTable (char_col)
2 VALUES (FullName(10010));
1 row created. SQL> SQL> drop table MyTable; Table dropped. SQL> drop table lecturer; Table dropped.
</source>
This function cannot be called from a SQL statement: cannot perform a DML operation inside a query
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION InsertTemp(
2 p_Num IN MyTable.num_col%TYPE, 3 p_Char IN MyTable.char_col%type) 4 RETURN NUMBER AS 5 BEGIN 6 INSERT INTO MyTable (num_col, char_col) 7 VALUES (p_Num, p_Char); 8 RETURN 0; 9 END InsertTemp; 10 /
Function created. SQL> SQL> REM Illegal query SQL> SELECT InsertTemp(1, "Hello")
2 FROM dual;
SELECT InsertTemp(1, "Hello")
*
ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "sqle.INSERTTEMP", line 6
SQL> SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Use a user-defined function in stored procedure
<source lang="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> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5));
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION CountCredits (
2 p_ID IN lecturer.ID%TYPE) 3 RETURN NUMBER AS 4 5 v_TotalCredits NUMBER; -- Total number of credits 6 v_CourseCredits NUMBER; -- Credits for one course 7 CURSOR c_RegisteredCourses IS 8 SELECT department, course 9 FROM myStudent 10 WHERE student_id = p_ID; 11 BEGIN 12 FOR v_CourseRec IN c_RegisteredCourses LOOP 13 SELECT num_credits 14 INTO v_CourseCredits 15 FROM session 16 WHERE department = v_CourseRec.department 17 AND course = v_CourseRec.course; 18 19 v_TotalCredits := v_TotalCredits + v_CourseCredits; 20 END LOOP; 21 22 RETURN v_TotalCredits; 23 END CountCredits; 24 /
Function created. SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE CreditLoop AS
2 myLecturerID lecturer.ID%TYPE; 3 v_Credits lecturer.current_credits%TYPE; 4 CURSOR c_lecturer IS 5 SELECT ID 6 FROM lecturer; 7 BEGIN 8 OPEN c_lecturer; 9 LOOP 10 FETCH c_lecturer INTO myLecturerID; 11 v_Credits := CountCredits(myLecturerID); 12 INSERT INTO MyTable (num_col, char_col) 13 VALUES (myLecturerID, "Credits = " || TO_CHAR(v_Credits)); 14 EXIT WHEN c_lecturer%NOTFOUND; 15 END LOOP; 16 CLOSE c_lecturer; 17 END CreditLoop; 18 /
Procedure created. SQL> SQL> drop table MyTable; Table dropped. SQL> drop table session; Table dropped. SQL> drop table myStudent; Table dropped. SQL> drop table lecturer; Table dropped. SQL>
</source>
