KEMBAR78
Condition and Control Statements | PDF | Pl/Sql | Control Flow
Scribd
Upload
33 views22 pages

Condition and Control Statements

This document provides an introduction to Oracle PL/SQL including its basic structure, variables, control structures like conditional statements and loops, and SQL queries in PL/SQL blocks. Key topics covered include declaring variables, data types, conditional logic with IF/ELSE statements, looping with FOR and WHILE loops, and using SELECT statements to retrieve data into variables.

Uploaded by

ajithram275
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views22 pages

Condition and Control Statements

This document provides an introduction to Oracle PL/SQL including its basic structure, variables, control structures like conditional statements and loops, and SQL queries in PL/SQL blocks. Key topics covered include declaring variables, data types, conditional logic with IF/ELSE statements, looping with FOR and WHILE loops, and using SELECT statements to retrieve data into variables.

Uploaded by

ajithram275
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 22

Oracle PL/SQL Introduction with Conditional statements AND For

loop condition & %TYPE AND %ROWTYPE

PL/SQL
 Procedural language extension to sql with design feature
of programing language
 Data manipulation and query statements of sql are
included with in procedural units of code.
 It is mainly used to reduce the network traffic
 It groups the set of logical statement in to block
 pl/sql statement is used to group the set of sql
statement into single block and send the entire block to
the server in a single call (here set of sql statement
can execute at a single time)
PL/SQL BLOCK STRUCTURE

DECLARE --> OPTIONAL


--VARIABLE DECLARATION
--CURSOR DECLARATION
--USER DEFINE EXCEPTION
BEGIN --> MANDATORY
--SQL STATEMENNTS
--PLSQL STATEMENTS (APPLICATION AND BUSINESS LOGIC)
EXCEPTION --> OPTIONAL
--ERROR HANDLING
END; --> MANDATORY
SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put_line('Welcome to Oracle');
3 END;
4 /
Welcome to Oracle

PL/SQL procedure successfully completed.

k.sasikumar4u@gmail.com Page 1
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

VARIABLES IN PLSQL
 TEMPORARY STORAGE OF DATA
 MANIPULATION OF STORED VALUE
 REUSABILITY
 EASY OF MAINTENANCE

TYPES OF VARIABLE
 PL/SQL VARIABLES
 NON-PL/SQL VARIABLES

PL/SQL VARIABLES
 SCALAR
 COMPOSITE
 REFERENCE
 LOB (LARGE OBJECT)
NON PL/SQL VARIABLES
 BIND VARIABLE

GUIDELINES FOR DECLARING PL/SQL VARIABLES


 FOLLOW NAMING RULE
 INITILIZE NOT NULL | CONSTANT
 DECLARE ONE IDENTIFIER PER LINE
 INITILIZE IDENTIFIERS BY USING ASSIGNMENT OPERATOR (:=)
EXAMPLE
identifier := expression
DECLARE
I NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE (I);
END;
/

k.sasikumar4u@gmail.com Page 2
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
DECLARE
I NUMBER := 10;
J DATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE (I ||' '|| J);
END;
/

DECLARE
PI NUMBER(3,2):=3.14;
BEGIN
PI:=2.50;
DBMS_OUTPUT.PUT_LINE('THE VALUE OF PI IS '||PI);
END;
/

DECLARE
i NUMBER(2) := 10;
Dt DATE := SYSDATE;
name VARCHAR2(30) := 'Raj';
BEGIN
Dbms_output.put_line('The value of i is : ' || i);
Dbms_output.put_line(q'[Today's date : ]'|| dt);
Dbms_output.put_line('Name : ' || name);
END;

DECLARE
x NUMBER(2) := 10 ;
y NUMBER(2) := 15;

k.sasikumar4u@gmail.com Page 3
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
z NUMBER(3);
BEGIN
z := x+y;
Dbms_output.put_line(z);
END;
/

DECLARE
x NUMBER(2) := 10 ;
y NUMBER(2) := 15;
z NUMBER(3);
BEGIN
z := x+y;
Dbms_output.put_line( 'The sum of ' || x ||' + '|| y || '
= ' || z );
END;
/

CREATE TABLE test_insert( name varchar2(20) );

BEGIN
INSERT INTO test_insert VALUES('Name1');
INSERT INTO test_insert VALUES(' Name2');
INSERT INTO test_insert VALUES(' Name3');
INSERT INTO test_insert VALUES(' Name4');

k.sasikumar4u@gmail.com Page 4
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
Commit;
END;

Select * from test_insert;

NESTED BLOCK

<<outer>>
DECLARE
i NUMBER :=10;
BEGIN
Dbms_output.put_line(i);
DECLARE
i NUMBER :=20;
BEGIN
Dbms_output.put_line(outer.i);
END;
END;
/

BEGIN
SELECT max(salary) FROM Employees;
END;
/

ORA-06550: line 2, column 4:


PLS-00428: an INTO clause is expected in this SELECT statement

DECLARE
v_max_salary NUMBER(7);

k.sasikumar4u@gmail.com Page 5
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
BEGIN
SELECT max(salary)
INTO v_max_salary
FROM Employees;
Dbms_output.put_line('Maximum Salary : ' || v_max_salary);
END;
/

DECLARE
v_First_name Varchar2(20);
v_last_name Varchar2(20);
v_salary NUMBER(8);
BEGIN
SELECT First_name , Last_name , Salary
INTO v_First_name , v_Last_name , v_Salary
FROM Employees
WHERE Employee_id = 100;
Dbms_output.put_line('First name : ' || v_First_name);
Dbms_output.put_line('Last name : ' || v_Last_name);
Dbms_output.put_line('Salary : ' || v_salary);
END;
/

DECLARE
v_First_name Varchar2(20);
v_last_name Varchar2(20);
v_salary NUMBER(8);
BEGIN
SELECT First_name , Last_name , Salary

k.sasikumar4u@gmail.com Page 6
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
INTO v_First_name , v_Last_name , v_Salary
FROM Employees
--WHERE Employee_id = 100;
Dbms_output.put_line('First name : ' || v_First_name);
Dbms_output.put_line('Last name : ' || v_Last_name);
Dbms_output.put_line('Salary : ' || v_salary);
END;
/

DECLARE
v_hire_date DATE;
v_dept_no NUMBER NOT NULL := 10;
v_location VARCHAR2(15) := 'INDIA';
v_comm CONSTANT NUMBER := 1400;
......
....

END;

DECLARE
Department_id NUMBER(3) := 90;
BEGIN
-- don't execute this block
-- DELETE FROM Employees
-- WHERE Department_id = Department_id;
END;
/

k.sasikumar4u@gmail.com Page 7
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
DECLARE
v_Department_id NUMBER(3) := 90;
BEGIN
DELETE FROM Employees
WHERE Department_id = v_Department_id;
END;
/

SQL> variable r number;


SQL>
SQL> begin
2 select max(salary) into :r
3 from employees
4 ;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> print r;

R
----------
24000

CONDITION AND CONTROL STATEMENTS


PL/SQL HAS THREE CATEGORIES OF CONTROL STATEMENTS

k.sasikumar4u@gmail.com Page 8
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
CONDITIONAL SELECTION STATEMENTS
 Which run different statements for different data values.
 The conditional selection statements are IF and CASE.
LOOP STATEMENTS
 Which run the same statements with a series of different
data values.
 The loop statements are the basic LOOP, FOR LOOP, and
WHILE LOOP.
 The EXIT statement transfers control to the end of a
loop.
 The CONTINUE statement exits the current iteration of a
loop and transfers control to the next iteration.
 Both EXIT and CONTINUE have an optional WHEN clause,
where you can specify a condition.
SEQUENTIAL CONTROL STATEMENTS
 Which are not crucial to PL/SQL programming.
 The sequential control statements are GOTO, which goes to
a specified statement, and NULL, which does nothing.
LOOPS
 In this loop structure, sequence of statements is
enclosed between the LOOP and END LOOP statements.
 At each iteration, the sequence of statements is executed
and then control resumes at the top of the loop.
 PL/SQL loops can be labelled.
 The label should be enclosed by double angle brackets (<<
and >>) and appear at the beginning of the LOOP
statement.
 The label name can also appear at the end of the LOOP
statement.
 You may use the label in the EXIT statement to exit from
the loop.
EXIT
 The Exit statement completes the loop and control passes
to the statement immediately after END LOOP
CONTINUE
 Causes the loop to skip the remainder of its body and
immediately retest its condition prior to reiterating.

k.sasikumar4u@gmail.com Page 9
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
GOTO STATEMENT
 Transfers control to the labelled statement.
 Though it is not advised to use GOTO statement in your
program.
THE STATEMENTS THAT EXIT A LOOP ARE
 EXIT
 EXIT WHEN
THE STATEMENTS THAT EXIT THE CURRENT ITERATION OF A LOOP ARE
 CONTINUE
 CONTINUE WHEN

IF | IF...ELSE | MULTIPLE IF...ELSE |


LOOP | FOR | WHILE |
CASE
WHEN...THEN...
WHEN...THEN...
WHEN...THEN...
WHEN...THEN...
ELSE
END CASE
IF STATEMENT
DECLARE
I NUMBER (2):=10;
BEGIN
IF I=10 THEN
dbms_output.put_line ('THE VALUE OF I IS TEN');
END IF;
END;
/
THE VALUE OF I IS TEN

PL/SQL procedure successfully completed.

k.sasikumar4u@gmail.com Page 10
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
IF ELSE STATEMENT
DECLARE
I NUMBER (2):=10;
BEGIN
IF I=10 THEN
dbms_output.put_line ('THE VALUE OF I IS TEN');
ELSE
dbms_output.put_line ('THE VALUE OF I IS NOT TEN');
END IF;
END;

THE VALUE OF I IS TEN

PL/SQL procedure successfully completed.

MULTIPLE IF ELSE
DECLARE
I NUMBER (2):=60;
BEGIN
IF I=10 THEN
dbms_output.put_line ('THE VALUE OF I IS 10');
ELSIF I=20 THEN
dbms_output.put_line ('THE VALUE OF I IS 20');
ELSIF I=30 THEN
dbms_output.put_line ('THE VALUE OF I IS 30');
ELSE
dbms_output.put_line ('THE VALUE OF I IS NOT 10 20 30');
END IF;
END;
THE VALUE OF I IS NOT 10 20 30

k.sasikumar4u@gmail.com Page 11
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
PL/SQL procedure successfully completed.
LOOP
DECLARE
I NUMBER :=10;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (I);
I := I+1;
EXIT WHEN I>20;
END LOOP;
END;
/
10
11
12
13
14
15
16
17
18
19
20

PL/SQL procedure successfully completed.


FOR LOOP
DECLARE
I number;
BEGIN
For I in 1..5 loop
Dbms_output.put_line(I);

k.sasikumar4u@gmail.com Page 12
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
End loop;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.


WHILE
DECLARE
I NUMBER :=1;
BEGIN
WHILE (I<=5) LOOP
DBMS_OUTPUT.PUT_LINE (I);
I := I + 1;
END LOOP;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.

create table test_udt(id NUMBER, salary NUMBER);

BEGIN

k.sasikumar4u@gmail.com Page 13
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

INSERT INTO test_udt VALUES(001,23000);


INSERT INTO test_udt VALUES(002,43000);
INSERT INTO test_udt VALUES(003,78000);
INSERT INTO test_udt VALUES(004,25000);
INSERT INTO test_udt VALUES(005,26000);
INSERT INTO test_udt VALUES(006,90000);
Commit;
END;

select * from test_udt;

IF

DECLARE
v_salary NUMBER;
v_id NUMBER;
BEGIN
SELECT id,salary
INTO v_id,v_salary
FROM test_udt
WHERE id = 1;

IF v_salary > 20000 THEN


UPDATE test_udt t
SET t.salary = v_salary+10005
WHERE t.id = v_id ;
END IF;

commit;
END;
/

IF ELSE

DECLARE
v_salary NUMBER;
v_id NUMBER;
BEGIN
SELECT id,salary
INTO v_id,v_salary
FROM test_udt
WHERE id = 2;

IF v_salary < 20000 THEN


UPDATE test_udt t

k.sasikumar4u@gmail.com Page 14
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

SET t.salary = v_salary+10005


WHERE t.id = v_id ;
ELSE
UPDATE test_udt t
SET t.salary = v_salary+20007
WHERE t.id = v_id ;
END IF;
commit;
END;
/

--same example with less number of lines

DECLARE
v_salary NUMBER;
v_id NUMBER;
BEGIN
SELECT id,salary
INTO v_id,v_salary
FROM test_udt
WHERE id = 2;

IF v_salary < 20000 THEN


v_salary := v_salary+10005;
ELSE
v_salary := v_salary+20007;
END IF;

UPDATE test_udt t
SET t.salary = v_salary
WHERE t.id = v_id ;
commit;
END;
/

select * from test_udt;

Multiple IF ELSIF

DECLARE
v_salary NUMBER;
v_id NUMBER;
BEGIN
SELECT id,salary
INTO v_id,v_salary

k.sasikumar4u@gmail.com Page 15
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

FROM test_udt
WHERE id = 3;

IF v_id = 1 THEN
v_salary := 1000;
dbms_output.put_line('1 inside');
ELSIF v_id = 2 THEN
v_salary := 2000;
dbms_output.put_line('2 inside');
ELSIF v_id = 3 THEN
v_salary := 3000;
dbms_output.put_line('3 inside');
ELSIF v_id = 4 THEN
v_salary := 4000;
dbms_output.put_line('4 inside');
END IF;

UPDATE test_udt t
SET t.salary = v_salary
WHERE t.id = v_id ;
commit;
END;
/

select * from test_udt;

LOOP

--syntax

LOOP
--statements
--increment
--exit statement
END LOOP;

CREATE TABLE prod_details (pid VARCHAR2(3), pname VARCHAR2(30));

DECLARE
i NUMBER := 1;
x VARCHAR2(3);
BEGIN
LOOP
x := lpad(i,3,'0');
dbms_output.put_line('x value : '|| x);

k.sasikumar4u@gmail.com Page 16
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

INSERT INTO prod_details VALUES(i, 'Product '||i);


EXIT WHEN i >900;
i := i+1;

IF MOD(i,100) = 0 THEN
commit;
dbms_output.put_line('commit executed');
END IF;

END LOOP;
END;

SELECT * FROM prod_details ORDER BY 1;

DROP TABLE prod_details;

FOR LOOP

BEGIN
FOR I in 1..5
LOOP
Dbms_output.put_line('The value of i : '||i);
END LOOP;
END;
/

FOR LOOP - Reverse

BEGIN
FOR I in REVERSE 14..50
LOOP
Dbms_output.put_line('The value of i : '||i);
END LOOP;
END;
/

WHILE LOOP

DECLARE
i_val NUMBER:=0;
BEGIN
WHILE (i_val < 11)
LOOP
dbms_output.put_line('The value of I is '||i_val);
i_val := i_val + 1;
END LOOP;
END;

k.sasikumar4u@gmail.com Page 17
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE

sample output:

The value of I is 0
The value of I is 1
The value of I is 2
The value of I is 3
The value of I is 4
The value of I is 5
The value of I is 6
The value of I is 7
The value of I is 8
The value of I is 9
The value of I is 10

%TYPE AND %ROWTYPE

--%type is used to fetch the data type of the particular column

create table product_details


(
p_id NUMBER(3),
p_nm VARCHAR2(30),
p_qty NUMBER(8),
order_dt DATE
);

BEGIN
INSERT INTO product_details VALUES(100,'Name0',400,'23-Mar-
13');
INSERT INTO product_details VALUES(101,'Name1',600,'26-Apr-
13');
INSERT INTO product_details VALUES(102,'Name2',800,'27-Jan-
12');
INSERT INTO product_details VALUES(103,'Name3',300,'23-Jul-
11');
INSERT INTO product_details VALUES(104,'Name4',200,'22-Aug-
11');
INSERT INTO product_details VALUES(105,'Name5',500,'25-Oct-
12');
commit;
END;
/

SELECT * FROM product_details;

------------------------------------

k.sasikumar4u@gmail.com Page 18
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
P_ID P_NM P_QTY ORDER_DT
------------------------------------
100 Name0 400 03/23/2013
101 Name1 600 04/26/2013
102 Name2 800 01/27/2012
103 Name3 300 07/23/2011
104 Name4 200 08/22/2011
105 Name5 500 10/25/2012
------------------------------------

DECLARE
v_name VARCHAR2(4);
BEGIN
SELECT p_nm
INTO v_name
FROM product_details
WHERE p_id = 100;
dbms_output.put_line('Product Name : ' || v_name);
--error numeric or value error
END;

DECLARE
v_name VARCHAR2(5);
BEGIN
SELECT p_nm
INTO v_name
FROM product_details
WHERE p_id = 100;
dbms_output.put_line('Product Name : ' || v_name);
END;
/

ALTER TABLE product_details


MODIFY p_nm VARCHAR2(15);

INSERT INTO product_details


VALUES
(106,
'name6',
700,
'26-Dec-12');

commit;

106 name6 700 12/26/2012

DECLARE

k.sasikumar4u@gmail.com Page 19
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
v_name VARCHAR2(5);
BEGIN
SELECT p_nm INTO v_name
FROM product_details
WHERE p_id = 106;
dbms_output.put_line('Product Name : ' || v_name);
--error
END;
/

DECLARE
v_name product_details.p_nm%TYPE;
BEGIN
SELECT p_nm
INTO v_name
FROM product_details
WHERE p_id = 106;
dbms_output.put_line('Product Name : ' || v_name);
END;
/

DROP TABLE product_details;

DECLARE
dep_id departments.department_id%TYPE;
dep_name departments.department_name%TYPE;
dep_man_id departments.manager_id%TYPE;
dep_loc_id departments.location_id%TYPE;
BEGIN

SELECT department_id,
department_name,
manager_id,
location_id
INTO dep_id,
dep_name,
dep_man_id,
dep_loc_id
FROM departments
WHERE department_id = 10;

dbms_output.put_line('Department_id : ' || dep_id);


dbms_output.put_line('Department_name : ' || dep_name);
dbms_output.put_line('Manager_id : ' || dep_man_id);
dbms_output.put_line('Location_id : ' || dep_loc_id);

END;

k.sasikumar4u@gmail.com Page 20
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
/

--%rowtype is used to fetch the data type of all the column


--Insted of using %type if we use %rowtype means we can reduce the no
of variables that we declare

DECLARE
dep_detail departments%ROWTYPE;
BEGIN

SELECT *
INTO dep_detail
FROM departments
WHERE department_id = 10;

dbms_output.put_line('Department_id : ' ||
dep_detail.department_id);
dbms_output.put_line('Department_name : ' ||
dep_detail.department_name);
dbms_output.put_line('Manager_id : ' || dep_detail.manager_id);
dbms_output.put_line('Location_id : ' ||
dep_detail.location_id);

END;
/

DROP TABLE dept_details;

CREATE TABLE dept_details


(
dept_id number(3) ,
dept_name varchar2(30),
dept_manager_name varchar2(30)
);

insert into dept_details values(10,'dept1','manager_name1');


insert into dept_details values(20,'dept2','manager_name2');

SELECT * FROM dept_details;

-------------------------------------------------------
| DEPT_ID | DEPT_NAME | DEPT_MANAGER_NAME |
+------------+-----------------------+-----------------
| 10 | dept1 | manager_name1 |
| 20 | dept2 | manager_name2 |
------------+-----------------------+------------------

k.sasikumar4u@gmail.com Page 21
Oracle PL/SQL Introduction with Conditional statements AND For
loop condition & %TYPE AND %ROWTYPE
DECLARE
all_data dept_details%ROWTYPE;
BEGIN

all_data.dept_id := 100;
all_data.dept_name := 'Admin';
all_data.dept_manager_name := 'John';

UPDATE dept_details
SET ROW = all_data
WHERE dept_id = 10;

dbms_output.put_line(SQL%ROWCOUNT || ' Row(s) get updated');

END;
/

1 Row(s) get updated

select * from dept_details;

---------------------------------------------------
| DEPT_ID | DEPT_NAME | DEPT_MANAGER_NAME|
---------------------------------------------------
| 100 | Admin | John |
| 20 | dept2 | manager_name2 |
---------------------------------------------------

Interview Question

1. What is the use of %TYPE?


2. What is the use of %ROWTYPE?
3. Difference between %TYPE and %ROWTYPE?

k.sasikumar4u@gmail.com Page 22

You might also like