Disadvantages of SQL:
*********************
-->It supports to execute only one command at a time.It will not suppoort block of statements.
-->It will not support high level language features like variables,constants,conditional and loops.
-->It will not support error handling.
-->It will not store the code in database.
-->It will not support user defined datatypes.
PL/SQL:
*******
-->PL/SQL stands for procedural language which is an extension of SQL.
-->SQL is a non procedural language where as PL/SQL is a procedural language.
-->In SQL every query statement is compiling and executing individually.So that no.of compilations are increased and reduce performance of database.
-->In PL/SQL all SQL queries are grouped into a single block and which will compile and execute only one time.So that it will reduce no.of compilations and improve performance of database.
-->It is a procedural language which is used to define our own logics.
-->It is used to execute block of statements at a time and increase the performance.
FEATURES OF PL/SQL:
*******************
-->It supports to execute a block of statements as one unit.
-->Supporting conditional and loop statements.
-->Supporting Reusablility.
-->Supports error handling using exceptions
-->Supports to define composite datatypes.
-->Provding security all programs are saved in database and authorized user can access the programs.
-->Supporting modular programing i.e In a PL/SQL a big program can be divided into small modules which are called as stored procedures and stored functions.
-->Enhancement(Existing programs are enhansable for future requirements.)
-->It improves performance.
PL/SQL Architecture:
********************
PL/SQL is block structure programing language.Which is having the following two engines.
1.SQL ENGINE
2.PL/SQL ENGINE.
-->Whenever we are sumbmitting PL/SQL block into oracle server then all SQL statements(Queries) are seperated and executing by SQL query executor with in SQL ENGINE.
Where as PL/SQL statements(CODE) are seperated and executing by PL/SQL executor within PL/SQL Engine.
BLOCK:
******
-->It is one of the area which is used to write programing logic.
-->A block is a set of statements which are compile and executed by oracle as a single unit.
-->There are two types of blocks supported by PL/SQL
a)Anonymous Blocks
b)Named Blocks
Anonymous Blocks:
*****************
-->These blocks doesn't have a name (Nameless block)
-->These blocks are not stored in database
-->It has no name and it can not be called.
-->It contains 3 sections.
a)Declare Section
b)Executable Section
c)Exception Section
Declare Section:
****************
-->This block starts with "DECLARE" statement.
-->It is one of the section which is used to declare variables,constants,cursors and so on.
-->It is optional section
Executable Section:
*******************
-->This block starts with "BEGIN" statement and ends with "END" statement.
-->It is one of the section which is used to write a program coding.
-->Implementing SQL statements(SQL) and logical code of a program(PL/SQL)
-->It is mandatory section
Exception Section:
******************
-->
-->It is one of the section which is used to handle errors at runtime.
-->It is optional section.
STRUCTURE:
===========
DECLARE
< VARIABLES,CURSOR,UD EXCEPTIONS>;
BEGIN
<EXECUTABLE STATEMENTS>
< WRITING SQL STATEMENTS>;
< PL/SQL LOGICAL CODE>;
EXCEPTION
< HANDLING EXCEPTIONS>;
END;
/
EX:
***
DECLARE --OPTIONAL
-------
------
BEGIN --MANDATORY
-----
-----
EXCEPTION --OPTIONAL
END; --MANDATORY
Named Blocks:
*************
-->These blocks have a name(Named Block)
-->These blocks are stored in database.
EX:
***
Procedures
Functions
Packages
Triggers
Variable:
*********
-->It is one of the memory location which is used to store the data.
-->Generally we are declaring variables in declare section of the PL/SQL block.
-->These are supported default and notnull.
Syntax:
*******
DECLARE
VARIABLENAME DATATYPE[<SIZE>)];
EX:
****
a number(20);
b number(20);
c varchar2(30);
d date;
Assignment Operator(:=):
************************
-->We are storing a value into variable
-->It is declared in either declare section or executable section.
Syntax:
*******
Variablename :=value;
EX:
*****
A:=50;
B:='MOHAN';
Display message or variable value:
**********************************
SYNTAX:
********
DBMS_OUTPUT.PUT_LINE(<VARIABLE NAME > (OR) '<UD MESSAGE>');
DBMS_OUTPUT -->PACKAGE NAME
PUT_LINE -->PROCEDURE NAME
EX:
***
DBMS_OUTPUT.PUT_LINE(A);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL');
DBMS_OUTPUT.PUT_LINE('ADDITION OF TWO NUMBERS'||C);
EX1:
****
TO PRINT "WELCOME TO PL/SQL" STATEMENT.
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL');
END;
NOTE:
*****
THE ABOVE PROGRAM WILL NOT DISPLAY THE OUTPUT OF A PL/SQL PROGRAM.IF ORACLE SERVER WANT TO DISPLAY OUTPUT OF A PL/SQL PROGRAM THEN WE USE THE FOLLOWING SYNTAX,
SYNTAX:
*******
SET SERVEROUTPUT OFF / ON;
show server output;
set serveroutput on;
Here,
OFF : OUTPUT IS NOT DISPLAY
ON : OUTPUT IS DISPLAY
SET SERVEROUTPUT ON;
Here,
:= --> ASSIGNMENT OPERATOR IN PL/SQL
= --> COMPARISION OPERATOS IN PL/SQL
Constant:
*********
-->The value doesn't change.
-->Value is fixed or default value.
PL/SQL OPERATORS:
*****************
1.ARITHMETICAL OPERATORS:
+
-
*
/
2.RELATIONAL OPERATORS:
<
>
<=
>=
=
!=
3.LOGICAL OPERATORS
AND
OR
NOT
4.SPECIAL OPERATORS:
IN - SPECIFIC VALUE
NOT IN - EXCEPT SPECIFIC VALUE
BETWEEN - TO SELECT RANGE
NOT BETWEEN - EXCEPT RANGE
LIKE - STARTING LETTER 'S'
NOT LIKE - EXCEPT
IS NULL - TO SELECT NULL VALUE
IS NOT NULL - TO SELECT NOT NULL VALUES.
5.SET OPERATORS:
UNION
UNION ALL
INTERSECT
MINUS
PLSQL (ONLY)
6. Assignment operator:
:= ASSIGNMENT OPERATOR
A:=10;
NAME:='RAJA';
d1:='22-FEB-18'
7.CONCATENATION OPERATOR (||)(TWO PIPE LINE CHARACTER)
=========================================================================
--First Program of PL/SQL
EX:
***
BEGIN
END;
/
--THE ABOVE PROGRAM IT WILL THROW THE ERROR.
EX1:
***
BEGIN
NULL; --NULL STATEMENT
END;
/
EX2:
****
--PRINT OUTPUT
BEGIN
DBMS_OUTPUT.PUT_LINE('THIS IS THE FIRST PROGRAM FOR PLSQL');
END;
/
EX3:
****
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL');
DBMS_OUTPUT.PUT_LINE(678);
DBMS_OUTPUT.PUT_LINE(SYSDATE);
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
DBMS_OUTPUT.PUT_LINE(USER);
DBMS_OUTPUT.PUT_LINE(10+20-15);
END;
EX4:
****
-->TO PRINT VARIABLES ?
DECLARE
X NUMBER(10); --VARIABLE DECLARATION
Y NUMBER(10); --VARIABLE DECLARATION
BEGIN
DBMS_OUTPUT.PUT_LINE('X');
DBMS_OUTPUT.PUT_LINE('Y');
END;
/
EX5:
****
-->DECLARE VARIABLE AND ASSIGN VALUE TO VARIABLE
DECLARE
A NUMBER; --VARIABLE DECLARATION
BEGIN
A:=20; --Assign value to variable using assignment operator in executable section
DBMS_OUTPUT.PUT_LINE(A); --Print the output
END;
/
EX6:
****
---Initialization using Assignment Operator(:=)
DECLARE
A NUMBER:=25; --variable declaration and Initialization in declare section
B NUMBER:=55; --variable declaration and Initialization in declare section
BEGIN
DBMS_OUTPUT.PUT_LINE(A+B);
END;
/
EX7:
****
DECLARE
A NUMBER := 25;
B NUMBER :=75;
BEGIN
A:=125; --PREVIOUS VALE WILL BE OVERRIDE
B:=175;
DBMS_OUTPUT.PUT_LINE(A+B);
END;
/
EX8:
****
--INITALIZATION USING DEFAULT KEYWORD
DECLARE
A NUMBER DEFAULT 25;
B NUMBER DEFAULT 75;
BEGIN
DBMS_OUTPUT.PUT_LINE(A+B);
END;
/
EX9:
****
DECLARE
A NUMBER DEFAULT 25;
B NUMBER DEFAULT 75;
BEGIN
A:=35; --PREVIOUS VALE WILL BE OVERRIDE
B:=50; --PREVIOUS VALE WILL BE OVERRIDE
DBMS_OUTPUT.PUT_LINE(A+B);
END;
/
EX10:
*****
--CONSTANTS
DECLARE
C1 CONSTANT NUMBER :=25; --Value of constant cannot be modified through out the program
C2 CONSTANT NUMBER :=45; --Value of constant cannot be modified through out the program
BEGIN
DBMS_OUTPUT.PUT_LINE(C1+C2);
END;
/
EX11:
*****
-->WRITE A PL/SQL PROGRAM TO PRINT ADDITION OF TWO NUMBERS
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=25;
B:=40;
C:=A+B;
DBMS_OUTPUT.PUT_LINE('ADDITION OF TWO NUMBER IS : '||C);
END;
/
EX: 12:
*******
DECLARE
--VARIABLE DECALRATION
A NUMBER(5);
B NUMBER;
C NUMBER;
BEGIN
A :=200;
B :=50;
C :=A+B;
DBMS_OUTPUT.PUT_LINE('A VALUE IS : '||A);
DBMS_OUTPUT.PUT_LINE('B VALUE IS : '||B);
DBMS_OUTPUT.PUT_LINE('C VALUE IS :'||C);
DBMS_OUTPUT.PUT_LINE('A VALUE IS : '||A||' '||'B VALUE IS : '||B||' '||'ADDITION OF TWO NUMBERS IS : '||C);
DBMS_OUTPUT.PUT_LINE('ADDITION OF TWO NUMBERS IS : '||C);
DBMS_OUTPUT.PUT_LINE(A+B);
END;
/
EX13:
****
-->TO PRINT SUM OF TWO NUMBERS AT RUNTIME ?
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=:A; (: --Bind parameter)
B:=:B;
C:=A+B;
DBMS_OUTPUT.PUT_LINE('ADDITION OF TWO NUMBER IS : '||C);
END;
-->TO PRINT SUM OF TWO NUMBERS AT RUNTIME ?
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=&A; (& --substitution parameter)
B:=&B;
C:=A+B;
DBMS_OUTPUT.PUT_LINE('ADDITION OF TWO NUMBER IS : '||C);
END;
/
EX14:
******
-->WRITE A PL/SQL PROGRAM TO CALCULATE ALL ARITHMETICAL OPERATORS.
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
D NUMBER;
E NUMBER;
F NUMBER;
BEGIN
A:=&A;
B:=&B;
C:=A+B;
D:=A-B;
E:=A*B;
F:=A/B;
DBMS_OUTPUT.PUT_LINE('Addition of two numbers is :'||C);
DBMS_OUTPUT.PUT_LINE('Subtraction of two numbers is :'||D);
DBMS_OUTPUT.PUT_LINE('Multiplication of two numbers is :'||E);
DBMS_OUTPUT.PUT_LINE('Division of two numbers is :'||F);
END;
/
EX15:
****
-->Write a plsql program to calculate the length of the string
DECLARE
NAME VARCHAR2(20);
L NUMBER;
BEGIN
NAME:='&NAME';
L:=LENGTH(NAME);
DBMS_OUTPUT.PUT_LINE('LENGTH OF NAME IS :'||L);
END;
EX15:
****
-->Write a pl/sql program to display the swapping of two numbers
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=&A;
B:=&B;
C:=A;
A:=B;
B:=C;
DBMS_OUTPUT.PUT_LINE('After Swapping');
DBMS_OUTPUT.PUT_LINE('A='||A);
DBMS_OUTPUT.PUT_LINE('B='||B);
END;
A --100
B --200
C :=A; --100 --C
A :=B; --200 --A
B :=C; --100 --B
EX16:
*****
-->Write a plsql program to calcluate simple interest
DECLARE
AMOUNT NUMBER:=&AMOUNT;
N NUMBER:=&YEAR;
R NUMBER:=&RATE;
SI NUMBER;
BEGIN
SI:=AMOUNT+(AMOUNT*N*R)/100;
DBMS_OUTPUT.PUT_LINE('Simple Interest is :'||SI);
END;
/
--NOT NULL
declare
a number not null:=200;
begin
--a :=null;
dbms_output.put_line(a);
end;
No comments:
Post a Comment