oraclePLSQL语法详细手册剖析Word文档下载推荐.docx

1、 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

2、 语句的基本形式用来在数据库中创建容纳数据行的表.create table 语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还可以指定约束条件,存储参数和该表是否是个cluster的一部分.Schema 用来指定所建表的owner,如不指定则为当前登录的用户.Table_name 用来指定所创建的表名,最长为30个字符,但不可以数字开头（可为下划线）,但不可同其它对象或Oracle的保留字冲突. Column 用来指定表中的列名,最多254个.Datatype 用来指定列中存储什么类型的数据,并保证只有有效的数据才可以输入.column_constraint 用来指定列约束,

3、如某一列不可为空,则可指定为not null.table_constraint 用来指定表约束,如表的主键,外键等.Pctfree 用来指定表中数据增长而在Oracle块中预留的空间. DEFAULT为10%,也就是说该表的每个块只能使用90%,10%给数据行的增大时使用.Pctused 用来指定一个水平线,当块中使用的空间低于该水平线时才可以向该中加入新数据行.Parallel 用来指定为加速该表的全表扫描可以使用的并行查询进程个数.Cache 用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.Cluster 用来指定该表所存储的 cluster.Tablespace 用来指定用数据库

4、的那个分区来存储该表的数据.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）;2、 create sequence语句 CREATE SEQU

5、ENCE schema.sequence_nameINCREMENT 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 指定当序列值逵到最大或最

6、小值时,该序列是否循环.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;insert into new （new_id,last_name,first_name） values（new_s.nextval,daur,permit）;3、 create view语句 CREATE OR REPLACE FORCE

7、 | 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 如果视

8、图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本 选项保证在每次插入或更新数据后,该数据可以在视 图中查到1） 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;4、 INSERT语句:1. 语法INSERT INTO schema.table | view

9、 | subquery dblink （column , column .） VALUES （expr , expr .） | subquery WHERE condition2. 插入单行使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL关键词将其值设为空值（两个连续的逗号也可以表示空值,也可使用NULL关键词）插入一行时试图为那些NOT NULL的列提供一个NULL值,会返回错误信息.举例:插入一条记录到DEPARTMENT表中INSERT INTO DEPARTMENT （DEPARTMENT_ID,NAME,LOCATION_ID）VALUES （01

10、,COMPUTER,167）3. 插入多行将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.INSERT INTO ORDER_TEMPSELECT A.ORDER_ID,B.ITEM_ID,C.NAME,E.FIRST_NAME|.|E.LAST_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 MO

11、NTHS_BETWEEN（TO_DATE（A.ORDER_DATE）,TO_DATE（01-7月-91） 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_ID4. 从其它表复制数据:要快速地从一个表向另一个尚不存在的表复制数据,可以使用CREATE TABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.CREATE TABLE EMPLOYEE_COPY AS SELECT * FROM

12、EMPLOYEE5、 UPDATE语句:UPDATE schema.table | view | subquerydblink alias SET （column , column .） = （subquery） | column = expr | （subquery） , （column , column .） = （subquery） | column = expr | （subquery） .UPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句可以用SELECT语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据,均要执行一次SELECT语句.

13、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（T

14、O_CHAR（B.START_DATE,） AND NVL（TO_NUMBER（TO_CHAR（END_DATE,）,29991231）6、 DELETE语句:DELETE FROM schema.table | viewdblink aliasDELETE语句删除所有满足WHERE子句条件的数据行.DELETE FROM itemWHERE ORDER_ID=5107、 TRUNCATE语句:TRUNCATE schema.table8、 各类Functions:1.1. 函數:TO_CHAR语法: TO_CHAR（number,format）用途:将一个数值转换成与之等价的字符串.如果不指

15、定格式,将转换成最简单的字符串形式.如果为负数就在前面加一个减号.Oracle为数值提供了很多格式,下表列出了部分可接受的格式:元素描述示例值结果9返回指定位数的数值,前面为0,显示为空格99999784-578123456745.895 784 -578# 46插入小数点9999.9945.3482784.0045.35在结果串的指定位置插入逗号9,999,9990.44 1,234,567 0$返回值前面加一个元符号$99,999 $784 -$578B结果的整数部分如果是0,就显示成空格B9999.9 784.0 .4S返回 有正负号的数值S9999+784EEEE以科 计数法表示数值9

16、.9EEEE450.0666 4.5E+01 6.7E-021.2. 函數: TO_CHAR（date,format）将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,用户可以用它们的组合来表示最终的输出格式.唯一限制就是最终的掩码不能超过22个字符.下表列出了部分日期格式化元素.格式意义D用数字表示星期几（1,2,3,4,5,6,7）DY用三个字符表示星期几的缩写形式（MON）DAY星期几的完整表示（MONDAY）DD用数字表示一月中的几号DDD用数字表示年份中的天数W一月中的星期数WW一年中的星期数MM用数字表示月数MON用三个字母表示月的

17、缩写（APR）MONTH月的完整英文表示（FEBRUARY）Y年份中的最后一个数字（9）YY年份中的最后二个数字（99）YYY年份中的最后三个数字（999）YYYY年份用四个数字表示（1999）YEAR年份的字母全部拼写（NINETEEN-NINETY-NINE）AM或PM午前或午后表示符HH用小时表示日期MI分钟表示小时SS秒钟表示分钟SSSS自午夜以来的秒数（这个数字经常在0-86399之间）1.3. TO_DATE TO_DATE（string,format）根据给定的格式将一个字符串转换成Oracle的日期值.该函数的主要用途是用来验证输入的日期值.在应用程序中,用户必须验证输入日期是

18、否有效,如月份是否在112之间和日期中的天数是否在指定月份的天数内.1.4. TO_NUMBER TO_NUMBER（string,format）该函数将一个字符串转换成相应的数值.对于简单的字符串转换数值（例如几位数字加上小数点）.格式是可选的.2. 日期函数2.1. 函數:ADD_MONTHS ADD_MONTHS（date,number）在日期date上加指定的月数,返回一个新日期.如果给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,如果是小数,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.例如: SELECT TO_CHAR（ADD_MONTH

19、S（sysdate,1）, DD-MON-YYYY） Next month FROM dualNext month-19-FEB-20002.2. 函數:LAST_DAY LAST_DAY（date）返回日期date所在月份的最后一天的日期. SELECT SYSDATE, LAST_DAY（SYSDATE） Last, LAST_DAY（SYSDATE） - SYSDATE Days Left FROM DUALSYSDATE Last Days Left- - -19-JAN-00 31-JAN-00 122.3. 函數:MONTHS_BETWEEN MONTHS_BETWEEN（date

20、1,date2）返回两个日期之间的月份.如果两个日期月份内的天数相同（或者都是某个月的最后一天）,返回值是整数.否则,返回值是小数,每于1/31月来计算月中剩余天数.如果第二个日期比第一个日期还早,则返回值是负数. SELECT MONTHS_BETWEEN（TO_DATE（02-02-1992, MM-DD-YYYY）, TO_DATE（01-01-1992） Months Months - 1.0322580602-29-199201-31-1992- 12.4. 函數:NEXT_DAY NEXT_DAY（date,day）该函数返回日期date指定若天后的日期.注意:参数day必须为星期

21、,可以星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分别表示星期日到星期六.例如,查询返回本月最后一个星期五的日期. SELECT NEXT_DAY（last_day（sysdate）-7）,FRIDAY）NEXT_DAY（-28-JAN-002.5. 函數:ROUND NEXT_DAY（date,format）该函数把一个日期四舍五入到最接近格式元素指定的形式.如果省略format,只返回date的日期部分.例如,如果想把时间（24/01/00 14:58:41）四舍五入到最近的小时.下表显示了所有可用格式元素对日期的影响.Format ElementResult2

22、4/01/00 14:4159:0024/01/00 15:00:25/01/00 00:01/02/01 00:01/01/00 00:CC SELECT to_char（ROUND（sysdate,HH）,DD-MON-YY HH24:MI:SSTO_CHAR（ROUND（SYSDATE,DD-MON-YYHH24:-24-JAN-00 15:2.6. 函數:TRUNC TRUNC（date,format）TRUNC函数与ROUND很相似,它根据指定的格式掩码元素,只返回输入日期用户所关心的那部分,与ROUND有所不同,它删除更精确的时间部分,而不是将其四舍五入. SELECT TRUNC

23、（sysdate）TRUNC（SYS24-JAN-00FLOOR函数：求两个日期之间的天数用；select floor（sysdate - to_date（20080805,yyyymmdd） from dual;3. 字符函数3.1. 函數:ASCIIASCII（character）返回指定字符的ASCII码值.如果为字符串时,返回第一个字符的ASCII码值. SELECT ASCII（ZASCII（ 903.2. 函數:CHRCHR（number）该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符. SELECT CH

24、R（65）|BCDEFCHR（65-ABCDEF3.3. CONCATCONCAT（string1,string2）该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符（|）. SELECT CONCAT（This is a, computer）CONCAT（THISISA-This is a computer它也可以写成这样: SELECT This is a| computer|COMPUT这两个语句的结果是完全相同的,但应尽可能地使用|操作符.3.4. 函數:INITCAPINITCAP（string）该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母. SELECT INITCAP（firs