PL/SQL - one procedure for business logic

Applications Programming
1

Hello, I need some advice how to to create one big transactional table.

My table has following columns

person_id, trans_id, date, dep_id, material_id, input, outpu, total

I created procedure from which I will enter all transaction into that table. Problem is I don't have any idea how to achive following.

When I enter for example id for paper and input size of 0,5, everything I see is the same counter which is updated for each transaction no matter which material is being updated.

So I need some ideas abou following

  1. How to keep old data in record no matter how many times
    I enter some data ?
  2. How to reduce number of input arguments, now I need to specify all values (output_size and total := 0) so I can calculate changes.
  3. How can I add more rigid checks in if statement for example
    (if total_size > 0 && output_size == 0) ?

I need something like SAP enviroment :smiley:

2

  1. How to keep old data in record no matter how many times I enter some data ?
    I did not understand this question!

  2. How to reduce number of input arguments, now I need to specify all values (output_size and total := 0) so I can calculate changes.
    PLSQL does support default parameter values.

PROCEDURE proc_name (total IN NUMBER DEFAULT 5) IS ...
OR
PROCEDURE proc_name (total IN NUMBER := 5) IS ...

So you can ignore total from actual parameter list when calling this procedure.

  1. How can I add more rigid checks in if statement for example (if total_size > 0 && output_size == 0) ?
    PLSQL does support AND and OR 
IF total_size > 0 AND output_size = 0 THEN
statments
ENF IF;
3

Ok, as I said this how many columns my "big table" has

person_id, trans_id,  date, dep_id, material_id, input, outpu, total

if I enter

100, 100, sysdate, 100, 100, 0, 0 /total is calculated inside the procedure, but for now I need to add this argument/

I will see the same data

but if I enter

100, 101, sysdate, 200, 0, 0

I see

100, 101, sysdate, 101, 200, 0 , 300

Oracle overwrites first entered record and I want all changes to be written in the same table.

4

What does the procedure look like?

tyler_durden