PL/SQL

Yes, we can label PL/SQL loops. Here are some key points about labels in a loop:

• The label is enclosed by << and >> and appears at the beginning of the LOOP statement.
• The label name can also be included at the end of the LOOP statement.

Let us see an example:

DECLARE
  i number(1);
  j number(1);
BEGIN
  << outer >>
  FOR i IN 1..5 LOOP
     << inner >>
     FOR j IN 1..2 LOOP
        dbms_output.put_line('i = '|| i || ', j = ' || j);
     END loop inner;
  END loop outer;
END;
/

The output:

i = 1, j = 1
i = 1, j = 2
i = 2, j = 1
i = 2, j = 2
i = 3, j = 1
i = 3, j = 2
i = 4, j = 1
i = 4, j = 2
i = 5, j = 1
i = 5, j = 2

The CASE statement in PL/SQL selects one sequence of statements to execute. The statement uses a selector instead multiple Boolean expression.

The following is the syntax:

CASE selector
  WHEN 'value1' THEN Statement1;
  WHEN 'value2' THEN Statement2;
  WHEN 'value3' THEN Statement3;
  ...
  ELSE Sn;  -- default case
END CASE;

The following is an example:

DECLARE
  points number := 100;
BEGIN
  case points
     when 20 then dbms_output.put_line('Rank 4');
     when 50 then dbms_output.put_line('Rank 3');
     when 75 then dbms_output.put_line('Rank 2');
     when 100 then dbms_output.put_line('Rank1... Topper');
     else dbms_output.put_line('No ranking!');
  end case;
END;
/

The output:

Rank1... Topper

The schema objects created with PL/SQL is Packages, Triggers, Cursors, etc.

• Packages
  

Packages are the schema objects that groups logically related PL/SQL types and variables.

• Triggers
  

Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.

• Cursors

Cursors has information about processing the statement. It holds the rows (one or more) returned by a SQL statement.

• %FOUND

The %FOUND cursor attribute returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

• %NOTFOUND

The %NOTFOUND cursor attribute returns TRUE if record was not fetched successfully, FALSE otherwise. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

Cursor attributes are used like this in PL/SQL:

sql%attribute_name

Above, set any attribute name under “attribute_name”. Here we are learning about %FOUND and %NOTFOUND attribute names.

Let us consider the following table for our example:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/    

The output:

5 rows affected!

Since we updated the budget for all the departments above, therefore our table rows would look like this:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

To display output, PL/SQL has an in-built package known as DBMS_OUTPUT. With this package, we can also display debugging information.

The following is an example:

DECLARE
   one varchar2(20) := 'Website';
   two varchar2(20) := ' World!';
BEGIN
   dbms_output.put_line(one || two);
END;
/

The output:

Website World!

Let us see the subprograms of DBMS_OUTPUT:

• DBMS_OUTPUT.DISABLE; 

Disables message output.

• DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);

Enables message output. A NULL value of buffer_size represents unlimited buffer size.

• DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER);

Retrieves a single line of buffered information.

• DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER);

Retrieves an array of lines from the buffer.

• DBMS_OUTPUT.NEW_LINE;

Puts an end-of-line marker.

• DBMS_OUTPUT.PUT(item IN VARCHAR2);

Places a partial line in the buffer.

• DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);

Places a line in the buffer.

A common question in PL SQL interview questions, don't miss this one.  

At each iteration in a basic loop in PL/SQL, a sequence of statements is executed. After the execution, the control resumes at the top of the loop. The EXIT statement is a must in the execution part to exit from the loop. The keyword LOOP begins the execution. It ends with the keyword END LOOP.

The following is an example:

DECLARE
  a number := 2;
BEGIN
  LOOP
     dbms_output.put_line(a);
     a := a + 2;
     IF a > 10 THEN
        exit;
     END IF;
  END LOOP;
  dbms_output.put_line('After exit, the value of a = ' || a);
END;
/

The output:

2
4
6
8
10
After exit, the value of a = 12

A subprogram in PL/SQL is either a procedure or a function that can be invoked repeatedly. Each subprogram has a name and a list of parameters. It performs a specific task and the following two subprograms exist in PL/SQL:

• Functions: Return a single value.
• Procedures: Do not return a value directly.

The following are the parts of a subprogram:

• Declarative

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had statements that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Welcome!');
END;
/

The output displays the procedure created successfully:

Procedure created.

The default value is NULL. PL/SQL assigns NULL as the default value of a variable. To assign another value, you can use the DEFAULT keyword or the assignment operator. Let us see them one by one.Initialization with DEFAULT keyword:

DECLARE
  msg varchar2(15) DEFAULT 'House';
BEGIN
  dbms_output.put_line(msg);
END;
/

The output:

House

Initialization with assignment operator:

DECLARE
  device varchar2(11) := 'Laptop';
BEGIN
  dbms_output.put_line(device);
END;
/

The output:

Laptop

PL/SQL has subtypes of datatypes. This means subtype is a subset of a datatype. The following are the types and subtypes of Numeric data type in PL/SQL

• PLS_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_FLOAT

Single-precision IEEE 754-format floating-point number

• BINARY_DOUBLE

Double-precision IEEE 754-format floating-point number

• DEC(prec, scale)

ANSI specific fixed-point type with maximum precision of 38 decimal digits

• DECIMAL(prec, scale)

IBM specific fixed-point type with maximum precision of 38 decimal digits

• NUMERIC(pre, secale)

Floating type with maximum precision of 38 decimal digits

• DOUBLE PRECISION

ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)

• SMALLINT

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

• REAL

Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

The following are some of the Oracle Database PL/SQL packages:

• DBMS_ALERT

You can use the database triggers to alert an application when a database values change.

• DBMS_OUTPUT

You may have used PUL_LINE in this package to display output from PL/SQL blocks, subprograms, packages, and triggers. This also displays debugging information.

• DBMS_PIPE Package

This package lets different sessions to communicate over named pipes

• HTF and HTP Packages

This allows the PL/SQL programs to generate HTML tags.

• UTL_SMTP

Using this package, you can send emails over SMTP (Simple Mail Transfer Protocol).

Recursion works the same way as we saw in programming languages like C, C++, Java, C#, etc.

A program or subprogram calling itself is called a recursive call and the process is recursion.

Let us see an example wherein we are displaying the usage of Recursive call:

DECLARE
  n number;
  factorial number;  
FUNCTION fact(a number)
RETURN number  
IS
  f number;
BEGIN
  IF a=0 THEN
     f := 1;
  ELSE
     f := a * fact(a-1);
  END IF;
RETURN f;
END;  
BEGIN
  n:= 3;
  factorial := fact(n);
  dbms_output.put_line('Value of Factorial '|| n || ' is ' || factorial);
END;
/

The output:

Value of Factorial 3 is 6

To return the ASCII value of a character, you need to use the ASCII() method. It returns a numeric value, which is the ASCII value.

The following is the syntax:

ASCII(myCharacter)

Here, myCharacter is the character you need to mention to get the ASCICC value.

The following is an example:

DECLARE
  a varchar(1) := 'B';
BEGIN
  dbms_output.put_line(ASCII(a));
END;
/

The output:

66

NCHR is a function in PL/SQL that returns the character based on the specified number, which is an ASCII value. An example:

NCHR(77)

It displays the associated character:

M

The RPAD function is used to pad the right side of a string with characters. An example here pads the string:

RPAD(jack, 3)

The output is:

jac

A subprogram in PL/SQl is a standalone subprogram at the schema level. The CREATE PROCEDURE or the CREATE FUNCTION statement is used in PL/SQL to create a standalone procedure.

• If you want to execute a standalone procedure, then use the EXECUTE keyword.
• If you want to delete a standalone procedure in PL/SQL, then you need to use the DROP PROCEDURE or DROP FUNCTION statement.

The following is the syntax to create a procedure:

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a procedure:

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Chalk and Duster!!');
END;
/

The output displays the procedure created successfully:

• Procedure created.
• Now to execute the procedure created above, you need to use the EXECUTE keyword. The name of the above procedure is “one”.
• To call it:

EXECUTE one;

The above call will give the following output:

Chalk and Duster!

The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types introduced in PL/SQL 11g Release 1. These are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively.

The SIMPLE_FLOAT and SIMPLE_DOUBLE datatypes are alike their base types, except for their NOT NULL constraint.

• SIMPLE_INTEGER:  When the value will never be NULL and overflow checking is unnecessary.
• SIMPLE_FLOAT: When the value will never be NULL.
• SIMPLE_DOUBLE: When the value will never be NULL.

The SIMPLE_FLOAT and SIMPLE_DOUBLE has better performance than BINARY_FLOAT and BINARY_DOUBLE.

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type and differs from it in its overflow semantics. It has the same range as PLS_INTEGER i.e. -2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint.

The DBMS_TRACE package has the interface to trace PL/SQL functions, procedures, and exceptions. The package provides subprograms to start and stop PL/SQL tracing in a session.

The following are the constants of the DBMS_TRACE package:

• TRACE_ALL_CALLS: Traces calls or returns
• TRACE_ALL_EXCEPTIONS: Traces exceptions
• TRACE_ENABLED_EXCEPTIONS: Traces exceptions and handlers
• TRACE_LIMIT: Save only the last few records.
• TRACE_ALL_SQL: Traces SQL statements
• TRACE_ENABLED_SQL: Traces SQL statements at PL/SQL level.
• TRACE_ALL_LINES: Traces each line
• TRACE_PAUSE: Pauses trancing
• TRACE_RESUME: Resume tracing
• TRACE_STOP: Stops tracing

The access operator (.) is used in PL/SQL to access the attributes and methods of the object using the instance name and the (.). The example shows the usage:

No, stored procedure in PL/SQL does not return a value. Stored Procedure call using CALL keyword.

Here are some of the predefined exceptions in PL/SQL:

1. PROGRAM_ERROR
2. TIMEOUT _ON_RESOURCE
3. INVALID_CURSOR
4. LOGON_DENIED
5. ZERO_DIVIDE
6. STORAGE_ERROR
7. NO_DATA_FOUND
8. TOO_MANY_ROWS
9. CURSOR_ALREADY_OPEN
10. INVALID_NUMBER

The EXIT statement is used to immediately terminate a loop. With this, the program control resumes at the next statement following the loop. It can also be used in a nested loop to stop the execution of the innermost loop.

When the EXIT statement is encountered inside a loop, the loop is immediately terminated and the program control resumes at the next statement following the loop.

The following is the syntax:

EXIT;

The example code:

DECLARE
  val number(2) := 2;
BEGIN
  WHILE val < 10 LOOP
     dbms_output.put_line ('value = ' || val);
     val := val + 1;
     IF val = 5 THEN
        EXIT;
     END IF;
  END LOOP;
END;
/

The output:

value = 2
value = 3
value = 4

Once a value is declared in PL/SQL, you cannot change it. The CONSTANT keyword is used in PL/SQL to declare a constant.

The following is a query to find the area of circle, wherein we have set the constant pi as:

pi constant number := 3.141592654;

Above, we have declared a constant.

Let us see the complete code:

DECLARE
  pi constant number := 3.141592654;
  radius number(5,2);  
  d number(5,2);  
  area number (10, 2);
BEGIN  
  radius := 3;  
  d:= radius * 2;  
  area := pi * radius * radius;
  dbms_output.put_line('Radius = ' || radius);
  dbms_output.put_line('Diameter = ' || d);
  dbms_output.put_line('Area = ' || area);
END;
/

The output displays the area of circle:

Radius = 3
Diameter = 6
Area = 28.27
Statement processed.
0.01 seconds

Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Syntax

The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:

CREATE [OR REPLACE ] TRIGGER name_of_trigger  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE
  Declaration-statements
BEGIN  
  Executable-statements
EXCEPTION
  Exception-handling-statements
END;

Here,

• CREATE [OR REPLACE] TRIGGER name_of_trigger – The statement creates or replaces existing trigger with the name_of_trigger.
• {BEFORE | AFTER | INSTEAD OF} – The BEFORE or AFTER specifies when the trigger will get executed. The INSTEAD OF is for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} – These clauses specify the DML operation.
• [OF col_name] − The column name that will be updated.
• [ON table_name] – The name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − Refer new and old values for various DML statements.
• [FOR EACH ROW] − A row-level trigger, i.e. the trigger will be executed for each row being affected.
• WHEN (condition) − A condition for rows for which the trigger would fire.

Let us now take an example wherein we will create a trigger, trigger it, and even delete it.

For that, we are considering the following table <DEPTBUDGET>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Create Trigger

Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget differences between new and old assigned budgets:

CREATE OR REPLACE TRIGGER budget_diff
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
  budget_diff number;
BEGIN
  budget_diff := :NEW.budget  - :OLD.budget;
  dbms_output.put_line('Old Budget Assigned =' || :OLD.budget);
  dbms_output.put_line('New Budget Assigned = ' || :NEW.budget);
  dbms_output.put_line('Differences in Budget = ' || budget);
END;
/

The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above:

BEFORE DELETE OR INSERT OR UPDATE ON deptbudget

We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.

The statement would update an existing record in the table:

UPDATE budget
SET  = budget + 4500
WHERE id = 4;

We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following:

Old Budget Assigned = 76878
New Budget Assigned = 81378
Differences in Budget = 4500

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened. Here, d_deptbudget is the cursor name:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.

The following is the syntax to create explicit cursor:

CURSOR name_of_cursor IS select_statement;

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

Let us declare a cursor and set a cursor name with the SELECT statement:

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

Above, “d_deptbudget” is our cursor.

The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

OPEN d_deptbudget;

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

CLOSE d_deptbudget;

Let us now see an example:

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

A transaction in PL/SQL is an atomic unit that may consist of one or more related SQL statements.

There is a difference between an executed SQL statement and a committed transaction. On successful execution of SQL statement, if transaction containing the statement is not committed, then it can be rolled back and the changes made by the statement can be undone. Here, committed means permanent in the database. However, rolled back means the changes can be undone.

Beginning with transactions, let us understand its concepts:

• COMMIT

The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.

• ROLLBACK

The Rollback undone all the changes since the last COMMIT or ROLLBACK.

• ROLLBACK TO SAVEPOINT

Undone all the changes made since the specified savepoint was made.

• SAVEPOINT

The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs. A transaction begins when the first SQL statement is encountered after connecting to the database.

To commit a transaction in PL/SQL, use the COMMIT command:

COMMIT;

Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (1, 'Finance', 'NORTH');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (2, 'Marketing', 'EAST');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (3, 'Operations', 'WEST');
COMMIT;

To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.

The syntax:

ROLLBACK [TO SAVEPOINT < name_of_savepoint>];

Here, name_of_savepoint is the name of the savepoint.

If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.

Savepoints splits a long transaction into smaller units. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:

SAVEPOINT name_of_savepoint;

Let us see an example of SAVEPOINT:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (1, 'Finance', 'NORTH', 20000);
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (2, 'Marketing', 'EAST', 30000);
SAVEPOINT s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;
ROLLBACK TO s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;  
WHERE DEPTNAME = 'MARKETING';
COMMIT;

Above, we have created a SAVEPOINT s1, which will allow us to rollback.

The LIKE operator is a comparison operator in PL/SQL through which compares a character, string, or CLOB value to a pattern. The operator returns TRUE if the value matches the pattern and FALSE if it does not.

For an example, we will take a procedure here and check for values:

DECLARE
PROCEDURE compare (myvalue  varchar2, pattern varchar2 ) is
BEGIN
  IF myvalue LIKE pattern THEN
     dbms_output.put_line ('True');
  ELSE
     dbms_output.put_line ('False');
  END IF;
END;  
BEGIN
  compare('John', 'J%n');
  compare('Jack', 'Ja_k');
  compare('Amit', 'Am%');
  compare('Sac', 'Sac_');
END;
/

The output displays the following:

True
True
True
False
Statement processed.

0.00 seconds

Above we have used the following wildcards:

For example, above we used J%n for “John”, the output was true, since the % wildcard matches any string of any length.

The following are the methods available in PL/SQL to trim characters:

The following is how you can use LTRIM() in PL/SQL to trim characters from the left:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(LTRIM(cars,'.'));
END;
/

The output:

Bentley.....

The following is how you can use RTRIM() in PL/SQL to trim characters from the right:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(RTRIM(cars,'.'));
END;
/

The output:

......Bentley

The following is how you can use TRIM() in PL/SQL to trim characters from the right and left:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(TRIM(cars,'.'));
END;
/

The output:

Bentley

When a cursor is closed, then the allocate memory is released.  To close a cursor, you need to use the CLOSE clause with the name of cursor you want to close.

CLOSE name_of_cursor;

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.

The following is the syntax to create explicit cursor:

CURSOR name_of_cursor IS select_statement;

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

Let us declare a cursor and set a cursor name with the SELECT statement:

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

Above, “d_deptbudget” is our cursor.

The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

OPEN d_deptbudget;

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

CLOSE d_deptbudget;

Let us now see an example:

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

PL/SQL Block Structures and it executes as a whole block.

The block structure can be understood with the concept of subprograms in PL/SQL. A block structure has the following parts:

• Declarative

It contains declarations of cursors, constants, variables, exceptions, etc. Variables are declared here.

• Executable

Had statements that perform actions. It must have atleast one statement.

• Exception Handling

The code that handles run-time errors. It use EXCEPTION keyword.

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Welcome!');
END;
/

The output displays the procedure created successfully:

Procedure created.

A staple in PL SQL interview questions for experienced, be prepared to answer this one.  

Each subprogram in PL/SQL has a name and a list of parameters. With that, it has the following parts:

• Declaration
• Executable
• Exception Handling

Let us now see them one by one:

• Declaration

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had statements that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

With PL/SQL, you can easily pass a record as a subprogram parameter in the same way pass any other variable.

The following is an example:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number
);
lp1 laptop;
lp2 laptop;
lp3 laptop;
PROCEDURE myDevice(lpt laptop) IS
BEGIN
 -- Laptop record
  dbms_output.put_line('Laptop Brand = '|| lpt.brand);
  dbms_output.put_line('Laptop RAM = '|| lpt.RAM);
  dbms_output.put_line('Laptop SNO = ' || lpt.SNO);
END;
BEGIN
  -- Laptop 1 specification
  lp1.brand:= 'Dell';
  lp1.RAM:= 4;  
  lp1.SNO:=  87667;
  -- Laptop 2 specification
  lp2.brand:= 'Lenevo';
  lp2.RAM:= 4;  
  lp2.SNO:=  47656;
  -- Laptop 3 specification
  lp3.brand:= 'HP';
  lp3.RAM:= 8;  
  lp3.SNO:=  98989;
  myDevice(lp1);
  myDevice(lp2);
  myDevice(lp3);
END;
/

The output:

Laptop Brand = Dell
Laptop RAM = 4
Laptop SNO = 87667
Laptop Brand = Lenevo
Laptop RAM = 4
Laptop SNO = 47656
Laptop Brand = HP
Laptop RAM = 8
Laptop SNO = 98989

Within a package, you can refer the objects and subprograms like this:

• name_of_package.object_name
• name_of_package.subprogram_name

Like other programming languages, PL/SQL also allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.

To create own exception, firstly consider the following table <DEPTBUDGET>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Let’s say we created the following procedure and a user-defined exception in it:

DECLARE
  d_id deptbudget.id%type := &dd_id;
  d_name deptbudget.deptname%type;
  d_loc deptbudget.deptloc%type;  
  my_exception  EXCEPTION;
BEGIN
  IF d_id <= 0 THEN
     RAISE my_exception;
  ELSE
     SELECT  deptname, deptloc INTO d_name, d_loc
     FROM deptbudget
     WHERE deptid = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  d_name);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc);
  END IF;
EXCEPTION
  WHEN my_exception THEN
     dbms_output.put_line('ID is always greater than zero!');
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

ID is always greater than zero!

The Large Object Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:

• BFILE: Store large binary objects in operating system files outside the database. The size is system-dependent and exceed 4 GB.
• BLOB: Store large binary objects in the database. The size is 8 to 128 TB.
• CLOB: Store large blocks of character data in the database. The size is 8 to 128 TB.
• NCLOB: Store large blocks of NCHAR data in the database. The size is 8 to 128 TB.

The %ROWCOUNT cursor attribute returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To use any of the above attribute, use the following syntax:

sql%attribute_name

Above, set any attribute name under “attribute_name”.

Let us consider the following table for our example:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET     |
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/   

The output:

5 rows affected!

Since we updated the budget for all the departments above, therefore our table rows would look like this:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

To find the factorial of a number, the following is the code:

DECLARE
a number :=1;    
n number := 5;    
BEGIN
while n > 0 loop   
a:=n*a;         
n:=n-1;            
end loop;          
dbms_output.put_line(a);   
end;

The output:

120

To include single quotes in a string literal, you need to use it like:

“That wasn’’t a big blow to the team”

Above, we have set two single quotes to display one single quote.

Let us see an example.

Our string is:

It's my life

The following is the code to include single quotes:

DECLARE
  a varchar2(15):= 'It''s my life!';
BEGIN  
  dbms_output.put_line(a);
END;
/

The output:

It's my life!

The BETWEEN operator in PL/SQL checks whether a value lies in a specified range or not.

For example, the value a BETWEEN x AND y says that a >= x and a <= y.

Let us see an example:

DECLARE
  val number(2) := 3;
BEGIN
  IF (val between 1 and 5) THEN
     dbms_output.put_line('True');
  ELSE
     dbms_output.put_line('False');
  END IF;
END;
/

The output:

True

Let us see another example:

DECLARE 
   val number(2) := 15; 
BEGIN 
   IF (val between 1 and 10) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/

The output:

False

The RAISE statement is used in PL/SQL to raise exceptions. Through this a user can raise exceptions explicitly.

The following is the syntax to display how to use raise exceptions with RAISE statement:

DECLARE
  exception_name EXCEPTION;
BEGIN
  IF condition THEN
     RAISE exception_name;
  END IF;
EXCEPTION
  WHEN exception_name THEN
  statement;
END;

An example can be if the ID is less than zero, the raise an exception:

IF d_id <= 0 THEN
     RAISE my_exception;

Above “my_exception” is the following we set above:

my_exception EXCEPTION;

Let us see an example wherein we are raising exceptions. The following is the table we are taking into consideration:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Let’s say we created the following procedure and a user-defined exception in it:

DECLARE
  id deptbudget.id%type := &d_id;
  deptname deptbudget.deptname%type;
  deptloc deptbudget.deptloc%type;  
  my_exception  EXCEPTION;
BEGIN
  IF d_id <= 0 THEN
     RAISE my_exception;
  ELSE
     SELECT  deptname, deptloc INTO deptname, deptloc
     FROM deptbudget
     WHERE id = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  deptname);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc);
  END IF;
EXCEPTION
  WHEN my_exception THEN
     dbms_output.put_line('ID is always greater than zero!');
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

ID is always greater than zero!

This question is a regular feature in PLSQL interview questions, be ready to tackle it.  

Actual parameters can be passed as Positional notation, Named notation as well as Mixed notation. In Positional Notation, the same parameters in the same order as they are declared in the procedure are specified.

The actual parameter is associated with the formal parameter in named notation using the arrow symbol ( => ) like:

x => a

The procedure call for this would be:

myFunction(x => a, y => b, z => c);

In PL/SQL, you can mix both notations in a procedure call with n mixed notation. Remember, the positional notation should precede the named notation.

myFunction(x, y, z => c);

The following is an example:

Let’s say we have below given table:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET       |
+-----------+-----------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Now, we will call with Positional, mixed, and named notations:

DECLARE
 d_id NUMBER(6) := 2;
 d_budget NUMBER(6) := 500;
 PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS
   BEGIN
     UPDATE employees SET budget = budget + amount_add WHERE deptid = id;
 END new_budget ;
BEGIN
 new_budget (did, d_budget ); -- positional procedure call for actual parameters
 new_budget (amount_add => d_budget , id=> d_id); -- named parameters
 new_budget (d_id, amount_add => d_budget ); -- mixed parameters
END;

Above, you can see we have a procedure called “new_budget”. We are updating the budget here:

PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS
   BEGIN
     UPDATE employees SET budget = budget + amount_add WHERE deptid = id;
END new_budget ;

We are calling one by one with positional, mixed, and named notation:

new_budget (did, d_budget ); -- positional procedure call for actual parameters
new_budget (amount_add => d_budget , id=> d_id); -- named parameters
new_budget (d_id, amount_add => d_budget ); -- mixed parameters

The TIMESTAMP datatype stores the year, month, and day of the DATE datatype. It also allows you to store hour, minute, and second values.

Expect to come across this popular question in PLSQL interview questions for experienced.  

Yes, a %ROWCOUNT attribute returns the total number of rows returned by the FETCH command.

With PL/SQL, we can create a user-defined subtype. It already has predefined subtypes in STANDARD package.

Let us now see how to create an Integer subtype:

DECLARE
  SUBTYPE info IS varchar2(50);
  car info;
BEGIN
  car:= 'I love cars!';
  dbms_output.put_line(car);
END;
/

The output:

I love cars!

The WHEN clause has search conditions that output Boolean values in a searched CASE statement.

The following is the syntax of searched CASE that has the WHEN conditions:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
 else_statements ]
END CASE;]

Let us see an example:

DECLARE
  points number := 100;
BEGIN
  case  
     when points = 20 then dbms_output.put_line('Rank 4');
     when points = 50 then dbms_output.put_line('Rank 3');
     when points = 75 then dbms_output.put_line('Rank 2');
     when points = 100 then dbms_output.put_line('Rank1... Topper');
     else dbms_output.put_line('No ranking!');
  end case;
END;
/

The output:

Rank1... Topper

In PL/SQL, packages are the schema objects that groups logically related PL/SQL types and variables. A Package has the following two parts:

• Package Specification

The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.

• Package Body

The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.

The following are the guidelines:

• You need to design and define the package specifications before the package bodies.
• Declare public cursors in package specifications and define them in package bodies.
• Assign initial values in the initialization part of the package body. Avoid this in declarations.

A must-know for anyone heading into a PLSQL interview, this question is frequently asked in PLSQL developer interview questions.  

VARRARY data structure stores a fixed-size sequential collection of elements of the same type. It has contiguous memory locations, with the lowest address as the first element and the highest address as the last element.

The CREATE TYPE statement is used to create a varray type. The following is the syntax for creating a VARRAY type within a PL/SQL block:

TYPE name_of_varray_type IS VARRAY(n) of <type_of_element>

Here,

n is the number of elements,
name_of_varray_type is a valid attribute name, and
type_of_element is the data type of the elements of the array

The following is an example:

Type marks IS VARRAY(10) OF INTEGER;

Let us see an example wherein we are displaying the appraisal points for employees according to their IDs:

DECLARE
  type points IS VARRAY(10) OF INTEGER;
  appraisal_points points;
  total integer;
BEGIN
  appraisal_points:= points(76, 79, 89, 96, 99, 86, 89, 97);
  total := appraisal_points.count;
  FOR i in 1 .. total LOOP
     dbms_output.put_line('Appraisal Points for Employee ' || i || ' = ' || appraisal_points(i));
  END LOOP;
END;
/

The output:

Appraisal Points for Employee 1 = 76
Appraisal Points for Employee 2 = 79
Appraisal Points for Employee 3 = 89
Appraisal Points for Employee 4 = 96
Appraisal Points for Employee 5 = 99
Appraisal Points for Employee 6 = 86
Appraisal Points for Employee 7 = 89
Appraisal Points for Employee 8 = 97

Sequence of characters are called strings. Just like any other language, a string can be letters, numbers, special characters, etc.

The following are the three types of strings in PL/SQL:

• Fixed-length strings

In fixed-length strings, the length is to be specified while declaring a string. The string is right-padded with spaces to the length so specified.

• Variable-length string

A maximum length up to 32,767 for the string is specified and no padding takes place.

• Character large objects (CLOBs)

CLOBs are variable-length strings that can be up to 128 terabytes.

Let us see how we can declare a String variable. We have a lot of string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB in PL/SQL.

The declaration:

DECLARE
   cars varchar2(25) := 'Bentley;

Let us see an example where we have two variables:

DECLARE
  car varchar2(15) := 'Bentley';
  device varchar2(11) := 'Laptop';
BEGIN
  dbms_output.put_line(UPPER(car));
  dbms_output.put_line(LOWER(car));
  dbms_output.put_line(device);
  dbms_output.put_line(UPPER(device));
END;
/

The output:

BENTLEY
bentley
Laptop
LAPTOP

To pad a string with zeros, you can use RPAD or LPAD function in PL/SQL. Let us see how to pad the right side of a string with zeros:

RPAD(tom, 6)

The output would have a width of 6 characters now. Since the string is of 3 characters, therefore rest of the space would be filled with 0s:

tom000

It's no surprise that this one pops up often in advanced PL SQL interview questions.  

The VSIZE() function returns the number of bytes in the internal representation of an expression. An example:

VSIZE('Selection Day')

The output:

13

The PL/SQL programming constructs include:

1. Embedded SQL support
2. Stored procedures
3. Packages
4. Triggers
5. Exception Handling
6. Cursors