What is PL/SQL?
PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.
PL/SQL Variables
PL/SQL Variables are an important part of the PL/SQL language, as they are used to store data that can be used and referenced throughout the code. Variables are declared with a data type, and can be used to manipulate data in a variety of ways. They can contain values such as strings, numbers, dates, times, and other types of data, and can be used for calculations, as well as for storing values for later use. Variables can also be used to store the results of a query, which can then be used to produce an output or to compare data from one query to another. Additionally, variables can be used to assign values to parameters that are passed to procedures or functions. PL/SQL variables are a powerful tool that can be used to make your code more efficient and flexible.
Data Type | Values |
Numeric | BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT |
Character | CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2 |
Boolean | BOOLEAN |
Date & Time | DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND |
Object | BFILE, BLOB, CLOBNCLOB |
PL/SQL Syntax
PL/SQL Block Structure
To get out output on your console you must set
SET SERVEROUTPUT ON from Terminal or set in code.DECLARE Declarting variables; BEGIN Execution statements; EXCEPTION Exception statements: END; /
Â
PL/SQL Function
PL/SQL Function is a type of programming code that takes inputs, processes them, and returns a single value. It is used to perform specific tasks within a larger program.
CREATE [OR REPLACE] FUNCTION function_name [( parameter_1 [IN] [OUT] data_type, parameter_2 [IN] [OUT] data_type, parameter_N [IN] [OUT] data_type] RETURN return_data_type IS --the declaration statements BEGIN -- the executable statements return return_data_type; EXCEPTION -- the exception-handling statements END; /
PL/SQL Procedure
A PL/SQL procedure is a block of code written in Oracle's proprietary procedural language that allows users to create, manipulate and retrieve data from an Oracle database. The procedure is typically embedded within a program or application, and can be used to perform any number of tasks such as data validation, calculations, and other complex tasks. PL/SQL procedures are written using statements and expressions, and can contain a series of instructions, declarations, and control structures such as IF-THEN-ELSE, LOOP, and CURSOR. PL/SQL procedures are a powerful tool in the Oracle database, providing the ability to perform complex tasks without having to write lengthy SQL queries. Additionally, stored procedures are more efficient in terms of performance as they are pre-compiled and optimized when they are stored in the database. As such, they can be executed multiple times without having to be recompiled, making them ideal for running the same task on a regular basis.
PROCEDURE [schema.]name[( parameter[, parameter...] ) ] [AUTHID DEFINER | CURRENT_USER] IS [--declarations statements] BEGIN --executable statements [ EXCEPTION ---exception handlers] END [name];
PL/SQL IF Statement
PL/SQL IF Statement is used to execute a certain block of code if a certain condition is true.
IF condition THEN sequence_of_statements; END IF;
PL/SQL Loop Statement
LOOP sequence_of_statements; END LOOP;
PL/SQL WHILE Loop
A PL/SQL WHILE loop is a type of loop that allows you to execute a set of instructions as long as a condition is true. It is used to repeat a set of instructions until a certain condition is met.
WHILE condition LOOP sequence_of_statements; END LOOP;
PL/SQL FOR Loop
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound LOOP sequence_of_statements; END LOOP;
PL/SQL Exception
EXCEPTION_NAME EXCEPTION; RAISE EXCEPTION_NAME;
PL/SQL Record
%ROWTYPE
To declare a table-based record you use a table name with
%ROWTYPEÂ attribute. The fields of the PL/SQL record has the same name and data type as the column of the table. It represents a row in a database table.DECLARE table_based_record table_name%ROWTYPE;
%TYPE
It is used to access column type and sync
DECLARE
PL/SQL Transaction management
COMMIT
A
COMMIT statement is used to make a permanent change to the database by the present transaction, ensuring that any modifications made to the database by the current transaction will be immediately visible to all other users. This is an important step, as it guarantees that all data modifications are accepted and stored in the database, and that all users have access to the same updated version of the database. After a COMMIT statement is issued, it cannot be undone, so it is important to consider all modifications carefully before committing them to the database.SAVEPOINT
A
SAVEPOINT is a marker within a transaction that provides an important feature: the ability to rollback any changes made in the transaction, up to and including the SAVEPOINT itself. This allows us to undo any mistakes we might have made, and even go back to the beginning of the transaction if we need to. SAVEPOINTs can be created at any time, so we can mark different points within the transaction to easily pinpoint where an issue may have occurred. This makes it much easier to identify and troubleshoot errors.SAVEPOINT name;
ROLLBACK
The
ROLLBACK statement is an incredibly useful tool that can be used to reverse the effects of the current transaction or a transaction that is in doubt. This statement can be used to undo the work that has been done, reverting the database to its state prior to the transaction. It is a powerful tool that can be used to ensure that the database remains consistent and up-to-date, as any errors or discrepancies that may have occurred in the current transaction can be reversed.PL/SQL Package
A PL/SQL Package is a collection of related procedures, functions, and variables that can be used to perform specific tasks. It is an important part of the Oracle database development toolkit, as it allows developers to easily create and maintain complex applications. Packages provide the ability to package related procedures and functions into one unit, making the code more readable and maintainable. It also provides the ability to group related code into a single unit, making it better organized and easier to debug. Additionally, packages can be used to improve performance by pre-compiling code and reducing the amount of time required for execution. By taking advantage of the PL/SQL Package, developers can create powerful, reliable applications that are easier to maintain and debug.
CREATE [OR REPLACE] PACKAGE package_name [ AUTHID { CURRENT_USER | DEFINER } ] { IS | AS }