1. oracle数据库结构
1.1 Oracle可以看做是一个大的数据库，里面可以创建很多实例;
一个实例对应多个表空间、多个用户;
一个用户只能有一个表空间;
一个表空间可以有多个用户;
一个用户只能看到自己建的表;
— 创建表空间
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;
