Oracle dynamic SQL statement and oraclesql statement

Oracle dynamic SQL statement and oraclesql statement

Dynamic SQL return cursor:

Create or replace procedure partition (p_regioncode in number, p_pscode in number, p_outputcode in number, p_pollutantType in number, Region in varchar2, p_attencode in varchar2, p_checkstatus in number, p_auditstatus in number, p_cursor out curdata) as begin open p_cursor for 'select bs. regioncode, bs. regionname, bs. pscode, bs. psname, bs. outputcode, bs. outputname, bs. ptype, bd. chkname, bd. approvename, bd. pollutantname, case when (bd. maxvalidtill-sysdate)> 0 and (bd. maxvalidtill-sysdate) <= 7) then ''yellow'' when (sysdate-bd. maxvalidtill)> 0 then ''red'' end color, case when bs. ptype = 1 then ''water'' when bs. ptype = 2 then ''q'' end pstype, bd. auddate, to_char (bd. maxvalidtill, ''yyyy-MM-dd HH24: mi'') validtill from (select vb. regioncode, vb. regionname, vb. pscode, vb. psname, vo. outputcode, vo. outputname, vo. ptype from (select oregioncode regioncode, oregionname regionname, pscode, psname from v_baseinfo where 1 = 1' | case when p_regioncode> 0 then' and oregioncode = '| p_regioncode else ''end | ''| case when p_pscode> 0 then 'and pscode =' | p_pscode else ''end |'' | case when p_psclasscode <> '0' then' and psclasscodexc in ('| p_psclasscode | ') 'else' 'end | ''| case when p_attencode <> '0' then' and attentiondegreecode in ('| p_attencode | ') 'else' 'end | ') vb, (select pscode, outputcode, outputname, inout, psstatus, ptstatus, ptype from v_output t where psstatus = 0 and ismonitor = 1 and ptstatus = 0' | case when p_pscode> 0 then 'and pscode =' | p_pscode else ''end |' '| case when p_outputcode> 0 then' and outputcode =' | p_outputcode else ''end | ''| case when p_checkstatus = 1 then' and isgjkh = 1 or isskkh = 1 'when p_checkstatus = 0 then' and isgjkh = 0 or isskkh = 0 'else' 'end | 'and ptype =' | p_pollutantType | ''| case when p_psclasscode <> '0' then' and psclasscodexc in ('| p_psclasscode | ') 'else' 'end | ''| case when p_attencode <> '0' then' and attentiondegreecode in ('| p_attencode | ') 'else' 'end | ') vo where vb. pscode = vo. pscode) bs, (select pscode, outputcode, pollutantname, pollutantcode, auddate, maxvalidtill, approvename, chkname from v_scenecommonite where checkresult = '1' | case when p_pscode> 0 then' and pscode = '| p_pscode else ''end | ''| case when p_outputcode> 0 then' and outputcode = '| p_outputcode else ''end | ''| case when p_auditstatus = 1 then' and (maxvalidtill-sysdate)> 0 and (maxvalidtill-sysdate) <= 7) 'When p_auditstatus = 2 then' and maxvalidtill> = sysdate-90 'when p_auditstatus = 3 then' and sysdate> maxvalidtill 'else' 'end | ') bd Where bs. pscode = bd. pscode and bs. outputcode = bd. outputcode order by bd. maxvalidtill desc, bs. regioncode, bs. pscode, bs. outputcode'; end proc_ValidityDueQuery;

2. dynamically create temporary tables

-- Create temporary table -- determine whether select count (*) into v_num from user_tables where table_name = upper ('zstemptable') exists '); -- if not, create if v_num = 0 then execute immediate 'Create global temporary table zstemptable (id number (1), reviseddata number (20, 6) on commit preserve rows '; end if; -- insert data execute immediate 'insert into zstemptable select 1, revisedStrength from (select RevisedStrength from region where pscode = '| p_pscode |' and outputcode = '| p_outputcode |' and pollutantCode = ''' | p_pollutantcode |'' and monitorTime <to_date (''' | p_recordTime | ''', ''yyyy-MM-dd HH24: mi: s'') and availableStatus = 0 order by MonitorTime desc) where rownum = 1 '; -- query data execute immediate 'select count (*) from zstemptable where id = 1' into v_hisstrength; -- clear table information execute immediate 'truncate table zstemptable ';

Dynamic SQL statements in oracle databases

This is the case:
The normal SQL statement is as follows:
Select count (*) FROM USER_TABLES WHERE TABLE_NAME = 'emp ';
Then V_ SQL: = ''; the outermost layer also contains quotation marks.
When the table name is a variable, we need to add single quotation marks when querying. If the outermost single quotation marks are used, the single quotation marks in the table need to be referenced by single quotation marks and single quotation marks.
Therefore, if you test whether your V_ SQL writing is normal or not, you can use raise_application_error (-20201, V_ SQL); to view it, because the output is normal.

How to execute dynamic SQL statements in oracle stored procedures ??

Sometimes you need to execute dynamic SQL statements in the oracle stored procedure. For example, the table name is dynamic or the field is dynamic,
Or the query command is dynamic. You can use the following method:
Set serveroutput ondeclaren number; SQL _stmt varchar2 (50 );
T varchar2 (20); beginexecute immediate 'alter session set nls_date_format = ''yyyymmdd ''';
T: = 't_' | sysdate;
SQL _stmt: = 'select count (*) from' | t;
Execute immediate SQL _stmt into n;
Dbms_output.put_line ('the number of rows of '| t |' is '| n); end;
If the dynamic SQL statement is very long and complex, the available package is available.
Create or replace package test_pkgISTYPE cur_typ is ref cursor;
PROCEDURE test_proc (v_table VARCHAR2, t_cur OUT cur_typ); END ;/
Create or replace package body test_pkgISPROCEDURE test_proc (v_table VARCHAR2, t_cur OUT cur_typ) ISsqlstr VARCHAR2 (2000); BEGINsqlstr: = 'select * from' | v_table;
OPEN t_cur FOR sqlstr; END ;/
Batch import, export, and delete tables whose names start with certain characters in oracle
Spool c: \ a. SQL