PL/SQL - Part 4
PL / SQL (Programming Language SQL) | (T-SQL ) (Transactional-SQL):
SQL limitations:
->SQL is capable of working with Tables/Views.
->SQL is not capable of Handling Procedural concepts (Arithmetic ops, Decision making, Iterations / Loops, Modularization)
->There are situations where to execute a task we need to write multiple Queries.
In such cases, the multiple queries get executed by the Oracle Engine, query by query.
->No Exception (Run time errors) Handling capability. ???
->No capability to take user input.
->SQL does not support the concept of variables.
------------------------------------------
PL/SQL (Programming Language of SQL)
->Combines Procedural capabilities (Arithmetic ops, Decision making, Iterations / Loops, Modularization) & SQL queries.
->PL/SQL supports the concept of variables (temp. storage)
->PL/SQL code block can accomodate multiple SQL Statements & gets executed as a single unit.
->PL/SQL has exception Handling capability.
->PL/SQL has the capability to take user input
-------------------------------------------
PL/SQL Code block Syntax:
-----------------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
variable declaration
comes here
BEGIN
Procedural &
SQL statements
come here
EXCEPTION
Exception handling
done here
END;
-----------------------------------------
:= assignment op
= comparison op
/* comment */
user input in PL/SQL:
varname := &varname;
to display a variable's value:
DBMS_OUTPUT.PUT_LINE(varname);
to display a message
DBMS_OUTPUT.PUT_LINE('message');
to display a variable's value along with a message:
DBMS_OUTPUT.PUT_LINE('message' || varname);
----------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*EMPTY*/
BEGIN
DBMS_OUTPUT.PUT_LINE('hELLO wORLD');
END;
/
----------------------------------------
Enter two numbers, add them and show the result
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n1 number(4);
n2 number(4);
n3 number(5);
BEGIN
/*user input*/
n1:=&n1;
n2:=&n2;
/*computation*/
n3:=n1+n2;
/*display the result*/
DBMS_OUTPUT.PUT_LINE(n3);
DBMS_OUTPUT.PUT_LINE('Addition is '||n3);
DBMS_OUTPUT.PUT_LINE('Addition of '||n1||' and '||n2||' is '||n3);
END;
/
----------------------------------------------------------------------
Enter 3 numbers and find the average of the 3 nos.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n1 number(5);
n2 number(5);
n3 number(5);
n4 number(6);
BEGIN
n1:=&n1;
n2:=&n2;
n3:=&n3;
n4:=(n1+n2+n3)/3;
DBMS_OUTPUT.PUT_LINE('Average of '||n1||','||n2||','||n3||' is '||n4);
END;
/
----------------------------------------------------------------------
Enter name and age from the user aND display
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
name varchar2(20);
age number(2);
dob date;
BEGIN
name:=&name;
age:=&age;
dob:=&dob;
DBMS_OUTPUT.PUT_LINE('Your name: '||name||' and your age: '||age||' and dob: '||dob);
END;
/
----------------------------------------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*Variable Declaration*/
n1 number;
n2 number;
res number;
BEGIN
/*Take user input*/
n1:=&n1;
n2:=&n2;
/*Compute*/
res:=n1+n2;
/*Display the result*/
DBMS_OUTPUT.PUT_LINE('Sum of '||n1||' and '||n2||' is '||res||' !');
END;
/
------------------------------------------------------------------------------------------------------------------
0 Comments