PL/SQL - PART 1

PL/SQL - PART 1

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*Variable declaration*/
nm varchar2(20):='Julie';
BEGIN
/*Variable initialization*/
/*nm := 'Julie';*/
DBMS_OUTPUT.PUT_LINE(nm);
DBMS_OUTPUT.PUT_LINE('Name entered is '||nm||'!');
END;
/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*Variable declaration*/
nm varchar2(20);
age number(3);
BEGIN
/*Variable initialization by the user*/
nm := &nm;
age := &age;
DBMS_OUTPUT.PUT_LINE('Name entered is '||nm||'!');
DBMS_OUTPUT.PUT_LINE('Age entered is '||age||'!');
END;
/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*Variable declaration*/
a number(10);
b number(10);
c number(10);
BEGIN
/*Variable initialization by the user*/
a := &a;
b := &b;
c := a + b;
DBMS_OUTPUT.PUT_LINE('Addition of '||a||' and '||b||' is '||c);
END;
/

----------------------------------------------------------------------
IF condition THEN
statements
statements
END IF;

------------------------------------
IF condition THEN
statements
statements
ELSE
statements
statements
END IF;
--------------------------------------
IF condition THEN
statements
statements
ELSIF condition THEN
statements
statements
ELSIF condition THEN
statements
statements
ELSE
statements
statements
END IF;

-------------------------------------
IF condition THEN
IF condition THEN
statements
statements
ELSE
statements
statements
END IF;
ELSE
IF condition THEN
statements
statements
ELSE
statements
statements
END IF;
END IF;
-------------------------------------

/*Enter the age of a person and check whether a person can vote in India or not*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
age number(3);
BEGIN
/*user input*/
age:=&age;
/*condition*/
IF age>=18 THEN
DBMS_OUTPUT.PUT_LINE('You CAN Vote!');
ELSE
DBMS_OUTPUT.PUT_LINE('You CANNOT Vote!');
END IF;
END;
/
-------------------------------------
Enter ages of 3 people. Find the largest age.
AND OR NOT


SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
age1 number(3);
age2 number(3);
age3 number(3);
BEGIN
/*user input*/
age1:=&age;
age2:=&age;
age3:=&age;
/*condition*/
IF age1>age2 AND age1>age3 THEN
DBMS_OUTPUT.PUT_LINE('Age1 is Greater');
ELSIF age2>age1 AND age2>age3 THEN
DBMS_OUTPUT.PUT_LINE('Age2 is Greater');
ELSE
DBMS_OUTPUT.PUT_LINE('Age3 is Greater');
END IF;
END;
/
-------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n number(3);
BEGIN
n:=&n;
IF n > 0 THEN
DBMS_OUTPUT.PUT_LINE(n|| ' is +ve');
ELSIF n < 0 THEN
DBMS_OUTPUT.PUT_LINE(n|| ' is -ve');
ELSE
DBMS_OUTPUT.PUT_LINE(n|| ' is ZeRo');
END IF;
END;
/
----------------------------------------

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n number(10);
BEGIN
/*User initialization*/
n := &n;
/*Checking*/
IF n>0 THEN
DBMS_OUTPUT.PUT_LINE(n||' is +ve');
ELSIF n<0 THEN
DBMS_OUTPUT.PUT_LINE(n||' is -ve');
ELSE
DBMS_OUTPUT.PUT_LINE(n||' is Zero');
END IF;
END;
/

----------------------------------------------------
LOOP Iterative COntrol:

LOOP
statements
statements
increment/decrement of loop counter
EXIT WHEN exitcondition;
END LOOP;
----------------------------------------------
/*Enter a number and display the table of that number*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n number(3);
i number(3);
res number(3);
BEGIN
n:=&n;
i:=1;
LOOP
res := n*i;
DBMS_OUTPUT.PUT_LINE(res);
i:=i+1;
EXIT WHEN i=11;
END LOOP;
END;
/
------------------------------------------------

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n number(10);
i number(10);
res number(10);
BEGIN
/*User initialization*/
n := &n;
i := 1;
LOOP
res := n * i;
DBMS_OUTPUT.PUT_LINE(n||' X '||i||' = '||res);
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
END;
/

-----------------------------------------------
WHILE Loop:

initialize loop counter;
WHILE  condition LOOP
statement
statement
increment/decrement of Loop counter;
END LOOP;
---------------------------------------------------
/*Enter a number and display the table of that number*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n number(3);
i number(3);
res number(3);
BEGIN
n:=&n;
i:=1;
WHILE i<=10 LOOP
res := n*i;
DBMS_OUTPUT.PUT_LINE(res);
i:=i+1;
END LOOP;
END;
/
----------------------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n number(10);
i number(10);
res number(10);
BEGIN
/*User initialization*/
n := &n;
i := 1;
WHILE i<=10 LOOP
res := n * i;
DBMS_OUTPUT.PUT_LINE(n||' X '||i||' = '||res);
i := i + 1;
END LOOP;
END;
/

---------------------------------------------------
FOR Syntax:

FOR loop_counter IN [REVERSE] lowerbound..upperbound LOOP
statement
statement
END LOOP;
------------------------------------------------
/*Enter a number and display the table of that number*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n number(3);
i number(3);
res number(3);
BEGIN
n:=&n;
for i IN 1..10 LOOP
res := n*i;
DBMS_OUTPUT.PUT_LINE(res);
END LOOP;
END;
/
------------------------------------------------
/*Enter a number and display the table of that number*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
n number(3);
i number(3);
res number(3);
BEGIN
n:=&n;
for i IN REVERSE 1..10 LOOP
res := n*i;
DBMS_OUTPUT.PUT_LINE(res);
END LOOP;
END;
/
------------------------------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n number(10);
res number(10);
BEGIN
/*User initialization*/
n := &n;
FOR i IN 1..10 LOOP
res := n * i;
DBMS_OUTPUT.PUT_LINE(n||' X '||i||' = '||res);
END LOOP;
END;
/
----------------------------------------------------

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
n number(10);
res number(10);
BEGIN
/*User initialization*/
n := &n;
FOR i IN REVERSE 1..10 LOOP
res := n * i;
DBMS_OUTPUT.PUT_LINE(n||' X '||i||' = '||res);
END LOOP;
END;
/
----------------------------------------------------

->input roll, name, m1,m2,m3,m4,m5
+
find total & per
+
check grade

->factorial of a number
------------------------------------------------------