Dynamic SQL and oracle dynamic SQL

Dynamic SQL and oracle dynamic SQL
Directory

1. Pass values to dynamic statements (USING clause)

2. Retrieve values from dynamic statements (INTO clause)

3. Dynamic call stored

4. Pass the return value to the PL/SQL record type. The % rowtype variable is also available.

5. Pass and retrieve the value. INTO clause before the USING clause

6. multi-row query option. This option is used to fill the temporary table with the insert statement.

7. dynamically define the cursor

 

-- Dynamic execution of SQL including ddl dml--1.exe cute immediate recommended performance is better --2.dbms_utility.exe c_ddl_statementbegin execute immediate 'select * from scott. emp '; execute immediate 'create table emp1121 (id number, name varchar2 (10), salary number)'; dbms_utility.exec_ddl_statement ('create table emp111 (id number, name varchar2 (10 ), salary number) '); end;

1. Pass values to dynamic statements (USING clause)

-- 1. pass the value (USING clause) declare l_depnam varchar2 (20): = 'testing'; l_loc varchar2 (10): = 'Dubai '; begin execute immediate 'insert into dept values (: 1,: 2,: 3) 'using 50, l_depnam, l_loc; commit; end; select * from dept

 

2. Retrieve values from dynamic statements (INTO clause)

-- 2. retrieve values from dynamic statements (INTO clause) declare l_cnt varchar2 (20); begin execute immediate 'select count (1) from emp 'into l_cnt; dbms_output.put_line (l_cnt); end;

 

3. Dynamic call stored

-- Create or replace procedure get_rowcnt (l_tblnam varchar2, l_cnt out number, l_status out varchar2) asbegin select t. empno into l_cnt from emp t where rownum = 1; l_cnt: = 3; l_status: = 'OK'; end; -- 3. dynamic call stored declare l_routin varchar2 (100): = 'get _ rowcnt '; l_tblnam varchar2 (20): = 'emp'; l_cnt number; l_status varchar2 (200 ); begin execute immediate 'begin' | l_routin | '(: 2,: 3,: 4); end; 'usi Ng in l_tblnam, out l_cnt, in out l_status; if l_status! = 'OK' then dbms_output.put_line ('error'); end if; end;

 

4. Pass the return value to the PL/SQL record type. The % rowtype variable is also available.

-- 4. pass the return value to the PL/SQL record type. The % rowtype variable declare type empdtlrec is record (empno number (4), ename varchar2 (20); empdtl empdtlrec; begin execute immediate 'select empno, ename from emp where rownum = 1' into empdtl; dbms_output.put_line (empdtl. empno); end;

 

5. Pass and retrieve the value. INTO clause before the USING clause

-- 5. pass and retrieve the value. before the USING clause, the INTO clause declare l_dept pls_integer: = 20; l_nam varchar2 (20); l_loc varchar2 (20); begin execute immediate 'select dname, loc from dept where deptno =: 1 'into l_nam, l_loc using l_dept; dbms_output.put_line (l_nam); end;

 

6. multi-row query option. This option is used to fill the temporary table with the insert statement.

-- 6. multi-row query option. this option uses the insert statement to fill the temporary table, which can be further processed by the temporary table. You can also use REF cursors to correct this defect. create table temp (empno number, ename varchar2 (111); declare l_sal pls_integer: = 2000; begin execute immediate 'insert into temp (empno, ename) '| 'select empno, ename from emp '| 'where sal>: 1' using l_sal; commit; end; select * from temp;

 

7. dynamically define the cursor

-- 7. dynamic definition CURSOR declare msql varchar2 (111): = 'select * from emp'; tbl_emp emp % rowtype; type cur_type is ref CURSOR; cur cur_type; begin OPEN cur for msql; -- open the dynamically defined cursor loop fetch cur into tbl_emp; -- cyclically assigned exit when cur % NOTFOUND; -- bounce condition dbms_output.put_line (tbl_emp.empno | ''| tbl_emp.ename ); -- print end loop; end;