oracle review

2012-3-26:oracle review
什么是PL/SQL,什么是T-SQL?
PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言.
T-SQL是基于事务的结构化查询语言.
两者都是对sql的扩展.
PL/SQL优点"
1.支持多种数据类型
2.可使用流程控制
3.能创建复杂逻辑的过程或函数
4.与oracle紧密集成


PL/SQL块是构成PL/SQL程序的基本单元.
PL/SQL块将逻辑上相关的声明和语句组合在一起.
PL/SQL块的组成:(三个部分)
1.声明执行[可选]
2.可执行部分
3.异常处理部分[可选]
[DECLARE
declarations]--声明部分
BEGIN
executable statements --可执行部分
[EXCEPTION
handlers]--异常处理部分
END;


在PL/SQL块中声明部分声明变量或常量
语法:
declare
变量名/常量名[constant] 数据类型  赋值([not null][:=值|default值])
赋值:
1.使用赋值运算符":="
2.使用select into 语句.




declare
myname varchar2(20) not null :="张三";
myage number;
mypi constant number :=3.14;
begin
myage:=26:
select ename info myname from emp where empno=7369;
--输出
end;


注意:
1.变量如果声明为not null,则必须初始化值.
2.常量一旦定义则不能修改.
3.一条语句只能声明一个变量.




PL/SQL支持的内置数据类型:
数据类型:
1.标量类型:数字,字符,布尔型,日期类型.
2.LOB类型(存储非结构化数据块):BFILE,BLOB,GLOB,NCLOB
3.属性类型:%TYPE(提供某个变量或数据库表列的数据类型),%ROWTYPE(提供表示表中一行的记录类型)




数字数据类型:
1.BINARY_INTEGER:SIGNTYPE,POSITIVEN,MATURALLN,NATURAL
2.NUMBER:REAL,INTEGER,FLOAT,DECIMAL(存储整数,实数和浮点数)
NUMBER[(P[S])],p表示精度(一共有几位),s表示小数位数
3.PLS_INTEGER(存储有符号整数,可使算数计算快速而有效)


字符数据类型包括:
数据类型          SQL类型         PL/SQL类型
CHAR:  1..2000  1..32767
VARCHAR2:         1..2GB          1..32760
LONG:  1..2GB          1..32760
RAW:  1..2000  1..32767 
LONG RAW:  1..4000  1..32767 


日期时间类型:
1.存储日期和时间类型.DATE,TIMESTAMP
DATE:精确到整数秒
TIMESTAMP:秒值精确到小数后6位
注意:日期格式为"DD-MON-YY" ,如"20-5月-10"




布尔数据类型:
BOOLEAN ,用于存数逻辑值(true,false,null),
1.不能向数据库中插入boolean数据
2.不能讲列值保存到boolean变量中
3.只能对boolean变量执行逻辑操作


LOB数据类型:
用于存储大文本,图像,视频剪辑和声音剪辑等非结构化数据.
LOB数据类型可存储最大4GB的数据.
LOB类型包括:
BLOB  将大型二进制对象存储在数据库中
CLOB  将大型字符串数据存储在数据库中
NCLOB 存储大型UNICODE字符数据
BFILE 将大型二进制对象存储在操作系统文件中
LOB类型的数据库列仅存储定位符,该定位符指向大型对象的存储位置.
DBMS_LOB程序包用于操作LOB数据




属性类型:
用于医用数据库列的数据类型,以及表示表中一行的记录类型.
1.列类型%type,语法:表名.列名%type(引用数据库列的数据类型)
2.行类型%rowtype,语法:表名%type(提供表示表中一行的记录类型)
优点
1.不需要知道被引用的表列的具体类型
2.如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变




假设现在不知道emp表Job列的类型，但要保存员工7369的岗位到变量中，如何定义？
declare
myjob emp.job%type;
begin
 select job into myjob from emp where empno=73769;
 dbms_output.put_line(myjob);
end;


假设要输出emp表员工7369的所有信息，怎么办？定义8个列类型变量，依次输出？
declare
myemp emp%rowtype;
begin
select * into myemp from emp where empno=7369;
dbms_output.put_line(myemp.ename||myemp.job);
end;


PL/SQL支持的流程控制结构:
1.条件结构: 
if 语句 
if ...then...end if;
if ...then ... else ... end if;
if ...then ... elseif ... then else ...end if;
注意:if语句必须以end if;结束
case 语句
case ...when...then...else...end case;


2.循环条件: Loop 循环,while 循环,for 循环
loop 无条件循环
while 根据条件循环
for  循环固定的次数


declare
num1 number;
num2 number;
begin
num1:='&请输入第一个数';
num2:='&请输入第二个数';
if num1<num2 then 
dbms_output.put_line('小于');
else if num1>num2 then 
dbms_output.put_line('大于');
else
dbms_output.put_line('等于');
end if;
end;


declare
grade varchar2(10);
begin
grade:='&grade';
case grade
when 'A' then dbms_output.put_line('优秀');
when 'B' then dbms_output.put_line('良好');
when 'C' then dbms_output.put_line('一般');
when 'D' then dbms_output.put_line('差');
else dbms_output.put_line('没这个');
end case;
end;


declare
counter number:0;
begin
loop
counter:=counter+1;
dbms_output.put_line(counter||':aa');
--if counter=100 then
--exit;
--end if;
exit when counter=100;
end loop;
end;


declare
counter number:=0;
begin
while number<100
loop
counter:=counter+1;
dbms_output.put_line(counter||':aa');
end loop;
end;


begin
for n in 1..100
loop
dbms_output.put_line(n':aa');
end loop;
end;


动态SQL:
动态SQL是指在PL/SQL程序执行时生成的SQL语句.
编译程序对动态SQL不做处理,而是在程序运行时动态构造语句,对语句进行语法分析并执行,
执行语法:
execute immediate 'sql语句'
[into 变量列表]--将查询的值赋值给变量
[using 参数列表] --使用参数填补占位符(:name)
应用场合:
1.DDL语句命令和会话控制语句不能在PL/SQL中直接使用,但是可以通过动态SQL来执行.
2.在运行时,根据需要动态构建SQL语句执行.


declare
myname emp.ename%type;
begin
execute immediate 
'select ename from emp where empno=:no' into myname
using '&no';
dbms_output.put_line(myname);
end;


在运行程序时出现的错误叫做异常
发生异常后,语句将停止执行,控制权转移到PL/SQL块的异常处理部分.
异常有两种类型:
1.预定义异常:当PL/SQL程序违反oracle规则或超越系统限制时隐式引发
others处理所有异常
在用户试图将重复的值存储在使用唯一索引的数据库列中时出现
DUP_VAL_ON_INDEX
在将字符串转换为数字时出现
INVALID_NUMBER
在表中不存在请求的行时出现
NO_DATA_FOUND
在执行SELECT INTO语句后返回多行时出现
TOO_MANY_ROWS
在以零作为除数时出现
ZERO_DIVIDE


必须有且只有一行返回结果
1、没有结果，引发no_data_found异常
2、多个结果，引发too_many_rows异常


declare
myname emp.ename%type;
begin
select ename into myname from emp where empno=1;
exception
when no_data_found then 
dbms_output.put_line('没有数据');
end;


declare
myname emp.ename%type;
begin
select ename into myname from emp;
exception
when too_many_rows then dbms_output.put_line('值过多');
end;


2.用户定义异常:用户可以在PL/SQL块的声明部分定义异常,自定义的异常
通过RAISE语句显示引发.
declare
myException Exception;
begin
if ... then 
raise myException;
end if;
Exception
when myException then
...
end;
注意:用户自定义异常类型为exception,必须通过raise引发用户自定义异常.


引发应用程序错误:
RAISE_APPLICATION_ERROR过程
1.用于创建用户定义的错误信息
2.可以在可后自行部分和异常处理部分使用
3.错误编号必须介于-20000和-20999之间
4.错误消息的长度可长达2048个字节.


引发应用程序错误的语法:
RAISE_APPLICATION_ERROR(error_number,error_message);






总结:
1.:=赋值运算符
2.||连接操作符
3.'&name'接收用户输入参数值
4.:name动态SQL占位符,使用using填补




补充:
数据模型是什么?
用来描述现实世界中的事物及其联系.它将数据库中的数据按照一定的结构组织
气力啊,以反映事物本身及事物之间的各种联系.
任何数据库管理系统都对应某种数据模型.
1.层次型数据库
2.网状型数据库
3.关系型数据库(sql server)
4.对象关系型数据库(oracle)




Oracle数据库的主要特点
支持多用户、大事务量的事务处理
数据安全性和完整性控制
支持分布式数据处理
可移植性


物理组件就是Oracle数据库所使用的操作系统物理文件。物理文件可分为三类：
数据文件 .dbf
用于存储数据库数据，如表、索引数据等


控制文件.ctl
记录数据库物理结构的二进制文件


日志文件.log
记录对数据库的所有修改信息，用于故障恢复


oracle逻辑组件:
数据库-->表空间-->段-->区-->数据块.


常用oracle命令行:
show user.
disconnect
connect +连接字符串
连接字符串格式
conn 用户名/密码@网络服务名[as sysdba/sysoper]
exit
start '文件路径'
edit '文件路径'
clear




创建用户:create user 用户名 identified by 密码.//注意密码不不能以数字开头
删除用户:drop user 用户名 cascade;
修改密码1:passw[ord]
修改密码2:alter user 用户名identified by 新密码;
锁定用户:alter user 用户名 account lock;
解锁用户:alter user 用户名 account unlock;




//修改电脑密码
net admin 密码.


connect:允许用户连接到数据库的角色
resource:允许用户使用数据库存储控件的角色
dba:系统管理员的角色


为用户指定默认表空间的语法:
create user 用户名 identified by 密码
default tablespace 表空间名;


为什么需要表空间?
1.数据库对象非常多时,便于用户管理.
2.数据量非常大时,便于将数据分布在不同的磁盘中,减少磁盘读写的开销.
创建表空间语法:
create tablespace test --表空间名
datafile 'D:\oracle\orc\test.dbf' --表空间对应的数据文件
size 32m  --初始大小
autoextent on  --自动增长打开
next 32m maxsize 2048m;  --每次增长32,最大到2G


删除表空间语法:
drop tablespace test
[including contents and datafiles];--[..]可选项,有则表示删除表空间的内容和对应的数据文件,




复制表
create table 表名 as select *from emp where 1=1;//复制表结构和数据,不能复制表约束
create table 表名 as select *from emp where 1=2;//复制表结构,不复制表数据和约束


修改表
alter table 表名 add (列名 类型); --增加一列
alter table 表名 modify (列名 类型); --修改字段的长度
alter table 表名 drop colnum 列名; --删除一列
rename 表名 to 表名 ;--修改表的名字
desc 表名; --查看表结构


多行插入:
insert into 表名 <select 语句>;


truncate table表名;--删除的数据不能回复,但truncate效率更好.


SQL:结构化查询语言
DDL:数据定义语言
DCL:数据控制语言
DML:数据操纵语言
TCL:事务控制语言


多表连接查询
1.卡迪尔积
如果在多表连接查询时没有where条件,则一个表中的所有记录将与
另一个表中的所有记录进行连接组合.
2.对卡迪尔积进行筛选
3.在连接查询中国使用between...and...
注意:在where条件中应首先使用等于操作,然后使用其他的比较操作.




自连接:自己与自己做连接查询
注意:自连接时需要给表指定别名
select b.ename from emp a,emp b where a.ename=b.mgr and a.ename='SMITH';


子查询（嵌套查询）
1-单行子查询
要求子查询返回的结果集只有一行 
测试：查询与员工SMITH属于同一部门的员工信息
2-多行子查询
子查询返回的结果集可以是一行或多行 
测试：查询与部门10员工工作岗位相同的员工信息
3-多列子查询
主查询的where子句中有多个列
测试：查询与SMITH的部门和岗位都相同的员工信息


4-内联视图子查询
在主查询FROM后面直接跟上一个子查询作为中间数据结果集 
该中间数据结果集称为内联视图
带内联视图的子查询称为内联视图子查询
优点：可以在内联视图中使用排序（其他不行）
测试：查询薪水最低的5个员工的信息
select * from (select * from emp order by sal asc) where rownum<=5;


分页查询:
select rownum,emp.* from emp;--查询全部
select rownum,emp.* from emp where rownum<=5;--第一页
select rownum,emp.* from emp where rownum>5 and rownum<=10;--错误,
注意:
rownum单表查询时,只能用于<和<=比较,如果要进行其他比较则必须将rownum作
为查询结果集中的新的列,且需要指定别名.
正确代码如下:
1.使用伪列
select rownum,e.* from (select rownum rn,emp.* from emp) e where rn>5 and rn<=10;
2.使用分析函数(已排序)
select *from (select row_number() over(order by empno) r,emp.* from emp) 
where r>10 and r<=15;


模糊查询like --通配符
% 表示匹配任意个字符
_ 表示匹配单个字符
In(值列表)
表示查询在指定的值列表范围内的数据
is null判断是否为空
测试：查询没有上级的员工信息
Between…and
判断是否在两值之间
测试：查询薪水在2000-3000之间的员工信息
与not连用表示取否定
not like，not in，is not null，not between




将两个查询的结果组合成一个结果集
Union（联合）
返回两个查询选定的所有的不重复的行
Union all（联合所有）
返回两个查询选定的所有行，包括重复行
Intersect（交集）
返回两个查询都有的行
Minus（减集）
返回由第1个查询选定但是没有被第2个查询选定的行。
即：在第1个结果集中减去在第2个结果集中出现的行

注意：集合操作符联接的各个查询要具有相同的列数，且对应列的数据类型必须相同。

今天写下来,眼睛都痛了.加油,lily,