Oracle SQL Developer
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Creating, compiling, and debugging PL/SQL

You can use either the SQL Worksheet, or the PL/SQL Editor, to create and edit your PL/SQL code. You can think of the SQL Worksheet as a free format text editor where you can create anonymous PL/SQL blocks or more formal structured program units, such as procedures. Using the PL/SQL Editor, you can edit program units already in the database. In either case, SQL Developer provides you with a set of tools to help you create, edit, refactor, and debug your code. Coding assistants, such as code insight, code templates, or code snippets, are available in both the SQL Worksheet and the PL/SQL Code editors.

However, feedback on errors is only available in the PL/SQL Code editor. We'll start the section by reviewing some of the code editing options available by initially working in the SQL Worksheet with anonymous PL/SQL blocks.

Writing PL/SQL in the SQL Worksheet

The SQL Worksheet is an excellent scratch pad for working with SQL, SQL*Plus, and PL/SQL, and combinations of these. When starting to develop a new program unit, having access to pieces of previously written code is useful. Time-saving code completion means less typing or the need to remember the correct spelling of object names or columns in tables.

Using code insight

SQL Developer provides a facility called code insight that displays in a popup list of possible choices for the next value or statement in the code. Consider the following code:

DECLARE
 BONUS NUMBER;
 SAL NUMBER;
 COMM NUMBER;
 EMP_NAME VARCHAR2 (20);
BEGIN
 SELECT LAST_NAME, SALARY, COMMISSION_PCT
 INTO EMP_NAME, SAL, COMM
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID = 157;
 BONUS := (SAL * 0.10) + (COMM * 0.15);
 DBMS_OUTPUT.PUT_LINE('EMPLOYEE '|| EMP_NAME || ' EARNS A NEW BONUS OF ' || BONUS );
END;

If you use the SQL Worksheet and start entering that code, a short list of values is displayed soon after you type BONUS, as shown in the following screenshot:

You can tab down, select NUMBER, and you're done. You can also type N and the list changes, in general revealing more possible options, thus revealing that the initial selection contained a shortened list of the most commonly used choices (in this case, displaying only NUMBER).

You can also choose to type any other letter and change the list of choices altogether. The list of choices here includes schema names, tables, columns, variable declarations, and procedure or function calls.

Each selection from the list reduces and changes the next list displayed. For example, take a package with procedures included. If you start typing the package name, the code insight feature provides a list of all packages with those initial letters. Once you have the package entered, typing the "." invokes the list of public procedures and functions available within the package. In the example that follows, the package and procedure are already in place and the code insight now reveals the parameters and data types required for input:

For more detail on controlling or switching off code insight, you can navigate to Tools | Preferences | Code Editor | Completion Insight.

Using code snippets and code templates

Code snippets are useful as they provide the framework for larger or smaller chunks of code. By navigating to View | Snippets, we can open the Snippets pane. There are PL/SQL snippets under the category PL/SQL Programming Techniques, in the Snippets window (shown in the following screenshot), which provide a useful selection of PL/SQL constructs.

In the preceding example, both the Snippets window and the resulting code skeleton, created here by dragging the Cursor snippet onto the SQL Worksheet, are shown. You can extend the selection of snippets provided by adding your own.

Code templates are very similar in concept, except that SQL Developer only provides a few default code templates. Instead, you have a blank canvas in which you need to add your own. You invoke code templates by entering the first few letters of the template, which displays the code using code insight. Code templates are useful for frequently used chunks of complete code, such as exception handlers.

For the add, edit, and remove code templates, we can navigate to Tools | Preferences and expand the Database node in the left pane of the Preferences dialog box, as shown in the following screenshot:

Working with triggers

SQL Developer supports creating, editing, and debugging PL/SQL program units. Triggers, Functions, Procedures, and Packages are listed in the Connections navigator, and you can view the details of these by selecting and clicking on each to open the display editors. In this section, we'll look at each of these constructs in turn and at the dialogs and utilities associated with them. Oracle Database triggers fire when you manipulate or update data, update database objects (such as dropping a table), or on certain database events (such as logging on). SQL Developer provides a number of utilities and dialogs to simplify creating and working with triggers.

Using the Create Trigger dialog

You can use the SQL Developer worksheet to write the PL/SQL code for the complete database trigger, without having the formal or, if you know the trigger structure very well, perhaps confining restrictions of a dialog. However, if you are a bit unfamiliar with creating triggers, then the Create Trigger dialog can be really useful by providing the structure for the DML event clause and any condition details.

Consider the following example:

CREATE OR REPLACE
TRIGGER TRACK_SALARY_HISTORY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.EMPLOYEE_ID >0)
DECLARE SAL_DIFF NUMBER;
BEGIN
 SAL_DIFF := :NEW.SALARY - :OLD.SALARY;
 INSERT INTO SALARY_HISTORY (EMP_ID, NEW_SAL, OLD_SAL, INCREASE, UPDATED_ON)
 VALUES (:NEW.EMPLOYEE_ID,:NEW.SALARY, :OLD.SALARY, SAL_DIFF, SYSDATE);
END;

By providing checklists, radio groups, and drop-down lists, the dialog shown in the following screenshot helps you create most of the structure of the trigger:

Once you have completed the dialog, all that is left for you to add are any variable declarations and the body of the PL/SQL block.

The Create Trigger dialog also has the advantage of setting a context, so that certain sections of the dialog are only editable for set conditions. For example, if you are creating a trigger that fires before or after INSERT or DELETE, then it is not relevant to select specific columns since these actions affect the entire record. However, you may want a trigger to fire on UPDATE of a specific column. In this case, when you select the Update option, the Selected and Available Columns fields are enabled. It is also not relevant to add a When clause to a Statement Level trigger, so this item is only enabled for Row Level triggers.

Once you have completed the details in the Trigger tab of the dialog, you can select the DDL tab to review the code that will be executed, and then click on OK to create the trigger. You need to add the PL/SQL trigger body and any additional declarations required, and then compile the code. For this example, before you can compile the code, you need the SALARY_HISTORY table. To compile the code, select the Compile button:

Once a trigger has been compiled, you can test the code by performing the various operations that cause it to fire. In this case, it should fire on inserting a new record, but not on delete. It should fire when updating the salary, but not for updating any other column values. In each case, the auditing table, SALARY_HISTORY is updated.

Creating INSTEAD OF triggers

The Create Trigger dialog supports creating advanced triggers, which are fired not only on table insert, update, and delete events, but also on these events for views. If a view is updateable, it means you can update the underlying table, or tables, by executing insert, update, or delete statements against the view. In general, complex views are not updateable, meaning that you can't use DML statements directly against the view to update the underlying table or tables. In this case, you can use INSTEAD OF triggers. When using the Create Trigger dialog, switching the trigger type from TABLE to VIEW causes the dialog to change to support the INSTEAD OF trigger.

In the previous screenshot, the trigger type of VIEW was selected, producing the skeleton shown. Once created, INSTEAD OF triggers are listed with other triggers in the Triggers node and can also be found in the triggers display for the views they are associated with.

Controlling triggers

SQL Developer provides a number of context-sensitive menus which provide a quick route to enabling, disabling, and compiling triggers. Select any trigger in the navigator and invoke the context menu, as shown in the following screenshot:

You can also apply global updates to triggers by invoking the context menu selection for tables. This displays a trigger menu that includes the Enable All and Disable All options.

Adding functions or procedures

You can create functions or procedures using the Create New Function or Create New Procedure dialogs, or by using the SQL Worksheet. The dialogs for creating functions and procedures are very basic, providing only the structure for naming and adding parameters. For functions, a default RETURN parameter is also defined:

The main content, the body, still needs to be written, but the dialog creates the framework, making it easier to get started. If you have no input parameters and only the return value, you can do this as easily in the SQL Worksheet by just typing the text. The advantage of using the dialog is that the skeleton code is opened in the PL/SQL code editor and you can get the immediate added benefit of the compiler messages. In the examples that follow, we use functions to illustrate the point, but the detail is pertinent to procedures too. For the most part, we'll refer to subprograms to include both functions and procedures.

Consider the following piece of code:

CREATE OR REPLACE
FUNCTION GET_SALARY (EMP_ID NUMBER) RETURN NUMBER IS
 CURR_SAL NUMBER;
BEGIN
 SELECT SALARY INTO CURR_SAL FROM EMPLOYEES
 WHERE EMPLOYEE_ID = EMP_ID;
 RETURN CURR_SAL;
END GET_SALARY;

Whether you used the SQL Worksheet or the Create Function dialog, once you have created the skeleton, open it in the PL/SQL code editor. If you have used the SQL Worksheet, once you execute the code to create the function, refresh the Functions node in the navigator, and click on the name of the new function. By way of example, enter the following piece of code in the SQL Worksheet, and press F5 to run the code:

CREATE OR REPLACE
FUNCTION GET_SALARY(EMP_ID NUMBER)
 RETURN NUMBER
 AS
 BEGIN
 RETURN NULL;
END GET_SALARY;

To invoke the code editor (shown in the following screenshot), click on the function name in the navigator and select Code.

Working with errors

The Connections navigator displays all packages, procedures, and functions the user owns, including those that are currently compiled with errors. A red cross overlaying the object icon indicates that the object is compiled with errors. In the following screenshot, the procedure EMP_ACTIONS package body has errors, while the ADD_JOB_HISTORY function has none. Errors may be syntactical, or might have errors if dependencies have been removed (for example, if a table that the function relies on has been dropped).

The other area to look for compile-time errors is in the editor itself. Once you have compiled a program, the errors are marked:

  • Within the code, using a line under the code.
  • In the right-hand margin. You can see each message by rolling the mouse over the point in the margin.

In the following screenshot, we have shown all the elements:

Note

There is a database hard limit of 20 messages displayed in the log window, including both warnings and errors. If you have 20 warnings before the first error is encountered, you will not see the error messages. Oracle Database 11g Release 2 has changed this to 20 errors and unlimited warnings.

Handling multiple error messages

You can control the type of PL/SQL warning messages that are displayed using Preferences. Navigate to Database | PL/SQL Compiler. The settings control whether the types of warnings are displayed or suppressed, and whether they are treated as errors.

Creating packages

Creating packages requires a specification and body to be created. In this case, the new dialog for the package specification only serves to provide the skeleton, requiring only a name as an input value. Once you click on OK, the package specification opens in the PL/SQL Code editor and you need to enter all of the required declarations. The following screenshot shows the very basic skeleton created by the Create PL/SQL Package dialog:

To complete the specification, you need to manually enter the code, such as declaring the public variables, procedures, and functions as needed. We added the following highlighted code to the example:

CREATE OR REPLACE
PACKAGE EMP_PROGUNITS AS
 TYPE EMPRECTYP IS
 RECORD (EMP_ID INT, SALARY REAL);
 CURSOR DESC_SALARY
 RETURN EMPRECTYP;
 PROCEDURE HIRE_EMPLOYEE
 (LAST_NAME VARCHAR2, JOB_ID VARCHAR2,
 MANAGER_ID NUMBER, SALARY NUMBER,
 DEPARTMENT_ID NUMBER);
 PROCEDURE FIRE_EMPLOYEE (EMP_ID NUMBER);
 PROCEDURE ADD_DEPT
 (DEPARTMENT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%TYPE,
 LOCATION_ID IN DEPARTMENTS.LOCATION_ID%TYPE);
END EMP_PROGUNITS;

Creating the body

Once you have provided all of the package declarations, using the Code editor, you can use the new package body dialog to create the skeleton. Of all the PL/SQL Create dialogs, this is the most useful. It provides skeletons in the package body for each of the procedures or functions declared in the package specification.

To automatically create the body, select the package specification, invoke the context menu, and select Create Body… to invoke the new package body dialog:

Tip

Creating a package body

Save time setting up the package body by defining all of the procedure and function declarations in the package specification, and then using the Create Body context menu to automatically create a skeleton of procedures and functions.

Refactoring code

The utilities discussed in this section can once again be used in either the SQL Worksheet or the Code editor. While all are not necessarily confined to PL/SQL, such as the Find DB Object search utility, it is useful to discuss them here.

Refactoring code is the process of redesigning the code to restructure it or improve the performance, without impacting the resulting behavior. SQL Developer supports a number of refactoring options, which include:

  • Surrounding the code with the following PL/SQL constructs
    • The FOR loop
    • The WHILE loop
    • The PL/SQL block
  • Extracting a procedure
  • Renaming a local variable

Some of these can assist you while writing your code, such as Surround with…, and so are as much about being productive as they are about refactoring. One feature that is useful when refactoring is Extract Procedure….

When you are creating PL/SQL code, it is often recommended that you create chunks of code that you can call from another program unit. This introduces reusability and readability to your code. In particular, it is recommended that you keep the lines of code in your triggers to a minimum, calling procedures from the trigger, instead of writing the full text in the trigger itself. Often, at the time of writing, you don't see the need for this chunking, nor does it seem to be necessary. However, as the code grows and becomes possibly less manageable, it is advisable to return to the code and rework or refactor it.

Consider the trigger, which is part of the HR schema, as shown in the following code:

CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES
 BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
BEGIN
 IF TO_CHAR (SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
 OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
 RAISE_APPLICATION_ERROR (-20205, 'YOU MAY ONLY MAKE CHANGES DURING NORMAL OFFICE HOURS');
 END IF;
END SECURE_EMPLOYEES;

This is not a large piece of code in the body of the trigger. We'll use it to illustrate the refactoring capabilities in SQL Developer. Select and highlight the code that can stand alone as a procedure, invoke the context menu, and navigate to Refactoring | Extract Procedure…:

If you have selected an incomplete piece of code, an error message will display stating that you have not selected a complete set of PL/SQL statements. If the code is acceptable, you are shown a dialog where you can provide the name of your new procedure:

Once you have provided a name for the new procedure, a new editable screen is displayed (shown in the following screenshot), allowing you to modify the procedure before you accept the code. You can of course just accept the code straightaway and make any additional modifications in the code editor. However, the intermediate code window allows you to review the code before accepting the changes. This is particularly useful if you've selected too much or too little code to refactor. Until you have accepted the dialog, the underlying trigger or procedure is not affected.

Once you have accepted the new procedure, the refactored trigger now includes the call to the new procedure, replacing the chunk of code, and the new procedure joins the others in the list of available procedures in the Connections navigator:

Searching for code

SQL Developer provides a number of utilities to search for strings of text. To search for strings within a piece of code, use the menu choice Find or Replace. There are also Incremental Find Forward and Incremental Find Backward to search for strings. Often, developers want to search for tables within a schema or across schemas or for PL/SQL declarations. The Find DB Object mechanism provides this facility.

Finding a DB Object

You can use the Find DB Object feature, on the View menu, to look for object types such as tables, views, and procedures in all databases supported by SQL Developer. The Find DB Object and Extended Search have been incorporated into a single search dialog. Therefore, you are able to search for objects and to drill down and search for variable declarations and references. To search for variable declarations, you need to be connected to Oracle Database 11g or above, as this search mechanism takes advantage of the PL/Scope facility that was introduced in Oracle Database 11g.

If you have access to Oracle Database 11g, then you can click on the More… button on the Find Database Object dialog (shown in the previous screenshot). This extends the dialog to include the Type and Usage options, which allow you to drill into the PL/SQL code units. Once you complete the search, select the required return links and the program unit is invoked in the Code editor in the background, with the cursor highlighting the point of reference.

Debugging PL/SQL code

Once your PL/SQL code has successfully compiled, it is important to review it to make sure it does what is required to do and that it performs well. You can consider a number of approaches when tuning and testing code. These approaches include:

  • Debugging: Run the code and add break points to stop and inspect areas of concern.
  • SQL performance: Use Explain Plan results to review the performance.
  • PL/SQL performance: Use the PL/SQL Hierarchical Profiler to identify bottlenecks.
  • Unit testing: Review edge cases and general function testing. Does the code do what you intended it to do?

In this section, we'll review the debugger. SQL and PL/SQL code may execute cleanly, and even produce an output. PL/SQL code may compile and produce results, but this is part of the task. Does it do what you are expecting it to do? Are the results accurate? Does it behave as expected for high and low values, odd dates, or names? Does it behave the same way when it's called from within a program as it does when tested in isolation? Does it perform as well for massive sets of data as it does for a small test case? All of these are aspects to consider when testing code, and many can been tracked by debugging the code.

Using the debugging mechanism in SQL Developer

Once again, you need a piece of compiled, working code. For this exercise, we will use the following piece of code:

CREATE OR REPLACE PROCEDURE EMP_DEPTS
 (P_MAXROWS VARCHAR2)
AS
CURSOR EMPDEPT_CURSOR IS
SELECT D.DEPARTMENT_NAME, E.LAST_NAME, J.JOB_TITLE
FROM DEPARTMENTS D, EMPLOYEES E, JOBS J
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
 AND E.JOB_ID = J.JOB_ID;
EMP_RECORD EMPDEPT_CURSOR % ROWTYPE;
TYPE EMP_TAB_TYPE IS TABLE OF EMPDEPT_CURSOR % ROWTYPE INDEX BY BINARY_INTEGER;
EMP_TAB EMP_TAB_TYPE;
I NUMBER := 1;
BEGIN
 OPEN EMPDEPT_CURSOR;
 FETCH EMPDEPT_CURSOR
 INTO EMP_RECORD;
 EMP_TAB(I) := EMP_RECORD;
 WHILE((EMPDEPT_CURSOR % FOUND) AND(I <= P_MAXROWS))
 LOOP I := I + 1;
 FETCH EMPDEPT_CURSOR
 INTO EMP_RECORD;
 EMP_TAB(I) := EMP_RECORD;
 END LOOP;
 CLOSE EMPDEPT_CURSOR; FOR J IN REVERSE 1 .. I
 LOOP DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE '|| EMP_TAB(J).LAST_NAME || ' WORKS IN DEPARTMENT '|| EMP_TAB(J).DEPARTMENT_NAME);
 END LOOP;
END;

Before you can debug code, you need to have the following privileges:

  • EXECUTE and DEBUG: You need to be able to execute the required procedure
  • DEBUG CONNECT SESSION: This is used in order to be able to debug procedures you execute in the session

Note, when granting the system privilege DEBUG ANY PROCEDURE, you are granting access to debug any procedure that you have execute privilege for and has been compiled for debug.

Using the Oracle debugging packages

Oracle provides two packages for debugging PL/SQL code. The first, DBMS_DEBUG, was introduced in Oracle 8i and is not used by newer IDEs. The second, DBMS_DEBUG_JWP, was introduced in Oracle 9i Release 2, and is used in SQL Developer when debugging subprograms.

Debugging

When preparing to debug any code, you need to set at least one breakpoint, and then you should select Compile for Debug. In the following screenshot, the breakpoint is set at the opening of the cursor, and the Compile for Debug option is shown in the drop-down list in the following screenshot:

Note

Instead of using the drop-down list to select the Compile or Compile for Debug options, just click on the Compile button. This compiles the PL/SQL code using the optimization level set in the Preferences. Navigate to Database | PL/SQL Compiler. By setting the Optimization Level preference to 0 or 1, the PL/SQL is compiled with debugging information.

Any PL/SQL code that has been compiled for debugging will show the little green bug overlaying the regular icon in the Connections navigator. The next screenshot shows that the EMP_DEPTS procedure and the GET_SALARY function have both been compiled for debug:

Tip

Compile for debug

Once you have completed a debugging session, be sure to compile again afterwards to remove any debug compiler directives. While negligible, omitting this step can have a performance impact on the PL/SQL program.

You are now ready to debug. To debug, click on the Debug button in the toolbar. SQL Developer then sets the sessions to a debug session and issues the command DBMS_DEBUG_JDWP.CONNECT_TCP (hostname, port), and sets up the debug windows, as shown in the following screenshot:

This connects you to a debugger session in the database. In some instances, the port selected is not open, due to firewall or other restrictions. In this case, you can have the SQL Developer prompt for the port. To set this option, open the Preferences dialog, and select the Debugger node. You can also specify the port range available for SQL Developer to use. These options mean that you can have more control over the ports used.

Working with Explain Plan

Oracle puts together a sequence of operations, called an execution plan, required to run SQL statements. Working specifically with DML statements SELECT, INSERT, UPDATE, and DELETE, the EXPLAIN PLAN statement displays the execution plans prepared by the optimizer and inserts rows into a global temporary table called the PLAN_TABLE. If you use SQL*Plus, you need to write queries to display the execution plans in the PLAN_TABLE . However, if you use SQL Developer, you need to only use the Explain Plan button to output the execution plan report below your query.

If you are new to these execution plans, then write a few basic queries and see the impact the query has on the output. In the first screenshot, we have used a simple SELECT statement, SELECT * FROM EMPLOYEES;. Click on the Explain Plan button, or press F10, to show the results. Notice that this is a full table scan, which makes sense as you have selected all columns and all rows from the table:

A full table scan has a high impact on resources, and is not advisable for large tables. More often than not, we want to look at a restricted set of records, and so we need to learn to write queries that restrict the records returned. A simple WHERE clause restricts records, but even that can be written more efficiently depending on the search conditions provided. If we restrict the query as shown in the next query, the Explain Plan output differs considerably:

In this case, you see the indexes are now being used and that the index on the DEPARTMENT_ID column is used in a range scan.

Controlling the Explain Plan output

SQL Developer provides options for controlling what is displayed in the Explain Plan output. In the previous screenshot, the predicate detail is shown in its own column, but on switching the preference and selecting the Predicates option, the details are included in the tree as shown in the following screenshot:

To control the detail in the Explain Plan report, use the Tools | Preferences menu to invoke the Preferences dialog, and then navigate to Database | Autotrace/Explain Plan and select or deselect the settings as required:

Execution plan details

Depending on which preferences you have selected, the Explain Plan report in SQL Developer displays the following details:

  • Operation: This part of the execution plan tree is always displayed and lists the method of access being used. This includes table access, or sort operations, and includes the join methods if used in the statement.
  • Object Name: This includes the tables referenced in the statement.
  • Options: This is the access method used, whether it's a full or range scan, for example.
  • Cost: This is used to optimize the query and is the relative cost of the operation based on a number of factors, such as initialization parameters, bind variable types if used, or statistics, if calculated for the tables.
  • Predicates: This lists the predicates used in the query and by the statement.

Using SQL reports

In the previous section, we looked at SQL statements and at how using the Explain Plan output can assist you when you are learning to write queries that perform better. It is good to try to write sound queries from the outset, but more often than not, what happens is that users come back to the developers complaining about performance. In this case, there is no sense in starting to wade through all of the code in the application, instead you need to determine which queries have the greatest impact on the system, such as the top running SQL statements. Once you have determined where the offending code is, you can review the execution plans for each of these and then work at reducing the impact these have on the system by improving the statements.

SQL Developer provides a number of packaged reports that you can use when tuning your queries. You can start by navigating to Data Dictionary Reports | Table | Statistics. These are simple reports, which report when the table was last analyzed and at the number of rows per table.

In the following example, the new CUSTOMERS_PART table has been analyzed and the row count of each of the tables in the schema is displayed. Knowing which are the smaller tables in a system is useful when writing queries that join multiple tables.

Running the Top SQL reports

In the packaged reports are six reports that look at the top SQL statements executed in the system. These include all of the SQL statements that are being executed and can be quite daunting when you first run the report. You can find these reports by navigating to Data Dictionary Reports | Database Administration | Top SQL.

If you select Top SQL by CPU, the output might look a little like the one we have illustrated in the following screenshot:

SQL Developer also provides a selection of ASH (Active Session History) and AWR (Automatic Workload Repository) reports. Statistical details useful for diagnosing performance issues are stored in the AWR and the Automatic Database Diagnostic Monitor (ADDM) analyzes this data. To review the SQL Developer reports, navigate to Data Dictionary Reports | ASH and AWR:

Tip

ASH, AWR, and ADDM are part of the Oracle Diagnostic Pack, an Oracle Database 11g Cost Option, and as such are an additional cost.

上一章目录下一章