1. oracle结构
1.1 oracle可以看做是一个大的数据库，里面可以创建很多实例;
一个实例对应多个表空间、多个用户;
一个用户只能有一个表空间;
一个表空间可以有多个用户;
一个用户只能看到自己建的表;
1.2 表空间，用户创建
— 创建表空间
create tablespace myspace –创建myspace的表空间，并将其关联的物理文件保存到
datafile ‘c:\mydata.dbf’ –c:\mydata.dbf该文件中，初始大小为100m,以后每次扩展
size 100m –10m.
autoextend on
next 10m
— 创建用户
create user cater
identified by rsx123
default tablespace myspace
— 用户授权
grant dba to cater
2. 数据类型，约束，序列
2.1数据类型
2.1.1数值型 number
整数 number(2) 88
小数number(2,1) 8.8
2.1.2 字符型
char 固定长度 <=2000
varchar2 可变长度 <=4000
long 大文本 <=2g
2.1.3 日期型
date 精确到秒
timestamp 精确到秒后9位
当前日期 sysdate
日期运算
日期 +- 数字 = 日期
select (sysdate + 3) from dual;
日期 +- 日期 = 数字(天数)
2.1.4 二进制
clog 存储字符数据 <= 4g
blog 存储视频，音频等 <=4g
2.2 约束
2.2.1 主键 primary key
2.2.2 唯一 unique
2.2.3 非空 not null
2.2.4 检查 check
例如：插入数据时性别只能在0,1中取值
create table t_check_test(
“id” number primary key,
“name” varchar2(30) not null,
“sex” number(1) check(sex in(0,1))
);
2.2.5 外键 foreign key
2.3 序列
序列的创建与使用常用于主键
create sequence seq_test; –创建序列
select seq_test.nextval from dual; –序列中下一个值
select seq_test.currval from dual; –序列中的当前值常用函数
创建序列的完整语法
create sequence 序列名
[increment by n] –每次增长n
[start with n] –从n开始取值
[{maxvalue/ minvalue n|nomaxvalue}] –最大最小值
[{cycle|nocycle}] –循环
[{cache n|nocache}];–缓存序列值的个数
3. 常用函数
3.1 字符
substr 截取
select substr(‘abcdefg’,2,3) from dual; –bcd 起始索引号为1
concat 拼接
select concat(‘abc’,’def’) from dual;
length 长度
select length(‘abcdefg’) from dual;
3.2 数值
round 四舍五入
select round(2.5) from dual; –3
trunc 取整
select trunc(2.99) from dual; –2
mod 取余
select mod(11,4) from dual; –3
3.3 转换
to_char 转换为字符
日期–>字符
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
数字–>字符
select to_char(2) from dual;
to_number 转换成数字
字符–>数字
select to_number(‘12.03’) from dual;
to_date 转换成日期
字符–>日期
select to_date(‘2018-05-31 15:12:31′,’yyyy-mm-dd hh24:mi:ss’) from dual;
–2018/5/31 15:12:31
3.4 日期
month_between(date1,date2) 两个日期间隔的月数
add_month(date,n) 在日期的基础上增加n个月
3.5 通用
nvl(字段,替代值) 空值处理
select name, nvl(tele,’未填写’) from person; — tele为空值时，返回’未填写’
decode 条件判断
语法：decode(col/expression, [search1,result1],[search2, result2]….[default])
col/expression:列名或表达式
search1，search2…:用于比较的条件
result1, result2…:返回值
select name,decode(sex,1,’男’,0,’女’) from person;
–sex字段值为1则返回男，为0返回女
3.6 聚合
count 统计
sum 求和
avg 平均值
min/max 最大/最小值
4. 高级查询
4.1多表关联查询
4.1.1内连接
select ct.name,c.name,c.opendate,s.name
from t_class c ,t_student s,t_class_type ct
where c.id = s.classid
and ct.id = c.type;
4.1.2外连接(两种形式)
–sql1999语法
select s.name,m.score from t_student s left join t_mark m
on s.id = m.studentid;
–oracle语法
select s.name,m.score from t_student s , t_mark m
where s.id = m.studentid(+);
内外连接区别：当与之关联的表没有数据时，内连接查询”主从表”都不会显示;外连接显示”主表”数据，”从表”如果没有数据则显示为空;
4.2分组统计
select co.id,co.name,round(avg(m.score),1) 平均分
from t_mark m,t_course co
where m.courseid = co.id
group by co.id,co.name
having round(avg(m.score),1) > 55
order by round(avg(m.score),1);
— 分组后的条件不能小于查询字段
4.3子查询
— 查询每门课程最高分
select co.name,h.maxscore from t_course co,
(select m.courseid id ,max(m.score) maxscore
from t_mark m group by m.courseid ) h
where co.id = h.id;
— in 查询缺考学生及课程名
select s.name,co.name
from t_student s,t_course co
where s.id not in(
select distinct m.studentid from t_mark m
);
4.4分页排序查询(三层嵌套)
select * from ( –第三层分页
select a.* ,rownum r from( — 第二层加rownum
select * from t_mark m order by m.score desc –第一层：排序
) a)
where r >= 5 and r <= 10 ;
5. 视图
特点：
隔离基表的改变;
简化了sql命令;
视图中的数据被修改，基表中的数据也会被修改;
语法：
create view 视图名 as
sql语句;
create view v_student as
select * from t_student where sex=1;
select * from v_student;
–更新视图后，表中的数据也会相应改变;
update v_student set tele=’19911112222′ where id=7;
只读视图：
create view v_student as
select * from t_student where edu=’本科’ with read only;
6. 索引
6.1索引结构
索引也是需要占用存储空间，并消耗性能的，因此创建合理的索引才能提高性能;
索引的结构为树形结构，每个节点存储rowid(每条记录的物理地址);
6.2索引创建，复合索引
语法：create index 索引名 on 表名(字段名);
create index index_student_name on t_student(name);
复合索引：
create index index_student_sex_name on t_student(sex,name);
–需要查询两个索引树，性能不一定能有提高;
7. plsql语法
7.1语法结构
[declare
–声明变量
]
begin
–代码逻辑
[exception
–异常处理
]
end;
7.2常量与变量
变量：
变量名 数据类型(长度)
两种赋值方式：
:=
select into
常量：
常量名 constant 数据类型(长度) := value –常量声明时必须赋值
eg:
declare
v_name varchar2(30);
v_tele varchar2(30);
v_constant constant number := 100;
begin
select name,tele into v_name,v_tele from t_student where id=5;
dbms_ouput.put_line(v_name||v_tele||v_constant); –输出在会话框
end;
7.3属性类型
引用类型 %type
变量名 表名.字段名%type — 该变量的类型就是该字段的类型
记录型 %rowtype
变量名 表名%rowtype
declare
v_name t_student.name%type;
v_student t_student%rowtype;
begin
v_name:=’小明’;
select * into v_student from t_student where id=5;
dbms_output.put_line(v_student.name||v_student.tele);
end;
7.4条件判断
语法1：
if 条件 then
–业务sql
else
–业务sql
end if;
语法2：
if 条件 then
–业务sql
elsif 条件 then
–业务sql
else
–业务sql
end if;
7.5循环
语法1：无条件循环
loop
–业务sql
exit when 结束条件;
end loop;
语法2：有条件循环
while 条件(结束条件)
loop
–业务
end loop;
语法3：for循环
for 变量 in 起始值..结束值 —变量不用在declare中声明
loop
–业务
end loop;
8. 异常
8.1预定义异常
oracle 21个预定义异常
命名的异常产生原因
access_into_null未定义对象
case_not_foundcase中若未包含相应的when，并且没有设置else时
collection_is_null集合元素未初始化
curser_already_open游标已经打开
dup_val_on_index唯一索引对应的列上有重复的值
invalid_cursor在不合法的游标上进行操作
invalid_number内嵌的sql语句不能将字符转换为数字
no_data_found使用select into未返回行，或应用索引表未初始化的元素时
too_many_rows执行select into时，结果集超过一行
zero_divide除数为0
subscript_beyond_count元素下标超过嵌套表或varray的最大值
subscript_outside_limit使用嵌套表或varray时，将下标指定为负数
value_error赋值时，变量长度不足以容纳实际数据
login_deniedpl/sql应用程序连接到oracle数据库时，提供了不正确的用户名或密码
not_logged_onpl/sql应用程序在没有连接oralce数据库的情况下访问数据
program_errorpl/sql内部问题，可能需要重装数据字典&pl./sql系统包
rowtype_mismatch宿主游标变量与pl/sql游标变量的返回类型不兼容
self_is_null使用对象类型时，在null对象上调用对象方法
storage_error运行pl/sql时，超出内存空间
sys_invalid_id无效的rowid字符串
timeout_on_resourceoracle在等待资源时超时
declare
v_name t_student.name%type;
v_student t_student%rowtype;
begin
select name into v_name from t_student where id<0;
select * into v_student from t_student where id>1;
exception
when no_data_found then
dbms_output.put_line(“未返回行”);
when too_many_rows then
dbms_output.put_line(“结果集超过一行”);
end;
8.2自定义异常
语法：(声明区)
自定义异常名 exception;
pragma exception_init(自定义异常名,错误编号);
declare
error_check exception;
pragma exception_init(error_check,-2290);
begin
–业务
exception
when error_check then
–异常处理
end;
9. 游标
9.1语法结构
declare
v_values 数据类型;
cursor 游标名[(字段数据类型)] is sql语句;
begin
open 游标名[(值)];
loop
fetch 游标名 into v_values;
exit when 游标名%notfound;
—业务处理
end loop;
close 游标名;
end;
9.2循环取值
方式1：
declare
v_student t_student%rowtype; –接收游标中的值
cursor cur_student(v_sex number) is select * from t_student where sex=v_sex;
begin
open cur_student(1);
loop
fetch cur_student into v_student;
exit when cur_student%notfound;
dbms_output.put_line(v_student.name||v_student.edu||v_student.tele);
end loop;
close cur_student;
end;
方式2：(推荐)
declare
cursor cur_student(v_sex number) is select * from t_student where sex=v_sex;
begin
for v_student in cur_student(1) –v_student for循环自己定义
loop
dbms_output.put_line(v_student.name||v_student.edu||v_student.tele);
end loop;
end;
9.3隐式游标
默认隐式游标 sql
可通过sql的属性值来判断sql语句的执行结果
%notfound 刚才的sql语句没有影响到行
%found 刚才的sql语句影响到了行
begin
update t_student set name=’小明’ where id=500;
if sql%notfound then
dbms_output.put_line(‘sql执行失败’);
end if;
if sql%found then
dbms_output.put_line(‘sql执行成功’);
end if;
commit;
end;
9.4系统引用游标
declare
v_student t_student%rowtype;
v_cursor sys_refcursor;
begin
open v_cursor for ‘select * from t_student’;
loop
fetch v_cursor into v_student;
exit when v_cursor%notfound;
dbms_output.put_line(v_student.name);
end loop;
close v_cursor;
end;
10. 存储过程
10.1语法结构
create [ or replace ] procedure 存储过程名称
(参数,参数,参数) –输出参数必须声明out
is|as
变量声明部分;
begin
逻辑部分
[exception
异常处理部分]
end;
10.2应用
–判断班级是否满员(每个班最多20人)，如果满员返回“此班已满员”
–如果输入的数据违反检查约束，则返回“违反检查约束”
create or replace procedure pro_student_add(
v_name varchar2,
v_sex number,
v_edu varchar2,
v_tele varchar2,
v_cardno varchar2,
v_classid number,
v_id out number,
v_msg out varchar2
)
as
v_count number;
err_check exception;
pragma exception_init(err_check ,-2290 );
begin
v_id:=-1;
–查询班级的人数
select count(1) into v_count from t_student
where classid=v_classid;
if v_count>=20 then
v_msg:=’此班级已经爆满’;
else
select seq_student.nextval into v_id from dual;
insert into t_student values(
v_id,
v_name,
v_sex,
v_edu,
v_tele,
v_cardno,
v_classid
);
commit;
v_msg:=’添加成功’;
end if;
exception
when err_check then
v_msg:=’违反检查约束’;
end;
10.3游标与存储过程
create or replace procedure pro_student_list(
v_list out sys_refcursor
)
as
begin
open v_list for select * from t_student;
end;
declare
v_student t_student%rowtype;
v_cursor sys_refcursor;
begin
pro_student_list(v_cursor);
loop
fetch v_cursor into v_student;
exit when v_cursor%notfound;
dbms_output.put_line(v_student.name);
end loop;
end;
11. 存储函数
11.1存储函数与存储过程
存储函数只能有一个返回值，存储过程可以有多个;
存储函数一般被sql语句调用，存储过程一般被应用程序调用;
11.2语法结构
create function 函数名(参数1,参数2,…)
return 数据类型
as
–声明
begin
–业务处理
return 返回值
end;
create function getstudentnamebyid(v_id number)
return varchar2
as
v_r_name varchar2(30);
begin
select name into v_r_name from t_student where id=v_id;
return v_r_name;
end;
select getstudentnamebyid(studentid),round(avg(score),1) from t_mark
group by studentid;
12. 触发器
数据库触发器是一个与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。
12.1语法结构
create [or replace] trigger 触发器名
before | after
[delete ][[or] insert] [[or]update [of 列名]]
on 表名
[for each row [when(条件) ] ]
declare
……
begin
plsql 块
end ;
for each row 作用是标注此触发器是行级触发器，否则是语句级触发器
在触发器中触发语句与伪记录变量的值
触发语句:old:new
insert所有字段都是空(null)将要插入的数据
update更新以前该行的值更新后的值
delete删除以前该行的值所有字段都是空(null)
12.2 前置后置
前置：
—每次往t_class中插入数据时，主键自增
create or replace trigger tri_insert_class
before –操作前
insert
on t_class
for each row –每行起作用
declare
v_seq number; –序列值
begin
–select seq_class.nextval into v_seq from dual;
–:new.id:=v_seq;
–可简写成
select seq_class.nextval into :new.id from dual;
end;
后置：
—更新t_student后将更新信息记录到t_edu_log表中
create or replace trigger tri_student_edu_update
after
update of edu
on t_student
for each row
declare
begin
–:old :new
insert into t_edu_log values (seq_log.nextval,sysdate,
:old.id,:old.edu,:new.edu
);
end;