几个基本的 Sql Plus 命令和例子

- 日理万妓 2022-09-20 08:42 131阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td>remark　</td> 
 <td>告诉SQLPLUS接下来的文字是注释，不是命令 </td> 
 </tr> 
 <tr> 
 <td>set haedsep </td> 
 <td>标题分隔符标识一个告诉SQLPLUS将一个标题分为两行或更多行的字符 </td> 
 </tr> 
 <tr> 
 <td>ttitle </td> 
 <td>设置报表每一页的头标题 </td> 
 </tr> 
 <tr> 
 <td>btitle </td> 
 <td>设置报表每一页的尾标题 </td> 
 </tr> 
 <tr> 
 <td>column </td> 
 <td>给SQLPLUS各种关于列的标题、格式和处理的指令 </td> 
 </tr> 
 <tr> 
 <td>break on </td> 
 <td>告诉SQLPLUS在报表的各部分间插入空格，或者将小计和总计断开 </td> 
 </tr> 
 <tr> 
 <td>compute sum </td> 
 <td>令SQLPLUS计算小计 </td> 
 </tr> 
 <tr> 
 <td>set linesize </td> 
 <td>为报表的行设置最大字符数 </td> 
 </tr> 
 <tr> 
 <td>set pagesize </td> 
 <td>为页设置最大行数 </td> 
 </tr> 
 <tr> 
 <td>set newpage </td> 
 <td>设置页与页之间的空行数 </td> 
 </tr> 
 <tr> 
 <td>spool </td> 
 <td>将通常在屏幕上显示的报表移入一个文件，以便打印 </td> 
 </tr> 
 <tr> 
 <td>/**/ </td> 
 <td>在SQL项内标记一个注释的开头和结尾。类似于remark </td> 
 </tr> 
 <tr> 
 <td>-- </td> 
 <td>在SQL项内标记联机注释所的开始。将该标记开始到本行末尾的一切内容都视为注释。类似于remark </td> 
 </tr> 
 <tr> 
 <td>set pause </td> 
 <td>使屏幕显示在页与页之间停顿 </td> 
 </tr> 
 <tr> 
 <td>save </td> 
 <td>把正在建立的SQL查询保存到一个选中的文件中 </td> 
 </tr> 
 <tr> 
 <td>host </td> 
 <td>向主操作系统发送命令 </td> 
 </tr> 
 <tr> 
 <td>start或@ </td> 
 <td>告诉SQLPLUS执行已经存储到文件中的指令 </td> 
 </tr> 
 <tr> 
 <td>edit </td> 
 <td>使你迅速离开SQLPLUS并进入所选择的编辑器 </td> 
 </tr> 
 <tr> 
 <td>define_editor </td> 
 <td>告诉SQLPLUS你所选择的编辑器的名字 </td> 
 </tr> 
 <tr> 
 <td>exit或quit </td> 
 <td>终止SQLPLUS </td> 
 </tr> 
 </tbody> 
</table>

一个简单的报表例子：  
  
**activity.lst**  
  
SQL>  
SQL> select Name, Title, CheckoutDate, ReturnedDate,  
2           ReturnedDate-CheckoutDate as DaysOut /\*Count Days\*/  
3      from BOOKSHELF\_CHECKOUT  
4     order by Name,CheckoutDate;  
Mon Aug 28                                                                                 page 1  
                            Checkout Log for 1/1/02-3/31/02  
  
                                                                          Days  
NAME                 TITLE                CHECKOUTDATE    RETURNEDDATE    Out  
\-------------------- -------------------- --------------- --------------- -------  
DORAH TALBOT         EITHER/OR            02-JAN-02       10-JAN-02          8.00  
                     POLAR EXPRESS        01-FEB-02       15-FEB-02         14.00  
                     GOOD DOG, CARL       01-FEB-02       15-FEB-02         14.00  
                     MY LEDGER            15-FEB-02       03-MAR-02         16.00  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         13.00  
  
EMILY TALBOT         ANNE OF GREEN GABLES 02-JAN-02       20-JAN-02         18.00  
                     MIDNIGHT MAGIC       20-JAN-02       03-FEB-02         14.00  
                     HARRY POTTER AND     03-FEB-02       14-FEB-02         11.00  
                     THE GOBLET OF FIRE  
  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         14.33  
  
FRED FULLER          JOHN ADAMS           01-FEB-02       01-MAR-02         28.00  
                     TRUMAN               01-MAR-02       20-MAR-02         19.00  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         23.50  
  
GERHARDT KENTGEN     WONDERFUL LIFE       02-JAN-02       02-FEB-02         31.00  
                     MIDNIGHT MAGIC       05-FEB-02       10-FEB-02          5.00  
                     THE MISMEASURE OF    13-FEB-02       05-MAR-02         20.00  
                     MAN  
  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         18.67  
  
JED HOPKINS          INNUMERACY           01-JAN-02       22-JAN-02         21.00  
                     TO KILL A            15-FEB-02       01-MAR-02         14.00  
                     MOCKINGBIRD  
  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         17.50  
  
PAT LAVAY            THE SHIPPING NEWS    02-JAN-02        12-JAN-02        10.00  
                     THE MISMEASURE OF    12-JAN-02        12-FEB-02        31.00  
                     MAN  
  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         20.50  
  
ROLAND BRANDT        THE SHIPPING NEWS    12-JAN-02        12-MAR-02        59.00  
                     THE DISCOVERERS      12-JAN-02        01-MAR-02        48.00  
                     WEST WITH THE NIGHT  12-JAN-02        01-MAR-02        48.00  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*                                                      -------  
avg                                                                         51.67  
  
                                                                          -------  
avg                                                                         22.58  
  
  
  
                                    from the Bookshelf  
SQL>

![20070622105955220.gif][] start activity.sql  
![20070622105955220.gif][]  
![20070622105955220.gif][]rem Bookshelf activity report  
![20070622105955220.gif][]  
![20070622105955220.gif][] set  headsep !  
![20070622105955220.gif][]  
![20070622105955220.gif][]ttitle  ' Checkout Log for 1/1/02-3/31/02 '   
![20070622105955220.gif][]btitle  ' from the Bookshelf '   
![20070622105955220.gif][]  
![20070622105955220.gif][] column  Name format a20  
![20070622105955220.gif][] column  Title format a20 word\_wrapped  
![20070622105955220.gif][] column  DaysOut format  **999.99**   
![20070622105955220.gif][] column  DaysOut heading  ' Days!Out '   
![20070622105955220.gif][]  
![20070622105955220.gif][] break   on  Name skip  **1**   on  report  
![20070622105955220.gif][] compute   avg   of  DaysOut  on  Name  
![20070622105955220.gif][] compute   avg   of  DaysOut  on  report  
![20070622105955220.gif][]  
![20070622105955220.gif][] set  linesize  **100**   
![20070622105955220.gif][] set  pagesize  **60**   
![20070622105955220.gif][] set  newpage  **0**   
![20070622105955220.gif][] set  feedback  off   
![20070622105955220.gif][]  
![20070622105955220.gif][]spool activity.lst  
![20070622105955220.gif][]  
![20070622105955220.gif][] select  Name, Title, CheckoutDate, ReturnedDate,  
![20070622105955774.gif][]![20070622105956648.gif][]       ReturnedDate \- CheckoutDate  as  DaysOut  /\*\*/ /\*Count Days\*/   
![20070622105955220.gif][]   from  BOOKSHELF\_CHECKOUT  
![20070622105955220.gif][]  order   by  Name,CheckoutDate;  
![20070622105955220.gif][]  
![20070622105955220.gif][]spool  off

[20070622105955220.gif]: http://oracle.chinaitlab.com/UploadFiles_7269/200706/20070622105955220.gif
[20070622105955774.gif]: http://oracle.chinaitlab.com/UploadFiles_7269/200706/20070622105955774.gif
[20070622105956648.gif]: http://oracle.chinaitlab.com/UploadFiles_7269/200706/20070622105956648.gif