PL SQL Tutorial
PL/SQL is Procedural Language Extension to SQL Programming. PL/SQL Programs consists of followng programming elements.
There are 3 sections/blocks in PL-SQL Programming
This is where all variables are declared. Then these variables can be used in Execution Section or Exception Section.
PL-SQL Executable Code written in this section
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;
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.|
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 ............
PL-SQL Program is composed of one or more logical statements. Each statement is seperated by semicolon(;) delimiter
PL/SQL language are constructed from digits,alphabets,symbols and whitespaces
Identifier is a name given to a PL/SQL Object.The following are the identifiers in PL/SQL Code
Please refer variables section Variables
A Literal is a value in the PL/SQL code.
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 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.
BOOLEAN Literals accept TRUE,FALSE,NULL values.Any boolean variable contain one of these values.
Pragma means an action. An action is given it to a PL/SQL compiler.There are six types of pragmas in PL/SQL
Tells the PL/SQL runtime engine transactions within nested block should work independently with outer block transactions.
Associate exception number with the identifier
Tells the compiler whether calls to a subprogram should be replaced with a copy of the subprogram
Tells PL/SQL engine package data should not be serialized/persist between references to that data.
Tells the compiler that you intend for your user-defined function to be invoked primarily from SQL rather than PL/SQL