Oracle Store Procedures Automation

I am currently working on the process of automating the creation of store procedure and functions in oracle DB I am using java to automate the process of creat开发者_运维百科ing store procedures such that when i give a table name the java code will produce procedures for :

• select
• insert
• update
• delete

and place them in text files

for example

select * from emp where emp_id=i_emp_id and emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if all the three inputs are not null it should function like

select * from emp where emp_id=i_emp_id and emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if i pass i_emp_id as null then the query should function like

select * from emp where emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if i pass i_emp_id as null and i_emp_dpt as null then the query should function like

select * from emp where emp_nm=i_emp_nm

similarily for update etc

I had already posted a question on this

Can you please give me some generalised queries for select insert update and delete

my input to the store procedures may vary as null or actual values

The issue of Table APIs is problematic. On the one hand they can prevent SQL statements being embedded throughout the application. On the other hand they can lead to a culture of poor practice because they cocoon the developers from acquiring any real understanding of the database.

Either way this is a huge chunk of work you are biting off. It requires a lot of understanding of Oracle , SQL and PL/SQL to get it right. And an incomplete solution will be useless. Perhaps even worse than useless.

So, don't write this yourself. Leading PL/SQL expert Steven Feuerstein has written a sophisticated utility to generate Table APIs: Quest Code Generation Utility (formerly QNXO). It is free from the Quest site. Find it here. True it is not written in Java, but so what?

I'm not sure what you want is going to prove useful, but if you want to generate insert, update or delete statements, it's good to know of the views all_tab_columns and user_tab_columns.

Example of insert:

select 'insert into ' || :table_name || ' (' qry
from   dual
union all
select column_name || ', '
from   user_tab_columns
where  table_name = :table_name
union all
select ') values ('
from   dual
union all
select ':' || column_name || ', '
from   user_tab_columns
where  table_name = :table_name
union all
select ')'
from   dual

There's lots of ways to solve this.

One solution would be to overload different code depending on the number of args and their types.

Another solution is to use a fixed set of parameters and not filter on the nulls....

select * 
from emp 
where emp_id=NVL(i_emp_id, emp_id) 
and emp_nm=NVL(i_emp_nm, emp_nm) 
and emp_dpt=NVL(i_emp_dpt, emp_dpt);

Or you could create the query as a string within PL/SQL and use 'EXECUTE IMMEDIATE'....

PROCEDURE finder (
      i_emp_id IN emp.emp_id%TYPE,
      i_emp_nm IN emp.emp_nm%TYPE DEFAULT NULL,
      i_emp_dpt IN emp.emp_dpt%TYPE DEFAULT NULL,
      .... 
   )
IS
   l_qry VARCHAR2(1000) := 'SELECT * FROM emp WHERE 1=1';
BEGIN
   IF (i_emp_nm IS NOT NULL ) 
   THEN
      l_qry := l_qry || ' AND emp_nm=' || i_emp_nm;
   IF (i_emp_dpt IS NOT NULL )
   THEN
      l_qry := l_qry || ' AND emp_dpt=' || i_emp_dpt;
   EXECUTE IMMEDIATE l_qry;
....