What Is SQL*Plus?

SQL*Plus is essentially an interactive query tool with some scripting capabilities. You can enter a SQL statement, such as a SELECT query, and view the results. You can execute data definition language (DDL) statements to create tables and other objects. DBAs can use SQL*Plus to start up, shut down, and otherwise administer a database. You can even enter and execute PL/SQL code.

SQL*Plus is primarily a command-line application, but, despite its lack of "flash," it is a workhorse tool used daily by database administrators, developers, and yes, even end users. As a DBA, it is my tool of choice for managing the databases under my care. I use it to peek under the hood ”to explore the physical implementation of my database, and to create and manage users, tables, and tablespaces. In my role as a developer, SQL*Plus is the first tool that I fire up when I need to develop a query. In spite of all the fancy, GUI-based SQL generators contained in products such as PowerBuilder, Clear Access, and Crystal Reports , I still find it quicker and easier to build up and test a complex query in SQL*Plus before transferring it to whatever development tool I am using.

1.1.1 Uses for SQL*Plus

Originally developed simply as a way to enter queries and see results, SQL*Plus has been enhanced with scripting and formatting capabilities and can be used for many different purposes. The basic functionality is simple. With SQL*Plus, you can do the following:

While these operations may not seem significant, they are the building blocks you can use to perform various useful functions.

Consider the ability to enter a SELECT statement and view the results. Example 1-1 shows how to do this using SQL*Plus.

Example 1-1. Executing a query in SQL*Plus

SQL> SELECT employee_id, employee_name, employee_billing_rate 2 FROM employee; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_BILLING_RATE ----------- ---------------------------------------- --------------------- 101 Marusia Churai 169 102 Mykhailo Hrushevsky 135 104 Pavlo Virsky 99 105 Mykola Leontovych 121 107 Lesia Ukrainka 45 108 Pavlo Chubynsky 220 110 Ivan Mazepa 84 111 Taras Shevchenko 100 112 Igor Sikorsky 70 113 Mykhailo Verbytsky 300

Combine this capability with SQL*Plus's formatting abilities and you can turn these results into a credible-looking report, such as that shown in Example 1-2, complete with page titles, page numbers , column titles, and nicely formatted output.

Example 1-2. A SQL*Plus formatted report

Employee Listing Page 1 Billing Emp ID Name Rate ---------- ------------------- -------- 101 Marusia Churai 9.00 102 Mykhailo Hrushevsky 5.00 104 Pavlo Virsky .00 105 Mykola Leontovych 1.00 107 Lesia Ukrainka .00 108 Pavlo Chubynsky 0.00 110 Ivan Mazepa .00 111 Taras Shevchenko 0.00 112 Igor Sikorsky .00 113 Mykhailo Verbytsky 0.00

Another twist on the same theme is to format the output as a list of comma-separated values, such as that shown in Example 1-3.

Example 1-3. Comma-separated values from SQL*Plus

101,"Marusia Churai",169 102,"Mykhailo Hrushevsky",135 104,"Pavlo Virsky",99 105,"Mykola Leontovych",121 107,"Lesia Ukrainka",45 108,"Pavlo Chubynsky",220 110,"Ivan Mazepa",84 111,"Taras Shevchenko",100 112,"Igor Sikorsky",70 113,"Mykhailo Verbytsky",300

Using the SQL*Plus SPOOL command, which you'll read more about in Chapter 5, you can write this output to a .csv file easily readable by most, if not all, spreadsheet programs. In fact, if you are running Microsoft Windows with Microsoft Office installed, simply double-clicking on a .csv file will open that file in Microsoft Excel, where you can further manipulate the data.

Beginning with SQL*Plus in Oracle8 i Database, you can use the SET MARKUP HTML command to generate HMTL output, such as that shown in Example 1-4.

Example 1-4. A SQL*Plus report formatted in HTML

 

Related

Категории