Basic Query in PL/SQL procedure

PL/SQL (Procedural Language/Structured Query Language) is a powerful extension to SQL, designed to combine the robustness of SQL with procedural constructs like loops, conditions, and more. It plays a crucial role in writing complex database interactions in Oracle databases. This article will cover an overview of PL/SQL, basic query operations, and an in-depth look at parameter modes in PL/SQL subprograms.

What is PL/SQL?

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 statements, and variable, constant, and data type. 

Key Features of PL/SQL

1. It can be created at the schema level, inside a package, and inside a PL /SQL block. 
2. The schema-level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE statement. In the subprogram, it is stored in the database and can be deleted with the DROP PROCEDURE statement. 
3. It is stored in the database and the package is deleted with the DROP PACKAGE statement. 
4. 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:

1. Function:
   • A function is designed to return a single value.
   • They are often used when you need to compute a value and use it elsewhere in your application.
   • Functions must return a value using the 'RETURN' statement.
2. Procedure: 
   • Procedures perform a specific action but are not required to return a value.
   • They are versatile and can include multiple operations such as inserting, updating, or deleting records in a database.

Creating Procedure in PL/SQL

The 'CREATE PROCEDURE' statement is used to define a procedure. You can also use 'OR REPLACE' to modify an existing procedure.

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 created, a procedure can be removed from the database using the DROP PROCEDURE statement.

Syntax:

DROP PROCEDURE procedure_name; 

Example:

DROP PROCEDURE Update course; 

Parameter modes in PL/ SQL subprograms

Parameter modes define how values are passed to and from the subprogram. There are three parameter modes in PL/SQL:

1. IN Mode

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 Mode

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 Mode

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. 

Conclusion

PL/SQL is a versatile and powerful extension to SQL, offering more control and flexibility in writing database programs. Understanding how to perform basic query operations and work with subprograms is fundamental to leveraging the full capabilities of Oracle databases. From creating and removing procedures to effectively using parameter modes, PL/SQL is a key skill for database developers aiming to build scalable, maintainable, and high-performance applications.