1.连接Oracle 2.操作数据库 3.单表查询 4.SUID 5.常用函数 6.集合运算 7.Oracle 对象 8.PL/SQL 9.存储函数 10.存储过程 11.触发器
1.连接到数据库
Oracle 特点：
多用户、大事务量
数据安全性、完整性控制
分布式处理
可移植性
比较MySQL；更加强大
Oracle 数据库的体系结构
逻辑结构：数据库、表空间、段、区、数据块
物理结构：文件、数据块
用户：
远程连接Oracle：
>sqlplus username/password@orcl
>sqlplus username/password@address:port/orcl
使用 PL-SQL Developer：指定连接时客户端工具信息 .
配置：
环境变量：指定 tnsnames.ora 位置
C:\Users\Administrator>echo %TNS_ADMIN%
C:\chuanzhi\PLS8\client\
连接工具：Oracle 主目录名（自动检测为空）：
OCI 库（自动检测为空）：
中文乱码：
select userenv('language') from dual;
C:\Users\Administrator>echo %NLS_LANG%
AMERICAN_AMERICA.AL32UTF8
JDBC 连接数据库：
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@address:port/instance", "username", "password");
2.操作数据库
- 创建库、用户、授权角色
创建表空间、创建用户
创建表空间
create tablespace name
datafile 'C:\oracleDB\filename.dbf'
size 100m
autoextend on
next 10m; 创建用户
create user username
identified by password
default tablespace ts_name;
授权角色
grant dba to username;
创建表的方法
创建表
create table t_name (
column_name type_name key;
...
) 数据类型：
CHAR 固定长度
VARCHAR2 变长
LONG 大文本类型
NUMBER 数值型
DATE 日期时间
示例：创建表
例子：
create table t_owners(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);
SQL 语句分类：
SQL 语言：
DDL：cteate drop truncate
DML：update insert delete
DQL：select
DCL：grant revoke
- DML 语句
增加数据：
insert into table_name (id,name) values (?,?);
commit; 删除数据：
delete from t_owners where id > 7 and id < 11;
commit; 修改数据：
update t_owners set column_name = value where id = ?;
commit;
- 库的导入导出
整库的导入、导出
exp system/password full=y
imp system/password full=y 按用户导入、导出
exp system/password owner=username file=${PATH}/${filename}
imp system/password fromuser=username file=${PATH}/${filename} 按照表导入、导出
exp username/password tables=t1,t2,t3 file=C:\username.dmp
imp username/password tables=t1,t2,t3 file=C:\username.dmp
exp： 命令后边须紧跟 USERID（username/password）
示例：导出表、导入表；不指定名称，默认“EXPDAT.DMP”，是个二进制文件
Desktop>exp scott@orcl tables=dept
Desktop>imp test@orcl tables=dept
示例：导出时，指定文件名；指定为 “dept.sql”，是个文本文件。（还是个 DMP 二进制文件）
Desktop>exp test tables=dept file=dept.sql
示例：导时，指定文件名不带扩展名，会自动添加，扩展名 DMP。
Desktop>exp scott@orcl tables=emp file=emp
Desktop>imp test@orcl tables=emp file=emp
3.单表查询
- 通配符： %、_（用在 like 子句中）
- 匹配空值：is、is not
- 排序：desc、asc
示例：查询某些特征的字符串
select * from t_owners where name like '__';--名字是俩字符
select * from t_owners where name like '刘%';--刘姓
示例：查询空值
select * from t_pricetable where maxnum is null;
示例：按列值排序
select * from t_account order by usenum asc;--升序
select * from t_account order by usenum desc;--降序
select * from t_account order by usenum;--默认升序
既然默人按升序排列，又为啥显式指定呢？因为某些情况下获取了结果，某个列不是排序的，需要特别指明。
select o.id,o.name,ot.name from t_owners o, t_ownertype ot where o.ownertypeid = ot.id;--查询的结果未排序
select o.id,o.name,ot.name from t_owners o, t_ownertype ot where o.ownertypeid = ot.id order by o.id asc;
4.SUID
示例：增删改
String sql = "insert into t_edu values (?, ?)";
String sql = "update t_edu set name = ? where id = ?";
String sql = "delete from t_edu where id = ?";
示例：查询 伪列
select rowid,rownum,e.* from t_edu e;
ROWID 固定值；ROWNUM 虚值，结果排序
示例：内连接
select t1.name, t2.name
from t_owners t1, t_ownertype t2
where t1.ownertypeid = t2.id;
示例：外连接
select t1.name, t2.name
from t_owners t1 right join t_ownertype t2
on t1.ownertypeid = t2.id;
示例：分页，前 5 行
select rownum,a.* from t_account a where rownum < 5;
伪列 rownum 为最后的结果表 排序，只有 [小于]、[小于等于] 能查出结果。
当需要查询 5~10 行，需要使用到子查询。在子查询的结果中使用伪列，最终结果表临时保存于内存中，外层查询是可以获取到虚拟表的 rownum 列信息。从而实现分页。
分页查询实现：子查询，别名
示例：分页，查询 5~10 行
select *
from (select rownum r, a.* from t_account a)
where r > 5
and r < 10;
5.函数
- 字符函数：length、substr、concat
- 数值函数：round、trunc
- 日期：add_months、last_day、
- 转换：字符串、日期、数值互转，to_char、to_date、to_number
- 条件：空值处理 nvl、条件取值 decode ...
- 分析函数： 漏
示例：字符串长度
SELECT length('hello') from dual;
select id, length(name) from t_owners;
示例：截取子串
select substr('itheima', 2, 2) from dual;--th
select id, substr(name, 0, 1) from t_owners;--姓
示例：字符串拼爹
select 'hello ' || 'die.' from dual;
select concat('hello', 'die') from dual;
示例：四舍五入
select round(3.1415926) from dual; --
select round(3.1415926, -1) from dual; --
select round(3.1415926, 2) from dual; --3.14
select round(3.1415926, 3) from dual; --3.142
示例：截取（绝对值只会变小）
select trunc(3.1415926) from dual; --
select trunc(3.1415926, 3) from dual; -- 3.141
select trunc(-3.1415926) from dual; -- -3 负数
select trunc(-3.1415926, 3) from dual; -- -3.141 负数
示例：12个月之前，一年后
select add_months(sysdate, -12) from dual;
select add_months(sysdate, 12) from dual;
示例：本月底最后一天的当前时间
select last_day(sysdate), last_day(sysdate + 1), last_day(sysdate + 4)
from dual;
-- 加号没有效果
时间：时间截取
select trunc(sysdate) from dual;--截取日期，截掉时间，效果-1
select trunc(sysdate,'y') from dual;--年份的第一天，没有时间
select trunc(sysdate,'mm') from dual;--年份和月份的第一天，没有时间
select trunc(sysdate,'dd') from dual;--等于 效果-1
select trunc(sysdate,'hh') from dual;--日期时间，分钟为零，秒为零
select trunc(sysdate,'mi') from dual;--日期时间，秒为零
示例：转字符串
select to_char(100) from dual; --
select to_char(sysdate, 'yy') from dual; --
select to_char(sysdate, 'yyyy') from dual; --
select to_char(sysdate, 'dd') from dual; --
示例：转成时间
select to_date('1988-3-16','yyyy-mm-dd') from dual;
示例：空值处理，为空是显示 null
select nvl(null, '空') 空值处理 from dual;--第一个参数为空时，返回第二个参数
select id,nvl(name,'null') from t_edu;--名称列出现空值时，显示字符串 null
把数据库适合的表达方式翻译成程序容易接受理解的内容。
示例：空值处理，NVL2
???
- 条件取值 decode(oxpr, case1, value1, case2, value2 ... [default]);
- 表达是 expr，与 case 比较，比中就返回对应的 value 结束比对，未比中有默认就返回默认。
示例：条件取值（等值比较）
select decode(33, 13,'xi',33,'ha','def') from dual;--ha
select decode(34, 13,'xi',33,'ha','def') from dual;--def
可以使用 case when then 达到一样甚至更加强大的效果，比较就不限于等值比较。
示例： case when then ...
select id,name,area,(case name
when 1 then v1
when 2 then v2
when 3 then v3
else 'other'
end ), (case area
when 1 then a1
when 2 then a2
when 3 then a3
else 'ao'
end ) from t_owners, t_area, t_address
...
6.集合运算
- 并集（union）：
- 交集（intersect）：
- 差集（minus）：
示例：union...
select * from t_account where id >40
intersect
select * from t_account where id <= 45;
select * from t_account where id in (47,48)
union
select * from t_account where id = 45;
select * from t_account where id in (47,48)
minus
select * from t_account where id = 45;
7.Oracle 对象
- 视图 view
- 序列 sequence
- 同义词 synonym
- 索引 index
示例：创建 视图
create view v_test
as
select * from t_test;--创建相同架构的视图 insert into v_test values(33, 'hhhe');
select * from v_test;--插入数据成功
select * from t_test;--插入视图，影响到了表
-----------------------
create view v_test2
as
select rownum id,t.id uuid,t.name from t_test t; select * from v_test2;
insert into v_test2 (uuid,name) values(44, 'work');--插入数据报错：视图操作不合法
-----------------------
create view v_owners
as
select * from t_owners;--创建名称为 v_owners 的视图
create or replace view v_owners
as
select rownum id,o.name,o.addressid,o.housenumber,o.watermeter from t_owners o;--创建视图时，有意隐藏某些列的信息
示例：删除创建视图的表，……
drop table t_test;
select * from v_test;--报错：视图不存在
--重新创建表
create table t_test(
id number,
name varchar2(30)
); insert into t_test values (29, 'xian');
insert into t_test values (10, 'beijing'); select * from t_test;
select * from v_test;--又能查询到数据
select * from v_test2;--又能查询到数据 --创建个其他架构的表，视图依然重新可查询
create table t_test(
id number,
name varchar2(30),
dirty varchar2(1)
);
视图的检查约束，创建的视图内容属于表的某个形式的子集时，必然存在一些过滤条件（不包括分组）。这时再去操作视图，受到约束检查限制。
示例：创建带检查约束的视图
create view v_ownertype
as
select * from t_ownertype where id > 13;--不带检查约束的视图
create view v_ownertype
as
select * from t_ownertype where id > 13
with check option;--创建带检查约束的视图，条件 id > 13.
只读视图，
示例：创建只读视图
create view v_ownertype
as
select * from t_ownertype where id > 13
with read only;--创建只读视图
先创建视图，再创建相应的表。
示例：创建带错误的视图
create force view v_ownertype
as
select * from t_ownertype
with chek option;--带上 force 创建 错误视图
物化视图，实体化视图，查询时，等于查询单表。
示例：创建物化视图
create meterialized view mv_test
as
select ...
物化视图的刷新方式：增量、全量
增量刷新的前提：为基表创建增量物化日志；创建视图时，增加日志依赖列
示例：创建序列
create sequence seq_test;--局部序列
同义词，用来处理兼容性问题
示例：创建同义词
create synonym syn_owners for t_owners;--私有
create public synonym syn_owners for t_owners;--公共
索引：提高查询效率
索引类型的选择，依据的是数据的类型情况：
- -普通索引：非均态分布的线性数据
- -反向键索引：均态分布的线性数据
- -位图索引：非线性数据
示例：创建索引
create index in_name_owners on t_owners(name);--创建索引
create unique index ... --唯一索引
create index in_max_account on t_account(ownerid,ownertypeid);--复合索引
create index in_re_account on t_account(name) reverse;--反向键索引
create bitmap index in_dept on ... --位图索引
位图索引，创建列内容非线性……
8.PL/SQL
- PL/SQL 语法
- 变量
- 异常
- 条件、循环
- 游标
PL/SQL 语法：
--变量： 变量名 类型（长度）；
--赋值： 变量名：=值； [declare
--
]
begin
--
[exception
--
]
end;
示例：声明变量、并赋值
declare
var_price number(10,2);--单价
var_usenum number;
var_money number(10,2);--小计金额
begin
var_price:=3.14;
var_usenum:=23233;
var_money:=var_price*var_usenum;
end; --使用 SQL 语句赋值
begin
select usenum into var_usenum from t_account
where id = '';
示例：引用型声明变量
--属性类型（引用型 表名.列名%type）
declare
var_price t_account.price%type;--单价
var_usenum t_account.usenum%type;
var_money t_account.money%type;--小计金额
begin
select price,usenum into var_price,var_usenum from t_account where id = '';
var_money:=var_price*var_usenum;
end;
示例：记录型声明变量
--属性类型（记录型 表名%rowtype）
declare
var_row_account t_account%rowtype;--记录类型变量
begin
select * into var_row_account from t_account where id = '';
var_price:=var_row_account.price;
var_usenum:=var_row_account.usenum;
var_money:=var_row_account.money;
end;
异常：运行程序时出现的错误。发生异常后，语句将停止执行。预定义异常有 21 种，用户还可以自定义异常。
示例：异常处理
--异常处理格式
exception
when ...异常类型 then
...异常处理逻辑 --异常处理实例
exception
when no_data_found then
...
when too_many_rows then
...
end;
条件判断
语法格式：
--单条件判断
if ... then
业务逻辑
end if;
--多条件判断
if ... then
业务逻辑
elsif ... then
业务逻辑
elsif ... then
业务逻辑
else
业务逻辑
end if;
循环：无条件循环、有条件循环、for循环
示例：无条件循环
--无条件循环，语法格式：
loop
循环体
end loop；
--实例
declare
var_num number;
begin
var_num:=1;
loop--无条件循环
dbms_output.put_line(var_num);
var_num:=var_num+13;
if var_num >= 1000 then
exit;--退出循环
end if;
end loop;--循环结束
end;
示例：有条件循环
--有条件循环，语法格式：
while ...--循环条件
loop
循环体
end loop；
--实例
declare
var_num number;
begin
var_num:=1;
while var_num <= 1000--循环条件
loop
dbms_output.put_line(var_num);
var_num:=var_num+13;
end loop;--循环结束
end;
示例：for循环
begin
for var_tmp in 1 .. 1000--for循环
loop
dbms_output.put_line(v_num);
end loop;
end;
游标：系统为用户准备的数据缓冲区，PL/SQL 中存放 SQL 语句的执行结果。 使用前，须先声明游标：cursor cur_name is SQL 语句;
示例：使用游标
--游标语法
declare
cursor cur_name is SQL 语句;
begin
open cur_name;--激活游标
loop
fetch cur_name into var_tmp;
exit when ...
end loop;
end;
--实例
declare
var_tmp t_pricetable%rowtype;
cursor cur_price is select * from t_pricetable where id > 30 and id <=73;--声明游标
begin
open cur_price;--打开游标
loop
fetch cur_price into var_tmp;--提取游标
exit when cur_price%notfound;--退出循环游标
end loop;
close cur_price;--关闭游标
end;
游标还可以使用参数，……
示例：带参游标，从使用参数的位置看，open 理解为执行游标好像更加合理。执行结束，关闭引用即可
--使用带参游标，参数表示基本工资
--获取薪资 1800 以上，名称是 4 个字符的员工名称
declare
cursor cur_sal(var_sal emp.sal%type)--游标带有参数
is
select distinct ename
from emp
where sal > var_sal and ename like '____';
var_tmp emp.ename%type;--定义临时变量
begin
open cur_sal(1800);--打开游标 loop
fetch cur_sal into var_tmp;--提取游标
exit when cur_sal%notfound;
dbms_output.put_line(var_tmp);
end loop; close cur_sal;
end;
for 循环处理游标，会更加简洁，打开、关闭、退出都会自动完成
示例：for 循环处理 游标（* 注意打印时调用方式）
declare
cursor cur_sal(var_sal emp.sal%type)
is
select ename--获取的是单值，与写成 * 一个效果
from emp
where sal > var_sal and ename like '____';
begin
for v_tmp in cur_sal(800)--读取的可不是单值
loop
dbms_output.put_line(v_tmp.ename);--单值输出方式也...
end loop;
end;
9.存储函数
- 是啥：自定义函数，封装查询
- 用途：查询
- 使用：SQL 语句中调用
- 格式：有返回值，必须返回
--存储函数格式
create or replace function fn_getIt(var_name var_type...)--参数有类型，无长度
return re_type--定义返回值类型，无长度
is
--声明变量
begin
--body
return ...
end;
示例：把练习的 sql 查询语句 改写成 存储函数（查询同一个入职年份全体员工的收入总和）
--scott 的 emp 表中，与某个入职时间同年份进公司的职业收入总和
--参数类型是日期型，与 入职日期 格式一致
create or replace function fn_sum(var_year emp.hiredate%type)
return emp.sal%type
is
var_result emp.sal%type;
begin
select sum(sal + nvl(comm, 0)) into var_result
from emp
where to_char(hiredate, 'yyyy') = to_char(var_year, 'yyyy');
return var_result;
end; --调用存储函数
select fn_sum(to_date('', 'yyyy-mm-dd')) from dual; --执行结果
SQL> select fn_sum(to_date('', 'yyyy-mm-dd')) from dual; FN_SUM(TO_DATE('','YYYY-MM-DD'))
----------------------------------------
25025 SQL>
示例： 把练习的 sql 查询语句 改写成 存储函数（查询某个时间之后入职的在职员工总数）
--统计某个日期时间之后入职的员工总数
--参数是字符串形式的日期类型
create or replace function fn_count(var_date varchar2)
return number
is
var_count number;
begin
select count(*) into var_count
from emp
where hiredate > to_date(var_date,'yyyy-mm-dd'); return var_count;
end; --调用存储函数
select fn_count('1981-07-01') from dual; --在 sqlplus 中截取执行结果
SQL> select fn_count('1981-07-01') from dual; FN_COUNT('1981-07-01')
----------------------
8 SQL>
* 遗憾的是，这里的示例只能返回单值，不能返回集合。
10.存储过程
- 是啥：对外封装函数
- 用途：修改（uid），java 程序使用
- 调用：call、PL/SQL 中直接调用名称、程序中调用（"{call pro_name}"）
- 参数模式：in、out、in out，默认 in
- 格式：没有返回值
--存储过程格式
create or replace procedure pro_name (var_name [参数模式] var_type...)
is|as
--声明变量
begin
--body
end;
示例：删除 scott 库部门表的部门信息，没有提交语句，在调用后查询不能立即生效。
--创建存储过程
--按照提供的部门编号，删除部门信息
create or replace procedure pro_delete(var_id in number)
is begin
delete from dept where deptno = var_id;
end; --调用存储过程
call pro_delete(40);--使用 call 调用 begin
pro_delete(20);--使用 PL-SQL 调用存储过程
end;
示例：修改 scott 库部门表的部门信息，增加提交语句
--创建存储过程 --参数 var_id：表中的现有的部门编号； --var_name：表中新的部门名称
create or replace procedure pro_update(var_id dept.deptno%type, var_name dept.dname%type)
is
begin
update dept set dname = var_name where deptno = var_id;
commit;--须提交才能生效
end; --调用
call pro_update(40, 'zhaoccai');--调用存储过程，修改表
示例：增加 scott 库部门表的部门信息，带提交语句
--创建存储过程
create or replace procedure pro_insert(var_id dept.deptno%type,var_name dept.dname%type, var_loc dept.loc%type)
is
begin
insert into dept (deptno, dname, loc) values(var_id, var_name, var_loc);
commit;--提交生效
end; --调用存储过程
call pro_insert(30, 'tjhh', 'haqiu');--调用存储过程增加部门信息 --调用存储过程
begin
pro_insert(50,'runhe','nanjing');
end;
示例：存储过程的调用方法
--SQL 语句调用存储过程
call pro_insert(30, 'tjhh', 'haqiu');--调用存储过程增加部门信息 --PL-SQL 调用存储过程
begin
pro_insert(50,'runhe','nanjing');
end; -- Java 语言中调用
stmt_insert = conn.prepareCall("{call pro_insert(?, ?, ?)}");
stmt_delete = conn.prepareCall("{call pro_delete(?)}");
stmt_update = conn.prepareCall("{call pro_update(?, ?)}");
11.触发器
- 用途：数据确认、安全检查、审计、数据备份……
- 伪记录变量：:new、:old
- *注意：自动提交，无须 显示 调用 commit()
- 格式：
--创建触发器
create or replace trigger tri_name
before | after--触发时机
[delete] [[or] insert] [[or] update [of column_name]]--监控点
on table_name--一定是依附于某个表
[for each row]
[when(条件)]--触发点
declare begin
--body
end;
A