数据库

Oracle-基础

略

Oracle与MySQL基本类似,下面说的是Oracle独有的一些知识.

表与数据的创建,修改,删除

数据类型

  1. CHAR (固定长度字符类型,2000字节)
  2. VARCHAR2 (可变长度字符类型,4000字节)
  3. LONG (大文本类型,2G)
  4. NUMBER (数值类型)

数据的增删改

Orcale的控制台中,对数据的操作需要COMMIT;后才能提交.

Oracle与JDBC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
//加载驱动
static {
	try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
     } catch (ClassNotFoundException e) {
         e.printStackTrace();
     }
}
//获得连接
public static Connection getConection() throws SQLException {
	return DriverManager.getConnection("jdbc:oracle:thin:@IP地址:1521:orcl", "账户", "密码");
}
//关闭连接
public static void closeAll(java.sql.ResultSet rs,
                                java.sql.Statement stmt, java.sql.Connection conn) {
        //关闭结果集
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭执行对象
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭执行对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

整库的导入与导出

full=y是整库

1
exp system/密码 full=y

file=文件名是指定文件

1
exp system/密码 file=文件名 full=y

导入

1
imp system/密码 full=y file=water.dmp

其他参数

  1. owner=wateruser(按用户导入导出)
  2. tables=t_account,t_are(按表导入导出)

查询

伪列的使用

  1. ROWID

    表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用ROWID 可以显示行是如何存储的。 

    1
    2
    select rowID,t.*
    from T_area t;

  2. ROWNUM

    查询的结果集的序号.

    1
    select rownum,t.* from T_ownertype t;

连接查询

外连接使用FROM-WHERE方式时使用(+)来表示显示空值

1
2
3
4
5
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM
T_OWNERS ow,T_ACCOUNT ac
WHERE ow.id=ac.owneruuid(+);--左外
-- 或者
WHERE ow.id(+)=ac.owneruuid;--右外

分页查询

Oracle没有limit语句,所以分页需要特殊处理

三层嵌套来实现分页

1
2
3
4
5
select * from
	(select rownum r,t.* from
    	(select * from T_account order by usenum desc) t
    where rownum <= 20) -- 有伪列存在where条件不可使用大于
where r > 10;
  • 最内层:用于得到所有数据,并且排序好.
  • 中间层:用于使用rownum伪列查询出小于多少的数据.
  • 最外层:用于控制大于多少的数据.

常见函数的使用

查表

行列转换

1
2
3
4
5
6
7
8
9
10
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else
0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else
0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else
0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else
0 end) 第四季度
from T_ACCOUNT where year='2012' group by areaid

每一列使用内联函数或者子查询来得到数据.

分析函数

用于排名显示

  1. rank()

    相同的值排名相同,排名跳跃

  2. dense_rank()

    相同的值排名相同,排名连续

  3. row_number()

    排名连续.

例如:

1
2
3
4
5
6
7
8
select rank() over(order by usenum desc ),usenum 
from T_ACCOUNT;
--
select dense_rank() over(order by usenum desc ),usenum
from T_ACCOUNT;
--
select row_number() over(order by usenum desc ),usenum
from T_ACCOUNT;

集合运算

  1. UNION [ALL]

    并集[包括重复数据]

  2. INTERSECT

    交集

  3. MINUS

    差集

视图

普通视图

1
2
3
4
CREATE [OR REPLACE] [FORCE] VIEW view_name --force 无表也会创建
AS subquery
[WITH CHECK OPTION ] -- 遵守表内约束
[WITH READ ONLY]	-- 只读

物化视图

1
2
3
4
5
6
7
8
9
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]	-- 构建视图模式 数据创建自动导入模式和非自动导入模式
REFRESH [FAST|COMPLETE|FORCE]	-- 刷新模式 增量|全部|系统自选
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT -- 数据更新后:手动导入,自动导入模式
(next_time)
]
AS
subquery

手动刷新代码

1
2
3
begin
DBMS_MVIEW.refresh('物化视图名','C');
end;

增量刷新物化视图

  1. 需要先创建视图日志
1
2
create materialized view log on t_address with rowid；
create materialized view log on t_area with rowid
  1. 创建物化视图
1
2
3
4
5
6
7
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name
adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;

序列

Oracle没有auto_increatment属性使用序列实现.

1
2
3
4
5
6
7
create sequence myseq
increment by 1|-1	--正为增加,负为减少
start with 1	-- 开始于
maxvalue 100	-- 最大
minvalue 1		-- 最小
cycle|nocycle  -- 是否循环
cache 20|nocache	-- 是否缓存,默认20

同义词

想到与别名

1
create [public] synonym mysyn for 表名

索引

其他略

反向链索引: 对于连续的字段,使用其来打乱顺序

1
create index ind_name on 表名(列名) reverse;

位图索引: 用于对枚举类型的列加索引

1
create bitmap index 索引名 on 表名(列名);

PL/SQL

1
2
3
4
5
6
7
8
9
[declare
--声明变量
]
begin
--代码逻辑
[exception
--异常处理
]
end;

变量

声明格式 : 变量名 类型(长度)

赋值语法: 变量名:=变量值

属性类型

使用表的类型,而不用明确指定

1
2
3
4
v_usenum T_ACCOUNT.USENUM%TYPE;--水费字数
v_num0 T_ACCOUNT.NUM0%TYPE;--上月字数
-- 或者
v_account T_ACCOUNT%ROWTYPE;--记录型

%ROWTYPE推荐使用

异常

系统自带的21个;重要的有NO-DATA-FOUND和TOO-MANY-ROWS

捕获方式:

1
2
3
exception
when 异常类型 then
异常处理逻辑

条件判断,循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
declare
v_num number:=1;
begin
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
exit when v_num>100;
end loop;
end ;

--

declare
v_num number:=1;
begin
while v_num<=100
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end ;

--

begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;

存储函数

1
2
3
4
5
6
7
8
9
10
11
CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;

存储过程

参数列表中可以使用 out 来输出数据

1
2
3
4
5
6
7
8
9
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace procedure pro_owners_add
(
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_type number,
v_id out number
)
is
begin
select seq_owners.nextval into v_id from dual;
insert into T_OWNERS
values( v_id,v_name,v_addressid,v_housenumber,v_watermete
r,sysdate,v_type );
commit;
end;

使用:

1
2
3
4
5
6
declare
v_id number;--定义传出参数的变量
begin
pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id); --v_id会被存储过程所赋值
DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;

触发器

1
2
3
4
5
6
7
8
9
10
CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ] -- 行级,或者非行级(一般都写行级,加上FOR EACH ROW)
declare
-- ....
begin
PLSQL 块
End ；

用于表中,可以绑定到字段,当字段发生改变,或表发生改变,就会触发开关,根据before或者after来决定之前或者之后来执行触发器的内容.

对于PLSQL块

可以使用:OLD,:NEW 来代表语句执行前,后的数据.例如:

1
2
3
4
5
6
7
8
9
create or replace trigger tri_account_updatenum1
before
update of num1 -- 每当num1列发生改变
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0; -- usenum就会根据num1-num0来得到
end;
  • insert 没有old,有new
  • update 都有
  • delete 有old,没有new

对于before可以改变new的数据,对于after只能获取到值,而不可以改变

after的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners北京市昌平区建材城西路金燕龙办公楼一层 电话：400-618-9090
for each row
declare
begin
insert into t_owners_log
values(sysdate,:old.id,:old.name,:new.name);
end;