3.6.5 与存储程序有关的数据字典
在数据库中,存储过程、存储函数以及程序包的信息是存放在数据字典中的。与存储程序有关的数据字典有:
user_procedures
user_objects
user_source
user_errors
在数据字典user_procedures中存放的是当前用户所拥有的存储过程和存储函数信息。例如,要想查看当前用户所拥有的存储过程和存储函数,执行下面的SELECT语句:
- SQL>SELECT object_name,object_type, authid FROM user_procedures;
- OBJECT_NAME OBJECT_TYPE AUTHID
- EMPLOYEE PACKAGE DEFINER
- EMPLOYEE PACKAGE DEFINER
- TAX_PER_DEPART FUNCTION DEFINER
- TOTAL_INCOME PROCEDURE DEFINER
数据字典user_objects用来存放当前用户所拥有的所有类型的数据库对象,包括表、视图、触发器、序列、存储过程、存储函数以及程序包等。如果要了解当前用户所拥有的数据库对象类型,可以执行下面的SELECT语句:
- SQL> SELECT distinct object_type FROM user_objects;
- OBJECT_TYPE
- ------------------
- FUNCTION
- INDEX
- PACKAGE
- PACKAGE BODY
- PROCEDURE
- TABLE
从执行结果可以看出,在当前用户所拥有的模式中,有索引、表、存储过程、存储函数和程序包五种数据库对象。程序包的头部和包体的类型分别为PACKAGE和PACKAGE BODY。如果要查看某个数据库对象的详细信息,同样可以执行相应的SELECT语句。例如,以下SELECT语句用来查看对象“total_income”的详细信息:
- SQL> SELECT object_name,object_type,created,
status FROM user_objects- WHERE object_name='TOTAL_INCOME';
- OBJECT_NAM OBJECT_TYPE CREATED STATUS
- ---------- ------------------ ---------- -------
- TOTAL_INCOME FUNCTION 28-5月-10 VALID
数据字典user_source用来存放存储过程、存储函数和程序包的源代码。当然,这个视图的目的只是为了查看源代码,PL/SQL程序的执行并不是从这里开始的,因为程序在创建时已经经过了编译,在数据库中以二进制形式存储。因此,试图通过修改这个数据字典而达到修改存储程序的功能是行不通的。Oracle在创建PL/SQL程序时,将按照用户在编写时的自然格式,以行的形式存储程序代码,并记录每行的行号,所有代码行合起来就是该程序的源代码。例如,要查看函数tax_per_depart的源代码,可以执行下列SELECT语句:
- SQL> SELECT line,text FROM user_source WHERE name='TAX_PER_DEPART';
- LINE TEXT
- ---------- --------------------------------------------------
- 1 function tax_per_depart(dno integer)
- 2 RETURN number
- 3 as
- 4 result number;
- 5 BEGIN
- 6 SELECT sum(sal)*0.03 INTO result FROM emp
- 7 WHERE deptno=dno
- 8 GROUP BY deptno;
- 9 RETURN result;
- 10 END;
如果在创建存储过程、存储函数或者程序包时发生了语法错误,SQL*Plus将把错误信息在屏幕上显示,同时Oracle把错误信息记录在数据字典中。数据字典user_errors就是用来存放当前用户在创建存储程序时发生的错误的。例如,在创建存储函数total_income时,错把SELECT语句中的“WHERE deptno=dno”写成了“WHERE deptno=ddno”,于是发生了错误:
- CREATE OR REPLACE FUNCTION total_income(dno emp.deptno%type)
- RETURN number
- as
- result number;
- BEGIN
- SELECT sum(sal) INTO result FROM emp
- WHERE deptno=ddno
- GROUP BY deptno;
- RETURN result;
- END;
- SQL>/
- 警告: 创建的函数带有编译错误。
为了确定发生的所有错误的位置,执行下列查询语句:
- SQL> SELECT sequence,line,position FROM user_errors
- WHERE name='TOTAL_ INCOME';
- SEQUENCE LINE POSITION
- ------------------------------ ------------
---------- ---------- ----------- 1 7 14
- 2 6 1
可以看出,发生了两个错误,第一个位于第7行第14个字符处,第二个位于第6行第1个字符处。为了查看第一个错误的详细信息,需要检索TEXT列的数据:
- SQL> SELECT text FROM user_errors WHERE SEQUENCE=1;
- TEXT
- --------------------------------------------------------------------
- PLS-00103: 出现符号"END"在需要下列之一时:
- BEGIN case DECLARE exit for
- goto if loop mod null pragma raise RETURN SELECT UPDATE while
- with <an identifier> <a double-quoted delimited-identifier>
- <a bind variable> << close current DELETE fetch lock INSERT
- open rollback savepoint set sql execute commit forall merge
- <a single-quoted SQL string> pipe
- PL/SQL: ORA-00904: "DDNO": 无效的标识符
根据这些错误信息很快便可以确定错误的原因,从而进行纠正。在很多情况下,发生的多个错误是由同一个原因引起的,只要修改了出现错误的程序代码,多个错误可能一起消失。这需要用户在编写程序的过程中不断积累经验。
SQL*Plus还提供了一种查看错误信息的简便方法,用show errors命令可以查看当前发生的错误,而不需要了解数据字典的详细结构。这个命令的用法为:
- SQL>show errors
命令执行的结果为:
- FUNCTION TOTAL_INCOME 出现错误:
- LINE/COL ERROR
- -------- ----------------------------------------
- 6/1 PL/SQL: SQL Statement ignored
- 7/14 PL/SQL: ORA-00904: "DDNO": 无效的标识符
或者在查看错误信息时指定发生错误的对象的类型和名称,如:
- SQL>show errors function total_income
在这种情况下,命令show errors的格式为:
- SQL>show errors 对象类型 对象名称