CodERORR - 4 months ago 23

SQL Question

How to create a function that can Insert in table1, table2, Table3.

It has to be called from some other procedure and values will be coming from that procedure.

Answer

The question is not clear. In general you can create a function which accepts as parameters the values you want to insert in the 3 tables and which will have 3 insert statements (one for each table), assuming no dependencies like FKs between the 3 tables.

```
create FUNCTION my_function (p_value_11 NUMBER ,p_value_12 varchar2 ,p_value_21 NUMBER ,p_value_31 NUMBER)
RETURN NUMBER
AS
v_result number := 0;
BEGIN
INSERT into my_table_1(col_11,col_12) values (p_value_11,p_value12);
INSERT into my_table_2(col_21) values (p_value_21);
INSERT into my_table_3(col_31) values (p_value_31);
RETURN v_result;
exception
when others then
v_result := -1;
RETURN v_result;
END;
```

Why does it need to be a function and not a procedure?