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

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;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND');

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

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.
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.
If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
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.
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 /

Next, we will rewrite the anonymous block above as a procedure. Note that we now use the user function to greet the user.

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”

Click here to Read

Tags

hemachandrareddy said:

vishakha arawal said:

ravikr said: