Oracle 定义对象的代码可以从user_source数据字典中找到

Oracle 定义对象的代码可以从user_source数据字典中找到

//在Oracle中，我们可以从数据字典user_source(视图)中查看对象定义代码;  
//我们先来看user_source视图的结构：  
desc user_source;  
Name Type           Nullable Default Comments                                                              
---- -------------- -------- ------- --------------------------------------------------------------------  
NAME VARCHAR2(30)   Y                Name of the object                                                    
TYPE VARCHAR2(12)   Y                Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",  
                                                         "PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"   
LINE NUMBER         Y                Line number of this line of source                               
TEXT VARCHAR2(4000) Y                Source text    
//  
//下面的代码是user_source视图的定义代码：  
CREATE OR REPLACE FORCE VIEW "SYS"."USER_SOURCE" ("NAME", "TYPE", "LINE", "TEXT") AS  
select o.name,  
       decode(o.type#,   
              7, 'PROCEDURE',   
              8, 'FUNCTION',   
              9, 'PACKAGE',  
              11, 'PACKAGE BODY',   
              12, 'TRIGGER',   
              13, 'TYPE',   
              14, 'TYPE BODY',  
              'UNDEFINED'),  
       s.line,   
       s.source  
from sys.obj$ o,   
     sys.source$ s  
where o.obj# = s.obj#   
  and ( o.type# in (7, 8, 9, 11, 12, 14) OR  
                   ( o.type# = 13 AND o.subname is null))  
  and o.owner# = userenv('SCHEMAID')  
union all  
select o.name,   
       'JAVA SOURCE',   
       s.joxftlno,   
       s.joxftsrc  
from sys.obj$ o,   
     x$joxfs s  
where o.obj# = s.joxftobn  
  and o.type# = 28  
  and o.owner# = userenv('SCHEMAID');  
//  
//下面是我先定义好的一个procedure：show_employee,  
//现在我们来看其定义代码,注意，传递的参数要大写:  
set linesize 1000;  
set pagesize 1000;  
set long 10000;  
select type,line||' '||text  
from user_source  
where name='SHOW_EMPLOYEE';  
//  
TYPE         LINE||''||TEXT  
------------ -----------------------------------------------------------------------------------------  
PROCEDURE    1 procedure show_employee(empno_in in emp.empno%type)  
PROCEDURE    2 as 
PROCEDURE    3    v_sign number;  
PROCEDURE    4    v_empno emp.empno%type;  
PROCEDURE    5    v_ename emp.ename%type;  
PROCEDURE    6    v_deptno emp.deptno%type;  
PROCEDURE    7 begin  
PROCEDURE    8    select 1 into v_sign  
PROCEDURE    9    from dual  
PROCEDURE    10    where exists(select count(*) from emp where empno=empno_in);  
PROCEDURE    11    if v_sign=1 then  
PROCEDURE    12       select empno,ename,deptno into v_empno,v_ename,v_deptno  
PROCEDURE    13       from emp where empno=empno_in;  
PROCEDURE    14       dbms_output.put_line('information of'||empno_in||' are:');  
PROCEDURE    15       dbms_output.put_line('empno:'||v_empno||',ename:'||v_ename||',deptno:'||v_deptno);  
PROCEDURE    16    end if;  
PROCEDURE    17    exception  
PROCEDURE    18             when others then  
PROCEDURE    19             dbms_output.put_line('no data found');  
PROCEDURE    20 end show_employee;  
PROCEDURE    21  
PROCEDURE    22  
//  
//我们来执行一下show_employee这个存储过程:  
exec show_employee('7788');  
information of7788 are:  
empno:7788,ename:SCOTT,deptno:20  
PL/SQL procedure successfully completed