Fehlermeldungen und Test
Werbung
Organisation gespeicherter
PL/SQL Objekte Data Dictionary
General
information
Testen von
Unterprogrammen
Source code
Editor
Parameters
Compile
errors
p-code
Debug
information
SQL> describe ...
DBMS_OUTPUT
Copyright © Oracle Corporation, 1998. All rights reserved.
USER_OBJECTS
8-2
*
Spalte
Spaltenbeschreibung
OBJECT_NAME
Name des Objekts
OBJECT_ID
Interner identifier des Objekts
OBJECT_TYPE
Typ des Objekts, z. B. TABLE,
PROCEDURE, FUNCTION, PACKAGE,
PACKAGE BODY
CREATED
Datum der Erstellung
LAST_DDL_TIME
Datum der letzten Änderung
TIMESTAMP
Datum und Zeit der letzten Recompilierung
STATUS
VALID or INVALID
8-3
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Liste aller Procedures und Functions
SQL>SELECT
2 FROM
3 WHERE
4 ORDER BY
5 /
object_name, object_type
user_objects
object_type in ('PROCEDURE', 'FUNCTION')
object_name
OBJECT_NAME
----------------ADD_DEPT
ADD_ONE
FIRE_EMP
GET_SAL
HIRE_EMP
LOG_EXECUTION
PROCESS_EMP
QUERY_EMP
REMOVE_AND_PROCESS_EMP
REMOVE_DEPT
8-4
OBJECT_TYPE
--------------------PROCEDURE
PROCEDURE
PROCEDURE
FUNCTION
PROCEDURE
PROCEDURE
PROCEDURE
PROCEDURE
PROCEDURE
PROCEDURE
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
USER_SOURCE
Spalte
Spaltenbeschreibung
NAME
Name des Objekts
TYPE
Typ des Objekts, z. B. PROCEDURE,
FUNCTION, PACKAGE, PACKAGE BODY
LINE
Zeilennummer im Quellcode
TEXT
Textzeile des Quellcodes
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
8-5
Liste von Übersetzungsfehlern
mit SHOW ERRORS
SQL>SHOW ERRORS PROCEDURE log_execution
Errors for PROCEDURE LOG_EXECUTION:
LINE/COL
------3/7
ERROR
------------------------------------PLS-00103: Encountered the symbol “INTO” when expecting one of
the following:
;= . ( @ % ;
Replacing “INTO” with “:=“.
4/1
PLS-00203: Encountered the symbol “VALUES” when expecting one
of the following:
. ( * % & = - + ; < / > in mod not rem an exponent (**)
<> or != or ~= >= <= <> and or like between etc.
Replacing “VALUES” with “(“.
4/23
PLS-100103: Encountered the symbol “;” when expecting one of
the following:
) , * & ⎮ = − + < / > in mod not rem => ..
an exponent (**) <> or != ~= >= <= <> and or like between etc.
) was inserted before “;” to continue.
8-7
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Code von Prozeduren und
Funktionen
SQL>
2
3
4
SELECT
FROM
WHERE
ORDER BY
text
user_source
name = 'QUERY_EMP'
line;
TEXT
-------------------------------------------------PROCEDURE QUERY_EMP
(v_empno
IN
emp.empno%TYPE,
v_name_job
OUT
VARCHAR2,
v_salary
OUT
emp.sal%TYPE,
v_commission OUT
emp.comm%TYPE)
IS
BEGIN
SELECT ename ||',' ||job, sal, comm
INTO
v_name_job, v_salary, v_commission
FROM EMP
WHERE empno = v_empno;
END query_emp;
8-6
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beschreibung von PL/SQL
Objekten in SQL*Plus
SQL>DESCRIBE HIRE_EMP
PROCEDURE HIRE_EMP
Argument Name Type
In/Out
------------------------- ------V_ENAME VARCHAR2(25)
IN
V_MANAGER_ID NUMBER(7)
IN
V_TITLE VARCHAR2(25)
IN
V_SALARY NUMBER(11,2)
IN
V_COMMISSION NUMBER(4,2) IN
SQL>DESCRIBE ADD_DEPT
PROCEDURE ADD_DEPT
Argument Name Type
------------------------V_NAME
VARCHAR2(25)
V_REGION_ID NUMBER(7)
8-8
Default?
--------
In/Out Default?
------ -------IN
DEFAULT
IN
DEFAULT
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beschreibung von PL/SQL
Objekten in SQL*Plus
SQL>DESCRIBE GET_SAL
FUNCTION GET_SAL RETURNS NUMBER
Argument Name Type
In/Out
-------- ------------ ---V_ID
NUMBER(7) IN
Abhängigkeiten
abhängige Objekte
referenzierte Objekte
Table
Default?
--------
View
View
Procedure
Sequence
Function
Synonym
Package Specification
Procedure
Package Body
Function
Database Trigger
Package Specification
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
8-9
8-10
Direkte Abhängigkeit
Procedure
Indirekte Abhängigkeit
Table
Procedure
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
Abhängiges
Objekt
8-11
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
View/Procedure
Table
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
xxxxxxxxxxxxxx
vvvvvvvvvvvvvv
Referenziertes
Objekt
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Abhängiges
Objekt
8-12
Abhängiges
Referenziertes
Referenziertes
Objekt
Objekt
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Gebrauch von DBMS_OUTPUT
1. SERVEROUTPUT einschalten
Überprüfen einer Stored Procedure
mit DBMS_OUTPUT
SQL> SET SERVEROUTPUT ON
2. Text in den Puffer bringen
SQL> EXECUTE DBMS_OUTPUT.PUT ('testing 1 2 3')
PL/SQL procedure successfully completed.
3. Pufferinhalt anschauen
SQL> EXECUTE DBMS_OUTPUT.NEW_LINE
testing 1 2 3
PL/SQL procedure successfully completed.
oder
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE
('testing 1 2 3 testing 4 5 6')
testing 1 2 3 testing 4 5 6
PL/SQL procedure successfully completed.
8-13
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Kontrollausgaben
Beispiel für Ausgaben der vorigen Prozedur
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE remove_and_process_emp (22)
no of employees in dept 44 is 2
as there is more than 1 person left in the dept,
fire_emp only, will be fired.
PL/SQL procedure successfully completed.
8-15
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
CREATE OR REPLACE PROCEDURE remove_and_process_emp
(v_id IN s_emp.id%TYPE)
IS
v_no_of_employees NUMBER; v_dept__id s_emp.dept_id%TYPE;
BEGIN
....../* SELECT statement to count the number of employees */ ......
dbms_output.put_line('no of employees in dept '⎮⎮ v_dept_id ⎮⎮ ' is '
⎮⎮ v_no_of_employees);
IF v_no_of_employees = 1 THEN
dbms_output.put_line('As there is only 1 person left in the dept.');
dbms_output.put_line('fire_emp then remove department');
fire_emp (v_id);
remove_dept (v_dept_id);
ELSE
dbms_output.put_line('As there is more than 1 person left in the dept, ');
dbms_output.put_line('fire_emp only, will be fired');
fire_emp (v_id);
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20200, 'Employee does not exist.');
END remove_and_process_emp;
8-14
Basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
