PLSql

CHAPTER-12 Getting Started with PL/SQL In this chapter. Introduction SQL Vs. PL/SQL PL/SQL Fundamentals PL/SQL Block Structure PL/SQL Control Structures Database Interaction in PL/SQL Exception Handling PL/SQL :

CHAPTER-12 Getting Started with PL/SQL In this chapter. Introduction SQL Vs. PL/SQL PL/SQL Fundamentals PL/SQL Block Structure PL/SQL Control Structures Database Interaction in PL/SQL Exception Handling PL/SQL

INTRODUCTION: PL/ SQL naturally, efficient and safely extends SQL. Its basic strength is in providing server side, stored procedures, robust, portable and secured Query language. SQL VS PL/SQL 1. No Procedural Capability 2. Time Consuming Processing 3. No Error Handling Procedures. 4. No Sharing of code 5. Low performance 1. Procedural Capability 2. Reduced Network Traffic 3. Error Handling Procedures 4. Facilitate Sharing of code 5. Improved Transaction Performance 6. Portable Code:

INTRODUCTION: PL/ SQL naturally, efficient and safely extends SQL. Its basic strength is in providing server side, stored procedures, robust, portable and secured Query language. SQL VS PL/SQL 1. No Procedural Capability 2. Time Consuming Processing 3. No Error Handling Procedures. 4. No Sharing of code 5. Low performance 1. Procedural Capability 2. Reduced Network Traffic 3. Error Handling Procedures 4. Facilitate Sharing of code 5. Improved Transaction Performance 6. Portable Code

Variable. A variable is one which is used to transfer the information between a PL/SQL program and the database. Bind Variable. A bind variable is a variable that you declare in a host environment and then use to pass runtime values, whether number or character, into or out of one or more PL/SQL programs. Declaration Section. The declaration section begins a PL/SQL block. The declaration section start with the DECLARE keyword, followed by a list of variable and cursor definitions. The syntax is as: DECLARE <declaration section> begin <executable commands> exception <exception handling> END;:

Variable. A variable is one which is used to transfer the information between a PL/SQL program and the database. Bind Variable. A bind variable is a variable that you declare in a host environment and then use to pass runtime values, whether number or character, into or out of one or more PL/SQL programs. Declaration Section. The declaration section begins a PL/SQL block. The declaration section start with the DECLARE keyword, followed by a list of variable and cursor definitions. The syntax is as: DECLARE <declaration section> begin <executable commands> exception <exception handling> END;

Here, the executable commands are written inside the keyword begin and end. Every PL/SQL block executable statements are written after the keyword begin. The end; signals the end of the PL/SQL block. Begin…End. The executable commands section always starts with the keyword begin and the block of executable commands ended with the keyword End. Nested Block. When a PL/SQL program holds more than one declarations section with more than one BEGIN and END structure, then we call it as nested block. This is simple called as nested structure like any other programming languages. Like a single block, the nested block also holds executable commands, exception, etc. The structure is as : :

Here, the executable commands are written inside the keyword begin and end. Every PL/SQL block executable statements are written after the keyword begin. The end; signals the end of the PL/SQL block. Begin…End. The executable commands section always starts with the keyword begin and the block of executable commands ended with the keyword End. Nested Block. When a PL/SQL program holds more than one declarations section with more than one BEGIN and END structure, then we call it as nested block. This is simple called as nested structure like any other programming languages. Like a single block, the nested block also holds executable commands, exception, etc. The structure is as :

The structure of Nested Block is as : DECLARE <declaration section> BEGIN <block_level_name> DECARE <declarations section> BEGIN <executable commands> exception <exception handling> end <block_level_name>; <executable commands> exception <exception handling> END; :

The structure of Nested Block is as : DECLARE <declaration section> BEGIN <block_level_name> DECARE <declarations section> BEGIN <executable commands> exception <exception handling> end <block_level_name>; <executable commands> exception <exception handling> END;

Here, the block_level_name in the inner block after END is optional. But for avoiding confusion, you can place the block name after END. DBMS_OUTPUT.PUT_LINE. In PL/SQL, you can make use of a special packaged procedure, DBMS_OUTPUT and within the procedure, you use the PUT_LINE procedure. The DBMS_OUTPUT.PUT_LINE (or dbms_output.put_line) is a package which allows displaying of messages and variable values on the console. Make sure to issue command SET SERVEROUTPUT ON at SQL command prompt before using any procedure of DBMS_OUTPUT package. The syntax is as: DBMS_OUTPUT.PUT_LINE(message); :

Here, the block_level_name in the inner block after END is optional. But for avoiding confusion, you can place the block name after END. DBMS_OUTPUT.PUT_LINE. In PL/SQL, you can make use of a special packaged procedure , DBMS_OUTPUT and within the procedure, you use the PUT_LINE procedure. The DBMS_OUTPUT.PUT_LINE (or dbms_output.put_line) is a package which allows displaying of messages and variable values on the console. Make sure to issue command SET SERVEROUTPUT ON at SQL command prompt before using any procedure of DBMS_OUTPUT package. The syntax is as: DBMS_OUTPUT.PUT_LINE(message);

Attributes. Attributes allow us to refer to data types and objects from the database. PL/SQL variables can have attributes. The following are the types of attributes supported by PL/SQL: - %TYPE - %ROWTYPE %TYPE. The %TYPE is used when declaring variables that refers to the database columns. That is, if you use the %TYPE declaration, then the variables only inherits the definition of the column used to define it. For example, DECLARE myname student.name%TYPE; Here, the PL/SQL variable myname Here the PL/SQL variable myname is a name type variable from student table. :

Attributes. Attributes allow us to refer to data types and objects from the database. PL/SQL variables can have attributes. The following are the types of attributes supported by PL/SQL: - %TYPE - %ROWTYPE %TYPE. The %TYPE is used when declaring variables that refers to the database columns. That is, if you use the %TYPE declaration, then the variables only inherits the definition of the column used to define it. For example, DECLARE myname student.name%TYPE; Here, the PL/SQL variable myname Here the PL/SQL variable myname is a name type variable from student table.

%ROWTYPE. A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. i.e., if you use the %ROWTYPE declaration, the variable inherits the column and datatype information for all the columns in the cursor’s result set. %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched by a cursor. The result is a record type in which the fields have the same names and types as the attributes of the relation, For example, DECLARE mytuple student%ROWTYPE; Here, mytuple is a variables, which stores a row from student table. :

%ROWTYPE. A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. i.e., if you use the %ROWTYPE declaration, the variable inherits the column and datatype information for all the columns in the cursor’s result set. %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched by a cursor. The result is a record type in which the fields have the same names and types as the attributes of the relation, For example, DECLARE mytuple student%ROWTYPE; Here, mytuple is a variables, which stores a row from student table.

Slide 9:

declare k emp%rowtype; begin select * into k from emp where ename='JONES'; dbms_output.put_line(k.empno); dbms_output.put_line(k.ename); dbms_output.put_line(k.job); end; / declare k emp.ename%type; begin select ename into k from emp where empno=7566; dbms_output.put_line(k); end; /

Slide 10:

DECLARE name varchar2(30); BEGIN name:='&string'; DBMS_OUTPUT.PUT_LINE(initcap(name); END;

Constant. The constant type declaration in a PL/SQL declaration section does not change its value at run time. This is declared with the keyword CONSTANT. The constant keyword must precede with a datatype . For example DECLARE PI CONSTATNT NUMBER(9,3):=3.142; Here, 3.142 is the initial and final value for a constant PI. Literal. A literal is a value which work like a constant. The term literal and constant are synonymous. Both literal and constant refer to a fixed datatype value. Literals are three types: Numeric literals. For example, 100, 100.50, NULL, etc. String Literals. For example, ‘CBSE IP’, ‘Hello’, etc. Boolean literals. For example, TRUE, FALSE, etc. :

Constant. The constant type declaration in a PL/SQL declaration section does not change its value at run time. This is declared with the keyword CONSTANT. The constant keyword must precede with a datatype . For example DECLARE PI CONSTATNT NUMBER(9,3):=3.142; Here, 3.142 is the initial and final value for a constant PI. Literal. A literal is a value which work like a constant. The term literal and constant are synonymous. Both literal and constant refer to a fixed datatype value. Literals are three types: Numeric literals . For example, 100, 100.50, NULL, etc. String Literals . For example, ‘CBSE IP’, ‘Hello’, etc. Boolean literals . For example, TRUE, FALSE, etc.

PL/SQL Assignment. To assign a value to a variable in PL/SQL, we use := sign. The assignment can occur either immediately after the type of the variable is declared or anywhere in the executable portion of the program. For example, NUM1:=NUM1+NUM2+(NUM3*3); SCHOOL:=‘KV2OCFSPN’; FLAGE1:=TRUE; Operator:- An operator is one which work on data items and performs some mathematical or logical calculation or changes the data. PL/SQL provides three types of operators : Arithmetical, Relational, and Logical. For example, +, -, *, etc. Arithmetical Operators. These are general mathematical operators, which work on more than one operand. For these operators always tow or more than two operands are needed. For example, sum:=num1+num2;:

PL/SQL Assignment. To assign a value to a variable in PL/SQL, we use := sign. The assignment can occur either immediately after the type of the variable is declared or anywhere in the executable portion of the program. For example, NUM1:=NUM1+NUM2+(NUM3*3); SCHOOL:=‘KV2OCFSPN’; FLAGE1:=TRUE; Operator:- An operator is one which work on data items and performs some mathematical or logical calculation or changes the data. PL/SQL provides three types of operators : Arithmetical, Relational, and Logical . For example, +, -, *, etc. Arithmetical Operators. These are general mathematical operators, which work on more than one operand. For these operators always tow or more than two operands are needed. For example, sum:=num1+num2;

Relational Operators. The operators used to do comparison are called relational operators. These are : =>, <, >, and !=. Logical Operators. These operators are used to combine one or more than one relational expression. These are : OR, AND and NOT. Expressions. Expressions are the constructs which generate new values by the application of operators. An expression consists of operators and operands. i.e., variables, constants and functions. For example, NET_VALUE:=ROUND(2345.90,0)+100; The expression are further divided into many types. These are: - Arithmetic or Numeric expressions . These expressions perform only on numeric data. For example, NSUM=N1+20; - String expressions. These expressions perform only on string or character data. E.g., BOOK:=INITCAP (‘informatics practices’) + ‘Class XII’;:

Relational Operators. The operators used to do comparison are called relational operators. These are : =>, <, >, and !=. Logical Operators . These operators are used to combine one or more than one relational expression . These are : OR, AND and NOT. Expressions. Expressions are the constructs which generate new values by the application of operators. An expression consists of operators and operands. i.e., variables, constants and functions. For example, NET_VALUE:=ROUND(2345.90,0)+100; The expression are further divided into many types. These are: - Arithmetic or Numeric expressions . These expressions perform only on numeric data. For example, NSUM=N1+20; - String expressions. These expressions perform only on string or character data. E.g., BOOK:=INITCAP (‘informatics practices’) + ‘Class XII’;

Date Expressions. These expressions perform only on date data. For example, C_DATE:=ADD_MONTHS(’23-OCT-2008’,2); - Boolean expressions. These expressions performs only on Boolean or logical data. For example, IF FLAG = TRUE THEN <CONDITION> END IF PL/SQL Comments. A constant is the text which appear within the SQL statements. These are not affect to the SQL statement execution. A comment can be written in two ways: - begin the comment with /* and ended with */ - or, begin with - - (hyphens) and proceed with the text of the comment.:

Date Expressions. These expressions perform only on date data. For example, C_DATE:=ADD_MONTHS(’23-OCT-2008’,2); - Boolean expressions. These expressions performs only on Boolean or logical data. For example, IF FLAG = TRUE THEN <CONDITION> END IF PL/SQL Comments. A constant is the text which appear within the SQL statements. These are not affect to the SQL statement execution. A comment can be written in two ways: - begin the comment with /* and ended with */ - or, begin with - - (hyphens) and proceed with the text of the comment.

v_sal NUMBER(9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal:=&p_monthly_sal *12; END; -- This is the end of the block.:

v_sal NUMBER(9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal:=&p_monthly_sal *12; END; -- This is the end of the block.

Control Structure in PL/SQL Database. Database is the collection of related data. Sequence. Sequence consists of operation followed by another and so on, e.g., a sequence of two or more operations is always arranged in a logical order. Selection. The selection structure allows execution of one of the two alternative paths depending on a condition. That is, the selection structure tests a condition is true or false. A condition is any variable or expression that returns a Boolean value (TRUE or FALSE). Repetition. In repetition, an operation or a set of operations is repeated as long as some conditions are true. This is also called as looping. PL/SQL has three forms of loop statements: LOOP, WHILE-LOOP and FOR-LOOP. :

Control Structure in PL/SQL Database. Database is the collection of related data. Sequence. Sequence consists of operation followed by another and so on, e.g., a sequence of two or more operations is always arranged in a logical order. Selection. The selection structure allows execution of one of the two alternative paths depending on a condition. That is, the selection structure tests a condition is true or false. A condition is any variable or expression that returns a Boolean value (TRUE or FALSE). Repetition. In repetition, an operation or a set of operations is repeated as long as some conditions are true. This is also called as looping. PL/SQL has three forms of loop statements: LOOP, WHILE-LOOP and FOR-LOOP .

IF-THEN Statement. The IF statement allows branching (decision making) depending upon the value or state of variables. In PL/SQL, the IF statement uses with relational operators. This statement is called decision statement because it tests a relationship, using the relational operator. Then it makes a decision about which statement to execute next, based on the result of that decision. The syntax is as : IF condition THEN Sequence of PL/SQL statements; END IF; For example; TEMP NUMBER(5, 2):=&TEMP; BEGIN IF TEMP=98.6 THEN DBMS_OUTPUT.PUT_LINE (‘Body temperature normal’); END IF; END;:

IF-THEN Statement. The IF statement allows branching (decision making) depending upon the value or state of variables. In PL/SQL, the IF statement uses with relational operators. This statement is called decision statement because it tests a relationship, using the relational operator. Then it makes a decision about which statement to execute next, based on the result of that decision. The syntax is as : IF condition THEN Sequence of PL/SQL statements; END IF; For example; TEMP NUMBER(5, 2):=&TEMP; BEGIN IF TEMP=98.6 THEN DBMS_OUTPUT.PUT_LINE (‘Body temperature normal’); END IF; END;

IF-THEN-ELSE Statement. The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The syntax is as: IF condition THEN Sequence of PL/SQL statements; ELSE Sequence of PL/SQL statements; END IF; The sequence of statements in the ELSE clause is executed only if the condition is false or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed. For example, :

IF-THEN-ELSE Statement. The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The syntax is as: IF condition THEN Sequence of PL/SQL statements; ELSE Sequence of PL/SQL statements; END IF; The sequence of statements in the ELSE clause is executed only if the condition is false or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed. For example,

Slide 19:

declare TEMP NUMBER(5, 2):=&TEMP; BEGIN IF TEMP=98.6 THEN dbms_output.put_line ('Body temperature is normal'); ELSE dbms_output.put_line ('Body temperature not normal'); END IF; END; /

IF-THEN-ELSIF Statement. The third form of IF statement uses the keyword ELSIF (not ELSEIF) to introduce additional conditions. The syntax is as: IF condition1 THEN Sequence of PL/SQL statements; ELSIF condition2 THEN Sequence of PL/SQL statements; ELSE Sequence of PL/SQL statements; END IF; IF the first condition is false or NULL, the ELSIF clause tests another condition. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is true its associated sequence of statements is executed and control passes to the next statement.:

IF-THEN-ELSIF Statement. The third form of IF statement uses the keyword ELSIF ( not ELSEIF ) to introduce additional conditions. The syntax is as: IF condition1 THEN Sequence of PL/SQL statements; ELSIF condition2 THEN Sequence of PL/SQL statements; ELSE Sequence of PL/SQL statements; END IF; IF the first condition is false or NULL, the ELSIF clause tests another condition. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is true its associated sequence of statements is executed and control passes to the next statement.

Declare Basicsal NUMBER(8, 2); Grade Char(1); If Basicsal>=6500 Then Grade:=‘A’; ELSIF Basicsal>=5000 and Basicsal<6500 Then Grade:=‘B’; ELSIF Basicsal>=4000 and Basicsal<5000 Then Grade:=‘C’; ELSE Grade:=‘’; END IF; DBMS_OUTPUT.PUT_LINE (‘Grade is’ || grade); END;:

Declare Basicsal NUMBER(8, 2); Grade Char(1); If Basicsal>=6500 Then Grade:=‘A’; ELSIF Basicsal>=5000 and Basicsal<6500 Then Grade:=‘B’; ELSIF Basicsal>=4000 and Basicsal<5000 Then Grade:=‘C’; ELSE Grade:=‘’; END IF; DBMS_OUTPUT.PUT_LINE (‘Grade is’ || grade); END;

LOOP Statement. The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. The syntax is as: LOOP Sequence of PL/SQL statements; END LOOP; With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. For example. :

LOOP Statement. The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. The syntax is as: LOOP Sequence of PL/SQL statements; END LOOP; With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. For example.

/*program to find the sum of first 10 natural numbers */ Declare nsum number(6):=0; I number:=1; Begin Loop nsum:=nsum+ i; i:=i+1; if i>10 then exit; end if; end loop; dbms_output.put_line (‘The sum is:’ || nsum); end;:

/*program to find the sum of first 10 natural numbers */ Declare nsum number(6):=0; I number:=1; Begin Loop nsum:=nsum+ i; i:=i+1; if i>10 then exit; end if; end loop; dbms_output.put_line (‘The sum is:’ || nsum); end;

EXIT Statement. The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement. Remember the EXIT statement must be placed inside a loop as show in previous example. EXIT WHEN Statement. The EXIT WHEN statement lets a loop complete conditionally. If the condition is true, the loop completes and control passes to the next statement after the loop. For example, :

EXIT Statement. The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement. Remember the EXIT statement must be placed inside a loop as show in previous example. EXIT WHEN Statement. The EXIT WHEN statement lets a loop complete conditionally. If the condition is true, the loop completes and control passes to the next statement after the loop. For example,

/* Program to find the sum of first 10 natural number */ Declare nsum nubmer(6):=0; I number:=1; Begin Loop nsum:=nusm+1; i:=i+1; EXIT WHEN I=11; -- Because I will process till 10 END LOOP; dbms_output.put_line(‘The sum is: ‘|| nsum); End;:

/* Program to find the sum of first 10 natural number */ Declare nsum nubmer(6):=0; I number:=1; Begin Loop nsum:=nusm+1; i:=i+1; EXIT WHEN I=11; -- Because I will process till 10 END LOOP; dbms_output.put_line(‘The sum is: ‘|| nsum); End;

WHIEL Loop Statement. The WHILE LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP. The syntax is as: WHIE condition LOOP Sequence of PL/SQL statements; END LOOP; Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.:

WHIEL Loop Statement. The WHILE LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP. The syntax is as: WHIE condition LOOP Sequence of PL/SQL statements; END LOOP; Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

/* Program to find the sum of first 10 natural numbers */ Declare nsum number(6):=0; I number:=1; begin while I<11 loop --because it will execute till 10 nsum:=nsum+nsum+I; I:=I+1; end loop; dbms_output.put_line (‘The sum is :’||nsum); end;:

/* Program to find the sum of first 10 natural numbers */ Declare nsum number(6):=0; I number:=1; begin while I<11 loop --because it will execute till 10 nsum:=nsum+nsum+I; I:=I+1; end loop; dbms_output.put_line (‘The sum is :’||nsum); end;

FOR Loop Statement. Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double do (..)serves as the range operator. The syntax is as: For counter IN [REVERSE] lower_bound..higher_bound LOOP Sequence of PL/SQL statements; END LOOP; For example, :

FOR Loop Statement. Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double do (..)serves as the range operator. The syntax is as: For counter IN [REVERSE] lower_bound..higher_bound LOOP Sequence of PL/SQL statements; END LOOP; For example,

/* Program to find the sum of first 10 natural numbers */ Declare nsum number:=0; I number:=1; Begin For I IN 1..10 LOOP nsum:=nsum + I; END LOOP; DBMS_OUTPUT.PUT_LINE (‘The sum is: ’||nsum); End;:

/* Program to find the sum of first 10 natural numbers */ Declare nsum number:=0; I number:=1; Begin For I IN 1..10 LOOP nsum:=nsum + I; END LOOP; DBMS_OUTPUT.PUT_LINE (‘The sum is: ’||nsum); End;

PL/ SQL BLOCK STRUCTURE: DECLARE .. .. .. BEGIN .. .. .. EXCEPTION .. .. .. END; DATATYPES: PL/ SQL provides variety of predefined datatypes such as: 1. Scalar data type 2. Composite data type 3. Reference data type 4. LOB (Large Object) data type:

PL/ SQL BLOCK STRUCTURE: DECLARE .. .. .. BEGIN .. .. .. EXCEPTION .. .. .. END; DATATYPES: PL/ SQL provides variety of predefined datatypes such as: 1. Scalar data type 2. Composite data type 3. Reference data type 4. LOB (Large Object) data type

SCALAR DATA TYPE: It is atomic i.e. it is not made up of other data types. It consists of the following: char varchar2 number Boolean date long binary_integer pls_integer COMPOSITE DATA TYPE: It is made up of other data types e.g. record, table and VARRAY. REFERENCE DATA TYPE: It holds values called pointers that point other program items. e.g. REF CURSOR LOB (Large Object) Data Types: A LOB type holds values, called locators that specify the location of large objects stored out of line. The LOB types are BFILE, BLOB, CLOB, NCLOB. LITERALS AND CONSTANTS: - Number literals - Character/ String literals - Boolean literals :

SCALAR DATA TYPE: It is atomic i.e. it is not made up of other data types. It consists of the following: char varchar2 number Boolean date long binary_integer pls_integer COMPOSITE DATA TYPE: It is made up of other data types e.g. record, table and VARRAY. REFERENCE DATA TYPE: It holds values called pointers that point other program items. e.g. REF CURSOR LOB (Large Object) Data Types: A LOB type holds values, called locators that specify the location of large objects stored out of line. The LOB types are BFILE, BLOB, CLOB, NCLOB. LITERALS AND CONSTANTS: - Number literals - Character/ String literals - Boolean literals

A constant is declared with a data type as per the following syntax: <constant name> CONSTANT <data type> := <value> Variables and Types Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be * One of the types used by SQL for database columns * A generic type used in PL/SQL such as NUMBER * Declared to be the same as the type of some database column The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare: DECLARE price NUMBER; item VARCHAR(20); Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns. :

A constant is declared with a data type as per the following syntax: <constant name> CONSTANT <data type> := <value> Variables and Types Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be * One of the types used by SQL for database columns * A generic type used in PL/SQL such as NUMBER * Declared to be the same as the type of some database column The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare: DECLARE price NUMBER; item VARCHAR(20); Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.

Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example: :

Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column . If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE dpname dept.dname%TYPE; gives PL/SQL variable dpname whatever type was declared for the name column in relation dname. A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance: DECLARE dpname dname%ROWTYPE; makes variable dpname be a record with fields name and manufacture, assuming that the relation has the schema dept(dname, loc) :

DECLARE dpname dept.dname%TYPE; gives PL/SQL variable dpname whatever type was declared for the name column in relation dname. A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance: DECLARE dpname dname%ROWTYPE; makes variable dpname be a record with fields name and manufacture, assuming that the relation has the schema dept(dname, loc)

The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example: DECLARE a NUMBER := 3; BEGIN a := a + 1; END; run; This program has no effect when run, because there are no changes to the database.:

The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example: DECLARE a NUMBER := 3; BEGIN a := a + 1; END; run; This program has no effect when run, because there are no changes to the database.

Control Flow in PL/SQL PL/SQL allows you to branch and create loops in a fairly familiar way. An IF statement looks like: IF <condition> THEN <statement_list> ELSE <statement_list> END IF; The ELSE part is optional. If you want a multiway branch, use: :

Control Flow in PL/SQL PL/SQL allows you to branch and create loops in a fairly familiar way. An IF statement looks like: IF <condition> THEN <statement_list> ELSE <statement_list> END IF; The ELSE part is optional. If you want a multiway branch, use:

IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF <condition_n> THEN ... ELSE ... END IF; The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert: :

IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF <condition_n> THEN ... ELSE ... END IF; The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(b,a); ELSE INSERT INTO T1 VALUES(b+10,a+10); END IF; END; run;:

DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(b,a); ELSE INSERT INTO T1 VALUES(b+10,a+10); END IF; END; run;

Loops are created with the following: LOOP <loop_body> /* A list of statements. */ END LOOP; At least one of the statements in <loop_body> should be an EXIT statement of the form EXIT WHEN <condition>; The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples: :

Loops are created with the following: LOOP <loop_body> /* A list of statements. */ END LOOP; At least one of the statements in <loop_body> should be an EXIT statement of the form EXIT WHEN <condition>; The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES(i, i); i := i+1; EXIT WHEN i>100; END LOOP; END; .run; :

DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES (i, i); i := i+1; EXIT WHEN i>100; END LOOP; END; .run;

Some other useful loop-forming statements are: * EXIT by itself is an unconditional loop break. Use it inside a conditional if you like. * A WHILE loop can be formed with · WHILE <condition> LOOP · <loop_body> END LOOP; * A simple FOR loop can be formed with: · FOR <var> IN <start>..<finish> LOOP · <loop_body> END LOOP; Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.:

Some other useful loop-forming statements are: * EXIT by itself is an unconditional loop break. Use it inside a conditional if you like. * A WHILE loop can be formed with · WHILE <condition> LOOP · <loop_body> END LOOP; * A simple FOR loop can be formed with: · FOR <var> IN <start>..<finish> LOOP · <loop_body> END LOOP; Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.

EXCEPTION HANDLING: In the PL/SQL language, errors of any kind are treated as exceptions -- situations that should not occur -- in your program. An exception can be one of the following: An error generated by the system (such as "out of memory" or "duplicate value in index") An error caused by a user action A warning issued by the application to the user PL/SQL traps and responds to errors using an architecture of exception handlers. The exception-handler mechanism allows you to cleanly separate your error processing code from your executable statements. It also provides an event-driven model, as opposed to a linear code model, for processing errors. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section. :

EXCEPTION HANDLING: In the PL/SQL language, errors of any kind are treated as exceptions -- situations that should not occur -- in your program . An exception can be one of the following: An error generated by the system (such as "out of memory" or "duplicate value in index") An error caused by a user action A warning issued by the application to the user PL/SQL traps and responds to errors using an architecture of exception handlers. The exception-handler mechanism allows you to cleanly separate your error processing code from your executable statements. It also provides an event-driven model, as opposed to a linear code model, for processing errors. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section.

When an error occurs in PL/SQL, whether a system error or an application error, an exception is raised. The processing in the current PL/SQL block's execution section halts and control is transferred to the separate exception section of your program, if one exists, to handle the exception. You cannot return to that block after you finish handling the exception. Instead, control is passed to the enclosing block, if any. :

When an error occurs in PL/SQL, whether a system error or an application error, an exception is raised. The processing in the current PL/SQL block's execution section halts and control is transferred to the separate exception section of your program, if one exists, to handle the exception. You cannot return to that block after you finish handling the exception. Instead, control is passed to the enclosing block, if any.

Why Exception Handling? It is a sad fact of life that most programmers never take the time to properly bullet-proof their programs. Instead, wishful thinking often reigns. Most of us find it hard enough -- and more than enough work -- to simply write the code that implements the positive aspects of an application: maintaining customers, generating invoices, etc. It is devilishly difficult from both a psychological standpoint and a resources perspective to focus on the negative side of our life: what happens when the user presses the wrong key? If the database is unavailable, what should I do? As a result, we write applications that often assume the best of all possible worlds, hoping that our programs are bug-free, that users will enter only the correct data in only the correct fashion, and that all systems (hardware and software) will always be a "go." :

Why Exception Handling? It is a sad fact of life that most programmers never take the time to properly bullet-proof their programs. Instead, wishful thinking often reigns. Most of us find it hard enough -- and more than enough work -- to simply write the code that implements the positive aspects of an application: maintaining customers, generating invoices, etc . It is devilishly difficult from both a psychological standpoint and a resources perspective to focus on the negative side of our life: what happens when the user presses the wrong key? If the database is unavailable, what should I do? As a result, we write applications that often assume the best of all possible worlds, hoping that our programs are bug-free, that users will enter only the correct data in only the correct fashion, and that all systems (hardware and software) will always be a "go."

Of course, harsh reality dictates that no matter how hard you try, there will always be one more bug in your application. And your users will always find just the right sequence of keystrokes it takes to make a screen implode. The situation is clear: either you spend the time up front to properly debug and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking frantic calls from your users and putting out the fires. You know what you should do. Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors in your programs. It is entirely feasible within the PL/SQL language to build an application which fully protects the user and the database from errors. :

Of course, harsh reality dictates that no matter how hard you try, there will always be one more bug in your application. And your users will always find just the right sequence of keystrokes it takes to make a screen implode. The situation is clear: either you spend the time up front to properly debug and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking frantic calls from your users and putting out the fires. You know what you should do. Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors in your programs. It is entirely feasible within the PL/SQL language to build an application which fully protects the user and the database from errors.

The exception handler model offers the following advantages: Event-driven handling of errors. As we've mentioned, PL/SQL exception handling follows an event-driven rather than a linear code model. No matter how a particular exception is raised, it is handled by the same exception handler in the exception section. You do not have to check repeatedly for a condition in your code, but instead can insert an exception for that condition once in the exception section and be certain that it will be handled throughout that block (and all of its enclosing blocks). Clean separation of error-processing code. With the exception-handling model, whenever an exception is raised, program control transfers completely out of the normal execution sequence and into the exception section. Instead of placing error-handling logic throughout different sections of your program, you can consolidate all of this logic into a single, separate section. Furthermore, if you need to add new exceptions in your program (perhaps you overlooked a possible problem, or a new kind of system error has been identified), you do not have to figure out where in your executable code to put the error-handling logic. Simply add another exception handler at the bottom of the block. :

The exception handler model offers the following advantages: Event-driven handling of errors. As we've mentioned, PL/SQL exception handling follows an event-driven rather than a linear code model. No matter how a particular exception is raised, it is handled by the same exception handler in the exception section. You do not have to check repeatedly for a condition in your code, but instead can insert an exception for that condition once in the exception section and be certain that it will be handled throughout that block (and all of its enclosing blocks). Clean separation of error-processing code. With the exception-handling model, whenever an exception is raised, program control transfers completely out of the normal execution sequence and into the exception section. Instead of placing error-handling logic throughout different sections of your program, you can consolidate all of this logic into a single, separate section. Furthermore, if you need to add new exceptions in your program (perhaps you overlooked a possible problem, or a new kind of system error has been identified), you do not have to figure out where in your executable code to put the error-handling logic. Simply add another exception handler at the bottom of the block.

Improved reliability of error handling. It is quite difficult for errors to go undetected with the PL/SQL error-handling model. If there is a handler, then that exception will be dealt with in the current block or in an enclosing block. Even if there is no explicit handler for that error, normal code execution will still stop. Your program cannot simply "work through" an error -- unless you explicitly organize your code to allow this. There is no avoiding the fact that if you want to trap errors in your PL/SQL programs you will have to write some additional code. The exception handler architecture, however, minimizes the amount of code you will need to write, and offers the possibility of guarding against all problems that might arise in your application. The following sections look at how you define, raise, and handle exceptions in PL/SQL :

Improved reliability of error handling. It is quite difficult for errors to go undetected with the PL/SQL error-handling model. If there is a handler, then that exception will be dealt with in the current block or in an enclosing block. Even if there is no explicit handler for that error, normal code execution will still stop. Your program cannot simply "work through" an error -- unless you explicitly organize your code to allow this. There is no avoiding the fact that if you want to trap errors in your PL/SQL programs you will have to write some additional code. The exception handler architecture, however, minimizes the amount of code you will need to write, and offers the possibility of guarding against all problems that might arise in your application. The following sections look at how you define, raise, and handle exceptions in PL/SQL

The SELECT INTO Clause The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database.  The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.    SQL> declare   2    v_authName author.author_last_name%type;   3  begin   4    select   5      author_last_name into v_authName   6    from   7      author   8    where   9      author_key = 'A103';  10  11    dbms_output.put_line ('Name: '||v_authName);  12  end;  13  /:

The SELECT INTO Clause The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables . SQL> declare 2    v_authName author.author_last_name%type; 3  begin 4    select 5      author_last_name into v_authName 6    from 7      author 8    where 9      author_key = 'A103'; 10 11    dbms_output.put_line ('Name: '||v_authName); 12  end; 13  /

Here the author_key was used to retrieve one author’s last name and place it in the variable called v_authName.  The query can also retrieve an entire row as a record with SELECT INTO.    In the example below a record based on the columns of the author table is declared in line two below.  Because v_author is declared as an author table %rowtype , you can safely use the SELECT * clause to retrieve all the columns.  SQL> declare   2    v_author author%rowtype;   3  begin   4    select   5      * into v_author   6    from   7      author   8    where   9      author_key = 'A103';  10  11    dbms_output.put_line('Name:                 '||v_author.author_first_name||' '||                 v_author.author_last_name);  12  end;  13  /:

Here the author_key was used to retrieve one author’s last name and place it in the variable called v_authName . The query can also retrieve an entire row as a record with SELECT INTO. In the example below a record based on the columns of the author table is declared in line two below.  Because v_author is declared as an author table %rowtype , you can safely use the SELECT * clause to retrieve all the columns. SQL> declare 2    v_author author%rowtype; 3  begin 4    select 5      * into v_author 6    from 7      author 8    where 9      author_key = 'A103'; 10 11    dbms_output.put_line('Name: '||v_author.author_first_name||' '|| v_author.author_last_name); 12  end; 13  /

Home Assignments Ch-12:

Home Assignments Ch-12 Type Questions A 1,2,3,4,6,9,10,11,12,13,16,21,22,24,26,27,28,29,32,36,37,41,42,44,46 B 1,5,7,9,10,12,14,21,26,27,28,29 C 2,3,4

CHAPTER-12 Getting Started with PL/SQL In this chapter. Introduction SQL Vs. PL/SQL PL/SQL Fundamentals PL/SQL Block Structure PL/SQL Control Structures Database Interaction in PL/SQL Exception Handling PL/SQL :

CHAPTER-12 Getting Started with PL/SQL In this chapter. Introduction SQL Vs. PL/SQL PL/SQL Fundamentals PL/SQL Block Structure PL/SQL Control Structures Database Interaction in PL/SQL Exception Handling PL/SQL

Prior to 1991, in order to use procedural constructs with SQL, one needed to use PRO*C where Oracle SQL statements were embedded in C code. This C code was precompiled to convert SQL statements into library calls so that they can be processed. In 1991, Oracle released PL/SQL version 1.0 to support procedural constructs. After that with each version of Oracle, PL/SQL was also refined (developed) and supported many more advanced features. PL/SQL version 8.0 was released with Oracle8 release 8.0. PL/SQL supports many enhancements of Oracle8 including Large Objects, Object Oriented Design and development, collections (VARRYAs and nested tables), and Oracle/AQ (the Oracle/Advanced Queueing) facility. :

Prior to 1991, in order to use procedural constructs with SQL, one needed to use PRO*C where Oracle SQL statements were embedded in C code. This C code was precompiled to convert SQL statements into library calls so that they can be processed. In 1991, Oracle released PL/SQL version 1.0 to support procedural constructs. After that with each version of Oracle, PL/SQL was also refined (developed) and supported many more advanced features. PL/SQL version 8.0 was released with Oracle8 release 8.0. PL/SQL supports many enhancements of Oracle8 including Large Objects, Object Oriented Design and development, collections (VARRYAs and nested tables), and Oracle/AQ (the Oracle/Advanced Queueing) facility.

PL/SQL is a procedural extension of Oracle-SQL that offers language constructs similar to those in imperative programming languages. PL/SQL allows its users to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, modules etc. SQL VS. PL/SQL:- SQL- Structure Query Language – is the natural language on most DBMSs. SQL proves very useful for interactive processing but it has certain limitations when used as conventional programming language. Some of these limitations of SQL are: i) No procedural Capabilities. SQL does not offer any type of procedural facilities/capabilities such as condition-testing, looping, branching etc., which are necessary for being used as a conventional programming language.:

PL/SQL is a procedural extension of Oracle-SQL that offers language constructs similar to those in imperative programming languages. PL/SQL allows its users to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, modules etc. SQL VS. PL/SQL:- SQL- Structure Query Language – is the natural language on most DBMSs. SQL proves very useful for interactive processing but it has certain limitations when used as conventional programming language. Some of these limitations of SQL are: i) No procedural Capabilities . SQL does not offer any type of procedural facilities/capabilities such as condition-testing, looping, branching etc., which are necessary for being used as a conventional programming language.

ii) Time Consuming Processing. All SQL statements are executed by the database server or Oracle Engine. For this the SQL statements are passed to Oracle Engine one at a time. Therefore, to execute four SQL statements (say for instance), four times, separately, the SQL statements will be passed to Oracle Engine. Compare this to four people going to same destination in separate vehicles. Wouldn’t it increate the traffic over the network? Remember, more traffic means slower data processing. iii) No Error Handling Routines/Procedures. While processing SQL statements, if an error occurs, SQL knows no way to handle these errors. On the contrary, PL/SQL not only provides procedural constructs, but also supports SQL’s data manipulation features. :

ii) Time Consuming Processing . All SQL statements are executed by the database server or Oracle Engine. For this the SQL statements are passed to Oracle Engine one at a time. Therefore, to execute four SQL statements (say for instance), four times, separately, the SQL statements will be passed to Oracle Engine. Compare this to four people going to same destination in separate vehicles. Wouldn’t it increate the traffic over the network? Remember, more traffic means slower data processing. iii) No Error Handling Routines/Procedures. While processing SQL statements, if an error occurs, SQL knows no way to handle these errors. On the contrary, PL/SQL not only provides procedural constructs, but also supports SQL’s data manipulation features.

The advantages offered by PL/SQL are as follows: i) Procedural Capabilities. PL/SQL is the procedural extension to Oracle SQL. That is, apart from supporting SQL, it also supports high level language features such as block structure, conditional statements, looping statements etc. ii) Reduced Network Traffic. While SQL statements are sent one at a time, PL/SQL sends an entire block of statements (i.e., a block containing multiple statements) at one time. This reduces network traffic considerably. iii) Error handling Procedures/Routines. PL/SQL supports customized error handling routines i.e., a user can write his/her own error handling routines. iv) Facilitates Sharing. PL/SQL allows the user to store compiled code in the database, thereby providing the access and sharing of the same subprograms by multiple applications.:

The advantages offered by PL/SQL are as follows: i) Procedural Capabilities. PL/SQL is the procedural extension to Oracle SQL. That is, apart from supporting SQL, it also supports high level language features such as block structure, conditional statements, looping statements etc. ii) Reduced Network Traffic. While SQL statements are sent one at a time, PL/SQL sends an entire block of statements (i.e., a block containing multiple statements) at one time. This reduces network traffic considerably. iii) Error handling Procedures/Routines. PL/SQL supports customized error handling routines i.e., a user can write his/her own error handling routines. iv) Facilitates Sharing. PL/SQL allows the user to store compiled code in the database, thereby providing the access and sharing of the same subprograms by multiple applications.

v) Improved Transaction Performance. PL/SQL supports all sorts of calculations without involving Oracle Engine. This results in improved transaction performance. vi) Portable Code. PL/SQL code can be executed on any computer hardware and operating system provided Oracle is loaded on it as PL/SQL is integrated with the database server. It does not exist as a stand alone language. Majorly, we can also say that SQL is declarative i.e., it just declares WHAT is required whereas, PL/SQL is procedural (not declarative) i.e., it specifies exactly HOW things get done.:

v) Improved Transaction Performance . PL/SQL supports all sorts of calculations without involving Oracle Engine. This results in improved transaction performance. vi) Portable Code . PL/SQL code can be executed on any computer hardware and operating system provided Oracle is loaded on it as PL/SQL is integrated with the database server. It does not exist as a stand alone language. Majorly, we can also say that SQL is declarative i.e., it just declares WHAT is required whereas, PL/SQL is procedural (not declarative) i.e., it specifies exactly HOW things get done.

PL/SQL Architecture PL/SQL is a technology and an integrated part of Oracle. It has its own processing engine that executes PL/SQL blocks and subprograms. This engine can be installed in the server or in any of the development tools such as Oracle Forms. If the host program does not have a PL/SQL engine, the blocks of code are sent to the Oracle server for processing. :

PL/SQL Architecture PL/SQL is a technology and an integrated part of Oracle. It has its own processing engine that executes PL/SQL blocks and subprograms. This engine can be installed in the server or in any of the development tools such as Oracle Forms. If the host program does not have a PL/SQL engine, the blocks of code are sent to the Oracle server for processing. PL/SQL block in the application program PL/SQL Engine SQL statement Executor Oracle Server Figure:- PL/SQL Engine and Oracle Server

PL/SQL and SQL*Plus:- PL/SQL does not support interactive input and output. Therefore, you need a tool like SQL*Plus to use PL/SQL. In SQL*Plus, you can use PL/SQL in the following ways: - To input, store and run PL/SQL blocks. - To create, load and run PL/SQL blocks. - To call a stored procedure. Every PL/SQL block begins with the reserved word DECLARE or BEGIN. BEGIN is used as the starting point of the block if the block contains no declaration. Beginning a block instructs SQL*Plus to clear the SQL buffer, enter the input mode and ignore semicolons. Ending the block with in a SAVE command. Use the SAVE command with the REPLACE clause to save the buffer after you have edited it. To retrieve and load in the buffer a previously save PL/SQL block, use the GET command. :

PL/SQL and SQL*Plus:- PL/SQL does not support interactive input and output. Therefore, you need a tool like SQL*Plus to use PL/SQL. In SQL*Plus, you can use PL/SQL in the following ways: - To input, store and run PL/SQL blocks. - To create, load and run PL/SQL blocks. - To call a stored procedure. Every PL/SQL block begins with the reserved word DECLARE or BEGIN. BEGIN is used as the starting point of the block if the block contains no declaration. Beginning a block instructs SQL*Plus to clear the SQL buffer, enter the input mode and ignore semicolons. Ending the block with in a SAVE command. Use the SAVE command with the REPLACE clause to save the buffer after you have edited it. To retrieve and load in the buffer a previously save PL/SQL block, use the GET command.

SQL > GET add_emp To execute a PL/SQL block that is stored in the buffer in SQL*Plus, use the / or the RUN command. For example, the following statements illustrate both ways to execute the buffer: SQL > RUN or / PL/SQL Fundamentals Let us now begin with PL/SQL fundamentals, the basic building blocks of PL/SQL. PL/SQL is a block structured language where everything you write exists in a block. Thus, a block is a fundamental unit of PL/SQL programming. A block refers to a specific part of a PL/SQL code that can contain both program code and variable declarations. :

SQL > GET add_emp To execute a PL/SQL block that is stored in the buffer in SQL*Plus, use the / or the RUN command. For example, the following statements illustrate both ways to execute the buffer: SQL > RUN or / PL/SQL Fundamentals Let us now begin with PL/SQL fundamentals, the basic building blocks of PL/SQL. PL/SQL is a block structured language where everything you write exists in a block. Thus, a block is a fundamental unit of PL/SQL programming. A block refers to a specific part of a PL/SQL code that can contain both program code and variable declarations.

Broadly the (block) structure of a PL/SQL program is as shown below: DECLARE … Declare here all the variables BEGIN … Write here all the program statements EXCEPTION … Write here all the error-handling statements END; We shall talk about blocks in details in a later section but before that let us learn about everything you can write in a block. Let us begin with characters that you can use while writing PL/SQL code. :

Broadly the (block) structure of a PL/SQL program is as shown below: DECLARE … Declare here all the variables BEGIN … Write here all the program statements EXCEPTION … Write here all the error-handling statements END; We shall talk about blocks in details in a later section but before that let us learn about everything you can write in a block. Let us begin with characters that you can use while writing PL/SQL code.

PL/SQL Character Set:- Character set is a set of valid characters that a language can rcecognise. A character represents any letter, digit, or any other sign. PL/SQL programs can consist of the following characters : Letters : A-Z, a-z Digits : 0-9 Special Symbols: ~ ! @ # % & * ( ) [] {} ; : ‘ “ <>. ? /- Identifiers: - An identifier refers to the name for a PL/SQL object. A PL/SQL object may be one of the following : - Variables - Procedures - Constants - Functions - Cursors - Records - Packages - Exceptions - PL/SQL Tables:

PL/SQL Character Set :- Character set is a set of valid characters that a language can rcecognise. A character represents any letter, digit, or any other sign. PL/SQL programs can consist of the following characters : Letters : A-Z, a-z Digits : 0-9 Special Symbols: ~ ! @ # % & * ( ) [] {} ; : ‘ “ <>. ? /- Identifiers: - An identifier refers to the name for a PL/SQL object. A PL/SQL object may be one of the following : - Variables - Procedures - Constants - Functions - Cursors - Records - Packages - Exceptions - PL/SQL Tables

While naming an object i.e., while specifying an identifier following rules must be followed : - The identifier name must begin with a letter. - It can contain digits. - It may contain these characters : $, _ (underscore), and #. But spaces and other characters are not allowed. - The maximum length of an identifier can be of 30 characters. - Do not use reserved word as an object name. - PL/SQL is case insensitive i.e., case of letters is insignificant when declaring identifiers. Reserved words are some predefined words that are used for some pre-decided specific purposes. Some of the reserved words are : BEGIN, END, IF, ELSE, END IF, WHILE, LOOP, FOR, OPEN, CLOSE, FETCH, DECLARE, EXCEPTION, EXIT etc.:

While naming an object i.e., while specifying an identifier following rules must be followed : - The identifier name must begin with a letter. - It can contain digits. - It may contain these characters : $, _ (underscore), and #. But spaces and other characters are not allowed. - The maximum length of an identifier can be of 30 characters. - Do not use reserved word as an object name. - PL/SQL is case insensitive i.e., case of letters is insignificant when declaring identifiers. Reserved words are some predefined words that are used for some pre-decided specific purposes. Some of the reserved words are : BEGIN, END, IF, ELSE, END IF, WHILE, LOOP, FOR, OPEN, CLOSE, FETCH, DECLARE, EXCEPTION, EXIT etc.

It is not the definitive list. You need to check Oracle PL/SQL documentation for the complete list. Some valid identifier names are given below : Myfile, Date7_7_77, Z2TOZ9, My$Money$, SUPP#, NAME$ The following are some invalid identifiers : min_%_marks, DATA REC, 29clct, Exit, 1st_year, new_identifier_for_newely_created_table Delimiters: - Delimiters are symbols with special meaning such as := (assignment operator), || (concatenation operator) etc. We can say that a delimiter is a symbol that separates one unit of code/data/information from another. PL/SQL supports many delimiters such as +, -, *, /, ** (exponentiation), = (equality), := (assignment), -- (single line comment), /* */ (multi-line comment), .. (range), << >> (label), || (concatenation operator), ; (statement delimiter) and so on.:

It is not the definitive list. You need to check Oracle PL/SQL documentation for the complete list. Some valid identifier names are given below : Myfile, Date7_7_77, Z2TOZ9, My$Money$, SUPP#, NAME$ The following are some invalid identifiers : min_%_marks, DATA REC, 29clct, Exit, 1st_year, new_identifier_for_newely_created_table Delimiters: - Delimiters are symbols with special meaning such as := (assignment operator), || (concatenation operator) etc. We can say that a delimiter is a symbol that separates one unit of code/data/information from another. PL/SQL supports many delimiters such as +, -, *, /, ** (exponentiation), = (equality), := (assignment), -- (single line comment), /* */ (multi-line comment), .. (range), << >> (label), || (concatenation operator), ; (statement delimiter) and so on.

The statement Delimiter: - Every PL/SQL program consists of many statements. The PL/SQL statements are terminated with the semicolon (;) character. An end of line is not the end of a statement, e.g., look at following IF-THEN statement : IF sales > 0 then commission := sales * 0.1; End If ; OR IF sales >0 Then commission := sales * 0.1 ; End IF; Functionality of above given both statements are identical, however, the first one is the preferred one as it enhance readability. :

The statement Delimiter : - Every PL/SQL program consists of many statements. The PL/SQL statements are terminated with the semicolon (;) character. An end of line is not the end of a statement, e.g., look at following IF-THEN statement : IF sales > 0 then commission := sales * 0.1; End If ; OR IF sales >0 Then commission := sales * 0.1 ; End IF; Functionality of above given both statements are identical, however, the first one is the preferred one as it enhance readability.

Datatypes: - Datatypes are means to identify the type of data and associated operations for handling it. A datatype specifies a storage format, constraints and a valid range for a value. PL/SQL provides a variety of predefined datatypes. These are: - Scalar types - Reference types - Composite types - LOB types i) Scalar Datatypes. A scalar datatype is the one that does not have any internal components. A scalar datatype is atomic i.e., it is not made up of other datatypes. It consists of atomic datatypes such as CHAR, VARCHAR2, DATE, BOOLEAN, NUMBER, LONG, BINARY-INTEGER etc. - Boolean logical datatype for TRUE, FALSE or NULL value - LONG character data of variable length upto 2 GB - BINARY_INTEGER signed integers b/w –.. to 2147483647) - PLS_INTEGER signed integers b/w -2147483647 through 214743647.:

Datatypes: - Datatypes are means to identify the type of data and associated operations for handling it. A datatype specifies a storage format, constraints and a valid range for a value. PL/SQL provides a variety of predefined datatypes. These are: - Scalar types - Reference types - Composite types - LOB types i) Scalar Datatypes. A scalar datatype is the one that does not have any internal components . A scalar datatype is atomic i.e., it is not made up of other datatypes. It consists of atomic datatypes such as CHAR, VARCHAR2, DATE, BOOLEAN, NUMBER, LONG, BINARY-INTEGER etc. - Boolean logical datatype for TRUE, FALSE or NULL value - LONG character data of variable length upto 2 GB - BINARY_INTEGER signed integers b/w –.. to 2147483647) - PLS_INTEGER signed integers b/w -2147483647 through 214743647.

Note: PLS_INTEGER values require less storage than NUMBER values, and operations on PLS_INTEGER’s use machine arithmetic, making them more efficient. ii) Composite DataTypes. A composite datatype has some internal components that can be manipulated individually. That is, a composite datatype is made up of other datatypes. Examples of composite datatypes are RCORD, TABLE and VARRAY. Of these RECORD datatype is being covered under Variable Declaration subtopic, coming later in this chapter. Datatypes TABLE and VARRAY are described in :

Note: PLS_INTEGER values require less storage than NUMBER values, and operations on PLS_INTEGER’s use machine arithmetic, making them more efficient. ii) Composite DataTypes . A composite datatype has some internal components that can be manipulated individually. That is, a composite datatype is made up of other datatypes. Examples of composite datatypes are RCORD, TABLE and VARRAY. Of these RECORD datatype is being covered under Variable Declaration subtopic, coming later in this chapter. Datatypes TABLE and VARRAY are described in

iii) Reference Datatypes: - A reference datatype holds values, called pointers that designate other program items. The reference :

iii) Reference Datatypes : - A reference datatype holds values, called pointers that designate other program items. The reference