12-4. Writing and Executing PL/SQL
Problem
You want to use Oracle SQL Developer to execute an anonymous block of code.
Solution
Establish a connection to the database of your choice, and the SQL worksheet will automatically open. Once the worksheet has opened, you can type the code directly into it. For the purposes of this recipe, type or copy/paste the following anonymous block into a SQL worksheet:
DECLARE
CURSOR emp_cur IS
SELECT * FROM employees;
emp_rec emp_cur%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
END;
Figure 12-8 shows the Oracle SQL Developer worksheet after this anonymous block has been written into it.
Figure 12-8. Oracle SQL Developer worksheet with PL/SQL anonymous block
How It Works
By default, when you establish a connection within Oracle SQL Developer, a SQL worksheet for that connection is opened. This worksheet can be used to create anonymous code blocks, run SQL statements, and create PL/SQL code objects. The SQL worksheet is analogous to the SQL*Plus command prompt, although it does not allow all the same commands that are available using SQL*Plus.
If you want to open more than one SQL worksheet or a new worksheet for a connection, this can be done in various ways. You can right-click (Ctrl+click) the database connection of your choice and then select Open SQL Worksheet from the menu. Another way to open a new worksheet is to use the SQL Worksheet option within the Tools menu. This will allow you to specify the connection of your choice to open a worksheet against.
As you type, you will notice that the worksheet will place all Oracle keywords into a different color. This helps distinguish between keywords and defined variables or stored programs. By default, the keywords are placed into a bold blue text, but this color can be adjusted within the user Preferences window that can be accessed from the Tools drop-down menu. Similarly, any text placed within single quotes will appear in a different color. By default, this is also blue, except it is not bold.
Besides the syntax coloring, there are some other features of the SQL worksheet that can help make your programming life easier. Oracle SQL Developer will provide autocompletion for some SQL and PL/SQL statements. For instance, if you enter a package name and type a dot, all the package members will be displayed in the drop-down list. You can also press Ctrl+spacebar to manually activate the autocomplete drop-down list. After the drop-down list appears, you can use the arrow keys to choose the option you want to use and then hit the Tab key. Oracle SQL Developer provides similar autocompletion for table and column names and even SQL statement GROUP BY and ORDER BY clauses. Take a look at Figure 12-9 to see the autocomplete feature in action.
Figure 12-9. Autocomplete drop-down list
Another feature that helps productivity is to use Oracle SQL Developer snippets. To learn more about snippets, please see Recipe 12-7. Within the SQL worksheet toolbar, there is a group of buttons that can be used to help increase programmer productivity. The group of buttons at the far-right side of the toolbar contains a button for making highlighted words uppercase, lowercase, and initial-cap. The button that has an eraser on it can be used to quickly clear the SQL worksheet. There is also button that can be used to display the SQL History panel. This SQL History panel opens along the bottom of the Oracle SQL Developer environment, and it contains all the SQL that has been entered into the worksheet. Double-clicking any line of the history will automatically add that SQL to the current worksheet. Figure 12-10 shows the SQL History window.
Figure 12-10. SQL History window
To execute the SQL or PL/SQL that is contained within the script, you can use the first two toolbar icons. The first icon in the toolbar (as shown in Figure 12-8) is a green arrow will execute the code that is in the worksheet and display the result in a separate pane. The second icon in the toolbar (as shown in Figure 12-8) that resembles a piece of paper with a green arrow in front will execute the code within the worksheet and then display the output in a pane that can be saved as script output.
Note It is possible to have more than one SQL statement or PL/SQL block within the SQL worksheet at the same time. In doing so, only the highlighted code will be executed when the green arrow button is selected. If all the code is selected, then a separate output pane will appear for the output of each block or statement. However, if the Script icon (paper with green arrow) is selected, then all the highlighted code will have its output displayed in the resulting script output pane.
Other toolbar options within the SQL worksheet include the ability to COMMIT or ROLLBACK changes that are made, run an explain plan on the current code, or set up autotrace. The SQL worksheet is like SQL*Plus with many additional features. It provides the power of many tools in one easy-to-use environment.