Welcome

PL SQL Tutorial


PL SQL Programming

Procedural Extension to SQL Programming, is PL-SQL Programming.

Pl-SQL Program Structure

     There are 3 sections 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

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

ADS