Oracle查询基础(摘)

Oracle 查询基础

下面给出查询的一些基本的操作描述,如果你是位Oracle 老手则可以跳这一章。

 §2.1      SELECT 语句

    在关系数据库中,使用频率最高要算 SELECT 语句了。尽管 SELECT  语句的使用非常
简单,但确很有学问。下面简单介绍有关 SELECT  语句的常用方法。

1.命令语法:

SELECT  的简单语法:

SELECT [DISTINCT | ALL] {* | column1[, column2]...}
FROM {table_1 | (subquery)} [alias]
[, {table_2 | (subquery)} [alias]]...
[WHERE condition]
[CONNECT BY condition [START WITH condition]
[GROUP BY expn] [HAVING expn]
[{ UNION [ALL] | INTERSECT | MINUS } SELECT . . . ]
[ ORDER BY [expn ] [ ASC | DESC]
[ FOR UPDATE [OF [user.]table | view] column ]
[NOWAIT]

详细语法结构需查阅最新 ORACLE              原版《ORACLE8i      SQL   REFERENCE  MANUAL》或
 《ORACLE9i   SQL   REFERENCE    MANUAL》

 §2.2      SQL 中的单记录函数

 许多资料(包括 Oracle      的资料)把Oracle 的SQL 语句中用到的函数分为单值函数和多
值函数,单值函数又分为字符函数和数字函数。下面分别介绍它们。

 §2.2.1      单记录字符函数

        函     数                      说                明
  ASCII                     返回对应字符的十进制值
  CHR                       给出十进制返回字符 
 CONCAT                    拼接两个字符串,与  ||       相同
  INITCAT                   将字符串的第一个字母变为大写
  INSTR                     找出某个字符串的位置
  INSTRB                    找出某个字符串的位置和字节数
  LENGTH                    以字符给出字符串的长度
  LENGTHB                   以字节给出字符串的长度
  LOWER                     将字符串转换成小写
  LPAD                      使用指定的字符在字符的左边填充
  LTRIM                     在左边裁剪掉指定的字符
  RPAD                      使用指定的字符在字符的右边填充
  RTRIM                     在右边裁剪掉指定的字符
  REPLACE                   执行字符串搜索和替换
  SUBSTR                    取字符串的子串
  SUBSTRB                   取字符串的子串(以字节)
  SOUNDEX                   返回一个同音字符串
  TRANSLATE                 执行字符串搜索和替换
  TRIM                      裁剪掉前面或后面的字符串
  UPPER                     将字符串变为大写
  NVL                       以一个值来替换空值

ASCII(<c1>)
<c1>是字符串。返回与指定的字符对应的十进制数。
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

     A      a  ZERO   SPACE
---------- ---------- ---------- ----------
     65     97     48     32

SQL> select ascii('赵') zhao,length('赵') leng from dual;

      ZHAO        LENG
    ----------  ----------
     54740         1

CHR(<I>[NCHAR])
给出整数,返回对应字符。如:

SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH   C
--  -
赵  A

CONCAT(<c1>,<c2>)
SQL> select concat('010-','8801 8159')||'转23' 赵元杰电话  from dual;

赵元杰电话

-----------------

010-8801 8159 转23

INITCAP(<c1>)

返回字符串c1       并第一个字母变为大写。例如:

SQL> select initcap('simth') upp from dual;

UPP
-----
Simth

INSTR(<c1>,<c2>[,<I>[,<j>] ] )
在一个字符串中搜索指定的字符,返回发现指定的字符的位置。
C1: 被搜索的字符串 
C2: 希望搜索的字符串 
I: 搜索的开始位置,缺省是1 
J: 出现的位置,缺省是1。 

SQL> SELECT INSTR ('Oracle Training', 'ra', 1, 2) "Instring" FROM DUAL;

  Instring

----------

        9

INSTRB(<c1>,<c2>[,<I>[,<j>] ] )

除了返回的字节外 ,与INSTR              相同,

LENGTH( <c> )

返回字符串  c      的长度。

SQL> l
 1  select name,length(name),addr,length(addr),sal,length(to_char(sal))
  2* from nchar_tst
SQL> /

NAME    LENGTH(NAME) ADDR            LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

------   ------------  ----------------  ------------  ----------  ----------------

赵元杰          3        北京市海淀区                 6         99999.99    8

LENGTHB( <c> )

以字节返回字符串的字节数。

SQL> select name,lengthb(name),length(name) from nchar_tst;

NAME     LENGTHB(NAME)       LENGTH(NAME)

------   -------------   ------------

赵元杰                 6             3

LOWER ( <c> )
返回字符串并将所有字符变为小写。

SQL> select lower('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

UPPER( <c>)
与  LOWER    相反,将给出字符串变为大写。如:

SQL>    select upper('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

AABBCCDD
RPAD 和LPAD(粘贴字符)
 
RPAD(string,Length[,'set']) 
LPAD(string,Length[,'set']) 
      RPAD在列的右边粘贴字符; 
      LPAD在列的左边粘贴字符。 
 
例 1: 
SQL>select   RPAD(City,35,'.'),temperature    from  weather; 
 
        RPAD(City,35,'.')            temperature 
        -------------------------- ---------------- 
           CLEVELAND......                    85 
           LOS  ANGELES..                     81 
          ......................... 
 
        (即不够 35 个字符用'.'填满) 

LTRIM(左截断)RTRIM(右截断) 函数 
 

LTRIM (string [,’set’])

Left TRIM (左截断)删去左边出现的任何set 字符。 

RTRIM (string [,’set’])

Right TRIM (右截断)删去右边出现的任何set 字符。 

例1: 

SELECT RTRIM (‘Mother Theresa, The’, ‘The’) “Example of Right

Trimming” FROM DUAL;

Example of Right

----------------

Mother Theresa,

SUBSTR Substr(string,start[,Count])

取子字符串中函数 
对字串 (或字段),从 start字符 开始,连续取 count 个字符并返回结果,如果没有指 count
则一直取到尾。 
 
select phone,substr(phone,1,3) || ‘0’ || substr(phone,4) 
from telecommunication where master ’中国电信’; 
 

SUBSTRB(string,start[,Count])
对字串 (或字段),从 start字节 开始,连续取 count 个字节并返回结果,如果没有指 count
则一直取到尾。 
 
 
REPLACE (‘string’ [,’string_in’,’string_out’])

String: 希望被替换的字符串或变量。 
String_in: 被替换字符串。 
String_out: 要替换字符串。 
 
SQL> select replace('Informaix 中国公司','Informaix','IBM Informix')
  2  IBM 数据库  from dual;

IBM 数据库
--------------------
IBM Informix 中国公司

SOUNDEX( <c> )

返回一个与给定的字符串读音相同的字符串(不管拼写是否一样)。

SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERE
SOUNDEX(DPL_NAME) = SOUNDEX(‘Saddam Hussain’) ;
DPL_NAME
----------------------------------------------
Al Husseni
Sadda Al Sada.

REPLACE (‘string’ [,’string_in’,’string_out’])

String:希望被替换的字符串或变量。 
String_in: 被替换字符串。 
String_out: 要替换字符串。 
 
SELECT REPLACE (‘Oracle’, ‘Or’, ‘Mir’) “Example “ FROM DUAL; 
Example 
------- 
Miracle 

TRIM  ( [<leading>] <trailing> FROM <trim_char>     )
RIM可以使你对给定的字符串进行裁剪(前面,后面或前后)。 

z 如果指定 LEADING, Oracle 从trim_char 中裁剪掉前面的字符; 
z 如果指定TRAILING, Oracle 从trim_char 中裁剪掉尾面的字符; 
z 如果指定两个都指定或一个都没有给出,Oracle从trim_char 中裁剪掉前面及尾面的字
   符; 
z 如果不指定 trim_character, 缺省为空格符; 
z 如果只指定trim_source, Oracle Oracle从trim_char 中裁剪掉前面及尾面的字符。 
 
例子:将下面字符串中的前面和后面的‘0                   ‘字符都去掉:

SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL;

TRIM example

--------------------------------

98723489

 §2.2.2       单记录数字函数

函数                              说明 
Value1 + value2                 加 
Value1 - value2                 减 
Value1 * value2                 乘 
Value1 / value2                 除 
ABS(value)                      绝对值 
CEIL(value)                     大于或等于 value 的最小整数 
COS(value)                      余弦 
COSH(value)                     反余弦 
EXP(value)                      e 的value 次幂 
FLOOR(value)                    小于或等于 value 的最大整数 
LN(value)                       value 的自然对数 
LOG(value)                      value 的以10 为底的对数 
MOD(value,divisor)              求模 
NVL(value,substitute)           value 为空时以 substitute 代替 
POWER(value,exponent)           value 的exponent 次幂 
ROUND(value,precision)          按 precision 精度4 舍5入 
SIGN(value)                     value 为正返回 1;为负返回-1;为0 返回 0. 
SIN(value)                      余弦 
SINH(value)                     反余弦 
SQRT(value)                     value 的平方根 
TAN(value)                      正切 
TANH(value)                     反正切 
TRUNC(value,按 precision)        按照 precision 截取 value 
VSIZE(value)                    返回 value在 ORACLE 的存储空间大小
ABS( <n> )
返回指定值的绝对值。如:

SQL> select abs(100),abs(-100) from dual;

  ABS(100)     ABS(-100)

---------- ----------

        100           100

ACOS( <n> )
给出反余弦的值。如:
SQL> Select acos(-1) acos from dual;

       ACOS
       ----------
       3.14159265

ASIN( <n> )
给出反正弦的值。如:

SQL> select asin(-1) "arc sine" from dual;

  arc sine
----------
-1.5707963

ATAN     ( <N>   )

返回一个数字的反正切值。如:

SQL> select atan(-1) "arc tangent" from dual;

arc tangent
-----------
  -.78539816

CEIL( <n> )
返回大于或等于给出数字的最小整数。如:

SQL> select ceil(3.14159) from dual;

CEIL(3.14159)

-------------

              4

COS ( <n> )
返回一个数字余弦值。如:

SQL> select cos(-3.1415926) from    dual;

COS(-3.1415926)

---------------

               -1

COSH ( <n> )

返回一个数字双曲余弦值。如:

SQL> select cosh(20) cosh from dual;

      COSH

----------

  242582598

EXP ( <n> )

返回一个数字  e         的  n 次方的值。如:

SQL> select exp(2),exp(1) from dual;

  EXP(2)        EXP(1)
----------      ----------
  7.3890561  2.71828183

FLOOR ( <n> )
对给定的数字取整数,如:

SQL> select floor(123.45),floor(45.56) from dual;

FLOOR(123.45)      FLOOR(45.56)

   -------------        ------------

             123             45

LN( <n> )

返回一个数字的对数值,n                 是大于  0     的数字,如:

SQL> select ln(1),ln(2),ln(3) from dual;

      LN(1)        LN(2)         LN(3)
       ----------  ----------  ----------
           0    .693147181     1.09861229

LOG( <n1>, <n2> )

返回一个以n1 为底的n2 的对数,n1 不是0 或 1 的正数。如:

SQL> select log(2,1),log(2,2) from dual;

  LOG(2,1)        LOG(2,2)
     ----------    ----------
           0             1

MOD( <n1>,<n2> )

SQL> Select mod(10,3), mod(10,2), mod(10,4)      from   dual;

  MOD(10,3)    MOD(10,2)     MOD(10,4)
     ----------   ----------    ----------
           1            0             2

POWER ( <n1>, <n2> )

返回  n1    的  n2  次方值,如:

SQL> select power(2,10),power(3,3) from dual;
POWER(2,10)      POWER(3,3)

-----------         ----------

        1024           27

ROUND(value,precision)  
 
  按照指定的精度进行舍入; 

select round(55.5),round(-55.5),trunc(55.5),trunc(-55.5) 
from dual; 
 
round(55.5) round(-55.5) trunc(55.5) trunc(-55.5) 
----------- ------------ ----------- ------------- 
       56             -56         55         -55 

SIGN(<n>) 
取数字 n 的符号,大于 0 返回 1;小于 0 返回-1; 等于 0 返回 0。 如: 
 
例: 
select sign(123), sign(-100),sign(0) from dual; 
 
sign(123)  sign(-100)  sign(0) 
---------- ---------- ---------- 
      1           -1         0 

SIN ( <n> )

返回一个数字的正弦值。如:

SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
         1

SINH( <n> )

返回双曲余弦的值,如:

SQL> select sin(20),sinh(20) from dual;
SIN(20)    SINH(20)
     ----------    ----------
.912945251     242582598

SQRT( <n> )

返回 数字  n       的根,如:

SQL> select sqrt(64),sqrt(10) from dual;

  SQRT(64)      SQRT(10)
     ---------- ----------
           8    3.16227766

TAN( <n> )

返回数字  n 的正切值,如:

SQL> select tan(20),tan(10) from dual;

TAN(20)          TAN(10)
----------        ----------
 2.23716094  .648360827 

TANH( <n> )

返回数字  n 的双曲正切值,如:

SQL> select tanh(20),tan(20) from dual;

  TANH(20)         TAN(20)
     ----------     ----------
           1     2.23716094

TRUNC(value,precision)   
 按照指定的截取一个数。如: 
 
SQL> SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2) 
---------- ------------------ 
        100             124.16 
 

 §2.2.3        单记录日期函数

 
Oracle 用到的日期函是: 
 
   函   数                              描      述 
ADD_MONTH                        在日期 date上增加 count 个月 
GREATEST(date1,date2,. . .)      从日期列表中选出最晚的日期 
LAST_DAY( date )                 返回日期 date 所在月的最后一天 
LEAST( date1, date2, . . .)       从日期列表中选出最早的日期 
MONTHS_BETWEEN(date2,date1)      给出 Date2 - date1 的月数(可以是小数) 
NEXT_DAY( date,’day’)            给出日期 date 之后下一天的日期,这里的 day 为星期,
                                 如: MONDAY,Tuesday 等。 
NEW_TIME(date,’this’,’other’)  给出在 this 时区=Other时区的日期和时间 
ROUND(date,’format’)              未指定 format 时,如果日期中的时间在中午之前,则
                                 将日期中的时间截断为 12 A.M.(午夜,一天的开始),
                                  否则进到第二天。时间截断为 12 A.M.(午夜,一天的
                                  开始),否则进到第二天。 
TRUNC(date,’format’)              未指定 format 时,将日期截为 12 A.M.( 午夜,一天
                                  的开始). 
 
 
 
 
ADD_MONTHS( <d>,<I> ) 
 
增加月份和减去月份,如: 
 
SQL> select to_char( add_months(to_date('199712','yyyymm'), 1),'yyyymm') add_month 
  2  from dual; 
 
ADD_MO 
------ 
199801 
 
 
SQL> select to_char(add_months(to_date('199712','yyyymm'), -1 ),'yyyymm') add_mo 
  2  from dual; 
ADD_MO 
------ 
199711 
 
 

LAST_DAY( date )   
 
返回日期 date 所在月的最后一天,如: 
 
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') 
  2  from dual; 
 
TO_CHAR(SY TO_CHAR((S 
---------- ---------- 
2001.05.18 2001.05.19 
 

MONTHS_BETWEEN(date2,date1) 
给出 Date2 - date1 的月数(可以是小数); 
 
SQL> select months_between('19-12月-1999','19-3 月-2000') mon_betw from dual; 
 
  MON_BETW 
---------- 
        -3 
 
 
SQL> select months_between(to_date('2000.05.20','yyyy.mm.dd'), 
  2  to_date('2005.05.20','yyyy.mm.dd') ) mon_bet from dual; 
 
   MON_BET 
---------- 
        -60 
 

NEW_TIME(date,’this’,’other’)  

 
给出在 this 时区=Other时区的日期和时间 
This和 other 是时区,它们可以是下面的值: 
 
时区缩写                                代表的时区 
AST/ADT                             大西洋标准/日期时间 
BST/BDT                             白令海标准/日期时间 
CST/CDT                             中部标准/日期时间 
GMT                                 格林威治时间 
HST/HDT                             阿拉斯加-夏威夷标准/日期时间 
MST/MDT                             山区标准/日期时间 
NST                                 新大陆标准时间 
PST/PDT                             太平洋标准/日期时间 
YST/YDT                             Yukon 标准/日期时间 
 
 
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time, 
  2   to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles 
  3    from dual; 
 
BJ_TIME             LOS_ANGLES 
------------------- ------------------- 
2001.05.19 06:25:25 2001.05.19 13:25:25 
 

NEXT_DAY( date,’day’)  

给出日期date和星期x之后计算下一星期x的日期,这里的day为星期,如: MONDAY,Tuesday
等。但在中文环境下,要写成’星期 x’这样的格式,如: 
 
例:比如今天是 5 月18日星期五,计算下一个星期五是几号: 
SQL>  select next_day('18-5 月-2001','星期五') nxt_day from dual; 
 
NXT_DAY 
---------- 
25-5 月 -01 
 
 

SYSDATE  

用来得到系统的当前日期,如: 
 
SQL> select to_char(sysdate,'dd-mon-yyyy day') from dual; 
 
TO_CHAR(SYSDATE,'DD 
------------------- 
18-5 月 -2001 星期五 
 
 

TRUNC(<date>,[,<fmt>] ) 

按照 给出的 fmt 的要求将日期截断。如果 fmt ’mi’ 则表示截断掉秒保留至分。如: 
 
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 
  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH                  HHMM 
------------------- ------------------- 
2001.05.18 22:00:00 2001.05.18 22:27:00 
 
 
 

 §2.2.4       单记录转换函数

 
   函   数                        描      述 
CHARTOROWID                将 字符转换到 rowid 类型  
CONVERT                    转换一个字符节到另外一个字符节 
HEXTORAW                   转换十六进制到 raw 类型 
RAWTOHEX                   转换 raw 到十六进制 
ROWIDTOCHAR                转换 ROWID到字符 
TO_CHAR                    转换日期格式到字符串 
TO_DATE                    按照指定的格式将字符串转换到日期型 
TO_MULTIBYTE               把单字节字符转换到多字节 
TO_NUMBER                  将数字字串转换到数字 
TO_SINGLE_BYTE             转换多字节到单字节 
 

CHARTOROWID(<c>)

将字符数据类型转换为ROWID 类型,如:

  1* select rowid,rowidtochar(rowid),ename from scott.emp
SQL> /

ROWID                    ROWIDTOCHAR(ROWID)           ENAME
----------------------------------- ---------------------------------------- ----------
AAAFXDAABAAAHVaAAA         AAAFXDAABAAAHVaAAA          SMITH
AAAFXDAABAAAHVaAAB         AAAFXDAABAAAHVaAAB          ALLEN
AAAFXDAABAAAHVaAAC         AAAFXDAABAAAHVaAAC          WARD

CONVERT( <c>,<dset>[,<sset>] )

将源字符串 sset 从一个语言字符集转换到另一个目的dset 字符集。

SELECT CONVERT (‘strutz’, ‘ WE8HP’, ‘ F7DEC ‘) “Conversion”

FROM DUAL;

Conversion
---------------

Strutz.

HEXTORAW( <x> )

将一个十六进制构成的字符串转化为二进制。如:

Insert into printers( printer_nbr,manufacturer,model,init_string)
Values ( 12,’HP’,’Laserjet’,”HEXTORAW(‘1B45’));

RAWTOHEX( <x> )

将一个二进制构成的字符串转化为十六进制。如:
select rawtohex ( init_string) hext from printers
where model=LaserJet’ and manufacturer=’HP’;

hext
-----------
1B45

ROWIDTOCHAR( <x> )

将ROWID 数据类型转换为字符类型,见  CHARTOROWID 。

TO_CHAR(date,’format’) 

 
根据 format 重新格式日期 date 的格式。如: 
 
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; 
 
TO_CHAR(SYSDATE,'YY 
------------------- 
2001/05/18 23:05:36 
 
日期格式比较多,详细内容请参考原版资料。下面给出常用的日期格式代码: 
日期格式代码表 
日期代码             格式说明                                       例子 
AD 或 BC          AD=Anno Domini 公元,BC=Before Christ 公       ‘YYYY AD’=1999 AD 
                 元前。不带点的公元或公元前 
A.D. 或 B.C.      带点的公元或公元前                                  ‘YYYY A.D.’=1999 A.D. 
AM或 PM         AM= ante meridiem 上午,PM=post meridiem  ‘HH12AM’=09AM 
               下午。不带点的上午或下午 
A.M.或P.M.      带点的上午或下午                               ‘HH12A.M.’=09A.M. 
DY             星期几的缩写                                Mon,Tue,... 
DAY            星期几的全拼                                Monday,Tuesday,... 
D              一周的星期几,星期天=1,星期六=7                    1,2,3,4,5,6,7 
DD             一月的第几天,131                            1,2,... 31 
DDD            一年的第几天,1366                           1,2,3,...366 
J              公元前的第几天(从公元前 4712 起 ?)                2451514,2451515,... 
W              一个月的第几周,1 5                           1,2,3,4,5 
WW,IW          一年的第几周,一年的 ISO 的第几周                   1,2,3,4,... 52 
MM             两为数的月                                 01,02,03,...12 
MON            月份的缩写                                 Jan,Feb,Mar ,...Dec 
MONTH          月份的全拼                                 January,February,... 
RM             罗马数字的月份,I  XII                        I,II,III,IV,...XII 
YYYY,YYY,YY,Y  四位数的年,三位数的年                           1999,999,99,9 
YEAR           年的全拼                                  Nineteen Ninety-nine 
SYYYY          如果是公元前(BC),年份前负号                      -1250 
RR             当前年份的后两位数字                            01 代表 2001年 
HH,HH12        12 小时制,112                            1,2,3,...12 
HH24           24 小时制,023                            0,1,2,3,...23 
MI             一小时中的第几分,059                          0,1,2,3...59 
SS             一分中的第几秒,059                           0,1,2,3,...59 
SSSSS          一天中的第几秒,086399                        0,1,2,3,...86399 
../-;:         标点符号表示法                               文字显示 
‘text’         引号表示法                                 文字显示 
 
 
 

TO_DATE(string,’format’)  

 
将一和字串转换为 ORACLE 的日期。如: 

Insert into demo(demo_key,date_col)
Values(1 , to_date(’04-Oct-1999’, ‘DD-Mon-yyyy’) );

TO_MULTI_BYTE(<c>)

将字符串中的单字节字符转换为多字节字符,  如:

TO_NUMBER(<c>)

将给出的字符转换为数字,如:

SELECT TO_NUMBER (‘1947’) “FISCAL_YEAR” FROM DUAL;

FISCAL_YEAR

-----------

1947

TO_MULTI_BYTE(<c>)及 TO_SINGLE_BYTE

将单字节转换为多字节或从多字节转换为单字节。

 §2.2.5         其它的单记录函数

BFILENAME( <dir>, <file> )

指定一个外部二进制文件。如:

INSERT INTO file_tbl

VALUES (BFILENAME (’lob_dir1’, ’image1.gif’));

CONVERT (‘x’,’desc_set’ [, ‘source_set’])

将x   字段或变量的源  source          转换为  desc,如:

select sid,serial#,username,
DECODE(command,
     0,’None’,
     2,’Insert’,
     3,’Select’,
     6,’Update’,
     7,’Delete’,
     8,’Drop’,
     ‘Other’) cmd
     from   v$session where type != ‘BACKGROUND’;

关于DECODE        在优化方面的内容在《Oracle8i/9i              高级数据库管理》中查阅。
DUMP( s,[,fmt [, start [, length ] ] ] )

DUMP    函数以fmt     指定的内部数字格式返回一个VARCHAR2 类型的值。如:

SQL> col global_name for a30
SQL> col DUMP_STRING for a50
SQL> set lin 200
SQL>    select global_name,dump(global_name,1017,8,5) dump_string
  2    from global_name;

GLOBAL_NAME                            DUMP_STRING
------------------------------------- ----------------------------------------------------------------
ORA816.US.ORACLE.COM         Typ=1 Len=20 CharacterSet=ZHS16GBK: U,S,.,O,R

EMPTY_BLOB()            和  EMPTY_CLOB()              函数

这两个函数都是用来对大数据类型字段进行初始化操作的函数,一般有:

BLOB 数据类型 --- EMPTY_BLOB() 
CLOB 数据类型 --- EMPTY_CLOB() 
NCLOB 数据类型 --- EMPTY_CLOB() 

Insert into proposal 
( proposal_id, recipient_name,proposal_name,short_description, 
proposal_text,budget , cover_letter ) 
values(2,’BRAD OHMONT’,’REBUILD FENCE’,NULL, 
EMPTY_CLOB(),EMPTY_BLOB(), 
BFILENAME(‘proposal_dir’,’P2.DOC’) ); 

GREATEST( <exp_list> )

返回一组表达式中的最大值,即比较字符的编码大小。如:
SQL> select greatest('AA','AB','AC') from dual;

GR
--
AC

SQL> select greatest('啊','安','天') from dual;

GR
--

即  “天”的编码比“安”和“啊”都大。

LEAST ( <exp_list> )

返回一组表达式中的最小值,即比较字符的编码大小。如:

SQL> select least('啊','安','天') from dual;

LE
--

UID    函数

返回标识当前用户的唯一整数,如:

SQL> show user
USER  为"SYSTEM"
SQL> l
  1* select username,user_id from dba_users where user_id=UID
SQL> /

USERNAME               USER_ID
------------------------------ ----------
SYSTEM                        5

USER     函数

返回当前用户的名字,如:

SQL> select user from dual;

USER
------------------------------
SYSTEM

USERENV( <opt> )

返回当前用户环境的信息,opt             选项可以是:
ENTRYID    返回当前用户会话的入口ID
SESSIONID  返回当前用户会话的ID
TERMINAL     返回当前系统会话的操作系统标识
OSDBA        如果当前用户有DBA 权限,则返回  TRUE
LABLE         返回当前用户会话的标号
LANGUAGE       返回当前用户的语言和区域
CLIENT_INFO    为当前用户会话返回  client-info       域的值,这个值由
               dbms_application_info,set_client_info 过程来设置。
LANG     以ISO   的三个字符表示当前用户会话所使用的语言。
VSIZE    返回表达式的字节大小。

ISDBA     函数

查看当前用户是否是  DBA           ,当SYSDBA  角色有效是才返回  TRUE,如:

SQL> show user
USER is "SYSTEM"
SQL> select userenv('ISDBA') from dual;

USEREN
------
FALSE

SQL> connect sys/sys@ora816
Connected.
SQL> select userenv('ISDBA') from dual;

USEREN
------
FALSE

SQL> connect internal
Connected.
SQL> select userenv('ISDBA') from dual;

USEREN
------
TRUE

SESSIONID 函数

返回审计会话标识,如:

select userenv(‘SESSIONID’) aud_id from dual;

aud_id
---------
47343

ENTRYID        函数

返回审计会话入口标识,当initsid.ora              文件中的  audit_trail=TRUE    时可以用,如:

select userenv(‘ENTRYID’) from dual;

USERENV(‘ENTRYID’)
-------------
835641

INSTANCE 函数

返回当前INSTANCE         的标识,如:

SQL> select userenv('INSTANCE') from dual;

USERENV('INSTANCE')
-------------------
            1

LANGUAGE 函数

返回当前环境的语言,如:

SQL> select userenv('LANGUAGE') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

LANG 函数

返回当前环境的语言的缩写,如:

SQL> l
  1* select userenv('LANG') from dual
SQL> /

USERENV('LANG')
----------------------------------------------------
ZHS

TERMINAL 函数

返回用户的终端或机器的标识,如:

SQL> select userenv('TERMINAL') from dual;

USERENV('TERMINA
----------------
ZHAOYUANJIE

VSIZE( <x> )

返回  x   的大小(字节)数,如:

SQL> select vsize(user),user from dual;

VSIZE(USER)     USER
-----------   -----------------------------
           6   SYSTEM

 §2.3       SQL 中的组函数

 §2.3.1        多记录组函数

AVG( [ { DISTINCT | ALL}] )

求平均值,ALL 表示对所有求平均值,DISTINCT                    只对不 同的求平均值,相同只取一个。
SQL> l
  1* select avg(sal) from emp
SQL> /

  AVG(SAL)
----------
2073.21429

MAX( [ { DISTINCT | ALL}] )

求最大值,ALL 表示对所有求最大值,DISTINCT                          只对不 同的求最大值,相同只取一个。

SQL> select max(sal) from emp;

  MAX(SAL)

----------
       5000

MIN( [ { DISTINCT | ALL}] )

求最小值,ALL 表示对所有求最小值,DISTINCT                          只对不同的求最小值,相同只取一个。

SQL> select min(sal) from emp;

  MIN(SAL)
     ----------
         800

STDDEV( [ { DISTINCT | ALL}] )

求标准差,ALL 表示对所有求标准差,DISTINCT                          只对不同的求标准差,相同只取一个。

SQL> select stddev(sal) from emp;

STDDEV(SAL)
-----------------
  1182.50322

VARIANCE( [ { DISTINCT | ALL}] )

求协方差,ALL 表示对所有求协方差,DISTINCT                          只对不同的求协方差,相同只取一个。

SQL> select variance(sal) from emp;

VARIANCE(SAL)
------------------------
   1398313.87

 §2.3.2         带  GROUP             BY    的计算

可以用  GROUP By       来实现对一组数进行分组统计(如 SUM,count(*)  等),如:

要列出部门代码、部门人数,部门工资总和,则用到GROUP BY                                  :

SQL> select deptno,count(*) ,sum(sal) from emp group by deptno;

     DEPTNO      COUNT(*)      SUM(SAL)
       ---------- ---------   ----------
         10            3        8750
         20            5       10875
         30            6        9400

 §2.3.3         用  HAVING             来限制分组的计算

     在分组GROUP BY         中,一般都不管 统计的结果是多少都要全显示,我们可以在GROUP
BY  前或后加  HAVING        子句来限制结果的统计,比如要求被统计的人数有 5  个人以上,则
有两方法可以实现:

SQL> select deptno,count(*) ,sum(sal) from emp group by deptno
  2     having count(*)>=5;

     DEPTNO      COUNT(*)      SUM(SAL)
       ----------  ----------   ---------
         20            5       10875
         30            6        9400

SQL> select deptno,count(*) ,sum(sal) from emp having count(*)>=5
  2   group by deptno;

     DEPTNO      COUNT(*)      SUM(SAL)
       ---------- ----------  ----------

 20          5      10875
        30          6       9400

 §2.4      控制和格式化输出

 §2.4.1       用  ORDER BY          子句来对结果进行排序

Oracle 提供ORDER BY  子句,可用于对查询到的结果进行排序输出。这样的操作是在内存
中进行的。比如按照部门代码顺序和员工的工资多少顺序进行输出,需要命令:
SQL> select deptno,ename,sal from emp order by deptno,sal desc;

    DEPTNO    ENAME          SAL
      --------- ---------- ----------
        10 KING             5000
        10 CLARK             2450
        10 MILLER           1300
        20 SCOTT            3000
        20 FORD             3000
        20 JONES            2975
        20 ADAMS             1100
        20 SMITH             800
        30 BLAKE             2850
        30 ALLEN            1600
        30 TURNER            1500
        30 WARD              1250
        30 MARTIN            1250
        30 JAMES             950

这里的  DESC    表 sal(工资)  按照降序排列。

 §2.4.2       用  BREAK        命令 来对结果进行排列

一般可以用  BREAK ON column SKIP xx      来对查询结果进行排列,BREAK ON              命令的参数
如下:

clear    breaks      清除所有的 break 定义 
break   on   column     在该列上中断 
break   on   row        在每一行上中断 
break   on    Page      在每一页上中断 
break   on    report    在每一报告上中断 
skip     n               跳过n 行 
skip     page            跳过未用完的页 

使用方法请看下面例子:
SQL> break on deptno 
SQL> set pagesize 100  
SQL> select deptno,ename,sal from emp order by deptno; 
 
    DEPTNO ENAME             SAL 
     ---------- ---------- ---------- 
        10 CLARK            2450 
           KING             5000 
           MILLER           1300 
        20 SMITH             800 
           ADAMS            1100 
           FORD             3000 
           SCOTT            3000 
           JONES            2975 
        30 ALLEN            1600 
           BLAKE            2850 
           MARTIN           1250 
           JAMES             950 
           TURNER           1500 
           WARD             1250 
 
已选择 14 行。 
 
SQL> break on deptno skip 2 
SQL> select deptno,ename,sal from emp order by deptno; 
 
    DEPTNO ENAME             SAL 
     ---------- ---------- ---------- 
        10 CLARK            2450 
           KING             5000 
           MILLER           1300 
 
 
        20 SMITH             800 


           ADAMS            1100 
           FORD             3000 
           SCOTT            3000 
           JONES            2975 
 
 
        30 ALLEN            1600 
           BLAKE            2850 
           MARTIN           1250 
           JAMES             950 
           TURNER           1500 
           WARD             1250 
 
SQL> break on deptno skip page 
SQL> select deptno,ename,sal from emp order by deptno; 
 
    DEPTNO ENAME             SAL 
     ---------- ---------- ---------- 
        10 CLARK            2450 
           KING             5000 
           MILLER           1300 
 
    DEPTNO ENAME             SAL 
     ---------- ---------- ---------- 
        20 SMITH             800 
           ADAMS            1100 
           FORD             3000 
           SCOTT            3000 
           JONES            2975 
 
    DEPTNO ENAME             SAL 
     ---------- ---------- ---------- 
        30 ALLEN            1600 
           BLAKE            2850 
           MARTIN           1250 
           JAMES             950 
           TURNER           1500 
           WARD             1250 
 
已选择 14 行。 
  §2.4.3       用  COMPUTE             命令对结果进行格式化

 
COMPUTE 命令 的语法如下: 
 
COMP[UTE] [function [LAB[EL] text] ... 
OF {expr|column|alias} ... 
ON {expr|column|alias|REPORT|ROW} ...] 
 
function 可以是下面参数之一 
AVG          数字类型平均值 
COU[NT]     所有类型的个数 
MIN[IMUM]  NUMBER, CHAR,NCHAR, VARCHAR2(VARCHAR), 
            NVARCHAR2 (NCHARVARYI NG) 类型的最小值 
MAX[IMUM]  NUMBER, CHAR,NCHAR, VARCHAR2(VARCHAR), 
            NVARCHAR2 (NCHARVARYI NG) 类型的最大值 
NUM[BER]     计算所有类型的行数 
SUM           计算所有非空数字类型的总和 
STD            计算数字类型的标准差 
DEV[IANCE]    计算数字类型的协方差 
 
LAB[EL] text 
显示的字符串。用它可以替换掉字段的显示。 
 
OF {expr|column|alias} ... 
OF子串或表达式或别名 
 
ON {expr|column|alias|REPORT|ROW} ... 
ON子串或表达式或别名或REPORT或ROW 
 
 

例子:
按照员工是  "clerk"、"analyst"、"analyst"及  "salesman"进行工资小计 ,并加标记"TOTAL",  则:
SQL> BREAK ON JOB SKIP 1
SQL> COMPUTE SUM LABEL ’TOTAL’ OF SAL ON JOB
SQL> SELECT JOB, ENAME, SAL
2 FROM EMP
3 WHERE JOB IN (’CLERK’, ’ANALYST’, ’SALESMAN’)
4 ORDER BY JOB, SAL;
The following output results:
JOB ENAME SAL
--------- ---------- ----------
ANALYST SCOTT 3000
FORD 3000

********* ----------
TOTAL 6000

CLERK SMITH 800
JAMES 950
ADAMS 1100
MILLER 1300
********* ----------
TOTAL 4150
SALESMAN WARD 1250
MARTIN 1250
TURNER 1500
ALLEN 1600
********* ----------
TOTAL 5600

计算工资小于  1,000        的总和:

SQL> COMPUTE SUM OF SAL ON REPORT
SQL> BREAK ON REPORT
SQL> COLUMN DUMMY HEADING ’’
SQL> SELECT ’ ’ DUMMY, SAL, EMPNO
2 FROM EMP
3 WHERE SAL < 1000
4 ORDER BY SAL;

SAL EMPNO
--- ---------- -----------
800 7369
950 7900
----------
sum 1750

计算平均和最大工资、部门:

SQL> BREAK ON DNAME SKIP 1
SQL> COMPUTE AVG LABEL ’Dept Average’ ->
MAX LABEL ’Dept Maximum’ ->
OF SAL ON DNAME

SQL> SELECT DNAME, ENAME, SAL
2 FROM DEPT, EMP
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 AND DNAME IN (’ACCOUNTING’, ’SALES’)

5 ORDER BY DNAME;

DNAME ENAME SAL
-------------- ---------- ----------ACCOUNTING
CLARK 2450
KING 5000
MILLER 1300
************** ----------Dept
Average 2916.66667

Dept Maximum 5000
SALES ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
************** ----------Dept
Average 1566.66667
Dept Maximum 2850
9 rows selected.

计算部门10和20        的工资总和,不打印:

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY SKIP 1
SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO <= 20
4 ORDER BY DEPTNO;

DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 KING 5000
10 MILLER 1300
----------
8750
20 SMITH 800
20 ADAMS 1100
20 FORD 3000
20 SCOTT 3000

20 JONES 2975
----------
10875
8 rows selected.

在报告结束不打印计算工资总和:

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY
SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO <= 20
4 ORDER BY DEPTNO;

DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 SMITH 800
20 ADAMS 1100
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
----------
19625
8 rows selected.

  

 §2.5        配置会话环境

    一般在 SQL>下进行 SQLPLUS  操作,都需要进行必要的环境设置才能完成我们所需要
的输出。所有环境的设置由  SET 命令加相应的环境变量来完成。下面是常用的环境设置:

 §2.5.1        ARRAYSIZE            (取回的行数)

SET  ARRAY[SIZE]{integer} 
 
一次可以提取(Fetch)的行的数目,1->5000,当有较长字段时应设小些。

§2.5.2      AUTOCOMMIT( 自动提交)

SET  AUTO [COMMIT] { [ OFF | ON | IMM | n] }
用于在操作中是自动提交或是部分提交或是不自动提交。
1)ON 或IMM 使得在完成每条 SQL 语句时将未提交的改变立刻提交给数据库系统。
2 )N  允许在用户发出COMMIT 后,可以执行命令的数量(将n 条 SQL 语句所做的改变进
   行提交)。
3)OFF   停止自动提交,用户必须用COMMIT 命令才能被提交。

 §2.5.3      LINESIZE(行显示宽度)

可以设置  LINESIZE    环境变量来控制行的显示宽度,缺省是 80 个字符。
SET    Lin[esize]{80|integer} 
Integer = 设置行宽度(字符个数),最大值 999,如: 
SQL>set linesize 160 
 

 §2.5.4      LONG(长类型显示字节数)

在缺省的 SQL> 状态下,SQL>缓冲区用于显示 LONG 的字节数只有 80 个字符。如果我们需要
查询的列中含有 LONG 类型的字段的话,就需要将 LONG 缓冲区设置大些。 
SET  LONG{80|integer} 
Integer 是 显示或拷贝 long 值的最大宽度, n 1->32767(但必须小于 Maxdata 值) 
SQL>show  Maxdata  (最大行宽) 
SQL>set long 2000 
 

 §2.5.5      PAGESIZE(页行数)

在缺省的 SQL> 状态下,SQL>缓冲区显示页的行数是 24 行,其中 22 行显示数据,2 行显示标
题和横线。我们将 pagesize 设置大些以减少提示标题和横线。 
SET  pag[esize]   {24|integer} 
SQL>SET pagesize 66 

§2.5.6      PAUSE(暂停)

可以设置 PAUSE 为 ON 或 OFF 来控制屏幕显示。当设置为 ON 时,在 select 语句发出后需
要按 Enter键才能显示一屏。 
SET PAUSE [ ON | OFF ] 
SQL> set pause on 
 
提示:在发出 select 语句并按 Enter 键后 还要再按 Enter键才能显示结果. 
 

 §2.5.7      SPACE(列间空格)

可用 set space 来设置各列间的空格数,语法为: 
SET   SPA[CE]    {1|n} 
N 为设置输出行列间的空格数,最大为 10。 
SQL>set space 2 
 

建议:在一般情况下,不用设置 space参数。 
 

 §2.5.8      Termout (启/停屏幕显示)

TERMOUT 用于设置在屏幕上显示或不显示所输出的信息。
SET TERMOUT { ON | OFF }

set    termout  off 
set    termout  on  
 
set   termout  off   常用 SPOOL  XXX 时,即关闭报表在屏幕上的显示(节省时间) 
set   termout  on    常用 SPOOL   off 之后,即恢复报表在屏幕上的显示 
 

 §2.5.9      ECHO (启/停命令显示)

可以用ECHO 命令来显示或不显示所执行的 SQL 命令。语法如:
SET   ECHO{OFF|ON} 
显示执行当中的各命令( 即用 start  时) 
set   echo    受到   set   termout  的影响 
 
set pagesize 100 
set echo on 

select table_name from dict where rownum<20; 
select * from cat where rownum<30; 
set echo off  
--下面只显示结果不显示命令: 
select table_name from dict where rownum<20; 
select * from cat where rownum<30; 
 

 §2.5.10       TRANSACTION (启动事务)

一个很重要的事务环境设置是TRANSACTION。它包括两个部分的内容:
SET TRANSACTION { READ ONLY | USE ROLLBACK SEGMENT segment_name }
READ ONLY    是用于保证读的一致性。即其他用户的修改不影响当前查询结果。
USE ROLLBACK SEGMENT segment_name         是为当前所处理的事务指定专门的回滚段。这主
要是在进行大量的Insert          或Delete  或Update 时,需要一个大的回滚段以保证事务正常完成。
详细见数据库管理员。

 §2.5.11       SHOW ALL(列出所有参数)

可以用 SHOW ALL  来显示当前的所有参数情况。它的用法很简单。比如:

    SQL> show all
    appinfo 为ON 并且已设置为"SQL*Plus"
    arraysize 15
    autocommit OFF
    autoprint OFF
    autorecovery OFF
    autotrace OFF
    blockterminator "." (hex 2e)
    btitle OFF and 为下一条  SELECT      语句的前几个字符
    cmdsep OFF
    colsep " "
    compatibility version NATIVE
    concat "." (hex 2e)
    copycommit 0
    COPYTYPECHECK       为  ON
    define "&" (hex 26)
    describe DEPTH 1 LINENUM OFF INDENT ON
    markup HTML OFF SPOOL OFF ENTMAP ON PREFORMAT OFF
    echo OFF
    editfile "afiedt.buf"
    embedded OFF

escape OFF
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE    为OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 801070000
repfooter OFF and    为  NULL
repheader OFF and     为  NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and  为下一条  SELECT           语句的前几个字符

 underline "-" (hex 2d)
    USER   为"SYS"
    verify ON
    wrap : 行将为已换行
SQL>
你可以从上面的参数中看到其当前值,也可以修改某些参数的值。

 §2.6       格式化输出

 §2.6.1        一般数据的格式化输出

    在Oracle 的SQL>  下,经常用COLUMN             命令来对所输出的列进行格式化,即按照一定
的格式进行显示。COLMUN 命令语法如下:

COL[UMN] [{ column | expr } [ option_1 ... option_n ] ]

column  :列名
expr :有效的  SQL  表达式
option_1... option_n:可以是下列之一:

ALI[AS] alias

CLE[AR]

FOLD_A[FTER]

FOLD_B[EFORE]

FOR[MAT] format

HEA[DING] text

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}

LIKE {expr|alias}

NEWL[INE]

NEW_V[ALUE] variable

NOPRI[NT]|PRI[NT]

NUL[L] text

OLD_V[ALUE] variable

ON|OFF

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

下面给出常用的关键字的解释:
Alias     给出列的别名,BREAK和COUMN可以引用所定义的别名。
CLEAR    取消列的定义。
FORMAT    列显示格式,format为:
9999990      9或0的个数决定最多显示多少位

9,999,999.99 按照逗号和小数点来 显示数据,若是0以空格显示
099999      显示前面补0
$999,999.99 数字前加美圆号
B99999      若为0   ,则结果为空白
99999Mi     若数字为负,则负号放在数字后(右边),缺省放在左边
99999PR     负号将以括号括起
9.999EEEE   以科学记数法表示(必须有4个E)
999V99      数字乘以  10n ,如  1234变为  123400
DATE        采用日期数字格式(MM/DD/YY )

Heading 重新标记列的显示标题,如:

SQL> col  ename  heading 姓名  format a10
SQL> select ename,sal from emp;

例子:

SQL

COLUMN   SALARY  FOR  $9,999,999.99

COLUMN   LAST_NAME  FOR  A35.

 §2.6.2       日期的格式化输出

Oracle 系统提供了一个  NLS_DATE_FORMAT 的环境变量来设置日期的显示格式。用它可以
完成按照不同格式要求的显示,比如按照中国的习惯为  yyyy 年mm 月dd  日等。

1.系统日期 sysdate 的显示 
用 sysdate 可以显示 ORACLE RDBMS 所在机器的日期及时间,如: 
SQL> alter session set nls_date_format ='"公元"yyyy"年"mm"月"dd"日"'; 
 
会话已更改。 
 
SQL> select sysdate from dual; 
 
SYSDATE 
------------------ 
公元 2001 年05月 30 日 
 
 
2.日期类型的显示 
 
select sysdate,to_char(sysdate,’yyyy.mm.dd hh24:mi;ss’) from dual;

SQL> connect scott/tiger 
已连接。 
SQL> alter session set nls_date_format ='yyyy"年"mm"月"dd"日生"'; 
     
    会话已更改。 
SQL> col HIREDATE heading 生日 
SQL> col sal heading 工资 
SQL> col sal ename 姓名 
SQL> select ename,sal,hiredate from emp; 
     
    姓名             工资             生日 
    ---------- ---------- ----------------- 
    SMITH             800 1980年 12月 17 日生 
    ALLEN            1600 1981年 02月 20 日生 
    WARD             1250 1981年 02月 22 日生 
    JONES            2975 1981年 04月 02 日生 
    MARTIN           1250 1981年 09月 28 日生 
    BLAKE            2850 1981年 05月 01 日生 
    CLARK            2450 1981年 06月 09 日生 
    SCOTT            3000 1987年 04月 19 日生 
    KING             5000 1981年 11月 17 日生 
    TURNER           1500 1981年 09月 08 日生 
    ADAMS            1100 1987年 05月 23 日生 
    JAMES             950 1981年 12月 03 日生 
    FORD             3000 1981年 12月 03 日生 
    MILLER           1300 1982年 01月 23 日生 
     
    已选择 14 行。 

 §2.7     加标题

    有时在输出一些结果时,可能需要加一些标题,如表上面的顶标题,落款等。这样的要
求可由Ttitle 和Btitle 来完成。

ttitle 和 btitle 

 
ttitle   [center|left|right]string    顶标题 
btitle   [center|left|right]string    底标题 
ttitle   center   'XX公司人员情况表' 
btitle   left     '制表人:xxxx'   right    '日期:xxxx 年xx 月' 
Clear    ttitle 

§2.8     建立简单报告

我们可以用TTITLE、BTITLE、COLUMN、BREAK ON、COMPUTE SUM 及 SET LINESIZE、
SET PAGESIZE、SET NEWPAGE 来设置查询结果的显示格式;在用  SPOOL                     命令将显示结
果输出到一个操作系统文件中去,一般输出文件的类型为.LST。

建立简单报告主要使用下面命令来实现:

1. SPOOL 命令 

 
SPOOL   filename      将缓冲区的内容写到文件中 
SPOOL   off           终止写命令 
 

2.  COLUMN   命令 

column    col_name[,heading]    format     format_spe 
把字段的结果指定为一种输出格式 
COL   name   heading   '姓名'   for    a10 
COL    sal      heading   '工资'    for    9,999.99 
 

3.ttitle、btitle 

 
ttitle   [center|left|right]string    顶标题 
btitle   [center|left|right]string    底标题 
ttitle   center   'XX公司人员情况表' 
btitle   left     '制表人:赵元杰'   right    '日期:1998.11 月' 
Clear    ttitle 
 

4.break、compute 

 
clear    breaks,clear    computes 
break   on   column     在该列上中断 
break   on   row        在每一行上中断 
break   on    Page 
break   on    report 
skip     n               跳过n 行 
skip     page            跳过未用完的页 
compute       avg 
compute      count 
compute       max 

compute       min 
compute       std 
compute       sum 
compute       var 
compute       num     计算所有行 
compute       sum   of   sal   on   deptno 
 

5.set   在报表中的设置 

z set    termout    off、set   termout   on 命令  
z set   termout    off   常用 SPOOL    XXX 前,即关闭报表在屏幕上的显示(节省时
    间) 
z set   termout    on    常用 SPOOL   off 之后,即恢复报表在屏幕上的显示 
z set   ECHO{OFF|ON}  显示执行当中的各命令(即用 start    时),set   echo    受
    到   set   termout    的影响 
z set    Lin[esize]{80|integer}    设置行宽度,最大值 999 
z set  pag[esize]   {24|integer}   设置页的大小 
 
例子:
SQL>COL ename heading ‘姓名’ for a12
SQL>COL sal heading ‘工资’ for a999,999.99
SQL>COL hiredate  heading ‘出生’
SQL>SET LINESIZE 200
SQL>SET PAGESIZE 60
SQL>SPOOL c:/all_emp
SQL>select ename,sal,deptno,hiredate from emp order by deptno;
SQL>SPOOL OFF

 §2.9     输入变量

    Oracle 提供一种在处理 SQL 语句时可以将参数作为变量来对待的技术,即在条件句中可
以是变量而不是具体的值,这样的处理就是输入变量。这样做的目的就是可以重复使用同样
的语句,每次只要输入相应的值即可。要实现将参数写成为变量,只要在变量前加一个&号
即可。看下面语句:

Select sid, serial#,username, command from v$session
Where   USERNAME = upper(‘&usr’);

这样的语句在运行中,系统会自动提示你回答变量的具体值,上面语句运行时提示和回答时
显示的信息如下:

    SQL> Select sid, serial#,username, command from v$session
      2  Where   USERNAME = upper('&usr');
输入  usr   的值:    sys
     原值      2: Where    USERNAME = upper('&usr')
    新值       2: Where    USERNAME = upper('sys')

        SID  SERIAL# USERNAME            COMMAND
    ---------- ---------- ------------------------------ ----------
          7       26 SYS                          3

在变量说明中,可以使用多个变量,比如:

Alter system kill session ‘&sid,&ser’;

Alter system kill session ‘&会话号,&序列号’;

它的运行情况如下:

SQL> Select   sid, serial#,username, command from v$session;

   SID   SERIAL# USERNAME             COMMAND
---------- ---------- ------------------------------ ----------
          1           1                   0
          2           1                   0
          3           1                   0
          4           1                   0
          5           1                   0
          6           1                   0
          7          26 SYS               3
          8          16 ZHAO              0

已选择8 行。

SQL> Alter system kill session '&sid,&ser';
输入  sid  的值:     8
输入  ser  的值:     16
原值       1: Alter system kill session '&sid,&ser'
新值       1: Alter system kill session '8,16'

系统已更改。 
 
一般系统缺省下是使用 “&” 符号来定义变量,你也可以使用另外的符号来代替,比如不喜
欢用 & 而要用 ?,则有: 
 
SQL> set define ? 

SQL> select sid,serial#,username from v$session where username '?usr'; 
输入 usr 的值:  SYS 
原值    1: select sid,serial#,username from v$session where username='?usr' 
新值    1: select sid,serial#,username from v$session where username='SYS' 
 
       SID    SERIAL# USERNAME 
---------- ---------- ------------------------------ 
          7          26 SYS 
 
次时由于我们修改了会话环境的参数值,可以用下面命令查看: 
 
SQL> show define 
define "?" (hex 3f)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值