怎么看oracle的procedure,Oracle基础 -- SQLPlus如何查看procedure的内容

desc procedure可以看到procedure的声明。

那如果要查看procudre的内容呢？

solution：

select text from all_source where name = ‘ABC_GET_CMF' and OWNER='CWUK_YY' order by line;

PROCEDURE abc_get_cmf(

v_account_no                          NUMBER,

abc_get_cmf_cv IN OUT cv_types.customer_tp)

IS

BEGIN

OPEN abc_get_cmf_cv FOR

SELECT

no_bill,

CMF.currency_code,

prev_bill_date

FROM CMF

WHERE CMF.account_no = v_account_no；

END;

14 rows selected.

我们可以看下all_source这个表的各个字段

SQL> desc  all_source;

Name                                                                    Null?    Type

----------------------------------------------------------------------- -------- ------------------------------------------------

OWNER                                                                            VARCHAR2(30)

NAME                                                                             VARCHAR2(30)

TYPE                                                                             VARCHAR2(12)

LINE                                                                             NUMBER

TEXT                                                                             VARCHAR2(4000)

SQL> select distinct type from all_source;

TYPE

------------

TYPE BODY

PROCEDURE

LIBRARY

TRIGGER

JAVA SOURCE

PACKAGE

TYPE

FUNCTION

PACKAGE BODY

ALL_SOURCE describes the text source of the stored objects accessible to the current user.

Related Views

DBA_SOURCE describes the text source of all stored objects in the database.

USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNERcolumn.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the object

NAME

VARCHAR2(30)

NOT NULL

Name of the object

TYPE

VARCHAR2(12)

Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER,TYPE, TYPE BODY

LINE

NUMBER

NOT NULL

Line number of this line of source

TEXT

VARCHAR2(4000)

Text source of the stored object

useful link： https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2063.htm#i1588578