3 - plsql for beginners

8/14/2019 3 - PLSQL for Beginners

1/30

An

Training Guide

PL/SQLfor

BeginnersSupplement


2/30

Training GuidePL/SQL for Beginners - Supplement

Copyright 1999 2004 ApplTop Solutions Limited 2

[email protected]

Supplement

The supplement covers a couple of topics that are

what you might call Advanced PL/SQL, the word

Advanced is purely relative, so a more appropriate

title may have been, "Some PL/SQL we didnt have

time for".

The PL/SQL for Beginners guide introduced you to

PL/SQL, the topics covered were based on what you

are most likely to come across in your early

developments.

I have picked the topics in this document based on

the fact that you are likely to use them from a

potential pot of hundreds of topics.

The style in which the topics are presented isdifferent to that of the training course. The main

course is in a tutorial style, whereas these topics are

much lighter in detail and by example.


3/30



[email protected]

Topics Covered

The following topics are covered:-

PL/SQL Records & Tables

Host File Access with UTL_FILE


4/30



[email protected]

PL/ SQL Records & Tables


5/30



[email protected]


Records

We have already seen PL/SQL records, remember

the variables you created which used the %ROWTYPE

keyword in their declaration; this is a record, for

example,

r_employee emp_cur%ROWTYPE;

The above record r_employee is a composite

datatype containing all the columns found in the

cursor emp_cur. A record could also be created

containing all the columns on a table:-

r_employee emp%ROWTYPE;

Each element of a record is referenced using dot

notation, for example, to reference the ename

element of the r_employee record you would use:-

r_employee.ename.

You are not restricted to creating records that are

anchored to a cursor or table, you can define your

own records with the TYPE statement.


6/30



[email protected]


Records

To create a record manually that contains employee

information you might use the following:-

TYPE t_emp_record IS RECORD

( enumber NUMBER

, firstname VARCHAR2(20)

, lastname VARCHAR2(20));

At this point, no PL/SQL object exists, just the new

datatype, so, we need to declare a variable of this

type:-

l_employee t_emp_record;

This is no different to declaring a variable of any of

the standard types. You can now use the new

l_employee record with the dot notation to get at

its elements, for example:-

l_employee.enumber := 100;

l_employee.firstname := 'Fred';

l_employee.lastname := 'Bloggs';


7/30



[email protected]


Assigning Records

You can assign one record to another so long as all

the elements in both records are exactly the same,

for example:-

DECLARE

TYPE t_record IS RECORD

( col1 NUMBER, col2 VARCHAR2(10) );

l_record1 t_record;

l_record2 t_record;

BEGIN

l_record1.col1 := 10;

l_record1.col2 := 'Test';

l_record2 := l_record1;

END;

Records can also be used as arguments in

procedures and functions, for example:-

CREATE OR REPLACE PROCEDURE print_dept

(p_dept_rec dept%ROWTYPE)

IS

BEGIN

DBMS_OUTPUT.put_line(p_dept_rec.deptno);



END;

The above procedure could be invoked with:-

DECLARE

l_rec dept%ROWTYPE;

BEGIN

SELECT * INTO l_rec

FROM dept

WHERE deptno = 10;

print_dept(l_rec);END;


8/30



[email protected]


Creating Tables

A PL/SQL table is very similar to an array found in

most third generation languages. Before a table can

be used, you need to declare a new datatype that is

of type table, you do this with the TYPE statement

in a similar way as you create records. The syntax

ofTYPE in this case is:-

TYPE table-name IS TABLE OF type

INDEX BY BINARY_INTEGER;

Where table-name can be any valid identifier and

type is any valid datatype, including any newdatatypes you have created, such as a record. So,

to declare a table to hold the names of employees

you might:-

TYPE t_names IS TABLE OF emp.ename%TYPE


The INDEX BY specifies how the records on the

table will be indexed, in most cases, this should be

BINARY_INTEGER (though this is not required as of

Oracle8)


9/30



[email protected]


Tables

Once your new table type is declared, you must

declare a variable of the new type, for example:-

names_tab t_names;

The above code will create a new variable called

names_tab of type t_names. You can now use the

table names_tab.

You access individual rows on a PL/SQL table by

using a table index (reference/subscript), similar to

an array subscript. The index should appear

immediately after the table name, for example, to

set the elements of record one in the names_tab

table:-

names_tab(1).empno := 10;

names_tab(1).ename := 'Fred';

The subscript can be any valid number that can be

represented by a binary integer.


10/30



[email protected]


Tables

Here is another example of a less complex table,

this time it is a table of a scalar datatype:-

DECLARE

TYPE t_names IS TABLE OF VARCHAR2(10)


names_tab t_names;BEGIN

names_tab(-10) := 'Fred';

names_tab(0) := 'John';

names_tab(250) := 'Richard';

END;

The table in memory will now look as follows:-

Index Value-10 Fred

0 John

250 Richard

Memory has only been allocated for 3 rows, this is

very much unlike arrays. To set the value of the

250'th row in an array, all elements preceding itmust exist. Nor can an array have a negative

subscript. PL/SQL tables grow dynamically in size as

ou create rows ver much like a database table.


11/30



[email protected]


Tables

If you attempt to reference a row that has not

already been created in a PL/SQL table, then an

exception is raised, for example:-

DECLARE

TYPE t_names IS TABLE OF VARCHAR2(10)


names_tab t_names;

BEGIN

names_tab(0) := 'Fred';

names_tab(1) := 'John';

names_tab(2) := 'Richard';

DBMS_OUTPUT.put_line(names_tab(3));

END;

The above code will fail with the following error:-

ORA-01403: no data found

It will failed because in the line which displays a

value (DBMS_OUTPUT), we are referencing a row

which does not yet exist. You have to handle the

error in a similar fashion as you would for a failing

SELECT statement.


12/30



[email protected]


Tables

Several table attributes exist which allow you to

easily work with tables:-

Attribute Type Returned DescriptionCOUNT NUMBER Returns the number of rows

in the table

DELETE N/A Delete a row from a tableEXISTS BOOLEAN Return TRUE if the specified

row exists, FALSE otherwiseFIRST BINARY_INTEGER Returns the index of the first

row in the tableLAST BINARY_INTEGER Returns the index of the last

row in the tableNEXT BINARY_INTEGER Returns the index of the next

row after a specified rowPRIOR BINARY_INTEGER Returns the index of the

previous row after a specifiedrow

Lets take a quick look at each of these attributes.


13/30



[email protected]


Tables

COUNT

This will return the number of rows in the table.

DECLARE

TYPE t_table IS TABLE OF NUMBER


number_tab t_table;

l_rows NUMBER;

BEGIN

number_tab(1) := 10;



number_tab(2500) := 100;

l_rows := number_tab.COUNT;

END;

In the above code, l_rows will be set to 4.


14/30



[email protected]


Tables

DELETE

This is used to remove rows from a table.

DECLARE



number_tab t_table;

BEGIN




number_tab(2500) := 100;

number_tab.DELETE(87);

END;

DELETE can be used in three ways:-

table.DELETE; will remove all rows

table.DELETE(x); remove row i.

table.DELETE(x,y); remove rows i through y.


15/30



[email protected]


Tables

EXISTS

This is used to check whether a specified row exists

or not.

DECLARE



number_tab t_table;

BEGIN




number_tab(2500) := 100;

IF number_tab.EXISTS(10) THENDBMS_OUTPUT.put_line('Row 10 Found');

END IF;

IF NOT number_tab.EXISTS(100) THENDBMS_OUTPUT.put_line('Row 100 not found');

END IF;

END;


16/30


17/30



[email protected]


Tables

NEXTandPRIOR

These allow you to find the index of the next or

previous row in a table based on a specified index.

They allow you to easily move through a table,

processing all rows.

DECLARE



number_tab t_table;

l_index BINARY_INTEGER;

BEGIN



number_tab(87) := 5;number_tab(2500) := 100;

-- Starting point in table, the first row

l_index := number_tab.FIRST;

LOOP

DBMS_OUTPUT.put_line(number_tab(l_index));

-- End loop when at last row

EXIT WHEN l_index = number_tab.LAST;

-- Get index of next row

l_index := number_tab.NEXT(l_index);

END LOOP;

END;

In the above code, l_index is set to the next row

in the table so long as it is not equal to the last row.


18/30



[email protected]


Tables

PL/SQL are generally very much under-used. They

are very powerful constructs and greatly enhance

the functionality of PL/SQL.

Many programs need to have some kind of

temporary storage area, normally used to hold

intermediate data which needs to be processed in

some way, a great deal of developers create this

temporary storage using database tables, while this

offers some advantages, such as the ability to re-

start a failed process from where it last was, PL/SQL

tables offer advantages too; such a vastly increased

performance, PL/SQL tables are much faster to work

with than database tables, as everything is done in

memory. You also have the advantage of not having

to create and maintain a temporary table. The only

real disadvantage is slightly more complex code.

Use PL/SQL tables wherever possible!!.


19/30



[email protected]

Host File AccessUTL_FILE


20/30


21/30



[email protected]

Host File Access

UTL_FILE

UTL_FILE is a server side package for writing to

host files on the server, there is another package,

TEXT_IO, which is client side and it not discussed in

this document.

UTL_FILE has the following functions and

procedures:-

FOPEN Used to open a file

FCLOSE Used to closed a file

FCLOSE_ALL Close all open filesIS_OPEN Check if a file is open

FFLUSH Output from UTL_FILE

buffered, this procedure is usedto ensure the buffer has beenwritten to the file.

PUT Write to file

PUT_LINE Write to file

NEW_LINE Write to file

PUTF Write to file

GET_LINE Read from a file


22/30



[email protected]

Host File Access

UTL_FILE

FOPEN

Use this function to open a file. FOPEN is defined

as:-

FUNCTION fopen( path IN VARCHAR2

, filename IN VARCHAR2

, mode IN VARCHAR2)RETURN FILE_TYPE;

PATH the directory containing the file

FILENAME the actual filename

MODE the Open Mode, this can be one of:-

'r' : Read from file

'w' : Write to file

'a' : Append to file

The return type is the File Handle which is used to

reference the open file is other functions. It's type is

UTL_FILE.file_type.

DECLARE

l_handle UTL_FILE.file_type;

BEGIN

l_handle := UTL_FILE.fopen( '/u01/app', 'my_file.dat', 'r');

. . .

The above code will open the file

/u01/app/my_file.dat for reading.


23/30



[email protected]

Host File Access

UTL_FILE

FCLOSE & FCLOSE_ALL

Use these procedures to close open files. Closing

files free's up any resources used by UTL_FILE.

DECLARE


BEGINl_handle := UTL_FILE.fopen( '/u01/app'

, 'my_file.dat'

, 'r');

. . .

UTL_FILE.fclose(l_handle);

or

UTL_FILE.fclose_all;

END;

Generally, always use FCLOSE(handle) rather

than FCLOSE_ALL. FCLOSE_ALL is useful in

exception handlers, where you may not know which

files are open, it can be used before a program ends

to ensure all resources are freed.

Notice that FCLOSE takes a file handle from a

previous FOPEN call, whereas FCLOSE_ALL takes

no arameters.


24/30



[email protected]

Host File Access

UTL_FILE

IS_OPEN & FFLUSH

The IS_OPEN function is used to determine if a file

is open. It takes as its argument the file handle and

it returns TRUE if the file is open, otherwise FALSE

is returned.

BEGIN

IF UTL_FILE.is_open(l_handle)UTL_FILE.fclose(l_handle);

END IF;END;

The above code will only attempt to close a file it it

is already open.

FFLUSH is used to ensure the output buffer has

been written to the file. It is wise to flush the buffer

before a program ends to ensure all data is written.

Be aware that flushing after every write to a file can

impact on performance.

For example:-

UTL_FILE.fflush(l_handle);


25/30



[email protected]

Host File Access

UTL_FILE

PUT & PUT_LINE

This procedure is used to write data to a file.

UTL_FILE.put(handle,buffer);

Where handle is the handle of an already open file

and buffer the string to be written.

PUT does not append a newline character to the

buffer; you should use PUT_LINE or NEW_LINE for

this. PUT_LINE is almost the same as PUT except

that it automatically add's a newline character to the

end of buffer.

Example

DECLARE


BEGIN

l_handle := UTL_FILE.fopen( '/u01/app'

, 'my_file.dat'

, 'w');

UTL_FILE.put(l_handle,'This line is written');


END;


26/30



[email protected]

Host File Access

UTL_FILE

PUTF

This procedure is used to write data to a file.

UTL_FILE.putf(handle,format,arg1,arg2,arg3,arg4,arg5);

Where handle is the handle of an already open file.

The parameters arg1 to arg5 are optional and are

string that are written to the file in a format

specified by format.

format is any string of text which can contain

special codes used to print the strings in arg1 to

arg5. The %s code is replaced in the string with is

corresponding arg parameter. You can also use the

code \n to add a newline character. It is similar to

C's printf function.

Example

DECLAREl_handle UTL_FILE.file_type;

BEGIN


, 'my_file.dat'

, 'w');

UTL_FILE.putf(l_handle,'arg1=%s\n','Fred');


END;


27/30



[email protected]

Host File Access

UTL_FILE

NEW_LINE

This procedure writes one or more newline

characters to the file.

UTL_FILE.new_line(handle,lines);


and lines is the number of newline characters to be

written.


28/30



[email protected]

Host File Access

UTL_FILE

GET_LINE

This procedure is used to read from a file.

UTL_FILE.get_line(handle,buffer);


and buffer is where the text retreived will be stored.

GET_LINE reads one full line from the file but it

does include the newline character.

Example

DECLAREl_handle UTL_FILE.file_type;

l_buffer VARCHAR2(500);

BEGIN


, 'my_file.dat'

, 'r');

UTL_FILE.get_line(l_handle,l_buffer);


END;

The above example opens a file and reads a single

line from it before closing the file.


29/30



[email protected]

Host File Access

UTL_FILE - Exceptions

There are many things that can go wrong when

working with host file, the directory may not exist,

the disk maybe fulletc. PL/SQL provides a way of

ensuring your program handles these problems

gracefully by providing a number of predefined

UTL_FILE exceptions. These exceptions are

handled like any other exceptions, within the

exception handler in your code. The exceptions are

listed below:-

Exception Raised When Raised ByINVALID_PATH Directory or filename is invalid FOPEN

INVALID_MODE Invalid open mode specified FOPEN

INVALID_FILEHANDLE File handle does not point to anopen file

FCLOSE,GET_LINE,PUT,PUTF,PUT_LINE,NEW_LINE,FFLUSH

INVALID_OPERATION Attempt to read a file open for writeor visa-versa. Could also be causedby some operating system problemsuch as file permissions

GET_LINE,PUT,PUTF,PUT_LINE,NEW_LINE,FFLUSH

READ_ERROR Operating system error during readoperation GET_LINE

WRITE_ERROR Operating system error during writeoperation

FCLOSE,PUT,PUTF,PUT_LINE,NEW

_LINE,FFLUSH,FCLOSE_ALL

INTERNAL_ERROR Unspecified internal error All

NO_DATA_FOUND End of file reached during read GET_LINE

VALUE_ERROR Data returned by GET_LINE is toolarge for buffer

GET_LINE


30/30


[email protected]

Host File Access

UTL_FILE - A Complete Example

Below is a prodedure that can be used to dump out

the contents of the dept table to a file.

CREATE OR REPLACE PROCEDURE dump_dept

IS

CURSOR dept_cur

IS

SELECT deptno, dname

, loc

FROM dept;


l_path VARCHAR2(50) := '/u01/app';

l_filename VARCHAR2(20) := 'dept.dat';

l_record VARCHAR2(200);

BEGIN

-- Open file

l_handle := UTL_FILE.fopen( l_path, l_filename

, 'w');

-- Get all dept rows

FOR r_dept IN dept_cur

LOOP

l_record := TO_CHAR(r_dept.deptno)||'|'||

r_dept.dname||'|'||

TO_CHAR(r_dept.loc);

-- Write row to file

UTL_FILE.put_line(l_handle,l_record);

END LOOP;

UTL_FILE.fflush(l_handle);


EXCEPTION

WHEN UTL_FILE.invalid_path THEN

DBMS_OUTPUT.put_line('Invalid Path');

WHEN UTL_FILE.write_error THEN

DBMS_OUTPUT.put_line('Write Error');

END;

3 - plsql for beginners

Documents