ORACLE 動態語句的筆記

ORACLE 動態語句的筆記[@more@]

-- 動態SQL例子
create or replace procedure DynEmpProc(p_job in varchar2)
as
type refcursor is ref cursor;
-- We must allocate our own host
-- variables and resources using dynamic sql.
l_cursor refcursor;
l_ename emp.ename%type;
begin
-- We start by parsing the query
open l_cursor for
'select ename from emp where job = :x' using in p_job;
loop
-- and explicitly FETCHING from the cursor.
fetch l_cursor into l_ename;
-- We have to do all error handling
-- and processing logic ourselves.
exit when l_cursor%notfound;
dbms_output_.put_line(l_ename);
end loop;
-- Make sure to free up resources
close l_cursor;
exception
when others then
-- and catch and handle any errors so
-- as to not 'leak' resources over time
-- when errors occur.
if(l_cursor%isopen)
then close l_cursor;
end if;
raise;
end;
/

-- 靜態SQL的例子
create or replace procedure StaticEmpProc(p_job in varchar2)
as
begin
for x in (select ename from emp where job = p_job)
loop
dbms_output.put_line(x.ename);
end loop;
end;
/

set serveroutput on size 1000000
exec DynEmpProc('CLEAK');
exec StaticEmpProc(CLEAK');

-- DBMS_SQL的使用
set pagesize 30
set pause on
prompt remember to hit ENTER to start reading
select text from all_source where name='DBMS_SQL' and type='PACKAGE' order by line;

create or replace function get_row_cnts(p_tname in varchar2) return number
as
l_theCursor integer;
l_columnValue number default null;
l_status integer;
begin
-- Step 1.open then cursor.
l_theCursor := dbms_sql.open_cursor;
begin
-- Step 2.parse the query.
dbms_sql.parse(c=>l_theCursor,
statement=>'select count(*) from || p_tname,
language_flag=>dbms_sql.native);
-- Step 5.define the output of this query as a NUMBER.
dbms_sql.define_column(c=>l_theCursor,
position=>1,
column=>l_columnValue);
-- Step 6.execute the statement.
l_status := dbms_sql.execute(l_theCursor);
-- Step 7.fetch the rows.
if(dbms_sql.fetch_rows(c=>l_theCursor) > 0)
then
-- Step 8.retrieve the outputs.
dbms_sql.column_value(e=>l_theCursor,
position=>1,
value=>l_columnValue);
end if;
-- Step.close the cursor.
dbms_sql.close_cursor(c=>l_theCursor);
return l_columnValue;
exception
when others then
dbms_output.put_line('=>'||sqlerrm);
dbms_sql.close_cursor(c=>l_theCursor);
raise;
end;
end;
/
set servcroutput on
begin
dbms_output.put_line('Emp has this many rows ' || get_row_cnts('emp'));
end;
/

begin
dbms_output.put_line('Not a table has this many rows ' || get_row_cnts('NOT_A_TABLE'));
end;
/

create or replace function update_row(p_owner in varchar2,
p_newDname in varchar2,p_newLoc in varchar2,
p_deptno in varchar2,p_rowid out varchar2)
return number
is
l_theCursor integer;
l_columnValue number default null;
l_status integer;
l_update long;
begin
l_update := 'update ' || p_owner || '.dept
set dname = :bv1,loc = :bv2
where deptno = to_number(:pk)
returning rowid into :out';
-- Step 1.open the cursor.
l_theCursor := dbms_sql.open_cursor;
begin
-- Step 2.parse the query.
dbms_sql.parse(c=>l_theCursor,
statement=>l_update,
language_flag=>dbms_sql.native);
-- Step 3.bind all of the INPUTS and OUTPUTS.
dbms_sql.bind_variable(c=>l_theCursor,
name=>':bv1',
value=>p_newDname);
dbms_sql.bind_variable(c=>l_theCursor,
name=>':bv2',
value=>p_newLoc);
dbms_sql.bind_variable(c=>l_theCursor,
name=>':pk',
value=>p_deptno);
dbms_sql.bind_variable(c=>l_theCursor,
name=>':out',
value=>p_rowid,
out_value_size=>4000);
-- Step 4.execute the statement.Since this is a DML.
-- statement,L_STATUS is be the number of rows updated.
-- This is what we'll return.
l_status := dbms_sql.execute(l_theCursor);
-- Step 5.retrieve the OUT variables from the statement.
dbms_sql.variable_value(c=>l_theCursor,
name=>':out',
value=>p_rowid);
-- Step 6.close the cursor.
dbms_sql.close_cursor(c=>l_theCursor);
return l_columnValue;
exception
when dup_val_on_index then
dbms_output.put_line('=>' || sqlerrm);
dbms_sql.close_cursor(c=>l_theCursor);
raise;
end;
end;
/

set serveroutput on
declare
l_rowid varchar(50);
l_rows number;
begin
l_rows := update_row('SCOTT','CONSULTING','WASHINGTON','10',l_rowid);
dbms_output.put_line('Updated' || l_rows || ' rows');
dbms_output.put_line('its rowid was ' || l_rowid);
end;
/

-- 本地動態SQL
create or replace function get_row_cnts(p_tname in varchar2) return number
as
l_cnt number;
begin
execute immediate 'select count(*) from ' || p_tname into l_cnt;
return l_cnt;
end;
/
set serveroutput on
exec dbms_output.put_line(get_row_cnts('emp'));

create or replace function update_row(p_owner in varchar2,
p_newDname in varchar2,p_newLoc in varchar2,p_deptno in varchar2,
p_rowid out varchar2)
return number
is
begin
execute immediate 'update ' || p_owner || '.dept
set dname = :bv1,loc = :bv2
where deptno = to_number(:pk)
returning rowid into :out'
using p_newDname,p_newLoc,p_deptno
returning into p_rowid;
return sql%rowcount;
end;
/

set serveroutput on
declare
l_rowid varchar2(50);
l_rows number;
begin
l_rows := update_row('SCOTT','CONSULTING','WASHINGTON','10',l_rowid);
dbms_output.put_line('Updated ' || l_rows || ' rows');
dbms_output.put_line('its rowid was ' || l_rowid);
end;
/

create or replace package my_pkg
as
type refcursor_type is ref cursor;
procedure get_emps(p_ename in varchar2 default null,
p_deptno in varchar2 default null,
p_cursor in out refcursor_type);
end;
/
create or replace package body my_pkg
as
procedure get_emps(p_ename in varchar2 default null,
p_deptno in varchar2 default null,
p_cursor in out refcursor_type)
is
l_query long;
l_bind varchar2(30);
begin
l_query := 'select deptno,ename,job from emp';
if(p_ename is not null)
then
l_query := l_query || ' where ename like :x';
l_bind := '%' || upper(p_ename) || '%';
elsif(p_deptno is not null)
then
l_query := l_query || ' where deptno = to_number(:x)';
l_bind := p_deptno;
else
raise_application_error(-20001,'Missing search criteria');
end if;
open p_cursor for l_query using l_bind;
end;
end;
/

variable C refcursor
set autoprint on
exec my_pkg.get_emps(p_ename=>'a',p_cursor=>:c)
exec my_pkg.get_emps(p_deptno=>'10',p_cursor=>:c)

create or replace package dyn_demo
as
type array is table of varchar2(2000);
/*
* DO_QUERY will dynamically query the emp
* table and process the results,You might
* call it like this:
*
* dyn_demo.do_query(array('ename','job'),
array('like','='),
array('%A%','CLERK'));
*
* to have it query up;
*
* select * from emp where ename like '%A%' and job = 'CLERK'
*
* for example.
*/
procedure do_query(p_enames in array,p_operators in array,
p_values in array);
end;
/

create or replace package body dyn_demo
as
/*
* DBMS_SQL-based implementation of dynamic
* query with unknown bind variables
*/
g_cursor int default dbms_sql.open_cursor;

procedure do_query(p_enames in array,
p_operators in array,
p_values in array)
is
l_query long;
l_sep varchar2(20) default ' where ';
l_comma varchar2(1) default '';
l_status int;
l_colValue varchar2(4000);
begin
/*
* This is our constant SELECT list - we'll always
* get these three columns. The predicate is what
* changes.
*/
l_query := 'select ename,empno,job from emp';
/*
* We build the predicate by putting:
* ename operator :bv X
* into the query first.
*/
for i in 1 .. p_enames.count loop
l_query := l_query || l_sep || p_enames(i) || '' ||
p_operators(i) || '' || ':bv' || i;
l_sep := ' and ';
end loop;
/*
* Now we can parse the query
*/
dbms_sql.parse(g_cursor,l_query,dbms_sql.native);
/*
* and then define the outputs.We fetch all three
* columns into a VARCHAR2 type.
*/
for i in 1..3 loop
dbms_sql.define_column(g_cursor,i,l_colValue,4000);
end loop;
/*
* Now,we can bind the inputs to the query
*/
for i in 1..p_enames.count loop
dbms_sql.bind_variable(g_cursor,':bv'||i,p_values(i),4000);
end loop;
/*
* and now we loop over the rows and print out the results.
*/
while(dbms_sql.fetch_rows(g_cursor) > 0)
loop
l_comma := '';
for i in 1..3 loop
dbms_sql.column_value(g_cursor,i,l_colValue);
dbms_output.put(l_comma || l_colValue);
l_comma := ',';
end loop;
dbms_output.new_line;
end loop;
end;
end dyn_demo;
/

set serveroutput on
begin
dyn_demo.do_query(dyn_demo.array('ename','job'),
dyn_demo.array('like','='),
dyn_demo.array('%A%','CLERK'));
end;
/

rem SCOTT must have GRANT CREATE ANY CONTEXT TO SCOTT;
rem or a role with that for this to work
create or replace context hv_context using dyn_demo;
create or replace package body dyn_demo
as
procedure do_query(p_enames in array,
p_operators in array,p_values in array)
is
type rc is ref cursor;
l_query long;
l_sep varchar2(20) default ' where ';
l_cursor rc;
l_ename emp.ename%type;
l_empno emp.empno%type;
l_job emp.job%type;
begin
/*
* This is our constant SELECT list - we'll always
* get these three columns.The predicate is what changes.
*/
l_query := 'select ename,empno,job from emp';
for i in 1..p_enames.count loop
l_query := l_query || l_sep || p_enames(i) || ' ' ||
p_operators(i) || ' ' || 'sys_context("BV_CONTEXT","' ||
p_enames(i) || '")';
l_sep := ' and ';
dbms_session.set_context('bv_context',p_enames(i),p_values(i));
end loop;
open l_cursor for l_query;
loop
fetch l_cursor into l_ename,l_empno,l_job
exit when l_cursor%notfound;
dbms_output.put_line(l_ename||','||l_empno||','||l_job);
end loop;
close l_cursor;
end;
end dyn_demo;
/

set serveroutput on
begin
dyn_demo.do_query(dyn_demo.array('ename','job'),
dyn_demo.array('like','='),
dyn_demo.array('%A%','CLERK'));
end;
/

create or replace procedure dump_query(p_query in varchar2)
is
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_line long;
/*
* We'll be using this to see how many columns
* we have to fetch so we can define them and
* then retrieve their values.
*/
l_deseTbl dbms_sql.desc_tab;
/* Step 1 - open cursor. */
l_theCursor integer default dbms_sql.open_cursor;
begin
/* Step 2 - parse the input query so we can describe it. */
dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
/* Step 3 - now,describe the outputs of the query. */
dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);
/*
* Step 4 - we do not use in this example,no BINDING needed.
* Step 5 - for each column.we need to define it,tell the database
* what we will fetch into. In this case,all data is going
* to be fetched into a single varchar2(4000) variable.
*/
for i in 1..l_colCnt
loop
dbms_sql.define_column(l_theCursor,i,l_columnValue,4000);
end loop;
/* Step 6 - execute the statement. */
l_status := dbms_sql.execute(l_theCursor);
/* Step 7 - fetch all rows. */
while(dbms_sql.fetch_rows(l_theCursor) > 0)
loop
/* Build up a big output line,this is more efficient than calling
* DBMS_OUTPUT.PUT_LINE inside the loop.
*/
l_cnt := l_cnt + 1;
l_line := l_cnt;
/* Step 8 - get and process the column data. */
for i in 1..l_colCnt loop
dbms_sql.column_value(l_theCursor,i,l_columnValue);
l_line := l_line || '.' || l_columnValue;
end loop;
/* Now print out this line. */
dbms_output.put_line(l_line);
end loop;
/* Step 9 - close cursor to free up resources.. */
dbms_sql.close_cursor(l_theCursor);
exception
when others then
dbms_sql.close_cursor(l_theCursor);
raise;
end dump_query;
/

create or replace procedure desc_query(p_query in varchar2)
is
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_line long;
/* We'll be using this to see what our query SELECTs */
l_descTbl dbms_sql.desc_tab;
/* Step 1 - open cursor. */
l_theCursor integer default dbms_sql.open_cursor;
begin
/* Step 2 - parse the input query so we can describe it. */
dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
/* Step 3 - new,describe the outputs of the query.
* L_COLCNT will contain the number of columns selected
* in the query.It will be equal to L_DESCTBL.COUNT
* actually and so it is redundant really.L_DESCCTBL
* contains the useful data about our SELECTed columns.
*/
dbms_sql.describe_columns(c=>l_theCursor,
col_cnt=>l_colCnt,desc_t=>l_descTbl);
for i in 1..l_colCnt
loop
dbms_output.put_line('Column Type......'||l_descTbl(i).col_type);
dbms_output.put_line('Max Length.......'||l_descTbl(i).col_max_len);
dbms_output.put_line('Name.............'||l_descTbl(i).col_name);
dbms_output.put_line('Name Length......'||l_descTbl(i).col_name_len);
dbms_output.put_line('ObjColumn Schema Name.'||l_descTbl(i).col_schema_name);
dbms_output.put_line('Schema Name Length.'||l_descTbl(i).col_schema_name_len);
dbms_output.put_line('Precision........'||l_descTbl(i).col_precision);
dbms_output.put_line('Scale............'||l_descTbl(i).col_scale);
dbms_output.put_line('Charsetid........'||l_descTbl(i).col_Charsetid);
dbms_output.put_line('Charset Form.....'||l_descTbl(i).col_charsetform);
if(l_desctbl(i).col_null_ok) then
dbms_output.put_line('Nullable........Y');
else
dbms_output.put_line('Nullable........N');
end if;
dbms_output.put_line('-------------------');
end loop;
/* Step 9 - close cursor to free up resources. */
dbms_sql.close_cursor(l_theCursor);
exception
when others then
dbms_sql.close_cursor(l_theCursor);
raise;
end desc_query;
/

set serveroutput on
exec desc_query('select rowid,ename from emp');

create or replace function dump_fixed_width(p_query in varchar2,
p_dir in varchar2,p_filename in varchar2) return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_line long;
l_descTbl dbms_sql.desc_tab;
l_dateformat nls_session_parameters.value%type;
begin
select value into l_dateformat
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
/* Use a date format that includes the time. */
execute immediate 'alter session set nls_date_format="dd-mon-yyyy hh24:mi:ss"';
l_output := utl_file.fopen(p_dir,p_filename,'w',32000);
/* Parse the input query so we can describe it. */
dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
/* Now,describe the outputs of the query. */
dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);
/*
* For each column,we need to define it,to tell the database
* what we will fetch into.In this case,all data is going
* to be fetched into a single varchar2(4000) variable.
*
* We will also adjust the max width of each column.We do
* this so when we OUTPUT the data.Each field starts and
* stops in the same position for each record.
*/
for i in 1..l_colCnt loop
dbms_sql.define_column(l_theCursor,i,l_columnValue,4000);
if(l_descTbl(i).col_type = 2) /* number type */
then
l_desctbl(i).col_max_len := l_desctbl(i).col_precision + 2;
elsif(l_desctbl(i).col_type=12) /* date type */
then
/* length of my format above */
l_desctbl(i).col_max_len := 20;
end if;
end loop;
l_status := dbms_sql.execute(l_theCursor);
while(dbms_sql.fetch_rows(l_theCursor) > 0)
loop
/*
* Build up a big output line.This is more efficient than
* calling UTL_FILE.PUT inside the loop.
*/
l_line := null;
for i in 1..l_colCnt loop
dbms_sql.column_value(l_theCursor,i,l_columnValue);
l_line := l_line || rpad(nvl(l_columnValue,''),l_desctbl(i).col_max_len);
end loop;
/* Now print out that line and increment a counter. */
utl_file.put_line(l_output,l_line);
l_cnt := l_cnt + 1;
end loop;
/* Free up resources. */
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose(l_output);
/* Reset the date format ... and return. */
execute immediate 'alter session set nls_date_format="'||l_dateformat||'"';
return l_cnt;
exception
when others then
dbms_sql.close_cursor(l_theCursor);
execute immediate 'alter session set nls_date_format="'||l_dateformat||'"';
end dump_fixed_width;
/

create or replace type vcArray as table of varchar2(400);
create or replace type dtArray as table of date;
create or replace type nmArray as table of number;
create or replace package load_data as
procedure dbmssql_array(p_tname in varchar2,
p_arraysize in number default 100,
p_rows in number default 500);
procedure dbmssql_noarray(p_tname in varchar2,
p_rows in number default 500);
procedure native_dynamic_noarray(p_tname in varchar2,
p_rows in number default 500);
procedure native_dynamic_array(p_tname in varchar2,
p_arraysize in number default 100,
p_rows in number default 500);
end load_data;
/

create or replace package body load_data
as
procedure dbmssql_array(p_tname in varchar2,
p_arraysize in number default 100,
p_rows in number default 500)
is
l_stmt long;
l_theCursor integer;
l_status number;
l_col1 dbms_sql.number_table;
l_col2 dbms_sql.date_table;
l_col3 dbms_sql.varchar2_table;
l_cnt number default 0;
begin
l_stmt := 'insert into ' || p_tname || '_q1(a,b,c) values (:a,:b,:c)';
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse(l_theCursor,l_stmt,dbms_sql.native);
/*
* We will make up data here. When we've made up ARRAYSIZE
* rows,we'll bulk insert them.At the end of the loop,
* if any rows remain,we'll insert them as well.
*/
for i in 1..p_rows
loop
l_cnt := l_cnt + 1;
l_col1(l_cnt) := i;
l_col2(l_cnt) := sysdate + i;
l_col3(l_cnt) := to_char(i);
if(l_cnt < p_arraysize)
then
dbms_sql.bind_array(l_theCursor,':a',l_col1,1,l_cnt);
dbms_sql.bind_array(l_theCursor,':b',l_col2,1,l_cnt);
dbms_sql.bind_array(l_theCursor,':c',l_col3,1,l_cnt);
l_status := dbms_sql.execute(l_theCursor);
l_cnt := 0;
end if;
end loop;
if(l_cnt > 0)
then
dbms_sql.bind_array(l_theCursor,':a',l_col1,1,l_cnt);
dbms_sql.bind_array(l_theCursor,':b',l_col2,1,l_cnt);
dbms_sql.bind_array(l_theCursor,':c',l_col3,1,l_cnt);
l_status := dbms_sql.execute(l_theCursor);
end if;
dbms_sql.close_cursor(l_theCursor);
end;

procedure dbmssql_noarray(p_tname in varchar2,p_rows in number default 500)
is
l_stmt long;
l_theCursor integer;
l_status number;
begin
l_stmt := 'insert into '||p_tname||'_q2(a,b,c) values (:a,:b,:c)';
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse(l_theCursor,l_stmt,dbms_sql.native);
/*
* We will make up data here.When we've made up ARRAYSIZE
* rows,we'll bulk insert them.At the end of the loop,
* if any rows remain,we'll insert them as well.
*/
for i in 1..p_rows
loop
dbms_sql.bind_variable(l_theCursor,':a',i);
dbms_sql.bind_variable(l_theCursor,':b',sysdate + i);
dbms_sql.bind_variable(l_theCursor,':c',to_char(i));
l_status := dbms_sql.execute(l_theCursor);
end loop;
dbms_sql.close_cursor(l_theCursor);
end;

procedure native_dynamic_noarray(p_tname in varchar2,
p_rows in number default 500)
is
begin
/*
* Here,we simply make up a row and insert it.
* A trivial amount of code to write and execute.
*/
for i in 1..p_rows
loop
execute immediate 'insert into '||p_tname||'_q3(a,b,c) values (:a,:b,:c)' using i,sysdate+i,to_char(i);
end loop;
end;

procedure native_dynamic_array(p_tname in varchar2,
p_arraysize in number default 100,
p_rows in number default 500)
is
l_stmt long;
l_theCursor integer;
l_status number;
l_col1 nmArray := nmArray();
l_col2 dtArray := dtArray();
l_col3 vcArray := vcArray();
l_cnt number := 0;
begin
/*
* We will make up data here.When we've made up ARRAYSIZE
* rows,we'll bulk insert them.At the end of the loop,
* if any rows remain,we'll insert them as well.
*/
l_col1.extend(p_arraysize);
l_col2.extend(p_arraysize);
l_col3.extend(p_arraysize);
for i in 1..p_rows
loop
l_cnt := l_cnt + 1;
l_col1(l_cnt) := i;
l_col2(l_cnt) := sysdate + i;
l_col3(l_cnt) := to_char(i);
if(l_cnt = p_arraysize)
then
execute immediate 'begin
forall i in 1..:n
insert into '||p_tname||'_q4(a,b,c) values (:a(i),:b(i),:c(i)); end;' using l_cnt,l_col1,l_col2,l_col3;
l_cnt := 0;
end if;
end loop;
if(l_cnt > 0)
then
execute immediate 'begin
forall i in 1..:n
insert into '||p_tname||'_q4(a,b,c) values (:a(i),:b(i),:c(i)); end;' using l_cnt,l_col1,l_col2,l_col3;
end if;
end;
end load_data;
/

create table t(a int,b date,c varchar2(15));
alter session set sql_trace=true;
truncate table t;
exec load_data.dbmssql_array('t',50,10000);

truncate table t;
exec load_data.native_dynamic_array('t',50,10000);

truncate table t;
exec load_data.dbmssql_noarray('t',10000);

truncate table t;
exec load_data.native_dynamic_noarray('t',10000);

create or replace procedure native_dynamic_select
as
type rc is ref cursor;
l_cursor rc;
l_ename varchar2(255);
l_cnt number := 0;
l_start number default dbms_utility.get_time;
begin
open l_cursor for 'select object_name from all_objects';
loop
fetch l_cursor into l_ename;
exit when l_cursor%notfound;
l_cnt := l_cnt + 1;
end loop;
close l_cursor;
dbms_output.put_line(l_cnt||' rows processed');
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||' seconds');
exception
when others then
if(l_cursor%isopen)
then
close l_cursor;
end if;
raise;
end;
/

create or replace procedure dbms_sql_select
as
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue dbms_sql.varchar2_table;
l_status integer;
l_cnt number := 0;
l_start number default dbms_utility.get_time;
begin
dbms_sql.parse(l_theCursor,'select object_name from all_objects',
dbms_sql.native);
dbms_sql.define_array(l_theCursor,1,l_columnValue,100,1);
l_status := dbms_sql.execute(l_theCursor);
loop
l_status := dbms_sql.fetch_rows(l_theCursor);
dbms_sql.column_value(l_theCursor,1,l_columnValue);
l_cnt := l_status + l_cnt;
exit when l_status <> 100;
end loop;
dbms_sql.close_cursor(l_theCursor);
dbms_output.put_line(l_cnt||' rows processed');
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||' seconds');
exception
when others then
dbms_sql.close_cursor(l_theCursor);
raise;
end;
/

set serveroutput on
exec native_dynamic_select
exec dbms_sql_select