Writing a function that reads through a specific table and fills another based on the table that got read

Question

What am trying to do is read through a table that is called transit and based on that table, I want to fill ord & ord 2 and if the function went ahead and executed with no problems, it would return true

CREATE OR REPLACE FUNCTION new_func
  RETURN boolean
IS 
BEGIN
   INSERT INTO ORD(ordernum,cnum,snum,rec,ship,typ)
    SELECT ordernum,cnum,snum,rec,ship,typ
    FROM TRANSIT;

INSERT INTO ORD2(ordernum,cnum,snum,rec,ship,typ)
    SELECT ordernum,cnum,snum,rec,ship,typ
    FROM TRANSIT
    return true;
    
END;

seems neither parameters of the function, nor local variables within the function are needed. Btw, end each insert statement with semi-colons. Add exception handling before `END` while removing both of `return true`; — Barbaros Özhan, Feb 26 '21 at 17:27
@BarbarosÖzhan Thanks for the tips, so a function with no parameters at all ??? — person_unknown, Feb 26 '21 at 17:57
@person_unknown well, what parameters does it need? Also, if you `return true` after the insert into ORD, then you never insert into ORD2, because `return` ends the function. — William Robertson, Feb 26 '21 at 19:17
Also, `insert into t2 (a, b, c) select x, y, z from t2` doesn't need any variables. And finally, the standard string type in Oracle is called `varchar2`. There is something called `char` but it is a [weird type best avoided](https://stackoverflow.com/a/42165653/230471). — William Robertson, Feb 26 '21 at 19:20
Actually, this is a poor use of a _function_. A function is meant to gather information and/or calculate a value, then return the result. Period. Think to_char() or cos. What you want is more appropriate for a _procedure_. — EdStevens, Feb 26 '21 at 19:24
Also, since your SELECTs have no WHERE clause, the will be selecting (and inserting) the _every_ row of your TRANSIT table. — EdStevens, Feb 26 '21 at 19:27
Also, it appears you are naming your parms and local variables the same as columns from your tables. You need to think about naming conventions, so that there is no confusion between a parm, a variable, and a column. A common standard is that parm names all begin with 'p_' (or 'i_' and 'o_' to distinguish bet. input and output) and varieables begin with 'v_'. I also like to make all parm, variable, and column names more descriptive by have the base part of the name in _ format. — EdStevens, Feb 26 '21 at 19:30
@WilliamRobertson if I to want the function to return true where should I place it in this case, after the second insert ??? — person_unknown, Feb 26 '21 at 20:05
@person_unknown Yes, because it will only get to that final `return true` if everything up till then works as expected. As Ed says, though, this is not a great use for a function. Think of how the code will read: `if new_func then ...` — William Robertson, Feb 26 '21 at 20:57
I do understand what @EdStevens said however am required to use a function to do the following, so I don't have much of a choice... — person_unknown, Feb 26 '21 at 21:03
Why are you "required" to use a function? Did you ask the person making the "requirement" why it is required to be a function and not a procedure? — EdStevens, Feb 26 '21 at 21:22

score 0 · Answer 1 · answered Feb 28 '21 at 22:18

Oracle provides a convenient statement for this type action: INSERT ALL. This allows inserting rows into several tables with just one statement: (see example fiddle here)

   insert all 
       into ord(ordernum,cnum,snum,rec,ship,typ)
          values(ordernum,cnum,snum,rec,ship,typ)
          
       into ord2(ordernum,cnum,snum,rec,ship,typ)
          values(ordernum,cnum,snum,rec,ship,typ)  
          
       select ordernum,cnum,snum,rec,ship,typ
         from transit;

In the above every row would be copied into both tables. This however is not required. The fiddle referenced above splits the tables between even and odd ordernum, but the split could be any on expression which eventuates true or false.

Initially my reply was just the above. But reading the comments it became apparent that OP may benefit from seeing examples of why this should be a procedure and not a function. (As could whoever required a function.)
As a developer I automatically assume a function can be called directly form SQL. In this case that is not true. This function can only be called form a plsql block. There are (at least) 2 reasons for this:

There is no boolean data type in SQL. Boolean data type is strictly a plsql data type.
A function called by SQL cannot perform DML.

The referenced fiddle above also contains a example of each of these and what happens when called when called via SQL and by plsql.

Writing a function that reads through a specific table and fills another based on the table that got read

1 Answers1