Allan’s Musings

Just another WordPress.com weblog

Posts Tagged ‘Oracle PLSQL’

Oracle Habits

Posted by Allan Peda on May 17, 2009

I am a big believer in following best practices, at least most of the time. In the case of Oracle databases that means creating surrogate keys. Even if they are of little use initially, I have found these unique identifiers are often useful later on. This is easy to do via auto numbering triggers.

Lets say we have a simple two column table, described below:

SQL> create table mytab (id integer primary key, descr varchar2(20));


The creation of an associated sequence and trigger will allow anyone to insert data into this table without bothering with the details of generating unique ID numbers.

SQL> create sequence mytab_seq;
Sequence created.
SQL> create or replace
trigger mytab_trg
before insert or update on mytab
for each row
BEGIN
  select nvl(:new.id,mytab_seq.nextval) into :new.id from dual;
END;
2    3    4    5    6    7    8  /
Trigger created.
SQL> insert into mytab (id, descr) values (1,'one');
1 row created.
SQL> insert into mytab (id, descr) values (null,'two');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytab;
	ID DESCR
---------- --------------------
	 1 one
	 2 two


Notice that the nvl() function always increments the sequence. This is important, as it allows users to manually add ID values without inadvertently triggering subsequent collisions. The only caveat I would add is that I am not sure if this is specified behavior, but I have observed it empirically
.

Posted in Programming, SQL | Tagged: , | Leave a Comment »

Oracle and rownum

Posted by Allan Peda on May 15, 2009

The other day I was looking at the pagination using the rownum pseudo column. Tom Kyte’s Oracle Column here has some useful hints and an excellent explanation of its dynamic nature. When I tried to recreate his query, it occurred to me that the following DQL would be more concise. I’m not sure if it is just as effective performance wise, but it is easier to read:

SQL> select T3.* from (select T2.*, rownum as outer_rownum
   2  from (select T1.report_date from mytab T1 order by T1.report_date )T2 )T3 
   3  where T3.outer_rownum between 3 and 5;


Which reminds me; much as I have a distaste for most things Microsoft®, it seems the Common Table Expressions (CTEs) are an overlooked way to make SQL more readable. It’s almost a secret, but Oracle 10g does supports CTE syntax.  Consider the following way of identifying violations of a business rule disallowing the same business unit being assigned to multiple companies (such as a Toyota Jeep being submitted in the same feed as a Chrysler Jeep):

with distinct_tab1_v as (select distinct
                              compid as COMP_ID,
                              unitid as UNIT_ID
                         from tab1)
select 'unit id: '|| unit_id || '  comp id: '|| comp_id
from distinct_tab1_v
where unit_id in ( select unit_id
                     from distinct_tab1_v
                 group by unit_id
                   having count(*) > 1 ) order by comp_id, unit_id;


The meat and potatoes of this query is the isolation of a unique collection of units and businesses, which are not needed elsewhere. Rather than create a view that sits around forever, a CTE accomplishes the same effect without globally modifying the schema.

It might be easier to look at this as three queries chained together. The first orders the select, the second assigns the row numbering, then the third imposes the limits on the desired rows. I rewrote the query using a table expression so as to make it easier to alias each derived select.

The raw data we are querying is as follows:

 select * from mytab order by commentary;

    ID DESCR        COMMENTARY
---------- -------------------- ----------
    10 ten         azure
     5 five        blue
     4 four        green
     6 six         indigo
     9 nine        ochre
     2 two         orange
     1 one         red
     8 eight       salmon
     7 seven       violet
     3 three       yellow


Selecting a subset from an ordered select is as simple (or as complex) as:

with ordered_sel as (
   select 
      id,
      descr,
      commentary
   from mytab
   order by commentary
)
select numbered_sel.* from (
   select
      ordered_sel.*,
      rownum as rnum
   from ordered_sel
) numbered_sel where numbered_sel.rnum between 2 and 5
/


Which results in:

	ID DESCR		COMMENTARY	 RNUM
---------- -------------------- ---------- ----------
	 5 five 		blue		    2
	 4 four 		green		    3
	 6 six			indigo		    4
	 9 nine 		ochre		    5


Yeah, SQL is weird, but after a while the logic becomes clearer.

Posted in PL/SQL, Programming | Tagged: | Leave a Comment »