oraclePLSQL语法详细手册.docx

oraclePLSQL语法详细手册.docx

《oraclePLSQL语法详细手册.docx》由会员分享，可在线阅读，更多相关《oraclePLSQL语法详细手册.docx（96页珍藏版）》请在冰点文库上搜索。

oraclePLSQL语法详细手册

oraclePLSQL语法详细手册

一、CREATETABLE语句3

二、CREATESEQUENCE语句5

三、CREATEVIEW语句6

四、INSERT语句:

7

五、UPDATE语句:

9

六、DELETE语句:

10

七、TRUNCATE语句:

11

八、各类FUNCTIONS:

12

1.转换函数:

12

2.日期函数16

3.字符函数20

4.数值函数28

5.单行函数:

33

6.多行函数35

第二部分PL/SQL语法部分41

一、PL/SQL语言简介41

二、变量讲明48

三、PL/SQL操纵程序流52

四、储备过程54

五、储备函数54

六、PACKAGE54

七、触发器56

八、应用实例58

1、Createtable语句

语句:

CREATETABLE[schema.]table_name

（{columndatatype[DEFAULTexpr][column_constraint]...

|table_constraint}

[,{columndatatype[DEFAULTexpr][column_constraint]...

|table_constraint}]...）

[[PCTFREEinteger][PCTUSEDinteger]

[INITRANSinteger][MAXTRANSinteger]

[TABLESPACEtablespace]

[STORAGEstorage_clause]

[RECOVERABLE|UNRECOVERABLE]

[PARALLEL（[DEGREE{integer|DEFAULT}]

[INSTANCES{integer|DEFAULT}]

）

|NOPARALLEL]

[CACHE|NOCACHE]

|[CLUSTERcluster（column[,column]...）]]

[ENABLEenable_clause

|DISABLEdisable_clause]...

[ASsubquery]

表是Oracle中最重要的数据库对象,表储备一些相似的数据集合,这些数据描述成若干列或字段.createtable语句的差不多形式用来在数据库中创建容纳数据行的表.createtable语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还能够指定约束条件,储备参数和该表是否是个cluster的一部分.

Schema用来指定所建表的owner,如不指定则为当前登录的用户.

Table_name用来指定所创建的表名,最长为30个字符,但不能够数字开头（可为下划线）,但不可同其它对象或Oracle的保留字冲突.

Column用来指定表中的列名,最多254个.

Datatype用来指定列中储备什么类型的数据,并保证只有有效的数据才能够输入.

column_constraint用来指定列约束,如某一列不可为空,则可指定为notnull.

table_constraint用来指定表约束,如表的主键,外键等.

Pctfree用来指定表中数据增长而在Oracle块中预留的空间.DEFAULT为10%,也确实是讲该表的每个块只能使用90%,10%给数据行的增大时使用.

Pctused用来指定一个水平线,当块中使用的空间低于该水平线时才能够向该中加入新数据行.

Parallel用来指定为加速该表的全表扫描能够使用的并行查询进程个数.

Cache用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.

Cluster用来指定该表所储备的cluster.

Tablespace用来指定用数据库的那个分区来储备该表的数据.

Recoverable|Unrecoverable用来决定是否把对本表数据所作的变动写入Redo文件.以复原对数据的操作.

As当不指定表的各列时,可利用As子句的查询结果来产生数据库结构和数据.

例:

1）createtablemytab1e（mydecdecimal,

myintinteter）

tablespaceuser_data

pctfree5

pctused30;

2）createtablemytable2

as（select*frommytable1）;

2、

createsequence语句

语句:

CREATESEQUENCE[schema.]sequence_name

[INCREMENTBYinteger]

[STARTWITHinteger]

[MAXVALUEinteger|NOMAXVALUE]

[MINVALUEinteger|NOMINVALUE]

[CYCLE|NOCYCLE]

[CACHEinteger|NOCACHE]

[ORDER|NOORDER]

序列用来为表的主键生成唯独的序列值.

Incrementby指定序列值每次增长的值

Maxvalue指定产生的序列的最大值

Minvalue指定产生的序列的最小值

Cycle指定当序列值逵到最大或最小值时,该序列是否循环.

Cache指定序列生成器一次缓存的值的个数

Order指定序列中的数值是否按访咨询顺序排序.

例:

1）createsequencemyseq

incrementby4

startwith50

maxvalue60

minvalue50

cycle

cache3;

2）

sql>createsequencenew_s;

sql>insertintonew（new_id,last_name,first_name）

values（new_s.nextval,’daur’,’permit’）;

3、

createview语句

语句:

CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[schema.]view_name

[（alias[,alias]...）]

ASsubquery

[WITHCHECKOPTION[CONSTRAINTconstraint]]

视图实际上是储备在数据库上旳select语句.每次在sql语句中使用视图时,表示该视图的select语句就用来得到需要的数据.

Orreplace创建视图时假如视图已存在,有此选项,新视图会覆盖旧的

视图.

Force如有此选项,当视图基于的表不存在或在该模式中没有创建视图的权限时,也能够建立视图.

Assubquery产生视图的select查询语句

Withcheckoption假如视图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本选项保证在每次插入或更新数据后,该数据能够在视图中查到

例:

1）createorplaceviewnew_v

as

selectsubstr（d.d_last_name,1,3）,

d.d_lastname,d.d_firstname,b.b_start_date,b.b_location

fromnew1d,

new2b

whered.d_lastname=b.b_lastname;

4、

INSERT语句:

1.语法

INSERTINTO[schema.]{table|view|subquery}[@dblink]

[（column[,column]...）]

{VALUES（expr[,expr]...）|subquery}

[WHEREcondition]

2.插入单行

使用VALUES关键词为新行的每一列指定一个值.假如不明白某列的值,能够使用NULL关键词将其值设为空值（两个连续的逗号也能够表示空值,也可使用NULL关键词）

插入一行时试图为那些NOTNULL的列提供一个NULL值,会返回错误信息.

举例:

插入一条记录到DEPARTMENT表中

INSERTINTODEPARTMENT

（DEPARTMENT_ID,NAME,LOCATION_ID）

VALUES（01,’COMPUTER’,167）

3.插入多行

将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.

举例:

INSERTINTOORDER_TEMP

SELECTA.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

FROMSALES_ORDERA,ITEMB,CUSTOMERC,

PRODUCTD,EMPLOYEEE

WHEREMONTHS_BETWEEN（TO_DATE（A.ORDER_DATE）,TO_DATE（'01-7月-91'））>0

ANDA.CUSTOMER_ID=C.CUSTOMER_ID

ANDC.SALESPERSON_ID=E.EMPLOYEE_ID

ANDA.ORDER_ID=B.ORDER_ID

ANDB.PRODUCT_ID=D.PRODUCT_ID

4.从其它表复制数据:

要快速地从一个表向另一个尚不存在的表复制数据,能够使用CREATETABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.

CREATETABLEEMPLOYEE_COPY

AS

SELECT*

FROMEMPLOYEE

5、

UPDATE语句:

1.语法

UPDATE[schema.]{table|view|subquery}[@dblink][alias]

SET{（column[,column]...）=（subquery）

|column={expr|（subquery）}}

[,{（column[,column]...）=（subquery）

|column={expr|（subquery）}}]...

[WHEREcondition]

UPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句能够用SELECT语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据,均要执行一次SELECT语句.

举例:

UPDATEEMPLOYEE_COP

SETSALARY=

SALARY-400

WHERETO_NUMBER（TO_CHAR（HIRE_DATE,'YYMMDD'））<850101

UPDATEITEM_COPA

SETA.ACTUAL_PRICE=

（

SELECTB.LIST_PRICE

FROMPRICEB,SALES_ORDERC

WHEREA.PRODUCT_ID=B.PRODUCT_IDAND

A.ORDER_ID=C.ORDER_IDAND

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）

）

6、

DELETE语句:

1.语法

DELETE[FROM][schema.]{table|view}[@dblink][alias]

[WHEREcondition]

DELETE语句删除所有满足WHERE子句条件的数据行.

举例:

DELETEFROMitem

WHEREORDER_ID=510

7、

TRUNCATE语句:

1.语法

TRUNCATE[schema.]table

8、

各类Functions:

1.转换函数:

1.1.函數:

TO_CHAR

语法:

TO_CHAR（number[,format]）

用途:

将一个数值转换成与之等价的字符串.假如不指定格式,将转换成最简单的字符串形式.假如为负数就在前面加一个减号.

Oracle为数值提供了专门多格式,下表列出了部分可同意的格式:

元素

描述

示例

值

结果

9

返回指定位数的数值,前面为0,显示为空格

99999

784

-578

1234567

45.895

‘784’

‘-578’

‘######’

‘46’

插入小数点

9999.99

784

45.3482

‘784.00’

’45.35’

在结果串的指定位置插入逗号

9,999,999

784

1234567

0.44

‘784’

‘1,234,567’

‘0’

$

返回值前面加一个元符号

$99,999

784

-578

‘$784’

‘-$578’

B

结果的整数部分假如是0,就显示成空格

B9999.9

784

0.44

‘784.0’

‘.4’

S

返回有正负号的数值

S9999

784

‘+784’

EEEE

以科计数法表示数值

9.9EEEE

45

0.0666

‘4.5E+01’

‘6.7E-02’

1.2.

函數:

TO_CHAR

语法:

TO_CHAR（date[,format]）

用途:

将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,用户能够用它们的组合来表示最终的输出格式.唯独限制确实是最终的掩码不能超过22个字符.下表列出了部分日期格式化元素.

Oracle为数值提供了专门多格式,下表列出了部分可同意的格式:

格式

意义

D

用数字表示星期几（1,2,3,4,5,6,7）

DY

用三个字符表示星期几的缩写形式（MON）

DAY

星期几的完整表示（MONDAY）

DD

用数字表示一月中的几号

DDD

用数字表示年份中的天数

W

一月中的星期数

WW

一年中的星期数

MM

用数字表示月数

MON

用三个字母表示月的缩写（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的日期值.

该函数的要紧用途是用来验证输入的日期值.在应用程序中,用户必须验证输入日期是否有效,如月份是否在1~12之间和日期中的天数是否在指定月份的天数内.

1.4.

函數:

TO_NUMBER

语法:

TO_NUMBER（string[,format]）

用途:

该函数将一个字符串转换成相应的数值.关于简单的字符串转换数值（例如几位数字加上小数点）.格式是可选的.

2.

日期函数

2.1.函數:

ADD_MONTHS

语法:

ADD_MONTHS（date,number）

用途:

在日期date上加指定的月数,返回一个新日期.假如给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,假如是小数,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.

例如:

SELECTTO_CHAR（ADD_MONTHS（sysdate,1）,

'DD-MON-YYYY'）"Nextmonth"

FROMdual

Nextmonth

-----------

19-FEB-2000

2.2.函數:

LAST_DAY

语法:

LAST_DAY（date）

用途:

返回日期date所在月份的最后一天的日期.

例如:

SELECTSYSDATE,LAST_DAY（SYSDATE）"Last",

LAST_DAY（SYSDATE）-SYSDATE"DaysLeft"

FROMDUAL

SYSDATELastDaysLeft

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

19-JAN-0031-JAN-0012

2.3.函數:

MONTHS_BETWEEN

语法:

MONTHS_BETWEEN（date1,date2）

用途:

例如:

SELECTMONTHS_BETWEEN（TO_DATE（'02-02-1992','MM-DD-YYYY'）,

TO_DATE（'01-01-1992','MM-DD-YYYY'））

"Months"

FROMDUAL

Months

----------

1.03225806

SELECTMONTHS_BETWEEN（TO_DATE（'02-29-1992','MM-DD-YYYY'）,

TO_DATE（'01-31-1992','MM-DD-YYYY'））

"Months"

FROMDUAL

Months

----------

1

2.4.函數:

NEXT_DAY

语法:

NEXT_DAY（date,day）

用途:

该函数返回日期date指定若天后的日期.注意:

参数day必须为星期,能够星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分不表示星期日到星期六.例如,查询返回本月最后一个星期五的日期.

例如:

SELECTNEXT_DAY（（last_day（sysdate）-7）,'FRIDAY'）

FROMdual

NEXT_DAY（

---------

28-JAN-00

2.5.函數:

ROUND

语法:

NEXT_DAY（date[,format]）

用途:

该函数把一个日期四舍五入到最接近格式元素指定的形式.假如省略format,只返回date的日期部分.例如,假如想把时刻（24/01/0014:

58:

41）四舍五入到最近的小时.下表显示了所有可用格式元素对日期的阻碍.

FormatElement

Result

SS

24/01/0014:

58:

41

MI

24/01/0014:

59:

00

HH

24/01/0015:

00:

00

DD

25/01/0000:

00:

00

MM

01/02/0100:

00:

00

YY

01/01/0000:

00:

00

CC

01/01/0000:

00:

00

例如:

SELECTto_char（ROUND（sysdate,'HH'）,'DD-MON-YYHH24:

MI:

SS'）

FROMdual

TO_CHAR（ROUND（SYSDATE,'HH'）,'DD-MON-YYHH24:

MI:

SS'）

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

24-JAN-0015:

00:

00

2.6.函數:

TRUNC

语法:

TRUNC（date[,format]）

用途:

TRUNC函数与ROUND专门相似,它依照指定的格式掩码元素,只返回输入日期用户所关怀的那部分,与ROUND有所不同,它删除更精确的时刻部分,而不是将其四舍五入.

例如:

SELECTTRUNC（sysdate）

FROMdual

TRUNC（SYS

---------

24-JAN-00

FLOOR函数：

求两个日期之间的天数用；

selectfloor（sysdate-to_date（'20180805','yyyymmdd'））fromdual;

3.

字符函数

3.1.函數:

ASCII

语法:

ASCII（character）

用途:

例如:

SELECTASCII（'Z'）

FROMdual

ASCII（'Z'）

----------

90

3.2.函數:

CHR

语法:

CHR（number）

用途:

该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符.

例如:

SELECTCHR（65）||'BCDEF'

FROMdual

CHR（65

------

ABCDEF

3.3.

函數:

CONCAT

语法:

CONCAT（string1,string2）

用途:

该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符（||）.

例如:

SELECTCONCAT（‘Thisisa’,’computer’）

FROMdual

CONCAT（'THISISA','

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

Thisisacomputer

它也能够写成如此:

SELECT‘Thisisa’||’computer’

FROMdual

'THISISA'||'COMPUT

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

Thisisacomputer

这两个语句的结果是完全相同的,但应尽可能地使用||操作符.

3.4.函數:

INITCAP

语法:

INITCAP（string）

用途:

该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母.

例如:

SELECTINITCAP（first_name||'.'||last_name）

FROMemployee

WHEREdepartment_id=12

INITCAP（FIRST_NAME||'.'||LAST_N

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

Chris.Alberts

Matthew.Fisher

Grace.Roberts

Michael.Douglas

3.5.函數:

INSTR

语法:

INST