Hi,
I have a big store procedure, that contains a lot of INSERTs. There're many INSERTS that almost identical - they're different by some parameter(s) (all INSERTs to the same table)
Is there a way to create a function/method, to which I'll pass the above parameter(s) and the function/method will generate concrete INSERT's?
Thanks
Sure. It is easy to create a private function.
create or replace procedure p1 as
n pls_integer;
function private_f return number is
begin
return n;
end private_f;
begin
n := private_f;
end p1;
The thing to watch out for is that the private procedure and function definitions must be the last declarations in the declare block. That is, we cannot declare any variables between a private function and the outer procedure's begin clause.
I haven't shown you how to implement a private function which inserts a row into a table. That's because this is a bad way of doing things. It is more efficient to use SQL in a set-based fashioned.
You don't say where the parameters come from, so I'll make something up.
worked example
This procedure emulates an ETL process. It takes some data from a staging table and looads it into a PL/SQL collection. It then manipulates the loaded data in some fashion, and then uses a bulk insert statement to put the data into the target table.
SQL> create or replace procedure pop_emps
2 ( p_mgr in emp.mgr%type)
3 as
4 type emp_nt is table of emp%rowtype;
5 new_emps emp_nt;
6 begin
7 -- populate array from staging table
8 select emp_seq.nextval
9 , t.ename
10 , t.job
11 , p_mgr
12 , trunc(sysdate)
13 , t.sal
14 , null
15 , t.deptno
16 bulk collect into new_emps
17 from emp_import t;
18 -- fix some special values
19 for i in new_emps.first..new_emps.last
20 loop
21 if new_emps(i).deptno = 50
22 then
23 new_emps(i).job := 'PLUMBER';
24 new_emps(i).mgr := 8061;
25 end if;
26 end loop;
27 -- insert new rows into EMP table
28 forall j in new_emps.first..new_emps.last
29 insert into emp
30 values new_emps(j);
31 end pop_emps;
32 /
Procedure created.
SQL>
Note that FORALL is a set operation not a loop.
Anyway, to show it works I will load these three rows...
SQL> select * from emp_import
2 /
ENAME SAL DEPTNO JOB
-------------------- ---------- ---------- --------------------
KESTELYN 3500 30 MARKETING
LIRA 3750 30 MARKETING
TRICHLER 3500 50 MARKETING
SQL> exec pop_emps(7839)
PL/SQL procedure successfully completed.
SQL> select * from emp where hiredate = trunc(sysdate)
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8083 KESTELYN MARKETING 7839 08-APR-10 3500 30
8084 LIRA MARKETING 7839 08-APR-10 3750 30
8085 TRICHLER PLUMBER 8061 08-APR-10 3500 50
SQL>
edit 2
If you really want to do the private function thang, then you can pass a %ROWTYPE as a parameter....
create or replace procedure pop_emps is
new_row emp%rowtype;
procedure pop_emp_row
( p_row in emp%rowtype)
is
begin
insert into emp
values p_row;
end pop_emp_row;
begin
-- assign some values to new_row
new_row.empno := emp_seq.nextval;
new_row.ename := 'WHOEVER';
new_row.hiredate := trunc(sysdate);
-- etc, etc
pop_emp_row(new_row);
end pop_emps;
/
you could use something like this
CREATE FUNCTION WEIGHTED_AVERAGE3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i1,i2,i3,i4,avg INT;
INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
RETURN 1;
END|
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT WEIGHTED_AVERAGE3(50,60,60,50,'Thoko')\G
*************************** 1. row ***************************
WEIGHTED_AVERAGE3(50,60,60,50,'Thoko'): 1
1 row in set (0.00 sec)
mysql> SELECT * FROM sfdata\G
but it is better used as a stored procedure, like so
DROP PROCEDURE IF EXISTS `sp_students_INSERT_byPK`
GO
CREATE PROCEDURE sp_students_INSERT_byPK
(
IN p_student_id INT(11) ,
IN p_password VARCHAR(15) ,
IN p_active_flg TINYINT(4) ,
IN p_lastname VARCHAR(30) ,
IN p_firstname VARCHAR(20) ,
IN p_gender_code VARCHAR(1) ,
IN p_is_on_staff TINYINT(4) ,
IN p_birth_dttm DATETIME
)
BEGIN
INSERT INTO students
(
student_id ,
password ,
active_flg ,
lastname ,
firstname ,
gender_code ,
is_on_staff ,
birth_dttm
)
VALUES
(
p_student_id ,
p_password ,
p_active_flg ,
p_lastname ,
p_firstname ,
p_gender_code ,
p_is_on_staff ,
p_birth_dttm
) ;
END
GO
found here
Inside of a package:
PROCEDURE do_insert(parameter_1_i IN table_name.column_name1%TYPE
,parameter_2_i IN table_name.column_name2%TYPE
,parameter_3_i IN table_name.column_name3%TYPE
... all of the table column names here
)
IS
/*
|| Add proper exception handling to this procedure.
*/
BEGIN
INSERT
INTO table_name
( column_name1
, column_name2
, column_name3
)
VALUES( parameter_1_i
, parameter_2_i
, parameter_3_i
... all of the parameters, some will have the value of NULL
)
END do_insert;
This meets your requirements listed in your question. Make sure you commit appropriately. You could change the parameter list to receive a record based on the table and do an insert with the record in the insert instead of all of the individual columns. Personal choice.