Dynamic SQL-searches Oracle for acceleration vyborok the data

The typical problem{task} at job with databases - to choose the information from different tables, to filter her{it} by the certain criteria, then to process and-or give out to the user for viewing and the visual analysis. If parameters of selection of recordings are available and are determined - this problem{task} is solved is trivial, with the help of the usual operator sql ” select … from … where … “ - where a set of conditions, available after where, is always determined. However, there are cases when the set of parameters of selection of the data is defined{determined} only before selection - and is primary, during designing the program, is not known.

For example, it is necessary to choose the clients "lighted" in a database of trading firm for certain term; or done shopping for the sum more than some set.

Or it is necessary to search for the concrete person, using in part known biographical particulars …

The situation becomes complicated even more if for definition, what recordings need to be chosen and what no, are necessary to cause any function realizing complex{difficult} and resursoemkie calculations. Certainly, this function needlessly is better for not including in processing …

All listed problems can be solved with the help dynamic sql.

Dynamic sql allows to build the text of search directly inside a code pl/sql - and then to carry out it{him}. Accordingly, the developer can construct the text of search, switching in him{it} only the necessary conditions involved by a present situation (a case when the text of sql-search can be generated inside the client application, we shall not consider{examine} - always there are situations when it cannot be made for any reasons).

For job with dynamic sql searches the package dbms_sql responds. In general, job with it{him} occurs under the following circuit.

1. The text of search with labels for parameters is under construction. The text of search can be submitted as a line or a collection of lines.

2. Function dbms_sql.open_cursor allocates the identifier of the cursor which will be used for job with search. The identifier refers to internal structure oracle, determining the cursor. This identifier is used by procedures of a package dbms_sql.

3. Analysis of the text of search is carried out. dbms_sql.parse.

4. Values of parameters of search are established. dbms_sql.bind_variable.

5. If the search returns the data are defined{determined} stolbcy and buffer variables in which the returned data will be placed. dbms_sql.define_column.

6. The search is carried out. dbms_sql.execute.

7. If the search returns the data sample of the data of the cursor and their necessary processing is made. dbms_sql.fetch_rows, dbms_sql.column_value.

8. The cursor is closed. dbms_sql.close_cursor.

Below we shall consider an example of use dynamic sql for search of the person on (incomplete) biographical particulars.

In the beginning we shall be defined{determined} with used structures of the data.

create table personparticulars

(id number (9) constraint pk_personparticulars primary key not null,

family varchar2 (32) constraint pp_chk_family not null,

firstname varchar2 (16) constraint pp_chk_firstname not null

)

tablespace x;

Fields of the table personparticulars:

• id - unique number{room} of biographical particulars

• family - a surname

• firstname - a name

• middlename - a patronymic

Process of reception of results we shall break into two parts: construction of the text of sql-search and, actually, his{its} performance. It is possible to issue it as two stored{kept} procedures, it is possible as - let the developer himself solves one. The text of sql-search can be formed both in one line, and as a collection - in case the text appears too long. In our case we shall use a collection - in spite of the fact that the length of the text of search will be small. What for? And it is simple so, for an example.

Let's agree also, that in stored{kept} procedure the following parameters managing search will be passed:

• familyfilter - a pattern for search on a surname

• firstnamefilter - a pattern for search by name

• middlenamefilter - a pattern for search on a patronymic

If as any from parameters value null is transferred{handed} - this parameter by search is ignored.

Results of search we shall return as the table in memory. For simplicity are there will be simply numbers{rooms} of found people (their value id).

create or replace procedure searchperson (familyfilter in varchar2, firstnamefilter in varchar2, middlenamefilter in varchar2, result in out dbms_sql.varchar2s) is

sqltext dbms_sql.varchar2s;/* the Text of search */

whereclause dbms_sql.varchar2s;/* the Part … where … */

i integer;/* the Counter */

c integer;/* the Identifier of the cursor */

b_id number;/* the Buffer variable for results */

begin

whereclause (1): = ' true ';

if familyfilter is not null then

whereclause (whereclause.last+1): = ' and family like:xfamilyfilter ';

end if;

if firstnamefilter is not null then

whereclause (whereclause.last+1): = ' and firstname like:xfirstnamefilter ';

end if;

if middlenamefilter is not null then

whereclause (whereclause.last+1): = ' and middlename like:xmiddlenamefilter ';

end if;

/* At this stage we have a part of search - where in which those conditions which have been set through nonempty parameters of stored{kept} procedure */are mentioned only

/* Now we shall construct the text of search completely */

sqltext (1): = ' select id ';

sqltext (2): = ' from personparticulars';

for i in whereclause.first.. whereclause.last loop

sqltext (sqltext.last+1): =whereclause (i);

end loop;

/* We receive the identifier of the cursor */

c: = dbms_sql.open_cursor;

/* Disassembly of the text of search */

dbms_sql.parse (c, sqltext, sqltext.first, sqltext.last, false, dbms_sql.native);

/* Installation of parameters of search */

if familyfilter is not null then

dbms_sql.bind_variable (c, ':xfamilyfilter ', familyfilter);

end if;

if firstnamefilter is not null then

dbms_sql.bind_variable (c, ':xfirstnamefilter ', firstnamefilter);

end if;

if middlenamefilter is not null then

dbms_sql.bind_variable (c, ':xmiddlenamefilter ', middlenamefilter);

end if;

/* Installation stolbcov in search */

dbms_sql.define_column (c, 1, b_id);

/* Performance of search */

dbms_sql.execute (c);

/* Sample of results of search */

loop

/* We choose the next line */

if dbms_sql.fetch_rows (c)> 0 then

dbms_sql.column_value (c, 1, b_id);

/* During this moment in a variable b_id we have the current value id the next line. That with her to do{make}, already business of the developer */

else

exit;/* If there are no more lines, are thrown out */

end if;

end loop;

/* We close the cursor */

dbms_sql.close_cursor (c);

end;

I hope, the basic ideas are clear?

Further-:)