文章內(nèi)容

Oracle 定義對象的代碼可以從user_source數(shù)據(jù)字典中找到

發(fā)布時間: 2012/8/21 16:45:49

//在Oracle中，我們可以從數(shù)據(jù)字典user_source(視圖)中查看對象定義代碼;
//我們先來看user_source視圖的結(jié)構(gòu)：
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,
//現(xiàn)在我們來看其定義代碼,注意，傳遞的參數(shù)要大寫:
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
//
//我們來執(zhí)行一下show_employee這個存儲過程:
exec show_employee('7788');
information of7788 are:
empno:7788,ename:SCOTT,deptno:20
PL/SQL procedure successfully completed