oracle入门学习之oracle数据库结构

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;