Oralce学习笔记(4)

1.数据字典

1.1简介

Oracle数据库中的表包括用户所定义的表及oracle系统所产生的表,两种类型。

数据字典表：描述数据库相关信息，运行状态，有多个用户，每个用户有哪些表，表的结构是什么，表空间，系统权限，角色。Oracle运行相关的数据均存在数据字典表中。执行DDL语句均会对数据字典表的内容产生变化。 (基表---视图)。

 

数据字典表大部分属于SYS这个帐户。

 

1.2命名规则

数据字典表的前缀：

USER     用户自己的

ALL 用户可以访问到的

DBA       管理员视图

V$   性能相关的数据

 

select * from user_tables 查询出当前用户所拥有的所有表

select * from all_tables 查询可以访问到所有表

select * from dba_tables　查询系统中所有的表

 

Ｃ、DICTIONARY存放了所有字典表

 

select * from DICTIONARY;

 

 

(2)、常用的字典表

select * from user_objects;

select * from USER_TAB_COLUMNS;--查表的列定义信息

select * from USER_TAB_COLUMNS wheretable_name='EMP';

select * from USER_CONSTRAINTS;

select * from USER_CONS_COLUMNS wheretable_name='EMP';

select * from user_views;

select * from user_sequences;

select * from user_synonyms;

select * from DBA_TAB_PRIVS;--对象权限

select count(*) from DBA_SYS_PRIVS;---系统权限

2. 方案schema

方案是一个用户所拥有的所有数据库对象及其它资源的集合。

hr.emp==>访问hr这个方案的emp对象。

每创建一个用户，就会立即创建与这个用户同名的方案。用户名与方案名是一对一关系。

3.数据导入导出及数据库维护

操作系统命令:exp导出数据

            imp导入数据

 exp-help

 imp-help

导出：

exp scott/test tables=(emp,dept) log=(f:\test\emp.log)file=(f:\test\emp.dump)

 

导出某几个用户下所有数据：

exp scott/test owner=(scott,test)log=(f:\test\emp.log) file=(f:\test\emp.dump)

导入:

 impscott/test file=(f:\test\emp.dump)

 

跨用户导入：

imp system/test fromuser=(scott)touser=(test) file=(f:\test\dept.dmp)

host imp system/test fromuser=scotttouser=test file=(f:\test\dept.dmp)

4.闪回(falshback)

4.1概述

传统恢复数据库的方法，靠备份。(备份定期进行，每天1次，全备份,增量备份)，数据库大时非常不方便，消耗很多时间。效率都非常差。

恢复数据是对正确及错误数据打包处理。

 

oracle 9i推出回收站的功能,10g,11g以后功能更加强大。可以通过数据使用命令快速恢复数据。闪回不依赖于备份，系统会自动根据操作日志来完成恢复。

闪回只对错误的数据进行处理。

 

闪回应用场景

错误删除数据commit,如何找回？

错误删除表,如何找回?

如何获得表中的历史记录?

如何撤销一个错误的事务(多个dml语句)

4.2闪回技术支持

1）闪回的类型

A、闪回表，把表回退到过去的一个时间上；(flashback table)

B、闪回删除:回收站；(flashbackdrop)

C、闪回版本查询(重点是查询):通过select获取历史记录；所有提交的历史记录。(flashback versionquery)

D、闪回事务查询：通过undo_sql撤销事务。(flashback transaction query)

E、闪回次数据库；把数据库回退到过去的一个时间上。

F、闪回归档日志

 

2）闪回的语法

FLASHBACK TABLE [schema.]<table_name>

TO 

{[BEFORE DROP [RENAME TO table]] 

[SCN|TIMESTAMP]expr

[ENABLE|DISABLE]TRIGGERS}

 

3）闪回表

把表回退到过去的一个时间上(或者系统改变号上).

 

SCN--系统改变号。

select timestamp_to_scn(sysdate)from dual;查询某一时间对应的系统改变号。

授权scott执行闪回表

grant flashback any table to scott;

闪回表的语法：

flashback table 表名 to scn 1182535;

flashback table 表名 to TIMESTAMP to_date('...');

没有启用行移动功能，不能闪回表。

启动行移动：

alter table flashback_table enable rowmovement;

alter table t1 enable row movement;

执行闪回

flashback table t1 to scn 1182535;

 

4）闪回删除(回收站)

闪回删除－回收站(只有普通用户才有回收站)。

 

命令:flashbacktable 表名 to beforedrop [rename to 新表];  表名可以是要闪回表的真实名字，也可以是在回收站中的名字。

 

回收站操作：

show recyclebin 显示回收站内容

purge recyclebin 清空回收站内容

删除表的时候指定不要放到回收站

drop tabel test purge;（不在回收站中的表无法闪回）

 

使用回收站中表名称来闪回表

flashback table"BIN$PkH2wA+WTZO6DCzCn9ryew==$0" to before drop;

 

闪回删除，并把闪回的表进行改名

flashback table dept1 to before drop renameto dept2;

 

5）闪回版本查询--查询(select),(flashback version query)

获得对一张表的操作完整的过程。

 

select cname from table_name versionsbetween scn 111 and 7777.

查看以前的数据。

 

select vid,content  from version_table versions between timestampminvalue and maxvalue;

 

查询操作时间的伪列

versions_operation,versions_starttime,versions_endtime,versions_xid(事务ID)

 

selectvid,content,versions_operation,versions_starttime,versions_endtime  from version_table versions between timestampminvalue and maxvalue

 

6）闪回事务查询

如果想要取消某一个事务.就需要通过flashback_transaction_query这个视图中获得这个取消该事务的sql语句(undo_sql).

要查询某一个事务的undo_sql，就需要获得事务xid，通过ersions_xi伪列可以查。

 

SQL> selectversions_operation,versions_starttime,versions_xid,deptno,dname,loc from dept1versions

between scn minvalue and maxvalue;

查询un_sql

select undo_sql fromflashback_transaction_query where xid='070001005A010000'

执行查询到的undo_sql。

查询事务版本需要权限：selectany transaction

5. PL/SQL编程基础

5.1概述

PL/SQL（Procedure Language/SQL）

PLSQL是Oracle对sql语言的过程化扩展

指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

5.2PL/SQL语法

declare

     说明部分    （变量说明，光标申明，例外说明〕

begin

     语句序列   （DML语句〕…

exception

     例外处理语句  

end;

简单例子：

set serveroutput on

declare 

--声明变量

v_name varchar2(50);--普通变量

v_sal number(6) :=5000;--声明变量并指定默认值

v_job emp.job%TYPE;--变量的类型与emp表中的job字段相同,引用型变量

v_job1 v_job%TYPE default 'hello';--引用

v_date date;

begin

--给变量赋值1

v_date :=sysdate;

dbms_output.put_line(v_date);

--给变量赋值2,从表中查询数据并赋给变量

select job into v_job from emp where empno=7369;

dbms_output.put_line(v_job);

--同时给多个变量赋值

select job,ename into v_job1,v_name fromemp where empno=7499;

dbms_output.put_line(v_name||':'||v_job1);

end;

5.3记录类型变量

set serveroutput on

declare 

--声明变量

v_r emp%ROWTYPE;--记录型变量,v_r可以存emp表的一行记录

v_name emp.ename%TYPE;

begin

--给记录型变量赋值

select * into v_r from emp whereempno=7369;

v_name:=v_r.ename;

dbms_output.put_line('员工姓名:'||v_name||',工资'||v_r.sal);

end;

5.4 if和循环语句

if语句：

set serveroutput on

--通过accept(SQLPLUS命令)接收键盘输入

--num:地址

accept num prompt '请输入一个数字';

declare 

--声明变量,&num变量引用内存地址

v_num number :=&num;

begin

--使用if来判断0，大于0,还是小于0

if v_num>0 then

dbms_output.put_line('大于0');

elsif v_num<0 then

dbms_output.put_line('小于0');

else

dbms_output.put_line('等于0');

end if;

end;

循环语句(3种方式)：

set serveroutput on

declare 

--声明变量,&num变量引用内存地址

v_num number :=10;

v_i number;

begin

--循环输出1到v_num

--while循环1

v_i:=1;

while(v_i<=v_num) loop

dbms_output.put_line(v_i);

v_i:=v_i+1;

end loop;

dbms_output.put_line('循环1结束........');

--循环2loop...end looop

v_i:=1;

loop

dbms_output.put_line(v_i);

v_i:=v_i+1;

/*

if(v_i>10) then

exit;

end if;*/

exit when v_i>10;

end loop;

dbms_output.put_line('循环2结束........');

 

--循环3,for  in loop end loop

for v_i in 1..v_num loop

dbms_output.put_line(v_i);

end loop;

dbms_output.put_line('循环3结束........');

end;

--dbms_output.put(v_i);不换行

 

输出99表：

set serveroutput on

declare 

--声明变量

v_i number;

v_j number;

begin

--输出9*9表

--dbms_output.put(v_i);不换行

for v_i in 1..9 loop

for v_j in 1..v_i loop

dbms_output.put(v_j||'*'||v_i||'='||(v_i*v_j)||'');

end loop;

dbms_output.put_line('');

end loop;

end;

6.游标CURSOR

1)声明游标

CURSOR 光标名  [ (参数名  数据类型[,参数名数据类型]...)]

     IS  SELECT   语句

普通游标：

CURSOR c_emp is select * from emp wheredeptno=20;

 

带参数游标:

CURSOR c_emp(v_deptno) is select * from emp where deptno=v_deptno;

 

2)在程序中使用游标

打开游标

 openc_emp;

取一行光标的值：fetchc_emp into  r_emp;

读值

fetch c_emp into  r_emp;

 

关闭光标 closec_emp;

 

读取游标属性：

%ISOPEN 是否已经打开

%NOTFOUND 找不到数据

%FOUND 是否找到数据

%ROWCOUNT 当前取到第几条

访问c_emp游标的%ROWCOUNT值:c_emp%ROWCOUNT

 

3)普通游标示例

declare 

--声明变量

--声明游标

CURSOR c_emp is select * from emp wheredeptno=20;

v_r emp%ROWTYPE;

begin

--打开游标

open c_emp;

--从游标取值

loop

fetch c_emp into v_r;

--如果取不到数据则退出

exit when c_emp%NOTFOUND;

--读值

dbms_output.put_line(c_emp%ROWCOUNT||':'||v_r.ename||'='||v_r.sal);

end loop;

--关闭光标

close c_emp;

end;

 

4)带参数的游标示例:

declare 

--声明变量

--声明带参数的游标

CURSOR c_emp(v_deptno number) is select *from emp where deptno=v_deptno;

v_r emp%ROWTYPE;

begin

--打开带参数的游标,要讲参数传进去

open c_emp(20);

--从游标取值

loop

fetch c_emp into v_r;

--如果取不到数据则退出

exit when c_emp%NOTFOUND;

--读值

dbms_output.put_line(c_emp%ROWCOUNT||':'||v_r.ename||'='||v_r.sal);

end loop;

--关闭光标

close c_emp;

end;

7.例外、异常

1）语法

begin

--程序体

exception

when 异常1 then

...

when 异常2 then

...

when others then

处理其它异常的代码

2）系统自定义的异常

No_data_found

Too_many_rows

Zero_Divide

Value_error

Timeout_on_resource

3）处理普通异常示例

set serveroutput on

--给员工涨工资的程序引入存储过程，如果员工职位是经理，涨20%，如果销售,15%,其它涨10%，总的可以涨的工资总额不能超过2000元。

declare 

--声明变量

v_sal emp.sal%TYPE;

begin

dbms_output.put_line(5/0);--

select sal into v_sal from emp wheredeptno=20;

dbms_output.put_line('工资:'||v_sal);

 

exception

when Too_many_rows then

dbms_output.put_line('数据太多!');

when others then

dbms_output.put_line('其它异常!');

end;

4）自定义异常

声明异常

  not_data exception;

程序体，抛出异常:

 raise not_data

处理异常

exception

  when not_data then

5) 自定义异常示例

set serveroutput on

accept num prompt '请输入';

declare 

--声明变量

v_num number:=&num;

e_errorData exception;

begin

 

if(v_num=0) then

--抛出异常:

raise e_errorData;

end if;

dbms_output.put_line('正确的数字:'||v_num);

 

exception

--处理异常

when e_errorData then

dbms_output.put_line('数据不能为0!');

when others then

dbms_output.put_line('其它异常!');

end;

8.存储过程及函数

8.1概述

指存储在数据库中供所有用户程序调用的子程序（基于pl/sql语法写出来程序）叫存储过程、存储函数。

 

为什么要用存储过程：

A、提升性能

B、安全性考虑

C、把复杂的业务放到存储过程。便于dba管理。

 

存储过程不好的地方：

代码不好维护;不容易扩展。

存储过程和存储函数的区别：

存储过程可以有返回值也可以没有返回值，存储函数必须有返回值。

8.2存储过程的使用

1）创建存储过程

create [or replace] PROCEDURE 过程名(参数列表)  AS

--声明变量

begin

--程序体

exception

 

end [过程名];

 

2）示例（涨工资）

CREATE OR REPLACE PROCEDURE RAISESALARY

(

 V_DEPTNO IN EMP.DEPTNO%TYPE 

, V_RATE IN NUMBER default 0.1

) AS

--声明变量

BEGIN

update emp set sal=sal*(1+v_rate) wheredeptno=v_deptno;

---加更多更好业务逻辑

END RAISESALARY;

 

3）调用存储过程

exec[ute] raisesalary(20,0.2);

call raisesalary(20,0.2);

8.3存储函数的使用

 

1）创建存储函数

 

CREATE [OR REPLACE] FUNCTION 函数名(参数列表)

 RETURN  函数值类型

AS

 

begin

 

return 返回值;

 

exception

 

end 函数名;

 

2)示例(获得某一个部门的平均工资)

 

CREATE OR REPLACE FUNCTION GETDEPTAVGSAL

(

 V_DEPTNO IN EMP.DEPTNO%TYPE 

) RETURN EMP.SAL%TYPE AS

--声明变量

v_sal emp.sal%TYPE;

 

BEGIN

 select avg(sal) into v_sal from emp where deptno=V_DEPTNO;

 RETURN v_sal;

END GETDEPTAVGSAL;

 

3)调用存储函数

查询语句里面调:

select getdeptavgsal(20) from dual;

可以在其它存储过程及函数中调用。

9.触发器

9.1概述

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

 

触发器的类型：

语句级触发器

在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

行级触发器（FOR EACHROW）：

触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

触发器是一段pl/sql程序,程序是存储在数据库中。调用不是由用户来调用。会在特定的条件下自动执行。

 

执行dml语句时候，他可能运行。

相当在后台设置监控程序，满足运行即运行。触发器。

9.2创建触发器

CREATE [or REPLACE] TRIGGER  触发器名

 

 {BEFORE | AFTER}

  {DELETE | INSERT | UPDATE [OF 列名]}

  

 ON  表名

  

[FOR EACH ROW [WHEN(条件) ] ]

 

being

 PLSQL 块

end;

9.3示例

create or replace trigger mytest

before insert

on emp

declare

--声明变量

begin

 

dbms_output.put_line('准备插入数据!');

if(to_char(sysdate,'yyyy')='2012') then

 raise_application_error(-20003,'2012世界末时，不用操作数据!');

end if;

end;

 

当执行往emp中insert数据时，会引上面的触发器运行。

SQL> insert into emp(empno,ename)values(999,'aaa');

准备插入数据!

insert into emp(empno,ename)values(999,'aaa')

           *

第 1 行出现错误:

ORA-20003: 2012世界末时，不用操作数据!

ORA-06512: 在 "SCOTT.MYTEST", line 7

ORA-04088: 触发器 'SCOTT.MYTEST' 执行过程中出错

10. Java中调用存储过程及函数

publicclass OracleTest {

      @Test

      publicvoid callProcedure() throws Exception{

           Class.forName("oracle.jdbc.OracleDriver");

           Stringurl="jdbc:oracle:thin:127.0.0.1:1521:orcl";

           Connectionconn= DriverManager.getConnection(url, "scott","test");

           CallableStatementstmt=null;

           try{

           //调用存储过程的语法

           //Statement/PreparedStatement/CallableStatement

            stmt= conn.prepareCall("{callRAISESALARY(?,?)}");

           //设置参数

           stmt.setObject(1,20);

           stmt.setObject(2,0.2);

           //调用

           stmt.execute();

           }

           catch(Exception e){

                 e.printStackTrace();

           }

           finally{

                 if(stmt!=null)stmt.close();

                 if(conn!=null)conn.close();

           }

      }

      @Test

      publicvoid callFunction() throws Exception{

           Class.forName("oracle.jdbc.OracleDriver");

           Stringurl="jdbc:oracle:thin:127.0.0.1:1521:orcl";

           Connectionconn= DriverManager.getConnection(url, "scott","test");

           CallableStatementstmt=null;

           try{

                 //方式1

           //调用存储函数的语法{?=call GETDEPTAVGSAL(?)}

           //Statement/PreparedStatement/CallableStatement

            stmt= conn.prepareCall("{?=callGETDEPTAVGSAL(?)}");

            //设置参数

            //如果是数据库端传回来的参数，则要注册为输出型参数

            stmt.registerOutParameter(1,OracleTypes.NUMBER );

           stmt.setObject(2,20);

           //调用

           stmt.execute();

           //读取结果

           System.out.println("结果："+stmt.getDouble(1));

           //方式2

           //使用sql语句函数

           //函数中不能有dml语句

           ResultSetrs= conn.createStatement().executeQuery("selectGETDEPTAVGSAL(20) from dual");

           if(rs.next()){

                 System.out.println(rs.getObject(1));

           }

           rs.close();

           }

           catch(Exception e){

                 e.printStackTrace();

           }

           finally{

                 if(stmt!=null)stmt.close();

                 if(conn!=null)conn.close();

           }

      }

}