Do you ever get that annoying colleague quoting Kyte at you … “if you can do it SQL then do it in SQL” ?
One of the more tedious aspects of development is those repetitive insert scripts – usually for adding reference data.
Consider a table like this :-
CREATE TABLE my_ref_data_tab( id NUMBER(3), ref_val VARCHAR2(30), active_ind VARCHAR2(1), created_by VARCHAR2(30) NOT NULL, creation_date DATE NOT NULL, modified_by VARCHAR2(30), modified_date DATE, CONSTRAINT mrdt_pk PRIMARY KEY ( id), CONSTRAINT mrdt_uk UNIQUE( ref_val)) /
The synthetic key is populated from a sequence.
Now, I need to create 5 records in this table ( it could be any number, but I got bored typing insert statements).
Usually, I’d have to do something like :-
spool myinsert.log INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, 'MIKE', 'Y', USER, SYSDATE) / INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, 'IS', 'Y', USER, SYSDATE) / INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, 'A', 'Y', USER, SYSDATE) / INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, 'LAZY', 'Y', USER, SYSDATE) / INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, 'SOD', 'Y', USER, SYSDATE) / spool off
Loads of unnecessary typing, lots of cut-and-paste…and more often than not, lots of debugging when you realise you’ve made a typo, or cut and paste an insert without changing the value you’re inserting. And what happens when someone decides to change the structure of the table ?
All in all, life is far too short. So….
set serveroutput on size 1000000 spool lazy.log DECLARE l_message VARCHAR2(15) := '1 row inserted.'; PROCEDURE ins( p_ref_val VARCHAR2) IS BEGIN INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date) VALUES( mrdt_id.NEXTVAL, p_ref_val, 'Y', USER, SYSDATE); END ins; BEGIN ins( 'MIKE'); DBMS_OUTPUT.PUT_LINE(l_message); ins('IS'); DBMS_OUTPUT.PUT_LINE(l_message); ins('A'); DBMS_OUTPUT.PUT_LINE(l_message); ins('LAZY'); DBMS_OUTPUT.PUT_LINE(l_message); ins('SOD'); DBMS_OUTPUT.PUT_LINE(l_message); END; / spool off
Note there’s no exception handling here – if anything fails, you’ll want to rollback the lot and figure out what’s happening. On the plus side however, you’ve only got one insert statement to puzzle over rather than five.