PL/SQL Procedural Language / Structured Query Language (Source CaoSys)

1. PL/SQLProcedural Language / Structured Query Language(Source CaoSys)

2. Overview • PL/SQL Records and Tables • • Host File Access with UTL_FILE

3. 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;

4. Records (Continued) 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 := 101; l_employee.firstname := ‘John'; l_employee.lastname := ‘Smith'; You can assign one record to another so long as all the elements in both records are exactly the same, for example:-

5. Records (Continued) 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;

6. Records (Continued) 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;

7. Records (Continued) The 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. 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;

9. Tables (Continued) 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;

10. Tables (Continued) 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 := ‘John';

11. Tables (Continued) 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; names_tab t_names; BEGIN names_tab(-10) := ‘John'; names_tab(0) := ‘Mary'; names_tab(250) := ‘Andrew'; END;

12. Tables (Continued) 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 you create rows, very much like a database table.

13. Tables (Continued) 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 you create rows, very much like a database table. Points of interest: Can not reference undeclared row, as it will result in a exception. A table object has a number of attributes which we can use to interact with it e.g. Count, last, exists etc.

14. Tables (Continued) 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; l_rows NUMBER; BEGIN number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; number_tab(2500) := 100; l_rows := number_tab.COUNT; END; In the above code, l_rows will be set to 4.

15. Tables (Continued) 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 number_tab(1) := 10; number_tab(10) := 20; number_tab(87) := 5; 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.

16. Tables (Continued) 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;

17. Tables (Continued) 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; l_first BINARY_INTEGER; l_last BINARY_INTEGER; BEGIN 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.

18. Records and Tables Review 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.

19. Host File Access UTL_FILE

20. Host File Access (Cont) 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. UTL_FILE is a server side package for writing to host files on the server, there is another package, TEXT_IO.

21. Host File Access (Cont) 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 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

22. Host File Access (Cont) FOPEN: Use this function to open a file. 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

23. Host File Access (Cont) 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.

24. Host File Access (Cont) 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. 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'); UTL_FILE.fclose(l_handle); END;

25. Host File Access (Cont) 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. 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',’John'); UTL_FILE.fclose(l_handle); END;

26. Host File Access (Example) Below is a procedure 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 UTL_FILE.file_type; l_path VARCHAR2(50) := '/u01/app'; l_filename VARCHAR2(20) := 'dept.dat'; l_record VARCHAR2(200);

27. Host File Access (Example) 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); UTL_FILE.fclose(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;