“Function” calling inside stored procedure

I have a big stored procedure that contains a lot of INSERTs. There are 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 p开发者_运维知识库arameter(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.