PL/SQL Programming   «Prev  Next»

Lesson 2Introducing PL/SQL
ObjectiveDescribe some of the Common Tools used to develop PL/SQL.

Describe Common Tools used to develop PL/SQL

The standard (PL) Procedural Language for Structured Query Language (SQL, pronounced sequel) is referred to as PL/SQL.
The language bridges the gap between database technology and procedural programming language. Basically an application development tool, PL/SQL uses the facilities of the sophisticated Oracle (RDBMS) relational database management system and extends the standard SQL database language.
PL/SQL is an extension to SQL. Thus, PL/SQL permits the use of all SQL data manipulation statements, including
  1. INSERT,
  2. UPDATE,
  3. DELETE, and
  4. SELECT,
as well as cursor operations and the transaction processing statements COMMIT, ROLLBACK, and SAVEPOINT. You have two choices for native tools in Oracle to develop your PL/SQL: SQL*Plus and SQL Worksheet.

SQL*Plus

SQL*Plus has been part of Oracle’s software system from the very beginning. SQL*Plus can be run in a windows mode on the client or server side or in line-command mode on the server side. This makes it a versatile tool for the database administrator. If you have taken the first course in this series, you are already familiar with SQL*Plus.

Using the SQL Worksheet

SQL Worksheet is one of the tools in Oracle Enterprise Manager. You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file. You can display a SQL Worksheet by right-clicking a connection in the Connections navigator and selecting Open SQL Worksheet, by selecting Tools and then SQL Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the Select Connection dialog box, select the database connection to use for your work with the worksheet. You can also use that dialog box to create and edit database connections. (You can have a SQL Worksheet window open automatically when you open a database connection by enabling the appropriate SQL Developer user preference under Database Connections.)To create a separate unshared worksheet for a connection, use Ctrl+Shift+N.


The SQL Worksheet has the user interface shown in the following figure:
SQL Worksheet consisting of 1) Enter SQL Statement 2) Results 3) Script Output 4) Explain 5) Autotrace
The table below shows a quick comparison of the pros and cons of each tool:
FeatureSQL*PlusSQL Worksheet
Windows-like interfacePoorGood
Browse tool for retrieving and saving filesNoYes
EditorUse any editor of your choiceUse any editor of your choice
Editing in toolLine mode only, limited mouse as well as cut-and-paste capabilityRich cut-and-paste and mouse capabilities
Format numbersYes, using the SET commandNo
Format headingYes, using the COLUMN commandNo
Retrieve prior commandsOnly most recent commandYes
Behavior on exitCommits transaction by default on exitRolls back transaction by default on exit

If you like the standard Microsoft Windows features, you will want to use SQL Worksheet. However, SQL Worksheet may get a little frustrating because the tool has a lot of bugs. SQL*Plus is very robust and has been around for a long time. In the next lesson, the different types of PL/SQL variables will be identified.