Dynamic SQL is a powerful tool. In its proper place it can achieve quite a bit of work in a relatively small amount of code. Used improperly, however, it can cause all sorts of code maintenance headaches.
Consider the following procedure. It uses the HR schema and sample data that comes with an installation of the Oracle database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
CREATE OR REPLACE procedure FOO.SEARCH_EMPLOYEES (I_DEPARTMENT_ID in number, I_MANAGER_ID in number, I_LOCATION_ID in number, I_COUNTRY_ID in varchar2, I_REGION_ID in number, O_CURSOR out SYS_REFCURSOR) is begin open O_CURSOR for 'select E.EMPLOYEE_ID, ' || ' D.DEPARTMENT_NAME, ' || ' M.FIRST_NAME || '' '' || M.LAST_NAME MANAGER_NAME, ' || ' M.EMAIL MANAGER_EMAIL, ' || ' M.PHONE_NUMBER MANAGER_PHONE_NUMBER, ' || ' L.STREET_ADDRESS, ' || ' L.POSTAL_CODE, ' || ' L.CITY, ' || ' L.STATE_PROVINCE, ' || ' C.COUNTRY_NAME, ' || ' R.REGION_NAME, ' || ' E.FIRST_NAME || '' '' || E.LAST_NAME EMPLOYEE_NAME, ' || ' E.EMAIL EMPLOYEE_EMAIL, ' || ' E.PHONE_NUMBER EMPLOYEE_PHONE_NUMBER ' || 'from HR.DEPARTMENTS D ' || ' join HR.EMPLOYEES M ' || ' on M.EMPLOYEE_ID = D.MANAGER_ID ' || ' join HR.LOCATIONS L ' || ' on L.LOCATION_ID = D.LOCATION_ID ' || ' join HR.COUNTRIES C ' || ' on C.COUNTRY_ID = L.COUNTRY_ID ' || ' join HR.REGIONS R ' || ' on R.REGION_ID = C.REGION_ID ' || ' join HR.EMPLOYEES E ' || ' on E.DEPARTMENT_ID = D.DEPARTMENT_ID ' || 'where ' || case when I_DEPARTMENT_ID is not null then 'D.DEPARTMENT_ID = :I_DEPARTMENT_ID' else ':I_DEPARTMENT_ID is null' end || ' and ' || case when I_MANAGER_ID is not null then 'D.MANAGER_ID = :I_MANAGER_ID' else ':I_MANAGER_ID is null' end || ' and ' || case when I_LOCATION_ID is not null then 'L.LOCATION_ID = :I_LOCATION_ID' else ':I_LOCATION_ID is null' end || ' and ' || case when I_COUNTRY_ID is not null then 'C.COUNTRY_ID = :I_COUNTRY_ID' else ':I_COUNTRY_ID is null' end || ' and ' || case when I_REGION_ID is not null then 'R.REGION_ID = :I_REGION_ID' else ':I_REGION_ID is null' end using I_DEPARTMENT_ID, I_MANAGER_ID, I_LOCATION_ID, I_COUNTRY_ID, I_REGION_ID; end SEARCH_EMPLOYEES; |
Since dynamic queries are not parsed until run-time, it behoves us to write as little SQL as possible to avoid bugs in the code. I’ve seen dynamic query strings that ran for hundreds of lines with several joins, unions, aggregations, and parameter values hard-coded into the string (we all know better than to do that unless (a) there’s an extremely compelling reason and (b) we check for SQL injection attacks, right?). The more involved our SQL is, especially if our code conditionally adds clauses to the query, the greater likelihood for a typographical error in SQL that we may or may not come across in our testing.
The query in SEARCH_EMPLOYEES has several joins, aliases some columns, and concatenates some columns with blank spaces, necessitating some escaped single-quotes in the middle of our string (lines 11 and 20). This example query string is not too bad, but we’ll use it to illustrate a coding strategy that can do a lot to simplify our code development and maintenance.
First, we’ll define a view to handle the joins, column transformations, and aliasing. We want as much work as possible performed by the view so that the procedure only selects and filters–nothing more:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
create or replace force view FOO.SEARCH_EMPLOYEES_VIEW (EMPLOYEE_ID, DEPARTMENT_ID, MANAGER_ID, LOCATION_ID, COUNTRY_ID, REGION_ID, DEPARTMENT_NAME, MANAGER_NAME, MANAGER_EMAIL, MANAGER_PHONE_NUMBER, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_NAME, REGION_NAME, EMPLOYEE_NAME, EMPLOYEE_EMAIL, EMPLOYEE_PHONE_NUMBER) as select E.EMPLOYEE_ID, D.DEPARTMENT_ID, D.MANAGER_ID, L.LOCATION_ID, C.COUNTRY_ID, R.REGION_ID, D.DEPARTMENT_NAME, M.FIRST_NAME || ' ' || M.LAST_NAME MANAGER_NAME, M.EMAIL MANAGER_EMAIL, M.PHONE_NUMBER MANAGER_PHONE_NUMBER, L.STREET_ADDRESS, L.POSTAL_CODE, L.CITY, L.STATE_PROVINCE, C.COUNTRY_NAME, R.REGION_NAME, E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE_NAME, E.EMAIL EMPLOYEE_EMAIL, E.PHONE_NUMBER EMPLOYEE_PHONE_NUMBER from HR.DEPARTMENTS D join HR.EMPLOYEES M on M.EMPLOYEE_ID = D.MANAGER_ID join HR.LOCATIONS L on L.LOCATION_ID = D.LOCATION_ID join HR.COUNTRIES C on C.COUNTRY_ID = L.COUNTRY_ID join HR.REGIONS R on R.REGION_ID = C.REGION_ID join HR.EMPLOYEES E on E.DEPARTMENT_ID = D.DEPARTMENT_ID |
Next, we’ll rewrite our stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE OR REPLACE procedure FOO.SEARCH_EMPLOYEES (I_DEPARTMENT_ID in FOO.SEARCH_EMPLOYEES_VIEW.DEPARTMENT_ID%type, I_MANAGER_ID in FOO.SEARCH_EMPLOYEES_VIEW.MANAGER_ID%type, I_LOCATION_ID in FOO.SEARCH_EMPLOYEES_VIEW.LOCATION_ID%type, I_COUNTRY_ID in FOO.SEARCH_EMPLOYEES_VIEW.COUNTRY_ID%type, I_REGION_ID in FOO.SEARCH_EMPLOYEES_VIEW.REGION_ID%type, O_CURSOR out SYS_REFCURSOR) is begin open O_CURSOR for 'select SEV.EMPLOYEE_ID, ' || ' SEV.DEPARTMENT_NAME, ' || ' SEV.MANAGER_NAME, ' || ' SEV.MANAGER_EMAIL, ' || ' SEV.MANAGER_PHONE_NUMBER, ' || ' SEV.STREET_ADDRESS, ' || ' SEV.POSTAL_CODE, ' || ' SEV.CITY, ' || ' SEV.STATE_PROVINCE, ' || ' SEV.COUNTRY_NAME, ' || ' SEV.REGION_NAME, ' || ' SEV.EMPLOYEE_NAME, ' || ' SEV.EMPLOYEE_EMAIL, ' || ' SEV.EMPLOYEE_PHONE_NUMBER ' || 'from FOO.SEARCH_EMPLOYEES_VIEW SEV ' || 'where ' || case when I_DEPARTMENT_ID is not null then 'SEV.DEPARTMENT_ID = :I_DEPARTMENT_ID' else ':I_DEPARTMENT_ID is null' end || ' and ' || case when I_MANAGER_ID is not null then 'SEV.MANAGER_ID = :I_MANAGER_ID' else ':I_MANAGER_ID is null' end || ' and ' || case when I_LOCATION_ID is not null then 'SEV.LOCATION_ID = :I_LOCATION_ID' else ':I_LOCATION_ID is null' end || ' and ' || case when I_COUNTRY_ID is not null then 'SEV.COUNTRY_ID = :I_COUNTRY_ID' else ':I_COUNTRY_ID is null' end || ' and ' || case when I_REGION_ID is not null then 'SEV.REGION_ID = :I_REGION_ID' else ':I_REGION_ID is null' end using I_DEPARTMENT_ID, I_MANAGER_ID, I_LOCATION_ID, I_COUNTRY_ID, I_REGION_ID; end SEARCH_EMPLOYEES; |
Notice how much tidier the query is in this procedure. We’ve reduced the amount of text in the string by moving much of the SQL heavy-lifting into a view, reducing our risk of typoes. We’ve defined the parameters to be typed by columns in the SEARCH_EMPLOYEES_VIEW, which creates a hard dependency between the procedure and the view. The prior version of this procedure had no hard dependencies. One could drop an underlying table with impunity–until the procedure was called. With the new version of the query an alteration of the underlying view would invalidate the procedure, alerting us to the fact that a code object has been impacted by the schema change.
Load comments