Basic Query in PL/SQL procedure
Last Updated : 26 Aug, 2024
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
- It can be created at the schema level, inside a package, and inside a PL /SQL block.
- 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.
- It is stored in the database and the package is deleted with the DROP PACKAGE statement.
- 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:
- 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.
- 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.
Similar Reads
Procedures in PL/SQL
PL/SQL procedures are reusable code blocks that perform specific actions or logic within a database environment. They consist of two main components such as the procedure header which defines the procedure name and optional parameters and the procedure body which contains the executable statements i
5 min read
Query Processing in SQL
Query Processing includes translations of high-level Queries into low-level expressions that can be used at the physical level of the file system, query optimization, and actual execution of the query to get the actual result. High-level queries are converted into low-level expressions during query
4 min read
Query Execution Plan in SQL
An execution plan in SQL is a detailed plan that outlines the steps that the database management system (DBMS) will take to execute a query. It is a crucial component of query optimization, as it helps the DBMS determine the most efficient way to retrieve data from the database. Here, we will learn
5 min read
How to Drop Procedure in SQL
In SQL, stored procedures are used to encapsulate complex logic and queries. However, there may come a time when we need to remove or delete a stored procedure from a database. In this article, we will cover the various methods for dropping a stored procedure in SQL along with examples and explanati
3 min read
How to Modify a Stored Procedure in SQL Server?
In this article, we will learn to modify the created stored procedure in MS SQL.You can modify the Stored Procedure in two ways. one is by using a client called SSMS and other way is by using T-SQL statements + SSMS in MS SQL Server. Method 1: Using SQL Server Management Studio (SSMS) to Modify the
3 min read
SQL Query to Rename Stored Procedure
Stored Procedure is a saved SQL code. It is created to save time as we can use it again and again without writing the whole query. In this article, we will see how to Rename a Stored Procedure. To create a Procedure:Syntax: CREATE PROCEDURE procedure_name AS SELECT * FROM table_name;SQL query whose
2 min read
Reverse a string in PL/SQL
Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given a string, the task is to reverse a string using P
1 min read
Reverse a number in PL/SQL
Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Explanation: Consider the example, input = 12345. Step
2 min read
SQL Concepts and Queries
In this article, we will discuss the overview of SQL and will mainly focus on Concepts and Queries and will understand each with the help of examples. Let's discuss it one by one. Overview :SQL is a computer language that is used for storing, manipulating, and retrieving data in a structured format.
5 min read
PostgreSQL - Introduction to Stored Procedures
PostgreSQL allows the users to extend the database functionality with the help of user-defined functions and stored procedures through various procedural language elements, which are often referred to as stored procedures.The store procedures define functions for creating triggers or custom aggregat
5 min read