Oracle期末总复习
1.Oracle体系结构
2.启动Oracle
第一章 truncate table 与 delete 区别
(一)关于truncate table 与 delete 区别
create table t(id int,name varchar2(30));
begin
for i in 1..10000 loop
insert into t values(i,dbms_random.string('A',30));
end loop;
end;
analyze table t compute statistics; --分析函数统计
select * from user_tables where table_name = 'T';
delete from t;
analyze table t compute statistics;
select * from user_tables where table_name = 'T';--观察和未删除前的变化
--分析查询的效率问题,再次插入数据
begin
for i in 1..10000 loop
insert into t values(i,dbms_random.string('A',30));
end loop;
end;
alter system flush shared_pool;
alter system flush buffer_cache;
set autotrace on | set autotrace traceonly;
SQL> select * from zhx.t;
10000 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=45)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=1 Bytes=45
)
Statistics
----------------------------------------------------------
252 recursive calls
0 db block gets
757 consistent gets
67 physical reads
0 redo size
484211 bytes sent via SQL*Net to client
7838 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> delete from zhx.t; --删除所有的数据后
SQL> commit;
SQL> alter system flush buffer_cache;--清空数据缓冲区
System altered.
SQL> alter system flush shared_pool;--清空library cache
System altered.
--现在已经没有数据了,但是我们可以做一下查询
SQL> select * from zhx.t;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=45)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=1 Bytes=45
)
Statistics
----------------------------------------------------------
252 recursive calls
0 db block gets
94 consistent gets
67 physical reads
0 redo size
284 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
0 rows processed
---现在再次插入数据然后用 trucate 来做试验
区别有两种，一是truncate速度快，无法回滚，因为truncate不是dml语句；二是truncate能降低HWM，
而delete 无法降低HWM，因此无法表在用delete删除，表的大小没有改变！
从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),
就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，
10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。
SQL> create tablespace ts_auto datafile 'd:\ts_auto.dbf' size 100m extent management local segment space management auto;
alter table table_name enable row movement ;
语法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
收缩表，相当于把块中数据打结实了，但会保持 high water mark;
alter table <tablespace_name> shrink space;
收缩表，降低 high water mark;
alter table <tablespace_name> shrink space cascade;
收缩表，降低 high water mark，并且相关索引也要收缩一下。
第二章 表连接
(一)多个表的连接
--查询每个员工的部门信息
SQL> select ename,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno;
--使用表别名
SQL> select ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno;
--查询每个员工的直接领导者姓名
SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
(二)外连接
在emp表插入两条数据，但mgr为null，执行
SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
会发现这些人没有出现在结果集中，原因?
左外连接
SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
ENAME ENAME
---------- ----------
zhang
wang
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES MARK
MARTIN BLAKE
BLAKE MARK
CLARK MARK
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
MARK
16 rows selected
修改emp表某些职工的部门号为空，我们想看每个人所在的部门信息时，就不能查看到每一个职工
SQL> select ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno; --可以发现少了人
使用左连接
SQL> select ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno(+);
--查询每个部门的职工姓名，没有职工的也要显示
SQL> select d.deptno,dname, ename from emp e,dept d where e.deptno(+) = d.deptno;
(三)SQL/92
3.1 inner join
(1)
SQL> select e.deptno,dname,ename
from emp e ,dept d
where e.deptno = d.deptno order by deptno;
(2) using on clauses
SQL> select e.deptno,dname,ename
from emp e
inner join dept d
on e.deptno = d.deptno
order by deptno;
–条件必须是等值连接，并且表中有相同的列名
(3) using keyword
select deptno,dname ,ename
from emp e
inner join dept d using(deptno) order by deptno;
3.2 left outer join
(1)
SQL> select ename, e.deptno,dname
from emp e,dept d
where e.deptno = d.deptno(+) order by deptno;
(2)using on clauses
SQL> select ename,e.deptno,dname
from emp e
left outer join dept d
on e.deptno = d.deptno
order by deptno;
(3) using keyword
SQL> select ename ,deptno,dname
from emp e
left outer join dept d
using(deptno) order by deptno;
3.3 right outer join
(1)
SQL> select d.deptno,dname,ename
from emp e ,dept d
where e.deptno(+) = d.deptno;
(2) on clauses
SQL> select d.deptno,dname,ename
from emp e
right outer join dept d on e.deptno = d.deptno;
(3) using keyword
SQL> select deptno,dname,ename
from emp e
right outer join dept d using(deptno);
3.4 full outer join
不能写两个"+"
(1)
SQL> select d.deptno,dname,ename
from emp e
full outer join dept d on e.deptno = d.deptno;
(2)
SQL> select deptno,dname,ename
from emp e
full outer join dept d using(deptno);
第三章 函数
(一) replace()
-- replace()
select replace('zhangsan','zhang' ,'wang') from dual;
--不指定被替换的字符则为删除
select replace('zhangsan','zhang') from dual;
(二) trim()
select (' zhangsan ') || 'is a student' from dual;
--去掉左右空格
select trim(' zhangsan ') || 'is a student' from dual;
--删除字符串的前后某个字符(只能是一个)
select trim('z' from 'zzhangsanzz') from dual;
--功能同上
select trim(both 'z' from 'zzhangsanzz') from dual;
--只去除左边的某个字符
select trim(leading 'z' from 'zzhangsanzz') from dual;
--只去除右边的某个字符
select trim(trailing 'z' from 'zzhangsanzz') from dual;
(三) round()
--round 函数
SQL> select round(1234.456,2) from dual;
ROUND(1234.456,2)
-----------------
1234.46
SQL> select round(1234.756) from dual;
ROUND(1234.756)
---------------
1235
SQL> select round(1238.456,-1) from dual;
ROUND(1238.456,-1)
------------------
1240
(四) trunc()
--trunc函数
SQL> select trunc(1238.456,2) from dual;
TRUNC(1238.456,2)
-----------------
1238.45
SQL> select trunc(1238.456) from dual;
TRUNC(1238.456)
---------------
1238
SQL> select trunc(1238.456,-1) from dual;
TRUNC(1238.456,-1)
------------------
1230
(五) mod()
--mod 函数
SQL> select mod(3,2) from dual;
MOD(3,2)
----------
1
(六) months_between()
--months_between
SQL> select months_between(sysdate,to_date('2013-09-30','YYYY-MM-DD')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
0.278933318399044
SQL> select months_between(sysdate,to_date('2013-09-01','YYYY-MM-DD')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
1.21442017622461
(七) add_months()
--add_months函数
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2013-11-7 15:33:51
(八) next_day()
--next_day
--求当前日期的下个星期一，（1：表示星期日）
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
-------------------
2013-10-14 15:39:11
(八) last_day()
--last_day(本月的最后一天)
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2013-10-31 15:42:
(九) 按年月取四舍五入
--按月取四舍五入
SQL> select round(sysdate,'MONTH') from dual;
ROUND(SYSDATE,'MONTH')
----------------------
2013-10-1
SQL> select round(to_date('2013-10-23','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE('2013-10-23','YY
------------------------------
2013-11-1
--按年四舍五入
SQL> select round(sysdate,'YEAR') from dual;
ROUND(SYSDATE,'YEAR')
---------------------
2014-1-1
SQL> select round(to_date('2013-1-23','YYYY-MM-DD'),'YEAR') from dual;
ROUND(TO_DATE('2013-1-23','YYY
------------------------------
2013-1-1
--按月取整
SQL> select trunc(sysdate,'MONTH') from dual;
TRUNC(SYSDATE,'MONTH')
----------------------
2013-10-1
SQL> select trunc(to_date('2013-10-23','YYYY-MM-DD'),'MONTH') from dual;
TRUNC(TO_DATE('2013-10-23','YY
------------------------------
2013-10-1
--按年取整
SQL> select trunc(sysdate,'Year') from dual;
TRUNC(SYSDATE,'YEAR')
---------------------
2013-1-1
SQL> select trunc(to_date('2013-1-23','YYYY-MM-DD'),'YEAR') from dual;
TRUNC(TO_DATE('2013-1-23','YYY
------------------------------
2013-1-1
第四章 转换函数
(一) to_char
SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD')
-----------------------------
2013-10-14
SQL> select ename,to_char(hiredate,'YYYY-MM-DD hh24:mi:ss') from emp;
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
10月
SQL> select ename,sal, to_char(sal,'$99,999.00') salary from emp ;
--如果实际位数超过格式规定的位数?
SQL> select ename,sal,to_char(sal,'$9,999.00') from emp;
(二) to_date
select to_date('2021-12-08','YYYY-MM-DD') from dual;
(三) nvl
SQL> select ename ,sal,sal+nvl(comm,0) from emp;--如果comm为null 则为 0 类型必须与comm一致
(四) nvl2
SQL> select ename,sal,comm, sal+nvl2(comm,comm,0) from emp; --不为空,返回第二个参数值,为空返回第三个参数值
SQL> select ename,sal,comm, sal+nvl2(comm,1000,0) from emp; --不为空,加1000;为空:加0
(五) group by
-
求Emp表中各个部门的工资之和
-
列出部门的名称
-
having
-
rollup
-
cube
SQL> select deptno ,job,avg(sal)
from emp group by deptno,job;
SQL> select deptno ,job,avg(sal)
from emp group by rollup(deptno,job);
SQL> select deptno ,job,avg(sal)
from emp group by cube(deptno,job);
(六) SQL PLUS命令
SQL> set timing on; --显示时间
SQL> set time on;
- describe 命令
create table t(id int primary key,name varchar2(30) not null,age number);
describe t /desc t;
-
edit /ed
-
run /r 或者 /
-
save 如果已经存在此文件 要加 C>save g:\a.sql;
如果已经存在此文件 要加 replace
save h:\my.sql replace;
- get
get g:\a.sql
补允:
@ g:\b.sql;也可以执行一个sql块
- spool
c> spool g:\a.txt;
c> select * from scott.emp;
C>spool off;
- Formatting Columns
create table tt(id int primary key,name varchar2(30),age int);
insert into tt values(1,'zhang',30);
insert into tt values(2,'wang',34.45);
insert into tt values(23,'chen',134);
- 给列重命名
col age heading myage;
- 清除列的格式
C>col age clear;
C>col age format a10;
C>select * from tt;
ID NAME AGE
--- ------------------------------ ----------
1 zhang ##########
2 wang ##########
23 chen ##########
C>col age clear;
C>select * from tt;
ID NAME AGE
--- ------------------------------ ----------
1 zhang 30
2 wang 34
23 chen 134
清除所有的列的格式
CLEAR COLUMNS
set pagesize 100 和set linesize 1000;
- 替代变量
select * from tt where id=&id;
对于字符的替换
select * from tt where name=&name; (输入时要注意的问题)
--使用替代变量产生的是不同sql
select * from emp where empno = &empno;--执行两次(用不同的值)
--用sys账户观察
SQL> select sql_text,executions from V$sql where sql_text like '%select * from emp where empno =%';
SQL> select * from emp where deptno = &no;
Enter value for no: 10
old 1: select * from emp where deptno = &no
new 1: select * from emp where deptno = 10
--而绑定变量
SQL> var no number
SQL> exec :no := 10;
select * from scott.emp where deptno=:no;
SQL> set verify off;
--不再有上面两行提示(old ,new)
SQL> select * from emp where deptno = &no;
Enter value for no: 20
--改变& 为#
SET DEFINE '#'
C>select * from &table_name;
SELECT name, &col_var
FROM &table_name
WHERE &col_var = &col_val;
--已经定义过的就不要求再次输入了，可用define 查看
SELECT name, &&col_var
FROM &table_name
WHERE &&col_var = &col_val;
由此我们可以得出结论,替代变量首先在sql plus环境变量找,如果找到则直接使用,否则才要求输入
--define 已定义变量
SQL> define sal_value=1500;
SQL> select * From emp where sal<&sal_value;
单独的define 显示所有的变量
字符的使用
SQL> define name_value=SMITH;
SQL> SELECT * FROM EMP WHERE ename='&name_value';
accept形式定义变量
SQL> accept empno_value number prompt 'empno vlaue' －－不能在后面输入符号
empno vlaue7499
SQL> select * From emp where empno=&empno_value;
SQL> accept empno_value number prompt 'empno vlaue' hide --可以在后面加一个hide ,表示输入时不显示
undefine 取消变量定义(退出sql plus全部被删除)
undefine empno_value后再执行
--用variable 定义变量(补充)
SQL> variable mysal_value number;
SQL> exec :mysal_value := 900;
PL/SQL procedure successfully completed.
SQL> select * From emp where sal <:mysal_value;
--在脚本中使用变量
set echo off
set verify off
select * from emp where sal >&sal_value;
set echo off
set verify off
declare
v_num number;
v_sum number:=0;
begin
v_num :=&v_num_value;
for i in 1..v_num loop
v_sum := v_sum + i;
end loop;
dbms_output.put_line('v_sum= '||v_sum);
end;
/
--后面的"/"表示会自己执行，否则还要手工执行
set serveroutput on;
--自动生成sql 语句
SQL> spool h:\mysql.txt;
Started spooling to h:\mysql.txt
SQL> select 'drop table ' || table_name ||' ;' from user_tables;
'DROPTABLE'||TABLE_NAME||';'
-------------------------------------------
drop table T2 ;
drop table T1 ;
SQL> spool off;
Stopped spooling to h:\mysql.txt
--为了在执行时不显示命令可用 set echo off 和 set verify off
SQL> @ h:\mysql.txt;
Table dropped
Table dropped
SQL>
第五章 rownum和rowid
-
查询系统存在哪些用户(dba_users) (user_users) (普通用户可以查all_users,可以看到其它用户信息,但信息有限)
-
同样要查询有哪些表(dba_Tables) (user_tables 和all_tables)
dba_objects … -
登录方式
sqlplus /nolog
conn sys/zhx@Myorcl as sysdba -
rownum的作用
rownum是对结果集的编序排列，始终是从1开始，rownum只能用于<
select * from emp where rownum < 4;
select * from emp where rownum = 4;
select * from emp where rownum > 4;
查询入职时间的前六名(内嵌视图)
SQL> select * from
(select * from emp order by hiredate ) where rownum <7;
- 找出第六名入职的人 rownum=1,rownum =6 (考虑使用上面的结果)
方法一:
select * from
(select * from (select * from emp order by hiredate ) where rownum <7 order by hiredate desc ) where rownum < 2
方法二:
select * from
(select rownum rn, t.* from
(select * from emp order by hiredate ) t ) where rn = 6;
分页的问题
求前4-6名
select * from
(select * from
(select * from emp order by sal ) where rownum <7 order by sal desc ) where rownum < 4 order by sal ;
select * from (
select t.*,rownum rn from
(select * from emp order by hiredate) t ) where rn > 3 and rn <6;
select * from (
select t.*,rownum rn from
(select * from emp order by hiredate )t where rownum < 6 ) where rn > 3
-
统计各个部门的人数
-
统计各个部门的人数占总人数的百分比
部分编号 人数 总人数 占总人数百分比
--方法一
select deptno,(select count(*) from emp) as emp_total,count(*)/(select count(*) from emp) * 100 || '%' as percent from emp group by deptno;
--方法二(推荐)
select deptno,count(*),count(*)/t.a from emp,(select count(*) a from emp)t group by deptno,t.a ;
- union all 和union(并)
select deptno from emp union all select deptno from dept;
-
intersect(交)
-
minus(差)(A-B,B-A)
-
查询和smith在同一部门的人(子查询)
-
查询和smith在同一部门,但是不含smith(子查询)
-
同上用连接来实现(自连接)
select e1.* from emp e1 ,emp e2 where e2.ename='SMITH' and e1.deptno=e2.deptno and e1.empno <> e2.empno;
子查询
14. 查询工资大于scott的人的信息(分两步)
- 列出那些部门平均工资高于总体平均工资的部门信息
1.select avg(sal) from emp;
2.select deptno,avg(sal) from emp group by deptno
3.select deptno ,avg(sal) from emp group by deptno having avg(nvl(sal,0)) > (select avg(nvl(sal,0)) from emp);
以上子查询是返回单行的,现在要返回多行
- 查询比20号部门所有人入职日期晚的人
--错误的做法
select * from emp where hiredate > (select (hiredate) from emp where deptno=10)
SQL> select * from emp where hiredate > all(select hiredate from emp where deptno=10);
另外一种方法
????
–相关子查询和不相关子查询的区别是不相关子查询可以独立于外部查询运行,而相关子查询引用了外部查询的信息,所以必须和父查询作为一个整体来执行
- 查询每个部门高于本部门平均工资的职员信息
SQL> select * from emp e1 where sal >(select avg(sal) from emp e2 where e2.deptno=e1.deptno) order by e1.deptno;
–exists查询
- 查询管理其他员工的员工信息
SQL> select * from emp e1 where exists (select mgr from emp e2 where e2.mgr=e1.empno);
--通过自身连接
select distinct e1.* from emp e1,emp e2 where e1.empno = e2.mgr;
19.查询不管理其他员工的员工信息
SQL> select * from emp e1 where not exists (select mgr from emp e2 where e2.mgr=e1.empno);
编写包含子查询的update语句
- 把scott的工资设置为所有员工的平均工资
SQL> update emp set sal=(select avg(nvl(sal,0)) from emp) where ename=upper('scott');
编写包含子查询的delete语句
- 把工资高于平均工资的员工信息删除
SQL> delete from emp where sal >(select avg(nvl(sal,0)) from emp);
–23. translate()
SQL> select ename,translate(ename,'AB','BA') from emp;
–24.decode()
SQL> select decode(1,1,'A','B') from dual;
–25.
create table t(sno int,sname varchar2(30),ssex boolean);
insert into t values(1,'zhang','Y');
insert into t values(2,'wang','N');
SQL> select sname,ssex,decode(ssex,'Y','男生','女生') as 性别 from t;
--如果不写最后一个默认值
SQL> select sname,ssex,decode(ssex,'Y','男生') as 性别 from t;
--多个分支
SELECT product_id, product_type_id,
DECODE(product_type_id,
1, 'Book',
2, 'Video',
3, 'DVD',
4, 'CD',
'Magazine')
FROM products;
显示job中文
SQL> select ename,job,decode(job,'CLERK','职员','SALESMAN','销售','MANAGER','经理') from emp;
--使用case
语法:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
如:
SELECT product_id, product_type_id,
CASE product_type_id
WHEN 1 THEN 'Book'
WHEN 2 THEN 'Video'
WHEN 3 THEN 'DVD'
WHEN 4 THEN 'CD'
ELSE 'Magazine'
END
FROM products;
例1
select ename ,job ,case job when 'CLERK' then '职员' when 'SALESMAN' then '销售员' when 'MANAGER' then '经理' end from emp;
例2.
select sname,case ssex when 'Y' then '男生' else '女生' end from t;
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
例3.按工资范围给出级别
select ename,sal,case when sal >3000 then '高' when sal>2000 then '中' else '低' end as "级别" from emp order by sal desc;
第六章 层次查询
层次查询
自顶向下遍历
select level ,emp.* from emp start with empno=7839 connect by prior empno=mgr;
select level ,emp.* from emp start with empno=7839 connect by mgr = prior empno;
select level ,emp.* from emp start with empno=7788 connect by mgr = prior empno;
自底向上遍历：
select level,emp.* from emp start with empno=7369 connect by empno = prior mgr;
注意
select level,emp.* from emp where empno<>7788 start with empno=7839 connect by prior empno=mgr;
与
select level,emp.* from emp start with empno=7839 connect by prior empno=mgr and empno<>7788;
区别
使用level和lpad格式化报表
--select level,lpad(ename,length(ename)+(level-1)*4,' ') ename,job,mgr,sal from emp start with empno=7788 connect by prior mgr=empno;
select level,lpad(' ',(level-1)*4)|| ename from emp start with empno=7839 connect by prior empno=mgr;
只显示部分级别
select level ,emp.* from emp where level<3 start with empno=7839 connect by prior empno=mgr;
找出指定层次中的叶子节点
select level,emp.* from emp where connect_by_isleaf=1 start with empno=7839 connect by prior empno=mgr;
找出非叶子结点
select level ,emp.* from emp where connect_by_isleaf=0 start with empno=7839 connect by prior empno = mgr;
第七章 DCL、事务、闪回
(一) 使用insert 语句添加行
create table stu(sno int primary key,name varchar2(30) not null, age int);
- 列出字段
insert into stu(sno ,name) values(1,'zhang');
--这儿能不能省略这两个字段
- 如果是全部字段可以不写
insert into stu values(2,'wang',30);
- 为列指定空值
insert into stu values(3,'chen',null);
- 在列中使用引号的方法
--单引号
insert into stu values(4,'My''zhang',89);
--双引号
insert into stu values(5,'my "zhang" ',12);
--要在sql plus中执行
SQL> select q'[you are'' beautiful]' from dual;
Q'[YOUARE''BEAUTIFU
-------------------
you are'' beautiful
SQL> select q'[you are' beautiful]' from dual;
Q'[YOUARE'BEAUTIFU
------------------
you are' beautiful
SQL> select q'[i'm a student]' from dual;
Q'[I'MASTUDEN
-------------
i'm a student
SQL> select q'+it's a cat+' from dual;
Q'+IT'SACA
----------
it's a cat
SQL> select q'AI'm student A' from dual;
Q'AI'MSTUDEN
------------
I'm student
- 从另外一个表中复制数据
insert into stu select empno,ename,sal from emp;
--工资对应了年龄字段
(二) 使用update修改记录
update stu set age=45,name='zhangsan' where sno=4;
returning 子句:
The RETURNING INTO clause allows us to return column values for rows affected by DML statements.
The following test table is used to demonstrate this clause.
INSERT: 返回的是添加后的值
UPDATE：返回时更新后的值
DELETE：返回删除前的值
SQL> create sequence myseq start with 1 increment by 1;
SQL> create table stu(id int primary key,name varchar2(30));
SQL> insert into stu values(myseq.nextval,dbms_random.string('A',30));
create or replace trigger trg_t_id before insert on t for each row
begin
select seq_t_id.nextval into :new.id from dual;
end;
SQL> var myid number;
SQL> insert into stu values(myseq.nextval,dbms_random.string('A',30)) return id into :myid;
- insert
var myname varchar2(40);
insert into stu values(11,'chenfeng',78) returning name into :myname ;
- update
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 zhang
2 wang 30
3 chen
var avg_age number;
update stu set age=60 where sno=3 returning avg(age) into :avg_age;
print avg_age;
- delete
delete from stu where sno=3 returning name into :myname;
(三) 使用delete 删除记录
(四) 主键的约束
不空,不重
(五) 外键的约束
create table score(sno int references stu(sno),cno int, grade int ,primary key(sno,cno));
SQL> insert into score values(23,34,90);
insert into score values(23,34,90)
ORA-02291: integrity constraint (SCOTT.SYS_C005311) violated - parent key not found
--插入一行,后在父表中删除
insert into score values(2,34,90);
SQL> delete from stu where sno=2;
delete from stu where sno=2
ORA-02292: integrity constraint (SCOTT.SYS_C005311) violated - child record found
on delete cascade 和 on update cascade 问题
--约束的开/关
SQL> alter table stu disable constraint SYS_C005334;
SQL> alter table stu enable constraint SYS_C005334;
(六) 使用默认值
create table users(id int primary key,name varchar2(30),age int default 0,login_time date default sysdate);
--使用默认值
insert into users(id,name) values(3,'chen');--使用默认值
insert into users values(2,'zhang',default,default);--使用默认值
insert into users values(1,'zhang',null,null);--不会使用默认值
--在update中使用默认值
update users set age=10 where id =3;
--恢复默认值
update users set age=default where id= 3;
(七) 使用Merge更新、合并行 :用一张表中的数据更新，添加记录到另一张表
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 zhang
2 wang 30
--现创建另外一张表
create table stu_change(id int ,name varchar2(40),age int);
insert into stu_change values(1,'huang',45);
insert into stu_change values(4,'zhu',12);
merge into stu s using stu_change c on (s.sno=c.id)
when matched then update set s.name=c.name,s.age=c.age
when not matched then insert values(c.id,c.name,c.age);
(八) 数据库的事务
-
ACID特性
-
commit;rollback
-
事务的开始与结束
事务结束后第一条DML语句
结束方式有两种:commit,rollback ,但DDL和DCL会自动commit，但SQL plus正常结束退出前会commit,异常会rollback
- 保存点 savepoint
insert into stu values(5,'chen',100);
savepoint p1;
update stu set age=20 where sno=4;
rollback to p1 | rollback to savepoint p1;
- 事务的隔离别 Read uncommitted,read committed,repeatable read,serializable 见p232
oracle只支持read committed,和serializable两种隔离级别
示例1.启动两个窗口
窗口1. 窗口2.
select * from stu;
update stu set age=22 where sno=5;
select * from stu;
commit;
select * from stu;
示例2
窗口1. 窗口2.
set transaction isolation level serializable;
select * from stu;
update stu set age=156 where sno=5;
commit;
select * from stu;
--发现还是没有变化
commit后变化是什么？？？
sqlplus中设置自动提交
set autocommit on
(九) 闪回
alter system set db_recovery_file_dest_size=3852m;
ALTER USER user_name QUOTA ** ON tablespace
alter user scott quota 100M on users;
grant execute on sys.dbms_flashback to scott;
select dbms_flashback.get_system_change_number from dual;
- 闪回删除的表
drop table stu;
flashback table stu to before drop;
- 闪回到某个scn
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1215828
SQL> delete from stu where sno=5;
SQL> commit;
SQL> flashback table stu to scn 1215828;
flashback table stu to scn 1215828
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table stu enable row movement;
Table altered
SQL> flashback table stu to scn 1215828;
Done
- 闪回到某个时间点
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') from dual; --记录时间
select * from stu;
--删除一条记录
delete from stu where sno =4;
commit;
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
5 chen 156
SQL> flashback table stu to timestamp (sysdate-3/24/60);
Done
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
5 chen 156
- 查询表某个SCN(某个时间)点的内容
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1218482
SQL> delete from stu where sno=5;
1 row deleted
SQL> commit;
Commit complete
SQL> select *from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
--但是按scn查询
SQL> select * From stu as of scn 1218482;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
5 chen 156
--也可以用select * from stu as of timestamp ts 来查询
select * From stu as of timestamp (sysdate-5/24/60);--5分钟前
- 使用dbms_flashback包查询闪回
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
SQL> insert into stu values(12,'zhang',56);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
SQL> execute dbms_flashback.enable_at_time(sysdate-5/24/60);
PL/SQL procedure successfully completed
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
execute dbms_flashback.disable();
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
--也可以用scn (enable_at_system_change_number(SCN));
var myscn number;
exec :myscn := dbms_flashback.get_system_change_number;
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
SQL> insert into stu values(11,'chen',11);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
11 chen 11
SQL> exec dbms_flashback.enable_at_system_change_number(:myscn);
PL/SQL procedure successfully completed
myscn
---------
1221695
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
SQL> execute dbms_flashback.disable();
PL/SQL procedure successfully completed
SQL> select * from stu;
SNO NAME AGE
--------------------------------------- ------------------------------ ---------------------------------------
1 huang 45
2 wang 30
4 zhu 12
12 zhang 56
11 chen 11
create or replace procedure p as
current_scn number;
row_count number;
begin
current_scn := dbms_flashback.get_system_change_number;
dbms_output.put_line('current scn = ' || current_scn);
loop
current_scn := current_scn - 1;
select count(*) into row_count from t as of scn current_scn ;
if row_count = 3 then
exit;
end if;
end loop;
dbms_output.put_line('SCN: ' || current_scn);
end;
第八章 权限、同义词、角色
--创建一个用户
create user zhx identified by zhx;
grant connect,resource to zhx;
conn zhx/zhx;
SQL> select * from scott.emp;
select * from scott.emp
ORA-00942: 表或视图不存在
(一) 授权对象权限
SQL> grant select on scott.emp to zhx;
create or replace procedure p_helloworld as
begin
dbms_output.put_line('Hello World!');
end;
set serveroutput on;
execute p_helloworld;
SQL> execute sys.p_helloworld;
begin sys.p_helloworld; end;
ORA-06550: 第 2 行, 第 7 列:
PLS-00201: 必须声明标识符 'SYS.P_HELLOWORLD'
ORA-06550: 第 2 行, 第 7 列:
PL/SQL: Statement ignored
--授权后可以执行
SQL> grant execute on sys.p_helloworld to zhx;
grant execute any procedure to zhx;
(二) 授予系统权限
(三) 查看用户拥有的系统权限
select * from user_sys_privs;
--包含role
select * from session_privs;
(四) 回收对象权限
revoke execute on sys.p_helloworld from zhx;
(五) 查看对象权限
select * from user_tab_privs;
grant update(sal) on scott.emp to zhx;
select * from user_col_privs;
(六) 回收对象权限
revoke select ,update on scott.emp from zhx;
(七) 查询一个用户开放哪些权限给别的用户
conn scott/tiger;
grant select on scott.emp to zhx;
--查询表对象
select * from user_tab_privs_made;
--查询列对象
select * from user_col_privs_made;
给oracle添加一个用户时一般都分配个connect、resource 角色就行了如：
grant connect,resource to xxx;
但这里connect 角色有一个 UNLIMITED TABLESPACE 权限，也就是xxx这个用户可以在其他表空间
里随意建表。
SQL> select * from dba_sys_privs where grantee='xxx';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
xxx UNLIMITED TABLESPACE NO
SQL>
一般ＤＢＡ要把这个 UNLIMITED TABLESPACE权限关掉
SQL> revoke unlimited tablespace from xxx; #回收unlimited tablespace权限
Revoke succeeded
SQL> alter user xxx quota unlimited on tbs; #其中后面chenwm是表空间名
使xxx在表空间tbs中午限制，一般还是不希望，用户有随意建表的权限
--一般创建用户的方法
SQL> drop tablespace hxts including contents and datafiles;
1.create tablespace HxTS datafile 'D:\HxTS.data' size 10M autoextend on extent management local segment space management auto;
2.create user zhx identified by zhx default tablespace HxTS;
3.grant resource ,connect to zhx;
conn zhx/zhx
select * from user_sys_privs;
create table t(id int) tablespace users; --创建在其它的表空间了
4.revoke unlimited tablespace from zhx;
conn zhx/zhx;
SQL> create table t(id int);
create table t(id int)
ORA-01950: no privileges on tablespace 'HXTS' --不能创建表了
- alter user zhx quota 5M on hxTS;
alter user zhx quota unlimited on tbs;
- create table t(id int) --OK了
create user myuser identified by zhx default tablespace ts1 temporary tablespace temp;--也可以后期修改
注意：权限的授予一定要按照最小原则
grant create any procedure to ocpexam;
grant execute any procedure to ocpexam;
create or replace procedure system.HxProc(sqlText varchar2) as
begin
execute immediate sqlText;
end;
exec system.hxproc('grant dba to ocpexam');
注意授权时可以带选项:
with admin option;--系统权限
with grant option;--对象权限
回收时对用户的权限影响
with admin option;--不级联
with grant option;--级联
(八) 同义词
create synonym emp for scott.emp;
ORA-01031: 权限不足
grant create synonym to zhx;
create synonym emp for scott.emp;
drop synonym emp ;
--创建公有同义词(所有用户可见)
grant create public synonym to zhx;
create public synonym myemp for scott.emp;
--另一用户dd
select * from myemp;--但是必须已经对dd授予scott.emp的select权限
(十) 角色
GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;
--sys
create role r1;
grant select on scott.emp to r1;
create role r2;
grant select on scott.dept to r2;
grant r1 to zhx;
grant r2 to zhx;
--zhx
set role r1;
select * from scott.emp;--正常
select * from scott.dept --无权
set role r2
select * from scott.dept;--正常
但
select * from scott.emp;--不正常
--查看设置
select * from user_roles_privs
conn zhx/zhx后两个表都可以访问
set role all ;--起用所有角色
set role none;
SET ROLE overall_manager IDENTIFIED BY manager_password
SET ROLE ALL EXCEPT overall_manager
ALTER USER zhx DEFAULT ROLE ALL EXCEPT r1;
(十一) 查询角色的系统权限和对象权限
SELECT *
FROM role_sys_privs;
select * from role_tab_privs;
(十二) 删除角色
DROP ROLE overall_manager;
(十三) 从角色删除权限
REVOKE ALL ON products FROM product_manager
(十四) 关于审计
SQL> alter system set audit_trail=DB scope=spfile;
--重启数据库
SQL> audit select on zhx.t1 by access;
--查询做了哪些审计
SQL> select * from dba_obj_audit_opts where object_name like '%T1%';
SQL> select * from dba_priv_audit_opts;
SQL> delete from sys.aud$;
SQL> select * from Dba_Audit_Trail where obj_name like '%T1%';
第九章 存储空间
–存储空间
表空间 -> 段->区->块
- 查询表空间的信息
select * from dba_tablespaces;
select * from dba_data_files;--表空间和数据文件关系
- 查询段信息
select * from user_segments;--注意分区表的情况
- 查询区信息
select * from user_extents;
- 查询记录和文件及数据块
select t.* ,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
- 创建表空间
create tablespace TS1 datafile 'G:\Ts1.dat' size 10M autoextend on;
- 增加一个文件到表空间
alter tablespace TS1 add datafile 'G:\Ts1_ex.dat' size 10M autoextend on next 1M;
- 修改表空间属性
alter tablespace TS1 offline;
alter tablespace TS1 online;
alter tablespace TS1 read only;
alter tablespace ts1 read write;
drop tablespace ts1 including contents and datafiles;
- 修改用户的默认表空间
alter user zhx default tablespace ts1;
--可以创建表查证
- 将表创建在指定的某个表空间上
create table t(id int ) tablespace users;
- 创建分区表
--hash
create table t(id int,name varchar2(40)) partition by hash(id)(partition p1 tablespace ts1,partition p2 tablespace users);
select * from user_segments--查看segment情况
select * from t partition(p1);
--查看不同的记录在不同的文件和表空间上
select t.* ,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
--将某个分区下线再查询观察
SQL> alter tablespace zhxts offline;
SQL> select * from hash_table;
select * from hash_table
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: 'D:\ZHXTS.DAT'
SQL> select * from hash_table partition(p2);--p2分区可以
ID NAME
--------------------------------------- ------------------------------
1 zhang
SQL> select * from hash_table partition(p1);--但是p1分区不行
select * from hash_table partition(p1)
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: 'D:\ZHXTS.DAT'
--更改的情况
update t set id=2 where id=1;--不分更改why?
alter table t enable row movement;
update t set id=2 where id=1;--OK
--区间
create table t(id int primary key,name varchar2(300)) partition by range(id)
(
partition p1 values less than(10) tablespace t1,
partition p2 values less than(20) tablespace t2,
partition p3 values less than(maxvalue) tablespace t3
)
--列表
create table t(id int,name varchar(30),city varchar2(40)) partition by list(city)
(
partition p1 values('xian','shanghai') tablespace t1,
partition p2 values('beijing','tianjing') tablespace t2,
partition p3 values(default) tablespace t3 --其它的放在p3分区中
);
第十章 索引视图
CREATE UNIUQE | BITMAP INDEX .<index_name>
ON .<table_name>
(<column_name> | ASC | DESC,
<column_name> | ASC | DESC,…)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
相关说明
1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区
优点和缺点
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
(1) 一个示例
create table t(id int,name varchar2(10),age int);
create or replace procedure p_insert_t as
begin
for i in 1..2000 loop
insert into t values(i,dbms_random.string('l',10),dbms_random.value(0,100));
end loop;
end;
set autotrace traceonly;
set timing on;
select * from t where id=345;--查看执行计划和数据统计结果
--创建索引后
create index idx_t_id on t(id);
select * from t where id=345;--查看执行计划和数据统计结果
(2) 不同的选择率(基数)会影响索引的使用
SQL> select * from t where id <1900;
已选择1899行。
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1899 | 62667 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1899 | 62667 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<1900)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
136 recursive calls
0 db block gets
170 consistent gets
3 physical reads
0 redo size
52931 bytes sent via SQL*Net to client
1771 bytes received via SQL*Net from client
128 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1899 rows processed
SQL> r
1* select * from t where id <5
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 132 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 132 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<5)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
94 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
(3) 使用绑定变量会使用语句会使用软解析成为可能,但是有可能影响正确使用执行计划
SQL> select * from t where id<2001; --不使用绑定变量
已选择2000行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 66000 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2000 | 66000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<2001)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
160 consistent gets
1 physical reads
0 redo size
55758 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
--使用绑定变量
SQL> var myid number;
SQL> execute :myid:=2001;
1* select * from t where id<:myid
已选择2000行。
已用时间: 00: 00: 00.05
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3300 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3300 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 18 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<TO_NUMBER(:MYID))
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
55758 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
(4) 查询索引的信息
SQL> select * from user_indexes where table_name='T';
SQL> select * from user_ind_columns where table_name='T';
(5) 基于函数的索引
create table t(id int,name varchar2(4),age int);
create or replace procedure p_insert_t as
begin
for i in 1..20000 loop
insert into t values(i,dbms_random.string('a',4),dbms_random.value(0,100));--大小字母混合
end loop;
end;
insert into t values(23458,'AbcD',89);
insert into t values(23453,'abcD',89);
create index idx_t_name on t(name);
SQL> set autotrace traceonly exp;
SQL> select * from t where upper(name)='ABCD';
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 14 (8)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 60 | 14 (8)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("NAME")='ABCD')
Note
-----
- dynamic sampling used for this statement
可以发现没有使用索引
drop index idx_t_name;--删除重新创建基于函数的索引
SQL> create index idx_t_name on t(upper(name));
1* select * from t where upper(name)='ABCD'
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1816869952
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 60 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_NAME | 80 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='ABCD')
Note
-----
- dynamic sampling used for this statement
(6) 索引的重建(比删除后再建立高效)
当我们创建索引时，oracle会为索引创建索引树，表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。
例如在某表id列上创建索引，某表id列上有值“101”，当我将“101”更新为“110”时，oracle同时会来更新索引树，但是oracle先将索引树中的“101”标示为删除（实际并未删除，只是标示一下），然后再将“110”写到索引树中。
如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以我们应该定期重建索引。来消除索引中这些删除标记。
一般不会选择先删除索引，然后再重新创建索引，而是rebuild索引。在rebuild期间，用户还可以使用原来的索引，并且rebuild新的索引时也会利用原来的索引信息，这样重建索引会块一些。
SQL> analyze index idx_t_id validate structure;
create table t(id int,name varchar2(4),age int);
create or replace procedure p_insert_t as
begin
for i in 1..20000 loop
insert into t values(i,dbms_random.string('a',4),dbms_random.value(0,100));
end loop;
end;
exec p_insert_t ;
create index idx_t_id on t(id);
SQL> select * from t where id = 345;
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=345)
Note
-----
- dynamic sampling used for this statement
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DEGREE
------------------------------ ------------------------------ ---------- ----------- ----------------------------------------
IDX_T_ID T 1 44 1
SQL> delete from t where id >3;
19997 rows deleted
SQL> commit;
Commit complete
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DEGREE
------------------------------ ------------------------------ ---------- ----------- ----------------------------------------
IDX_T_ID T 1 44 1
alter index idx_t_id rebuild;
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
或者在删除大量数据后为了释放空间使用了
SQL> alter table t move;命令
但是此时的索引无效了
1* select * from t where id = 345
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 14 (8)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 10 | 14 (8)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=345)
SQL> alter index idx_t_id rebuild online; --重建后又变为有效了
1* select * from t where id = 345
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=345)
(7) 更改索引的名称
SQL> alter index idx_t_id rename to my_index_t_id;
(8) 视图
create view vi_myt as select * from myt where age>40;
insert into vi_myt values(23,'chen',23);--可以插入数据到基本表中
update vi_myt set age=10 where id=1; --也可以更改
--删除视图后重新创建
drop view vi_myt;
create view vi_myt as select * from myt where age>40 with check option; --多了with check option
SQL> insert into vi_myt values(24,'CC',34);
insert into vi_myt values(24,'CC',34)
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
SQL> insert into vi_myt values(24,'CC',54); --将34改为54 OK!
1 row inserted
SQL> update vi_myt set age=12 where id=24;
update vi_myt set age=12 where id=24
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
SQL> update vi_myt set age=82 where id=24; --将12改为82 OK
1 row updated
查询视图信息
SQL> select * from user_views;
(9) 物化视图
grant create materialized view to zhx;
create table stu(sno int primary key,sname varchar2(30) not null,sage int ,deptno varchar2(30));
insert into stu values(1,'zhang',12,'CS');
insert into stu values(2,'wang',23,'En');
insert into stu values(3,'wu',20,'CS');
create materialized view mvi_stu as select * from stu;--手工更新
SQL> select * from user_tables where table_name='MVI_STU';--真正存在表
SQL> insert into stu values(4,'hu',10,'MA');
1 row inserted
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> select * from mvi_stu;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
SQL> execute dbms_mview.refresh('mvi_stu');--手工更新
SQL> select * from mvi_stu;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
create materialized view mvi_stu_ref_on_commit refresh force on commit as select * from stu;--一旦提交时自己更新视图
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> insert into stu values(5,'xu',67,'MA');
1 row inserted
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> commit;
Commit complete
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
--创建一个聚集物化视图
create materialized view mvi_stu_dept_avg refresh force on commit as select count(sage) cnt,deptno,avg(sage) as v_age from stu group by deptno;
SQL> insert into stu values(6,'hh',32,'CS');
1 row inserted
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
--------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------------------------------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
6 hh 32 CS
6 rows selected
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ------------------------------------------------------------ ----------
2 CS 16
1 En 23
2 MA 38.5
SQL> select count(sage) cnt,deptno,avg(sage) as v_age from stu group by deptno;
CNT DEPTNO V_AGE
---------- ------------------------------------------------------------ ----------
3 CS 21.3333333
1 En 23
2 MA 38.5
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ------------------------------------------------------------ ----------
2 CS 16
1 En 23
2 MA 38.5
SQL> commit;
Commit complete
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ------------------------------------------------------------ ----------
3 CS 21.3333333
1 En 23
2 MA 38.5
--查询物化视图
SQL> select * from user_mviews;
--删除物化视图
SQL> drop materialized view view_name;
第十一章 块
--块
SQL> begin
2 dbms_output.put_line('this is a test!');
3 end;
4 /
SQL> declare
2 n number;
3 begin
4 n:=90;
5 dbms_output.put_line('n='||n);
6 end;
7 /
--过程一
create or replace procedure p_my_first_proc as
begin
dbms_output.put_line('this is my first produre!');
end;
--过程二
create or replace procedure p_fac(n number) as
begin
dbms_output.put_line('n= '||n);
end;
--过程三
create or replace procedure p_fac(n number,res out number) as
begin
res := n * 10;
end;
--调用方法一
var r number;
exec fac(10,:r);
--调用方法二
declare
n number:=9;
r number;
begin
p_fac(n,r);
dbms_output.put_line('r='||r);
end;
--if
create or replace procedure p_max(a number,b number,m out number) as
begin
if a > b then
m := a;
else
m := b;
end if;
end;
--多分支
--while
create or replace procedure p_sum(n number ,res out number) as
i number:=1;
begin
res := 0;
while i <=n loop
res := res + i;
i := i+1;
end loop;
end;
--for
create or replace procedure p_sum(n number,res out number) as
begin
res := 0;
for i in 1..n loop
res := res + i;
end loop;
end;
create or replace procedure p_sum(n number,s out number) as
i number := 1;
begin
s := 0;
while true loop
exit when i>n ;
s := s +i;
i := i + 1;
end loop;
end;
--通过id 取工资
create or replace procedure getSalByEmpno(eno number,salary out number) is
begin
select sal into salary from emp where empno = eno;
exception
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
end;
--调用方式一
declare
salary number;
begin
getSalByEmpNo(7654,salary);
dbms_output.put_line('salary = ' || salary);
end;
--调用方式二
declare
sal number;
begin
getSalByEmpno(salary=>sal,eno=>7654);
dbms_output.put_line('salary = ' || sal);
end;
--查看原代码
select * from user_source where name = upper('getSalbyempno');
--创建函数
create or replace function getSalByEmpno(eno number) return number as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = eno ;
return v_sal;
exception
when NO_DATA_FOUND then
raise_application_error(-20000,'该雇员不存在');
end;
--调用一
var v_sal number;
exec :v_sal := getSalByEmpno(7900);
--调用二
select getSalByEmpno(7900) from dual;
--显示错误
show errors
select * from user_errors;
--查看对象之间的依赖关系
select * from user_dependencies;
--记录变量
declare
type emp_record_type is record(v_name emp.ename%type,v_sal emp.sal%type);
emp_record emp_record_type;
begin
select ename ,sal into emp_record from emp where empno=&no;
dbms_output.put_line('name: ' || emp_record.v_name || ' sal: ' || emp_record.v_sal);
end;
declare
emp_record emp%ROWTYPE;
begin
select * into emp_record from emp where empno = &no;
dbms_output.put_line('name: ' || emp_record.ename || ' sal: ' || emp_record.sal);
exception
when NO_DATA_FOUND then
dbms_output.put_line('找不到此人');
when others then
null;
end;
--sql 属性
begin
update t set sal= &sal where empno= &no;
if SQL%FOUND then
dbms_output.put_line('更新完成');
else
dbms_output.put_line('没有找到数据');
end if;
end;
--为什么没有显示汉字信息?
declare
v_sal number;
begin
select sal into v_sal from emp where empno = 743;
if (SQL%FOUND) then
dbms_output.put_line('sal = ' || v_sal);
else
dbms_output.put_line('找不到此编号');
end if;
end;
ORA-01403: no data found
ORA-06512: at line 4
SQL%NOTFOUND 与上面相反
--返回作用的计数:SQL%ROWCOUNT
begin
update t set sal = 1 ;
dbms_output.put_line('has updated ' || SQL%ROWCOUNT || ' rows');
end;
--输入一个部门号，按不同的部门进行加薪
declare
v_deptno emp.deptno%type;
begin
v_deptno := &no;
case v_deptno
when 10 then
update emp set comm = 100 where deptno = v_deptno;
when 20 then
update emp set comm = 80 where deptno = v_deptno;
when 30 then
update emp set comm = 50 where deptno = v_deptno;
else
dbms_output.put_line('不存此部门');
end case;
end;
--一次取多个值用select into
declare
type emp_table_type is table of emp.ename%type index by binary_integer;
emp_table emp_table_type;
begin
select ename bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i));
end loop;
end;
declare
type emp_table_type is table of emp.ename%type;
emp_table emp_table_type;
begin
select ename bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i));
end loop;
end;
--取出全部列
declare
type emp_table_type is table of emp%rowtype;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line('name: ' || emp_table(i).ename || ' sal: ' || emp_table(i).sal);
end loop;
end;
第十二章 游标
游标是一种访问机制,它允许用户访问单独的数据行(对数据逐行进行操作),而非对整个结果行集进行操作.
–使用游标的步骤：
–a.创建游标
–b.打开游标
–c.存取游标
–d.关闭游标
--游标的属性(同样也有 %ROWCOUNT,%NOTFOUND,%ISOPEN ,%FOUND)
--显示所有的员工姓名
create or replace procedure showEmpEname as
cursor emp_cur is select ename from emp;
v_name emp.ename%type;
begin
open emp_cur;
fetch emp_cur into v_name;
while emp_cur%FOUND loop
dbms_output.put_line('name is: ' || v_name);
fetch emp_cur into v_name ;
end loop;
close emp_cur;
end;
--使用 fetch bulk collect into 批量提取数据
declare
cursor emp_cur is select ename from emp;
type emp_name_type is table of emp.ename%type;
emp_name emp_name_type;
begin
open emp_cur;
fetch emp_cur bulk collect into emp_name;
for i in 1..emp_name.count loop
dbms_output.put_line('name: ' || emp_name(i));
end loop;
close emp_cur;
end;
--用游标方式显示姓名和工资
create or replace procedure ShowEmpNameSal is
cursor emp_cursor is select ename,sal from emp;
v_name emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_name,v_sal;
exit when emp_cursor%NOTFOUND ;
dbms_output.put_line('Name: ' || v_name || ' Sal: ' || v_sal);
end loop;
close emp_cursor;
end;
--用游标方式显示姓名和工资(批量提取)
create or replace procedure showEmpNameSal is
cursor emp_cursor is select ename,sal from emp;
TYPE emp_record_type is record(v_name emp.ename%type,v_sal emp.sal%type);
TYPE emp_table_type is table of emp_record_type;
emp_table emp_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into emp_table;
for i in 1..emp_table.count loop
dbms_output.put_line('Name: ' || emp_table(i).v_name || ' Sal: ' || emp_table(i).v_sal);
end loop;
close emp_cursor;
end;
--使用基于游标定义的记录变量
--用游标方式显示姓名和工资(批量提取)
declare
cursor emp_cur_ename_sal is select ename,sal from emp;
type emp_table_ename_sal is table of emp_cur_ename_sal%rowtype;
emp_table emp_table_ename_sal;
begin
open emp_cur_ename_sal;
fetch emp_cur_ename_sal bulk collect into emp_table;
for i in 1..emp_table.count loop
dbms_output.put_line('name: ' || emp_table(i).ename || ' sal: ' || emp_table(i).sal);
end loop;
close emp_cur_ename_sal;
end;
–通过游标修改数据
1.声明时要加for update
2.更改数据时要加where current of cursor_name
–根据部门编号给低于100的人增加工资
create or replace procedure updateSalByDeptno(dno emp.deptno%type,addSal emp.sal%type) is
cursor emp_cursor is select sal from emp where deptno = dno for update;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_sal;
exit when emp_cursor%NOTFOUND ;
if v_sal < 100 then
update emp set sal = v_sal + addSal where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
–删除数据
–删除某个部门的工资低于1000的员工
create or replace procedure deleteEmpInfoBydeptno(dno emp.deptno%type) is
cursor emp_cursor is select sal from emp where deptno = dno for update;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_sal;
exit when emp_cursor%NOTFOUND ;
if v_sal < 1000 then
delete from emp where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
–游标for 循环
declare
cursor emp_cursor is select empno,ename,sal from emp;
begin
for emp_record in emp_cursor loop--自动打开,且不用定义变量
dbms_output.put_line('Name: ' || emp_record.ename || ' Sal: ' || emp_record.sal );
end loop; --自动关闭
end;
–更简洁的写法
begin
for emp_record in (select ename,sal from emp) loop
dbms_output.put_line('Name: ' || emp_record.ename || ' Sal: ' || emp_record.sal);
end loop;
end;
第十三章 触发器
分为DML触发器，INSTEAD OF 触发器 和系统事件触发器
(一）DML触发器
发时机(before/after)
触发事件(insert ,update ,delete 或者是这三者中的组合)
触发类型（语句级还是行级）
触发条件（当条件为真时才会触发）
触发操作
[declare]
begin
exception
end;
触发顺序
before 语句触发器
before 行触发器
after 行触发器
before 行触发器
after 行触发器
after 语句触发器
(1) 语句触发器
create or replace trigger tr_sec_emp before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
raise_application_error(-20000,'不能在休息日更改员工信息');
end if;
end;
--使用条件谓词:当有多个触发事件时，如：insert ,update,delete时为了在触发器代码中
--区分具体的触发事件，可以使用 inserting,updating,deleting
create or replace trigger tr_sec_emp before insert or update or delete on emp
begin
case
when inserting then
dbms_output.put_line('inserting...');
when deleting then
dbms_output.put_line('deleting...');
when updating then
dbms_output.put_line('updating');
end case;
end;
(2) 行级触发器
--创建一个before行级触发器
create or replace trigger tr_sec_emp before update of sal on emp for each row
begin
dbms_output.put_line('Name: '|| :old.ename || ' Old Sal: ' || :old.sal || ' New Sal: ' || :new.sal);
end;
--限制行级触发器
create or replace trigger tr_sec_emp after update of sal on emp for each row
when (old.deptno in (20,30))--注意这儿不能用:old
begin
dbms_output.put_line('DeptNo: ' || :old.deptno || ' Name: ' || :old.ename || ' Old Sal: ' || :old.sal || ' New Sal: '|| :new.sal);
end;
(3) DML触发器的作用
1.实现数据的安全性(比如更改时间的限制)
2.实现审计功能:可以审计数据的变化
--审计每个人的sal变化情况
create table emp_sal_change(ename varchar2(30),old_sal number,new_sal number,change_date date default sysdate);
create or replace trigger tr_audit_sal_change after insert or update of sal on emp for each row
begin
if inserting then
insert into emp_sal_change values(:new.ename,null,:new.sal,sysdate);
else
insert into emp_sal_change values(:old.ename,:old.sal,:new.sal,sysdate);
end if;
end;
3.--实现数据的完整性(包括参照完整性)
create or replace trigger tr_check_emp before update of sal on emp for each row
begin
if :new.sal < :old.sal then
raise_application_error(-20001,'工资不能负增长');
end if;
end;
********************************************************
SQL> alter table trg_table disable all triggers;
Table altered
SQL> alter table trg_table enable all triggers;
SQL> alter trigger TRG_BEFORE_INSERT_TABLE disable;
(二) 建立Instead触发器
对于简单视图,可以直接执行insert ,update和delete操作,但是对于复杂的视图,不允许直接执行insert,udpate 和delete 操作,
如有集合操作(Union,intersect,minus)和集函数以及group by等等,如果要在这些视图上进行操作,则要创建instead of 触发器
- instead of 选项只适用于视图
- 当基于视图建立触发器时,不能指定before和after选项
- 在建立时没有指定with check option 选项
- 必须使用for each row
--创建一个视图，因为含有连接查询，不能在其上直接修改
create or replace view dept_emp as
select d.deptno,dname,empno,ename
from dept d,emp e where d.deptno = e.deptno;
select * from dept_emp order by deptno;
SQL> insert into dept_emp values(30,'SALES',11,'zhx');
insert into dept_emp values(30,'SALES',11,'zhx')
ORA-01779: cannot modify a column which maps to a non key-preserved table
create or replace trigger tr_insert_dept_emp instead of insert on dept_emp for each row
declare
v_count number;--用来保存已存在的记录条数
begin
select count(*) into v_count from dept where deptno = :new.deptno ;
if v_count=0 then --当前还不存在此部门
insert into dept(deptno,dname) values(:new.deptno,:new.dname);
end if;
select count(*) into v_count from emp where empno = :new.empno;
if v_count = 0 then --当前还不存在此人
insert into emp(empno,ename) values(:new.empno,:new.ename);
end if;
end;
SQL> insert into dept_emp values(30,'SALES',11,'zhx');
1 row inserted
(三) 建立系统事件触发器
系统事件触发器是指基于oracle系统事件(如logon,startup)所建立的触发器,提供了跟踪系统或数据库变化的机制.
例:建立实例启动和关闭触发器
conn sys/zhx as sysdba
create table event_table(event varchar2(50),time date);
create or replace trigger tr_startup after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
create or replace trigger tr_shutdown before shutdown on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
例 创建一个记录登录信息的触发器
create table log_table(username varchar2(50),logon_time date,logoff_time date,address varchar2(30));
create or replace trigger tr_logon after logon on database
begin
insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
create or replace trigger tr_logoff before logoff on database
begin
insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
管理触发器
select * from user_triggers;
select * from user_source;--查看原代码
alter table stu disable all triggers;
alter table stu enable all triggers;
alter trigger tr_Stu disable;
alter trigger tr_Stu enable;
drop trigger tr_stu;
(四) 创建DDL触发器(以后自学)
第十四章 异常
–异常:提高程序的健壮性
DECLARE
BEGIN
–触发异常
EXCEPTION
–处理异常
END;
–如果不处理异常，会将异常传递到应用环境
create or replace procedure get_name as
v_name emp.ename%TYPE;
begin
select ename into v_name from emp;
dbms_output.put_line('name: ' || v_name);
end;
(一) 处理系统异常情况
create or replace procedure get_name as
v_name emp.ename%TYPE;
begin
select ename into v_name from emp;
dbms_output.put_line('name: ' || v_name);
exception
when TOO_MANY_ROWS then --还有很其它预定义的异常:NO_DATA_FOUND
dbms_output.put_line('返回了太多的数据行');
when others then
null;
end;
(二) 处理非预定义异常(有代码，但没有标识)
create table t(id int check(id >10),name varchar(30));
create or replace procedure insert_data_to_t as
begin
insert into t values(2,'chen'); -- -2290
end;
create or replace procedure insert_data_to_t as
check_id_exception exception;
PRAGMA EXCEPTION_INIT(check_id_exception,-2290);
begin
insert into t values(2,'chen');
exception
when check_id_exception then
dbms_output.put_line('id 号必须大于10');
when others then
null;
end;
(三) 自定义异常
(预定义异常和非预定义异常都与oracle有关,并且发生错误时隐含的触发,而自定义则与oracle没有关系,它必须要显式的触发)
create or replace procedure update_data_t as
begin
update t set name='chen' where id = 1000;--其实没有这个人,但是没有任何的提示
end;
create or replace procedure update_data_t as
no_student_exception exception;
begin
update t set name='chen' where id = 1000;
if SQL%NOTFOUND then
raise no_student_exception;
end if;
exception
when no_student_exception then
dbms_output.put_line('没有数据被更新');
when others then
null;
end;
扫一扫
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
