Basic Query in PL/SQL procedure

In this article, we will discuss the overview of PL/SQL and then our main focus on performing basic query operations in PL/SQL. And finally will conclude with Parameter modes in PL/ SQL subprograms. Let’s discuss it one by one.

Introduction :
PL/SQL Stands for procedural language extension to SQL. In a procedure, the role of the subprogram is to perform a particular task and it is a unit module of a program. It combined to form larger programs. A subprogram can be involved by another program which is called the calling program. PL/SQL provides a block structure of executable unit code. It Provides procedural constructs, for example, in control structure includes loops, conditional statement and variable, constant, and data type. 

Features :
It can be created at the schema level, inside a package, and inside a PL /SQL block. 

1. The schema level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE statement. In the subprogram, it stores in the database and can be deleted with the DROP PROCEDURE statement. 
2. It stores in the database and the package is deleted with the DROP PACKAGE statement. 
3. PL/SQL provides to kind of subprogram function and procedure.

Function and procedure in PL /SQL :
Let’s understand the meaning of function and procedure in PL/SQL as follows.

1. Function –
   It is written in a single value. It used to compute and return a value. 
   
2. Procedure – 
   It mainly used to perform an action. 

Creating procedure in PL/SQL :
Here, we will discuss, how you can create the procedure using PL/SQL query as follows. 

Syntax –

CREATE [ OR REPLACE ] PROCEDURE 
procedure_name 
[( Parameter [ parameter ] ) ] 
IS 
[ declaration_section ] 
BEGIN 
executable_section 
[ EXCEPTION 
exception_section] 
END [ procedure_name]; 

Removing procedure in PL/SQL :
Once, we create a procedure in Oracle we need to remove it from the database by using the DROP command as follows. 

Syntax –

DROP PROCEDURE procedure_name; 

Example –

DROP PROCEDURE Update course; 

Parameter modes in PL/ SQL subprograms :
Here, we will discuss the Parameter modes in PL/ SQL subprograms as follows.

1. IN – 
   It is read read-only a parameter. IN parameter act like a constant. And within called program or function, It can be referenced. The program cannot assign a new value to the IN parameter. Their value cannot be changed inside the subprogram. 
   
2. OUT – 
   It is used for getting output from the subprograms. It is a read-write variable inside the subprograms. Their value can be changed inside the subprogram. 
   
3. IN OUT – 
   To get the input and output from the subprogram then this IN-OUT can be used for getting the results. Their values can be changed inside the subprograms. 

My Personal Notes arrow_drop_up

Save