Oracle Applications: PL SQL

INTRODUCTION TO PL/SQL

WHAT IS PL/SQL

PL/SQL stands for "Procedural Language extensions to SQL." PL/SQL is available primarily as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. As user can see from the following code example, PL/SQL allows you to combine SQL statements with "standard" procedural constructs. This single program can either insert a company into or delete a company from the database. It relies on the IF statement (not a SQL statement) to determine which action to take:

PROCEDURE maintain_company (action_in IN VARCHAR2, id_in IN NUMBER, name_in IN VARCHAR2 := NULL) IS BEGIN IF action_in = 'DELETE' THEN DELETE FROM company WHERE company_id = id_in; ELSIF action_in = 'INSERT' THEN INSERT INTO company (company_id, name) VALUES (id_in, name_in); END IF; END;

PL/SQL is an unusual -- and an unusually powerful -- programming language. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors.

PL SQL, IS THERE ANY ADVANTAGE

1).In case of SQL to send 3 queries we will need three network trips between client and server.

2).In PL-SQL we bundle any number of queries in a block and in single network trip task is done.

LANGUAGE FEATURES

1).Variables and Data types

2).Loops and Control statements

3).Procedures and Functions

4).Packages

5).Triggers

6).Objects

7).Records (Its like structure in C language)

PL/SQL BLOCK STRUCTURE

A pl/sql block have a definite structure which can be divided into sections.the sections of pl/sql are:

1)Declare section

2)Begin Section or executable command section

3)Exception handling

4)End section

1)DECLARE SECTION

It is first section of pl/sql block. In this variable are declare,coursors are declare as well as these can be initialized.once these are declare they can be used in SQL statement for data manipulation. The declaration section starts with declare keyword followed by list of variables are coursors definition.

2)BEGIN SECTION

In this section the variable and coursor declare in declare section of PL/SQL block are manipulated. Retrieval,looping and branching are specified in ths section. The begin section always start with the “begin” keyword.

3)EXCEPTION HANDLING

This section deals with handling of errors that arises during execution of statement. Error can arise due to wrong syntax, logic is wrong or validation rule. When user define or system related errors or exception takes place the control of PL/SQL shifts to the exeception handling section.

Within this section the “where” clause is used to evaluate which exception is to be raised with in the executable command section or PL/SQL block the flow of commands immediately leaves the command section & searches the exception handling section for an exception matching the error that has taken place.

This section always begin with keyword “exception” AND terminate with the end section.

Once an exception is take place user cannot returned to normal flow of command processing with executable command section.

If user need to maintain control within the executable command to test for possible exception before they are encountered by the program or create a nested block within it’s own local exception handling.

4)END SECTION

This marks the end of the keyword end is used to terminate the PL block.

EXECUTION ENVIORMENT IN PL/SQL

PL/SQL is an integrated part of oracle so its block containing logically related statement and subprograms (procedure, function) are compiled and executed by its own PL/SQL engine. This engine can be installed on the oracle server or with any of oracle development tools such as oracle forms, oracle reports etc.if the application doesn’t have a PL/SQL engine then PL/SQL block is sent to the oracle server for processing.PL/SQL block programs i.e. stored procedure and function, are stored in a complied form. They got loaded on calling and pass it to the engine. The engine can executed the procedural part of the PL/SQL block but pass the SQL statement to the SQL statements executor in the oracle server.

APPLICATION PROGRAM

THE PL/SQL CHARACTER SET

Every language -- whether human or computer -- has a syntax, vocabulary, and character set. In order to communicate within that language, you have to learn the rules that govern its usage. Many of us are very wary of learning a new computer language. Change is often scary, but, in general, programming languages are very simple tongues, and PL/SQL is a relatively simple programming language.

A PL/SQL program consists of a sequence of statements, each of which is made up of one or more lines of text. Text is made up of combinations of the characters shown in below table.

Table 2.1: PL/SQL Character Set
Type	Characters
Letters	A-Z, a-z
Digits	0-9
Symbols	~ ! @ # $ % & * ( ) _ - + = \| [ ] { } : ; " ' < > , . ? /
Whitespace	Tab, space, carriage return

PL/SQL is a case-insensitive language. Uppercase letters are treated the same way as lowercase letters except when the characters are surrounded by single quotes (when they are literal strings) or represent the value of a character variable.

Every valid statement in PL/SQL, from declaration to executable statement to keyword, is made up of various combinations of the above characters.

SIMPLE AND COMPOUND SYMBOLS IN PL/SQL
SYMBOL	DESCRIPTION
;	Semicolon: statement terminator
%	Percent sign: attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE). Also used as multibyte wildcard symbol, as in SQL.
_	Single underscore: single-byte wildcard symbol, as in SQL
:	Colon: host variable indicator, such as :block.item in Oracle Forms
**	Double asterisk: exponentiation operator
< > and !=	"Not equals"
\|\|	Double vertical bar: concatenation operator
<< and >>	Label delimiters
<= and >=	Relational operators
:=	Assignment operator
=>	Association operator for positional notation
--	Double dash: single-line comment indicator
/* and*/	Beginning and ending multiline comment block delimiters

Characters are grouped together into lexical units, also called atomics of the language, because they are the smallest individual components.

Characters are grouped together into lexical units, also called atomics of the language, because they are the smallest individual components. A lexical unit in PL/SQL is any of the following:

· IDENTIFIER

· LITERAL

· DELIMITER

· COMMENT

1)IDENTIFIER

An identifier is a name for a PL/SQL object, including any of the following:

· Constant

· Variable

· Exception

· Procedure

· Function

· Package

· Record

· PL/SQL table

· Cursor

· Reserved word

PROPERTIES OF AN IDENTIFIER ARE SUMMARIZED BELOW:

· Up to 30 characters in length

· Must start with a letter

· Can include $ (dollar sign), _ (underscore), and # (pound sign)

· Cannot contain spaces

Remember that PL/SQL is not case-sensitive, so if the only difference between two identifiers is the case of one or more letters, PL/SQL treats those two identifiers as the same. For example, the following identifiers are all considered by PL/SQL to be the same, because the characters in the name are the same; the only difference is their case:

lots_of_$MONEY$ LOTS_of_$MONEY$ Lots_of_$Money$

The following strings are valid identifier names:

lots_of_$MONEY$	FirstName
company_id#	address_line1
primary_acct_responsibility	address_line2
First_Name	S123456

The following identifiers are all illegal in PL/SQL:

1st_year	-- Starts with numeral
procedure-name	-- Contains invalid character "-"
minimum_%_due	-- Contains invalid character "%"
maximum_value_exploded_for_detail	-- Name is too long
company ID	-- Cannot have embedded spaces in name

1.1)RESERVED WORDS

Many elements of your program have been named by PL/SQL itself. These are the reserved words in the language. An identifier is a reserved word if it has a special meaning in PL/SQL and therefore should not -- and, in most cases, cannot -- be redefined by programmers for their own use.

One very important reserved word is END. It is used to terminate programs, IF statements, and loops. If you try to declare a variable named "end", as I do below, then you will get the subsequent compile error:

DECLARE    end VARCHAR2(10) := 'blip'; BEGIN    DBMS_OUTPUT.PUT_LINE (end); END; / PLS-00103:

Encountered the symbol "END" when expecting one of the following:

The appearance of the word "end" in the declaration section signals to PL/SQL the premature termination of that anonymous block.

PL/SQL tries to be as accommodating as possible when you do redefine reserved words in your program. It makes its best effort to interpret and compile your code. You would be much better off, however, if you never redefined a reserved word for your own use. Even if you do get away with it, the resulting code will be confusing. And a later version of the PL/SQL compiler might decide that your use of that keyword is, after all, unacceptable.

1.2)QUOTED IDENTIFIER

Quoted identifier can be useful when you want to use a PL/SQL reserved word in SQL statements.

Use a reserved word and Enclosed the identifier in double quotes(“ “) is called quoted identifier.

2).LITERALS

A literal is a value which is not represented by an identifier; it is simply a value. A literal may be composed of one of the following types of data:

Number

415, 21.6, or NULL

String

`This is my sentence' or `31-JAN-94' or NULL

Boolean

TRUE, FALSE, or NULL

Notice that there is no way to indicate a true date literal. The value `31-JAN-94' is a string literal (any sequence of characters enclosed by single quotes is a string literal). PL/SQL and SQL automatically convert such a string to a date for you (by calling TO_DATE), but a date has only an internal representation.

A string literal can be composed of zero or more characters from the PL/SQL character set. A literal of zero characters is represented as ''(two consecutive single quotes with no characters between them) and is defined as the NULL string. This literal has a datatype of CHAR (fixed-length string).

PL/SQL is case-sensitive within string literals. The following two literals are different:

'Steven' 'steven'

The following condition, for example, evaluates to FALSE:

IF 'Steven' = 'steven'

3)COMMENTS

Inline documentation, otherwise known as comments, is an important element of a good program

Comments are basically of two types:

1).SINGLE LINE COMMENTS

2).MULTI-LINE COMMENTS.

3.1) SINGLE-LINE COMMENTS

The single-line comment is initiated with two hyphens ( -- ), which cannot be separated by a space or any other characters. All text after the double hyphen, to the end of that physical line, is considered commentary and is ignored by the compiler. If the double hyphen appears at the beginning of the line, then that whole line is a comment.

Remember: the double hyphen comments out the remainder of a physical line, not a logical PL/SQL statement. In the following IF statement, I use a single-line comment to clarify the logic of the Boolean expression:

IF salary < min_salary (1994) -- Function returns min salary for year. THEN    salary := salary + salary

3.2)MULTI-LINE COMMENTS

While single-line comments are useful for documenting brief bits of code and also ignoring a line that you do not want executed at the moment, the multiline comment is superior for including longer blocks of commentary.

Multiline comments start with a slash-asterisk ( /* ) and end with an asterisk-slash ( */ ). PL/SQL considers all characters found between these two sequences of symbols to be part of the comment, and they are ignored by the compiler.

The following example of multiline comments shows a header section for a procedure

ROCEDURE calc_revenue (company_id IN NUMBER) IS /* || Program: calc_revenue || Author: Steven Feuerstein || Change history: ||    9/23/94 - Start program9/23/94 - Start program */ BEGIN    ... END

We can also use multiline comments to block out lines of code for testing purposes. In the following example, the additional clauses in the EXIT statement are ignored so that testing can concentrate on the a_delimiter function:

4).DELIMITER

A PL/SQL program is made up of a series of statements. A statement is terminated with a semicolon ( ; ), not with the physical end of a line. In fact, a single statement is often spread over several lines to make it more readable. The following IF statement takes up four lines and is indented to reinforce the logic behind the statement:

IF salary < min_salary (1994) THEN    salary := salary + salary*.25; END IF;

There are two semicolons in this IF statement. The first semicolon indicates the end of the single executable statement within the IF-END IF construct. The second semicolon terminates the IF statement itself. This same statement could also be placed on a single physical line (if it would fit):IF salary < min_salary (1994) THEN salary := salary + salary*.25; END IF;

PROGRAMS

PROGRAM 1:

TITLE:

Write a PL/SQL block to convert temperature from fahrenheit to celsius.

OBJECTIVE:

To convert temperature from fahrenheit to celsius where the value of temperature in fahrenheit is entered by the user.

CONCLUSION:

This program convert the temperature is taking place from fahrenheit to celsius.

PROGRAM 2:

TITLE:

Write a PL/SQL block to swap 2 nos. without using the third variable.

OBJECTIVE:

To check the swapping between 2 variables without the use of third variable where the values of the two numbers are entered by the user.

CONCLUSION:

Swapping between two variables without the use of third variable.

PROGRAM 3:

TITLE:

Write a PL/SQL code to generate a message using if-then statement.

OBJECTIVE:

To display the use of if-then statement.

CONCLUSION:

If the condition gets true then the greatest number will be displayed.

PROGRAM 3:

TITLE: write a Pl/SQL code to check whether a number is greater than 5 or not using if-then-else statement.

OBJECTIVE: To study the working of if-then-else statement.

CONCLUSION: In the above program the value entered by user is 1, which is less than 5.so, it display a message “wrong value”.

PROGRAM 4:

TITLE: Write a PL/SQL code to check whether a number is greater than or not using if-then-else statement.the number should be entered by user.

OBJECTIVE: To show the working of if-then-else statement and ‘&’ symbol to enter the value from user.

CONCLUSION:

In above program the value of a and b entered by user is 1 and 0 respectively. So it display a message “a is greater”.

PROGRAM 5:

TITLE: write a PL/SQL code to find the greatest of three numbers using if-then-else statement.

OBJECTIVE: To show the working of elsif statement.

CONCLUSION:

In the above program the value of a,b and c are 10,15,20 respectively. So it display the message “ c is greater ”.

PROGRAM 6:

TITLE: Write a PL/SQL code to check a whether a number is prime or not. The number should be entered by user.

OBJECTIVE: To show the working of while loop.

CONCLUSION:

In the above program the value entered by the user is 11 which is prime so it display the message “ a is prime”.

PROGRAM 7:

TITLE: Write a PL/SQL code to find whether a year is leap or not.

OBJECTIVE: To show the working of mod function.

CONCLUSION: In the above program the year entered by the user so it display the message “Whether the number is prime or not”.

PROGRAM 8:

TITLE:

Write a PL/SQL code to find whether a year is leap or not.

OBJECTIVE:

To display the use of mod function where mod function will be used to find whether a year is leap or not

OUTPUT:

CONCLUSION:

A leap year is the one in which there are 366 days and it comes after every 4 years.So,if the year gets divided by 4 completely i.e. it leaves 0 as remainder that means it is a leap year else not

PROGRAM 9:

TITLE:

Write a PL/SQL code to find whether a no. is even or odd using if-then-else statement.

OBJECTIVE:

To display the use of if-then-else and mod function.

OUTPUT:

CONCLUSION:

If the no. entered by the user leaves remainder 0 after being divided by 2 it is even else it is odd.

PROGRAM 10:

TITLE: Write a PL/SQL code to print 100..1000 number using simple loop.

OBJECTIVE:To show the working of exit statement and simple loop also known as “infinite loop”.

CONCLUSION: In above illustration user initialise the value of a is 99 & then increment it up to 1000, so it show the number up 100-1000 .

PROGRAM 11:

TITLE: Write a PL/SQL code to find the area of circle using simple loop.

OBJECTIVE: To calculate area until a condition aries.

CONCLUSION: In The above program user enter the value of radius is 1,2,3,4 and corresponding area is evaluated.when area is greater then 100, it exit from the loop.

PROGRAM 12:

TITLE: write a Pl/SQL code to print 1..n using simple loop.

OBJECTIVE: To study the working of simple loop and exit statement to generate the finite result.

CONCLUSION: In the above program the value entered by user is 10 so control will exit out of loop and display “the value of a is1-10”.

PROGRAM 13:

TITLE: Write a PL/SQL code to print 100..1000 using while loop.

OBJECTIVE: To show the working of while loop by initialising the value of a to 100 as per given condition.

CONCLUSION:In above program the value of a is initialised to 100 and using while loop it display 1000 values.

PROGRAM 14:

TITLE: write a PL/SQL code to calculate the area of circle for a value of radius varying from 3 to 7 using while loop statement.

OBJECTIVE: To evaluate the area of circle using while loop.

CONCLUSION: In the above program the value of radius is initialised to 3 as per given condition and then it display the areas of circle with different values of radius up to 7.

PROGRAM 15:

TITLE: Write a PL/SQL code to find the number between 100..1000 using for loop.

OBJECTIVE: To display the value of I from 100 to 1000 by using for loop.

CONCLUSION: In the above program the value of I prints from 100-1000 as per in the given program.

PROGRAM 16:

TITLE: Write a PL/SQL code to find sum of first 10 nos using for loop.

OBJECTIVE: To show the working of for loop

CONCLUSION: In the above program the output is 55 by adding the number as 1+2+3+4+5+6+7+8+9+10.

PROGRAM 17:

TITLE:

Write a PL/SQL code to print table of any no. using while loop.

OBJECTIVE:

To display the use of while loop.

CONCLUSION:

There are various types of loops such as simple loop, for loop and while loop. All these types of loops differ in syntax but their output comes out to be same

PROGRAM 18:

TITLE:

Write a PL/SQL code to find the area of circle using while loop and insert into table ‘circle’.

OBJECTIVE:

To display the use of while loop, creation of a table and insertion of the result in the table.

CONCLUSION:

Using while loop here we first found out the area of circle and then inserted the value of area in the table circle which we created before.

PROGRAM 19:

TITLE:

Write a PL/SQL code to find whether a no. is even or odd using while loop.

OBJECTIVE:

To display the use of while loop with if-then-else and mod function.

CONCLUSION:

Using while loop here we check for a few nos. whether they are even or odd using mod function.

PROGRAM 20:

TITLE:

Write a PL/SQL code to print 10…1 using for loop.

OBJECTIVE:

To display the use of for loop to print a list of nos. in reverse order

CONCLUSION:

Loops can also be used to execute statements in reverse order.

PROGRAM 21:

TITLE:

Write a PL/SQL code to find sum of first 10 nos using for loop.

OBJECTIVE: To show the working of for loop.

OUTPUT:

CONCLUSION: In the above program the output is 55 by adding the number as 1+2+3+4+5+6+7+8+9+10.

PROGRAM 22.

TITLE:

Wap to print factorial of a number using for loop.

OBJECTIVE:

To find the factorial of a number and number is enter by a user.

OUTPUT:

CONCLUSION: In the above program number 5 which is entered by a user and output is 120.

PROGRAM 23

TITLE:

Write a pl/sql code for inverting a number 5639 to 9365 using for loop.

OBJECTIVE:

For inverting a number 5639 to 9365 using for loop.

OUTPUT:

CONCLUSION: It shows the reverse of digits 5639 to 9365.

PROGRAM 24:

TITLE:

Write a PL/SQL code to print 10…1 using for loop.

OBJECTIVE:

To display the use of for loop to print a list of nos. in reverse order.

OUTPUT:

CONCLUSION:

This conclude numbers are printed in reverse order from 10 to 1.

PROGRAM 25

TITLE:

Write a pl/sql code to display the sum of digits in number.

OBJECTIVE:

Objective of this program to show the sum of digits.

OUTPUT:

CONCLUSION:

Number 456 is enterd by user and desired output is 15.

PROGRAM 26

TITLE:

Write a program to display the inverse of the string entered by the user.

OBJECTIVE:

To display the inverse of the string entered by the user.

OUTPUT:

CONCLUSION: Since values of a=bombay.so according to the requirement of output ,the string got reversed to ‘yabmob’.

PROGRAM 27:

TITLE:

Wap to show whether a string is palindrome or not using for or while loop.

OBJECTIVE:

Objective of this program is to check whether the string is palindrome or not.

OUTPUT:

CONCLUSION: It concludes that string “madam” which is entered by user is palindrome.

PROGRAM:28

TITLE:

Write a pl/sql code to enter a display the following pattern.

***….N.where n is entered by the user.

OBJECTIVE:

To enter a display the following pattern.

***….N.WHERE N IS ENTERED BY THE USER.

OUTPUT:

CONCLUSION: The desired pattern is printed.

EXCEPTION HANDLING IN PL/SQL

INTRODUCTION

Exception is an uneven break in the program flow due to logical issues in the PL/SQL block. It is raised by Oracle server and the execution process is aborted as soon as it is encountered. It can be trapped and handled in the EXCEPTION section of a PL/SQL block. Note that it is an optional section in a block, which plays a vital role in large applications.

http://www.club-oracle.com/articles/attachments/exp1.jpg-388/

STRUCTURE OF EXCEPTION HANDLING.

The General Syntax for coding the exception section

DECLARE

   Declaration section

 BEGIN

   Exception section

 EXCEPTION

 WHEN ex_name1 THEN

    -Error handling statements

 WHEN ex_name2 THEN

    -Error handling statements

 WHEN Others THEN

   -Error handling statements

END;

General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

TYPES OF EXCEPTION.

There are 3 types of Exceptions. a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

A)NAMED EXCEPTION

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

For example: ‘NO_DATA_FOUND’ and ‘ZERO_DIVIDE’ are called Named System exceptions.

B)UNNAMED SYSTEM EXCEPTION

Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1). By using the WHEN OTHERS exception handler, or
2). By associating the exception code to a name and using it as a named exception

C) User-defined Exceptions

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

PRE-DEFINED EXCEPTION

Oracle server defines a bracket of exceptions. If unhandled, they are raised implicitly by the server, which we have already noticed in earlier examples. Else they can be raised and handled explicitly too.
Below table lists some commonly used exceptions. They are predefined in oracle STANDARD package.

Error	Named Exception
ORA-00001	DUP_VAL_ON_INDEX
ORA-00051	TIMEOUT_ON_RESOURCE
ORA-01001	INVALID_CURSOR
ORA-01012	NOT_LOGGED_ON
ORA-01017	LOGIN_DENIED
ORA-01403	NO_DATA_FOUND
ORA-01410	SYS_INVALID_ROWID
ORA-01422	TOO_MANY_ROWS
ORA-01476	ZERO_DIVIDE
ORA-01722	INVALID_NUMBER
ORA-06500	STORAGE_ERROR
ORA-06501	PROGRAM_ERROR
ORA-06502	VALUE_ERROR
ORA-06504	ROWTYPE_MISMATCH
ORA-06511	CURSOR_ALREADY_OPEN
ORA-06530	ACCESS_INTO_NULL
ORA-06531	COLLECTION_IS_NULL
ORA-06532	SUBSCRIPT_OUTSIDE_LIMIT
ORA-06533	SUBSCRIPT_BEYOND_COUNT