Introduction to PL-SQL
Oracle uses SQL as its primary language for solving any type of Queries. As SQL is a fourth generation language, it is very easy to use and flexible for the user. Even though SQL is very easier and flexible, some complex queries will not be solved by SQL. To solve the complex queries that are not solved by SQL Oracle introduces a procedural or Third generation language called PL/SQL (Procedural Structure Query Language).
PL/Sql Featrues :
PL/SQL is a third generation or procedural language.
Most of the PL/SQL language features are taken from the object oriented programming language ADA.
As it is a procedural language, it provides the features like condition checking, looping, error handling etc.
PL/SQL Programming is called as a Block structured programming.
Data Types :
The keyword of a language that is used to specify the type of value that can be stored in a variable is called as Datatype. Data types in PL/SQL are as follows.
Number
Varchar2
Char
Long
Long raw
Raw
Date
Binary_Integer
Pls_Integer
Boolean
Float
Int
Binary_Integer, Pls_Integer and int are used to store integer values. Although their purpose is same, internal memory representation is different. Float is used to store real values, number is used to store both integer and float values and Boolean is used to store True or False.
Variable : An identifier used to identify a location in memory to store and access constants is called as a variable.
Declaration : A statement in a language that associates a variables with its corresponding data type is called as Declaration statement. PL/SQL uses the following syntax for declaring a variable.
Syntax : Varname Datatype(Size) [ Constant ] [ Default / := Value] ;
Example : Sno Number(5);
Sno Number(5) := 0;
Sno Number(5) Default 0;
Sno Number(5) Constant := 20;
Operators In Pl/Sql :
Arithmetic Operators : +, -, *, /, ** (Exponential operator)
Relational Operators : <, >, <=, >=, != / <> / ~=
Comparison Operator : =
Assignment Operator : :=
Concatenation Operator :
Comments in PL/SQL : -- Single Line Comment
/*…*/ Multiple Line Comment
Note : For the Output to be printed on Screen as a result of any program/procedure, you need to …
SQL> Set Severoutput On
PROGRAMMING LOGICS :
In any language programming logic is of three types.
1. Sequence 2. Selection 3. Iteration
SEQUENCE : Executing the program instructions from start to end in a sequence without skipping any statements and without repeated execution of any statements is called as a Sequence.
SELECTION : Executing the program instructions by selection based on a condition is called as Selection. Selection is again three types.
SINGLE ALTERNATIVE : Specify the statement is to be executed when the given condition is true without specifying the statement to be executed when condition is false is called as a Single Alternative.
Syntax : IF Condition THEN
Statements
END IF;
DOUBLE ALTERNATIVE : Specifying the statements to be executed for both true and false stages of a condition is called as double Alternative.
Syntax : IF Condition THEN
Statemetns
Else
Statements
END IF;
MULTIPLE ALTERNATIVE : Implementing the selection logic with more than one condition is called as multiple alternative.
Syntax : IF Condition THEN
Statements
ELSIF Condition THEN
Statements
ELSIF Condition THEN
Statements
:
:
:
ELSE
Statements
END IF;
ITERATION : Executing the program instructions repeatedly until the given condition is false is called as ITERATION or looping.
WHILE :
Syntax : VAR INIT;
WHILE CONDITION
LOOP
Statements
INC/DEC VAR (Increase or Decrease variable )
END LOOP;
LOOP :
Syntax : VAR INIT;
LOOP
Statements
INC/DEC VAR
EXIT WHEN Condition
END LOOP;
FOR :
Syntax : FOR VAR IN SVAL..EVAL ( Starting Value .. Ending Value )
LOOP
Statements
END LOOP;
REVERSE FOR :
Syntax : FOR VAR IN REVERSE SVAL..EVAL
LOOP
Statements
END LOOP;
PL / SQL BLOCKS :
Basic unit of PL/SQL program is called as a BLOCK. A PL/SQL program is a collection of more than one Block. PL/SQL Blocks are classified into four types.
1. ANONYMOUS BLOCKS : A PL/SQL block without a name is called as Anonymous Block.
2. NAMED BLOCKS : A PL/SQL Block is created with a name is called as a NAMED BLOCK.
3. SUB PROGRAMS : Functions, Procedures and Packages of PL/SQL are called as sub
programs.
4. TRIGGERS : Triggers are PL/SQL blocks that are given a name and stored within the database like subprograms. But Triggers are executed implicitly by Oracle depending on
Triggering event without the intervention of user.
ANONYMOUS BLOCKS :
A PL/SQL Block without a name is called as an Anonymous Block. These blocks are generally created once and used once as they are not stored in the database. Syntax for an anonymous block is …
DECLARE
VAR DECLARATIONS
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END;
DECLARE section is used to declare the variables and constants required by the program. BEGIN section is used to write the actual code of the program, exception section is used to write error handling code and the statement END represents the end of the program.
à Write a PL/SQL block that accepts two numbers from user and prints the sum of those numbers.
DECLARE
A NUMBER(5):=&A;
B NUMBER(5):=&B;
C NUMBER(10);
BEGIN
C := A + B;
DBMS_OUTPUT.PUT_LINE( ‘SUM =’ C );
END;
à Write a PL/SQL block that accepts two numbers from user and prints the sum of the two numbers.
DECLARE
A NUMBER(5):=&A;
B NUMBER(5):=&B;
C NUMBER(5);
BEGIN
C:=A+B;
DBMS_OUTPUT.PUT_LINE('SUM IS : 'C);
END;
à Write a PL/SQL block to accept marks in three subjects for a student and display his marks and average marks.
DECLARE
A NUMBER(3):=&A;
B NUMBER(3):=&B;
C NUMBER(3):=&C;
T NUMBER(3);
V NUMBER(5,2);
BEGIN
T := A + B + C;
V := T/3;
DBMS_OUTPUT.PUT_LINE('TOTAL IS : ' T );
DBMS_OUTPUT.PUT_LINE('AVERAGE IS : ' V );
END;
à Write a PL/SQL block to determine whether a given number is even or odd.
DECLARE
A NUMBER(10):=&A;
BEGIN
IF MOD(A,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE( A ' IS AN EVEN NUMBER' );
ELSE
DBMS_OUTPUT.PUT_LINE( A ' IS ODD NUMBER’ );
END IF;
END;
à Write a PL/SQL block to find the big number among the given three numbers.
DECLARE
A NUMBER(10):=&A;
B NUMBER(10):=&B;
C NUMBER(10):=&C;
BEGIN
IF (A>B) AND (A>C) THEN
DBMS_OUTPUT.PUT_LINE( A ' IS THE BIGGEST NUMBER.' );
ELSIF (B>A) AND (B>C) THEN
DBMS_OUTPUT.PUT_LINE( B ' IS THE BIGGEST NUMBER ' );
ELSE
DBMS_OUTPUT.PUT_LINE( C ' IS THE BIGGEST NUMBER ' );
END IF;
END;
à Write a PL/SQL block to print 1 to 10 natural numbers.
DECLARE
A NUMBER(10):=1;
BEGIN
WHILE (A<10)
LOOP
DBMS_OUTPUT.PUT_LINE( A );
A := A + 1;
END LOOP;
END;
à The Same example with FOR Loop
DECLARE
A NUMBER(10):=1;
BEGIN
FOR A IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE( A );
END LOOP;
END;
à The Same example using LOOP
DECLARE
A NUMBER(10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( A );
A := A + 1;
EXIT WHEN A > 10;
END LOOP;
END;
DECLARE
M NUMBER(37);
A NUMBER(37):=&A;
R NUMBER(37):=0;
BEGIN
WHILE A> 0
LOOP
M := MOD(A,10);
R := (R*10)+M;
A := TRUNC(A/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE(R);
END;
à The same program with LOOP.
DECLARE
M NUMBER(5);
A NUMBER(5):=&A;
R NUMBER(5):=0;
BEGIN
LOOP
M=MOD(A,10);
R=R*10+M;
A= TRUNC(A/10);
EXIT WHEN A<>0;
END LOOP;
DBMS_OUTPUT.PUT_LINE(R);
END;
à Write a program to reverse a string.
DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
BEGIN
LEN:=LENGTH(STR);
WHILE(LEN>0)
LOOP
REV:=REVSUBSTR(STR,LEN,1);
LEN := LEN-1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;
à The same program using LOOP
DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
BEGIN
LEN:=LENGTH(STR);
LOOP
REV:=REVSUBSTR(STR,LEN,1);
LEN := LEN-1;
EXIT WHEN LEN=0;
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;
à The same using FOR LOOP
DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
i NUMBER(10);
BEGIN
LEN:=LENGTH(STR);
FOR I IN REVERSE 1..LEN
LOOP
REV:=REVSUBSTR(STR,I,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;
NAMED BLOCKS :
The PL/SQL blocks that are given a label prior to the declaration section are called as NAMED BLOCKS. Like ANANYMOUS block NAMED blocks are also not stored within the database. Hence these are also created once and used once.
Syntax :
<
No comments:
Post a Comment