Lazy Insert

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s