7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 1/35

21.1.1.Block Structure

www.java2s.com/Tutorial/Oracle/CatalogOracle.htm

You typically use PL/SQL to add business logic to the database.

PL/SQL programs are divided up into structures known as blocks.

Each block containing PL/SQL and SQL statements.

 A typical PL/SQL block has the following structure:

[DECLARE  declaration_statements]BEGIN

  executable_statements[EXCEPTION  exception_handling_statements]END;

1. The declaration and exception blocks are optional.2. declaration_statements declares the variables subsequently used in the rest of the block.3. These variables are local to that block.4. Declarations are always placed at the start of the block.5. executable_statements are the actual executable statements for the block.6. executable_statements may include statements for performing tasks such as loops, conditional logic, and

so on.

7. exception_handling_statements are statements that handle any errors.8. Every statement is terminated by a semicolon (;).9. A block is terminated using the END keyword.

DECLARE

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 2/35

  2 width INTEGER;  3 height INTEGER := 2;  4 area INTEGER;  5 BEGIN

  6 area := 6;  7 width := area / height;  8 DBMS_OUTPUT.PUT_LINE('width = ' || width);  9 EXCEPTION

 10 WHEN ZERO_DIVIDE THEN 11 DBMS_OUTPUT.PUT_LINE('Division by zero'); 12 END; 13 /width = 3

PL/SQL procedure successfully completed.

SQL>

21.1.2.Your First PL/SQL Block

SQL>SQL> DECLARE  2 x NUMBER;  3 BEGIN

  4 x := 72600;  5 END;  6 /

PL/SQL procedure successfully completed.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 3/35

21.1.3.The slash character (/) at the end of the example executes thePL/SQL.

SQL>SQL> SET ECHO ONSQL> SET SERVEROUTPUT ON

SQL> DECLARE  2 age NATURAL;  3  4 BEGIN

  5 age := 10;  6 DBMS_OUTPUT.PUT_LINE('age:');  7 DBMS_OUTPUT.PUT_LINE(age);  8 END;  9 /age:10

PL/SQL procedure successfully completed.

SQL>

21.1.4.You can declare the whole string to be enclosed in quotes by usingthe construct q'!text!'

SQL>SQL> declare

  234  begin

  5 NULL;  6 End ;  7 /

PL/SQL procedure successfully completed.

Text literals in Oracle are case sensitive.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 4/35

21.1.5.All identifiers within the same scope must be unique.

SQL>SQL> declare  2 v_amount NUMBER;  3 v_amount BINARY_INTEGER; -- duplicate!!!  4 Begin

  5 NULL;  6 end ;  7 /

PL/SQL procedure successfully completed.

SQL>SQL>

21.1.6.Building Expressions with Operators

Expressions are constructed by using operands and operators.

 An Example of a Simple PL/SQL Expression

SQL>SQL> declare  2 v_i1 NUMBER;  3 v_i2 NUMBER;  4  begin

  5 v_i1:=10/3;  6 v_i2:=-v_i1;  7 end ;

  8 /

PL/SQL procedure successfully completed.

SQL>

21.1.7.PL/SQL datatypes

Oracle PL/SQL supports all SQL types plus the following additional Oracle PL/SQL specific types

Type DescriptionCHAR[(length [BYTE | CHAR])] Fixed-length character data of length bytes or characters and padded

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 5/35

with trailing spaces. Maximum length is 2,000 bytes.

VARCHAR2(length [BYTE | CHAR])Variable-length character data of up to length bytes or characters.Maximum length is 4,000 bytes.

NCHAR[(length)]Fixed-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8. Maximum length is 2,000 bytes.

NVARCHAR2(length) Variable-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8 encoding. Maximum length is 4,000 bytes.

BINARY_FLOATStores a single precision 32-bit floating-point number. Operationsinvolving BINARY_FLOAT are typically performed faster than onNUMBERs. BINARY_FLOAT requires 5 bytes of storage space.

BINARY_DOUBLEStores a double precision 64-bit floating-point number. Operationsinvolving BINARY_DOUBLE are typically performed faster than onNUMBERs. BINARY_DOUBLE requires 9 bytes of storage space.

NUMBER(precision, scale) andNUMERIC(precision, scale)

Variable-length number; precision is the maximum number of digits (in

front of and behind a decimal point, if used) that may be used for thenumber. The maximum precision supported is 38; scale is themaximum number of digits to the right of a decimal point (if used). If neither precision nor scale is specified, then a number with up to aprecision and scale of 38 digits may be supplied (meaning you cansupply a number with up to 38 digits, and any of those 38 digits may bein front of or behind the decimal point).

DEC and DECIMALSubtype of NUMBER. A fixed-point decimal number with up to 38 digitsof decimal precision.

DOUBLE PRECISION and FLOAT

Subtype of NUMBER. A floating-point number with up to 38 digits of 

precision.

REALSubtype of NUMBER. A floating-point number with up to 18 digits of precision.

INT, INTEGER, and SMALLINTSubtype of NUMBER. An integer with up to 38 digits of decimalprecision.

DATE

Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second. May be used to store a dateand time between January 1, 4712 B.C. and December 31, 4712 A.D.Default format is specified by the NLS_DATE_FORMAT parameter (for 

example: DD-MON-RR).INTERVAL YEAR[(years_precision)] Time interval measured in years and months; years_precision specifies

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 6/35

TO MONTHthe precision for the years, which may be an integer from 0 to 9 (defaultis 2). Can be used to represent a positive or negative time interval.

INTERVAL DAY[(days_precision)] TOSECOND[(seconds_precision)]

Time interval measured in days and seconds; days_precision specifiesthe precision for the days, which is an integer from 0 to 9 (default is 2);seconds_precision specifies the precision for the fractional part of theseconds, which is an integer from 0 to 9 (default is 6). Can be used torepresent a positive or negative time interval.

TIMESTAMP[(seconds_precision)]

Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second; seconds_precision specifiesthe number of digits for the fractional part of the seconds, which can bean integer from 0 to 9 (default is 6). Default format is specified by theNLS_TIMESTAMP_FORMAT parameter.

TIMESTAMP[(seconds_precision)]WITH TIME ZONE

Extends TIMESTAMP to store a time zone. The time zone can be anoffset from UTC, such as ??-5:0', or a region name, such as ??US/Pacific'. Default format is specified by theNLS_TIMESTAMP_TZ_FORMAT parameter.

TIMESTAMP[(seconds_precision)]WITH LOCAL TIME ZONE

Extends TIMESTAMP to convert a supplied datetime to the local timezone set for the database. The process of conversion is known asnormalizing the datetime. Default format is specified by theNLS_TIMESTAMP_FORMAT parameter.

CLOB Variable length single-byte character data of up to 128 terabytes.

NCLOBVariable length Unicode national character set data of up to 128terabytes.

BLOB Variable length binary data of up to 128 terabytes.

BFILE Pointer to an external file.

LONGVariable length character data of up to 2 gigabytes. Superceded byCLOB and NCLOB types, but supported for backwards compatibility.

RAW(length)Variable length binary data of up to length bytes. Maximum length is2,000 bytes. Superceded by BLOB type, but supported for backwardscompatibility.

LONG RAWVariable length binary data of up to 2 gigabytes. Superceded by BLOBtype but supported for backwards compatibility.

ROWID Hexadecimal string used to represent a row address.

UROWID[(length)]Hexadecimal string representing the logical address of a row of anindex-organized table; length specifies the number of bytes. Maximum

length is 4,000 bytes (also default).REF object_type Reference to an object type.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 7/35

VARRAYVariable length array. This is a composite type and stores an orderedset of elements.

NESTED TABLENested table. This is a composite type and stores an unordered set of elements.

XMLType Stores XML data.

User defined object type You can define your own object type and create objects of that type.

PL/SQL only data type PL/SQL only data typeBOOLEAN Boolean value (TRUE, FALSE, or NULL).

BINARY_INTEGER Integer between C231 (C2,147,483,648) and 231 (2,147,483,648).

NATURAL Subtype of BINARY_INTEGER. A non-negative integer.

NATURALNSubtype of BINARY_INTEGER. A non-negative integer (and cannot beNULL).

POSITIVE Subtype of BINARY_INTEGER. A positive integer.

POSITIVENSubtype of BINARY_INTEGER. A positive integer (and cannot beNULL).

SIGNTYPE Subtype of BINARY_INTEGER. An integer of C1, 0, or 1.

PLS_INTEGERInteger between C231 (C2,147,483,648) and 231 (2,147,483,648).Similar to BINARY_INTEGER, but computations involvingPLS_INTEGER values are faster.

STRING Same as VARCHAR2.

RECORD Composite of a group of other types. Similar to a structure in C.

REF CURSOR Pointer to a set of rows.

These datatypes can be used for creating simple scalar variables.

They can be combined into structures such as records or PL/SQL tables.

 A scalar variable is a variable that is not made up of some combination of other variables.Scalar variables don't have internal components that you can manipulate individually.

They are often used to build up more complex datatypes such as records and arrays.

Some of the datatype names match those used by Oracle for defining database columns.

In most cases the definitions are the same for both the database and PL/SQL, but there are a few differences.

PL/SQL also provides subtypes of some datatypes.

 A subtype represents a special case of a datatype.

 A subtype represents a narrower range of values than the parent type.

For example, POSITIVE is a subtype of BINARY_INTEGER that holds only positive values.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 8/35

21.1.8.Variable Naming Rules

Variable names can be composed of letters, dollar signs, underscores, and number signs.

No other characters can be used.

 A variable name must start with a letter, after which any combination of the allowed characters can be used.

The maximum length for a variable name is 30 characters.

Variable names, like those of keywords and other identifiers, are not case sensitive

21.1.9.Introducing the Main Data type Groups

There are four main groups:

1. Scalar datatypes represent single values that can't be divided into parts.2. Composite datatypes include internal components that can be manipulated independently.3. %ROWTYPE is an example of the PL/SQL RECORD datatype.4. References contain pointers to other program items.

5. Large objects store or point to large amounts of textual or binary information, such as images, movies, or books.

Scalar datatypes are divided into families:

1. Numeric datatypes.2. Character datatypes.3. Date/time datatypes.

4. Boolean datatypes.

22.PL SQL Statements

22.1.IF( 21 ) 22.13.Rollback Commit( 6 )

22.2.CASE( 12 ) 22.14.Binded variable( 6 )

22.3.Loop( 15 ) 22.15.Dynamic SQL( 6 )

22.4.For LOOP( 20 ) 22.16.Execute immediate( 20 )

22.5.GOTO( 6 ) 22.17.bulk collect( 9 )

22.6.Labeled statement( 2 ) 22.18.forall( 6 )

22.7.While( 11 ) 22.19.RETURNING( 1 )

22.8.EXIT( 8 )

22.20.Timing a Statement( 2 )

22.9.DDL Statement( 1 ) 22.21. Autonomous Transaction( 6 )

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 9/35

22.10.Select into( 19 ) 22.22.Dynamic SQL statement( 6 )

22.11.Named Block( 1 ) 22.23.NULL( 1 )

22.12.Nested Block( 5 ) 22.24.RAISE( 4 )

22.1.IF

22.1.1. Conditional Logic

22.1.2. Handling conditions

22.1.3.  A Simple Condition Statement

22.1.4.  A Simple Condition Statement with BOOLEAN variable

22.1.5. The IF...THEN...ELSE Statement

22.1.6. Use IF THEN ELSE IF

22.1.7. IF...ELSE statements

22.1.8. Using an ELSIF Statement

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 10/35

22.1.9. IF..ELSIF ladder 

22.1.1.Conditional Logic

You may use the IF, THEN, ELSE, ELSIF, and END IF keywords in PL/SQL for performing conditional logic.

The following syntax illustrates the use of conditional logic:

IF condition1 THEN  statements1ELSIF condition2 THEN  statements2ELSE  statements3END IF;

where

1. condition1 and condition2 are Boolean expressions that evaluate to true or false.

2. statements1, statements2, and statements3 are PL/SQL statements.

This conditional logic flows as follows:

1. If condition1 is true, then statements1 is executed.2. If condition1 is false but condition2 is true, then statements2 is executed.

3. If neither condition1 nor condition2 are true, then statements3 is executed.SQL> CREATE OR REPLACE PROCEDURE cant_go_there

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 11/35

  2 AS  3 l_salary NUMBER := 10000;  4 BEGIN

  5 IF l_salary > 20000  6 THEN  7 dbms_output.put_line ('Executive');  8 ELSE  9 dbms_output.put_line ('Drone');

 10END

IF; 11 END cant_go_there; 12 /

Procedure created.

SQL>SQL> SHOW ERRORSNo errors.SQL>

SQL> CREATE OR REPLACE PROCEDURE cant_go_there  2 AS

  3 l_name varchar2(100) := 'steven';  4 BEGIN

  5 IF l_name = 'STEVEN'  6 THEN  7 dbms_output.put_line ('Impossible');  8 ELSE  9 dbms_output.put_line ('Guaranteed'); 10 END IF; 11 END cant_go_there; 12 /

Procedure created.

SQL>SQL> SHOW ERRORSNo errors.SQL>

22.1.2.Handling conditions

Conditions can be connected by using logical operations: AND, OR, and NOT.The default order of evaluation is standard.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 12/35

First any parentheses are resolved.

Then operators are executed on the same level in order of precedence: NOT (highest precedence), AND, and OR(lowest precedence).

SQL> declare  2 v_day NUMBER := TO_CHAR(TO_DATE('20060101','YYYYMMDD'),'D');  3  begin

  4 if v_day in (1,7) or (v_day not in (1,7) and (v_day between 0 and 6 or v_day between 19 and 23))  5 then  6 DBMS_OUTPUT.put_line(v_day||': Off-peak');  7 else

  8 DBMS_OUTPUT.put_line(v_day||': Peak');  9 end if; 10 end ; 11 /1: Off-peak

PL/SQL procedure successfully completed.

SQL>

22.1.3.A Simple Condition Statement

SQL>SQL> create or replace function f_isSunday (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5  begin

  6 if to_char(in_dt,'d')=1 then

  7 v_out:='Y';  8 DBMS_OUTPUT.put_line('IsSunday=Y');  9 end if; 10 return v_out; 11 end ; 12 /

Function created.

SQL>SQL>

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 13/35

22.1.4.A Simple Condition Statement with BOOLEAN variable

SQL>SQL> create or replace function f_isSunday (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5 v_flag_b BOOLEAN;  6  begin

  7 v_flag_b := to_char(in_dt,'d')=1;  8 if v_flag_b then  9 v_out:='Y'; 10 DBMS_OUTPUT.put_line('IsSunday=Y'); 11 end if; 12 return v_out; 13 end ; 14 /

Function created.

22.1.5.The IF...THEN...ELSE Statement

The IF...THEN...ELSE statement allows you to process a series of statements under ELSE if the condition isfalse.

The Syntax for the IF...THEN...ELSE Statement

IF <some_condition_evaluates_to_true>THEN  <perform_statements_condition_true>ELSE  <perform_statements_condition_false>END IF;

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 14/35

some_condition_evaluates_to_true, tests a BOOLEAN condition that you provide.

If true, the second parameter, perform_statements_condition_true, executes.

If the condition is false, the parameter perform_statements_condition_false executes.

SQL>SQL> set echo onSQL>SQL> DECLARE

  2 v_a Number := 50 ;  3 v_b Number;  4 BEGIN

  5 IF v_a > 40 THEN  6 v_b := v_a - 40;  7 DBMS_OUTPUT.PUT_LINE('Hours b worked = ' || v_b);  8 ELSE  9 v_b := 0; 10 END IF; 11 END; 12 /Hours b worked = 10

PL/SQL procedure successfully completed.

SQL>

22.1.6.Use IF THEN ELSE IF

SQL>

SQL> SET SERVEROUTPUT ONSQL> SET ECHO ONSQL> DECLARE  2 employee_name_c CHAR(32);  3 employee_name_v VARCHAR2(32);  4 BEGIN

  5 --Assign the same value to each string.  6 employee_name_c := 'CHAR';  7 employee_name_v := 'VARCHAR';  8  9 --Test the strings for equality. 10 IF employee_name_c = employee_name_v THEN 11 DBMS_OUTPUT.PUT_LINE('The names are the same'); 12 ELSE 13 DBMS_OUTPUT.PUT_LINE('The names are NOT the same');

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 15/35

 14 END IF; 15 END; 16 /The names are NOT the same

PL/SQL procedure successfully completed.

SQL>SQL>

22.1.IF

22.1.1. Conditional Logic

22.1.2. Handling conditions

22.1.3.  A Simple Condition Statement

22.1.4.  A Simple Condition Statement with BOOLEAN variable

22.1.5. The IF...THEN...ELSE Statement

22.1.6. Use IF THEN ELSE IF

22.1.7. IF...ELSE statements

22.1.8. Using an ELSIF Statement

22.1.9. IF..ELSIF ladder 

22.1.10. Block IF statement

22.1.11. IF with ELSE

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 16/35

22.1.12. The Syntax for IF...ELSIF

22.1.13. ELSIF Ladder 

22.1.14. The Syntax for Nested IF Statements

22.1.15. Use if with 'IN'

22.1.16. Three valued comparison

22.1.17. JUMP out of a IF statement with goto

22.1.18. Comparing with NULL

22.1.19. If block statement

22.1.20. Create a function and call it in an if statement

22.1.21. PLW-06002: Unreachable code

22.1.1.Conditional Logic

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 17/35

You may use the IF, THEN, ELSE, ELSIF, and END IF keywords in PL/SQL for performing conditional logic.

The following syntax illustrates the use of conditional logic:

IF condition1 THEN  statements1ELSIF condition2 THEN  statements2

ELSE  statements3END IF;

where

1. condition1 and condition2 are Boolean expressions that evaluate to true or false.

2. statements1, statements2, and statements3 are PL/SQL statements.

This conditional logic flows as follows:

1. If condition1 is true, then statements1 is executed.2. If condition1 is false but condition2 is true, then statements2 is executed.

3. If neither condition1 nor condition2 are true, then statements3 is executed.

SQL> CREATE OR REPLACE PROCEDURE cant_go_there  2 AS  3 l_salary NUMBER := 10000;  4 BEGIN

  5 IF l_salary > 20000  6 THEN  7 dbms_output.put_line ('Executive');  8 ELSE  9 dbms_output.put_line ('Drone'); 10 END IF;

 11 END cant_go_there; 12 /

Procedure created.

SQL>SQL> SHOW ERRORSNo errors.SQL>

SQL> CREATE OR REPLACE PROCEDURE cant_go_there  2 AS  3 l_name varchar2(100) := 'steven';  4 BEGIN

  5 IF l_name = 'STEVEN'

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 18/35

  6 THEN  7 dbms_output.put_line ('Impossible');  8 ELSE  9 dbms_output.put_line ('Guaranteed'); 10 END IF; 11 END cant_go_there; 12 /

Procedure created.

SQL>SQL> SHOW ERRORSNo errors.SQL>

22.1.2.Handling conditions

Conditions can be connected by using logical operations: AND, OR, and NOT.The default order of evaluation is standard.

First any parentheses are resolved.

Then operators are executed on the same level in order of precedence: NOT (highest precedence), AND, and OR(lowest precedence).

SQL> declare  2 v_day NUMBER := TO_CHAR(TO_DATE('20060101','YYYYMMDD'),'D');  3  begin

  4 if v_day in (1,7) or (v_day not in (1,7) and (v_day between 0 and 6 or v_day between 19 and 23))  5 then  6 DBMS_OUTPUT.put_line(v_day||': Off-peak');

  7 else  8 DBMS_OUTPUT.put_line(v_day||': Peak');  9 end if; 10 end ; 11 /1: Off-peak

PL/SQL procedure successfully completed.

SQL>

22.1.3.A Simple Condition Statement

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 19/35

SQL>SQL> create or replace function f_isSunday (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5  begin

  6 if to_char(in_dt,'d')=1 then

  7 v_out:='Y';  8 DBMS_OUTPUT.put_line('IsSunday=Y');  9 end if; 10 return v_out; 11 end ; 12 /

Function created.

SQL>SQL>

22.1.4.A Simple Condition Statement with BOOLEAN variable

SQL>SQL> create or replace function f_isSunday (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5 v_flag_b BOOLEAN;  6  begin

  7 v_flag_b := to_char(in_dt,'d')=1;  8 if v_flag_b then  9 v_out:='Y'; 10 DBMS_OUTPUT.put_line('IsSunday=Y'); 11 end if; 12 return v_out; 13 end ; 14 /

Function created.

22.1.5.The IF...THEN...ELSE Statement

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 20/35

The IF...THEN...ELSE statement allows you to process a series of statements under ELSE if the condition isfalse.

The Syntax for the IF...THEN...ELSE Statement

IF <some_condition_evaluates_to_true>THEN  <perform_statements_condition_true>

ELSE  <perform_statements_condition_false>END IF;

some_condition_evaluates_to_true, tests a BOOLEAN condition that you provide.

If true, the second parameter, perform_statements_condition_true, executes.

If the condition is false, the parameter perform_statements_condition_false executes.

SQL>SQL> set echo onSQL>SQL> DECLARE  2 v_a Number := 50 ;

  3 v_b Number;  4 BEGIN

  5 IF v_a > 40 THEN  6 v_b := v_a - 40;  7 DBMS_OUTPUT.PUT_LINE('Hours b worked = ' || v_b);  8 ELSE  9 v_b := 0; 10 END IF; 11 END; 12 /Hours b worked = 10

PL/SQL procedure successfully completed.

SQL>

22.1.6.Use IF THEN ELSE IF

SQL>SQL> SET SERVEROUTPUT ONSQL> SET ECHO ON

SQL> DECLARE  2 employee_name_c CHAR(32);

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 21/35

  3 employee_name_v VARCHAR2(32);  4 BEGIN

  5 --Assign the same value to each string.  6 employee_name_c := 'CHAR';  7 employee_name_v := 'VARCHAR';  8  9 --Test the strings for equality. 10 IF employee_name_c = employee_name_v THEN 11 DBMS_OUTPUT.PUT_LINE('The names are the same'); 12 ELSE 13 DBMS_OUTPUT.PUT_LINE('The names are NOT the same'); 14 END IF; 15 END; 16 /The names are NOT the same

PL/SQL procedure successfully completed.

SQL>SQL>

22.1.7.IF...ELSE statements

Using ELSE in a Condition Statement

IF <condition> then  ...<<set of statements>>...else

  ...<<set of statements>>...end if;

SQL>SQL> create or replace function f_isSunday (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5 v_flag_b BOOLEAN;  6  begin

  7 if to_char(in_dt,'d')=1 then  8 v_out:='Y';  9 else

 10 v_out:='N'; 11 end if;

 12 return v_out; 13 end ;

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 22/35

 14 /

Function created.

22.1.8.Using an ELSIF Statement

SQL>SQL>SQL> create or replace function f_getDateType (in_dt DATE)  2 return VARCHAR2  3 is  4 v_out VARCHAR2(10);  5  begin

  6 if to_char(in_dt,'MMDD') in ('0101','0704') then  7 v_out:='HOLIDAY';  8 elsif to_char(in_dt,'d') = 1 then  9 v_out:='SUNDAY';

 10 elsif to_char(in_dt,'d') = 7 then 11 v_out:='SATURDAY'; 12 else

 13 v_out:='WEEKDAY'; 14 end if; 15 return v_out; 16 end ; 17 /

Function created.

22.1.9.IF..ELSIF ladder 

if <condition> then  ...<<set of statements>>...elsif <condition> then  ...<<set of statements>>...elsif <condition> then  ...<<set of statements>>...else

  ...<<set of statements>>...end if;

SQL>

SQL>create

or replacefunction

f_isSunday (in_dt DATE)  2 return VARCHAR2

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 23/35

  3 is  4 v_out VARCHAR2(10);  5  begin

  6 if to_char(in_dt,'d')=1 then  7 v_out:='Y';  8 else

  9 null; 10 end if; 11 return v_out; 12 end ; 13 /

SP2-0806: Function created with compilation warnings

22.1.10.Block IF statement

SQL>

SQL> set serveroutput onSQL> set echo onSQL>SQL> DECLARE  2 v_A Number := 50 ;  3 v_B Number := 0 ;  4 BEGIN

  5 IF v_A > 40 THEN  6 v_B := v_A - 40;  7 DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);  8 END IF;  9 END; 10 /

V_B = 10

PL/SQL procedure successfully completed.

SQL>

22.1.11.IF with ELSE

SQL>SQL> set serveroutput on

SQL> set echo onSQL> DECLARE

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 24/35

  2 v_A Number := 50;  3 v_B Number;  4 BEGIN

  5 IF v_A > 40 THEN  6 v_B := v_A - 40;  7 DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);  8 ELSE  9 v_B := 0; 10 END IF; 11 END; 12 /V_B = 10

PL/SQL procedure successfully completed.

22.1.12.The Syntax for IF...ELSIF

IF <condition1_evaluates_to_true>  THEN  <perform_statements>  ELSIF <condition2_evaluates_to_true>  THEN  <perform_statements>  ELSIF <condition3_evaluates_to_true>  THEN  <perform_statements>  ELSE <this is always optional as the default value>  <perform_statements>  END IF;

22.1.13.ELSIF Ladder 

SQL>SQL> set serveroutput onSQL> set echo onSQL>SQL> DECLARE  2 v_Score Number := 85; --Percentage  3 v_LetterGrade Char(1);  4 BEGIN

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 25/35

  5 IF v_Score >= 90 THEN  6 v_LetterGrade := 'A';  7 ELSIF v_Score >= 80 THEN  8 v_LetterGrade := 'B';  9 ELSIF v_Score >= 70 THEN 10 v_LetterGrade := 'C'; 11 ELSIF v_Score >= 60 THEN 12 v_LetterGrade := 'D'; 13 ELSE 14 v_LetterGrade := 'E'; 15 END IF; 16 DBMS_OUTPUT.PUT_LINE('Your Letter Grade is: ' || v_LetterGrade); 17 END; 18 /Your Letter Grade is: B

PL/SQL procedure successfully completed.

SQL>SQL>

22.1.14.The Syntax for Nested IF Statements

IF <condition1 evaluates to true>  THEN  IF <condition2 evaluates to true>  THEN  <perform statements>  ELSE <both conditions have been evaluated to false>  IF <condition3 evaluates to true>  THEN

  <perform statements>  ELSE  <perform statements>  END IF;  END IF;  END IF;

22.1.15.Use if with 'IN'

SQL>SQL> SET SERVEROUTPUT ON

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 26/35

SQL> DECLARE  2 test_date DATE;  3 day_of_week VARCHAR2(3);  4 years_ahead INTEGER;  5 BEGIN

  6 test_date := TO_DATE('1-Jan-1997','dd-mon-yyyy');  7  8 FOR years_ahead IN 1..10 LOOP  9 day_of_week := TO_CHAR(test_date,'Dy'); 10 11 IF day_of_week IN ('Sat','Sun') THEN 12 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,'dd-Mon-yyyy')|| ' A long weekend!'); 13 ELSE 14 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,'dd-Mon-yyyy')|| ' Not a long weekend.'); 15 END IF; 16 test_date := ADD_MONTHS(test_date,12); 17 END LOOP; 18 END; 19 /01-Jan-1997 Not a long weekend.01-Jan-1998 Not a long weekend.

01-Jan-1999 Not a long weekend.01-Jan-2000 A long weekend!01-Jan-2001 Not a long weekend.01-Jan-2002 Not a long weekend.01-Jan-2003 Not a long weekend.01-Jan-2004 Not a long weekend.01-Jan-2005 A long weekend!01-Jan-2006 A long weekend!

PL/SQL procedure successfully completed.

SQL>

SQL>

22.1.16.Three valued comparison

SQL>SQL> SET ECHO ONSQL> SET SERVEROUTPUT ONSQL> DECLARE  2 a INTEGER;

  3 b BOOLEAN;  4 n INTEGER; --this will be our null value.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 27/35

  5 BEGIN

  6 a := 2;  7  8 b := (a <> n);  9 IF b THEN 10 DBMS_OUTPUT.PUT_LINE('a <> n is TRUE'); 11 ELSIF NOT b THEN 12 DBMS_OUTPUT.PUT_LINE('a <> n is FALSE'); 13 ELSE 14 DBMS_OUTPUT.PUT_LINE('a <> n is NULL'); 15 END IF; 16 END; 17 /a <> n is NULL

PL/SQL procedure successfully completed.

SQL>

22.1.17.JUMP out of a IF statement with goto

SQL>SQL> set serveroutput onSQL> set echo onSQL>SQL> DECLARE  2 v_Status NUMBER := 1;  3 BEGIN

  4 IF v_Status = 1 THEN  5 GOTO mybranch;

  6 ELSE  7 v_Status := 1;  8 END IF;  9 <<mybranch>> 10 NULL; 11 END; 12 /

PL/SQL procedure successfully completed.

SQL>

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 28/35

22.1.18.Comparing with NULL

SQL>SQL>SQL> declare  2 v NUMBER;  3  begin

  4 if v = 1 then  5 DBMS_OUTPUT.put_line('Equal to 1');  6 elsif v!= 1 then  7 DBMS_OUTPUT.put_line('Not equal to 1');  8 elsif v = v then  9 DBMS_OUTPUT.put_line('Equal to itself');

 10 else 11 DBMS_OUTPUT.put_line('Undefined result'); 12 end if; 13 v:=v+1; 14 DBMS_OUTPUT.put_line('New value: <'||v||'>'); 15 end ; 16 /Undefined resultNew value: <>

PL/SQL procedure successfully completed.

SQL>SQL>

22.1.19.If block statement

SQL>SQL>SQL> create table employee (  2 id number,  3 employee_type_id number,

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 29/35

  4 external_id varchar2(30),  5 first_name varchar2(30),  6 middle_name varchar2(30),  7 last_name varchar2(30),  8 name varchar2(100),  9 birth_date date, 10 gender_id number);

Table created.

SQL>SQL>SQL>SQL> create table gender (  2 id number,  3 code varchar2(30),  4 description varchar2(80),  5 active_date date default SYSDATE not null,  6 inactive_date date );

Table created.

SQL>SQL>SQL>SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 2, 'M', 'Male' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' );

1 row created.

SQL>SQL> create table employee_type (  2 id number not null,  3 code varchar2(30) not null,  4 description varchar2(80) not null,  5 active_date date default SYSDATE not null,  6 inactive_date date );

Table created.

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 30/35

SQL>SQL> insert into employee_type(id,code,description)values(1,'C','Contractor' );

1 row created.

SQL> insert into employee_type(id,code,description)values(2,'E','Employee' );

1 row created.

SQL> insert into employee_type(id,code,description)values(3,'U','Unknown' );

1 row created.

SQL>SQL>SQL>SQL>SQL> set serveroutput on size 1000000;SQL>SQL> declare  2 n_id employee.id%TYPE;

  3 n_employee_type_id employee.employee_type_id%TYPE;  4 v_external_id employee.external_id%TYPE;  5 v_first_name employee.first_name%TYPE;  6 v_middle_name employee.middle_name%TYPE;  7 v_last_name employee.last_name%TYPE;  8 v_name employee.name%TYPE;  9 d_birth_date employee.birth_date%TYPE; 10 n_gender_id employee.gender_id%TYPE; 11 12 n_inserted number := 0; 13 n_updated number := 0; 14

 15  begin 16 v_first_name := 'JOHN'; 17 v_middle_name := 'J.'; 18 v_last_name := 'DOE'; 19 v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name); 20 d_birth_date := to_date('19800101', 'YYYYMMDD'); 21 22  begin

 23 select id into n_employee_type_id from employee_type where code = 'C'; 24 exception 25 when OTHERS then 26 raise_application_error(-20002, SQLERRM||' on select employee_type');

 27end 

; 28

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 31/35

 29  begin

 30 select id into n_gender_id from gender where code = 'M'; 31 exception 32 when OTHERS then 33 raise_application_error(-20004, SQLERRM||' on select gender'); 34 end ; 35 36  begin

 37 select id into n_id from employee 38  where name = v_name 39 and birth_date = d_birth_date 40 and gender_id = n_gender_id; 41 exception 42 when NO_DATA_FOUND then 43 n_id := NULL; 44 when OTHERS then 45 raise_application_error(-20003, SQLERRM||' on select employee_T'); 46 end ; 47 48 if n_id is NULL then 49  begin

 50 select 12 into n_id from SYS.DUAL; 51 exception 52 when OTHERS then 53 raise_application_error(-20004, SQLERRM||' on select 12'); 54 end ; 55 56  begin

 57 select lpad(to_char(12), 9, '0') into v_external_id from SYS.DUAL; 58 exception 59 when OTHERS then 60 raise_application_error(-20005, SQLERRM||' on select 12'); 61 end ;

 62 63  begin

 64 insert into employee ( 65 id, 66 employee_type_id, 67 external_id, 68 first_name, 69 middle_name, 70 last_name, 71 name, 72 birth_date, 73 gender_id )

 74 values ( 75 n_id,

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 32/35

 76 n_employee_type_id, 77 v_external_id, 78 v_first_name, 79 v_middle_name, 80 v_last_name, 81 v_name, 82 d_birth_date, 83 n_gender_id ); 84

 85 n_inserted := sql%rowcount; 86 exception 87 when OTHERS then 88 raise_application_error(-20006, SQLERRM||' on insert employee'); 89 end ; 90 else

 91  begin

 92 update employee 93 set employee_type_id = n_employee_type_id 94  where id = n_id; 95 96 n_updated := sql%rowcount;

 97 exception 98 when OTHERS then 99 raise_application_error(-20007, SQLERRM||' on update employee');100 end ;101 end if;102103 DBMS_OUTPUT.PUT_LINE(to_char(n_inserted)||' row(s) inserted.');104 DBMS_OUTPUT.PUT_LINE(to_char(n_updated)||' row(s) updated.');105 end ;106 /1 row(s) inserted.0 row(s) updated.

PL/SQL procedure successfully completed.

SQL>SQL> drop table gender;

Table dropped.

SQL>SQL> drop table employee;

Table dropped.

SQL>

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 33/35

SQL> drop table employee_type;

Table dropped.

SQL>

22.1.20.Create a function and call it in an if statementSQL> CREATE TABLE emp (  2 id NUMBER PRIMARY KEY,  3 fname VARCHAR2(50),  4 lname VARCHAR2(50)  5 );

Table created.

SQL>SQL> CREATE TABLE books (  2 isbn CHAR(10) PRIMARY KEY,  3 category VARCHAR2(20),  4 title VARCHAR2(100),  5 num_pages NUMBER,  6 price NUMBER,  7 copyright NUMBER(4),  8 emp1 NUMBER,  9 emp2 NUMBER, 10 emp3 NUMBER 11 );

Table created.

SQL>SQL>SQL> CREATE OR REPLACE FUNCTION Threeemp(p_ISBN IN books.isbn%TYPE)  2 RETURN BOOLEAN AS  3  4 v_emp3 books.emp3%TYPE;  5 BEGIN

  6 SELECT emp3 INTO v_emp3 FROM books WHERE isbn = p_ISBN;  7  8 IF v_emp3 IS NULL THEN  9 RETURN FALSE;

 10 ELSE 11 RETURN TRUE;

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 34/35

 12 END IF; 13 END Threeemp; 14 /

Function created.

SQL>SQL> set serveroutput onSQL>

SQL> BEGIN

  2 FOR v_Rec IN (SELECT ISBN, title FROM books) LOOP  3 IF Threeemp(v_Rec.ISBN) THEN  4 DBMS_OUTPUT.PUT_LINE('"' || v_Rec.title || '" has 3 emp');  5 END IF;  6 END LOOP;  7 END;  8 /

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL> drop table emp;

Table dropped.

22.1.21.PLW-06002: Unreachable code

SQL>SQL> ALTER SESSION SET plsql_warnings = 'enable:all'  2 /

Session altered.

SQL>

7/31/2019 21karthga

http://slidepdf.com/reader/full/21karthga 35/35

SQL> DROP FUNCTION plw6002;

Function dropped.

SQL>SQL> CREATE OR REPLACE PROCEDURE plw6002  2 AS  3 l_checking BOOLEAN := FALSE;  4 BEGIN

  5 NULL;  6 IF l_checking  7 THEN  8 DBMS_OUTPUT.put_line ('Never here...');  9 ELSE 10 DBMS_OUTPUT.put_line ('Always here...'); 11 GOTO end_of_function; 12 END IF; 13 <<end_of_function>> 14 NULL; 15 END plw6002; 16 /

SP2-0804: Procedure created with compilation warnings

SQL>SQL> SHOW ERRORS PROCEDURE plw6002Errors for PROCEDURE PLW6002:

LINE/COL ERROR-------- -----------------------------------------------------------------5/4 PLW-06002: Unreachable code6/7 PLW-06002: Unreachable code8/7 PLW-06002: Unreachable code

14/4 PLW-06002: Unreachable codeSQL>SQL>