Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Chapter 2. Introduction to SQL and SQL Developer

This chapter provides an introduction to the SQL language and one of two tools for working with it. Section 2.1 presents a high-level overview of the SQL language, which will give you an idea of the capabilities of this language. Then some important basic concepts of the SQL language are introduced in Section 2.2, such as constants, literals, variables, expressions, conditions, functions, operators, operands, and so on. Finally, in Section 2.3, this chapter provides a tour of SQL Developer, the main tool we recommend using to learn the SQL language. In order to maximize the benefits of any tool, you first must learn how to use it and to identify the main features available. In Chapter 11 the old, but still widely used SQL*Plus tool is explained in great detail. Even though many of the examples in this book are shown using SQL*Plus, feel free to use whichever tool you find most convenient.

This is the first chapter with real SQL statement examples. It thus would be beneficial for you to have access to an Oracle database and a schema with the seven case tables introduced in Chapter 1 and described in detail in Appendix A. You can find the scripts to create that schema in the download hosted from this book’s catalog page or the Source Code page on the Apress website.

We assume that Oracle is running; database (instance) startup and shutdown are normally tasks of a system or database administrator. Specific startup and shutdown procedures might be in place in your environment. However, if you are working with a stand-alone Oracle environment, and you have enough privileges, you can try the SQL*Plus STARTUP command or use the GUI offered by Oracle Enterprise Manager to start up the database.

2.1 Overview of SQL

SQL (the abbreviation stands for Structured Query Language) is a language you can use in (at least) two different ways: interactively or embedded. Using SQL interactively means that you enter SQL commands via a keyboard, and you get the command results displayed on a terminal or computer screen. Using embedded SQL involves incorporating SQL commands within a program in a different programming language (such as Java or C). This book deals solely with interactive SQL usage.

Although SQL is called a query language, its possibilities go far beyond simply data retrieval. Normally, the SQL language is divided into the following four command categories:

· Data definition (Data Definition Language, or DDL)

· Data manipulation (Data Manipulation Language, or DML)

· Retrieval

· Security and authorization (Data Control Language, or DCL)

Data Definition

The SQL data definition commands allow you to create, modify, and remove components of a database structure. Typical database structure components are tables, views, indexes, constraints, synonyms, sequences, and so on. Chapter 1 introduced tables, columns, and constraints; other database object types (such as views, indexes, synonyms, and sequences) will be introduced in later chapters.

Almost all SQL data definition commands start with one of the following three keywords:

· CREATE, to create a new database object

· ALTER, to change an aspect of the structure of an existing database object

· DROP, to drop (remove) a database object

For example, with the CREATE VIEW command, you can create views. With the ALTER TABLE command, you can change the structure of a table (for example by adding, renaming, or dropping a column). With the DROP INDEX command, you can drop an index.

One of the strengths of an RDBMS is the fact that you can change the structure of a table without needing to change anything in your existing database application programs. For example, you can easily add a column or change its width with the ALTER TABLE command. In modern DBMSs such as Oracle, you can even do this while other database users or applications are connected and working on the database—like changing the wheels of a train at full speed. This property of an RDBMS is known as logical data independence (see Ted Codd’s rules, discussed inChapter 1).

Data definition is covered in more detail in Chapters 3 and 7.

Data Manipulation and Transactions

Just as SQL data definition commands allow you to change the structure of a database, SQL data manipulation commands allow you to change the contents of your database. For this purpose, SQL offers three basic data manipulation commands:

· INSERT, to add rows to a table

· UPDATE, to change column values of existing rows

· DELETE, to remove rows from a table

You can add rows to a table with the INSERT command in two ways. One way is to add rows one by one by specifying a list of column values in the VALUES clause of the INSERT statement. The other is to add one or more rows to a table based on a selection (and manipulation) of existing data in the database (called a subquery).

Note You can also load data into an Oracle database with various tools specifically developed for this purpose—such as Data Pump since Oracle Database 10g, Export and Import in previous Oracle releases, and SQL*Loader. These tools are often used for high-volume data loads.

Data manipulation commands are always treated as being part of a transaction. This means (among other things) that all database changes caused by SQL data manipulation commands get a pending status until you confirm (commit) or cancel (roll back) the transaction. No one (except the transaction itself) can see the pending changes of a transaction before it is committed. That’s why a transaction is often labeled atomic: it is impossible for other database users to see parts of a transaction in the database. It is “all or nothing,” no matter how many DML operations the transaction comprises.

SQL offers two commands to control your transactions explicitly:

· COMMIT, to confirm all pending changes of the current transaction

· ROLLBACK, to cancel all pending changes and restore the original situation

Sometimes, transactions are committed implicitly; that is, without any explicit request from a user. For example, every data definition command (like CREATE, DROP, TRUNCATE etc.) implicitly commits your current transaction.

Note the following important differences between data manipulation and data definition:

· DELETE empties a table; DROP removes a table. TRUNCATE allows you to delete all the rows in a table in an efficient (but irrevocable) way.

· UPDATE changes the contents of a table; ALTER changes its structure.

· You can undo the consequences of data manipulation with ROLLBACK; data definition commands are irrevocable.

Chapter 6 will revisit data manipulation in more detail. Chapter 7 discusses the TRUNCATE command, which is considered a data definition command.

Retrieval

The only SQL command used to query database data is SELECT. This command acts at the set (or table) level, and always produces a set (or table) as its result. If a certain query returns exactly one row, or no rows at all, the result is still a set: a table with one row or the empty table, respectively.

The SELECT command (as defined in the ANSI/ISO SQL standard) has six main components, which implement all SQL retrieval. Figure 2-1 shows a diagram with these six main components of the SELECT command.

Figure 2-1. The six main components of the SELECT command

The lines in this diagram represent all possibilities of the SELECT command, like a railroad map. You can deduce the following three syntax rules from Figure 2-1:

· The order of these six command components is fixed.

· The SELECTand FROM components are mandatory.

· The remaining components (WHERE, GROUP BY, HAVING, and ORDER BY) are optional.

Table 2-1 gives a high-level description of the roles of these six components of the SELECT command.

Table 2-1. The Six Main Components of the SELECT Command

Tip The order of the SELECT command components as displayed in Table 2-1 is also a good order to think about them when writing SQL statements. Notice that the SELECT clause is almost the last one.

Components of the SELECT command implement three of the relational operators introduced in Chapter 1 (Section 1.6) as follows:

· The SELECT component acts as the projection operator.

· The FROM component implements the join operator.

· The restriction operator corresponds to the WHERE component.

Now that we are on the subject of relational operators, note that the union, intersection, and difference (minus) operators are also implemented in SQL. You can use these three set operators to combine the results of multiple SELECT commands into a single result table, as illustrated inFigure 2-2. We will revisit these operators in Chapter 8.

Figure 2-2. A SQL set operators syntax diagram

Security

SQL offers several commands to implement data security and to restrict data access.

First of all, access to the database must be defined. User authorization is implemented by providing database users a login name and a password, together with some database-wide privileges. These are the most important commands in this area:

· CREATE USER, to define new database users

· ALTER USER, to change properties (privileges and passwords) of existing database users

· DROP USER, to remove user definitions from the database

Privileges and Roles

If users are authorized to access the database, you can implement fine-grained data access by granting specific privileges. The Oracle DBMS offers two types of privileges: system privileges and object privileges.

System privileges pertain to the right to perform certain (nonobject-related) actions; for example, you can have the CREATE SESSION privilege (allows you to log on to the database) and the CREATE TABLE privilege. Oracle supports approximately 190 different system privileges.

Object privileges involve the right to access a specific database object in a specific way; for example, the right to issue SELECT, INSERT, and UPDATE commands against the EMPLOYEES table. Table 2-2 lists the most important Oracle object privileges.

Table 2-2. Important Oracle Object Privileges

Note Creating users, granting and revoking system privileges are typically tasks for database administrators. See Oracle SQL Reference, part of the official documentation set for the Oracle Database, for more details on user creation and system and object privileges.

The Oracle DBMS allows you to group privileges into roles. Roles make user management much easier, more flexible, and also more manageable. You will need to be given the ‘create role’ privilege by your DBA. The following are the corresponding SQL commands used to administer these privileges and roles:

· GRANT, to grant certain privileges or roles to users or roles

· REVOKE, to revoke certain privileges or roles from users or roles

A typical scenario is the following:

CREATE ROLE <role name>
GRANT privileges TO <role name>
GRANT <role name> TO user(s)

The first step creates a new (empty) role. The second step (which can be repeated as many times as you like) populates the role with a mix of object and system privileges. The third step grants the role (and thereby all its privileges) to a user in a single step.

Roles have several useful and powerful properties:

· Roles are dynamic; further changes to the role contents automatically affect all users previously granted that role.

· Roles can be enabled or disabled during a session.

· You can protect roles with a password. In that case, only users who know the role password can enable the role.

· The most important advantage of roles is their manageability.

GRANT and REVOKE

Each table has an owner, the user who created the table. Table owners are able to grant privileges on their tables to other database users using the GRANT command. As soon as you create a table, you implicitly get all object privileges on that table, WITH GRANT OPTION, as illustrated inFigure 2-3, which shows the syntax of the GRANT command.

Figure 2-3. The GRANT command syntax diagram

Note System privileges and roles are not considered in Figure 2-3, so the syntax diagram is incomplete.

Here are some comments about the GRANT command:

· Table owners cannot grant the right to remove a table (DROP TABLE) to other database users. Note, however, that Oracle supports a (rather dangerous) DROP ANY TABLE system privilege.

· If you want to grant all object privileges to someone else, you can use the keyword ALL (see Figure 2-3). (Instead of ALL PRIVILEGES, the Oracle DBMS also allows you to specify ALL.)

· With a single GRANT command, you can grant privileges to a single user, a list of users, a role, or all database users. You can address all database users with the pseudo-user PUBLIC (see Figure 2-3).

· The UPDATE privilege supports an optional refinement: this privilege can also be granted for specific columns, by specifying column names between parentheses.

· In principle, there is no difference between tables and views when granting object privileges; however, the privileges ALTER, INDEX, and REFERENCES are meaningless in the context of views.

· The GRANT OPTION not only grants certain object privileges, but also grants the right to the grantee to spread these privileges further.

The counterpart of GRANT is the REVOKE command. Figure 2-4 shows the syntax diagram for REVOKE.

Figure 2-4. The REVOKE command syntax diagram

Besides the two standard SQL commands mentioned in this section (GRANT and REVOKE), Oracle supports several additional commands in the security and data access area; for example, to influence the locking behavior of the DBMS, to implement auditing, and to set up more detailed user authorization.

2.2 Basic SQL Concepts and Terminology

This section discusses the following topics:

· Constants (literals)

· Variables

· Operators, operands, conditions, and expressions

· Functions

· Database object names

· Comments

· Reserved words

Constants (Literals)

A constant (or literal) is something with a fixed value. We distinguish numbers (numeric constants) and text (alphanumeric constants). In database jargon, alphanumeric constants are also referred to as strings.

In the SQL language, alphanumeric constants (strings) must be placed between single quotation marks (quotes). Numbers are also relatively straightforward in SQL; however, don’t put them between quotes or they will be interpreted as strings. If you like, you can explicitly indicate that you want SQL to interpret numeric values as floating point numbers by adding the suffixes f or d to indicate single (float) or double precision, respectively. Be careful with the decimal period and group separators (commas) in numbers, because the correct interpretation of these characters depends on the value of a session parameter (NLS_NUMERIC_CHARACTERS), and there are some cultural differences in this area.

In SQL, dates and time durations (intervals) are special cases. They are typically specified and represented as alphanumeric constants, but they need something else to distinguish them from regular strings. In other words, you must help the DBMS to interpret the strings correctly as date or time-interval constants. Probably the most straightforward (and elegant) method is to prefix the strings with a keyword (DATE, TIMESTAMP, or INTERVAL) and to adhere to a well-defined notation convention. (See the examples in Table 2-3 and the third option in the following list.) These are the three options to specify date and time-related constants in SQL:

· Specify them as alphanumeric constants (strings) and rely on implicit interpretation and conversion by the Oracle DBMS. This is dangerous, because things can go wrong if the actual format parameter for that session is different from the format of the string.

· Specify them as alphanumeric constants (strings) and use a CAST or TO_DATE conversion function to specify explicitly how the strings must be interpreted (see Chapter 5).

· Specify them as alphanumeric constants (strings), prefixed with DATE, TIMESTAMP, or INTERVAL. If you use INTERVAL, you also need a suffix to indicate a dimension, such as DAY, MONTH, or YEAR.

Table 2-3 shows examples of using SQL constants.

Table 2-3. Examples of SQL Constants (Literals)

Note the subtle difference between 132 and '132'. The difference between numbers and strings becomes apparent when considering the operators they support. For example, numbers can be added or multiplied, but you cannot do that with strings. The only operator you can apply to strings is the concatenation operator.

In general, the SQL language is case-insensitive. However, there is one important exception: alphanumeric constants (strings) are case-sensitive. For example, 'JOneS' is not equal to 'Jones'. This is sometimes the explanation of getting the message “no rows selected” in cases where you were expecting to see rows in the result.

Variables

A variableis something that may have a varying value over time, or even an unknown value. A variable always has a name, so you can refer to it.

SQL supports two types of variables:

· Column name variables: The name of a column stays the same, but its value typically varies from row to row while scanning a table.

· System variables: These have nothing to do with tables; nevertheless, they can play an important role in SQL. They are commonly referred to as pseudo columns. See Table 2-4 for some examples of Oracle system variables.

Table 2-4. Examples of Oracle System Variables (Pseudo Columns)

The difference between dates (and timestamps) at the database side and those at the client application side can be relevant if you are connected over a network connection with a database in a remote location.

Users commonly make mistakes by forgetting to include quotes in SQL statements. Consider the following SQL statement fragment:

...WHERE LOCATION = UTRECHT...

LOCATION and UTRECHT are both interpreted by Oracle as variable names (column names), although the following was probably the real intention:

...WHERE LOCATION = 'UTRECHT'...

Operators, Operands, Conditions, and Expressions

An operatordoes something. Operands are the “victims” of operations; that is, operands serve as input for operators. Sometimes, operators need only a single operand (in which case, they are also referred to as monadic operators), but most operators need two or more operands.

The SQL operators are divided in four categories, where the differentiating factor is the operand datatype:

· Arithmetic operators

· Alphanumeric operators

· Comparison operators

· Logical operators

Arithmetic Operators

The SQL language supports four arithmetic operators, as shown in Table 2-5.

Table 2-5. SQL Arithmetic Operators

You can apply arithmetic operators only on NUMBER values; however, there are some exceptions:

· If you subtract two DATE values, you get the difference between those two dates, expressed in days.

· You can add a DATE and an INTERVAL value, which results in another date.

· If you add a DATE and a NUMBER, the number is interpreted as an interval expressed in days.

The Alphanumeric Operator: Concatenation

SQL offers only one alphanumeric operator, allowing you to concatenate string expressions: ||. This modest number of operators is compensated for by the overwhelming number of alphanumeric functions in SQL, which are discussed in Chapter 5. For an example of the use of the concatenation operator, see Table 2-8, later in this chapter.

Comparison Operators

The comparison operators allow you to formulate conditions in SQL. Table 2-6 shows the comparison operators available in SQL.

Table 2-6. SQL Comparison Operators

Expressions with comparison operators are also referred to as predicates or Boolean expressions. These expressions evaluate to TRUE or FALSE. Sometimes, the outcome is UNKNOWN, such as when you have rows with missing information. We will revisit this topic in more detail inChapter 4, when we discuss null values.

Logical Operators

SQL also offers three operators whose operands are conditions: the logical (or Boolean) operators. Table 2-7 lists these operators.

Table 2-7. SQL Logical Operators

Expressions

An expression is a well-formed string containing variables, constants, operators, or functions. Just like constants, expressions always have a certain datatype. See Table 2-8 for some examples of expressions.

Table 2-8. SQL Expression Examples

The last example in Table 2-8 shows that the simplest expression is just a constant.

When SQL expressions get more complex, operator precedence can become an issue; in other words: what are the operator priority rules? Of course, SQL has some precedence rules. For example, arithmetic operators always have precedence over comparison operators, and comparison operators have precedence over logical operators. However, it is highly recommended that you use parentheses in your complex SQL expressions to force a certain expression evaluation order, just as you would do in regular mathematics.

Functions

Oracle has added a lot of functionality to the SQL standard in the area of functions. This is definitely one of the reasons why Oracle SQL is so powerful. You can recognize SQL functions by their signature: they have a name, followed by one or more arguments (between parentheses) in a comma-separated list. You can use functions in expressions, in the same way that you can use operators.

These are the six SQL function categories, based on their operand types:

· Numeric functions

· Alphanumeric functions

· Group functions

· Date functions

· Conversion functions

· Other functions

Table 2-9 shows some examples of SQL functions.

Table 2-9. Examples of SQL Functions

Oracle even allows you to create your own SQL functions by using the PL/SQL or Java languages. Chapter 5 will show a simple example of a user-defined function.

Database Object Naming

All objects in a database need names. This applies to tables, columns, views, indexes, synonyms, sequences, users, roles, constraints, functions, and so on. In general, to enhance the readability of your SQL code, it is highly recommended that you restrict yourself to using the characters Athrough Z, the digits 0 through 9, and optionally the underscore (_).

Note In Oracle, object names are case-insensitive; that is, internally all database object names are converted to uppercase, regardless of how you enter those names.

You may use digits in database object names; however, database object names should always start with a letter. Oracle object names have a maximum length of 30 characters.

Database objects need different names to be able to distinguish them, obviously. To be more precise, database objects need unique names within their namespace. On the other hand, different database users may use the same names for their own objects if they like, because the owner/object name combination is used to uniquely identify an object in the database.

If you insist on creating your own object names in Oracle SQL using any characters you like (including, for example, spaces and other strange characters), and you also want your object names to be case-sensitive, you can include those names within double quotes. The only restriction that remains is the maximum name length: 30 characters. Using this “feature” is discouraged, because you will always need to include those names in double quotes again in every interactive SQL statement you want to execute against those objects. On the other hand, you can use this technique in written applications to prevent conflicts with reserved words, including reserved words of future DBMS versions not known to you at application development time. Actually, several Oracle database utilities use this technique under the hood for precisely this reason.

Comments

You can add comments to SQL commands in order to clarify their intent or to enhance their maintainability. In other words, you can add text that does not formally belong to the SQL statements themselves, and as such should be ignored by the Oracle DBMS. You can add such comments in two ways: between /* and */ or after two consecutive minus signs. Comments after two minus signs are implicitly ended by a newline character; comments between /* and */ can span multiple lines. See Listing 2-1 for two examples.

Listing 2-1. SQL Comments Examples

/* this text will be considered a comment,
so the Oracle DBMS will ignore it ... */
-- and this text too, until the end of this line.

Listing 2-1 shows how you can add comments to SQL commands. Note that you can also add comments to database objects with the COMMENT command. See Chapter 7 for details.

Reserved Words

Just like any other language, SQL has a list of reserved words. These are words you are not allowed to use, for example, as database object names. If you insist on using a reserved word as an object name, you must enclose the name within double quotes, as explained earlier in the “Database Object Naming” section.

These are some examples of SQL reserved words: AND, CREATE, DROP, FROM, GRANT, HAVING, INDEX, INSERT, MODIFY, NOT, NULL, NUMBER, OR, ORDER, RENAME, REVOKE, SELECT, SYNONYM, SYSDATE, TABLE, UPDATE, USER, VALUES, VIEW, and WHERE.

Tip The Oracle data dictionary contains a V$RESERVED_WORDS view. You can check your object names against this view to avoid using reserved words.

See Oracle SQL Reference for more details about naming rules for database objects and a more complete listing of SQL reserved words.

2.3 Introduction to SQL Developer

SQL Developer is the graphical user interface (GUI) tool that Oracle supplies to query the database, explore objects, run reports, and run scripts. It runs on Windows, Linux, and Mac OSX. It can be used to access Oracle databases 9i, 10g, 11g and 12c, as well as other databases such as Times Ten, Microsoft Access, MySQL, and SQL Server.

Screenshots in the following pages come from SQL Developer 4.0.0.13.

SQL*Plus, which is the old, original tool for writing SQL and PL/SQL is explained in detail in Chapter 11.

Installing and Configuring SQL Developer

SQL Developer is included as part of Oracle Database 11g and 12c. You can also download it from the following URL—you need a free Oracle account to do the download.

http://www.oracle.com/technology/products/database/sql_developer/index.html

Once you save the downloaded zip file and extract it to a directory of your own choice, just double click sqldeveloper.exe to start SQL Developer.

Note SQL Developer for Windows does not create any menu shortcuts or icons on the desktop. You need to create these manually if you want them. Create a desktop shortcut by right clicking on the file and selecting Send To Desktop (create shortcut). SQL Developer also does not create any registry entries. Thus, uninstalling SQL Developer is as simple as deleting the SQL Developer directory that you created when you unpacked the archive.

One of the first tasks that you may be prompted to do when you start SQL Developer for the first time is to locate the Java Development Kit (JDK). If you selected the option to download SQL Developer with the JDK, then java.exe will be included. In this example, SQL Developer is installed in C:\oracle\product\sqldeveloper and the location of the JDK will be in the subdirectory structure show in Figure 2-5.

Figure 2-5. SQL Developer java.exe location

When SQL Developer first starts, the Start Page shown in Figure 2-6 opens. This page includes links to documentation, to tutorials, and to the SQL Developer Forum.

Figure 2-6. The SQL Developer start page

Note As SQL Developer is a non-licensed (free) product, support is not obtained through the MyOracleSupport site, formerly known as Metalink. The SQL Developer Forum on Oracle Technet (http://sqldeveloper.oracle.com/) is the location for support and questions. When you have questions or issues, look there for assistance.

There is not a great deal of basic configuration for SQL Developer that you need to do at this time. The “out of the box” settings are fairly good for most users, but there are a couple of items that are worth considering: setting the default script file location and enabling the zebra striped output.

It is usually a good idea to specify the default location for saving and running scripts. One minor annoyance with SQL Developer is that the settings for the file locations are spread among several different dialogs. Select Tools Preferences to bring up the Preferences dialog box, as shown in Figure 2-7. To set the Script location, select Databases Worksheet and enter the preferred location for scripts in the “Select default path to look for scripts” box.

Figure 2-7. Setting the default script location

A second task might be to ensure readable output by turning on the option “Grid in checker board or Zebra pattern.” Every second line will now appear in a slightly darker tone for better readability as shown here.

Figure 2-8. Zebra striped output

Connecting to a Database

Unlike SQL*Plus, you do not have to enter your username, password, and database name every time you connect. With SQL Developer you can have multiple connections that can be saved and organized. If you are using multiple accounts for a single database, you can have a connection created for each of those accounts.

Note You can have multiple connections open at one time, but be careful when one of those connections is to a production database. Two common problems leading to the need for database recovery are when a table is accidently dropped and when data is mistakenly modified in production. Hint: Change the background color of your production connections to an easily distinguishable color.

To create a new connection, click on the Connections tab to make it active and then click on the large green cross (+) in the upper left corner. You can also right click on the Connections icon and select New Connection. This will bring up the New / Select Database Connection dialog as seen in Figure 2-9. In this example, the connection is the book user to a local database.

Figure 2-9. Creating a database connection

To organize your connections, you can create folders and add them to folders. You could organize by database name, type, and location, or any meaningful criteria. There is no option to create a new folder, so you add a connection to a new folder. Right click on the connection, select Add to Folder, and if there aren’t any folders defined you will only have the New Folder option. Enter a folder name in the dialog box. If folders have already been defined, you have the option to add to an existing folder or create a new folder. For existing folders, you can drag and drop the connection onto a folder name to assign it to that folder.

Exploring Objects

SQL Developer includes an Object Browser, which enables you to see the tables, indexes, procedures, and so on that you own and have access to query or execute. You activate the object browser simply by clicking the object in the Navigator Pane on the left hand side.

The tabs on the table object window enable you to see additional details about the object. There are two tabs that deserve special mention, Data and SQL. The Data tab will display the actual data in the table, which is like doing a SELECT * from <table_name>. By default the result window will show the first 50 records from your table. The SQL tab, which is in every object window, displays the actual SQL calls to create the object. Figure 2-10 shows the data in the Employees table that is displayed by clicking the Data tab.

Figure 2-10. Browsing a table

You can also explore the objects owned by others that you are able to access. At the very bottom of the object list, the Other Users entry can be expanded to show all of the objects you can access. All the users in the database are displayed, even if you cannot see any of their objects.

Schema Browser

You may find it more convenient to browse your database objects using the Schema Browser, which you invoke by right clicking your connection in the left hand navigator panel and choosing Schema Browser. That looks like this:

The SQL in Figure 2-11 doesn’t look too good. You might like Ctrl+F7 to beautify your code. This is very useful, especially with inherited code over many, many lines!

Figure 2-11. Schema Browser

Formatting settings may be changed to your likes in Tools/Preferences/Database/SQL Formatter. Now it looks a lot better (see Figure 2-12).

Figure 2-12. Formatted

Entering Commands

The SQL Worksheet is where you enter commands to query and modify data. Like SQL*Plus, you can enter SQL and PL/SQL commands. Some SQL*Plus commands are supported, such as COLUMN, DESCRIBE, and SPOOL. For a full list of supported and unsupported SQL*Plus commands, please refer to the online OracleSQL Developers User’s Guide.

The worksheet is automatically opened when you connect to a database. If you need to open another worksheet or have closed the only one open, click on the SQL Worksheet icon or select the Tools SQL Worksheet menu option.

Note If the worksheet contains more than one statement, the statements must be terminated with a ; or / (on a separate line). If they are not properly terminated, the session will return an error message: ORA-00933: SQL command not properly ended.

Browsing table data

In Figure 2-13 the Data tab is shown when about to enter a new record. We pressed the 3^rd icon, the green plus-sign, and now the Commit and Rollback icons are active. You may enter data in the fields, where it now says (null), and then press Commit. An automatic INSERT statement will be executed for you. But notice that SQL Developer does both the INSERT and the COMMIT when you press the icon.

Figure 2-13. Browsing a table’s data

Run Statement

Unlike SQL*Plus, a statement is not automatically run when you enter a ; or /. The Run Statement (Ctrl+Enter) command or the large green triangle icon is used to run a single command. If the worksheet contains more than one command, Run Statement will run the command under the cursor, assuming that the previous statement(s) have been terminated with a ; or /.

Let’s start by entering the following, simple statement:

SELECT * FROM EMPLOYEES;

There are two things worth noting: First, the SQL statement reserved words are highlighted; second, EMPLOYEES is suggested as the table after you type FROM E. The syntax highlighting is handy when you accidentally type FORM instead of FROM. The auto-complete feature is also a time saver as it can suggest table or view and column names. You can invoke Auto-complete/Completion Insight with Ctrl+Space if it doesn’t appear or if you accidentally dismissed it with Esc.

Click on the Run Statement button or press Ctrl+Enter (or the old way: F9) to execute the query and display the data in the Query Result window, as seen in Figure 2-14.

Figure 2-14. Querying the EMPLOYEES table

To change the sort order of the data, double click on a column heading in the Query Result window.

Run Script

The Run Scriptcommand will run all the statements and/or SQL*Plus commands in the worksheet. This is the command to use when you have multiple statements or want to format the output using supported SQL*Plus commands.

Below the SELECT * FROM EMPLOYEES; we entered in the worksheet, enter SELECT * FROM DEPARTMENTS; and then click the Run Script button or press F5. The output will be displayed in the Script Output window alongside the Query Result window. Notice that the output is almost identical to what you would see in SQL*Plus and is displayed below in Figure 2-15.

Figure 2-15. Querying EMPLOYEES and DEPARTMENTS tables

When running scripts, the output is appended to the Script Output window. To clear the window so that only new output is displayed, click on the Clear button (the picture of the pencil eraser).

Note Not all supported SQL*Plus output formatting commands are properly interpreted for Run Script. For example, the COLUMN command does not change the column headings, but SET FEEDBACK OFF works as expected.

Saving Commands to a Script

After taking time to create a complex statement, it is wise to save that command to a script that you can run later. After entering the commands and statement(s), select File Save, press Ctl+S, or click on the disk button to bring up the File Save dialog box. The directory that it opens should be the same one you set in the Configuration section. The File Save dialog box is shown in Figure 2-16.

Figure 2-16. Saving employees.sql

Running a Script

To run the script we just saved, there are two ways to load and run. The SQL*Plus standard of using @ is supported. To use the @ command, type @employees.sql in the worksheet and select Run Script (F5). This is demonstrated in Figure 2-17.

Figure 2-17. Running employees.sql using @

The second option is to select File Open and pick the employees.sql file you just saved. The commands contained in that file will be loaded into the worksheet. Select the database connection you want to use in the Choose db Connection drop down box in the upper right of the employees.sql window. Until you select the connection, the other buttons will remain grayed out. After you select the connection, press the Run Script button to see the output, as seen in Figure 2-18.

Figure 2-18. Running employees.sql using File Load

You may not like the Shortcut Keys predefined in SQL Developer, and you are free to change them. Go to Tools/Preferences/Shortcut Keys where you see them all and where you may change them to your liking. (see Figure 2-19)

Figure 2-19. Setting shortcut keys

Exporting Your Data

After running your statement with Ctrl+Enter you might like to export your data to a file. SQL Developer provides many file formats such as csv, html, xml, excel, pdf, insert, sqlloader.

Right click your output in the Query Result window, choose Export, and set your choice. You can even predefine what choice should appear by default in Tools/Preferences/Database/Utilities/Export. (see Figure 2-20)

Figure 2-20. Exporting data

User-Defined Reports

You may choose to include your statement as a user-defined report that you can run from the Report library. Right click your output in the Query Result window, and choose Save Grid as Report. (see Figure 2-21)

Figure 2-21. Create Report

Now that your statement is saved as a report you may run by just clicking the name of your report. Open the menu View/Reports—at the bottom of the list you have User Defined Reports. (see Figure 2-22) If you wish, you can also schedule your report to be run automatically using SQL Developer’s GUI interface to the Oracle supplied package dbms_scheduler, which you find in the Navigator under your connection in the node Scheduler.

Figure 2-22. User Defined Reports

Tuning Your SQL

If you have slow statements you may want to investigate how they execute by using the two tools: Explain Plan (F10) and Autotrace (F6). The difference between the two is that Explain Plan just displays the execution plan for the statement taken from the database cache, whereas Autotrace actually executes the statement to be able to display information about the actual execution plan as well as statistics about the current execution, for example, rows fetched, blocks read from disk or cache, and so on.

Figure 2-23 shows an explain plan for a join of two tables.

Figure 2-23. The Explain Plan tool

Using Tools/Preferences/Database/Autotrace-ExplainPlan, you may change which columns/statistics are shown when using these two tools.

Figure 2-24 shows the output from Autotrace (F6). After the explain plan, we see the statistics about the execution of the statement. On the first execution these statistics “hide” themselves below the lower limit of the page—you must pull the black up/down arrows, found at the bottom left, upward to be able to see the statistics.

Figure 2-24. Autotrace

By pressing the little red pin you may keep this first autotrace output, change the SQL statement, and reuse Autotrace to get a second explain plan plus statistics, and those can then be compared by right clicking the second Autotrace tab and chosing Compare with Autotrace.

Then your output would look like what appears in Figure 2-25, with differences marked in red.

Figure 2-25. Comparing Autotrace

Writing PL/SQL

The SQL Developer tool is also intended for PL/SQL development, complete with wizards, editors, compilation, and debugging. The editor for writing a function or procedure looks like Figure 2-26 and is reacheable through the left hand navigator. Just right click the node for the object type; for example, for a function, choose New Function, fill in the wizard fields, and click OK. You are now in the PL/SQL editor where you can edit, compile, compile for debug, set breakpoints, and debug. As you see in Figure 2-26 the function is only started. You must fill in contents for the executable part of the function before the stub RETURN NULL, and, of course, you must change the return value.

Figure 2-26. PL/SQL Editor

Running PL/SQL Code for Testing

When running your code with the green arrow, SQL Developer offers an anonymous block for executing the function. Fill in your chosen test value and click OK. (see Figure 2-27)

Figure 2-27. Using the Run function

Debugging Code to Find Errors

When your code compiles without errors, you may still experience that the code delivers wrong results. To find out why, we debug. By the way, in the childhood of programming, insects actually got stuck on the hot machine panels, and removing these moths became known asdebugging.When debugging your code, you first compile for debug using the Compile option under the two small gray wheels. All references here are for Figure 2-28. The compiled version of your function now contains both the executable p-code and the source code for reference purposes while running.

Figure 2-28. Debug code

Then you set stopping points, breakpoints, at different interesting places in your code, which show up as red points in the margin. Now run the code using the little red ladybug. Again you see the automatically provided anonymous block for running the code, input your chosen test values, and click OK.

The program now provides an extra debug bar at the top for navigating through your code while debugging. The bottom part shows several tabs for investigating the stack of variables and the breakpoints and for setting watches for especially interesting variables—these are very useful if you have many variables in your code.The fourth icon (the red arrow pointing into the code page) means Step Into; which is most useful for stepping one statement at a time through the code; the current statement is identifiable via the red arrow in the margin. When stepping this slowly, you have time to investigate all stages your variables go through; you are even able to see when they are NULL, that is, without value, a situation that is often the cause of wrong results.

The procedure is about the same for making and debugging stored procedures.

The wizards for stored packages and triggers are fairly easy to use. The online documentation can be found at: Packages:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6006.htm#SQLRF01306

Triggers:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7004.htm#SQLRF01405

Data Modeller

Modelling the database, or else reverse engineering an existing schema, is available through the Data Modeller, which is now free and included in SQL Developer.

To produce the following Design diagram from existing tables in your HR account, you would use the menu File/Data Modeler/Import/Data Dictionary. In the wizard you then go though the 4 steps: 1) Choose your connection - 2) Choose the schema in question: HR in this case and All Selected (two diffent checkboxes) - 3) Select relevant tables and go to the other tabs to deselect other unwanted stuff - 4) Review and click Finish and you will see something like what appears in Figure 2-29.

Figure 2-29. Data Modeller—reverse engineering

Here we have just touched upon the many many features of SQL Developer.

For further information, visit the SQL Developer home page at: http://www.oracle.com/technology/products/database/sql_developer/index.html.

Since GUI interfaces like SQL Developer are not always available, it is also very useful to be able to work through the original SQL*Plus interface, which is explained in depth in Chapter 11.