Oracle PL/SQL/Stored Procedure Function/Function Call — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
Call function in dbms_output.put_line
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>
Calling a Function
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.
Function with insert statement can be called from a DML statement.
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>
Local Subprograms
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.
This function can be called from a SQL statement.
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.
This function cannot be called from a SQL statement: cannot perform a DML operation inside a query
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>
Use a user-defined function in stored procedure
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>