Oracle PL/SQL/Stored Procedure Function/Procedure Call
Содержание
- 1 A PL/SQL procedure with no parameter
- 2 Call a stored procedure in a PL/SQL block
- 3 Call a stored procedure then other statements
- 4 Calling ParameterLength illegally (ORA-6502)...
- 5 Default parameter value
- 6 Dependency Example
- 7 Exceptions in Subprograms
- 8 Exception throwed out of procedure call
- 9 Forward Declarations
- 10 Pass parameter by data type
- 11 This script demonstrates how to create and call a simple procedure.
- 12 Use named notation
- 13 Use named notation, but with a different order of the formal parameters
A PL/SQL procedure with no parameter
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE InsertIntoTemp AS
2 v_Num1 NUMBER := 1;
3 v_Num2 NUMBER := 2;
4 v_String1 VARCHAR2(50) := "Hello World!";
5 v_String2 VARCHAR2(50) := "-- This message brought to you by PL/SQL!";
6 v_OutputStr VARCHAR2(50);
7 BEGIN
8 INSERT INTO MyTable (num_col, char_col)
9 VALUES (v_Num1, v_String1);
10 INSERT INTO MyTable (num_col, char_col)
11 VALUES (v_Num2, v_String2);
12
13 SELECT char_col
14 INTO v_OutputStr
15 FROM MyTable
16 WHERE num_col = v_Num1;
17 DBMS_OUTPUT.PUT_LINE(v_OutputStr);
18
19 SELECT char_col
20 INTO v_OutputStr
21 FROM MyTable
22 WHERE num_col = v_Num2;
23 DBMS_OUTPUT.PUT_LINE(v_OutputStr);
24
25 ROLLBACK;
26
27 END InsertIntoTemp;
28 /
Procedure created.
SQL>
SQL> BEGIN
2 InsertIntoTemp;
3 END;
4 /
Hello World!
-- This message brought to you by PL/SQL!
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
Call a stored procedure in a PL/SQL block
SQL>
SQL>
SQL> create or replace procedure my_proc as
2 begin
3 dbms_output.put_line ( "Hello World" );
4 end my_proc;
5 /
Procedure created.
SQL>
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> begin
2 my_proc;
3 end;
4 /
Hello World
PL/SQL procedure successfully completed.
SQL>
Call a stored procedure then other statements
SQL>
SQL> create or replace procedure swap(
2 p_parm1 in out number,
3 p_parm2 in out number ) as
4 l_temp number;
5 begin
6 l_temp := p_parm1;
7 p_parm1 := p_parm2;
8 p_parm2 := l_temp;
9 end swap;
10 /
Procedure created.
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 l_num1 number := 100;
3 l_num2 number := 101;
4 begin
5 swap( l_num1, l_num2 );
6 dbms_output.put_line( "l_num1 = " || l_num1 );
7 dbms_output.put_line( "l_num2 = " || l_num2 );
8 end;
9 /
l_num1 = 101
l_num2 = 100
PL/SQL procedure successfully completed.
SQL>
SQL>
Calling ParameterLength illegally (ORA-6502)...
SQL>
SQL> CREATE OR REPLACE PROCEDURE ParameterLength (
2 p_Parameter1 IN OUT VARCHAR2,
3 p_Parameter2 IN OUT NUMBER) AS
4 BEGIN
5 p_Parameter1 := "abcdefghijklmno";
6 p_Parameter2 := 12.3;
7 END ParameterLength;
8 /
Procedure created.
SQL>
SQL> DECLARE
2 v_Variable1 VARCHAR2(10);
3 v_Variable2 NUMBER(7,3);
4 BEGIN
5 ParameterLength(v_Variable1, v_Variable2);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "sqle.PARAMETERLENGTH", line 5
ORA-06512: at line 5
SQL>
Default parameter value
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE DefaultTest (
2 p_ParameterA NUMBER DEFAULT 10,
3 p_ParameterB VARCHAR2 DEFAULT "abcdef",
4 p_ParameterC DATE DEFAULT SYSDATE) AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 "A: " || p_ParameterA ||
8 " B: " || p_ParameterB ||
9 " C: " || TO_CHAR(p_ParameterC, "DD-MON-YYYY"));
10 END DefaultTest;
11 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 DefaultTest(p_ParameterA => 7, p_ParameterC => "30-DEC-95");
3 END;
4 /
A: 7 B: abcdef C: 30-DEC-1995
PL/SQL procedure successfully completed.
SQL>
Dependency Example
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 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 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 := 80;
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>
SQL>
SQL> CREATE OR REPLACE PROCEDURE markFullSession AS
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 markFullSession;
14 /
Procedure created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>
Exceptions in Subprograms
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>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
2 p_Raise IN BOOLEAN := TRUE,
3 p_ParameterA OUT NUMBER) AS
4 BEGIN
5 p_ParameterA := 7;
6
7 IF p_Raise THEN
8 RAISE DUP_VAL_ON_INDEX;
9 ELSE
10 RETURN;
11 END IF;
12 END RaiseError;
13 /
Procedure created.
SQL> DECLARE
2 v_TempVar NUMBER := 1;
3 BEGIN
4 INSERT INTO MyTable (num_col, char_col)
5 VALUES (v_TempVar, "Initial value");
6 RaiseError(FALSE, v_TempVar);
7
8 INSERT INTO MyTable (num_col, char_col)
9 VALUES (v_TempVar, "Value after successful call");
10
11 v_TempVar := 2;
12 INSERT INTO MyTable (num_col, char_col)
13 VALUES (v_TempVar, "Value before 2nd call");
14 RaiseError(TRUE, v_TempVar);
15 EXCEPTION
16 WHEN OTHERS THEN
17 INSERT INTO MyTable (num_col, char_col)
18 VALUES (v_TempVar, "Value after unsuccessful call");
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
1 Initial value
7 Value after successful call
2 Value before 2nd call
2 Value after unsuccessful call
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Exception throwed out of procedure call
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 AddNewStudent (
2 p_FirstName lecturer.first_name%TYPE,
3 p_LastName lecturer.last_name%TYPE,
4 p_Major lecturer.major%TYPE DEFAULT "Economics") AS
5 BEGIN
6 INSERT INTO lecturer VALUES (20001, p_FirstName, p_LastName, p_Major, 0);
7 END AddNewStudent;
8 /
Procedure created.
SQL>
SQL> BEGIN
2 AddNewStudent("S", "S");
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 AddNewStudent(p_FirstName => "Veronica",
3 p_LastName => "Vassily");
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (sqle.SYS_C006785) violated
ORA-06512: at "sqle.ADDNEWSTUDENT", line 6
ORA-06512: at line 2
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Forward Declarations
SQL> DECLARE
2 v_TempVal BINARY_INTEGER := 5;
3
4 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);
5
6 PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
7 BEGIN
8 IF p_Counter > 0 THEN
9 B(p_Counter);
10 p_Counter := p_Counter - 1;
11 END IF;
12 END A;
13
14 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
15 BEGIN
16 p_Counter := p_Counter - 1;
17 A(p_Counter);
18 END B;
19 BEGIN
20 B(v_TempVal);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Pass parameter by data type
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE DefaultTest (
2 p_ParameterA NUMBER DEFAULT 10,
3 p_ParameterB VARCHAR2 DEFAULT "abcdef",
4 p_ParameterC DATE DEFAULT SYSDATE) AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 "A: " || p_ParameterA ||
8 " B: " || p_ParameterB ||
9 " C: " || TO_CHAR(p_ParameterC, "DD-MON-YYYY"));
10 END DefaultTest;
11 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> BEGIN
2 DefaultTest(7);
3 END;
4 /
A: 7 B: abcdef C: 18-JUN-2008
PL/SQL procedure successfully completed.
SQL>
This script demonstrates how to create and call a simple procedure.
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewAuthor(p_ID emp.ID%TYPE,p_FirstName emp.fname%TYPE,p_LastName emp.lname%TYPE) AS
2 BEGIN
3 INSERT INTO emp (id, fname, lname) VALUES (p_ID, p_FirstName, p_LastName);
4 END AddNewAuthor;
5 /
Procedure created.
SQL>
SQL>
SQL> DECLARE
2
3 v_NewFirstName emp.fname%TYPE := "C";
4 v_NewLastName emp.lname%TYPE := "C";
5 v_NewAuthorID emp.ID%TYPE := 100;
6 BEGIN
7 AddNewAuthor(v_NewAuthorID, v_NewFirstName, v_NewLastName);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Use named notation
SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS
2 BEGIN
3 NULL;
4 END CallMe;
5 /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL> DECLARE
2 v1 VARCHAR2(10);
3 v2 NUMBER(7,6);
4 v3 BOOLEAN;
5 v4 DATE;
6 BEGIN
7 CallMe(pA => v1, pB => v2,pC => v3,pD => v4);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Use named notation, but with a different order of the formal parameters
SQL>
SQL> -- First create a procedure with 4 parameters
SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS
2 BEGIN
3 NULL;
4 END CallMe;
5 /
SP2-0804: Procedure created with compilation warnings
SQL> DECLARE
2 v1 VARCHAR2(10);
3 v2 NUMBER(7,6);
4 v3 BOOLEAN;
5 v4 DATE;
6 BEGIN
7 CallMe(pB => v2, pC => v3,pD => v4,pA => v1);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>