This pl/sql below creates function sp_generate_procs in the database. You can then call this function to generate insert/update/delete and select procedures.
Example usage:
begin
:result := sp_generate_procs(p_table_name => 'candidate');
end;
Function definition:
create or replace function sp_generate_procs(p_table_name varchar2, p_owner varchar2 := null)
return clob is
result clob;
v_parameters clob; -- column name parameters
v_cols clob; -- plain column names
v_cols_variables clob; -- column name variables
vComment varchar2(1000);
v_update varchar2(1000) := 'UDPATE ';
v_table_name varchar2(40) := upper(p_table_name);
v_curr_user varchar2(100);
v_colname varchar2(100);
v_pkname varchar2(30);
v_pktype varchar2(30);
v_wasPk boolean := false;
begin
/*open c for select *
from all_tab_columns
where table_name = p_table_name
and owner = v_curr_user;*/
select c.column_name
into v_pkname
from user_constraints u, user_cons_columns c
where u.constraint_type = 'P'
and u.table_name = c.table_name
and c.constraint_name = u.constraint_name
and c.table_name = v_table_name;
vComment := chr(13) || '--Procedure auto generated by sp_generate_procs on ' || sysdate ||
chr(13);
if p_owner is null then
select SYS_CONTEXT('USERENV', 'CURRENT_USER') into v_curr_user from DUAL;
else
v_curr_user := p_owner;
end if;
v_update := chr(9) || 'UPDATE ' || v_table_name || ' SET ' || chr(13);
for c in (select *
from all_tab_columns
where table_name = v_table_name
and owner = v_curr_user)
loop
if lower(v_pkname) = lower(c.column_name) then
v_pktype := c.data_type;
v_colname := 'p_' || substr(c.column_name, 1, 28) || ' IN OUT ' || c.data_type;
else
v_colname := 'p_' || substr(c.column_name, 1, 28) || ' ' || c.data_type;
end if;
dbms_output.put_line(c.column_name);
--v_colname := 'p_' || c.column_name || ' ' || c.data_type;
if v_parameters is null then
v_parameters := chr(9) || chr(9) || v_colname;
v_cols := chr(9) || chr(9) || c.column_name;
v_cols_variables := chr(9) || chr(9) || 'p_' || substr(c.column_name, 1, 28);
else
if (v_wasPk) then
v_parameters := v_parameters || ', -- Primary key is IN/OUT';
v_wasPk := false;
else
v_parameters := v_parameters || ',';
end if;
v_parameters := v_parameters || chr(13) || chr(9) || chr(9) || v_colname;
v_cols := v_cols || ',' || chr(13) || chr(9) || chr(9) || c.column_name;
v_cols_variables := v_cols_variables || ',' || chr(13) || chr(9) || chr(9) || 'p_' ||
substr(c.column_name, 1, 28);
end if;
if lower(v_pkname) != lower(c.column_name) then
v_update := v_update || chr(9) || chr(9) || c.column_name || '=' || 'p_' ||
substr(c.column_name, 1, 28) || ',' || chr(13);
else
v_wasPk := true;
end if;
end loop;
v_update := substr(v_update, 1, length(v_update) - 2);
v_update := v_update || chr(13) || chr(9) || 'where ' || v_pkname || '= p_' || v_pkname || ';' ||
chr(13);
result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_SELECT (p_' || v_pkname || ' ' ||
v_pktype || ',curs out sys_refcursor) AS ' || chr(13);
result := result || vComment || 'begin' || chr(13);
result := result || 'open curs for ' || chr(13);
result := result || chr(9) || 'select ' || chr(13) || v_cols || chr(13) || chr(9) || ' from ' ||
v_table_name || ' where ' || v_pkname || '= p_' || v_pkname || ';' || chr(13);
result := result || chr(13);
result := result || 'END;' || chr(13);
result := result || '/' || chr(13);
result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_INSERT (' || chr(13);
result := result || v_parameters || ') AS ' || chr(13);
result := result || vComment || 'begin' || chr(13);
result := result || chr(9) || 'insert into ' || v_table_name || '(' || chr(13);
result := result || v_cols || chr(13) || chr(9) || ') values (' || chr(13) || v_cols_variables || ');' ||
chr(13);
result := result || chr(13);
result := result || 'END;' || chr(13);
result := result || '/' || chr(13);
result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_UPDATE (' || chr(13);
result := result || v_parameters || ') AS ' || chr(13);
result := result || 'begin' || chr(13) || vComment || chr(13);
result := result || v_update;
result := result || 'END;' || chr(13);
result := result || '/' || chr(13);
result := result || 'CREATE OR REPLACE PROCEDURE ' || v_table_name || '_DELETE(p_' || v_pkname || ' ' ||
v_pktype || ') AS ' || chr(13);
result := result || 'begin' || chr(13);
result := result || chr(9) || 'delete from ' || v_table_name;
result := result || chr(9) || ' where ' || v_pkname || '= p_' || v_pkname || ';' || chr(13);
result := result || chr(13);
result := result || 'END;' || chr(13);
result := result || '/' || chr(13);
return(result);
end sp_generate_procs;
/
