Oracle Cursor 관련 사용 예제모음

##########################################################################
### MASSIVE READ USING ARRAY AND BULK COLLECT
### - reading 1000 -> 2000 -> 3000 -> 4000 -> 5000 ros ROWS
### - this is executing 5 different sql statement within loop
### - PAGE 361
##########################################################################

declare
c sys_refcursor; /* SYS_REFCURSOR can be used from oracle9ir1 and up only. */
type array is
  table of big_table%rowtype
  index by binary_integer;
l_rec array;
begin
for i in 1 .. 5
loop
  open c for select * from big_table;
  fetch c bulk collect into l_rec limit i*1000;
  /* i*1000 sets the number of the rows */
  close c;
end loop;
end;
/



##########################################################################
### USING REF CURSOR
##########################################################################

variable a refcursor

variable b refcursor

variable c refcursor

begin
open :a for select empno form emp q1 where ename = 'blake';
open :b for select empno form emp q1 where ename = 'blake';
open :c for select empno form emp q1 where ename = 'blake';
end;
/



##########################################################################
### DYNAMIC SQL USING BULK COLLECT
### following coding dynamically decides
### - which table to be targeted on runtime, also the value to be inserted
### - DBMS_SQL USAGE
##########################################################################

create or replace package dyn_insert
as
procedure dbms_sql_method  /* target table name */
  (p_tname in varchar2, p_value in varchar2);
procedure dbms_imd_method  /* value to be inserted */
  (p_tname in varchar2, p_value in varchar2);
end;
/

create or replace package body dyn_insert
as
g_last_tname  varchar2(30);  /* stores table name for future soft parsing */
g_cursor number := dbms_sql.open_cursor; /* saves cursor handle information curding session */
procedure dbms_sql_method
  (p_tname in varchar2, p_value in varchar2)
is
l_rows number;
begin
if (g_last_tname <> p_tname or g_last_tname is null)
then
  dbms_sql.parse (g_cursor,
    'insert into ' || p_tname ||
    ' (x) values (:x)',
    dbms_sql.native);
  g_last_tname := p_tname;
end if;
dbms_sql.bind_variable(g_cursor, ':x', p_value);
l_rows := dbms_sql.execute (g_cursor);
end;
/



##########################################################################
### DYNAMIC SQL USING BULK INSERT
### - following coding dynamically decides
###   which table to be targeted on runtime, also the value to be inserted
### - USING ARRAY, BLUK COLLECT
### - DBMS_SQL USAGE
##########################################################################

create or replace vcArray as table of varchar2(5)
/


create or replace package dyn_insert2
as
procedure exec_imd_method
  (p_tname in varchar2, p_value in vcArray);
end;
/


create or replace package body dyn_insert2
as
procedure exec_imd_method
  (p_tname in varchar2, p_value in vcArray)
is
begin
forall in 1 .. p_value.count
execute immeidate 'insert into ' || p_tname ||
'(x) values (:x)' using p_value(i);
/***
*** this block is dynamic BULK inserts in Oracle 8i
execute immeidate
'begin
  forall i in 1 .. n
  insert into ' || p_tname || '(x) values (:x(i);
  end;'
using p_value.count, p_value;
***
***/

end;
end;
/


declare
l_array vcArray := vcArray();
begin
for i in 1 .. 5000 loop
  l_array.extend;
  l_array(l_array.count) := i;
  lf (mod(l_array.count,1000) = 0 or i=5000)
  then
   dyn_insert2.exec_imd_method('T', l_array);
   l_array := vcArray();
  end if;
end loop;
end;
/



##########################################################################
### DECREASING # OF PARSING IN JAVA
##########################################################################

static PreparedStatement saveTimesPs;

static void saveTiems (Connection con, String which, long elap) throws Exception
{
system.out.println( which + " ( " + elap + ")" );
if ( saveTimesPS == null )
saveTimesPs = con.prepareStatement
  ("insert into timings " +
   "( which, elap ) values "+
   "( ?, ? )" );

saveTimesPs.setString(1.which);
saveTimesPs.setLong(2,elap);
saveTimesPs.executeUpdate();



##########################################################################
### CREATING SAMPLE TABLES
##########################################################################

create table t1
as
select mod(rownum, 1000) id,
rpad('x', 300, 'x') data
from all_objects
where rownum <= 5*1000;



##########################################################################
### FOR OLTP & OLAP WORK-STYLE SIMULATION
### - DBMS_JOB, DBMS_STATS USAGE
##########################################################################

- oltp work-style procedure

create or replace procedure oltp_style
as
l_rec big_table%rowtype;
l_n number;
begin
for i in 1 .. 10000
loop
  l_n := trunc (dbms_random.value(2.1000000) );
  select * into l_rec from big_table where id = l_n;
end loop;
end;



- olap work-style procedure

create or replace procedure dw_style
as
l_n number;
begin
select count(*) into_n
from (
  select /*+  USE_HASH(t1, t2)
    FULL(T1) FULL(T2)
    NOPARALLEL(T1) NOPARALLEL(T2) */
   t1.data_object_id, t2.data_object_id
  from big_table t1, big_table t12
  where t1.id = t2.id
  );
end;
/


- oltp simulation pl/sql block

declare
n number;
begin
oltp_style;
dbms_job.submit(n, 'oltp_style;');
dbms_job.submit(n, 'oltp_style;');
dbms_job.submit(n, 'oltp_style;');
commit;

dbms_stats.gather_system_stats (gathering_mode => 'START',
     sttab => 'SYSTEM_STATS',
     statid => 'OLTP');

select count(*) into n from user_jobs where what = 'oltp_style;';
while (n>0)
loop
  dbms_lock.sleep(5);
  select count(*) into n form user_jobs where what = 'oltp_style;';
end loop;

dbms_stats.gather_system_stats (gathering_mode => 'STOP',
     sttab => 'SYSTEM_STATS',
     statid => 'OLTP');
end;
/

-- for olap simulation usage, add following codes and modify the rest

olap_style;
dbms_job.submit(n, 'dw_style;');
dbms_job.submit(n, 'dw_style;');
dbms_job.submit(n, 'dw_style;');
commit;

-- for mixed simulation usage, add following codes and modify the rest


dbms_job.submit(n, 'dw_style;');
dbms_job.submit(n, 'dw_style;');
dbms_job.submit(n, 'dw_style;');
dbms_job.submit(n, 'olap_style;');
dbms_job.submit(n, 'olap_style;');
dbms_job.submit(n, 'olap_style;');
commit;

-- also you might want to execute "alter system flush shared_pool" prior
   loading statistics into data dictionary from user statistics tables.



##########################################################################
### WEB-SITE PURPOSE APPLICATION
### - first_row-based using rownum
##########################################################################

select *
from (
select a.*, rownum r
from (your_query_goes_here) a
where rownum <= :max_row)
where r >= :min_row



##########################################################################
### WEB-SITE PURPOSE APPLICATION
### - searches for min_row/max_row within results
### - requires inputs for hint, search_user_name, min_row/max_row value
##########################################################################

create or replace procedure
get_result_set (
p_hint  in varchar2,
p_owner  in varchar2,
p_min_row in varchar2,
p_max_row in varchar2,
p_result_set in out sys_refcursor)
as
begin
open p_result_set
for ' select *
  from (select ' || p_hint || ' a.*.rownum r
    from (select t1.object_name a,
           t2.object_name b
          from big_table t1, big_table t2
          where t1.object_id = t2.object_id
          and t1.owner = :p_owner)a
    where rownum <= :p_max_row)
  where r >= :p_min_row;
using p_owner, p_max_row, p_min_row;
end;
/

exec get_result_set(null, 'WMSYS', 1, 3, :x)

exec get_result_set('/*+ FIRST_ROWS */', 'WMSYS', 10, 30, :x)



##########################################################################
### inserting 1000 rows at the same time
### - DBMS_SQL USAGE
### - PAGE 358
##########################################################################

declare
l_data dbms_sql.number_table;
l_empty dbms_sql.number_table;
begin
for i in 1 .. 1000
loop
  l_data(mod(i,100)) := i;
  if (mod(i,100) = 0)
  then
   forall j in 0 .. l_data.count-1
    insert into t values (l_data(j));
   l_data := l_empty;
  end if;
end loop;
end;
/



##########################################################################
### FOR INVESTIGATING THE CLUSTER'S BLOCK USAGE EFFICIENCY
### shows how many blocks are being used for each cluster index key value
### - DBMS_ROWID USAGE
### - PAGE 527
##########################################################################

select owner,
starting_file_# || '.' || starting_block_#,
ending_file_# || '.' || ending_block_#,
count(*) number_of_rows
(
select b.owner,
  dbms_rowid.rowid_relative_fno(b.rowid) starting_file_#,
  dbms_rowid.rowid_block_number(b.rowid) starting_block_#,
  dbms_rowid.rowid_relative_fno(a.rowid) ending_file_#,
  dbms_rowid.rowid_relative_fno(a.rowid) ending_block_#,
from user_info a, users_objects b
where a.user_name = b.owenr
)
group by owner, starting_file_#, starting_block_#, ending_file_#, ending_block_#



##########################################################################
### SIMULATING OLTP ENVIRONMENT ACCESS
### simulating access of 150,000 times
### - PAGE 537
##########################################################################

declare
l_rec  single_table_hash_cluster%rowtype;
begin
for iters in 1 .. 3
loop
  for i in i .. 50000
  loop
   select * into l_rec
   from single_table_hash_cluster
   where object_id = i;

   select * into l_rec
   from heap_table
   where object_id = i;
  end loop;
end loop;
end;
/



##########################################################################
### DIFF. BETWEEN SINGLE-ROW READ AND BULK-COLLECT READ
### simulating diff. between single-row and bulk collect read using array
### - PAGE 544
##########################################################################

declare
type array is table of varchar2(100);
l_array1 array;
l_array2 array;
l_array2 array;
begin
for i in 1 .. 10
loop
  for x in (select * from all_users)
  loop
   /* single-row read simulation */
   for y in (select col1, col2, col3 from heap single_row
      where username = x.username)
   loop
    null;
   end loop;
   for y in (select col1, col2, col3 from iot single_row
      where username = x.username)
   loop
    null;
   end loop;
   
   /* multi-row read simulation using array and bulk collect */
   select * bulk collect
   into  l_array1, l_array2, l_array3
   from heap bulk_collect
   where username = x.username;
   select * bulk collect
   into  l_array1, l_array2, l_array3
   from iot bulk_collect
   where username = x.username;
  end loop;
end loop;
end;
/

   

##########################################################################
### REF CURSOR and LONG datatype usage
### - PAGE 576
##########################################################################

variable x refcursor

declare
l_stmt long;
begin
for x in (
  select  '''' || column_name || '''' quoted,
   column_name
  from user_tab_columns
  ...)
end loop;
l_stmt := 'select ......';
open :x for l_stmt;
end;
/


##########################################################################
### PL/SQL CODING RUNS FOR 100 HRS
### DBMS_RANDOM USAGE
### - PAGE 644
##########################################################################

declare
l_load number;
begin
for l_hours in 1 .. 100
loop
  for i_hosts in 1 .. 100
  loop
   l_load : dbms_random.random;
   insert into hosts_heap
   (hostname, dt, load, other_stats)
   values
   ('hostnm' || l_hosts, sysdate-(100-l_hours)/24, l_load, 'x');
   insert into host_iot
   (hostname, dt, load, other_stats)
   values
   ('hostnm' || l_hosts, sysdate-(100-l_hours)/24, l_load, 'x');
  end loop;
  commit;
end loop;
end;
/


##########################################################################
### DBMS_APPLICATION_INFO USAGE
### shows the number of function 'f' were being executed within sql.
### - PAGE 654
##########################################################################

create or replace function f
(v_prod_key in number  default null,
v_prc_chk_key in number  default null,
v_return in   varchar2  default null,
v_want_sr in varchar2 defautl null,
v_version in number)
return varchar2
as
begin
dbms_application_info.set_client_info
(userenv('client_info')+1);
return 'x';
end;
/


exec dbms_application_info.set_client_info(0);

select .... f(column_name)

select userenv('client_info') data
from dual;



##########################################################################
### CREATING PAGE NUMBER USING ROWNUM
### - PAGE 659
##########################################################################

select *
from (select /*+ first_rows */ a.*, ROWNUM rnum
  from (your_query_goes_here) a
  where ROWNUM <= :MAX_ROW_TO_FETCH)
where rnum >= :MIN_ROW_TO_FETCH;


== following is bad sql using rownum

select *
from (select /*+ first_rows */ a.* ROWNUM rnum
  from (your_query_goes_here) a )
where rnum between :min_row_to_fetch and :max_row_to_fetch;


##########################################################################
### BULK COLLECT AND DYNAMIC SQL AND ROWNUM SAMPLE
### DBMS_SQL USAGE
### - PAGE 666
##########################################################################

declare
l_owner  dbms_sql.varchar2_table;
l_object_name dbms_sql.varchar2_table;
l_object_type dbms_sql.varchar2_table;
l_created dbms_sql.varchar2_table;

cursor c is
select owner, object_name, object_type, created
from big_table
order by created DESC;

begin
select owner, object_name, object_type, created
bulk collect into l_owner, l_object_name, l_object_type, l_created
from (
  select owner, object_name, object_type, created
  from big_table
  order by created DESC)
where ROWNUM <= 10;

open c;
fetch c bulk collect
into l_owner, l_object_name, l_object_type, l_created
limit 10;
close c;
end;
/


##########################################################################
### SQL TUNING(OUTER JOIN) SAMPLE USING SCALAR SUBQUERY
### - PAGE 675
##########################################################################

- bad sample
select a.username, a.user_id, a.created,
nvl(b.cons_cnt, 0), nvl(c.tables_cnt, 0) tables

from all_users a,

(select owner, count(*) cons_cnt
  from all_constraints
  group by owner) b,

(select owner, count(*) tables_cnt
  from all_tables
  group by owner) c

where a.username = b.owner(+)
and a.username = c.owner(+)
and a.created > sysdate-50
/


- the good one

select a.username, a.user_id, a.created,
nvl(b.cons_cnt, 0) cons, nvl(c.tables_cnt, 0) tables
from all_users a,

(select all_constraints.owner,
  count(*) cons_cnt
  from all_constraints, all_users
  where all_users.created > sysdate - 50
  and all_users.username = all_constraints.owner
  group by owner) b,

(select all_tables.owner,
  count(*) tables_cnt
  from all_tables, all_users
  where all_users.created > sysdate - 50
  and all_suers.username = all_tables.owner
  group by owner) c

where a.username = b.owner(+)
and a.username = c.owner(+)
and a.created > sysdate - 50
/


##########################################################################
### function OVER PARTITION BY ... USAGE SAMPLES
### - from PAGE 680 ~
##########################################################################

select deptno, ename, sal,
sum(sal) over () Total_Salary,
/* shows sum of every employees' salary in every rows */

sum(sal) over (order by deptno, sal) Cum_Total,
/* shows every employee's sequential sum in every rows in order of deptno->sal. */

sum(sal) over (partition by deptno) Salary_By_Dept,
/* shows each dept's salary sum in corrosponding rows. partitioned by deptno */

sum(sal) over (partition by deptno order by sal) Cum_Dept_Total
/* shows each dept's salary sequential sum in order of sal. partitioned by deptno

from emp
order by deptno, sal
/

select owner, last_ddl_time, object_name, object_type
from (
select t1.*,
  max(last_ddl_time) over (partition by owner) max_time
from big_table t1
)
where last_ddl_time = max_time
/


select deptno, ename, sal,
row_number() over (partition by deptno order by sal desc) rn,
rank() over (partition by deptno order by sal desc) rank,
dense_rank() over (partition by deptno order by sal desc) dense_rank
from emp
order by depnto, sal DESC
/


select deptno,
max(decode (dense_rank, 1, sal )) sal1,
max(decode (dense_rank, 2, sal )) sal2,
max(decode (dense_rank, 3, sal )) sal3
from (
select deptno, sal,
  dense_rank() over(partiton by deptno order by sal desc) dense_rank
from emp
)
where dense_rank <= 3
group by deptno
/


select port, activity, nxt_activity,
avg(nxt_activity_date - activity_date) days_between
from (
select port, activity,
  lead(activity) over (partition by order by activity_date) nxt_activity,
  activity_date,
  lead(activity_date) over (partition by port order by activity_date) nxt_activity_date
from t
)
where nxt_activity is not null
group by port, activity, nxt_activity
ordre by 1, 2
/



##########################################################################
### BULK COLELCT USAGE SAMPLES
### DBMS_SQL USAGE
###- PAGE 742
##########################################################################

declare
l_ename  dbms_sql.varchar2_table;
l_empno  dbms_sql.number_table;
l_hiredate dbms_sql.date_table;
begin
for i in 1 .. 5000
loop
  select ename, empno, hiredate
  bulk collect into l_ename, l_empno, l_hiredate
  from emp;
end loop;
end;
/



##########################################################################
### BULK COLELCT and ARRAY USAGE SAMPLES
### - comparing one-at-time and bulk collect
### - shows how to control the array size
### - for oracle 9i r2 and above only
### - PAGE 742
##########################################################################

- bad sample

create or replace procedure row_at_a_time
as
begin
for x in (select * from dba_objects)
loop
  insert into t1 values x;
end loop;
end;
/



/* following code is for oracle9i r2 and above only */

create or replace procedure nrows_at_a_time
(p_array_size in number)
as
type array is table of dba_objects%rowtype;
l_data array;
cursor c is select * from dba_objects;
begin
open c;
loop
  fetch c bulk collect into l_data LIMIT p_array_size;
  forall i in 1 .. l_data.count
   insert into t2 values l_data(i);
  exit when c%notfound;
end loop;
end;
/


##########################################################################
### BULK COLELCT using ARRAY VS REF CURSOR
### - ref cursor is better then bulk collect in client environment
### - PAGE 752
##########################################################################

create or replace package demo_pkg
as
type varchar2_array is table of varchar2(30)
index by binary_integer;

type rc is ref cursor;

procedure index_by (
  p_owner  in varchar2,
  p_object_name  out varchar2_array,
  p_object_type out varchar2_array,
  p_timestamp out varchar2_array);

procedure ref_cursor (
  p_owner  in varchar2,
  p_cursor in out rc);
end;
/


create or replace package body demo_pkg
as

procedure index_by (
  procedure index_by (
  p_owner  in varchar2,
  p_object_name  out varchar2_array,
  p_object_type out varchar2_array,
  p_timestamp out varchar2_array);
is
begin
select object_name, object_type, timestamp
bulk collect into
  p_object_name, p_object_type, p_timestamp
from t
where owner = p_owner;
end;

procedure ref_cursor (
  p_owner  in varchar2,
  p_cursor  in out rc)
is
open p_cursor for
select object_name, object_type, timestamp
from tS
where owner = p_owner;
end;

end;
/
   


##########################################################################
### BULK COLELCT using ARRAY SAMPLE
### - PAGE 777
##########################################################################

create or replace procedure
array_fetch_heap_insert
(p_arraysize in number default 100)
as
type array is table of all_objects.object_name%type
  index by binary_integer;
l_key_col array;
cursor c is select object_name from all_objects;
begin
open c;
loop
  fetch c bulk colelct into l_key_col limit p_arraysize;

  forall i in 1 .. l_key_col.count
   insert into built_by_us (key_col, key_val)
   values (l_key_col(i),
    (select key)val
     from lookup_heap
     where key_col = l_key_col (i))
    )
  exit when c%notfound;
end loop;
close c;
end;