PL/SQL Control Structures define as follow two ways
Ø Conditional statements
Ø Loops
Conditional Statements
There are three forms of IF statements
IF-THEN-END IF;
IF-THEN-ELSE-END IF;
IF-THEN-ELSIF-END IF;
Syntax:
IF condition THEN
Statements;
ELSIF condition THEN
Statements;
ELSE
Statements;
END IF;
Note:- You can also have a IF within a IF called as Nested IF.
Example: IF..ELSE..END IF
-- Block to demonstrate IF...ELSE...END IF
DECLARE
vname emp.ename%TYPE;
veno emp.empno%TYPE := &emp_num;
vsal emp.sal%TYPE;
BEGIN
SELECT ename, sal
INTO vname, vsal
FROM emp
WHERE empno = veno;
-- Displays an appropriate message if salary is greater than 1500
IF vsal > 1500
THEN
DBMS_OUTPUT.put_line(vname || ' earns a salary greater than 1500');
--Else it shows the employee name whose sal is <1500
ELSE
DBMS_OUTPUT.put_line(vname || ' earns a salary not greater than 1500');
END IF;
DBMS_OUTPUT.put_line('This line executes irrespective of the condition');
END;
Example: Nested IF
DECLARE
firstno NUMBER (5) := &firstno;
secondno NUMBER (5) := &secondno;
BEGIN
IF firstno IS NULL OR secondno IS NULL
THEN
DBMS_OUTPUT.put_line('Improper Input');
ELSE
IF firstno = secondno
THEN
DBMS_OUTPUT.put_line('The numbers are equal');
ELSE
IF firstno > secondno
THEN
DBMS_OUTPUT.put_line('The first no. is greater');
ELSE
DBMS_OUTPUT.put_line('The second no. is greater');
END IF;
END IF;
END IF;
END;
/
LOOP Statements
Simple Loops
WHILE Loops
FOR Loops
Simple Loops
LOOP
Sequence_of_statements;
END LOOP;
Add EXIT statement to exit from the loop
WHILE Loops
WHILE condition
LOOP
Statements;
END LOOP;
Condition is evaluated before each iteration of the loop
Eg. # 1
DECLARE
v_count NUMBER (5) := &count;
-- If you enter count as 10 program will give all
-- even and odd numbers from 1 to 10.
v_even_count NUMBER (5) := 0;
v_odd_count NUMBER (5) := 0;
BEGIN
WHILE v_count > 0
LOOP
IF MOD (v_count, 2) = 0
THEN
v_even_count := v_even_count + 1;
ELSE
v_odd_count := v_odd_count + 1;
END IF;
v_count := v_count - 1;
END LOOP;
DBMS_OUTPUT.put_line('Even Nos are :' || v_even_count);
DBMS_OUTPUT.put_line('Odd Nos are :' || v_odd_count);
END;
LOOP Statement: Example
DECLARE
v_i NUMBER (2) := 1;
BEGIN
LOOP
DBMS_OUTPUT.put_line('Value : ' || v_i);
EXIT WHEN v_i = 10;
v_i := v_i + 1;
END LOOP;
END;
WHILE Loop: Example
DECLARE
v_i NUMBER (2) := 1;
BEGIN
WHILE (v_i <= 10)
LOOP
DBMS_OUTPUT.put_line('Value : ' || v_i);
v_i := v_i + 1;
END LOOP;
END;
FOR Loops
The number of iterations for simple loops and WHILE loops is not known in advance, it depends on the loop condition. Numeric FOR loops, on the other hand, have defined number of iterations.
FOR counter IN [REVERSE] low_bound .. high_bound
LOOP
Statements;
END LOOP;
Where:
counter: is an implicitly declared integer whose value automatically increases or decreases by 1 on each iteration
REVERSE: causes the counter to decrement from upper bound to lower bound
low_bound: specifies the lower bound for the range of counter values
high_bound: specifies the upper bound for the range of counter values
The following loop will print the number series from 1 to 10.
BEGIN
FOR v_count IN 1 .. 10
LOOP
DBMS_OUTPUT.put_line('Count is :' || v_count);
END LOOP;
END;
The following loop will print the number series from 10 to 1.
BEGIN
FOR v_count IN REVERSE 1 .. 10
LOOP
DBMS_OUTPUT.put_line('Count is :' || v_count);
END LOOP;
END;
FOR Loop: Example
BEGIN
FOR v_i IN 1 .. 10
/* The LOOP VARIABLE v_i of type BINARY_INTEGER is declared automatically */
LOOP
DBMS_OUTPUT.put_line('Value : ' || v_i);
END LOOP;
END;
For Loop with EXIT condition
DECLARE
myno NUMBER (5) := &myno;
counter NUMBER (5) := 1;
BEGIN
FOR i IN 2 .. myno - 1
LOOP
counter := counter + 1;
EXIT WHEN myno MOD i = 0;
END LOOP;
IF counter = myno - 1
THEN
DBMS_OUTPUT.put_line
('The given
number is prime');
ELSE
DBMS_OUTPUT.put_line
('The given number is not
a prime number');
END IF;
END;
/
Salve
ReplyDeleteSeems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on Pl/sql control structure!
I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.
Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.
Is this even possible?
Thanks for any help you can give.
Wayne
Appreciate your effort for making such useful blogs and helping the community.
Merci
Salaam,
ReplyDeleteHot! That was HOT! Glued to the PL/SQL Control Structures your proficiency and style!
I have data in the table like this
https://www.yahoo.com/server
https://www..google.com/test/server
I want get between "// and / and the output should be like this
www.yahoo.com
www..google.com
Can anybody provide me sql to get above output?
By the way do you have any YouTube videos, would love to watch it . I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
MuchasGracias,
Radhey
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete