plsql - oracle PL/SQL with automatic values -

plsql - oracle PL/SQL with automatic values -

hi folks have question regarding calculating value in column in oracle.

so have table

name process1 process2 weight total_weight item1 0 0 10 item2 1 1 10 item3 1 1 15

so trying here generating value in total_weight based on process1 , process2 in pl/sql. because later on need show sum of total weight in php page. item 2 total weight should 20 , item 3 should 30. should utilize procedure generating value in total weight? want value updated when user alter value in process1 or process2. please help me kinda newbie here.

declaring virtual (computed) columns in oracle table design

i'll agree of has been said far additional elaboration. starting table design looks similar inaccurate utilize cases explained below.

create table "processed_product_weight" ( "product_name" varchar2(40) not null, "process1" number, "process2" number, "weight" number, "total_weight" number generated ((process1 + process2)*weight) virtual, "recorded_date" date, constraint "processed_product_weight_pk" primary key ("product_name", "recorded_date") ) / previous suggestions , assumptions

table bound attribute properties: table build used @bob jarvis known virtual column. works because definition of total_weight exclusively dependent on other values contained within same table.

sql query associated calculation: on other hand, @nishanthi grashia , @oldprogrammer both recommend modifying value within each sql query executed against database.

both cases may work assuming mass per unit of product not alter during lifetime of production cycle.

an illustration assumption not flexible if products consist of units of varying mass per unit volume.

since not mentioned in op, consider possibility: products item1, item2 , item3 have variable weights per unit. they produced in coffee packaging plant. each item can type of coffee bean , source. "processes" bean "treatments" such decaffeination, roasting type or flavor infusion.

the "units" packaging of varying sizes. mean bundle volumes have direct effect on mass (called "weight") per product unit counted.

test cases identifying effect of changing unit sizes

each test case shows how virtual column not satisfy possibility of variations in unit sizes , masses of each product on time.

test case one:

for production observations made 2/14/2015

test case two:

the mass per unit processed on 3/14/2014 increased only, skewing total mass produced since item quantities made multiplied larger value through virtual column definition.

test case three:

data output , results

above test results associated 3 test cases. resulting values not right utilize cases created. demonstrate changing weight value, virtual/calculated column formula , approach gives wrong results.

a give-and-take of alternate solutions

the trigger approach may work maintaining calculated values total_weight. incremental changes (updates) appended current, existing value each component varies.

force dml through single dml operation contained in crud package. problem defining embedded sql statement enforce requirements other processes , developers need familiar isolated php form/page within app in order duplicate own operation.

if there concern overhead or possible locking of main table, consider introducing composite key: product_name + weight. covers problem quantities of same product name multiplied right weight , values calculated remain unchanged if weight multiplier modified.

sometimes, always, never... popular assumptions thrown around in developer's project circles. how happen @ all? depends... if you're coffee bean packaging outfit, i'd it's quite possible.

onward!

oracle plsql