oracle代码换算字典里的,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