Back to: Oracle Tutorials for Beginners and Professionals
Boolean Parameters in Oracle Stored Procedure
In this article, I am going to explain how to use Boolean Parameters and PL/SQL records in Oracle Stored Procedure with Examples. Please read our previous article where we discussed Exception Handing in Oracle Stored Procedure with Examples.
Boolean Parameters in Oracle Stored Procedure
Let us try to create a Stored Procedure with Boolean Parameter by executing the following SQL Statements.
CREATE OR REPLACE PROCEDURE P ( X BOOLEAN ) IS BEGIN IF X THEN DBMS_OUTPUT.PUT_LINE ('X is true'); END IF; END;
From the above procedure statement, you can see that the procedure name is P with X as a parameter which is of type Boolean. The Boolean may be true or false. The main logic of the procedure is if the Boolean is true then print “X is true”. Let us try to execute this procedure.
So, the procedure is successfully compiled. Let us try to test this procedure by calling this procedure from an anonymous block by executing the below statements.
Declare V Boolean; V:=true; P(V); End;
So, from the above statements, you can see that we have declared a bind variable v which is Boolean. We have then assigned the value true to the Boolean variable of v. P(v) is calling the function. Let us try to run this block.
The procedure is successfully executed.
PL/SQL in the Parameter Block of a Stored Procedure
Let us try to learn how to use the PL/SQL in the parameter block of a stored procedure in oracle. So, create the procedure by executing the below statements.
CREATE OR REPLACE PROCEDURE TEST_PLSQL_RECORDS ( REC IN DEPARTMENTS%ROWTYPE ) IS BEGIN INSERT INTO DEPARTMENTS VALUES REC; END;
From the above statement, you can see that the procedure name is TEST_PLSQL_RECORDS. The Parameter is REC where DEPARTMENTS%ROWTYPE takes the PL/SQL record. The main logic of the procedure is inserted into the table DEPARTMENTS where the values are from the parameter of the procedure. This concept is very important in the future if we work in collection and data migration. Let us go ahead and compile this procedure.
So, the procedure is compiled. Let us try to test this procedure.
DECLARE V DEPARTMENTS%ROWTYPE; BEGIN V.DEPARTMENT_ID:=3; V.DEPARTMENT_NAME:='V DEPT'; TEST_PLSQL_RECORDS (V); END;
From the above statement, we have declared a variable DEPARTMENTS%rowtype. From the DEPARTMENTS table, we can see the parameters take the row detail.
So, using these column names we will be inserting the data into the table Departments. We are inserting the records as DEPARTMENT_ID as 3 and DEPARTMENT_NAME as v dept. In the end, we will be calling the procedure with the parameter v. Let us try to execute this PL/SQL block.
The procedure block is executed successfully. As we have executed this on the table DEPARTMENTS. Let us try to check the table departments and see if the record is inserted or not by executing the below select statements.
SELECT * FROM DEPARTMENT;
You can see in the end the table has the record 3 as department_id and v dept as the department. This helps us to understand them a lot better about the procedures.
Note: We can use Boolean and PL/SQL blocks and we can also use collections like arrays in the parameters of the procedures.
In the next article, I am going to discuss User Defined Functions in Oracle with Examples. Here, in this article, I try to explain How to Create Boolean Parameters in Oracle Stored Procedure with Examples. I hope you enjoy this Boolean Parameter in the Oracle Stored Procedure article.