学步园

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