How to create a procedure which have insert and delete code inside the procedure body in pl/sql.

See more:

SQL

I have a table (template_colour_t) with two columns. Those two columns are combination of primery key.

> template_id colour_id

> -------------|-------------

> tp0001 | 0001
> tp0001 | 0003
> tp0002 | 0002
> tp0003 | 0001

I want a procedure which will maintain insert and update code in this single procedure.

More info:

Template_id 'tp0001' is connected with multiple colour_id (0001, 0005, 0003)

I want to add and remove some colur_id with this template_id.

Example:

After add and remove the template_id 'tp0001' should have (0001, 0005, 0004, 0009, 0008) this many colour_id.


Inside the procedure we need to check what and all exsist and what are the new one and if any one is not exist then need to delete.

Based on that we need to delete, insert the row.

Please help me to get that code.

Advanced thanks to all.