PL/SQL Tutorial

PL/SQL Tutorial

This article is a PL/SQL Tutorial. You can find almost everything for beginners in one article about PL/SQL.

In addition to this article, you may want to read below articles to comprehend everything about Oracle PL/SQL, Oracle Date Functions, Oracle String Functions and Oracle Numeric/Math Functions.

Oracle Date Functions“,

Oracle String Functions“,

Oracle Numeric/Math Functions

Index:

  1. Chapter1:What is PL/SQL in Oracle?
  2. Chapter2:PL/SQL Data Types and Variables
  3. Chapter3:PL/SQL Operators
  4. Chapter4:PL/SQL Conditional Statements
  5. Chapter5:PL/SQL Loops
  6. Chapter6:PL/SQL Procedures
  7. Chapter7:PL/SQL Functions
  8. Chapter8:PL/SQL Cursor
  9. Chapter9:PL/SQL Records
  10. Chapter10:PL/SQL Exception
  11. Chapter11:PL/SQL Trigger
  12. Chapter12:PL/SQL Packages
  13. Chapter13:PL/SQL Collections

Chapter1:

What is PL/SQL in Oracle?

PL/SQL(Procedural Language extensions to SQL) is basically a programming language ​​in Oracle database. PL/SQL allows to use structures such as if, loop, while, function, and procedure in programming languages.

Many databases use the data query language called SQL. However, advanced database management systems such as Oracle, SQL Server meet the features in various programming languages ​​with additional languages ​​to use SQL language flexibly.

SQL Server meets this need with T-SQL and Oracle meets this need with PL/SQL.

The main feature of these languages ​​is that they allow the use of variables, conditional expressions, loops, functions and procedures.

Thanks to these features, operations such as inserting data and processing on data are made easier.

In fact, these operations can be done by programming languages ​​by connecting to the database.

However, since languages ​​such as T-SQL, PL/SQL are included in the database and compiled beforehand, it enables faster processing.

PL/SQL structure

In many sources you can see that the PL/SQL language is passed as a block-based language.

This is because the commands written are in blocks.

The PL/SQL structure is as follows.

The DECLARE keyword in the PL/SQL structure is the part where the variables to be used in the PL/SQL commands are defined and it is not mandatory.

BEGIN and END is the part where SQL and PL/SQL commands will be run and it is mandatory to use.

The part determined by the EXCEPTION keyword is the area used to find and manage errors in SQL or PL/SQL commands and it is not mandatory.

PL/SQL Usage

PL/SQL can be used after Oracle installation, there is no need for an additional installation.

PL/SQL commands are used by writing in SQL Plus or SQL Developer tool in Oracle.

You can see the sample PL/SQL code that writes Hello Oracle on the screen With PL/SQL.

You can see the sample PL/SQL  code that writes Hello Oracle on the screen using variable With PL/SQL.

NOTE: Oracle PL/SQL uses the syntax of Pascal and ADA languages.

Chapter2:

PL/SQL Data Types and Variables

This PL/SQL Tutorial is about Data Types and Variables.

Data Types

Data types indicate how data is stored in memory.

PL / SQL has several data types to store data.

PL / SQL data types are divided into four sections: Scalar, Large Object, Composite and Refence.

1)Scalar Data Types in PL/SQL

Scalar data types is a data type that store a single data such as NUMBER, DATE, BOOLEAN.

Numeric Data Types in PL/SQL:

PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, NUMBER(prec, scale), DEC(prec, scale), DECIMAL(prec, scale), NUMERIC(pre, secale), DOUBLE PRECISION, FLOAT, INT, INTEGER, SMALLINT, REAL

Character Data Types in PL/SQL:

CHAR, VARCHAR2, RAW, NCHAR, NVARCHAR2, LONG, LONG RAW, ROWID, UROWID

Boolean Data Types in PL/SQL:

The BOOLEAN data type takes TRUE, FALSE and NULL.

Datatime Data Types in PL/SQL:

DATE, TIMESTAMP, INTERVAL

2)Large Object Data Types in PL/SQL

Large Object data types are data types that store files such as text, images, and videos.

BFILE, BLOB, CLOB, NCLOB

3)Composite Data Types in PL/SQL

Composite data types are data types such as Collections and Records.

4)Reference Data Types in PL/SQL

The reference data type is the data type that refers to the previously defined data types.

NOTE: Some data types have the same feature.

User Defined Data Types in PL/SQL

PL/SQL allows users to define data types.

Unnecessary length of data is prevented by using user-defined data types.

Also, developed PL/SQL programs are provided to be more meaningful.

Variables in PL/SQL

PL/SQL allows the use of variables to temporarily store data.

We can define a variable as follows.

Variables can be up to 30 characters long.

name_surname, v_name_surname

Variables must start with the ASCII character.

For example, a variable name cannot be 1_name_surname.

The keyword := and DEFAULT can be used to give initial values ​​to variables.

The NOT NULL keyword can be used so that variables do not take NULL.

The data type of the variable can be the data type of the column in the table.

The CONSTANT keyword can be used to make the variable constant.

Literals in PL/SQL

Literal values in PL/SQL are called literal.

‘Hello Oracle’

TRUE, FALSE, NULL

‘A’

Chapter3:

PL/SQL Operators

This PL/SQL Tutorial is about PL/SQL Operators.

Operators are special expressions that operate process between one or more values ​​in programming languages.

PL/SQL has operators that allow to process various data.

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
  • String operators

Arithmetic Operators in PL/SQL

Arithmetic operators are operators that provide basic numerical operations such as addition, subtraction, multiplication and division on numbers.

Relational Operators in PL/SQL

By comparing the two values, according to the operator result, it returns TRUE or FALSE.

Comparison Operators in PL/SQL

Like Relational Operators, Comparison operators compares two values and returns TRUE or FALSE. You can see comparision operators as below.

  • Like Operator in Oracle
  • Between Operator in Oracle
  • IN Operator in Oracle
  • IS NULL Operator in Oracle

LIKE Operator in PL/SQL

Like operator is used to search by the pattern.

Between Operator in PL/SQL

The Between Operator is used to query whether the specified value is within the specified range.

IN Operator in PL/SQL

The IN Opearator is used to query whether the specified value is one of the specified values.

IS NULL Operator in PL/SQL

Used to check if the value is NULL.

Logical Operators in PL/SQL

There are 3 logical operator in Oracle PL/SQL. AND, OR,NOT.

AND Operator in PL/SQL: It connects two operands each other(X AND Y). If both operands is true, then the result is true.

OR Operator in PL/SQL: It connects two operands each other(X AND Y). If one of the operands is true, then the result is true.

NOT Operator in PL/SQL: It reverse the result. If the result is true it returns false, and if the result is false, it returns true.

String Operator || in PL/SQL

PL / SQL || operator is used to concatenate string expressions.

Chapter4:

PL/SQL Conditional Statements

This PL/SQL Tutorial is about Conditional Statements.

Conditional statements allow to execute commands according to a certain condition.

The condition in conditional statements is created by using the operators in Oracle PL / SQL.

IF Condition in PL/SQL

If the specified condition is true, commands run in the block.

Sample IF Condition Usage is as follows;

IF ELSE Condition in PL/SQL

ELSE condition contains commands to run if the condition set by IF is not true.

Sample IF ELSE Condition Usage is as follows;

ELSIF Condition in PL/SQL

Used to specify multiple conditions.

Sample ELSIF Condition Usage is as follows;

CASE Condition in PL/SQL

IF AND ELSIF cause multiple operators to be used for a value.

The CASE keyword is used to check the result of a single value.

Sample CASE Condition Usage is as follows;

You see that less operators are used compared to the previous ELSIF example.

The CASE condition can also be used with operators.

Sample usage is as follows;

Conditional expressions can also be used in loops.

Chapter5:

PL/SQL Loops

This PL/SQL Tutorial is about Loops.

Loops are structures used to run commands again until a certain condition is met.

LOOP in Oracle

It is used to run the commands in the block continuously.

NOTE: If the loop is not terminated with the EXIT keyword it will run continuously-infinitely. Example LOOP usage is as follows.

Example LOOP WHEN EXIT usage is as follows.

WHILE LOOP in PL/SQL

It is used to run the commands in the block until the condition is met.

Example WHILE usage is as follows.

FOR LOOP in PL/SQL

It is used to run commands in the block as much as predetermined number.

Example FOR usage is as follows.

The number range can be a custom range.

To reverse the start and end in the FOR loop

GOTO Statement in PL/SQL

It is used to direct the command flow to a predetermined tag.

Example GOTO usage is as follows.

With GOTO, the loop can be created as follows.

NOTE: It is not recommended to use GOTO. Because GOTO complicates the command flow.

EXIT Statement in PL/SQL

It is used to exit the loop.

The WHEN keyword can be used to determine the condition.

Continue Statement in PL/SQL

Its used to skip loop steps.

The WHEN keyword can be used to determine the condition.

Various operators can be used for a certain range.

NOTE: The keyword CONTINUE must be added at the beginning of the commands. Otherwise, the step is skipped after the commands are executed.

Chapter6:

PL/SQL Procedures

This PL/SQL Tutorial is about Procedures.

These are the structures that keep Oracle PL/SQL commands under a name and run them when needed.

Procedures are compiled the first time they run and a query plan is created.

When you run stored procedures next time, they use this query plan.

The keywords to be used when creating the procedure are as follows.

The REPLACE keyword included in the command is not mandatory. However, it would be beneficial to use it as it will throw an error if you try to recreate an existing procedure. Let’s create a sample procedure.

Once the procedure is created, we can run it as follows.

The commands in the procedure will be executed each time the procedure is run.

Parameters

Another important feature of the procedure structure is the possibility to take parameters.

IN Parameter in PL/SQL Procedures

If the parameter is marked with IN, the parameter is passed only to the procedure.

OUT Parameter in PL/SQL Procedures

If the parameter is marked OUT, the parameter sends out data.

IN OUT Parameter in PL/SQL Procedures

If the parameter is marked with IN OUT, it takes both IN and OUT.

Example

In the example below, the loop start and end values are taken as parameters.

SYS.all_procedures table can be used to get information about the created procedures.

The following command is used to delete the created procedure.

The reason for using the procedures is to ensure that the written commands are securely stored in the database management system.

Also, the created procedures will work better because they are compiled beforehand.

When data is received from a table with any programming language, the SQL sentence must first be written and sent to the database.

The sent SQL statement is executed after it is checked by the database management system.

However, since the procedures are compiled beforehand, it is operated without recompilation, thus ensuring performance.

The procedures are similar to function structures, causing confusion.

While procedures are not required to return values, functions must return values.

Procedures are generally used in CRUD processes.

Chapter7:

PL/SQL Functions

This PL/SQL Tutorial is about Functions.

Oracle PL / SQL function structure, as in the procedure structure, are structures that can store commands under a name in the database and can be run again if needed.

The difference of the functions from the procedures is that they have to return values.

The keywords to be used when creating the function are as follows.

An example of creating a function with Oracle PL / SQL is as follows.

We can run the functions as below;

SYS.all_objects table can be used to get information about the created functions.

The following command is used to delete a function.

The reason for using the functions is to perform operations where the functions in Oracle are insufficient.

The function that converts the first letter in a text to uppercase can be written as follows.

You can run the function as below;

In addition, functions can be used for calculating a continuously used process (such as VAT calculation).

Chapter8:

PL/SQL Cursor

This PL/SQL Tutorial is about Cursor.

When the data in a table is listed, we can read the data row by row using the cursor and we can operate on these rows.

By using various values ​​in the cursor, it receives information about whether it is at the end of the record list and whether there is data.

There are two types of Cursors;

  • Implicit
  • Explicit

Implicit Cursor in PL/SQL

When the database queries run, the cursor created and managed by oracle is called Implicit Cursor.

We can not control this cursor type.

Explicit Cursor in PL/SQL

This is the cursor most of us knows. We can control this cursor type.

If we need the perform something(query execution, calculation) for all the rows of a query result set we can use cursor.

We can define cursor as follows;

Sample Cursor Definition;

Once the cursor is defined, we open the Cursor with the OPEN keyword.

After opening the cursor , the values ​​are fetched with FETCH keyword.

After the process is completed, we close the Cursor with the CLOSE keyword.

Sample Cursor Usage is as follows;

We created the variables to be used in the DECLARE according to the table data type.

We created a cursor and query with CURSOR.

We opened a cursor in BEGIN, we ran the query step by step with LOOP and FETCH.

Using IF in the FETCH block, we compared the value received with the cursor.

At the end of the BEGIN , we returned the memory by closing the cursor.

Thanks to the cursor, we had the opportunity to run PL / SQL commands on the data step by step.

Chapter9:

PL/SQL Records

This PL/SQL Tutorial is about Records.

Data types such as NUMBER, VARCHAR, VARCHAR2 in Oracle PL / SQL are used to store a single data.

However, defining multiple variables related to each other makes the codes complicated.

Record is a Composite data type used to store more than one value in a single variable.

Record data type definition can be table-based, cursor-based and user-defined.

Table Based Records in PL/SQL

In this type of definition, Record definition is made by using TABLE_NAME% ROWDTYPE structure.

In the example below, a record data type named PERSON has been created using some columns in the contacts table.

Cursor Based Records in PL/SQL

In this type of definition, the fields defined with Cursor are taken with CURSOR_NAME% ROWDTYPE and a record is defined.

In the example below, the record data type named PERSON was created using the columns indicated by the LIST_TABLE cursor.

User Defined Records in PL/SQL

In this type of definition, the variables to be included in the record are defined using the TYPE keyword.

In the example below, using the TYPE keyword, we have defined a Record named PERSON, which contains the values of first_name, last_name, email, phone.

Record data types previously defined in the user-defined record can also be used. Another important feature of the Record data type is that it can pass parameters to structures that take parameters such as procedure and function. In this way, related parameters can be grouped and processed with a single record.

Chapter10:

PL/SQL Exception

This PL/SQL Tutorial is about Exception.

These are the structures used for the management of errors that occur during the execution of commands.

PL/SQL Exception Types

  • System-defined
  • User-defined

The use of the PL / SQL Exception structure is as follows.

Sample Exception Usage is as follows;

PL/SQL System Defined Exceptions

The Exception type previously created by Oracle PL / SQL is called System defined.

System-defined exception types are listed below.

ACCESS_INTO_NULL, CASE_NOT_FOUND, COLLECTION_IS_NULL, DUP_VAL_ON_INDEX, INVALID_CURSOR, INVALID_NUMBER, LOGIN_DENIED, NO_DATA_FOUND, NOT_LOGGED_ON, PROGRAM_ERROR, ROWTYPE_MISMATCH, SELF_IS_NULL, STORAGE_ERROR, TOO_MANY_ROWS, VALUE_ERROR, ZERO_DIVIDE

PL/SQL User Defined Exceptions

Oracle PL/SQL also allows custom exception definition. These Exception types are called user defined exceptions. You can create User Defined Exception as follows.

We can set a special error code for the exception as follows.

The occured error can be triggered by RAISE.

The RAISE_APPLICATION_ERROR function takes the “error code”, “error message”, and “whether the error should be replaced with existing errors” as parameters to create a custom error.

Chapter11:

PL/SQL Trigger

This PL/SQL Tutorial is about Trigger.

The structure used to run a specific command after or before various operations on the database is called a trigger.

Triggers have the same structure as the procedures in PL / SQL.

The most important feature that distinguishes the triggers from the procedures is that the triggers run automatically after an event.

For example after or before an insert. The keywords to be used when creating a trigger are as follows.

Triggers are operated in the following cases.

DML Triggers– INSERT, UPDATE, DELETE

DDL Triggers– CREATE, ALTER, DROP

Database Triggers – SERVERERROR, LOGON, LOGOFF, STARTUP ve SHUTDOWN

DML Triggers in PL/SQL

It is often used to record transactions in DML operations.

Before Insert Trigger in PL/SQL

In the example below, it will print “New Product inserted.” on the screen before the record is inserterd to the Products table. Because this is before insert trigger.

If we use AFTER instead of BEFORE as a keyword, then it will print “New Product inserted.” on the screen after the record is inserterd to the Products table.

Also there are triggers like BEFORE INSERT TRIGGER as below.

  • Before Delete Trigger
  • Before Update Trigger
  • After Insert Trigger
  • After Delete Trigger
  • After Update Trigger

The transaction can be recorded by adding a record to another table within the BEGIN and END block.

It will be sufficient to add a record to the Products table to run the trigger.

Disable Trigger in PL/SQL

The ALTER and DISABLE keywords are used to disable the trigger.

Enable Trigger in PL/SQL

The ALTER and ENABLE keywords are used to enable the trigger.

List Triggers in PL/SQL

SYS.user_triggers table can be used to get information about triggers.

Drop Trigger in PL/SQL

The DROP keyword is used to delete the trigger.

Enable ALL Triggers in a Table

Disable ALL Triggers in a Table

Note: Using too many triggers can cause a bottleneck on the database server.

Chapter12:

PL/SQL Packages

This PL/SQL Tutorial is about Packages.

The structure in which structures, such as procedure, function, are gathered together is called Packages.

Oracle PL / SQL packages definition consists of two parts: definition and body.

Create PACKAGE in PL/SQL

The keywords to be used when creating the package are as follows.

An example of creating a package with Oracle PL / SQL is as follows.

After making the necessary definitions for the package, the content of the structures such as procedure and function is written in the BODY section.

Call Procedure in PACKAGE in PL/SQL

We can access the package contents as follows.

For example, let’s run the MyLOOP procedure above.

Similarly, other objects in the package can be accessed.

The user_objects table is used to get information about the created packages.

The DESC keyword is used to get information about a package.

DROP PACKAGE in PL/SQL

The DROP keyword is used to delete the package.

Predefined PACKAGES in PL/SQL

There are packages created for various processes within Oracle.

DBMS_OUTPUT

This is the package used to display the data on the screen.

DBMS_PIPE

This is the package used to process between sessions.

DBMS_LOCK

This is the package in which user lock operations are performed.

DBMS_STANDART

This is the standard package used in PL/SQL commands.

For example; The RAISE_APPLICATION_ERROR function is included in the DBMS_STANDART package.

The reason for using the packages, as can be understood from the examples, is the collection of commands that do interrelated work.

The use of packages for commands where the same or interrelated operations are performed will be useful for regular development.

Chapter13:

PL/SQL Collections

It is the data type where values ​​with similar data types are stored and variable capacity grows dynamically.

Collections are used to store data of the same data type in a variable, such as arrays contained in PL / SQL.

The difference of the collections from the arrays is that their dimensions can be changed when new elements are added or requested.

There are 3 types of PL / SQL collection structures.

  • Associative (Index-By Table)
  • Nested Table
  • Varray

Associative Array in PL/SQL

A collection type that stores data as a key-value pair.

The collection size increases as data is added. Associative Array definition is as follows.

Sample Associative Array usage is as follows.

If you want to make the key value a numerical value, a numeric data type such as PLS_INTEGER, NUMBER can be specified instead of VARCHAR2 (20).

Nested Table in PL/SQL

It is a type of collection that allows us to store data as in arrays.

The most important feature that distinguishes this collection from the arrays is that its capacity can be increased.

Unlike the Associative Array type, INDEX BY cannot be used.

Adding value to the collection is done by expanding the collection with EXTEND method.

Varray in PL/SQL

Arrays in many programming languages ​​are also available in PL/SQL.

Unlike other programming languages, PL / SQL arrays start from 1. Array definition is as follows.

Sample array usage is as follows.

Collection Methods in PL/SQL

There are several methods for using collections flexibly.

EXISTS (n): Checks if the specified element exists in the collection.

COUNT: Returns the number of elements in the collection.

LIMIT: Returns the collection capacity.

FIRST: Returns the first index value in the collection.

LAST: Returns the last index value in the collection.

PRIOR (n): Returns the sequence number before n.

NEXT (n): Returns the sequence number after n.

EXTEND: Extends the collection by appending a single null element to the collection.

EXTEND (n): Extends the collection by appending n single null element to the collection.

EXTEND (n1, n2): Extends the collection by appending n1 copies of the n2th element to the collection.

TRIM: Removes the last element in the collection.

TRIM (n): Removes elements as much as n from the end of the collection.

DELETE: Removes all elements from the collection.

DELETE (n): Removes element n from the collection.

DELETE (n1, n2): Removes all elements from n1 to n2 from the collection.

Leave a Reply