[Laskey99] Section 9.2. SQL*Plus

来源：百度文库编辑：神马文学网时间：2024/04/28 00:45:58

9.2. SQL*Plus

SQL*Plusis almost certain to be the first Oracle tool that comes to mind, andfor good reason. SQL*Plus has been available since the earliestversions of Oracle, when it was called the User Friendly Interface(UFI). SQL*Plus is primarily an interface to the database that allowsthe execution of SQL statements, but it is also much more than that.SQL*Plus can execute the following types of statements:

SQL: Corresponds to the ANSI SQL standard and Oracle extensions
PL/SQL: A proprietary SQL language extension from Oracle
SQL*Plus: A proprietary formatting and operational tool from Oracle

SQL*Plus can't be easily categorized. Its behavior is probablyclosest to that of an interpreter, which means that it works withsource statements directly without the need for separate compilation.SQL and PL/SQL statements are sent to the Oracle kernel, eitherlocally or via SQL*Net, where they are parsed (a process similar tocompilation) and executed. SQL*Plus statements are operated on andexecuted directly by the running copy of SQL*Plus.

9.2.1. SQL from SQL

One of the most powerful features ofSQL*Plus is its ability to use SQL statements to create a new set ofSQL statements, which in turn yield an interesting or useful result.For example, as the DBA, you might want to drop a set of tables froma particular user's schema. You could, of course, list all thetables and then issue a DROP TABLE command for each, but it would bemuch easier to write a SQL script (a collection of SQL*Plus, SQL,and/or PL/SQL statements) to do it. Here is a simple script thatwill, in turn, generate and run a script to drop all of SCOTT'stables:

set verify off
set pagesize 0
set termout off
set feedback off
set sqlprompt ''
spool dropem.sql
SELECT 'DROP TABLE',owner||'.'||table_name,';'
FROM dba.tables
WHERE owner='SCOTT'
/
spool off
set verify on
set feedback on
start dropem.sql
exit

When the script listed above is run, it will create an output filecalled dropem.sql. That file will contain thefollowing:

DROP TABLE SCOTT.BONUS;
DROP TABLE SCOTT.DEPT;
DROP TABLE SCOTT.EMP;
DROP TABLE SCOTT.SALGRADE;

This technique of generating SQL from SQL only works when the script is placed in a file and run using the START or @ command. The script will not work properly if it is executed interactively from the terminal.

As you can see, the first script generated a set of SQL statementsthat dropped all of SCOTT's tables. This script may be mademore general, so that all tables belonging to any owner can bedropped by using a SQL*Plus substitution variable, as follows:

set verify off
set pagesize 0
set termout off
set feedback off
spool dropem.sql
SELECT 'DROP TABLE',owner||'.'||table_name,';'
FROM dba.tables
WHERE owner=UPPER(&Owner)
/
spool off
set verify on
set feedback on
start dropem.sql
exit

The substitution variable &Owner will cause SQL*Plus to promptfor the name of a schema owner, and will then substitute the valueentered into the SQL statement as though it had been entereddirectly. This technique is extremely powerful, and allows the DBA toperform many functions with relative ease.^[1]

^[1] For acollection of ready-made scripts, see OracleScripts, by Brian Lomasky and David C. Kreines(O'Reilly & Associates, 1998). For a complete discussion ofSQL*Plus, see Oracle SQL*Plus: The DefinitiveGuide, by Jonathan Gennick (O'Reilly &Associates, 1999).

9.2.2. Producing Reports

SQL*Plusalso contains a powerful set of commands for formatting output andproducing reports. While not as powerful or flexible as a fullcommercial report writing system, reports generated by SQL*Plus areattractive and easy to program, and may satisfy a variety ofreporting needs.

Consider the following SQL statement, which lists all employees inthe scott.emp table and prints a projected new salary based on a 10%increase:

SELECT empno,ename,sal,sal*1.10,comm
FROM scott.emp
/

When this statement is executed, the following output is produced:

    EMPNO ENAME           SAL  SAL*1.10      COMM
--------- --------- --------- --------- ---------
     7369 SMITH           800       880
     7499 ALLEN          1600      1760       300
     7521 WARD           1250      1375       500
     7566 JONES          2975    3272.5
     7654 MARTIN         1250      1375      1400
     7698 BLAKE          2850      3135
     7782 CLARK          2450      2695
     7788 SCOTT          3000      3300
     7839 KING           5000      5500
     7844 TURNER         1500      1650         0
     7876 ADAMS          1100      1210
     7900 JAMES           950      1045
     7902 FORD           3000      3300
     7934 MILLER         1300      1430

14 rows selected

While this output is readable and contains the requested information,it is probably not a report you would want to present to seniormanagement. Try adding a few SQL*Plus formatting commands:

set space 2
set feedback off
set linesize 54
set pagesize 30
COLUMN empno heading "Employee|Number" format 9999
COLUMN ename heading "Employee|Name" format a10
COLUMN sal heading "Current|Salary" format $9999.99
COLUMN newsal heading "New|Salary" format $9999.99
COLUMN comm heading "Commission" format $9999.99
TTITLE LEFT 'The Totally Bogus Company' -
       RIGHT 'Page: ' FORMAT 99 SQL.PNO SKIP 2
BTITLE CENTER 'Company Confidential' 
SELECT empno,ename,sal,sal*1.10 newsal,comm
FROM scott.emp
/

Now the following report will be produced:

The Totally Bogus Company                       Page:  1
           
Employee  Employee      Current        New
  Number  Name           Salary     Salary  Commission
--------  ----------  ---------  ---------  ----------
    7369  SMITH         $800.00    $880.00
    7499  ALLEN        $1600.00   $1760.00     $300.00
    7521  WARD         $1250.00   $1375.00     $500.00
    7566  JONES        $2975.00   $3272.50
    7654  MARTIN       $1250.00   $1375.00    $1400.00
    7698  BLAKE        $2850.00   $3135.00
    7782  CLARK        $2450.00   $2695.00
    7788  SCOTT        $3000.00   $3300.00
    7839  KING         $5000.00   $5500.00
    7844  TURNER       $1500.00   $1650.00        $.00
    7876  ADAMS        $1100.00   $1210.00
    7900  JAMES         $950.00   $1045.00
    7902  FORD         $3000.00   $3300.00
    7934  MILLER       $1300.00   $1430.00

                 Company Confidential

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next