[转]Advanced Oracle SQL Developer Features

本文转自：http://www.oracle.com/technetwork/cn/server-storage/linux/sqldev-adv-otn-092384.html

Advanced Oracle SQL Developer Features

< 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 more advanced features within Oracle SQL Developer.

Time to Complete

Approximately 50 minutes

Overview

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. Oracle SQL Developer also includes lesser known features, such as file based development, integrated version control, and an array of smaller features, such as code formatting and code insight, copy, export and compare. You can also browse your Oracle Application Express applications.

This tutorial takes you through a few of the more advanced features; you create a code template, use the extended search capabilities and use the schema copy features. For users working with Application Express there is a section on that too.

Developed in Java, Oracle SQL Developer runs on Windows, Linux and the Mac OS X. The default connectivity to the database is through the JDBC Thin driver so no Oracle Home is required. To install Oracle SQL Developer, simply unzip the downloaded file. With SQL Developer, users can connect to any supported Oracle Database, for all Oracle database editions including Express Edition.

Prerequisites

Before starting this tutorial, you should:

• Install Oracle SQL Developer 2.1 early adopter from OTN here. Follow the readme instructions here.
• Install the Oracle Database 10g and later.
• Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:     alter user hr identified by hr account unlock;
• Download and unzip the sqldev_adv.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:

.	Open Oracle SQL Developer.
.	In the Connections navigator, right-click Connections and select New Connection.
.	Create the system_orcl connection with the following details and click Test to verify the status.             Connection Name: system_orcl             Username: system             Password: <password> (The password is case sensitive in Oracle Database 11g.)             Hostname: localhost             Port: 1521             SID: orcl
.	The test was successful. Click Connect to connect and save the connection.
.	Expand the new system connection. Right-click Other Users and select Create User.
.	Enter the following and click the System Privileges tab. User name: test             Password test             Default Tablespace Users             Temporary Tablespace Temp
.	Select the following privileges and click Apply. CREATE PROCEDURE             CREATE SEQUENCE             CREATE SESSION             CREATE TABLE             CREATE TRIGGER             CREATE TYPE             CREATE VIEW             UNLIMITED TABLESPACE
.	Review the SQL that was executed. Click Close.
.	Create another connection for the test user. Right-click Connections and select New Connection.
.	Enter the following details and click Connect. Connection Name: test_orcl             Username: test             Password: test             Check Save Password             Hostname: localhost             Port: 1521             SID: orcl
.	Create one more connection for the hr user. Right-click Connections and select New Connection.
.	Enter the following details and click Connect. Connection Name: hr_orcl             Username: hr             Password: hr             Check Save Password             Hostname: localhost             Port: 1521             SID: orcl Note: If you worked on another tutorial and you already have a hr_orcl connection you can skip this step.

Exporting Objects

You can use the Export Wizard to export some or all database objects of a database connection to a file. The export file will not only contain the SQL data definition language (DDL) statements to create the exported objects, but if you choose to export the data too, the export file will contain Data Manipulation Language (DML) to populate the exported objects.

.	Select Tools > Database Export
.	Enter c:< directory where you unzipped the files from the Prerequisites>export.sql in the File field and select the hr_orcl connection.
.	Under the DDL options the Terminator and Pretty Print DDL options are selected by default. Review the rest of the options available, leaving them unchecked. Click Next.
.	If the export file exists, you receive a prompt to confirm overwriting. Click Yes.
.	The Types to Export window includes the Object Types you want to be exported from the hr_orcl connection. By default all the object types are selected. Click Toggle All check box to deselect all the object types.
.	Select only the object type Tables and then click Next. Note: You must select at least one object type.
.	Click Go to search all objects that meet the selection criteria you specified in the previous step.
.	All tables owned by hr are listed. Click the tables EMPLOYEES and DEPARTMENTS and then use the arrow key to move them to the list box on the right hand side of the window.
.	Click Next.
.	You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications. To create the output file, click Finish.
.	The file is also displayed in a SQL Worksheet window, where you can edit it and run it as a script. Select the test_orcl connection in the drop down list.
.	Click Run Script.
.	The script ran successfully. In the Connections navigator, expand the test_orcl connection.
.	Expand Tables. The DEPARTMENT and EMPLOYEES tables appear in the list.

Copying Schemas

You can use the Database Copy to copy objects directly from a source to a target schema. This is a quick and very useful way of copying all tables and data from one schema to another. To copy tables between two schemas perform the following steps:

.	Select Tools > Database Copy
.	You will copy the schema objects from the hr_orcl connection to the test_orcl connection. Select hr_orcl for Source Connection and test_orcl for Destination Connection and click Next.
.	Notice in the Copy Summary that all the tables in HR will be created in Test, even the EMPLOYEES and DEPARTMENTS. Click Finish.
.	The result of the comparison is displayed in a SchemaCopy log file in the SQL Developer window. Review the full results log. Do you expect the errors you find? Click Refresh to see the newly copied objects.
.	The newly copied objects are displayed.
.	Scroll down in the SchemaCopy log to see that you received an error when the CREATE TABLE "DEPARTMENTS" statement was run indicating that it already existed.

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 editing pane and work with it. In Oracle SQL Developer Release 1.5 and later, 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 the Run Script icon.
.	The procedure compiled 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. Click More.
.	Now when you perform the lookup, it will search in all types of database objects including PL/SQL procedures. Click Lookup.
.	Now there are occurrences of c_emp_cursor 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 %dept% for Name and select View for Type and click Lookup.
.	All the views with DEPT in its name and displayed.

Setting Preferences for the SQL Worksheet

You can customize many aspects of the SQL worksheet by modifying SQL Developer preferences according to your preferences and needs. To set preferences for the SQL Worksheet, perform the following steps:

Setting the Drag and Drop Effects

Drag and Drop Effects determine the type of SQL statement (select, insert, update or delete) created in the SQL Worksheet when you drag an object from the Connections navigator into the SQL Worksheet. By default, drag and drop from the navigator to the SQL Worksheet creates a new Select statement with all the columns in the table included. You can modify these settings.

To set the Drag and Drop Effects according to your preference, perform the following steps:

.	Select Tools > Preferences.
.	Expand Database and select Drag And Drop.
.	Change the Drag and Drop effects to Insert. Click OK.
.	In Connections navigator, right-click hr_orcl and select Open SQL Worksheet.
.	Expand hr_orcl > Tables. Select and drag the JOBS table to the SQL Worksheet area.
.	A dialog appears asking you what type of statement you want to create. Notice that the default is set to Insert which you just changed in your preferences. This dialog will appear because the check box Prompt every time is selected. Deselect the Prompt every time check box and click Apply.
.	An Insert statement for the JOBS table was created.

Setting Code Templates

You can create Code Templates for frequently used code. To create and use code templates, perform the following steps:

.	Select Tools > Preferences.
.	Select Database > SQL Editor Code Templates. You want to create a new template, click Add Template.
.	Click in the Id field and enter excep. Then click in the Template field for that row.
.	Click the Edit icon to add the code for this template.
.	Enter the following code that will be used when the code template is referenced. Then click Close. EXCEPTION             WHEN NO_DATA_FOUND THEN             DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
.	Click OK.
.	Expand Procedures and select PROC_EMP_CURSOR .
.	Type exc in a new line below END LOOP; Notice that the code template popup appears. Double-click on the code template.
.	Notice that the code template is inserted into the procedure. Select Compile from the drop down list box.
.	The procedure compiled successfully.

Integrating Application Express

Oracle SQL Developer provides an interface to Oracle Application Express applications and offers a number of useful activities to assist you when working with Oracle APEX. In this tutorial you perform the following operations:

Browsing an Application

SQL Developer provides a variety of ways of browsing Oracle APEX applications. In this section you browse the OEHR Sample Objects application, which is part of the "Getting Started with Oracle Application Express" online guide.To browse through your applications, perform the following steps:

.	Create a new database connection for the user HR_APEX. (If you still have a number of other tabs open from earlier sections, you can close those) In the Connections navigator, right-click Connections and select New Connection.
.	Enter the following details and click Connect. Connection Name: hr_apex_orcl             Username: hr_apex             Password: hr_apex             Hostname: localhost             Port: 1521             SID: orcl
.	In the Connections navigator, expand the Application Express node to display a list of all the applications owned by hr_apex schema.
.	Select Sample Application to display the application-level details, which includes Lists of Values, Lists, Templates, Tabs, etc.
.	Expand the Sample Application to see a list of the pages that make up the application.
.	Click the Master Detail page to display a detail tab with all the page-level details. This includes Regions, Items, Buttons, Processes, etc.
.	You can compare details of different pages or different applications. Click the Freeze View icon for the Master Detail page.
.	Select the Add Modify Customers page to open a new tab and display the details for that page.
.	Select the tab for the Add Modify Customers page and drag it below the Master Detail page and release. (As you drag you'll notice a blue box which shows the new positioning)
.	Click the Items tab for Add Modify Customers.
.	Then click the Items tab for the Master Detail page.
.	Now you can easily compare the values. When done reviewing, you can close both tabs.

Importing an Application

You can use SQL Developer to import Oracle Application Express applications. In this section you import the TIMESHEETS sample application. Oracle APEX provides a selection of packaged applications, which you can install and use out-of-the-box or modify. You can access these applications from the Oracle APEX homepage on OTN. (http://apex.oracle.com)

To import and browse an Oracle APEX application, perform the following steps:

.	In the Connection Navigator select the hr_apex connection, right-click the Application Express node and select Import Application.
.	In the dialog, click Browse to locate the file to import.
.	Select the timesheets_installer_1.0.sql file from the directory where you unzipped the files from the Prerequisites and click Select.
.	Click Next.
.	In the Choose Import Options dialog, check the Run Install Script option and accept the rest of the defaults. Then click Next.
.	Review the Results. Click Finish.
.	The import process will take a short while. Click OK to complete the install process.

Executing an Application Express Report

SQL Developer provides a selection of shipped reports. These reports include a selection of Application Express reports. To review some of the reports, perform the following steps::

.	Select the Reports tab.
.	Expand All Reports > Data Dictionary Reports > Application Express and review the available reports.
.	Click Applications. A Select Connection window appears, select hr_apex and click OK.
.	A report with details for each application is displayed. Click the Sample Application.
.	In the details report, select the LOV tab. Here you review the various Lists Of Values that exist with in the application.

Summary

In this tutorial, you have learned how to:

• Export objects
• Copy schemas
• Use extended search
• Set your preferences
• Integrate with Oracle Application Express