PL/SQL
PL/SQL tutorial provides basic and advanced concepts of SQL. Our PL/SQL tutorial is designed for beginners and professionals.
PL/SQL is a block structured language that can have multiple blocks in it.
Our PL/SQL tutorial includes all topics of PL/SQL language such as conditional statements, loops, arrays, string, exceptions, collections, records, triggers, functions, procedures, cursors etc. There are also given PL/SQL interview questions and quizzes to help you better understand the PL/SQL language.
SQL stands for Structured Query Language i.e. used to perform operations on the records stored in database such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.
What is PL/SQL
PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for “Procedural Language extension of SQL” that is used in Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQL.
PL/SQL Functionalities
PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.
With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.
The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.
PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows:
Delimeters
Identifiers
Literals
Comments
PL/SQL Procedure
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.
The procedure contains a header and a body.
Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.
How to pass parameters in procedure:
When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:
IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
PL/SQL Create Procedure
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Create procedure example
In this example, we are going to insert record in user table. So you need to create user table first.
Table creation:
create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.
Procedure Code:
create or replace procedure “INSERTUSER”
(id IN NUMBER,
name IN VARCHAR2)
is
begin
insert into user values(id,name);
end;
/
Output:
Procedure created.
PL/SQL program to call procedure
Let’s see the code to call above created procedure.
BEGIN
insertuser(101,’Rahul’);
dbms_output.put_line(‘record inserted successfully’);
END;
/
Now, see the “USER” table, you will see one record is inserted.