Introduction

PL SQL Programming

Variables

Control Statements

Loops

Exceptions

Cursors

Stored Procedures

Stored Functions

PL SQL Triggers

Packages

DML Operations in PL/SQL

PL SQL Collections

PL SQL BULK Collect

Welcome

PL SQL Tutorial

PL SQL Programming

PL/SQL is Procedural Language Extension to SQL Programming. PL/SQL Programs consists of followng programming elements.

  • PL/SQL Charset
  • Identifiers
  • Literals
  • Datetime Inervals
  • Comments
  • Pragmas
  • Statements
  • Block Structure

PL-SQL Program Structure

     There are 3 sections/blocks in PL-SQL Programming

  • DECLARE section(optional)

    •     This is where all variables are declared. Then these variables can be used in Execution Section or Exception Section.

  • EXECUTION SECTION(mandatory)

    •     PL-SQL Executable Code written in this section

  • EXCEPTION SECTION(optional)

    •     EXCEPTION Section is part of EXECUTION SECTION and it is Optional which has Exception Handlers, Exception section allows us to let the program run without interruption.Otherwise abruptly terminates the application. 

		DECLARE  
			  VAR1  TYPE;
			  VAR2  TYPE;
			  VARN  TYPE;
		
		BEGIN  
				PL/SQL  Code .....
		EXCEPTION
			WHEN  EX1 THEN
			WHEN  EX2 THEN
			WHEN  OTHERS THEN
		END;

First Program in PL-SQL

			1. SET SERVEROUTPUT ON;
			
			2.BEGIN
			3.	DBMS_OUTPUT.PUT_LINE(' Hello World!');
			4.END;
			5. /

Line 1: Sql*Plus Command Set Serveroutput ON; default it is set to OFF. This command allows SQL*PLUS to display output from PL-SQL Code.
Line 2-4: PL/SQL Execution Section. It should start with BEGIN keyword, end with END keyword. DBMS_OUTPUT is a built-in package in Oracle Server. Has methods and procedures for accepting user input or display output.
PUT_LINE procedure Outputs the data. It accepts any type of Data. Here passing 'Hello World!' as a Input to the procedure.Same thing Displayed on the Terminal.
Line 5: Execute above PL/SQL Block of code.

Comments in PL/SQL

   PL/SQL Comments can be single line or multiline comments.
Single line comments starts with -- ,
Multiline comments /* PL-SQL statements ... */

PL-SQL engine ignores these comment lines, Mainly used for better readability, & proper maintaining code by you and other programmers, and also for documentation Purpose.

Single Line comments

				-- function returns squre of the number
				
				 create function sqr(n number)
				 as
				 ............

Multi Line comments

				
				/* 
				      This package has procedures, functions and cursors
				      
				      related to customer orders.
				*/
				
				 create package pkg_customer_orders
				 as
				 ............

Statements

    PL-SQL Program is composed of one or more logical statements. Each statement is seperated by semicolon(;) delimiter

PL/SQL Charset

     PL/SQL language are constructed from digits,alphabets,symbols and whitespaces

Identifiers

       Identifier is a name given to a PL/SQL Object.

The following are the identifiers in PL/SQL Code
  • Variables
  • Cursor
  • Exception
  • Keywords
  • Label
  • Procedure,Package,function,trigger etc.,

Please refer variables section Variables

Literals

     A Literal is a value in the PL/SQL code.

  • Numeric Literals

    •      Integers and Real Numbers are considers as a Numeric Literals. day := 7 here 7 is a Integer Numeric Literal, temp := 98.99 here 98.99 is a real number Literal.

  • String Literals

    •     String Literals are specified in single quotes. city:='NEW YORK', here 'NEW YORK' is a String Literal, String within single quotes are case sensitive. In the Sense 'New York' and 'New york' both are different Strings.

  • Datetime interval Literals
  • Boolean Literals

    •     BOOLEAN Literals accept TRUE,FALSE,NULL values.Any boolean variable contain one of these values.

Pragmas

    Pragma means an action. An action is given it to a PL/SQL compiler.There are six types of pragmas in PL/SQL

  • AUTONOMOUS_TRANSACTION

    •     Tells the PL/SQL runtime engine transactions within nested block should work independently with outer block transactions.

  • EXCEPTION_INIT

    •     Associate exception number with the identifier

  • INLINE

    •    Tells the compiler whether calls to a subprogram should be replaced with a copy of the subprogram

  • DETERMINISTIC,PARALLEL_ENABLE
  • SERIALLY_REUSABLE

    •     Tells PL/SQL engine package data should not be serialized/persist between references to that data.

  • UDF

    •     Tells the compiler that you intend for your user-defined function to be invoked primarily from SQL rather than PL/SQL

ADS