ORACLE 8i
基本语法
一.
创建表空间
create tablespace 表空间名
datafile {文件名 [autoextend {off | on next 数值 maxsize 数值}],
……
}
minimum extent 数值
logging | nologging
default storge (……)
online | offline
permanent | temporary;
参数含义
1. datafile: 为此表空间指定数据文件的名字
2. autoextend:使自动扩展数据文件为有效或无效，并在有效时指定next值为下次分配给数据文件的磁盘空间，maxsize为可分配的最大磁盘控件，若用unlimited则表示不受限制
3. minimum extent:控制表空间的自由空间片，方法是保证表空间中每个使用的或自由的扩充尺寸至少是指定数值的倍数
4. logging | nologging:指定在表空间中的所有表、索引和分区的缺省日志使用方式，其中logging表示对操作做日志，nologging表示对数据操作不作日志
5. default storage:为在表空间中建立的全部对象指定缺省的存储参数
6. online | offline:使表空间联机或脱机
7. permanent | temporary:指定表空间用来包含永久对象或暂时对象
举例
create tablespace test
datafile ‘/Orcale/data/test01.dbf’ 5M,
‘/ Orcale/data/test02.dbf’ 5M
default storage (initial 5k
next 5k
minextents 2
maxextentes 10
pctincreace 50)
online;
二.
增加表空间
alter tablespace 表空间名
logging | nologging
add datafile {数据文件名 [autoextend], ……}
rename datafile 文件名to 文件名
coalesce
default storage
minimum extent 数值
online | offline [normal | temporary | immediate | for recover]
{begin | end} backup
read only | write
permanent | temporary
1. add datafile:增加表空间的数据文件 它同样可带autoextend选项
2. coalesce:为表空间中的每个数据文件，将所有相连的控件扩充合并到相邻的较大的扩充中去，这一选项不能被其他命令指定
3. {begin | end} backup:开始和结束联机备份表空间中的数据文件，在备份过程中用户可以继续访问该表空间，但备份过程中不能将表空间脱机，也不能关闭数据库
4. read only | write:其中read only表示此表空间内容是只读的，不能在将来向其中写入任何东西，而read write则表示表空间可读可写
举例
alter tablespace test
add datafile ‘/Orcale/data/test03.dbf’ 10M,
‘/ Orcale/data/test04.dbf’ 10M
online;
三.
创建表
表是建立在表空间下的
create [or replace] table <表名>
(<列名1> 类型 [NULL | NOT NULL],
<列名2> 类型 [NULL | NOT NULL],
……
);
复制表
create table <新表名> as
select <列名1>,<列名2>,…<列名n>
from <旧表名>
where <约束条件> [可选]
复制表结构
若只想复制一个表结构，只要在where 子句中指定一个永假值即可
create table <新表名> as z
select <列名1>,<列名2>,…<列名n>
from <旧表名>
where 1= 2
四.
查看表结构
describe <表名>
五.
修改表结构
Ø
增加列
alter table <表名>
add (<列名1> 类型 [约束条件],
<列名2> 类型 [约束条件],
……
);
举例
alter table PERSON_NAME
add （ADDRESS char(10)）;
Ø
修改列
alter table <表名>
modify (<列名1> 类型 [约束条件],
<列名2> 类型 [约束条件],
……
);
举例
alter table PERSON_NAME
modify （ADDRESS char(10)）;
六.
创建主键、候选键、外键
1.
和表一起创建
Ø
主键
create table persons
(
…
PRIMARY KEY (列1,列2,…列n) );
Ø
候选键
create table persons
(
…
UNIQUE
(列1,列2,…列n) );
Ø
外键
create table persons
(…
FOREIGN KEY (列1,列2,…列n) references 表名 on cascade);
2.
建表后创建
alter table <表名>
add(PRIMARY
KEY (列名)
UNIQUE( 列名1,列名2,…列名n ));
删除键
Ø 候选键删除：drop UNIQUE (列名1,列名2,…列名n);
Ø 主外键删除：drop PRIMARY KEY CASCADE
Ø CASCADE:表示将把这个主键级联的所有依赖于它的外键都删除
七.
创建序列
create sequence 序列名
start with 数值
increment by 数值
nominvalue | minvalue 数值
nomaxvalue | maxvalue 数值
cycle或 nocycle
cache 或 nocache
举例
create sequence persons_sequence
start with 1
increment by 1
nominvalue
nomaxvalue
nocycle
nocache
八.
创建索引
create [unique] index <索引名>
on table 表名(列名1，列名2……)
where 查询条件;
unique:用来建立唯一索引的参数
备注，创建索引列的列值不能有NULL
九.
创建视图
create [or replace] view <视图名>
as
select <列名1>,<列名2>……<列名n>
from <表1>,<表2>……<表n>
where <约束条件>;
举例
create or replace view vw_PERSONS as
select * from PERSONS;
十.
修改视图数据
修改视图数据的限制
1) 用group by、distinct、或伪装列定义的视图不能进行修改操作
2) 对于包含函数或用计算得来的列不能修改
3) 对于多个表组合而成的视图，也是不允许修改其中的列值
十一.
创建游标
curson <游标名>
is
select <列>
from <表>
where <条件>
for <操作类型>；
打开游标
open <游标名>;
关闭游标
close <游标名>;
判断游标打开状态
<游标名>%ISOPEN
判断游标是否结束
<游标名>%NOTFOUND
或 not <游标名>%FOUND
举例
declare
person_no number(5),
person_name char(10),
person_sex char(1),
curson person is
select NO,NUME,SEX from PERSONS WHERE NO < 98050
begin
open person
fetch person into person_no, person_name, person_sex
loop
exit when NOT person%FOUND
IF in_sex = ‘M’ THEN
INSERT INTO man
Values(person_no, person_name);
ELSE
INSERT INTO woman
Values(person_no, person_name);
END IF;
end loop;
close person
end;
十二.
创建过程
create [or repalce] procedure <过程名>
(<参数1>，[参数类型1] <数据类型1>,
参数2>，[参数类型2] <数据类型2>,
……)
is | as
BEGIN
PL/SQL过程体
End <过程名>;
举例
create procedure count_num
( in_sex
in person.SEX%TYPE, --In 输入参数
out_num out NUMBER) --out 输出参数
is
begin
IF in_sex = ‘M’ THEN
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘M’;
ELSE
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘W’;
END IF;
end count_num;
十三.
创建函数方法
create [or replace] function <函数名>
(<参数1>，[参数类型1] <数据类型1>,
参数2>，[参数类型2] <数据类型2>,
……)
return <数据类型>
is | as
begin
PL/SQL语句
--其中必须要有一个return 字句
end <函数名>;
举例
create function count_num
(in_sex in PERSON.SEX%TYPE) --输入参数
return NUMBER
is
out_num NUMBER
begin
IF in_sex = ‘M’ THEN
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘M’;
ELSE
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘W’;
END IF;
return (out_num);
end count_num;
十四.
创建包
包由包描述＋包体组成
包描述 可以定义变量，游标，函数，过程等
包体包描述部分的具体定义。
Ø
包描述语法：
create packag <包名>
is
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表及返回类型;
end <包名>;
Ø
包体语法：
create package body <包名>
is
游标、函数、过程的具体定义；
end <包名>;
举例
create package my_package
is
man_num NUMBER;
woman_num NUMBER;
curson person;
create function F_ count_num (in_sex in PERSONS.SEX%TYPE)
return NUMBER;
create procedure P_count_num
(in_sex in PERSONS.SEX%TYPE,
out_num out NUMBER):
end my_package;
create package body my_packages
as
--游标的具体定义
curson person is
select NO,NUME,SEX from PERSONS WHERE NO < 98050;
--函数的具体定义
function F_ count_num
(in_sex in PERSON.SEX%TYPE)
--输入参数
return NUMBER
is
out_num NUMBER
begin
IF in_sex = ‘M’ THEN
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘M’;
ELSE
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘W’;
END IF;
return (out_num)
end F_count_num;
--过程的具体定义
procedure P_count_num
( in_sex
in person.SEX%TYPE, --In 输入参数
out_num out NUMBER) --out 输出参数
is
begin
IF in_sex = ‘M’ THEN
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘M’;
ELSE
SELECT COUNT(sex) into out_num from persons WHERE SEX = ‘W’;
END IF;
end P_count_num;
十五.
创建同义名
create [public] synonym 同义名 for 对象
Ø public: 表示是公共同义名，所有用户都可以引用，若省略不写，则缺省是private,即私有同义名，只能为某一用户使用。
Ø 对象：指某一特定对象的名字，它可以是表、视图、序列、过程、函数、包和其他同义名。
举例
create synonym PERSON from Persons
备注：
1. 创建了同义名后，就可以在其他地方引用它，若对象的名字发生了改变，必须先将同义名删除再重新创建。同义名不支持replace
2. 同义名不能对列其别名，这一点有别于视图
十六.
异常处理
1.
系统内定义的异常
|
异 常
|
说 明
|
|
NO_DATA_FOUND
|
没有数据满足查询请求
|
|
ZERO_DIVIDE
|
试图用零去除一个数
|
|
INVALID_NUMBER
|
在要求数值的地方使用了非数值
|
|
INVALID_CURSOR
|
引用了一个非法游标
|
|
NOT_LOGGED_ON
|
没有和ORACLE连接上
|
|
STORAGE_ERROR
|
程序用到的内存有错误
|
|
TOO_MANY_ROWS
|
Select into语句返回记录多于一行
|
|
VALUE_ERROR
|
遇到了算术的、转换的、截去的或约束错误
|
|
CURSOR_ALREADY_OPEN
|
试图打开一个已经打开的游标
|
|
DUP_VAL_ON_INDEX
|
试图插入一个已经存在的唯一约束的值
|
|
LOGIN_DENIED
|
要求进入系统的请求被拒绝
|
|
PROGRAM_ERROR
|
程序的内部有错误
|
|
TIMEOUT_ON_RESORCE
|
等待的系统资源已超时
|
|
TRANSACTION_BACKED_OUT
|
某个远程服务器回退了事务
|
2.
用户自定义的异常
步骤如下：
1) 在PL/SQ
L程序的declare部分定义异常名，类型为EXCEPTION。如下：
<异常名> EXCEPTION
2) 当自定义的异常情况出现时，在PL/SQL程序中用raise语句引发它，如下：
raise <异常名>
3) 在EXCEPTION部分做相应异常处理
4) ORACLE有两个系统变量sqlCode和sqlErrm，他们可以返回错误号和错误信息。 错误号范围在”-20000”到”-20999”，共1000个。
5) ORACLE中有一个raise_application_error系统过程，可生成应用程序的异常错误号和错误信息，并将这些信息返回给调用环境（如应用程序）
举例
declare
person_no number(5);
person_weight number(5,1);
error_no number;
error_info char(20);
TOO_FAT EXCEPTION;
Pragma exception_init(TOO_FAT,-20001); --定义错误号
Begin
Select NO,WEIGHT into person_no, person_weight from CHECK_BODY where no = 98033;
If person_weight > 80 then
Raise TOO_FAT
End if;
EXCEPTION
When TOO_FAT then
Error_no := sqlcode;
Error_info := sqlerrm;
Raise_application_error(error_no,error_info);
End;
十七.
多表查询
1.
连接查询
select 列名1,列名2，……，列名n from
<表1>,<表2>
where < 表1>.列名 = <表2>.列名
2.
左右连接查询
1) 左连接
select 列名1,列名2，……，列名n from
<表1>,<表2>
2) 右连接
select 列名1,列名2，……，列名n from
<表1>,<表2>
where < 表1>.列名= <表2>.列名(+);
3.
并、交、差查询
1)
并查询
(union | union all)
select 列名1,列名2,……列名n from 表1 where 条件
union | union all
select 列名1,列名2,……列名n from 表2 where 条件
union :表示将两个表里所有信息列出来，并且不重复
union all:表示列出查询表里的所有信息，有重复记录
2)
交查询
(intersect)
查询结果：为表1和表2中同时存在的查询结果
select 列名1,列名2,……列名n from 表1 where 条件
intersect
select 列名1,列名2,……列名n from 表2 where 条件
3)
差查询
(minus)
查询结果：为表1的查询结果减去在表2的查询结果中存在的部分
select 列名1,列名2,……列名n from 表1 where 条件
minus
select 列名1,列名2,……列名n from 表2 where 条件
4.
级联查询
select 列1,列2,……列n
f rom <表>
start with 列 = value
connect by 列1 = prior 列2;
级联查询也可有对应的删除操作
delete
f rom <表>
start with 列 = value
connect by 列1 = prior 列2;
850
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
