如何在Oracle中重载存储过程 Overloading Procedures in PL/SQL http://it.toolbox.com/blogs/oracle-guide/overloading-procedures-in-plsql-1439

An Expert's Guide to Oracle Technology by LewisC (Senior Datawarehouse and BI Consultant) Lewis Cunningham is an Oracle ACE, Database Architect and self-professed database geek. Lewis has almost 20 years of database ... more Lewis Cunningham is an Oracle ACE, Database Architect and self-professed database geek. Lewis has almost 20 years of database experience. Follow along as he builds a working Oracle encyclopedia, sharing his knowledge and experiences, and describing ways to integrate that technology in various projects and business areas. ..less Blog Main  /  Archive  /  Invite Peers

<script type="text/javascript"></script>

Previous Entry / Next Entry

Overloading Procedures in PL/SQL

LewisC  (Senior Datawarehouse and BI Consultant) posted 2/9/2007 | Comments (4)

A FAQ that I get, well, frequently, is about overloading procedures. I think most people get how to overload but not when or why. Today I am going to explain a classic example of where overloading makes your life easier. More importantly, it makes the life of the code maintainer behind you easier.

I am going to use the sample data on the HR schema for this entry. Specifically, the EMPLOYEES table.

In real life, I have had to dump table to text files so many times I can't count them. I have also had to format data for reports and interfaces and such. The common thread with these activities is that I need to convert the data to a string to make it useful wherever it is going.

To keep this simple, I am just going to dump three fields from the EMPLOYEES table: first_name, hire_date and salary. That will cover three basic data types: varhcar2, date and number. I will also limit myself to 3 records.

SELECT first_name, hire_date, salary
  FROM hr.employees
  WHERE rownum <= 3;

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SELECT first_name, hire_date, salary 2 FROM hr.employees 3 WHERE rownum <= 3; FIRST_NAME HIRE_DATE SALARY -------------------- --------- ---------- Donald 21-JUN-99 2600 Douglas 13-JAN-00 2600 Jennifer 17-SEP-87 4400 SQL>

To stringify this query, I would need to convert all of the values to char:

SELECT to_char(first_name), to_char(hire_date), to_char(salary)
  FROM hr.employees
  WHERE rownum <= 3;

Oops, that doesn't work. I can't to_char() a char field. So, I get rid of the to_char on the first_name column.

It would also probably be useful to convert the date field to a specific format rather than the nls format.

SELECT first_name, 
                to_char(hire_date, 'DD/MM/YYYY'), 
                to_char(salary)
  FROM hr.employees
  WHERE rownum <= 3; 

That's not so bad. At least not when only three columns are involved. I have to know what all of the data types are.

Let's put this in a procedure that will write the output to a file. To run this procedure, you will first have to create a directory:

CREATE OR REPLACE DIRECTORY file_dir AS 'C:/temp'; 

Here is the first pass at the procedure.

CREATE OR REPLACE PROCEDURE write_employees AS CURSOR c1 IS SELECT_name, to_char( firsthire_date, 'DD/MM/YYYY') hire_date, to_char(salary) salary FROM hr.employees WHERE rownum <= 3; v_fh UTL_FILE.file_type; BEGIN v_fh := UTL_FILE.fopen('FILE_DIR', 'emps.txt', 'w', 32000); FOR ci IN c1 LOOP UTL_FILE.put_line( v_fh, ci.first_name || ci.hire_date || ci.salary ); END LOOP; UTL_FILE.fclose( v_fh ); END;
If I was dealing with a query (instead of a table) that was producing 500 columns for a CSV file, in addition to knowing which column had which data type, I would have to know which columns to surround with quotes. That means I would not be able to embed the to_char in the query, I would need to perform that logic in the code. My procedure would then change to:

CREATE OR REPLACE PROCEDURE write_employees2 AS CURSOR c1 IS SELECT first_name, hire_date hire_date, salary salary FROM hr.employees WHERE rownum <= 3; v_fh UTL_FILE.file_type; v_string VARCHAR2(32000); BEGIN v_fh := UTL_FILE.fopen('FILE_DIR', 'emps2.txt', 'w', 32000); FOR ci IN c1 LOOP v_string := v_string || '"' || ci.first_name || '",'; v_string := v_string || to_char(ci.hire_date, 'DD/MM/YYYY') || ','; v_string := v_string || to_char(ci.salary); UTL_FILE.put_line( v_fh, v_string ); v_string := NULL; END LOOP; UTL_FILE.fclose( v_fh ); END;
That's still not so bad but it is only a single table and three columns. Imagine it as a query involving multiple tables and many columns. If coding that is mind numbing and full of despair, can you imagine the guy who comes along two years later and has to maintain that steaming pile of PL/SQL? Arghhh!!!

I am going to create a package to help do this. I will call the package STRINGER.

CREATE OR REPLACE PACKAGE stringer AS FUNCTION stringify(p_field IN VARCHAR2) RETURN VARCHAR2; FUNCTION stringify(p_field IN NUMBER) RETURN VARCHAR2; FUNCTION stringify(p_field IN DATE) RETURN VARCHAR2; END;
A package definition does not get much easier than that. I will have a function for each data type that I need to convert. I don't really need a VARCHAR2 function, but if I don't have one, I have to know what the data types are coming in and I am right back where I was before.

The package body is equally simple:

CREATE OR REPLACE PACKAGE BODY stringer AS FUNCTION stringify(p_field IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN p_field; END; FUNCTION stringify(p_field IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN to_char(p_field); END; FUNCTION stringify(p_field IN DATE) RETURN VARCHAR2 IS BEGIN RETURN to_char(p_field, 'DD/MM/YYYY'); END; END;
Now the procedure can be recoded as such:

CREATE OR REPLACE PROCEDURE write_employees3 AS CURSOR c1 IS SELECT first_name, hire_date hire_date, salary salary FROM hr.employees WHERE rownum <= 3; v_fh UTL_FILE.file_type; v_string VARCHAR2(32000); BEGIN v_fh := UTL_FILE.fopen('FILE_DIR', 'emps3.txt', 'w', 32000); FOR ci IN c1 LOOP v_string := v_string || '"' || stringer.stringify(ci.first_name) || '",'; v_string := v_string || stringer.stringify(ci.hire_date) || ','; v_string := v_string || stringer.stringify(ci.salary); UTL_FILE.put_line( v_fh, v_string ); v_string := NULL; END LOOP; UTL_FILE.fclose( v_fh ); END;
There is no longer any reason for us to be concerned with data types. It is still pretty ugly, though, with the double quotes. I also don't like the fact that we have a hard-coded date format.

I will change the package to allow setting a global date format for use by the package as well as adding function to comma separate appropriately. I'm going by the rule that strings are quoted but dates and numbers are not.

CREATE OR REPLACE PACKAGE stringer AS PROCEDURE set_format_mask( p_format_mask IN VARCHAR2 ); FUNCTION stringify(p_field IN VARCHAR2) RETURN VARCHAR2; FUNCTION stringify(p_field IN NUMBER) RETURN VARCHAR2; FUNCTION stringify(p_field IN DATE) RETURN VARCHAR2; FUNCTION stringify_csv(p_field IN VARCHAR2) RETURN VARCHAR2; FUNCTION stringify_csv(p_field IN NUMBER) RETURN VARCHAR2; FUNCTION stringify_csv(p_field IN DATE) RETURN VARCHAR2; END;
I added a new procedure, set_format_mask, which will set a date format. You could do the same with a number mask or any other data type.

I also added stringify_csv for each data type. I want to keep my functions that return a regular varchar2 so I extended them by adding the csv functions.

CREATE OR REPLACE PACKAGE BODY stringer AS g_format_mask VARCHAR2(32000) := 'DD/MM/YYYY'; PROCEDURE set_format_mask( p_format_mask IN VARCHAR2 ) IS BEGIN g_format_mask := p_format_mask; END; FUNCTION stringify(p_field IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN p_field; END; FUNCTION stringify(p_field IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN to_char(p_field); END; FUNCTION stringify(p_field IN DATE) RETURN VARCHAR2 IS BEGIN RETURN to_char(p_field, g_format_mask); END; FUNCTION stringify_csv(p_field IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN '"' || stringify(p_field) || '",'; END; FUNCTION stringify_csv(p_field IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN stringify(p_field) || ','; END; FUNCTION stringify_csv(p_field IN DATE) RETURN VARCHAR2 IS BEGIN RETURN stringify(p_field) || ','; END; END;
Notice that the functionality to convert the data types reuses the logic already contained in the original function calls. I only modify what needs to be modified.

The procedure to cal this can be slightly changed to set the format mask and call the csv version:

CREATE OR REPLACE PROCEDURE write_employees4 AS CURSOR c1 IS SELECT first_name, hire_date hire_date, salary salary FROM hr.employees WHERE rownum <= 3; v_fh UTL_FILE.file_type; v_string VARCHAR2(32000); BEGIN v_fh := UTL_FILE.fopen('FILE_DIR', 'emps4.txt', 'w', 32000); stringer.set_format_mask('DD/MM/YYYY'); FOR ci IN c1 LOOP v_string := v_string || stringer.stringify_csv(ci.first_name); v_string := v_string || stringer.stringify_csv(ci.hire_date); v_string := v_string || stringer.stringify_csv(ci.salary); UTL_FILE.put_line( v_fh, v_string ); v_string := NULL; END LOOP; UTL_FILE.fclose( v_fh ); END;
You can also call the string functions directly from your SQL if you are writing an online interface. I prefer to leave SQL uncorrupted and do this kind of formatting via PL/SQL but that is just a personal choice.

I hope this helps explain why and when you might use overloading.

BTW, this code should also compile and run in EnterpriseDB.

Take care,

LewisC