oracle的几道程序题

公司为期了两周的oracle培训，今天进行了一次测试，现将其中三道程序题供大家分享，共同学习讨论。

1、设计一个函数，给出对象号，返回对象名

create or replace function get_obj_name (v_id number)
 return VARCHAR2
 is
 v_name varchar2(100);
 cursor c(o_id number) is
  select object_name from dba_objects
   where object_id=o_id;
 begin
 open c(v_id);
 fetch c into v_name;
  if c%notfound then
   v_name:='not found';
  end if;
 close c;
 return v_name;
end;
/
select get_obj_name(124) from dual;
select get_obj_name(124334343) from dual;

2、设计一个程序，显示某表空间中空间使用情况。dba_free_space
TABLESPACE_NAME:表空间名
FILE_ID :空闲空间所在的文件号
BLOCK_ID：空闲空间开始的块号
BLOCKS ： 空闲空间大小

CREATE OR REPLACE PROCEDURE list_free_space
(v_name varchar2, ncol number,used varchar2,freed varchar2)
is
   cursor c(t_name varchar2) is
     select * from dba_free_space
     where tablespace_name=t_name
     order by file_id,block_id;
   cursor c2(t1_name varchar2) is
     select blocks from dba_data_files
     where tablespace_name=t1_name;
   j number:=1;
   k number:=1;
   m number:=1;
begin
   for x in c(v_name) loop
      for i in j..x.block_id-1 loop
         dbms_output.put(used);
         k:=k+1;
         if k>=ncol then
           dbms_output.put_line('|');
           k:=1;
         end if;
      end loop;
      for i in 1..x.BLOCKS loop
         dbms_output.put(freed);
         k:=k+1;
         if k>=ncol then
           dbms_output.put_line('|');
           k:=1;
         end if;
      end loop;
      j:=x.block_id+x.BLOCKS;
   end loop;
   open c2(v_name);
   fetch c2 into m;
   m:=1;
   for i in 1..m-j loop
     dbms_output.put(used);
     k:=k+1;
     if k>=ncol then
     dbms_output.put_line('|');
        k:=1;
     end if;
   end loop;
   close c2;
   close c;
   dbms_output.put_line('END');
   NULL;
end;
/

select blocks from dba_data_files where tablespace_name='UNDOTBS1';
set serveroutput on size 50000
set linesize 100
exec list_free_space('UNDOTBS1',30,'*','_');

3、需要经常统计每个会话的逻辑读和物理读信息，直接使用SQL很不方便，设计一个程序，
简化查看某个会话逻辑读、物理读的过程
CREATE OR REPLACE PACKAGE my_stat IS
   TYPE stat IS RECORD(
     value number,
     name varchar2(100));
   type var_stat is table of stat index by binary_integer;
   n_stat var_stat;
   n_stat_tmp number;
   PROCEDURE get_stat(v_sid number,v_name varchar2);
END my_stat;
/

CREATE OR REPLACE PACKAGE BODY my_stat IS
   PROCEDURE get_stat(v_sid number,v_name varchar2) is
      cursor c1(c_sid number,c_name varchar2)
        is select a.name,b.value
           from v$statname a,v$sesstat b where
           a.STATISTIC#=b.STATISTIC# and
           a.name=c_name and b.sid=c_sid;
      i number;
   begin
 if n_stat_tmp = 0 then
    n_stat_tmp:=1;
    n_stat(n_stat_tmp).value:=0;
    n_stat(n_stat_tmp).name:=v_name;
 else
   i:=1;
          while i<=n_stat.count loop
            if n_stat(i).name = v_name then
         n_stat_tmp:=i;
               goto EXIT_LOOP;
            end if;
     i:=i+1;
          end loop;
   if i=n_stat.count+1 then
            n_stat(i).value:=0;
     n_stat(i).name:=v_name;
            n_stat_tmp:=i;
          end if;
  end if;
 <<EXIT_LOOP>>
 for x in c1(v_sid,v_name) loop
    dbms_output.put_line(x.value-n_stat(n_stat_tmp).value );
    n_stat(n_stat_tmp).value:=x.value;
  end loop;
   end get_stat;
   begin
   n_stat_tmp:=0;
END my_stat;

/