SQL PL/SQL语法手册

SQL PL/SQL语法

目 录

第一部分 SQL语法部分................................................................................................................................................. 3

一、 CREATE TABLE 语句.............................................................................................................................. 3

二、 CREATE SEQUENCE语句........................................................................................................................ 5

三、 CREATE VIEW语句................................................................................................................................. 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

六、 PACKAGE.................................................................................................................................................. 54

七、 触发器..................................................................................................................................................... 56

八、 应用实例................................................................................................................................................. 58

第一部分 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语句中,本 选项保证在每次插入或更新数据后,该数据可以在视 图中查到

例:

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;

四、 INSERT语句:

1.语法

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

[ (column [, column] ...) ]

{VALUES (expr [, expr] ...) | subquery}

[WHERE condition]

2.插入单行

使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL关键词将其值设为空值(两个连续的逗号也可以表示空值,也可使用NULL关键词)

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

举例:

插入一条记录到DEPARTMENT表中

INSERT INTO DEPARTMENT

(DEPARTMENT_ID,NAME,LOCATION_ID)

VALUES (01,’COMPUTER’,167)

3.插入多行

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

举例:

INSERT INTO ORDER_TEMP

SELECT 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 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

4.从其它表复制数据:

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

CREATE TABLE EMPLOYEE_COPY

AS

SELECT *

FROM EMPLOYEE

五、 UPDATE语句:

1.语法

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语句:

1.语法

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

[WHERE condition]

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

举例:

DELETE FROM item

WHERE ORDER_ID=510

七、 TRUNCATE语句:

1.语法

TRUNCATE [schema.]table

八、 各类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之间)