8.
Programs development using creation of procedures, passing parameters IN and
OUT of PROCEDURES.
PL/SQL procedure syntax
A PL/SQL procedure is a reusable unit that encapsulates the specific business logic of the
application. Technically speaking, a PL/SQL procedure is a named block stored as a schema
object in the Oracle Database.
The following illustrates the basic syntax of creating a procedure in PL/SQL:
CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)
IS
[declaration statements]
BEGIN
[execution statements]
EXCEPTION
[exception handler]
END [procedure_name ];
PL/SQL procedure header
A procedure begins with a header that specifies its name and an optional parameter list.
Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies
whether a parameter can be read from or written to.
IN
An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you
cannot change its value. Oracle uses IN as the default mode. It means that if you don’t
specify the mode for a parameter explicitly, Oracle will use the IN mode.
OUT
An OUT parameter is writable. Typically, you set a returned value for the OUT parameter
and return it to the calling program. Note that a procedure ignores the value that you supply
for an OUT parameter.
INOUT
An INOUT parameter is both readable and writable. The procedure can be read and modified.
Note that OR REPLACE option allows you to overwrite the current procedure with the new
code.
PL/SQL procedure body
Similar to an anonymous block, the procedure body has three parts. The executable part is
mandatory whereas the declarative and exception-handling parts are optional. The executable
part must contain at least one executable statement.
1) Declarative part
In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a
declaration part of a procedure does not start with the DECLARE keyword.
2) Executable part
This part contains one or more statements that implement specific business logic. It might
contain only a NULL statement.
3) Exception-handling part
This part contains the code that handles exceptions.
Table creation and insertion
CREATE TABLE Student1 (
rollno NUMBER PRIMARY KEY,
name VARCHAR2(50),
marks NUMBER
);
INSERT INTO Student1 VALUES (101, 'Alice', 85);
INSERT INTO Student1 VALUES (102, 'Bob', 78);
INSERT INTO Student1 VALUES (103, 'Charlie', 90);
COMMIT; -- Save the changes
Creating a Procedure with IN and OUT Parameters
Create a procedure to fetch a student's name based on their roll number.
-- Creating the procedure
CREATE OR REPLACE PROCEDURE Get_Student_Name (
p_rollno IN NUMBER, -- Input parameter (Student Roll No)
p_name OUT VARCHAR2 -- Output parameter (Student Name)
IS
BEGIN
-- Fetch the student name based on roll number
SELECT name INTO p_name FROM Student1 WHERE rollno = p_rollno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_name := 'Not Found'; -- Handling exception if roll number does not exist
END Get_Student_Name;
Executing the Procedure with IN and OUT Parameters
Since PL/SQL procedures cannot be called directly in SQL statements, we use an
anonymous PL/SQL block to call them.
DECLARE
v_rollno NUMBER := 101; -- Input value
v_name VARCHAR2(50); -- Output variable
BEGIN
-- Calling the procedure
Get_Student_Name(v_rollno, v_name);
-- Displaying the fetched name
DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_name);
END;
Procedure with IN OUT Parameter
Scenario:
Create a procedure that increments a student's marks and returns the updated value.
-- Creating the procedure
CREATE OR REPLACE PROCEDURE Update_Marks (
p_rollno IN NUMBER, -- Input parameter
p_marks IN OUT NUMBER -- Input-Output parameter (Marks)
IS
BEGIN
-- Updating marks by adding 5
p_marks := p_marks + 5;
-- Updating the Student table
UPDATE Student1
SET marks = p_marks
WHERE rollno = p_rollno;
COMMIT; -- Save changes
END Update_Marks;
Executing the IN OUT Procedure
DECLARE
v_rollno NUMBER := 101;
v_marks NUMBER := 50; -- Initial marks
BEGIN
-- Calling the procedure
Update_Marks(v_rollno, v_marks);
-- Displaying the updated marks
DBMS_OUTPUT.PUT_LINE('Updated Marks: ' || v_marks);
END;
DECLARE
v_rollno NUMBER := 101; -- Input roll number
v_marks NUMBER; -- Variable to store marks from the table
BEGIN
-- Fetch existing marks from the table
SELECT NVL(MARKS, 0) INTO v_marks FROM Student1 WHERE ROLLNO =
v_rollno;
-- Calling the procedure
Update_Marks(v_rollno, v_marks);
-- Displaying the updated marks
DBMS_OUTPUT.PUT_LINE('Updated Marks: ' || v_marks);
END;
