Academic Tutorials



English | French | Portugese | German | Italian
Home Advertise Payments Recommended Websites Interview Questions FAQs
News Source Codes E-Books Downloads Jobs Web Hosting
Chats

PL/SQL Tutorial
PL/SQL Introduction
PL/SQL Software Installation
PL/SQL Commands
PL/SQL Functions
PL/SQL Multiple Tables
PL/SQL Tables and Constraints
PL/SQL Database Objects
PL/SQL Blocks
PL/SQL Control Structures
PL/SQL Cursors
PL/SQL Exception Handling
PL/SQL Collections
PL/SQL Procedures and Functions
PL/SQL Database Triggers
PL/SQL Oracle Packages
PL/SQL Oracle Utilities
PL/SQL Objects

HTML Tutorials
HTML Tutorial
XHTML Tutorial
CSS Tutorial
TCP/IP Tutorial
CSS 1.0
CSS 2.0
HLML
XML Tutorials
XML Tutorial
XSL Tutorial
XSLT Tutorial
DTD Tutorial
Schema Tutorial
XForms Tutorial
XSL-FO Tutorial
XML DOM Tutorial
XLink Tutorial
XQuery Tutorial
XPath Tutorial
XPointer Tutorial
RDF Tutorial
SOAP Tutorial
WSDL Tutorial
RSS Tutorial
WAP Tutorial
Web Services Tutorial
Browser Scripting
JavaScript Tutorial
VBScript Tutorial
DHTML Tutorial
HTML DOM Tutorial
WMLScript Tutorial
E4X Tutorial
Server Scripting
ASP Tutorial
PERL Tutorial
SQL Tutorial
ADO Tutorial
CVS
Python
Apple Script
PL/SQL Tutorial
SQL Server
PHP
.NET (dotnet)
Microsoft.Net
ASP.Net
.Net Mobile
C# : C Sharp
ADO.NET
VB.NET
VC++
Multimedia
SVG Tutorial
Flash Tutorial
Media Tutorial
SMIL Tutorial
Photoshop Tutorial
Gimp Tutorial
Matlab
Gnuplot Programming
GIF Animation Tutorial
Scientific Visualization Tutorial
Graphics
Web Building
Web Browsers
Web Hosting
W3C Tutorial
Web Building
Web Quality
Web Semantic
Web Careers
Weblogic Tutorial
SEO
Web Site Hosting
Domain Name
Java Tutorials
Java Tutorial
JSP Tutorial
Servlets Tutorial
Struts Tutorial
EJB Tutorial
JMS Tutorial
JMX Tutorial
Eclipse
J2ME
JBOSS
Programming Langauges
C Tutorial
C++ Tutorial
Visual Basic Tutorial
Data Structures Using C
Cobol
Assembly Language
Mainframe
Forth Programming
Lisp Programming
Pascal
Delphi
Fortran
OOPs
Data Warehousing
CGI Programming
Emacs Tutorial
Gnome
ILU
Soft Skills
Communication Skills
Time Management
Project Management
Team Work
Leadership Skills
Corporate Communication
Negotiation Skills
Database Tutorials
Oracle
MySQL
Operating System
BSD
Symbian
Unix
Internet
IP-Masquerading
IPC
MIDI
Software Testing
Testing
Firewalls
SAP Module
ERP
ABAP
Business Warehousing
SAP Basis
Material Management
Sales & Distribution
Human Resource
Netweaver
Customer Relationship Management
Production and Planning
Networking Programming
Corba Tutorial
Networking Tutorial
Microsoft Office
Microsoft Word
Microsoft Outlook
Microsoft PowerPoint
Microsoft Publisher
Microsoft Excel
Microsoft Front Page
Microsoft InfoPath
Microsoft Access
Accounting
Financial Accounting
Managerial Accounting
Network Sites


Introduction to Oracle PL/SQL


Previoushome Next






Introduction to Oracle PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary procedural extension to the SQL database language, used in the Oracle database. Some other SQL database management systems offer similar extensions to the SQL language.


A D V E R T I S E M E N T

PL/SQL's syntax strongly resembles that of Ada, and just like Ada compilers of the 1980s the PL/SQL runtime system uses Diana as intermediate representation.

The key strength of PL/SQL is its tight integration with the Oracle database.
PL/SQL is one of three languages embedded in the Oracle Database, the other two being SQL and Java.

Oracle's PL/SQL (Procedural Language/SQL) is a database-oriented programming language that is a powerful extension of SQL with procedural capabilities. It is similar to ADA in respect to its syntax, and data types and is used in Oracle databases. Oracle implemented it primarily because of its flexibility and ability to wrap - convert the code to a binary (*.plb) which can't be done with SQL statements. Initially PL/SQL was used on the front end. Lately it obtained more ground on the back end.

All PL/SQL programs are made up of blocks and PL/SQL adds selective and iterative loops (constructs) to SQL.

PL/SQL originates from an older language, Pascal, and carries a lot of similarities to ADA in language structure as much of it is based on ADA. PL/SQL incorporates the Descriptive Intermediate Attribute Notation for Ada (DIANA). DIANA compiles the source interpreted code into an m-code for faster execution. There are many of Ada's concepts in procedures, packages with a package in the most conforming to a similar component of Ada.

Introduction to Oracle 9i:SQL, PLSQL. and SQL *Plus

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure: DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL statements go here. */ /* This is the only section of the block that is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END; Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used. To execute a PL/SQL program, we must follow the program text itself by * A line with a single dot ("."), and then * A line with run; As with Oracle SQL programs, we can invoke a PL/SQL program either by typing in sqlplus.

 

PL/SQL can be structured into blocks. It can use conditional statements, loops and branches to control program flow. Scope of the variables can be defined to restrict the visibility within the block.

PL/SQL is a database-orientated programming language. It extends Oracle SQL with procedural capabilities.
 
 
PL/SQL programs are organized into functions, procedures and packages and support for object-oriented programming is limited. It's based on the Ada programming language.
 
 
The three types of PL/SQL blocks:
  1. Anonymous procedure

    :It is an unnamed procedure and can't be called. Anonymous procedure is placed where it is to be run, normally attached to a database trigger or application event.
  2. Named procedure

    :It may be called and it may accept inbound parameters, but named procedure won't explicitly return any value.
  3. Named function

    :A named function can be called and it may accept inbound parameters. Named function will always return a value.


All of these block types share most PL/SQL features so during this tutorial the features that apply to all block types will be grouped into single subjects.
DECLARE
Definition of any variables or objects that are used within the declared block.
BEGIN
Statements that make up the block.
EXCEPTION
All exception handlers.
END;
End of block marker.

	DECLARE
	TEMP_COST NUMBER(10,2);
	BEGIN
	SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE 
	ISBN = 21;
	IF TEMP_COST > 0 THEN
	UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) 
	WHERE ISBN = 21;
	ELSE 
	UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;
	END IF; 
	COMMIT;
	Oracle PL-SQL Tutorial 
	EXCEPTIONOracle PL-SQL Tutorial
	Oracle PL-SQL Tutorial 
	WHEN NO_DATA_FOUND THENOracle PL-SQL Tutorial
	Oracle PL-SQL Tutorial 
	INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 
	'ISBN 21 NOT FOUND');Oracle PL-SQL Tutorial
	Oracle PL-SQL Tutorial 
	END;
	
	
As you can see there are several elements in the example that haven't been covered in the SQL tutorial, these elements are the PL/SQL extensions. They include :-

 

Variables and Constants
These objects are used to store and manipulate block level data. They can be CHAR, VARCHAR2, NUMBER, DATE or BOOLEAN data types.
SQL support
All SQL statements are supported within PL/SQL blocks including transaction control statements.
Composite Datatypes
Records allow groups of fields to be defined and manipulated in PL/SQL blocks.
Flow Control
Ifs, Loops, GOTOs and labels provide conditional actions, tests, branching and iterative program control.
Built In functions
Most SQL data functions are supported within PL/SQL blocks.
Cursor handling
Cursors (a memory area holding a result set) can be explicitly defined and manipulated allowing the processing of multiple rows. A group of PL/SQL system attributes provide the ability to test a cursor's internal state.
Exception handling
Blocks have the ability to trap and handle local error conditions (implicit exceptions). You may also self generate explicit exceptions that deal with logic and data errors.
Code storage
Blocks may be stored within an Oracle database as procedures, functions, packages (a group of blocks) and triggers.

Rules of block Structure

  1. Every unit of PL/SQL must constitute a block. As a minimum there must be the delimiting words BEGIN and END around the executable statements.
  2. SELECT statements within PL/SQL blocks are embedded SQL (an ANSI category). As such they must return one row only. SELECT statements that return no rows or more than one row will generate an error. If you want to deal with groups of rows you must place the returned data into a cursor. The INTO clause is mandatory for SELECT statements within PL/SQL blocks (which are not within a cursor definition), you must store the returned values from a SELECT.
  3. If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
  4. If you include an EXCEPTION section the statements within it are only processed if the condition to which they refer occurs. Block execution is terminated after an exception handling routine is executed.
  5. PL/SQL blocks may be nested, nesting can occur wherever an executable statement could be placed (including the EXCEPTION section).
 
Your first example in PL/SQL will be an anonymous block --that is a short program that is ran once, but that is neither named nor stored persistently in the database.
 
 
	SQL> SET SERVEROUTPUT ON
	SQL> BEGIN
	2 dbms_output.put_line('First World');
	3 END;
	4 /
	 
PL/SQL code is compiled by submitting it to SQL*Plus. Unless your program is an anonymous block, your errors will not be reported. Instead, SQL*Plus will display the message ``warning: procedure created with compilation errors''. You will then need to type:
SQL> SHOW ERRORS
to see your errors listed. If yo do not understand the error message and you are using Oracle on UNIX, you may be able to get a more detailed description using the oerr utility, otherwise use Oracle's documentation (see References section).
 
SQL> SELECT sysdate FROM DUAL
2 /
	CREATE OR REPLACE PROCEDURE welcome 
IS
user_name VARCHAR2(8) := user;
BEGIN -- `BEGIN' ex
dbms_output.put_line('First World, '
|| user_name || '!');
END;
/

 
Make sure you understand the changes made in the code: Once you have compiled the procedure, execute it using the EXEC command.
 
SQL> EXEC welcome
 

Generating Output on Screen

 
The built-in packages offer a number of ways to generate output from within your PL/SQL program. While updating a database table is, of course, a form of "output" from PL/SQL, this chapter shows you how to use two packages that explicitly generate output. UTL_FILE reads and writes information in server-side files, and DBMS_OUTPUT displays information to your screen.
Here is a very good chapter from Orially which helps to understand the complete concept and usage of Generating Output on Screen


View comments on this page.

Posted By Vinotha Selva on: Tuesday, April 13, 2010
Thanking
It is a very nice article well explanatory. I got few more ideas also from this page. Thanks




  PL/SQL Tutorial eBooks

No eBooks on PL/SQL could be found as of now.

 
 PL/SQL Tutorial FAQs
More Links » »
 
 PL/SQL Tutorial Interview Questions
More Links » »
 
 PL/SQL Tutorial Articles

No PL/SQL Articles could be found as of now.

 
 PL/SQL Tutorial News

No News on PL/SQL could be found as of now.

 
 PL/SQL Tutorial Jobs

No PL/SQL Articles could be found as of now.


Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
  • blinkbits
  • BlinkList
  • blogmarks
  • co.mments
  • connotea
  • del.icio.us
  • De.lirio.us
  • digg
  • Fark
  • feedmelinks
  • Furl
  • LinkaGoGo
  • Ma.gnolia
  • NewsVine
  • Netvouz
  • RawSugar
  • Reddit
  • scuttle
  • Shadows
  • Simpy
  • Smarking
  • Spurl
  • TailRank
  • Wists
  • YahooMyWeb

Previoushome Next

Keywords: Oracle PL-SQL Tutorial, PL-SQL, pl-sql, PL-SQL Tutorial, PL-SQL tutorial pdf, history of PL-SQL, PL-SQL Administration, learn PL-SQL

HTML Quizzes
HTML Quiz
XHTML Quiz
CSS Quiz
TCP/IP Quiz
CSS 1.0 Quiz
CSS 2.0 Quiz
HLML Quiz
XML Quizzes
XML Quiz
XSL Quiz
XSLT Quiz
DTD Quiz
Schema Quiz
XForms Quiz
XSL-FO Quiz
XML DOM Quiz
XLink Quiz
XQuery Quiz
XPath Quiz
XPointer Quiz
RDF Quiz
SOAP Quiz
WSDL Quiz
RSS Quiz
WAP Quiz
Web Services Quiz
Browser Scripting Quizzes
JavaScript Quiz
VBScript Quiz
DHTML Quiz
HTML DOM Quiz
WMLScript Quiz
E4X Quiz
Server Scripting Quizzes
ASP Quiz
PERL Quiz
SQL Quiz
ADO Quiz
CVS Quiz
Python Quiz
Apple Script Quiz
PL/SQL Quiz
SQL Server Quiz
PHP Quiz
.NET (dotnet) Quizzes
Microsoft.Net Quiz
ASP.Net Quiz
.Net Mobile Quiz
C# : C Sharp Quiz
ADO.NET Quiz
VB.NET Quiz
VC++ Quiz
Multimedia Quizzes
SVG Quiz
Flash Quiz
Media Quiz
SMIL Quiz
Photoshop Quiz
Gimp Quiz
Matlab Quiz
Gnuplot Programming Quiz
GIF Animation Quiz
Scientific Visualization Quiz
Graphics Quiz
Web Building Quizzes
Web Browsers Quiz
Web Hosting Quiz
W3C Quiz
Web Building Quiz
Web Quality Quiz
Web Semantic Quiz
Web Careers Quiz
Weblogic Quiz
SEO Quiz
Web Site Hosting Quiz
Domain Name Quiz
Java Quizzes
Java Quiz
JSP Quiz
Servlets Quiz
Struts Quiz
EJB Quiz
JMS Quiz
JMX Quiz
Eclipse Quiz
J2ME Quiz
JBOSS Quiz
Programming Langauges Quizzes
C Quiz
C++ Quiz
Visual Basic Quiz
Data Structures Using C Quiz
Cobol Quiz
Assembly Language Quiz
Mainframe Quiz
Forth Programming Quiz
Lisp Programming Quiz
Pascal Quiz
Delphi Quiz
Fortran Quiz
OOPs Quiz
Data Warehousing Quiz
CGI Programming Quiz
Emacs Quiz
Gnome Quiz
ILU Quiz
Soft Skills Quizzes
Communication Skills Quiz
Time Management Quiz
Project Management Quiz
Team Work Quiz
Leadership Skills Quiz
Corporate Communication Quiz
Negotiation Skills Quiz
Database Quizzes
Oracle Quiz
MySQL Quiz
Operating System Quizzes
BSD Quiz
Symbian Quiz
Unix Quiz
Internet Quiz
IP-Masquerading Quiz
IPC Quiz
MIDI Quiz
Software Testing Quizzes
Testing Quiz
Firewalls Quiz
SAP Module Quizzes
ERP Quiz
ABAP Quiz
Business Warehousing Quiz
SAP Basis Quiz
Material Management Quiz
Sales & Distribution Quiz
Human Resource Quiz
Netweaver Quiz
Customer Relationship Management Quiz
Production and Planning Quiz
Networking Programming Quizzes
Corba Quiz
Networking Quiz
Microsoft Office Quizzes
Microsoft Word Quiz
Microsoft Outlook Quiz
Microsoft PowerPoint Quiz
Microsoft Publisher Quiz
Microsoft Excel Quiz
Microsoft Front Page Quiz
Microsoft InfoPath Quiz
Microsoft Access Quiz
Accounting Quizzes
Financial Accounting Quiz
Managerial Accounting Quiz
Testimonials | Contact Us | Link to Us | Site Map
Copyright 2008. Academic Tutorials.com. All rights reserved Privacy Policies | About Us
Our Portals : Academic Tutorials | Best eBooksworld | Beyond Stats | City Details | Interview Questions | Discussions World | Excellent Mobiles | Free Bangalore | Give Me The Code | Gog Logo | Indian Free Ads | Jobs Assist | New Interview Questions | One Stop FAQs | One Stop GATE | One Stop GRE | One Stop IAS | One Stop MBA | One Stop SAP | One Stop Testing | Webhosting in India | Dedicated Server in India | Sirf Dosti | Source Codes World | Tasty Food | Tech Archive | Testing Interview Questions | Tests World | The Galz | Top Masala | Vyom | Vyom eBooks | Vyom International | Vyom Links | Vyoms | Vyom World
Copyright 2003-2017 Vyom Technosoft Pvt. Ltd., All Rights Reserved.