PLSQL for Beginners - Suppliement

An

Training Guide

PL/SQL for Beginners Supplement

Training Guide PL/SQL for Beginners - Supplement

www.appltop.com [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 didn\u2019t 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 is different 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.

Copyright \u00a9 1999 \u2013 2004 ApplTop Solutions Limited

2



Topics Covered The following topics are covered:\

u

2

0

2

2

PL/SQL Records & Tables

\

u

2

0

2

2

Host File Access with UTL_FILE

Copyright \u00a9 1999 \u2013 2004 ApplTop Solutions Limited

3



PL/ SQL Records & Tables

Copyright © 1999 – 2004 ApplTop Solutions Limited

4



PL/ SQL Records & Tables 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.


5



PL/ SQL Records & Tables 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 a

its elements, for example:l_employee.enumber := 100; l_employee.firstname := 'Fred'; l_employee.lastname := 'Bloggs';


6



PL/ SQL Records & Tables 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) );

BEGIN

END;

l_record1 t_record; l_record2 t_record; l_record1.col1 := 10; l_record1.col2 := 'Test';

l_record2 := l_record1;

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); DBMS_OUTPUT.put_line(p_dept_rec.deptno); 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; END;

print_dept(l_rec);


7



PL/ SQL Records & Tables 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 of TYPE 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 new

datatypes 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 INDEX BY BINARY_INTEGER;

The INDEXBY specifies how the records on the table will be indexed, in most cases, this should be

BINARY_INTEGER (though this is not required as o

Oracle8)


8



PL/ SQL Records & Tables 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 typet_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.


9

Training Guide


PL/SQL for Beginners - Supplement

PL/ SQL Records & Tables 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) INDEX BY BINARY_INTEGER;

BEGIN

names_tab t_names;

names_tab(-10) := 'Fred'; names_tab(0) := 'John'; names_tab(250) := 'Richard'; END;

The table in memory will now look as follows:I ndex

Value

-10 0 250

Fred John 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 it must exist. Nor can an array have a negative subscript. PL/SQL tables grow dynamically in size as y

ou create rowsver much like a database table. ,

y


10



PL/ SQL Records & Tables 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) INDEX BY BINARY_INTEGER;

BEGIN

names_tab t_names;

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.


11



PL/ SQL Records & Tables Tables Several table attributes exist which allow you to easily work with tables:Attribute Type Returned COUNT

NUMBER

DELETE

N/A

EXISTS

BOOLEAN

FIRST

BINARY_INTEGER

LAST

BINARY_INTEGER

NEXT

BINARY_INTEGER

PRIOR

BINARY_INTEGER

Description

Returns the number of rows in the table Delete a row from a table Return TRUE if the specified row exists, FALSE otherwise Returns the index of the first row in the table Returns the index of the last row in the table Returns the index of the next row after a specified row Returns the index of the previous row after a specified row

Lets take a quick look at each of these attributes.


12



PL/ SQL Records & Tables Tables COUNT

This will return the number of rows in the table. DECLARE TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab t_table;

BEGIN

END;

l_rows NUMBER; number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; number_tab(2500) := 100; l_rows := number_tab.COUNT;

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


13



PL/ SQL Records & Tables Tables DELETE

This is used to remove rows from a table. DECLARE TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab t_table; BEGIN

END;

DELETE

number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; number_tab(2500) := 100;

number_tab.DELETE(87);

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.


14



PL/ SQL Records & Tables Tables EXISTS

This is used to check whether a specified row exists or not. DECLARE TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab t_table; BEGIN

number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; number_tab(2500) := 100;

IF number_tab.EXISTS(10) THEN DBMS_OUTPUT.put_line('Row 10 Found'); END IF; IF NOT number_tab.EXISTS(100) THEN DBMS_OUTPUT.put_line('Row 100 not found'); END IF; END;


15



PL/ SQL Records & Tables Tables FIRST and LAST

These are used to find the index of the first and last rows in a table. DECLARE TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab t_table;

BEGIN

l_first BINARY_INTEGER; l_last BINARY_INTEGER; number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; number_tab(2500) := 100; l_first := number_tab.FIRST; l_last := number_tab.LAST;

END;

In the above code, l_first wil be set to 1 and l_last

will be set to 2500.


16

Training Guide



PL/ SQL Records & Tables Tables NEXT andPRIOR

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 TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab t_table;

BEGIN

l_index BINARY_INTEGER; number_tab(1) := 10; number_tab(10) := 20; 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.


17



PL/ SQL Records & Tables 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 restart 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!!.


18



Host File Access UTL_FILE


19



Host File Access UTL_FILE Reading from and writing to host files is a common task for PL/SQL. PL/SQL itself does not directly support this kind of functionality, but it can be done using an Oracle supplied package, UTL_FILE. What follows is a very brief introduction to UTL_FILE, check out any of the books mentioned

at the back of the main course notes for further information.


20



Host File Access UTL_FILE UTL_FILE

is a server side package for writing t

host files on the server, there is another package,

TEXT_IO, which is client side and it not discus

this document. UTL_FILE

has the following functions and

procedures:Used to open a file FCLOSE Used to closed a file FCLOSE_ALL Close all open files IS_OPEN Check if a file is open FFLUSH Output from UTL_FILE buffered, this procedure is used to ensure the buffer has been written 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 FOPEN


21

Training Guide



Host File Access UTL_FILE FOPEN

Use this function to open a file. FOPEN is defined as:FUNCTION fopen( , , RETURN FILE_TYPE;

PATH FILENAME MODE

path filename mode

IN VARCHAR2 IN VARCHAR2 IN VARCHAR2)

the directory containing the file the actual filename 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.


22



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 l_handle UTL_FILE.file_type; BEGIN l_handle := UTL_FILE.fopen( , , . . .


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. p


23

Training Guide



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

END;

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

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);


24



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 l_handle UTL_FILE.file_type; BEGIN l_handle := UTL_FILE.fopen( , ,

'/u01/app' 'my_file.dat' 'w');

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



25

Training Guide



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 DECLARE l_handle UTL_FILE.file_type; BEGIN l_handle := UTL_FILE.fopen( , ,

'/u01/app' 'my_file.dat' 'w');

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



26



Host File Access UTL_FILE NEW_LINE

This procedure writes one or more newline characters to the file. UTL_FILE.new_line(handle,lines);

Where handle is the handle of an already open file and lines is the number of newline characters to be written.


27



Host File Access UTL_FILE GET_LINE

This procedure is used to read from a file. UTL_FILE.get_line(handle,buffer);

Where handle is the handle of an already open file and buffer is where the text retreived will be stored. GET_LINE

reads one full line from the file but

does include the newline character. Example DECLARE l_handle UTL_FILE.file_type; l_buffer VARCHAR2(500); BEGIN l_handle := UTL_FILE.fopen( , ,


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.


28



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 full…etc. 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 By

INVALID_PATH Directory or filename is invalid FOPEN INVALID_MODE Invalid open mode specified FOPEN INVALID_FILEHANDLE File handle does not point to anFCLOSE,GET_LIN E,PUT,PUTF,PUT_ open file LINE,NEW_LINE, FFLUSH GET_LINE,PUT,P INVALID_OPERATION Attempt to read a file open for write or visa-versa. Could also be causedUTF,PUT_LINE,N by some operating system problemEW_LINE,FFLUSH such as file permissions READ_ERROR Operating system error during read GET_LINE operation WRITE_ERROR Operating system error during write FCLOSE,PUT,PUT F,PUT_LINE,NEW operation _LINE,FFLUSH,FC LOSE_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 too GET_LINE large for buffer


29



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_handle l_path l_filename l_record BEGIN

UTL_FILE.file_type; VARCHAR2(50) := '/u01/app'; VARCHAR2(20) := 'dept.dat'; VARCHAR2(200);

-- 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); UTL_FILE.fclose(l_handle); EXCEPTION WHEN UTL_FILE.invalid_path THEN DBMS_OUTPUT.put_line('Invalid Path');

END;

WHEN UTL_FILE.write_error THEN DBMS_OUTPUT.put_line('Write Error');


30

PLSQL for Beginners - Suppliement

Recommend Documents