Oracle学习笔记_02
Oracle_02
Oracle的体系结构
数据库实例——表空间——用户——表结构——数据文件
创建表空间
create tablespace demo1 -- 表空间名称
datafile 'c:\demo1.dbf' -- 表空间路径（服务器）
size 10m -- 初始化表空间大小
autoextend on -- 自动增长空间（当初始化存储空间存满之后自动增长）
next 1m; -- 每次自动增长1M
删除表空间
drop tablespace demo1; -- 删除的是逻辑关系，具体的文件需要在服务器端进行删除
创建用户
create user testuser -- 用户账号
identified by testuser -- 用户密码
default tablespace demo1; -- 指定表空间
删除用户
drop user 用户;
-- 若用户下存在表和数据，可以采用级联删除，慎用
drop user 用户 cascade;
权限设置
- connect
- resource
- dba
grant 角色 to 用户;
创建表
- 列的类型（Oracle中没有int类型，但是可以使用，Oracle会自己解析成自己的number类型）
- 创建表
-- 1. 利用子查询创建表(注意：这种创建表的方式只会复制表中的列和数据，但是具体的结构不会复制，比如主键和外键)
create table 表名 as 子查询
-- demo
create table emp as select * from scott.emp;
/* SQL> desc emp; Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y MGR NUMBER(4) Y HIREDATE DATE Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y DEPTNO NUMBER(2) Y */
-- 2. 常规创建表
create table stu(
sid number,
sname varchar2(10),
sgrade varchar2(10)
);
修改表（DDL）
关键字：alter、create、drop、truncate（清空表，效率高，直接删除表，再进行创建）
- 增加列
alter table stu add age number;
- 删除列
alter table stu drop column age;
- 删除表
drop table stu;
- 修改列名
alter table stu rename column sname to stu_name;
- 修改字段属性
alter table stu modify stu_name varchar2(20);
约束
- check约束（在mysql中不支持，但是使用不报错）
alter table stu modify age number check(age>0); -- age必须大于0
-- 正确
insert into stu values(1, 'zhangsan', '1', 20);
-- 错误（包如下错误）
insert into stu values(2, 'lisi', '2', 0);
插入数据
-- 利用子查询插入数据
insert into 表名 子查询语句;
insert into stu select * from stu1;
-- 常规插入数据
insert into stu values(1, 'zhangsan', '1', 20);
-- 或者
insert into stu(sid, stu_name, sgrade, age) values(1, 'zhangsan', '1', 20);
删除数据
delete from 表名 where 条件;
-- ddl实现删除，效率更快，不需事务提交
truncate table stu1;
Oracle中的事务
视图
- 什么是视图？
视图就是封装了一条复杂的查询语句。视图本身是不存储任何数据的，都来自于一条查询语句。 - 作用
1）封装查询语句
2）屏蔽表中细节 - 语法：
-- or replace：存在则进行修改，不存在则进行创建
-- with read only：只读，不可进行修改（修改视图会修改表中数据）
create [or replace] view 视图名 as 查询语句 [with read only]
序列
在Oracle中，并不提供像mysql中auto_increment这样的关键字，则需要创建序列来实现自增的功能
- 创建序列
create sequence seq_test1
start with 1-- 从几开始
increment by 1-- 每次自增1
nomaxvalue -- 最大值 [maxvalue X] x代表任意数
nominvalue-- 最小值 [minvalue X] x代表任意数
nocycle-- 不循环 循环：cycle
nocache;-- 不缓存 缓存：cache
- 使用序列和查看当前序列号
select seq_test1.nextval from dual; -- 使用
select seq_test1.currval from dual; -- 执行此语句之前曾经必须执行过select seq_test1.nextval from dual;
- 在表中使用序列
select * from stu;
insert into stu values(seq_test1.nextval,'bob', '1', 15);
select * from stu;
索引
在数据量特别大的时候，查询某条我们经常查询的数据就显得非常的耗费时间和资源，系统性能就显得非常的低，这时候若建立个索引，很大程度节省了时间和空间（提升系统性能）。但是，创建索引的时候是比较耗时的，且随着数据量的增大效果越来越显著。索引的原理主要借助的是b-tree和balance tree。
语法：
create index 索引名称 on 表名称(字段名称...);
-- demo
create index index_test1 on stu(sid);
create index index_test2 on stu(sid,stu_name);
创建索引后，可以从cost（cpu占用率）和cardinality（影响行数）看出效果（执行完语句后按F5）
PLSQL入门了解
PL/SQL Developer是一个集成开发环境，专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的，但支持Navicat Premium。（百度百科）
- 语法
declare
变量的定义
begin
业务逻辑
end;
- 简单入门
declare
i varchar2(10) := 'testuser'; -- 变量赋值 :=
begin
dbms_output.put_line(i); -- 打印输出
end;
declare
testname stu.stu_name%type; -- 引用型变量 %type：自动匹配类型
begin
select stu_name into testname from stu where sid=1;
dbms_output.put_line(testname);
end;
declare
teststu stu%rowtype; -- 记录型变量
begin
select * into teststu from stu where sid=1;
dbms_output.put_line(teststu.stu_name);
end;
- 条件判断
declare
begin
if 条件 then
结果
elsif 条件 then
结果
else
结果
end if;
end;
-- demo
declare
i number := 50;
begin
if i<50 then
dbms_output.put_line('小于50');
elsif i=50 then
dbms_output.put_line('等于50');
else
dbms_output.put_line('大于50');
end if;
end;
- 循环
1）while循环
2）for循环
3）loop循环
while循环语法：
declare
变量的定义
begin
while 条件 loop
...
end loop;
end;
for循环语法：-- for循环中的变量可以不用定义 reverse:逆序
declare
begin
for 变量 in [reverse] 起始值..最终值 loop
...
end loop;
end;
loop循环语法：
declare
变量定义
begin
loop
exit when 条件
...
end loop;
end;
-- while
declare
i number := 1;
begin
while i<=10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
-- for reverse:逆序
declare
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
-- loop
declare
i number := 1;
begin
loop
exit when i > 10; -- 退出循环的条件
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
游标
/* 游标： 1. 声明游标 cursor 游标名称([参数]) is 结果集 2. 打开游标 open 游标名称 3. 取数据 fetch 游标名称 into 声明型变量(%rowtype) 4. 关闭游标 close 游标名称 */
declare
-- 1.声明游标
cursor stus is select * from stu;
-- 声明型变量
st stu%rowtype;
begin
-- 2. 打开游标
open stus;
-- 3. 取数据
loop
fetch stus into st;
exit when stus%notfound;
dbms_output.put_line('学生姓名：'||st.stu_name||' 年龄：'||st.age);
end loop;
-- 4. 关闭游标
close stus;
end;
/* 得到指定年级的学生信息 */
declare
-- 1.声明游标
cursor stus(grade varchar2) is select * from stu where sgrade=grade;
-- 声明型变量
st stu%rowtype;
begin
-- 2. 打开游标
open stus(1);
-- 3. 取数据
loop
fetch stus into st;
exit when stus%notfound;
dbms_output.put_line('学生姓名：'||st.stu_name||' 年龄：'||st.age);
end loop;
-- 4. 关闭游标
close stus;
end;
/* 使用for循环（自动打开和关闭游标，不用手动操作） 按照员工工作给员工涨工资，总裁涨1000，经理800， 其它400(注意：不能再where条件中使用职位（JOB），否则会进行多次更新) */
declare
cursor emps is select * from emp;
begin
for e in emps loop
if e.job = 'PRESIDENT' then update emp set sal = sal + 1000 where empno = e.empno;
dbms_output.put_line('1000+');
elsif e.job = 'MANAGER' then update emp set sal = sal + 800 where empno = e.empno;
dbms_output.put_line('800+');
else update emp set sal = sal + 400 where empno = e.empno;
dbms_output.put_line('400+');
end if;
end loop;
commit;
end;
系统调用游标
/* 系统引用游标 1. 声明系统引用游标 游标名称 sys_refcursor 2. 打开游标 open 游标名称 for 结果集 3. 取数据 fetch ... into... 4. 关闭游标 close 游标名称 */
declare
stus sys_refcursor;
st stu%rowtype;
begin
open stus for select * from stu;
loop
fetch stus into st;
exit when stus %notfound;
dbms_output.put_line('学生姓名：'||st.stu_name||' 年龄：'||st.age);
end loop;
close stus;
end;
系统异常
格式：
declare
begin
exception
when 异常类型 then 处理;
when others then 处理;
end;
/* 系统异常：常见 zero_divide：除数为零 value_error：；类型转换异常 too_many_rows：将查询出来的多个结果直接赋值给rowtype no_data_found:没有查询出数据 */
-- zero_divide
declare
i number := 1;
j number;
begin
j := i/0;
exception
/* when 异常类型 then */
when others then
dbms_output.put_line(sqlerrm); -- sql error massage
end;
自定义异常
格式：
declare
异常名称 exception;
begin
-- 抛出异常
raise 异常名称;
exception
when 异常类型 then 处理;
when others then 处理;
end;
/* 自定义异常 */
declare
emp_no exception;
cursor emps is select * from emp where empno=11111;
em emp%rowtype;
begin
open emps;
loop
fetch emps into em;
-- raise 抛出异常
if emps %notfound then raise emp_no;
end if;
exit when emps %notfound;
dbms_output.put_line(em.ename);
end loop;
close emps;
exception
when emp_no then
dbms_output.put_line('未发现数据');
end;
存储过程
格式：
create [or replace] procedure 存储过程名称(参数1 in|out 参数类型...)
is
声明变量
begin
逻辑
end;
/* 存储过程 */
create or replace procedure getsal (eno in number)
is
-- 声明变量
salary number;
begin
select sal into salary from emp where empno = eno;
dbms_output.put_line(salary);
end;
/* 执行存储过程 */
-- 1.
call 存储过程名称(参数);
call getsal(7788);
-- 2.
declare
定义变量
begin
存储过程名称(参数);
end;
declare
salary number;
begin
getsal1(7788,salary);
end;
/* 查询指定员工的年薪加奖金 */
create or replace procedure getSalAndComm(eno in number, salandcomm out number)
is
begin
select sal*12 + nvl(comm,0) into salandcomm from emp where empno = eno;
end;
declare
sc number;
begin
getSalAndComm(7788,sc);
dbms_output.put_line(sc);
end;
存储函数
存储函数和存储过程在功能上没有太大的差异，只不过存储函数可以直接在sql语句上使用，存储函数可以直接被存储过程进行调用。
格式：
create [or replace] function 存储过程名称(参数1...) return 输出参数类型
is
声明变量
begin
逻辑
return 输出参数;
end;
/* 存储函数(参数默认为in) */
create or replace function getSalAndCommFun(eno number) return number
is
sc number;
begin
select sal*12 + nvl(comm,0) into sc from emp where empno = eno;
return sc;
end;
-- sql语句进行调用
-- 存储函数可以直接在sql语句中调用
select ename,getSalAndCommFun(empno) sc from emp;
JDBC调用存储过程和存储函数
注：记得加载驱动
- 存储过程
@Test
public void test1() throws ClassNotFoundException, SQLException{
/* * 加载Oracle驱动 */
Class.forName("oracle.jdbc.driver.OracleDriver");
// 服务器地址自行修改*
String url = "jdbc:oracle:thin:@***.***.***.***:1521:orcl";
String user = "testuser";
String password = "testuser";
Connection conn = DriverManager.getConnection(url, user, password);
// 存储过程
String sql = "{call getSalAndComm(?,?)}";
CallableStatement state = conn.prepareCall(sql);
// 参数位置从1开始
state.setInt(1, 7788);
// 注册输出参数
state.registerOutParameter(2, OracleTypes.NUMBER);
// 执行语句
state.execute();
// 获得输出参数的值
int tatol = state.getInt(2);
System.out.println(tatol);
}
- 存储函数
/* * JDBC执行存储函数 */
@Test
public void test2() throws ClassNotFoundException, SQLException{
/* * 加载Oracle驱动 */
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@172.22.229.194:1521:orcl";
String user = "testuser";
String password = "testuser";
Connection conn = DriverManager.getConnection(url, user, password);
// 存储函数
String sql = "{? = call getSalAndCommFun(?)}";
CallableStatement state = conn.prepareCall(sql);
// 参数位置从1开始
state.setInt(2, 7788);
// 注册输出参数
state.registerOutParameter(1, OracleTypes.NUMBER);
// 执行语句
state.execute();
// 获得输出参数的值
int tatol = state.getInt(1);
System.out.println(tatol);
}
触发器（行级触发器，语句级触发器）
- 在执行DML时，触发其它的业务逻辑
- 做校验
语法:
create [or replace] trigger 触发器名称
before|after
insert|update|delete
on 表名称
[for each row] -- 行级触发器
declare
变量的定义
begin
触发的逻辑
end;
-- 语句级触发器
create or replace trigger insertResult
after
insert
on stu
declare
begin
dbms_output.put_line('insert success!!!');
end;
-- 行级触发器
create or replace trigger checkUpdate
before
update
on stu
for each row
declare
begin
if :new.age <= 0 then
raise_application_error(-20099, '年龄设置错误');
end if;
end;
利用触发器和序列实现主键id的自动增长
/* 模拟主键自增长 */
create or replace trigger seq
before
insert
on stu
for each row
declare
pkey number;
begin
select seq_test1.nextval into pkey from dual;
:new.sid := pkey;
end;
insert into stu values(null,'tom','3',26);
投递字节跳动等公司7个岗位
