Use of dynamic SQL in oracle

Use oracle dynamic SQL syntax for dynamic SQL in oracle: Excute immediate dynamic SQL statement using binding parameter list returning into output parameter list; www.2cto.com describes this statement as follows: 1) dynamic SQL refers to DDL and uncertain DML (that is, DML with parameters) 2) the list of bound parameters is the list of input parameters, that is, the type is in, at runtime, it is bound with parameters in dynamic SQL statements (actually placeholders can be understood as formal parameters in functions. 3) the output parameter list is the list of parameters returned after the dynamic SQL statement is executed. 4) Because dynamic SQL statements are determined at runtime, compared with static SQL statements, it will lose some system resources in exchange for its flexibility. TIPS: The returning into mentioned here does not have any effect on all statements. It only applies to insert, update, and delete statements. For select statements. The syntax is as follows: Excute immediate dynamic SQL statement into output parameter list using binding parameter list; examples used in actual projects: [SQL] create or replace function get_number_zws (tableName varchar2, filed varchar2, condition varchar2, orderby varchar2) return number is returnvalue number; -- return Value countrow number; -- total number of record rows rowindex number; -- position of the median strsql1 varchar2 (4000 ); -- statistics SQL strsql2 varchar2 (4000); -- Query SQL/* function: returns the median parameter of a sequence of numeric type: tableName data table filed: the condition query condition string of the field that generates the median. Author: Li Guojun contact information: 82729536 time: 2012-11-26 */begin strsql1: = 'select count (1) from '| tableName | 'where 1 = 1 and' | condition; dbms_output.put_line (strsql1); execute immediate strsql1 into countrow; if mod (countrow, 2) = 0 then begin rowindex: = countrow/2; strsql2: = 'select avg ('| filed |') from (select' | filed | ', rownum sn from '| tableName | 'where 1 = 1 and' | condition | 'ORDER BY' | orderby | ') where sn between' | rowindex | 'and' | rowindex + 1; execute immediate strsql2 into returnvalue; end; else begin rowindex: = floor (countrow/2) + 1; strsql2: = 'select' | filed | 'from (select' | filed | ', rownum sn from '| tableName | 'where 1 = 1 and' | condition | 'ORDER BY' | orderby |') where sn = '| rowindex; execute immediate strsql2 into returnvalue; -- dbms_output.put_line (strsql2); end if; return returnvalue; end;