Unit - 3 PL/SQL and Conditional Statements Title P.NO. 3.1 Introduction to PL/SQL (Definition & 42 Block Structure) 3.2 Variables, Constants and Data Type 44 3.3 Assigning Values to Variables 5a 3.4 User Defined Record 52 3.5 Conditional Statements 56 3.5.1 IF..THEN statement 56 3.5.2 IF Else statements 37 3.5.3 Multiple conditions 38 3.5.4 Nested IF statements 59 3.5.5 CASE statements 62 I ee re Jump2Learn Publication [www.jump2learn.com)42. Unit-3 PL/SQL and Conditional Statements 3.1 Introduction to PL/SQL (Definition & Block Structure) PL/SQL program units organize the code into blocks. A block without a name is known as an ‘anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the Oracle database. An anonymous block is an only one-time use and useful in certain situations such as creating test ‘units. The following illustrates anonymous block syntax: [DECLARE] Declaration statements; BEGIN Execution statements; [Exception] Exception handling statements; / Let’s examine the PL/SQL block structure in greater detail. Header is Declaration Section BEGIN Execution Section EXCEPTION Exception Section END; ee | Jump2Learn Publication {www.jump2tearn.com)yptgpL/soLand Conditional Statements ag L Block Structure mous blockhas three basic sections that are the declaration, execution, and exception psa ‘he anon randing- only the execution section Is mandatory and the others are optional. fine data types, structures, and variables. You often ion section allows you to det by giving them names, data types, and initial values. the declarati les in the declaration section declare varial the execution section is required in a block structure and it must have at least one statement. The execution section isthe place where you put the execution code or business logic code. You wrnuse both procedural and SQL statements inside the execution section. word. The exception section is section is starting with the EXCEPTION key\ ier catch or handle exceptions The exception handling the code to handle exceptions. You can eith the place that you put t in the exception section. PL/SQL block structure example: Let's take a look at the simplest PL/SQL block that does nothing. BEGIN NULL; END; if you execute the above anonymous block in SQL*Plus you will see that it issues a message saying: PL/SQL procedure successfully completed. Because the NULL statement does nothing. To display database’s output on the screen, you need to: ict SQL*Plus to echo database's output First, use the SET SERVEROUTPUT ON command to instruc Plus command, which is after executing the PL/SQL block. The SET SERVEROUTPUT ON is SQL* Not related to PL/SQL. Second, use the DBMS_OUTPUT.PUT_LINE procedure to output a string on the screen. Jump2Learn Publication [www.jump2learn.com}44 Unit-3 PL/SOL and Conditional Statements The fol SET SE BEGIN ‘owing example displays a message Hello PL/SQL on a screen using SQL*Plus: /EROUTPUT ONSIZE1000000 DBMS_OUTPUT.PUT_LINE('Hello PL/SQL’); END; / 3.2 Variables, Constants and Data Type PL/SQL - Data Types constants and parameters must have a valid data type, which specifies a storage format, constraints, and a valid range of values. We will focus on the SCALAR and the LOB data types in this unit. The other: ‘two data types will be covered in other units. The PL/SQL variables, S.No Category & Description Scalar Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN. Large Object (LOB) Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. Composite Data items that have internal components that can be accessed individually. For example collections and records. Reference Pointers to other data items. Jump2Learn Publication [www.jump2learn.com]_. nits} PL/SOL and Conditional Statements 45 Jar Data Types and Subtypes 1/SOi Scalar Data Types and Subtypes come under the following categories ~ S.No Date Type & Description Numeric Numeric values on which arithmetic operations are performed. | 2 Character { Alphanumeric values that represent single characters or strings of characters. ole | 3 Boolean Logical values on which logical operations are performed. Al Datetime Dates and times. PL/SQL provides subtypes of data types. For example, the data type NUMBER has a subtype called INTEGER. You can use the subtypes in your PL/SQL program to make the data types compatible with data types in other programs while embedding the PL/SQL code in another program, such as a Java program. PL/SQL Numeric Data Types and Subtypes Following table lists out the PL/SQL pre-defined numeric data types and their sub-types ~ S.No Data Type & Description PLS_INTEGER igned integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits BINARY_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits 3 BINARY_FLOAT Single-precision IEEE 754-format floating-point number Jump2Learn Publication [www.jump2learn.com]10 a 2 13 14 .— Unit: PL/SQL and Conditional Statements BINARY_DOUBLE Double-precision IEEE 754-format floating-point number NUMBER(prec, scale) Fixed-point or floating-point number with absolute value in range 1£-130 to (but not including) 1.0£126. A NUMBER variable can also represent 0 DEC(prec, scale) ANSI specific fixed-point type with maximum precision of 38 decimal digits DECIMAL(prec, scale) IBM specific fixed-point type with maximum precision of 38 decimal digits NUMERIC(pre, secale) Floating type with maximum precision of 38 decimal digits DOUBLE PRECISION ANS! specifi floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits) FLOAT ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits) INT ANSI specific integer type with maximum precision of 38 decimal digits INTEGER ANSI and IBM specific integer type with maximum precision of 38 decimal digits SMALLINT ANSI and IBM specific integer type with maximum precision of 38 decimal digits REAL Floating-point type with maximum precision of 63 binary digits (approximately 18 dee digits) Jump2Learn Publication [www.jump2learn.com]pt 94/s0L and Conditional Statements ———_____________ * wing isa valid declaration ~ ecu ARE gum INTEGER: urn? REAL: | sma DOUBLE PRECISION; | BEGIN null; END; , i when the above code is compiled and executed, it produces the following result — a u/s Character Data Types and Subtypes Following is the detail of PL/SQL pre-defined character data types and their sub-types ~ S.No Data Type & Description ; CHAR Fixed-length character string with maximum size of 32,767 bytes % VARCHAR2, Variable-length character string with maximum size of 32,767 bytes RAW 3 Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL 4 NCHAR Fixed-length national character string with maximum size of 32,767 bytes 5 NVARCHAR2 Variable-length national character string with maximum size of 32,767 bytes Jump2Learn Publication [www.jump2learn.com)4g Unit-3 PL/SQL and Conditional Statements LONG RAW 7 Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL a ROW Physical row identifier, the address of a row in an ordinary table 9 UROWID Universal row identifier (physical, logical, or foreign row identifier) PL/SQL Boolean Data Types ‘The BOOLEAN data type stores logical values that ai values are the Boole; re used in logical operations. The logical "an values TRUE and FALSE and th the value NULL. However, SQL has no data type equivalent to BOOLEAN. Therefore, Boolean values cannot be used in ~ * SQL statements Built-in SOL functions (such as TO_CHAR) PL/SQL functions invoked from SQL statements PL/SQL Datetime and Interval Types The DATE datatype is used to store fi ixed-length datetimes, Seconds since midnight. Valid dates rar which include the time of day in Inge from January 1, 47 ‘12 BC to December 31, 9999 ap. example, the default might be 'DD-MON- the month, an abbreviation of t 01-OCT-12, igit number for the day of he month name, and the last two digits of the year. For example, Each DATE includes the century, year, month, day, hour, minute, and second. The following table shows the valid values for each field - SF eee Jump2tearn Publication (wwwjump2learn.com)> 3pi/Sab | and Conditional Statements 9 etd Name valid Datetime Values Valid Interval Values ‘eh _a712 to 9999 (excluding year 0) ‘Any nonzero integer MONTH O1to 12 Otol mited by the values of MONTH and 01 to 31 (li DAY YEAR, according to the rules of the calendar Any nonzero integer for the locale) HOUR 00 to 23 0to23 MINUTE 00 to 59 0to59 00 to 59.9(n), where 9{n) is the precision of Oto 59,9(n), where 9(n) is SECOND time fractional seconds the precision of interval fractional seconds 12 to 14 (range accommodates daylight : licabl TIMEZONE HOUR vings time changes) Not applicable TIMEZONE_MINUTE 00 to 59 Not applicable Found in the dynamic performance view TIMEZONE_REGION yeriimezONe_NAMES Not applicable Found in the dynamic performance view Not applicable TIMEZONE_ABBR — y¢TiMEZONE_NAMES PL/SaL Large Object (LOB) Data Types .e data items such as text, graphic images, video clips, Large Object (LOB) data types refer to larg: low efficient, random, piecewise access to this data. and sound waveforms. LOB data types all Following are the predefined PL/SQL LOB data types ~ Data Type Description Size apug Used to store large binary objects in operating System-dependent. Cannot system files outside the database. exceed 4 gigabytes (GB). siog _Used to store large binary objects in the 8 to 128 terabytes (TB) database. Jump2Learn Publication [www,jump2learn.com]oa ie se a Unit-3 PL/SQL and Conditional tatemeny, ; =o 108 the database. tha sore lrgebiocksOfNCHAR datain 8 to 128 7g Mee the database. PL/SQL User-Defined ‘Subtypes Asubtype isa subset of another data type, which is called its base ‘YP. A subtype aS the same ‘alld operations a its base type, but only a subset of its valid values, types in package STANDARD. Fore; INTEGER as follows - SUBTYPE CHARACTER IS CHAt PU/SQL predefines several sub “ample, PLISOL predefines the subtypes CHARACTER and I e SUBTYPE INTEGER IS NUMBER(38,0), ‘You can define and use your own subtypes. The following program illustrates, defining and using a user-defined subtype — DECLARE SUBTYPE name IS char(20}; SUBTYPE message Is. varchar2(100); salutation name; sreetings message; BEGIN salutation := 'Reader Srectings = 'Welcome to the World of PUsaL’; bms_output.put_tine(‘Hello | | salutation ENt i {1 greetings), ‘When the above code is executed atthe SQL prompt, it produces the following result Hello Reader Welcome to the World of PL/SQL PUISQL procedure successfully completed. NULLS in PLYsOL a on ee | Jump2Learn Publication [www.jump2iearn.com)1 and Conditional Statements rit-3 PLS yalues represent missing or unknown data and they are not an integer, 2 other specific data type. Note that NULL is not the sameas an empty data string cter value '\O". A null can be assigned but it cannot be equated with anything, pysaL NULL character, oF aM cor the null chara’ including itself. 33 Assigning Values to Variables assigning SQL Query Results to PL/SQL Variables you can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. ‘he following example illustrates the concept. Let us create a table named CUSTOMERS - “CREATE TABLE CUSTOMERS \t Ip INTNOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18,2), PRIMARY KEY (ID) ‘ Table Created Let us now insert some values in the table - INSERT INTO CUSTOMERS (ID, NAME,AGE,ADDRESS,SALARY) VALUES (1,'Aarohi’,32,'Ahmedabad',2000.00); 'NSERT INTO CUSTOMERS (ID, NAME,AGE,ADDRESS,SALARY) VALUES (2,'Ishani’,25,'Delhi',1500.00); " een INTO CUSTOMERS (ID,NAME,AGE,ADDRESS SALARY) UES (3,Ronit',23,'Kota’,2000.00); ns Mee. INTO CUSTOMERS (ID, NAME,AGE,ADDRESS,SALARY) 8S (4,siya’,25,'Mumbai’,6500.00); INSERT wes mn 'NTO CUSTOMERS (ID, NAME,AGE,ADDRESS SALARY) {5 ‘krish’,27,'Bhopal’,8500.00); INSERT into, VALUES (¢-4,gcSTOMERS (ID,NAME,AGE, ADDRESS SALARY) ‘Aditi,22,'MP",4500.00); Jump2Learn Publication {www.jump2iearn.com)sar nit-3 PL/SQL and Conditional Statements The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL ~ | Decuare | id customers.idsétype:=1; | ¢_name customers.namestype; | only if the condition returns , In the case of condition evaluates to then, SOL aml Skip the 100 ) THEN dbms_output.put_fine('a is greater than 100’ ENDIF; doms_output.put_line(‘Program complete END; / Code Output: Program started. Program completed. 3.5.2 IF..Else statement IF-THEN-ELSE Statement wnt is mainly used to select between two alternatives based on the + The IF-THEN-ELSE stateme! condition. «Below is the syntax representation of IF-THEN-ELSE statement. ‘Syntax for IF-THEN-ELSE Statements: IF THEN -executed only if the condition returns TRUE ELSE -execute if the condition failed (returns FALSE) END if; evaluates to «In the above syntax, keyword ‘IF will be followed by a condition "TRUE'/'FALSE’. + The control will execute the only if the condition returns . Jump2Learn Publication [www.jump2learn.com)58 Unit-3 PL/SQL and Conditional Statements + Incase of condition evaluates to then, SQL will execute , + Inany case, one of the two action blocks will be executed. Note: Whenever condition evaluates to ‘NULL’, then SQL will treat 'NULL' as 'FALSE’ Example 1: In this example, we are going to print message whether the given number is odd or even, DECLARE dbms_output put _line (‘Program started’); 1F( mod(a,2}=0) THEN. dbms_output.put_line('a is even number’); ELSE dbms_output.put_line(‘a is odd number); ENDIF; dbms_output.put_line (‘Program completed.’); END; f Code Output: Program started. ais odd number Program completed. 3.5.3 Multiple conditions IF-THEN-ELSIF Statement The IF-THEN-ELSIF statement is mainly used where one alternative should be chosen from a set of alternatives, where each alternative has its own conditions to be satisfied, + The first conditions that return will be executed, and the remaining conditions will be skipped. + The IF-THEN-ELSIF statement may contain ‘ELSE’ block in it. This ELSE" block will be executed if none of the conditions is satisfied. Note: ELSE block is optional in this conditional statement. If there is no ELSE block, and none of the condition satisfied, then the controller will skip all the action block and start executing the remaining part of the code. ‘Syntax for IF-THEN-ELSIF Statements: Jump2tearn Publication [wwwjump2tearn.com}upit-3 PL/SQL and Condit 1 THEN executed only ifthe condition returns TRUE USK ELSIF ELSE —optional END if; + In the above syntax, the control will execute the only if the condition returns . «If condition! is not satisfied, then the controller will check for condition2. «The controller will exit from the IF-statement in the following two cases. «When the controller found any condition that returns . In this case, the corresponding, action_block will be executed and the controller will exit this IF-statement block and will start ‘executing the remaining code. + When none of the conditions satisfied, the then controller will execute ELSE block if present, then will exit from the IF-statement, Note: Whenever condition evaluates to ‘NULL’, then SQL will treat ‘NULL’ as ‘FALSE’. 3.5.4 Nested IF statement NESTED-IF Statement + The NESTED-IF statement is basically allowed programmers to place one or more ‘IF’ condition inside another If" condition’s other than normal statements. «Each 'IF* condition should have a separate END IF’ ‘statement which marks the end-of-scope of that particular . + The ‘IF! statement will consider the nearest 'END IF’ statement as an endpoint for that particular condition. + The pictorial representation for NESTED-IF is shown below diagram. Jump2Learn Publication [www.jump2learn.com)50. IF —executed only if the condi ‘action blocki starts> 'F END IF; END IF corre: n returns TRUE 'sponds to condition END IF; —END IF corresponds to condition ‘Syntax Explanation: SOT aia ay ce oe Sel lump2Learn Publication {www jumpatearn com]Lnit-3 PL/SQL and Conditional Statements 61 ere we are going to see an example of Nested If - Example of Nested- If Statement: Greatest of three number Inthis example, we are going to print the greatest of three numbers by using Nested-If statement. The numbers will be assigned in the declare part, as you can see in the code below, i.e Number= 10,15 and 20 and the maximum number will be fetched using nested-if statements. DECLARE a NUMBER :=: 0; b NUMBER :=15; NUMBER °=1 BEGIN dbms_output put_ine(’Program started. iF(a > b)THEN /*Nested-if!*/ ELSE dbms_output.put_ine(‘Checking Nested-IF 1°); iF(a>c) THEN dbms_output.put_line('A is greatest’); ELSE dbms_output.put_line((C is greatest’); ENDIF; /*Nested-if2 */ dbms_output.put_ine('Checking Nested-IF 2"; IF(b>c) THEN dbms_output.put_line(’B is greatest’); ELSE dbms_output.put_line(’C is greatest ); ENDIF; Jump2Learn Publication [www,jump2learn.com}iz Unit:3 PL/SOL and Conditional stateme, a END IF; dbms_output.put_line(’Program completed.’ }; END; i Output of code: Program started, Checking Nested-IF 2 Cis greatest Program completed, 3.5.5 CASE statement ACASE statement is similar to IF-THEN-ELSIF statement that selects one alternative based on the condition from the available options. CASE statement uses "selector" rather than a Boolean expression to choose the sequence. The value of the expression in the CASE statement will be treated as a selector. The expression could be of any type (arithmetic, variables, etc.) Each alternative is assigned with a certain pre-defined value (selector), and the alternative with selector value that matches the conditional expression value will get executed. + Unlike IF-THEN-ELSIF, the CASE statement can also be used in SQL. statements, ELSE block in CASE statement holds the sequence that needs to be executed when none of the alternatives got selected. Syntax: CASE (expression) WHEN THEN action_block1; WHEN THEN action_block2; WHEN THEN action_block3; ELSE action_block_default; END CASE; + Inthe above syntax, the expression will return a value that could be of any type (variable number, ete.). Each 'WHEN' clause is treated as an alternatives which have and will be executed. + 'ELSE’ block is optional which hold the that needs to be executed when none of the alternatives match the expression value. + The 'END' marks the end of the CASE statement, and itis a mandatory part of the CASE. Example 1: Arithmetic Calculation using Case In this example, we are going to do arithmetic calculation between two numbers 55 and 5. DECLARE a NUMBEI NUMBER :=5; arth_operationVARCHAR2(20) :='MULTIPLY’; BEGIN dbms_output.put_line(‘Program started." ); CASE {arth_operation) WHEN ‘ADD’ THEN doms_output.put_line(‘Addition of the numbers are:"|| a+b); WHEN ‘SUBTRACT’ THEN dbms_output.put_line(‘Subtraction of the numbers are: "| |a-b ); WHEN ‘MULTIPLY’ THEN dbms_output.put_line(‘Multiplication of the numbers are: "|| a*b % WHEN ‘DIVIDE’ THEN dbms_output.put_line( Division of the numbers are:'|| a/b); ELSE dms_output.put_line(’No operation action defined. Invalid operation’ END CASE; dbms_output.put_line(‘Program completed.’ ); END; / Code Output: Program started. Jump2Learn Publication [www.jump2learn.com)64 Unit:3 PL/SOIL and Conditional Ststeme _——— ty Multiplication of the numbers are: 275 Program completed. SEARCHED CASE Statement ‘The SEARCHED CASE statement is similar to the CASE statement, rather than using the select, to select the alternative, SEARCHED CASE will directly have the expression defined in the whey clause. +The first WHEN clause that satisfies the condition will be executed, and the controller yi skip the remaining alternatives. Syntax: CASE WHEN THEN action_block1; WHEN THEN action_block2; WHEN THEN action_block3; ELSE action_block_default; END CASE; + In the above syntax, each WHEN clause has the separate and . © The WHEN clause for which the expression returns TRUE will be executed. ‘+ ‘ELSE’ block is optional which hold the that needs to be executed when none of the alternatives satisfies. ‘+ The 'END’ marks the end of the CASE statement and, it isa mandatory part of CASE. Example 1: Arithmetic Calculation using Searched Case ee ee Jump2Learn Publication {www.jump2learn.com]d Conditional Statements unit-3 PL/SQL ant 2. = inthis example, we are going to do arithmetic calculation between two numbers 55 and 5, DECLARE a NUMBER :=55; NUMBER :=5; arth_operationVARCHAR2(20) :='DIVIDE’; BEGIN dbms_output.put_line(‘Program started.’ ); CASE WHEN arth_operation = ‘ADD’ THEN dbms, output.put_line(‘Addition of the numbers are: "| |a+b ); WHEN arth_operation = ‘SUBTRACT" ‘THEN doms_output.put_line('Subtraction of the numbers are:"| | 2-b); WHEN arth_operation = MULTIPLY’ THEN doms_output.put_line(‘Multiplication of the numbers are: ‘|| a*b ); WHEN arth_operation = ‘DIVIDE’ THEN dbms_output.put_ine(‘Division of the numbers are: '|| a/b ): ELSE dbms_output.put_line(’No operation action defined. Invalid operatior END CASE; dbms_output.put_line('Program completed.’ ); END; / — Jump2Learn Publication [www.jump2learn.com]