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.
|
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.
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.
SYMBOL | DESCRIPTION |
; | |
% | |
_ | |
: | |
** | |
< > and != | |
|| | |
<< and >> | |
<= and >= | |
:= | |
=> | |
-- | |
/* and*/ |
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_ | -- 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:
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).
'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.
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
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
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.
• 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.
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 |
PROGRAMS OF EXCEPTION HANDLING
PROGRAM 1:
TITLE: Program to show the exception ‘ZERO-DIVIDE’
OBJECTIVE: the objective of this program is to show exception message when any number is divide by zero.
OUTPUT:
Conclusion: This conclude that when a number is divide by zero it give message that number cannot be divide by zero.
PROGRAM 2:
TITLE: Write a program to show exception ‘No_data_found’
OBJECTIVE: When a SELECT...INTO clause does not return any row from a table.
Output:
Conclusion: This program conclude that when no data found then it will give the desired message.
PROGRAM 3:
Title: Write a program to show exception ‘too_many_rows’
OBJECTIVE: When you SELECT or fetch more than one row into a record or variable.
Output:
Conclusion: This program conclude that when too many rows selected then it will print exception message.
PROGRAM 4:
TITLE: Write a program show the dup_val_on_index.
OBJECTIVE: When we enter the wrong data those not in table what that present.
OUTPUT:
CONCLUSION: when we enter wrong data it present “an INTO clause is expected in this select statement ”.
PROGRAM 5:
TITLE: Write a pl/sql code using exception value_error
OBJECTIVE: Using exception value_error
OUTPUT:
CONCLUSION: predefined exception value_error is used
PROGRAM 6:
TITLE: Write A Pl/Sql Code Using Exception Invalid_Number
OBJECTIVE: Using Exception Invalid_Number
OUTPUT:
CONCLUSION: PREDEFINED EXCEPTION INVALID_NUMBER IS USED
PROGRAM 7:
TITLE: Write A Pl/Sql Code Using Exception Case_Not_Found
OBJECTIVE: Using Exception Invalid_Number
OUTPUT:
CONCLUSION : Predefined Exception Case_Not_Found Is Used
No comments:
Post a Comment