PL/SQL follows the basic BLOCK structure. Each code contains SQL and PL/SQL statements that together make a PL/SQL block.

Each PL/SQL block has these 3 sections:

• The optional Declaration Section
• The mandatory Execution Section
• The optional Exception handling Section

[DECLARE]

--declaration statements (optional)

BEGIN

--execution statements

[EXCEPTION]

--exception handling statements (optional)

END;

This clause represents the current row from an explicit cursor. This clause applies updates and deletes the row which is currently under consideration without mentioning the row ID.

Syntax:

UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name

By using Pragma called EXCEPTION_INIT we can name a PL/SQL Exception Block. It allows the programmer to command the compiler to provide custom error messages based on business logic by overtaking the predefined messages during the compilation time.

Syntax:

DECLARE
  exception_name EXCEPTION;
  PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
// PL/SQL Logic
EXCEPTION
 WHEN exception_name THEN
	// Steps to handle exception
END;

As the name says, ‘Trigger’ means to start or ‘activate’ something. In PL/SQL, it is a stored procedure that tells what action the database will take when an event related to it is performed.

· Syntax:

TRIGGER trigger_name

trigger_event

[ restrictions ]

BEGIN

actions_of_trigger;

END;

Some instances where Triggers can be used:

• To sustain complex integrity constraints.
• For auditing any PL/SQL table information.
• We also use triggers to signal other actions upon completion of the change whenever changes are done in a table.
• Implement complex rules of business.
• To prevent invalid transactions.

Sentences that do not have any effect on the functionality and are only used for the core purpose of enhancing the readability are called Comments. They are of two types:

• Single Line Comment: By using the symbol – and writing the comment next to it is way you can create a single line comment
• Multi-Line comment: To add multiple lines comments use the syntax like /* comment information */

Example:

SET SERVEROUTPUT ON;

DECLARE

-- Hi There! I am a single line comment.

var_name varchar2(40) := ‘I love PL/SQL’ ;

BEGIN

/*

Hi! I am a multi line

comment. I span across

multiple lines

*/

dbms_output.put_line(var_name);

END;

/

Output:

I love PL/SQL

CREATE PROCEDURE get_employee_details @salary nvarchar(30)

AS

BEGIN

SELECT * FROM ib_employee WHERE salary = @salary;

END;

DECLARE
-- Declared variables string, letter, reverse_string where string is the original string.
string VARCHAR2(10) := 'abccba';
letter VARCHAR2(20);
reverse_string VARCHAR2(10);
BEGIN
FOR i IN REVERSE 1..LENGTH(string) LOOP
 letter := SUBSTR(string, i, 1);
 -- concatenate letter to reverse_string variable
 reverse_string := reverse_string ||''||letter;
END LOOP;
IF reverse_string = string THEN
       dbms_output.Put_line(reverse_string||''||' is palindrome');
ELSE
   	dbms_output.Put_line(reverse_string ||'' ||' is not palindrome');
END IF;
END;

DECLARE
-- declare necessary variables
   -- num represents the given number
   -- number_to_word represents the word format of the number
   -- str, len and digit are the intermediate variables used for program execution
num   INTEGER;
number_to_word VARCHAR2(100);
digit_str   VARCHAR2(100);
len   INTEGER;
digit   INTEGER;
BEGIN
   num := 123456;
   len := LENGTH(num);
   dbms_output.PUT_LINE('Input: ' ||num);
   -- Iterate through the number one by one
   FOR i IN 1..len LOOP
   	digit := SUBSTR(num, i, 1);
   	-- Using DECODE, get the str representation of the digit
   	SELECT Decode(digit, 0, 'Zero ',
                   	1, 'One ',
                   	2, 'Two ',
                 	  3, 'Three ',
                   	4, 'Four ',
                   	5, 'Five ',
                   	6, 'Six ',
                   	7, 'Seven ',
                   	8, 'Eight ',
                   	9, 'Nine ')
   	INTO digit_str
   	FROM dual;
   	-- Append the str representation of digit to final result.
   	number_to_word := number_to_word || digit_str;
   END LOOP;
   dbms_output.PUT_LINE('Output: ' ||number_to_word);
END;
Input: 12345
Output: One Two Three Four Five

DECLARE
--Declare variables num, sum_of_digits and remainder of datatype Integer
num  INTEGER;
sum_of_digits INTEGER;
remainder  INTEGER;
BEGIN
num := 123456;
sum_of_digits := 0;
-- Find the sum of digits until original number doesn't become null
WHILE num <> 0 LOOP
 remainder := MOD(num, 10);
 sum_of_digits := sum_of_digits + remainder;
 num := TRUNC(num / 10);
END LOOP;
dbms_output.PUT_LINE('Sum of digits is '|| sum_of_digits);
END;
Input: 9874
Output: 28

DUP_VAL_ON_INDEX

ZERO_DIVIDE

NO_DATA_FOUND

TOO_MANY_ROWS

CURSOR_ALREADY_OPEN

INVALID_NUMBER

INVALID_CURSOR

PROGRAM_ERROR

TIMEOUT _ON_RESOURCE

STORAGE_ERROR

LOGON_DENIED

VALUE_ERROR

etc.

Cursor_Already_Open
Invaid_cursor

• A triggering statement or event.
• A restriction
• An action

DECODE and CASE statements are the same but the difference is CASE is an extended version of DECODE. DECODE won’t allow Decision-making statements in its place.

select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);

This statement returns an error.

CASE is used in PL SQL, but DECODE is used in PL SQL through SQL only.

MERGE combines multiple DML statements into one.

Syntax: merge into table name

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

1. SQL > select to_char(to_date(’29-Mar-2013′,’dd-mon-yyyy’),’J’) as Julian from dual;
2. JULIAN

1. spool/tmp/sql_outtxt
2. select smp_name, smp_id from SMP where dept=’accounts’;
3. spool off;

• PL/SQL table and record TYPE statements
• Procedures and Functions
• Cursors
• Variables ( tables, scalars, records, etc.) and constants
• Exception names and pragmas for relating an error number with an exception
• Cursors

• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkproof utilities

• SQL and PL/SQL time consumption reporting
• Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
• By using the command-line utility you can have multiple interactive analytics reports in HTML format.
• Effective than conventional profilers.

• Allocate individual text messages to a defined row in the PL/SQL table
• It recovers the message text by number
• It automatically replaces your standard Oracle error messages with restricted toggle
• Bulk load message numbers and text from a database table directly

• Null substitution value
• Set of assertion routines
• Miscellaneous utilities
• Set of constants used throughout PL vision
• Pre-defined data types

• PLVprs: It is an extension and the lowest level of string parsing for PL/SQL functionality

• PLVprsps: It is the highest level package of parsing PL/SQL source code into separate atomics. It depends on other parsing packages to get work done.

• Continue processing
• Record and then continue
• Halt processing
• Record and then halt processing

• Display a header for the table
• Display the row numbers for the table values
• Display a prefix before each row of the table

There are two ways to save a message in a table:

• Use add_text procedure to load individual messages with calls

• Use load_from_dbms procedure to load sets of messages from a database table

• module_in
• cor_in
• Last_module_in

PLVcmt and PLVrb are the two packages that manage the transaction processes in PL/SQL applications.

• PLVcmt: It wraps logic and complexity to manage commit processing

• PLVrb: It is a programmatic interface to roll-back activity