oracle_PLSQL_语法详细手册范本

SQL PL/SQL语法手册
第一部分SQL语法部分
Create table 语句
语句: CREATE TABLE [schema.]table_name
( { column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint}
[, { column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint} ]...)
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[ RECOVERABLE | UNRECOVERABLE ]
[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]
[ INSTANCES { integer | DEFAULT } ]
)
| NOPARALLEL ]
[ CACHE | NOCACHE ]
| [CLUSTER cluster (column [, column]...)] ]
[ ENABLE enable_clause
| DISABLE disable_clause ] ...
[AS subquery]
表是Oracle中最重要的数据库对象,表存储一些相似的数据集合,这些数据描述成若干列或字段.create table 语句的基本形式用来在数据库中创建容纳数据行的表.create table 语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还可以指定约束条件,存储参数和该表是否是个cluster的一部分.
Schema 用来指定所建表的owner,如不指定则为当前登录的用户.
Table_name 用来指定所创建的表名,最长为30个字符,但不可以数字开头(可为下划线),但不可同其它对象或Oracle的保留字冲突.
Column 用来指定表中的列名,最多254个.
Datatype 用来指定列中存储什么类型的数据,并保证只有有效的数据才可以输入. column_constraint 用来指定列约束,如某一列不可为空,则可指定为not null.
table_constraint 用来指定表约束,如表的主键,外键等.
Pctfree 用来指定表中数据增长而在Oracle块中预留的空间. DEFAULT为10%,也就是说该表的每个块只能使用90%,10%给数据行的增大时使用.
Pctused 用来指定一个水平线,当块中使用的空间低于该水平线时才可以向该中加入新数据行.
Parallel 用来指定为加速该表的全表扫描可以使用的并行查询进程个数.
Cache 用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.
Cluster 用来指定该表所存储的cluster.
Tablespace 用来指定用数据库的那个分区来存储该表的数据.
Recoverable|Unrecoverable 用来决定是否把对本表数据所作的变动写入Redo 文件.以恢复对数据的操作.
As 当不指定表的各列时,可利用As子句的查询结果来产生数据库结构和数据.
例:
1) create table mytab1e(mydec decimal,
myint inteter)
tablespace user_data
pctfree 5
pctused 30;
2) create table mytable2
as ( select * from mytable1);
create sequence语句
语句: CREATE SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[START WITH integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]
序列用来为表的主键生成唯一的序列值.
Increment by 指定序列值每次增长的值
Start with 指定序列的第一个值
Maxvalue 指定产生的序列的最大值
Minvalue 指定产生的序列的最小值
Cycle 指定当序列值逵到最大或最小值时,该序列是否循环. Cache 指定序列生成器一次缓存的值的个数
Order 指定序列中的数值是否按访问顺序排序.
例:
1) create sequence myseq
increment by 4
start with 50
maxvalue 60
minvalue 50
cycle
cache 3;
2)
sql> create sequence new_s;
sql>insert into new (new_id,last_name,first_name)
values(new_s.nextval,’daur’,’permit’);
create view语句
语句: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name [(alias [,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
视图实际上是存储在数据库上旳select语句.每次在sql语句中使用视图时,表示该视图的select语句就用来得到需要的数据.
Or replace 创建视图时如果视图已存在,有此选项,新视图会覆盖旧的
视图.
Force 如有此选项,当视图基于的表不存在或在该模式中没有创建视图的权限时,也可以建立视图.
As subquery 产生视图的select查询语句
With check option 如果视图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本选项保证在每次插入或更新数据后,该数据可以在视图中查到
例:
create or place view new_v
as
select substr(d.d_last_name,1,3),
d.d_lastname,d.d_firstname,b.b_start_date,b.b_location
from new1 d,
new2 b
where d.d_lastname=b.b_lastname;
INSERT语句:
语法
INSERT INTO [schema.]{table | view | subquery }[@dblink]
[ (column [, column] ...) ]
{VALUES (expr [, expr] ...) | subquery}
[WHERE condition]
插入单行
使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL 关键词将其值设为空值(两个连续的逗号也可以表示空值,也可使用NULL关键词)
插入一行时试图为那些NOT NULL的列提供一个NULL值,会返回错误信息.
举例:
插入一条记录到DEPARTMENT表中
INSERT INTO DEPARTMENT
(DEPARTMENT_ID,NAME,LOCATION_ID)
VALUES (01,’COMPUTER’,167)
插入多行
将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.
举例:
INSERT INTO ORDER_TEMP
SELECT A.ORDER_ID,B.ITEM_ID,,E.FIRST_NAME||'.'||ST_NAME,
A.ORDER_DATE,A.SHIP_DATE,D.DESCRIPTION,
B.ACTUAL_PRICE,
B.QUANTITY,B.TOTAL
FROM SALES_ORDER A, ITEM B, CUSTOMER C,
PRODUCT D, EMPLOYEE E
WHERE MONTHS_BETWEEN(TO_DATE(A.ORDER_DATE),TO_DATE('01-7月-91'))>0 AND A.CUSTOMER_ID=C.CUSTOMER_ID
AND C.SALESPERSON_ID=E.EMPLOYEE_ID
AND A.ORDER_ID=B.ORDER_ID
AND B.PRODUCT_ID=D.PRODUCT_ID
从其它表复制数据:
要快速地从一个表向另一个尚不存在的表复制数据,可以使用CREATE TABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.
CREATE TABLE EMPLOYEE_COPY
AS
SELECT *
FROM EMPLOYEE
UPDATE语句:
语法
UPDATE [schema.]{table | view | subquery}[@dblink] [alias]
SET { (column [, column] ...) = (subquery)
| column = { expr | (subquery) } }
[, { (column [, column] ...) = (subquery)
| column = { expr | (subquery) } } ] ...
[WHERE condition]
UPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句可以用SELECT语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据,均要执行一次SELECT语句.
举例:
UPDATE EMPLOYEE_COP
SET SALARY=
SALARY-400
WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,'YYMMDD'))<850101
UPDATE ITEM_COP A
SET A.ACTUAL_PRICE=
(
SELECT B.LIST_PRICE
FROM PRICE B,SALES_ORDER C
WHERE A.PRODUCT_ID=B.PRODUCT_ID AND
A.ORDER_ID=C.ORDER_ID AND
TO_NUMBER(TO_CHAR(C.ORDER_DATE,'YYYYMMDD')) BETWEEN
TO_NUMBER(TO_CHAR(B.START_DATE,'YYYYMMDD')) AND
NVL(TO_NUMBER(TO_CHAR(END_DATE,'YYYYMMDD')),29991231) )
DELETE语句:
语法
DELETE [FROM] [schema.]{table | view}[@dblink] [alias] [WHERE condition]
DELETE语句删除所有满足WHERE子句条件的数据行.
举例:
DELETE FROM item
WHERE ORDER_ID=510
TRUNCATE语句:
语法
TRUNCATE [schema.]table
各类Functions:
转换函数:
函數:TO_CHAR
语法:
TO_CHAR(number[,format])
用途:
将一个数值转换成与之等价的字符串.如果不指定格式,将转换成最简单的字符串形式.如果为负数就在前面加一个减号.
Oracle为数值提供了很多格式,下表列出了部分可接受的格式:
函數:TO_CHAR
语法:
TO_CHAR(date[,format])
用途:
将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,用户可以用它们的组合来表示最终的输出格式.唯一限制就是最终的掩码不能超过22个字符.下表列出了部分日期格式化元素.
Oracle为数值提供了很多格式,下表列出了部分可接受的格式:
函數:TO_DATE
语法:
TO_DATE(string,format)
用途:
根据给定的格式将一个字符串转换成Oracle的日期值.
该函数的主要用途是用来验证输入的日期值.在应用程序中,用户必须验证输入日期是否有效,如月份是否在1~12之间和日期中的天数是否在指定月份的天数内.
函數:TO_NUMBER
语法:
TO_NUMBER(string[,format])
用途:
该函数将一个字符串转换成相应的数值.对于简单的字符串转换数值(例如几位数字加上小数点).格式是可选的.
日期函数
函數:ADD_MONTHS
语法:
ADD_MONTHS(date,number)
用途:
在日期date上加指定的月数,返回一个新日期.如果给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,如果是小数,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.
例如:
SELECT TO_CHAR(ADD_MONTHS(sysdate,1),
'DD-MON-YYYY') "Next month"
FROM dual
Next month
-----------
19-FEB-2000
函數:LAST_DAY
语法:
LAST_DAY(date)
用途:
返回日期date所在月份的最后一天的日期.
例如:
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL
SYSDATE Last Days Left
--------- --------- ----------
19-JAN-00 31-JAN-00 12
函數:MONTHS_BETWEEN
语法:
MONTHS_BETWEEN(date1,date2)
用途:
返回两个日期之间的月份.如果两个日期月份内的天数相同(或者都是某个月的最后一天),返回值是整数.否则,返回值是小数,每于1/31月来计算月中剩余天数.如果第二个日期比第一个日期还早,则返回值是负数.
例如:
SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'),
TO_DATE('01-01-1992', 'MM-DD-YYYY'))
"Months"
FROM DUAL
Months
----------
1.03225806
SELECT MONTHS_BETWEEN(TO_DATE('02-29-1992', 'MM-DD-YYYY'),
TO_DATE('01-31-1992', 'MM-DD-YYYY')) "Months"
FROM DUAL
Months
----------
1
函數:NEXT_DAY
语法:
NEXT_DAY(date,day)
用途:
该函数返回日期date指定若天后的日期.注意:参数day必须为星期,可以星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分别表示星期日到星期六.例如,查询返回本月最后一个星期五的日期.
例如:
SELECT NEXT_DAY((last_day(sysdate)-7),'FRIDAY')
FROM dual
NEXT_DAY(
---------
28-JAN-00
函數:ROUND
语法:
NEXT_DAY(date[,format])
用途:
该函数把一个日期四舍五入到最接近格式元素指定的形式.如果省略format,只返回date的日期部分.例如,如果想把时间(24/01/00 14:58:41)四舍五入到最近的小时.下表显示了所有可用格式元素对日期的影响.
例如:
SELECT to_char(ROUND(sysdate,'HH'),'DD-MON-YY HH24:MI:SS')
FROM dual
TO_CHAR(ROUND(SYSDATE,'HH'),'DD-MON-YYHH24:MI:SS')
-----------------------------------------------------------------
24-JAN-00 15:00:00
函數:TRUNC
语法:
TRUNC(date[,format])
用途:
TRUNC函数与ROUND很相似,它根据指定的格式掩码元素,只返回输入日期用户所关心的那部分,与ROUND有所不同,它删除更精确的时间部分,而不是将其四舍五入.
例如:
SELECT TRUNC(sysdate)
FROM dual
TRUNC(SYS
---------
24-JAN-00
FLOOR函数：求两个日期之间的天数用；
select floor(sysdate - to_date('20080805','yyyymmdd')) from dual;
字符函数
函數:ASCII
语法:
ASCII(character)
用途:
返回指定字符的ASCII码值.如果为字符串时,返回第一个字符的ASCII码值.
例如:
SELECT ASCII('Z')
FROM dual
ASCII('Z')
----------
90
函數:CHR
语法:
CHR(number)
用途:
该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符.
例如:
SELECT CHR(65)||'BCDEF'
FROM dual
CHR(65
------
ABCDEF
函數:CONCAT
语法:
CONCAT(string1,string2)
用途:
该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符(||).
例如:
SELECT CONCAT(‘This is a’,’ computer’)
FROM dual
CONCAT('THISISA','
------------------
This is a computer
它也可以写成这样:
SELECT ‘This is a’||’ computer’
FROM dual
'THISISA'||'COMPUT
------------------
This is a computer
这两个语句的结果是完全相同的,但应尽可能地使用||操作符.
函數:INITCAP
语法:
INITCAP(string)
用途:
该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母.
例如:
SELECT INITCAP(first_name||'.'||last_name)
FROM employee
WHERE department_id=12
INITCAP(FIRST_NAME||'.'||LAST_N
-------------------------------
Chris.Alberts
Matthew.Fisher
Grace.Roberts
Michael.Douglas
函數:INSTR
语法:
INSTR(input_string,search_string[,n[,m]])
用途:
该函数是从字符串input_string的第n个字符开始查找搜索字符串的第m次出现,如果没有找到搜索的字符串,函数将返回0.如果找到,函数将返回位置.
例如:
SELECT INSTR('the quick sly fox jumped over the
lazy brown dog','the',2,1)
FROM dual
INSTR('THEQUICKSLYFOXJUMPEDOVERTHELAZYBROWNDOG','THE',2,1)
----------------------------------------------------------
31
函數:INSTRB
语法:
INSTRB(input_string,search_string[,n[,m]])
用途:
该函数类似于INSTR函数,不同之处在于INSTRB函数返回搜索字符串出现的字节数,而不是字符数.在NLS字符集中仅包含单字符时,INSTRB函数和INSTR函数是完全相同的.
函數:LENGTH
语法:
LENGTH(string)
用途:
该函数用于返回输入字符串的字符数.返回的长度并非字段所定义的长度,而只是字段中占满字符的部分.以列实例中,字段first_name定义为varchar2(15).
语法:
SELECT first_name,LENGTH(first_name)
FROM employee
FIRST_NAME LENGTH(FIRST_NAME)
--------------- ------------------
JOHN 4
KEVIN 5
函數:LENGTHB
语法:
LENGTHB(string)
用途:
该函数用于返回输入字符串的字节数.对于只包含单字节字符的字符集来说LENGTHB 函数和LENGTH函数完全一样.
函數:LOWER
语法:
LOWER(string)
用途:
该函数将字符串string全部转换为小写字母,对于数字和其它非字母字符,不执行任何转换.
函數:UPPER
语法:
UPPER(string)
用途:
该函数将字符串string全部转换为大写字母,对于数字和其它非字母字符,不执行任何转换.
函數:LPAD
语法:
LPAD(string,length[,’set’])
用途:
在字符串string的左边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.
语法:
SELECT first_name,LPAD(first_name,20,' ')
FROM employee
FIRST_NAME LPAD(FIRST_NAME,20,'')
--------------- -----------------------------------------
JOHN JOHN
KEVIN KEVIN
函數:RPAD
语法:
RPAD(string,length[,’set’])
用途:
在字符串string的右边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.
例如:
SELECT first_name,rpad(first_name,20,'-')
FROM employee
FIRST_NAME RPAD(FIRST_NAME,20,'-')
--------------- -----------------------------------------
JOHN JOHN----------------
KEVIN KEVIN---------------
函數:LTRIM
语法:
LTRIM(string[,’set’])
用途:
该函数从字符串的左边开始,去掉字符串set中的字符,直到看到第一个不在字符串set 中的字符为止.
例如:
SELECT first_name,ltrim(first_name,'BA')
FROM employee
WHERE first_name='BARBARA'
FIRST_NAME LTRIM(FIRST_NAM
--------------- ---------------
BARBARA RBARA
函數:RTRIM
语法:
RTRIM(string[,’set’])
用途:
该函数从字符串的右边开始,去掉字符串set中的字符,直到看到第一个不在字符串set 中的字符为止.具有NULL值的字段不能与具有空白字符的字段相比较.
这是因为空白字符与NULL字符是完全不同的两种字符.该函数的另外一个用途是当进行字段连接时去掉不需要的字符.
函數:SUBSTR
语法:
SUBSTR(string,start[,length])
用途:
该函数从输入字符串中取出一个子串,从start字符处开始取指定长度的字符串,如果不指定长度,返回从start字符处开始至字符串的末尾.
函數:REPLACE
语法:
REPLACE(string,search_set[,replace_set])
用途:
该函数将字符串中所有出现的search_set都替换成replace_set字符串.可以使用该函将字符串中所有出现的符号都替换成某个有效的名字.如果不指定replace_set,则将从字符串string中删除所有的搜索字符串search_set.
例如:
SELECT REPLACE('abcdefbdcdabc,dsssdcdrd','abc','ABC')
FROM dual
REPLACE('ABCDEFBDCDABC,
-----------------------
ABCdefbdcdABC,dsssdcdrd
函數:TRANSLATE
语法:
TRANSLATE(string,search_set,replace_set)
用途:
该函数用于将所有出现在搜索字符集search_set中的字符转换成替换字符集replace_set中的相应字符.注意:如果字符串string中的某个字符没有出现在搜索字符集中.则它将原封不动地返回.如果替换字符集replace_set比搜索字符集search_set小,那么搜索字符集search_set中后面的字符串将从字符串string中删除.
例如:
SELECT TRANSLATE('GYK-87M','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 9999999999xxxxxxxxxxxxxx')
FROM dual
TRANSL
------
xx-99x
数值函数
函數:ABS
语法:
ABS(number)
用途:
该函数返回数值number的绝对值.绝对值就是一个数去掉符号的那部分. 函數:SQRT
语法:
SQRT(number)
用途:
该函数返回数值number的平方根,输入值必须大于等于0,否则返回错误.
函數:CEIL
语法:
CEIL(number)
用途:
该函数返回大于等于输入值的下一个整数. 函數:FLOOR
语法:
FLOOR(number)
用途:
该函数返回小于等于number的最大整数.
函數:MOD
语法:
MOD(n,m)
用途:
该函数返回n除m的模,结果是n除m的剩余部分.m,n可以是小数,负数. 函數:POWER
语法:
POWER(x,y)
用途:
该函数执行LOG函数的反操作,返回x的y次方.
函數:ROUND
语法:
ROUND(number,decimal_digits)
用途:
该函数将数值number四舍五入到指定的小数位.如果decimal_digits为0,则返回整数.decimal_digits可以为负数.
函數:TRUNC
语法:
TRUNC(number[,decimal_pluces])
用途:
该函数在指定的小数字上把一个数值截掉.如果不指定精度,函数预设精度为0. decimal_pluces可以为负数.
函數:SIGN
语法:
SIGN(number)
用途:
该函数返回number的符号,如果number为正数则返回1,为负数则返回-1,为0则返回0.
函數:SIN
语法:
SIN(number)
用途:
该函数返回弧度number的正弦值.
函數:SINH
语法:
SINH(number)
用途:
该函数返回number的返正弦值.
函數:COS
语法:
COS(number)
用途:
该函数返回弧度number的三角余弦值.要用角度计算余弦,可以将输入值乘以0.01745转换成弧度后再计算.
函數:COSH
语法:
COSH(number)
用途:
该函数返回输入值的反余弦值.
函數:TAN
语法:
TAN(number)
用途:
该函数返回弧度number的正切值. 函數:TANH
语法:
TANH(number)
用途:
该函数返回数值number的反正切值. 函數:LN
语法:
LN(number)
用途:
该函数返回number自然对数.
函數:EXP
语法:
EXP(number)
用途:
该函数返回e(2.71828183)的number次方.该函数执行自然对数的反过程. 函數:LOG
语法:
LOG(base,number)
用途:
该函数返回base为底,输入值number的对数.
单行函数:
单行函数中可以对任何数据类型的数据进行操作.
函數:DUMP
语法:
DUMP(expression[,format[,start[,length]]])
用途:
该函数按指定的格式显示输入数据的内部表示.下表列出了有效的格式.
例如:
SELECT DUMP('FARRELL',16)
FROM dual
DUMP('FARRELL',16)
----------------------------------
Typ=96 Len=7: 46,41,52,52,45,4c,4c
函數:GREATEST
语法:
GREATEST(list of values)
用途:
该函数返回列表中项的最大值.对数值或日期来说,返回值是最大值或最晚日期,如果列表中包含字符串,返回值是按字母顺序列表中的最后一项.
例如:
SELECT GREATEST(123,234,432,112)
FROM dual
GREATEST(123,234,432,112)
-------------------------
432
函數:LEAST
语法:
LEAST(list of values)
用途:
该函数返回列表中项的最小值.对数值或日期来说,返回值是最小值或最早日期,如果列表中包含字符串,返回值是按字母顺序列表中的第一项.
例如:
SELECT LEAST(sysdate,sysdate-10)
FROM dual
LEAST(SYS
---------
10-JAN-00
函數:NVL
语法:
NVL(expression,replacement_value)
用途:
如果表达式不为空值,函数返回该表达式的值,如果是空值,就返回用来替换的值.
例如:
SELECT last_name,
NVL(TO_CHAR(COMMISSION),'NOT APPLICABLE')
FROM employee
WHERE department_id=30
LAST_NAME NVL(TO_CHAR(COMMISSION),'NOTAPPLICABLE')
--------------- ----------------------------------------
ALLEN 300
WARD 500
MARTIN 1400
BLAKE NOT APPLICABLE
多行函数
组函数可以对表达式的所有值操作,也可以只对其中不同值进行操作,组函数的语法如下所示:
function[DISTINCT|ALL expression]
如果既不指定DISTINCT,也不指定ALL,函数将对查询返回的所有数据行进行操作.不能在同一个SELECT语句的选择列中同时使用组函数和单行函数.。