Oracle implementation of dynamic SQL Assembly Essentials _oracle

Although Oracle's dynamic SQL statements are really handy to use, the process of assembling them is cumbersome. Especially when the date Type field is involved in the Assembly statement, the Assembly should be added To_char first to the character, in the SQL, and then use To_date to convert to date type and the original field to compare.

For example, there is an SQL statement:

Select ' ========= and t.created>=to_date (' | | To_char (sysdate, ' yyyy-mm-dd ') | | ', ' YYYY-MM-DD ') and t.created< to_date (' | To_char (sysdate+1, ' yyyy-mm-dd ') | | ', ' ' yyyy-mm-dd ') ' from dual; 

It turns the sysdate into a string, and then converts the string to date in the generated SQL.

The results of the assembly are as follows:

========= and (T.created>=to_date (' 2012-11-08 ', ' yyyy-mm-dd ') and t.created< to_date (' 2012-11-09 ', ' yyyy-mm-dd ') )) 

The string 2012-11-08 is generated by using To_char (sysdate, ' yyyy-mm-dd '), and each single quotation mark in the statement is escaped by writing two single quotes.

Although the assembly process is annoying, as long as three points, you should be able to assemble the available SQL statements.

First, set a goal . You should ensure that the SQL that you assemble is what it should be, and then configure that dynamic SQL

Second, when assembling SQL, all use connectors | | The objects that are connected should all be VARCHAR2 types whose objects begin with single quotes and end in single quotes. The numbers will automatically turn, but date requires us to manually use the To_char function to turn.

If you encounter quotes, write two single quotes .

such as ' I am a ' SQL Developer ' | | v_name| | ' ' In '. Telephone is ' | | v_number| | '. '

V_name is a character type, so assembling it is preceded by a single quotation mark.

This conversion is annoying, but starting from 10g has a new feature that can make people less annoying. It is Q ' [xxxxx] '

Examples are as follows:

Select Q ' [I ' m a SQL developer '] ' | | To_char (sysdate, ' yyyy ') | | Q ' [' In-Telephone is] ' | | 1990| | '. ' from dual; 

The results are as follows:

I ' m a SQL developer ' in '. Telephone is 1990. 

I ' m using a single quote in Q ' [] '.
To_char (sysdate, ' yyyy ') is converted to 2012, preceded by a single quotation mark. So add a single quote at the end of Q ' [xxx '].
This makes it so that we don't have to use the "'" as a single quote.

In short, mastering these three points should be able to assemble the available SQL. If you use a binding variable to enter the output, you need to use the into using keyword.

Set serveroutput on; 
declare
incoming date:=sysdate-10;
outgoing int;
Begin
Execute immediate ' select COUNT (*) user_objects where created >: Incoming ' into outgoing using incoming ;
Dbms_output.put_line (' Count is: ' | | | outgoing);
End

The advantage of using use is not to turn the date type to the varchar type, and then turn back to the tedious operation of the date type.

The SQL code is as follows:

declare
incoming date:=sysdate-10;
outgoing int;
Begin
Execute immediate ' insert into T_object (a) Select COUNT (*) from user_objects where created >: Incoming ' into Outgoing using incoming;
Dbms_output.put_line (' Count is: ' | | | outgoing);
End

ORA-01007: Variable not in select list
ORA-06512: On line 6

Tom explains this error: followup November, 2004-7am-time zone:
You are have to use Dbms_sql ' the number of outputs is not known until run time.

The SQL code is as follows:

DECLARE 
v_cursor number;--Define cursor 
v_string varchar2 (2999); 
V_row number; 
Begin 
V_string: = ' INSERT into T_object (a) Select COUNT (*) from user_objects where created >: Incoming ';--action statement, where: n AME is the variable v_cursor:=dbms_sql.open_cursor;--that determines the value when the statement is run- 
open processing cursor 
dbms_sql.parse (v_cursor,v_string,dbms_ sql.native);--interpretation 
of sentences dbms_sql.bind_variable (V_cursor, ': Incoming ', sysdate-30);--Assign a value to a variable 
v_row: = Dbms_ Sql.execute (v_cursor);--Execute Statement 
dbms_sql.close_cursor (v_cursor);--Close cursor 
--dbms_output.put_line (v_row) 
; commit; 
Exception when 
others then 
dbms_sql.close_cursor (v_cursor);--close cursor 
rollback; 
End