Procedure var_refs
The next example illustrates how references to variables (and table columns) declared with the same name are resolved in SQL PL.
1 create procedure var_refs(in yr_born smallint) 2 b1: begin 3 declare yr_born smallint default 1802; 4 b2: begin 5 declare yr_born smallint default 1866; 6 select full_name, yr_born from author where yr_born = b2.yr_born; 7 select full_name, yr_born from author where yr_born = b1.yr_born; 8 select full_name, yr_born from author where author.yr_born = var_refs.yr_born; 9 select full_name, yr_born from author where yr_born = @yr_born; 10 select full_name, yr_born from author b1 where b1.yr_born = var_refs.yr_born; 11 end; 12 end b1; 13 ;
This procedure has 5 different objects named yr_born: 1) line 1, the procedure argument; 2) line 3, the SMALLINT variable declared in the block labeled b1 with a DEFAULT value 1802; 3) line 5, the SMALLINT variable declared in the block labeled b2 with a DEFAULT value 1866; 4) lines 6-10, the yr_born column in the author table; 5) line 9, the @yr_born referring to a global variable of the same name.
In general, ambiguous variable references are resolved by SQL by working from the inner most blocks to the outermost. The tables listed in the FROM clause of a SELECT that contains the reference form the innermost “block”. Hence, the first yr_born reference in all of the SELECT statements as well as the unqualified references to yr_born in the WHERE clauses on lines 6, 7 and 9 resolve to author.yr_born.
All of the other yr_born references on lines 6-10 are qualified in order to identify which particular yr_born variable is to be used. So, b2.yr_born in line 6 refers to the one declared in block b2 at line 5. The b1.yr_born in line 7 refers to the one declared in block b1 at line 3. The procedure argument named yr_born is referenced by qualifying it with the procedure name, var_refs.yr_born as shown in lines 8 and 10. Note, however, that the b1.yr_born reference on line 10 does not refer to the one declared in the b1 block but to the author table column as the author table has a correlation name b1 and the FROM clause tables have priority. The @yr_born is a reference to a global variable named yr_born which must have been declared when the procedure is both compiled and called.
The rdm-sql script which compiles and calls the var_refs procedure is as follows.
rdm-sql: open database bookshop; rdm-sql: declare yr_born smallint; rdm-sql: set @yr_born = 1819; rdm-sql: .r var_refs.sql rdm-sql: call var_refs(1828); full_name yr_born Potter, Beatrix 1866 Wells, H. G. (Herbert George) 1866 full_name yr_born Dumas, Alexandre 1802 Hugo, Victor 1802 full_name yr_born Tolstoy, Leo 1828 Verne, Jules 1828 full_name yr_born Eliot, George 1819 Whitman, Walt 1819 full_name yr_born Tolstoy, Leo 1828 Verne, Jules 1828
Note the DECLARE statement at statement 2 and the SET (assignment) statement at statement 3. The first declares a global variable named yr_born and the second assigns the value 1819 to it. Note that global variable assignments require an '@' prefix.