关闭

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

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


一个简单的报表例子:


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>
 

start activity.sql

rem Bookshelf activity report

set headsep !

ttitle 
'Checkout Log for 1/1/02-3/31/02'
btitle 
'from the Bookshelf'

column Name format a20
column Title format a20 word_wrapped
column DaysOut format 999.99
column DaysOut heading 'Days!Out'

break on Name skip 1 on report
compute avg of DaysOut on Name
compute avg of DaysOut on report

set linesize 100
set pagesize 60
set newpage 0
set feedback off

spool activity.lst

select Name, Title, CheckoutDate, ReturnedDate,
       ReturnedDate
-CheckoutDate as DaysOut /*Count Days*/
  
from BOOKSHELF_CHECKOUT
 
order by Name,CheckoutDate;

spool 
off
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:308311次
    • 积分:4267
    • 等级:
    • 排名:第7148名
    • 原创:94篇
    • 转载:207篇
    • 译文:3篇
    • 评论:12条
    最新评论