Working with PL/SQL

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial demonstrates some of the advanced features within Oracle SQL Developer.

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Default connectivity to the database is through the JDBC Thin driver, so no Oracle Home is required. To install SQL Developer, simply unzip the downloaded file. SQL Developer users can connect to any supported Oracle Database.

Oracle SQL Developer 3.0 introduces a DBA Navigator and provides support for DBMS Scheduling, to name only two of many features in the latest release; other features include a new Query Builder, SQL Tuning Advisor, and Explain Plan Diff utility. For a complete list of new features, click here.

This tutorial takes you through a few of the more advanced features; use the extended search capabilities and refactoring code.

Prerequisites

Before starting this tutorial, you should:

Install Oracle SQL Developer 3.0 from OTN. Follow the release notes here.
Install the Oracle Database 11g.
Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command:
alter user hr identified by hr account unlock;
Download and unzip the files.zip file that contains all the files you need to perform this tutorial.

Setting up Your Environment

This tutorial requires a selection of users. To prepare the environment, you use SQL Developer to create a new user and a variety of connections. To complete the setup, you need to perform the following steps:

.	If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer session. If you do not have the icon located on your desktop, find the executable file (either named "sqldeveloper.exe" on Windows or "sqldeveloper.sh" on Linux), and run it.
.	Your Oracle SQL Developer application starts.
.	In the Connections navigator, right-click Connections and select New Connection.
.	Enter the following details and click Connect. Connection Name: hr_orcl Username: hr Password: <your_password> Check Save Password Hostname: localhost Port: 1521 SID: <your_orcl> Note: If you worked on another tutorial and you already have a hr_orcl connection you can skip this step.

Searching for Database Objects

You can find various types of objects (tables, columns, declarations within functions or procedures, and so on) associated with an Oracle database connection. After searching an object you can also open it in the editing pane and work with it. In Oracle SQL Developer Release, you can take advantage of the PLScope feature in Oracle Database 11g, which allows users to search for parameter declarations and references. To search for database objects, perform the following steps.

.	Select File > Open.
.	Select the proc_emp_cursor.sql file from the directory where you unzipped the files from the Prerequisites and click Open.
.	Select hr_orcl from the list of connections.
.	Click Run Script .
.	The procedure is created and compiles successfully.
.	Select View > Find DB Object.
.	In the Find Database Object window, select hr_orcl for Connection, enter c_emp_cursor for Name and click Lookup.
.	Note that no occurrences were found because the search was performed on object names, not the contents of the objects. Click More.
.	Now you can specify the Type of object and Usage for your search. When you perform the lookup, it will search in all types of database objects including PL/SQL procedures. Click Lookup.
.	The results are returned. All occurrences of c_emp_cursor are displayed. The name of the object where the text is found and the line on which it is found are also displayed.
.	Click one occurrence of c_emp_cursor to view and edit it in PL/SQL Editor.
.	You can also use wild cards while searching database objects. Change the Name to %emp_cursor and click Lookup.
.	All occurrences of the cursor c_emp_cursor and also the procedure proc_emp_cursor are displayed.
.	You can apply a filter on the search results. Select DECLARATION for Usage and click Lookup.
.	Only DECLARATION statements appear in the list.
.	You can also filter on the basis of Type. Change Usage back to All, enter %emp% for Name and select View for Type and click Lookup.
.	All the views with EMP in its name is displayed.

Refactoring Code

Redesigning or restructuring code to improve its performance without changing the resulting behavior is called Refactoring.

When your code increases and possibly becomes less manageable, it is advisable to rework or refactor it. SQL Developer 3.0 gives you refactoring options to extract a procedure, surround code with FOR, WHILE, or PL/SQL block constructs, and rename a local variable. Options like Surround with can also be used while you write your code.

In this topic, you will learn to use the Extract Procedure option by reworking a small piece of code. You will then use the Surround with option to write a new piece of code to use the extracted procedure.

Using the Extract Procedure Option

.	Close the open tabs and select the Connections navigator. To open the script file, select File > Open.
.	Browse to the location where you unzipped files.zip and open the update_employees.sql file.
.	Review the code. The input to the update_employees procedure is employee_id and percent. The procedure increments the employee's salary by the input percent value.
.	Select hr_orcl connection for the worksheet.
.	To extract a new procedure, select the UPDATE and DBMS_OUTPUT statements and right-click to invoke a context menu. Note: If you select an incomplete piece of code, and error message is displayed.
.	Select Refactoring > Extract Procedure....
.	A New Procedure dialog opens. Ensure that the Stored radio button and the Call with Named Arguments checkbox are selected. Note: If you do not see the Stored and Call with Named Arguments options, ensure that you performed step 4.
.	Enter raise_salary for Name and click OK.
.	A Confirm Running SQL dialog opens. Review the code and click Yes.
.	The procedure is created successfully and inserted in place of the existing code.
.	To view the procedure, expand the Procedures node. Note: If you do not see the new procedure, click Refresh .
.	The RAISE_SALARY procedure is created. The green color on the procedure icon specifies a refactored code. Select RAISE_SALARY.
.	To execute the procedure, click Run .
.	A Run PL/SQL dialog opens. Change the NULL values to 100 and 10 and click OK.
.	The procedure is executed successfully.

Using the Surround with Option

.	You need to rewrite the update_employees procedure to increment the salary of all employees who were hired before Jan '05. Click the update_employees.sql tab.
.	Copy and paste the following code before the BEGIN keyword. The code uses a cursor to store the EMPLOYEE_ID of the employees hired before Jan '05. CURSOR emp_cursor IS SELECT employee_id FROM employees where hire_date < '01-jan-05';
.	Delete the parameters of the procedure.
.	To call the raise_salary procedure for each EMPLOYEE_ID retrieved, you need to enclose the statement in a loop. Select the statement and right-click to invoke the context menu.
.	Select Refactoring > Surround with > FOR Loop....
.	The statement is inserted inside a FOR loop.
.	Type emp_cursor in place of 1..n.
.	Delete the existing parameters of the raise_salary procedure and insert the following: x.employee_id,10
.	To execute the code, click Run .
.	The procedure is compiled successfully. To view the procedure, select the Procedures node and click the Refresh icon.
.	Select UPDATE_EMPLOYEES.
.	To execute the procedure, click Run .
.	Click OK.
.	The procedure is executed successfully.