15709_procedures22

7/30/2019 15709_Procedures22

1/47

Introduction to PL/SQL

7/30/2019 15709_Procedures22

2/47

Oracle Server

PL/SQL Engine

Procedural

statement

executorProcedural

SQL

PL/SQL Architecture

2

PL /SQL

Block

PL/SQL

Block

SQL statement executor

7/30/2019 15709_Procedures22

3/47

3

PL/SQL Block structure

[DECLARE]1

BEGIN

[EXCEPTION] 1

END;

[ ] 1 optional keywords are marked by square brackets

BEGIN & END keywords are mandatory

7/30/2019 15709_Procedures22

4/47

4

Comments in PL/SQL block

Two hyphens(--) placed in the beginning of any

statement would be considered as comment

Multi-line comments (C style) are also allowed

which begins with /* and ends with */

Nesting of comments are not allowed, similar to C

language syntax

DECLARE

--declaration of variables

BEGIN

/* SQL and

PL/SQL statement(s) */END;

7/30/2019 15709_Procedures22

5/47

5

Named PL/SQL blocks (1 of 2)

PL/SQL blocks can have a name

Named blocks have an additional section called HEADER

HEADER conveys the name of the block and whether the

block is a PROCEDURE or a FUNCTION

If the named PL/SQL block is a FUNCTION then we have tospecify the data type of value it would be returning

When the block is run, it does not execute immediately,rather it compiles and stores it in the database for laterexecution

7/30/2019 15709_Procedures22

6/47

6

Named PL/SQL blocks (2 of 2)

CREATE [ OR REPLACE ] PROCEDURE block_name(p_param DATATYPE)

IS/AS

--declaration of variables

BEGIN

--SQL and PL/SQL statementsEXCEPTION

--error handling

END;

Other procedures and functions can be invoked within a named

PL/SQL block

Parameters can be passed to and returned from a named PL/SQL

block

7/30/2019 15709_Procedures22

7/47

7

Variables

Variables Variables are memory regions used to hold data

Type of value stored is decided by the data type

Declared in the DECLARE section

Scalar datatype A scalar datatype can hold a single value

Subcategories Character/String Number

Boolean

Date/Time

7/30/2019 15709_Procedures22

8/47

8

Variables

Syntax

Variables defined as NOT NULL also have to be

initialized

variable_name datatype [ := value ]

7/30/2019 15709_Procedures22

9/47

9

Variables

Examples on Character/String

SQL> DECLARE

2 v_productcategory VARCHAR2(20) :='Mobilephone';

7/30/2019 15709_Procedures22

10/47

10

Variables

Boolean

Used for storing boolean value such as TRUE, FALSE,

and NULL

Do not attempt to print or display the value storedin boolean variable, as it is not possible

SQL> DECLARE

2 v_test BOOLEAN;

3 BEGIN

4 v_test:='TRUE'; --Wrong

5 v_test:= TRUE; --Correct

6 END;

7/30/2019 15709_Procedures22

11/47

11

Variables

Date

Used for storing DATE

Stores the century, year, month, day, hour, minute,

second

Timestamp

Used for storing the date and time

Provides sub second time upto nine digits(the defaultis six)

SQL> DECLARE

2 v_transactiondate DATE:= SYSDATE;

3 v_billingdate TIMESTAMP := SYSTIMESTAMP;. . .

. . .

7/30/2019 15709_Procedures22

12/47

12

DBMS_OUTPUT.PUT_LINE (1 of 2)

DBMS_OUTPUT.PUT_LINE

An oracle supplied packaged procedure

DBMS_OUTPUT is a package and PUT_LINE is a procedurewithin the package

The string which has to be printed should be specified inparenthesis, following the PUT_LINE keyword

Using SET SERVEROUTPUT ON this package should beenabled in SQLPLUS

7/30/2019 15709_Procedures22

13/47

13

DBMS_OUTPUT.PUT_LINE (2 of 2)

SQL> SET SERVEROUTPUT ON

SQL> DECLARE2 v_productcategory VARCHAR2(20) :='Mobile Phone';

3 v_transactiondate DATE:= SYSDATE;

6 v_billingdate TIMESTAMP := SYSTIMESTAMP;

7 v_test BOOLEAN:=TRUE;

8 BEGIN9 DBMS_OUTPUT.PUT_LINE(v_productcategory);

10 DBMS_OUTPUT.PUT_LINE(v_transactiondate);

11 DBMS_OUTPUT.PUT_LINE (v_billingdate);

12 END;

13 /

Mobile Phone

10-MAR-09

10-MAR-09 03.39.27.845000 PM

7/30/2019 15709_Procedures22

14/47

declarations

7/30/2019 15709_Procedures22

15/47

declarations %TYPE

Usage 1 : Declare variables that directly maps to

a column definition in the database

SQL> DECLARE1 -- variablename TABLENAME.COLUMNNAME%TYPE; --Syntax

2 v_itemcode ITEM.ITEMCODE%TYPE;

. . . .

. . . .

7/30/2019 15709_Procedures22

16/47

%rowtype

Used for record

Declare

D dept%rowtype;Refer to fields

d.deptno;

d.deptname;

7/30/2019 15709_Procedures22

17/47

17

Accepting input in PL/SQL

Declare the variables in the declaration section of PL/SQL block

Accept the value for variables in the executable block Display the accepted values as shown below


SQL> DECLARE

2 v_itemname VARCHAR2(30);

3 BEGIN4 v_itemname :='&v_item';

5 DBMS_OUTPUT.PUT_LINE(v_itemname);

6 END;

7 /

Enter value for v_item: STN001

old 4: v_itemname :='&v_item';

new 4: v_itemname :='STN001';

STN001

PL/SQL procedure successfully completed.

7/30/2019 15709_Procedures22

18/47

Operators & Expressions

7/30/2019 15709_Procedures22

19/47

Operators & Expressions

Concatenation Operator ( || )

Arithmetic Operators( +, -, *, /,**)

Relational Operators( =, !=, , =)

Logical Operators (AND, OR and NOT)

19

7/30/2019 15709_Procedures22

20/47

Concatenation Operator

|| is the concatenation operator

Concatenates two or more strings together

Do not use || instead of Logical operator OR

20

DECLAREv_customername varchar2(10):=John';

BEGINv_customername := v_customername || '10';DBMS_OUTPUT.PUT_LINE('value of v_customername : '||v_customername);END;

value of v_customername : John10

7/30/2019 15709_Procedures22

21/47

Arithmetic Operator - Addition

Addition of NULL with any number is NULL

Only numeric and date data types can be used along witharithmetic operators

21

DECLAREv_reorderlevel NUMBER;

BEGINv_reorderlevel := v_reorderlevel+10; -- NULL +10DBMS_OUTPUT.PUT_LINE(value of v_reorderlevel : '||

v_reorderlevel);END;

Value of v_reorderlevel : (NULL)

7/30/2019 15709_Procedures22

22/47

22

Nested PL/SQL blocks (1 of 4)

A PL/SQL block defined within another PL/SQL

block is called nested PL/SQL block

Can be nested in the executable section or in

exception handling section

One or more nested blocks can be present within

an anonymous PL/SQL block

Overlapping of nested blocks are not allowed

7/30/2019 15709_Procedures22

23/47

23


DECLARE

--declaration of variables in the enclosed blockBEGIN

--SQL and PL/SQL statement(s)

DECLARE

--- declaration of variables in the nested block

BEGIN-- SQL & PL/SQL statement(s) in nested block

END;

DECLARE

-- declaration of variables in the nested block


END;


END;

7/30/2019 15709_Procedures22

24/47

24


DECLARE

--declaration of variables in the enclosed blockBEGIN


DECLARE

-- declaration of variables in the nested block


DECLARE

-- declaration of variables

BEGIN

-- SQL and PL/SQL statement(s)END;

END;


END;

7/30/2019 15709_Procedures22

25/47

25

Scope of variables(1 of 3)

Variables declared in the DECLARE section would

be visible in the EXECUTABLE section and

EXCEPTION section

Lifetime of variables declared in the nested block

will be only within the nested block

Variables declared in the outermost block are

visible in all the nested blocks

7/30/2019 15709_Procedures22

26/47

Scope of variables (2 of 3)

26

DECLAREv_qoh NUMBER:=10;BEGIN

DBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);

DBMS_OUTPUT.PUT_LINE('The value of v_price: '||v_price);END;

DECLAREv_price NUMBER:=20;

BEGINDBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);DBMS_OUTPUT.PUT_LINE('The value of v_price: '||v_price);

END;

Scope of the variable is

from DECLARE to its END

ERROR because scope of the

variable is within the inner

DECLARE to its END.

identifier 'V_PRICE' must be

declared

7/30/2019 15709_Procedures22

27/47

PL/SQL Conditional Constructs

7/30/2019 15709_Procedures22

28/47

IF-THEN

28

IF conditionTHEN

action;END IF;

DECLAREv_qoh NUMBER :=10;v_itemrequired NUMBER :=&b_itemrequired;

BEGINIF v_itemrequired>v_qoh THENDBMS_OUTPUT.PUT_LINE('Item not available ');

END IF;END;

Enter value for b_itemrequired: 11old 3: v_itemrequired NUMBER :=&b_itemrequired;new 3: v_itemrequired NUMBER :=11;Item not available

7/30/2019 15709_Procedures22

29/47

IF-THEN-ELSE

29

IF conditionTHENaction;

ELSEaction;END IF;

DECLARE --Comparing NUMBER datatypesv_qoh NUMBER :=10;v_itemrequired NUMBER :=&b_itemrequired;

BEGINIF v_itemrequired > v_qoh THENDBMS_OUTPUT.PUT_LINE('Item not available ');

ELSEDBMS_OUTPUT.PUT_LINE('Item available ');

END IF;END;

Enter value for b_itemrequired: 9old 3: v_itemrequired NUMBER :=&b_itemrequired;new 3: v_itemrequired NUMBER :=9;Item available

7/30/2019 15709_Procedures22

30/47

IF-THEN-ELSIF

30

IF conditionTHENaction;

ELSIF conditionTHENaction;

[ELSEaction;]

END IF;

DECLAREv_qoh NUMBER :=10;v_itemrequired NUMBER :=&b_itemrequired;

BEGIN

IF v_itemrequired>v_qoh THENDBMS_OUTPUT.PUT_LINE('Item not available ');

ELSIF v_itemrequired=10 THENDBMS_OUTPUT.PUT_LINE('Item available but no

more stock ');ELSEDBMS_OUTPUT.PUT_LINE('Item not available);

END IF;END;

7/30/2019 15709_Procedures22

31/47

LOOP

31

BEGINLOOP

DBMS_OUTPUT.PUT_LINE('I AM IN LOOP!!!');END LOOP;

END;

This is an

infinite LOOP!!!


BEGINLOOPDBMS_OUTPUT.PUT_LINE('Price: '||v_price);

v_price:=v_price+1;EXIT WHEN v_price >5;

END LOOP;END;

LOOPaction

END LOOP;

OUTPUT:---------Price: 1Price: 2

Price: 3Price: 4Price: 5

7/30/2019 15709_Procedures22

32/47

Numeric FOR Loop

The variable is automatically declared and initialized

32

BEGINFOR v_price IN 1..5LOOP

DBMS_OUTPUT.PUT_LINE('Price: '||v_price);END LOOP;

END;

FOR counter IN low_number.. high_numberLOOP

action;END LOOP;

OUTPUT:---------Price: 1Price: 2Price: 3

Price: 4Price: 5

7/30/2019 15709_Procedures22

33/47

WHILE Loop

33


BEGINWHILE v_price

7/30/2019 15709_Procedures22

34/47

Using SQL SELECT in PL/SQL

Only one row value can be returned to the variable_list

If no value is returned then the No data found exception is thrown If more than one record is returned then the Exact fetch returns more thanrequested number of rows exception is thrown.

34

SELECT select_list [INTO variable_list]

FROM table_list [WHERE where_clause]

[ORDER BY column_list];

/

7/30/2019 15709_Procedures22

35/47

Using SQL SELECT in PL/SQL

35

-- Write a PL/SQL block to display the quantity on hand for-- an item with itemid STN001DECLARE

v_qtyonhand item.qtyonhand%TYPE;

BEGIN

SELECT qtyonhand INTO v_qtyonhand FROM item WHERE itemid=STN001;

DBMS_OUTPUT.PUT_LINE(Qty On Hand : '||v_qtyonhand);

END;

What would you do, if you want to select all the columns from item table where itemid

is STN001 ?

C it d t t

7/30/2019 15709_Procedures22

36/47

36

Composite datatype

Is a datatype which can store more than one value

All the values could be homogeneous or heterogeneous innature meaning, values belonging to similar or differentdatatypes are stored collectively

Especially useful for creation of record variables

To create a record variable named v_itemrecwhich in turnstores ITEMID, ITEMNAME, QTYONHAND collectively we use

%ROWTYPE

After declaration, v_itemreccan hold only one record atany point of time_itemrec

ITEMID ITEMNAME QTYONHAND

1001 Pencil 50=

%ROWTYPE

7/30/2019 15709_Procedures22

37/47

37

%ROWTYPE

For declaring record variable, we use %ROWTYPE

Syntax: recordvariablename tablename%ROWTYPE;Example:

The above declaration anchors the v_itemrec record variable to allcolumns in the ITEM table

Individualcolumnnames and their datatypes in record variable will bethe same as that of the basetable column names and definitions

If any underlying column definition is modified, the change would bereflected in the structure of record variable, the next time the PL/SQLblock is run or compiled

SQL> DECLARE

2 v_itemrec item%ROWTYPE;

%ROWTYPE

7/30/2019 15709_Procedures22

38/47

%ROWTYPE

38

To select all the columns from item table where itemid is STN001 using record variable


SQL> DECLARE

2 v_itemrec item%ROWTYPE;

3 BEGIN

4 SELECT * INTO v_itemrecFROM item WHERE itemid=STN001;5 DBMS_OUTPUT.PUT_LINE( v_itemrec.itemid);6 DBMS_OUTPUT.PUT_LINE( v_itemrec.itemname);7 DBMS_OUTPUT.PUT_LINE( v_itemrec.qtyonhand);8 END;

SQL> /

STN001

Pen

250

PL/SQL procedure successfully completed.

U i SQL INSERT i PL/SQL

7/30/2019 15709_Procedures22

39/47

Using SQL INSERT in PL/SQL

INSERT can be used in PL/SQL block as it is in

SQL The following example is a direct insertion of

values to the respective columns

39

INSERT INTO table_name[(column_list)]

VALUES select_statement | (value_list);

--Inserting values to supplier table directly by providing--valuesBEGIN

INSERT INTO supplier(supplierid, suppliername,suppliercontactno) VALUES ('S001','Reynolds','0012233');

END;

U i SQL INSERT i PL/SQL

7/30/2019 15709_Procedures22

40/47

Using SQL INSERT in PL/SQL

40

--Inserting values to supplier table by accepting from end userDECLARE

v_supplierid supplier.supplierid%TYPE;

v_suppliername supplier.suppliername%TYPE;

v_suppliercontactno supplier.suppliercontactno%TYPE;

BEGIN

v_supplierid:='&supplierid';v_suppliername:='&suppliername';v_suppliercontactno:='&suppliercontactno';INSERT INTO supplier(supplierid, suppliername,

suppliercontactNo ) VALUES (v_supplierid, v_suppliername,v_suppliercontactno);

END;

E ti

7/30/2019 15709_Procedures22

41/47

Exception

Exception is an identifier in PL/SQL that is raised duringexecution

It terminates the main body of action and transfers thecontrol to the EXCEPTION section

Program execution will never return to the next statement,after the exception is raised

For example, if the exception is thrown in the nth line of a

PL/SQL block, control will not return to the (n+1)th linei.e. (n+1)th line will not be executed and the programterminates

41

H t h dl ti ?

7/30/2019 15709_Procedures22

42/47

How to handle an exception?

Exception part of any PL/SQL block handles anexception

If not handled in the exception part, theexception is propagated to the callingenvironment

42

DECLARE

BEGIN

END;

Exception is raised

EXCEPTIONException is trapped

E ti S t

7/30/2019 15709_Procedures22

43/47

43

Exception Syntax

EXCEPTIONWHEN exception1 [OR exception2 . . .] THEN

statement1;

statement2;

. . .

[WHEN exception3 [OR exception4 . . .] THEN

statement1;

statement2;

. . .]

[WHEN OTHERS THEN

statement1;

statement2;. . .]

END;

P d fi d O l S E ti

7/30/2019 15709_Procedures22

44/47

44

Predefined Oracle Server Exception

Oracle Error Predefined Exception Description

ORA-1403 NO_DATA_FOUND SELECT statement matches no rows

ORA-1422 TOO_MANY_ROWS SELECT statement matches more

than one row

ORA-0001 DUP_VAL_ON_INDEX Unique constraint violated

ORA-1476 ZERO_DIVIDE Division by zeroORA-6502 VALUE_ERROR Truncation, Arithmetic error

ORA-1722 INVALID_NUMBER Conversion to a number failed. Ex.

2A is not valid

NO DATA FOUND P d fi d E i

7/30/2019 15709_Procedures22

45/47

45

NO_DATA_FOUND - Predefined Exception

--Given an itemid display the itemname

--If the given itemid is invalid, display Invalid Itemid

SET SERVEROUTPUT ON

DECLARE

v_itemid item.itemid%TYPE;

v_itemrec item%ROWTYPE;

BEGIN

v_itemid := '&v_itemid';

SELECT * INTO v_itemrec FROM ITEM WHERE itemid=v_itemid;

DBMS_OUTPUT.PUT_LINE('Item Name is '||v_itemrec.itemname);

EXCEPTION

WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('Invalid Input / item id');

END;

/

Enter value for v_itemid: STN04

Invalid Input / item id

U d fi d ti

7/30/2019 15709_Procedures22

46/47

User-defined exception

Declare an exception identifier in the declaration section

Raise the exception explicitly in the executable section using RAISE statement

Handle the exception in the exception handling part

46

U d fi d E ti

7/30/2019 15709_Procedures22

47/47

User-defined Exception

--Given an itemid display the itemname

--If the given itemid is invalid, display Invalid Itemid

SET VERIFY OFF

SET SERVEROUTPUT ON

DECLARE

v_itemid ITEM.ITEMID%TYPE;

v_count number;

e_Invalid_Itemid exception;

BEGIN

v_itemid := '&v_itemid';

SELECT count(*) INTO v_count FROM ITEM WHERE itemid=v_itemid;

IF v_count = 0 THEN

RAISE e_Invalid_Itemid;

END IF;

DBMS_OUTPUT.PUT_LINE('Valid item id');

EXCEPTION

WHEN e_Invalid_Itemid THEN

DBMS_OUTPUT.PUT_LINE('Invalid Input / item id');

END;

15709_procedures22

Documents