PL/SQL

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is a powerful extension of SQL used in Oracle databases.
PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements.
It was developed by Oracle in 1992 in order to overcome some limitations of SQL which given below:

• SQL does not include decision-making, looping, or branching capabilities. 

• There is no error-checking feature available when manipulating data.

• When SQL statements are sent to the Oracle engine simultaneously, the increased traffic can cause a decrease in execution speed.

PL/SQL

PL/SQL and SQL are strongly integrated.
PL/SQL supports various features such as cursors, exceptions, packages, triggers, and bind variables that enhance the functionality and performance of SQL statements.
PL/SQL can update, query, and transform data in a database and also gives high productivity to developers.

1. PL/SQL is a procedural language, which provides the functionality of decision making, iteration, and numerous further features of procedural programming languages.

2. Using a single command, PL/SQL executes several queries in one block.

3. PL/SQL can handle the exception generated in the PL/SQL block.

   That block is called an exception handling block.

4. One can create a PL/SQL unit such as procedures, packages, triggers, functions, and types, which are stored in the database for reuse by applications.

5. Applications that are written in PL/SQL are portable to computer hardware or operating systems where Oracle is functional.

​PL/SQL - Main benefits

An ordered collection of the same type of elements is referred to as a PL/SQL table.
position of each element in the ordered collection is determined by its index number.
The position of each element in the ordered collection is determined by its own index number. A user-defined type must be declared first for the PL/SQL table, and then it must be declared as a variable.

DECLARE

       TYPE Vehicle_SSN_tabtype IS TABLE OF

             integer (9)

             INDEX BY binary _integer;

       Vehicle_SSN_table Vehicle_SSN_tabtype;

​PL/SQL - Tables

PL/SQL cursor controls the context area.
A cursor holds one or more than one row returned by an SQL statement. set of rows which is held by the cursor is known as an active set.

Two types of cursors exist in PL/SQL:

• Implicit Cursor - Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.

  Programmers cannot control the implicit cursors and the information in it.

  Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement.

• Explicit cursor - Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block.

​PL/SQL - Cursor

Multiple Choice

What thing controls PL/SQL cursors?

• Context area

• Implict

• Exciplit

• Area

Multiple Choice

Does every PL/SQL program have blocks that can be nested inside of one another?

• TRUE

• FALSE

COMMIT statement: The changes made during a transaction are saved permanently by the COMMIT command.

Syntax:

DECLARE

BEGIN

//  command;

COMMIT;

END;

ROLLBACK statement: It is used to undo any modification made since the transaction’s start.

Syntax:

DECLARE

BEGIN

// command;

ROLLBACK;

END;

SAVEPOINT statement: A transaction point that can be utilised to roll back to a certain point in the transaction is created using the SAVEPOINT statement.

Syntax:

DECLARE

BEGIN

SAVEPOINT sp;

// command;

ROLLBACK  TO sp;

END;

Multiple Choice

Position of each element in the ordered collection is determined by?

• Null

• Index number

• Variable

• None of the given answers

There are two types of data types present in PL/SQL.

• Scalar data types: NUMBER, DATE, CHAR, VARCHAR2, BOOLEAN and LONG are scalar data types.

• Composite data type: TABLE and RECORD are composite data types.

  List the types of exceptions in PL/SQL.

Two types of exceptions are present in PL/SQL.

• Pre_defined exception

• User-defined exception

PL/SQL does not support data definition commands like CREATE, ALTER etc.

Below are some PL/SQL exceptions.

• INVALID_NUMBER

• TOO_MANY_ROWS

• ACCESS_INTO_NULL

• CASE_NOT_FOUND

• ZERO_ERROR

• NO_DATA_FOUND

Multiple Choice

Which of the given answers have data-oriented language?

• SQL

• PL/SQL

• NOSQL

• None of the given answers

• A record is a type of data structure that may store several types of data elements. Like a row in a database table, a record is made up of various fields.

• There are three types of records in PL/SQL.

• Table-based records

• Cursor-based records

• User-defined records are created by programmers.

• TTITLE statement is used to define the top title similarly for defining the bottom title we will use BTITLE statement.

• The following are valid second values:

• 00 to 59.9(n), where 9(n) is the accuracy in fractional seconds of time.

• For DATE, the 9(n) section does not apply.

• In PL/SQL, a delimiter is a compound symbol having a unique meaning. Delimiters are used to indicate arithmetic operations like division, addition etc.

• The SQL % NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records.

  If the last SQL statement run had no effect on any rows, this variable returns TRUE. an SQL statement had no impact on any rows.

• The || operator is used to combine the strings. Both DBMS_OUTPUT.put line and select statements functions use the || operator.

• Definition commands like the CREATE command are not supported by PL/SQL.

• A view is generated by combining one or more tables. it is a virtual table that is based on the outcome of SQL statements; It includes rows and columns like an actual table.

  Syntax: CREATE VIEW view_name AS SELECT columns FROM tables;

• The following three are basic parts of a trigger.

• Trigger statement

• Trigger restriction

• Trigger action

• We will trace the code to measure its performance during run time. below are some methods to trace the PL/SQL code.

• DBMS_TRACE

• DBMS_APPLICATION_INFO

• DBMS_SESSION

• DBMS_MONITOR

Multiple Choice

For defining a bottom title what command are we use?

• TTITLE

• MTITLE

• BCTITLE

• BTITLE

Multiple Choice

A RECORD is type of?

• Data structure

• Data element

• Table structure

• Table elements

• None of the given answers

Multiple Choice

Does a "triger reaction" belongs to main parts of the triger?

• TRUE

• FALSE

Decision Making in PL/SQL (if-then , if-then-else, Nested if-then, if-then-elsif-then-else )

There come situations in real life when we need to make some decisions and based on these decisions, we decide what should we do next.
Similar situations arise in programming also where we need to make some decisions and based on these decisions we will execute the next block of code. Decision-making statements in programming languages decide the direction of flow of program execution. Decision-making statements available in pl/SQL are:

1. if then statement

2. if then else statements

3. nested if-then statements

4. if-then-elseif-then-else ladder

IF-THEN

• if then statement if then statement is the most simple decision-making statement. It is used to decide whether a certain statement or block of statements will be executed or not i.e if a certain condition is true then a block of statement is executed otherwise not.

  Syntax:

  if condition then
  --do something
  end if;
  
  Here, condition after evaluation will be either true or false.

IF-THEN-ELSE

• if – then- else: The if statement alone tells us that if a condition is true it will execute a block of statements and if the condition is false it won’t.

  But what if we want to do something else if the condition is false.

  Here comes the else statement.

  We can use the else statement with if statement to execute a block of code when the condition is false. Syntax:-

if (condition) then
-- Executes this block if
-- condition is true
else
-- Executes this block if
-- condition is false

NESTED-IF-THEN

• nested-if-then: A nested if-then is an if statement that is the target of another if statement. Nested if-then statements mean an if statement inside another if statement. Yes, PL/SQL allows us to nest if statements within if-then statements. i.e, we can place an if then statement inside another if then statement. Syntax:-

if (condition1) then
-- Executes when condition1 is true
if (condition2) then
-- Executes when condition2 is true
end if;
end if;

Hvala na pažnji !!