Oracle PL/SQL - Dynamic SQL
February 2015
 
Sometimes you may need to fetch data from database when table, fields or where conditions are not known at compile time. Let's take for an example a form where user searches customers by arbitrary fields. You can't know what filed will user fill, and what conditions you will need in your sql statement. This article will try to show advantages and shortcomings of different solutions - using static and dynamic sql.

Our example table will have the following structure.
create table customers (
  id number,
  first_name varchar2(100),
  last_name varchar2(100),
  city varchar2(100)
);
/
create index i_customers_first_name on customers (first_name);
/
create index i_customers_last_name on customers (last_name);
/
create index i_customers_city on customers (city);
Let's say a single index exist for every field.

If a user wants to find a customer by last name, your PL/SQL function might look like this.
function search_customers (
  p_last_name varchar2
) return sys_refcursor is
  cur sys_refcursor;
begin
  open cur for
  select *
  from customers
  where last_name = p_last_name;

  return cur;
end;
Database will use the given last_name index, and query will have low cost.

But if a user wants to be able to search customers sometimes by first name, sometimes by last name, and sometimes by both, you PL/SQL function would need to look something like this.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2
) return sys_refcursor is
  cur sys_refcursor;
begin
  if p_first_name is not null and p_last_name is null then
    open cur for
    select *
    from customers
    where first_name = p_first_name;
  elsif p_first_name is null and p_last_name is not null then
    open cur for
    select *
    from customers
    where last_name = p_last_name;
  elsif p_first_name is not null and p_last_name is not null then
    open cur for
    select *
    from customers
    where first_name = p_first_name
      and last_name = p_last_name;
  end if;

  return cur;
end;
As we can see this is not very pretty solution and would be hard to maintain if query was more complicated. Every change to query would have to be done 3 times.

And now imagine search by city was also needed, you would have to write the same query 7 times to cover all combinations. For 4 fields it would be 15 combinations, for 5 fields 31 combinations, and so on. It would be impossible to maintain.

Of course there is another solution. You could write your function like this.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2,
  p_city varchar2
) return sys_refcursor as
  cur sys_refcursor;
begin
  open cur for
  select *
  from customers
  where (p_first_name is null or first_name = p_first_name)
    and (p_last_name is null or last_name = p_last_name)
    and (p_city is null or city = p_city);

  return cur;
end;
User will be able to search customers by every combination of fields, but there is a drawback to this kind of where clause - database will not use indexes, it will always go with full table scan as input values are not known at execution plan calculation time, and database can't know what index to use.

This is where dynamic SQL comes in. Dynamic SQL allows you to build an SQL statement at runtime.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2,
  p_city varchar2
) return sys_refcursor as
  sql_statement varchar2(1000);
  cur sys_refcursor;
begin
  sql_statement := 'select * from customers where 1=1 ';

  if p_first_name is not null then
     sql_statement := sql_statement || 'and first_name = ''' || p_first_name || ''' ';
  end if;

  if p_last_name is not null then
     sql_statement := sql_statement || 'and last_name = ''' || p_last_name || ''' ';
  end if;

  if p_city is not null then
     sql_statement := sql_statement || 'and city = ''' || p_city || ''' ';
  end if;

  open cur for sql_statement;

  return cur;
end;
If, for example, user inputs last name "Smith", sql statement used to open cursor would be built as following.
select * from customers where 1=1 and last_name = 'Smith'
This would work, user could input all combinations and would get expected results, but there are a few drawbacks with this method. Input values are concatenated to the sql statement so with every new search, a different sql statement is built. This means database can't reuse execution plan and need to do hard parse of statements on every run. This of course consumes time and resources. Also this kind of dynamic sql is vunerable to sql injection attack. The solution to this drawbacks are bind variables.

Using bind variables separates input data from sql statement. In the following example value for last_name is passed as bind variable (denoted with colon). This way sql statement always stays the same and the execution plan can be reused.
function search_customers (
  p_last_name varchar2
) return sys_refcursor as
  sql_statement varchar2(1000);
  cur sys_refcursor;
begin
  sql_statement := 'select * from customers where last_name = :last_name';

  open cur for sql_statement using p_last_name;

  return cur;
end;
Restrictions of bind variables is that they are referenced by order as they appear in sql statement and the value always must be passed for each one.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2,
  p_city varchar2
) return sys_refcursor as
  sql_statement varchar2(1000);
  cur sys_refcursor;
begin
  sql_statement := 'select * from customers where 1=1 ';

  if p_first_name is not null then
     sql_statement := sql_statement || ' and first_name = :first_name ';
  end if;

  if p_last_name is not null then
     sql_statement := sql_statement || ' and last_name = :last_name ';
  end if;

  if p_first_name is not null and p_last_name is null then
  open cur for sql_statement using p_first_name;
  elsif p_first_name is null and p_last_name is not null then
  open cur for sql_statement using p_last_name;
  elsif p_first_name is not null and p_last_name is not null then
  open cur for sql_statement using p_first_name, p_last_name;
  end if;

  return cur;
end;
In previous example we can see we again need to cover all input combination, so for more input parametars this wouldn't be a good solution. So we need a solution to have bind varibales in sql statement indenpendently of where clause. We can do that using with clause.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2,
  p_city varchar2
) return sys_refcursor as
  sql_statement varchar2(1000);
  cur sys_refcursor;
begin
  sql_statement := 'with params as (select :first_name first_name, :last_name last_name, :city city from dual) '
    || 'select * from customers where 1=1 ';

  if p_first_name is not null then
     sql_statement := sql_statement || ' and first_name = (select first_name from params) ';
  end if;

  if p_last_name is not null then
     sql_statement := sql_statement || ' and last_name = (select last_name from params) ';
  end if;
  
  if p_city is not null then
     sql_statement := sql_statement || ' and city = (select city from params) ';
  end if;

  open cur for sql_statement using p_first_name, p_last_name, p_city;

  return cur;
end;
The with clause is always part of sql statement and bind variables always exist no mater whetever they are used in where clause or not. This way using clause where openeing the cursot is always the same.

There is another solution to the problem of variable number of bind variables - so called query transformations. This solution is based on boolean algebra and the fact that with or operator id one operand is true, we have no need to check the value of other operand.
function search_customers (
  p_first_name varchar2,
  p_last_name varchar2,
  p_city varchar2
) return sys_refcursor as
  sql_statement varchar2(1000);
  cur sys_refcursor;
begin
  sql_statement := 'select * from customers where 1=1 ';

  sql_statement := sql_statement || ' and (' || case when p_first_name is not null then '' else '1=1 or ' end || 'first_name = :first_name) ';
  sql_statement := sql_statement || ' and (' || case when p_last_name is not null then '' else '1=1 or ' end || 'last_name = :last_name) ';
  sql_statement := sql_statement || ' and (' || case when p_city is not null then '' else '1=1 or ' end || 'city = :city) ';

  open cur for sql_statement using p_first_name, p_last_name, p_city;

  return cur;
end;
With this solution bind variables are always in the same order in sql statement, but depending on the values of input parameters, true statement is added with every condition that isn't needed. For example if user inputs only first name, the following sql statement will be generated.
select *
from customers
where 1=1
  and (first_name = :first_name)
  and (1=1 or last_name = :last_name)
  and (1=1 or city = :city)
If we take a look at the execution plan of the previous sql statement, we can see that last_name and city conditions are not taken into account. These conditions are completely ignored by the sql parser.
OPERATION                             |OBJECT NAME           |OBJECT TYPE  |OPTIONS                  |OPTIMIZER    |COST      |CPU COST  |IO COST   |BYTES     |CARDINALIT|
--------------------------------------+----------------------+-------------+-------------------------+-------------+----------+----------+----------+----------+----------+
SELECT STATEMENT                      |                      |             |                         |ALL_ROWS     |         1|        10|         1|       169|         1|
  TABLE ACCESS                        |CUSTOMERS             |TABLE        |BY INDEX ROWID           |             |         1|        10|         1|       169|         1|
    INDEX                             |I_CUSTOMERS_FIRST_NAME|INDEX        |RANGE SCAN (1 column)    |ANALYZED     |         1|        10|         1|          |         1|
      Access Predicates               |                      |             |                         |             |          |          |          |          |          |
        FIRST_NAME=:FIRST_NAME        |                      |             |                         |             |          |          |          |          |          |
--------------------------------------+----------------------+-------------+-------------------------+-------------+----------+----------+----------+----------+----------+
Happy coding.



Comments


Leave a comment