oracle学习总结
来源:互联网 发布:免费linux云主机 编辑:程序博客网 时间:2024/06/11 07:04
普通用户:scott/tiger
数据库管理员:system/manager
OS管理员:sys/change_on_install
\
基本命令:
开始-运行-sqlplusw或sqlplus可以打开sql plus工具
显示用户:show user
切换用户:conn 用户名/密码@orcl
查找所有表:select * from tab where tabtype='TABLE';//单引号,'TABLE':oracle区分大小写,这里只能大写
user 和date 都是系统的关键字,建表的时候不可以使用
数据类型:
数值型:number
number(5)
number(5,2)//总共五位,有两位小数
字符型:char 2000个字节 定长
varchar2 4000个字节 不定长
日期型:date 7个字节
大文本类型:CLOB 4G
大对象类型:二进制,BLOB
create table student(
sid number(10) primary key,
sname varchar2(50),
sage number(5)
);
1、DDL 数据定义语言:create,drop,alter,truncate
alter table emp add a number(32);
alter table emp drop column a;
2、DML 数据操作语言:insert,delete,update,select
3、DCL 数据控制语言:grant,revoke
4、TCL 事务控制语言:commit,rollback,savepoint
DB2 --- IBM
sysbase
sqlserver
oracle
mysql:php+mysql
*************************************************************************************************************************************************
函数
一、单行函数
1、字符函数
(1) || concat() 字符拼接
(2) initcap() 将首字母改为大写,其他的改为小写
(3) upper(),lower() 将所有字母改为小写、大写
(4)lpad(),rpad() 左右填充
select lpad(job,10,'+') from emp;//注意要用单引号
各参数意义:(字段,填充完后长度,不足部分的填充物)
(5)ltrim(),rtrim() 去除左右空格
(6)substr(ename,1,3) 截取字符串
(7) length(ename) 求长度
2、日期,时间函数
(1)sysdate 查看系统日期
1.select sysdate from emp;结果会有很多个(与表的行数相同)
解决办法:使用dual,dual是一张虚拟表,只有一行一列
select sysdate from dual;//结果只显示一行,但没有时分秒,所以要对其进行格式化,格式化之后就可以了。
//下面的dates都可以不加
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') dates from dual;
结果:2011-08-24 02:24:23
to_char不仅有将其他类型的数据转换成字符串的功能,还可以对其进行格式化
select to_char(sysdate,'D') dates from dual;//一周的第几天
select to_char(sysdate,'DD') dates from dual;//一个月的第几天
select to_char(sysdate,'DDD') dates from dual;//一年的第几天
select to_char(sysdate,'DAY') dates from dual;//星期几
select to_char(sysdate,'year') dates from dual;//输出年
select to_char(sysdate,'month') dates from dual;//输出月份
select to_char(sysdate,'SSSSS') dates from dual;//从今天00:00到现在的秒数
(2)add_months() //增加月份(负数表示减少月份)
select add_months(sysdate,1) dates from dual;//结果:2011-9-24 1
select add_months(sysdate,1) from dual; //结果:2011-9-24 14:34:46
select add_months(sysdate,-1) dates from dual;
(3)last_day()//求最后一天
select last_day(sysdate) from dual;//结果:2011-8-31 14:36:5
//求一个月的第三天:先加上三天到下个月的第三天,然后再减去一个月
select add_months(last_day(sysdate)+3,-1) from dual;
(4) months_between()//两个时间相差几个月(有可能是小数,因为不是一个整月)
select months_between(sysdate,to_date('2010-08-24','yyyy-mm-dd')) from dual;
select sysdate+1 from dual;
3、转换函数
to_char() :to_char(sysdate,'yyyy-mm-dd hh:mi:ss'
to_date() :to_date('2010-08-25','yyyy-mm-dd')
to_number()
nvl() 空值转换 :select nvl(comm,'0') from emp;//控制被替换为第二个参数
4、数学函数
ceil(x) 不小于x的最小整数
ceil(12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整数
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x)
舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; //结果:2011-08-24 02:46:44
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; //结果:2011-08-24 14:46:44
select to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual; //结果:2011-08-24 00:00:00
mod(x,n) x除以n以后的余数
mod(5,2) 1
mod(4,2) 0
power(x,y) x的y次方
select power(3,3) from dual;
abs(x) 绝对值
5、混合函数
求最大值:select greatest(100,45,01,78,999) from dual;//参数可以放任意个数,也可放数组
求最小值:select least(100,45,01,78,999) from dual;
6、复杂函数:
decode()
select emp.*,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','不作判断') 工资等级 from emp;
相当于下面的判断:
if(sal=800){
低工资
}else if(sal=3000){
正常工资
}else if(sal=5000){
高工资
}else{
不作判断
}
*************************************************************************************************************************************************
二、聚合函数,或称为多行函数、分组函数
count()
select count(distinct job) from emp;
select distinct job from emp;
select job from emp group by job;
avg()
-- 每个部门每种职位平均工资
select deptno,avg(sal),job from emp group by deptno,job; //group by后面可以加多个字段
sum()
max()
min()
//每种工作的最高工资与最低工资
select max(sal),min(sal) from emp group by job;
--显示部门名称和人数
select deptno,dname,(select count(*) from emp where emp.deptno=dept.deptno) from dept;
--每个部门最高工资
select * from emp a
where (select count(*) from emp where deptno = a.deptno and sal > a.sal) =0;
********--每个部门前两名工资
********select * from emp a where (select count(*) from emp where deptno=a.deptno and sal>a.sal)<=1;
*************************************************************************************************************************************************
三。层次查询
***老总为第一层,经理为第二层,组长为第三层...可以查询层次
level //可以显示第几层
select lpad('+',level*2,' ')||ename from emp connect by prior empno=mgr start with ename='KING';
----------先拼接“+”号左边的部分:lpad('+',level*2,' ');再通过字符串连接完成
----------empno=mgr:员工=他的经理
----------start with ename='KING' 以什么为根节点
----------去掉prior只显示根节点???????
//结果:
+KING //第一层
+JONES //第二层
+SCOTT //第三层
+ADAMS //第四层
+FORD //第二层
+SMITH
+BLAKE
+ALLEN
+WARD
+MARTIN
+TURNER
+JAMES
+CLARK
+MILLER
*************************************************************************************************************************************************
伪列:rowid:行的唯一标识, rownum:行号
分页:
select * from (select rownum as num,emp.* from emp) where num>=6 and num<=10;//结果正确
select * from (select rownum as num,emp.* from emp) where rownum>=6 and rownum<=10;//结果错误,,因为系统认为此处三个rownum不是同一个
约束:
定义约束一般的命名规则
主键:pk_ 非空+唯一
唯一:uk_
非空:nk_
外键:fk_
检查:ck_ check (age between 1 and 100)
--复制表结构及表中全部数据
create table myemp as select * from emp;
--复制表结构
create table myemp as select * from emp where 1=2;
--复制全部数据
insert into myemp select * from emp;
一个表上到底有哪些约束???
select constraint_name,constraint_type
from user_constraints
where table_name = 'EMP'
--查看表上有什么约束
select * from user_constraints;
--查看约束作用在什么字段上
select * from user_cons_columns
where CONSTRAINT_NAME='PK_STU';
user_constraints数据字典表
*************************************************************************************************************************************************
DCL 数据控制语言:grant,revoke
conn system/manager@orcl //连接到管理员
create user zhangsan identified by abc;//创建用户zhangsan,abc是密码,注意密码不可以以数字开头。这是张三什么权限也没有,连数据库都连接不上
grant connect,resource to zhangsan //授予zhangsan权限,这样就可以连接上了,但是不可以查询其他用户的表,需要其他用户给张三赋予权限
conn scott/tiger@orcl //连接到给zhangsan赋予权限的scott用户
grant all on emp to zhangsan //将增删改查都赋予zhangsan
grant select on emp to zhangsan //只将查找权限赋予zhangsan
conn zhangsan/abc@orcl //再次连接到zhangsan就可以增删改查了。 也可以这样写:conn zhangsan/abc
grant create view to scott //连接上system后给scott赋予创建视图的权限
revoke select on emp from zhangsan //收回权限
*************************************************************************************************************************************************
序列
sequences:右键新建序列。
命名规范:seq_表名_字段名(一般是主键)
select seq_emp_empno.currval from dual;//查询当前值
select seq_emp_empno.nextval from dual;//查询下一个值
*************************************************************************************************************************************************
视图:
//使用视图主要为了安全
//视图中存的不是具体的数据,而是视图的定义即查询语句*******
create view aa as select * from emp where sal>2500;
create or replace view a as select * from emp where sal<3000;//加上or replace 表示如果之前有则覆盖掉之前的视图
select * from aa;//对于视图的操作和普通表差不多
//可以使用视图像原始表中添加、更新数据,但是基于多张表的视图不可以修改。不想通过视图修改的话要加:with read only
//如果在视图的定义中有如下条件where sal<3000,则加上:with cheak option,会在通过视图更新基表时先判断此条件,不符合条件的不让更新
SQL> create or replace view a as select empno,ename from emp where sal>2500 with check option;
View created
SQL> insert into a values(1231,'d');
insert into a values(1231,'d')
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
*************************************************************************************************************************************************
同义词:
//先将定义同义词的权限赋予用户:grant create synonym to scott;
//就是起个别名,比如给SCOTT.EMP表起一个别名
关键字:synonym
create or replace synonym AAAA for SCOTT.EMP;
create or replace public synonym AAAA for SCOTT.EMP;//公有的
*************************************************************************************************************************************************
索引:
在查询的时候用,提高查询效率,内部基于B树或位图
*************************************************************************************************************************************************
按工资和工作月份的乘积排序。
select sal*months_between(sysdate,hiredate) as total from emp order by total
*************************************************************************************************************************************************
PL/SQL编程:
1、基本格式:
declare
begin
end;
/
//注意:上面的end后面要加分好,而且后面还要加一个斜杠,这样才可以编译执行。
2、declare:
name varchar2(10):='aaa';声明并且初始化
3、
dbms_output.put_line(name);输出并且换行,dbms_output.put_line是一个包
4、注释:
单行注释:--aaaa
多行注释:
/*sagfaf
agrarga
*/
5、要想输出结果则要在PL/SQL语句块上面加上set serveroutput on;这样就可以输出了。
而且只是dbms_output.put(name),这样不会输出。只能加上一句dbms_output.new_line();或者就用dbms_output.put_line
//总而言之要有换行
6、如何与表字段的类型保持一致?
v_name emp.ename%type;--表示提取emp表中的ename列的类型赋给v_name
v_emp emp%rowtype;--表示提取emp表中一行各列的类型赋给v_name,v_name像一个数组存了各列的类型
select * into v_emp from emp where empno=7788;--主意一定要将结果赋给某个变量:into v_emp
dbms_output.put_line(v_emp.ename);--读取列的类型
dbms_output.put_line(v_emp.sal);
7、定义和使用TABLE变量(像数组)。(类似于自定义类型)
type aa is table of varchar2(30) index by binary_integer;
v_t aa; --上面定义的类型此处是可以使用滴
v_t(1):='MONDAY'; --赋值
v_t(2):='TUESDAY';
v_t(3):='WEDNESDAY';
v_t(4):='THURSDAY';
v_t(5):='FRIDAY';
dbms_output.put_line(v_t(1));--输出
dbms_output.put_line(v_t(2));
dbms_output.put_line(v_t(3));
dbms_output.put_line(v_t(4));
dbms_output.put_line(v_t(5));
8、三种循环:
set serveroutput on
declare a number default 1;
begin
--111111111:
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
--222222222:
loop
dbms_output.put_line(a);
a:=a+1;
exit when a=10;
end loop;
--333333333:
while a<10 loop
dbms_output.put_line(a);
a:=a+1;
end loop;
end;
/
--------
set serveroutput on
--【训练1】 查询雇员编号为7788的雇员姓名和工资。
declare
v_name emp.ename%type;
v_sal emp.sal%type;
v_1 number(4);
v_date date default sysdate;
v_emp emp%rowtype;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
--type abctype is table of varchar2(30) index by binary_integer;
--v_t abctype;
begin
--case语句:
/* CASE v_job
WHEN 'PRESIDENT' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
WHEN 'MANAGER' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:经理');
ELSE
DBMS_OUTPUT.PUT_LINE('雇员职务:未知');
END CASE;
*/
/*v_t(1):='MONDAY';
v_t(2):='TUESDAY';
v_t(3):='WEDNESDAY';
v_t(4):='THURSDAY';
v_t(5):='FRIDAY'; */
--if语句:
/* if i<>100 or i!=90 then
dbms_output.put_line('aa');
elsif i>200 then
dbms_output.put_line('cc');
else
dbms_output.put_line('ee');
end if;*/
select * into v_emp from emp where empno=7788;
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.sal);
end;
/
********************************************************************************************************************************************************
游标: ----在内存中的一个临时区域,存储数据。
1、隐士游标:一下时候会用到,不过对于我们来说是透明的
insert
update
delete
select into (单行查询)
使用隐士游标:
SQL%isopen 判断游标是否打开
SQL%found 判断是否执行成功
SQL%notfound 判断
SQL%rowcount 总的行数
例子:
--【训练1】 使用隐式游标的属性,判断对雇员工资的修改是否成功。
set serveroutput on
declare
begin
update emp set sal=sal+1000 where empno=7788;
if SQL%FOUND then
dbms_output.put_line('修改成功');
commit;
else
dbms_output.put_line('修改失败');
rollback;
end if;
end;
/
/*/*/*/*/*/*/*/*//*/*/*/*/*/*/*/*/
************2、显式游标/*/*/*/*/*/
/*/*/*/*/*/*/*/*//*/*/*/*/*/*/*/*/
格式:cursor 游标名 is 查询语句(所有查询语句都支持)
cursor c1 is select empno,sal from emp;--游标定义
cur1 c1%rowtype;--游标变量(将游标c1的类型赋给cur1)与以下类似:
v_empno emp.empno%type;
v_sal emp.sal%type;
--【训练1】 用游标提取emp表中7788雇员的名称和职务。
set serveroutput on
declare
cursor c1 is select ename,job from emp where empno=7788;
v_name emp.ename%type;
v_job emp.job%type;
begin
--打开游标
open c1;
--提取游标数据
fetch c1 into v_name,v_job;
dbms_output.put_line('员工姓名:'||v_name||',员工职务:'||v_job);
--关闭游标
close c1;
end;
/
--【训练2】 用游标提取emp表中7788雇员的姓名、职务和工资。
set serveroutput on
declare
cursor c1 is select ename,job,sal from emp where empno=7788;
v_emp c1%rowtype;
begin
--打开游标
open c1;
--提取游标数据
fetch c1 into v_emp;
dbms_output.put_line('员工姓名:'||v_emp.ename||',员工职务:'||v_emp.job||','||v_emp.sal);
--关闭游标
close c1;
end;
/
--【训练3】 显示工资最高的前3名雇员的名称和工资。
set serveroutput on
declare
cursor c1 is select ename,sal from emp order by sal desc;
v_emp c1%rowtype;
begin
--打开游标
open c1;
--提取游标数据
for i in 1..3 loop
fetch c1 into v_emp;
dbms_output.put_line('员工姓名:'||v_emp.ename||','||v_emp.sal);
end loop;
--关闭游标
close c1;
end;
/
/*/*/*/*/*/*/*/*//*/*/*/*/*/*/*/*//*/*/*//*/*/*/*/*/*/*/*/
/*/*/*/*/*/特殊的FOR循环----类似于foreach/*/*/*/*/*/*/*/*/
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/
--【训练1】 使用特殊的FOR循环形式显示全部雇员的编号和名称。
set serveroutput on
declare
cursor c1 is select empno,ename from emp;
--v_emp c1%rowtype;--下面采用隐士定义
begin
for v_emp in c1 loop-----------不在需要显示的打开与关闭游标
dbms_output.put_line(v_emp.empno||','||v_emp.ename);
end loop;
end;
/
显示游标中个属性的含义:
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
--【训练1】 使用游标的属性练习。
SET SERVEROUTPUT ON
DECLARE
V_ename VARCHAR2(10);
CURSOR emp_cursor IS SELECT ename FROM emp;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!');
END IF;
CLOSE emp_cursor;
END;
/
--【训练1】 带参数的游标(注意没有参数类型)
--注意区别
SET SERVEROUTPUT ON
DECLARE
V_ename VARCHAR2(10);
CURSOR emp_cursor(p_ename varchar2) IS SELECT sal FROM emp where ename=p_ename;
BEGIN
OPEN emp_cursor('scott'); --传参数
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!');
END IF;
CLOSE emp_cursor;
END;
/
-/-/-/-/-/-/-/-/-/*/*/*/*-*
--【训练1】 动态SELECT查询。
SET SERVEROUTPUT ON
DECLARE
str varchar2(100);
v_ename varchar2(10);
begin
str:='select ename from scott.emp where empno=7788';
execute immediate str into v_ename;
dbms_output.put_line(v_ename);
END;
/
/*定义游标类型的语句如下:
TYPE 游标类型名 REF CURSOR;
声明游标变量的语句如下:
游标变量名 游标类型名;
在可执行部分可以如下形式打开一个动态游标:
OPEN 游标变量名 FOR 查询语句字符串; */
--【训练2】 按名字中包含的字母顺序分组显示雇员信息。
set serveroutput on
declare
type cur_type is ref cursor;
cur cur_type;
rec scott.emp%rowtype;
str varchar2(50);
letter char:= 'A';
begin
loop
str:= 'select ename from emp where ename like ''%'||letter||'%''';
open cur for str;
dbms_output.put_line('包含字母'||letter||'的名字:');
loop
fetch cur into rec.ename;
exit when cur%notfound;
dbms_output.put_line(rec.ename);
end loop;
exit when letter='Z';
letter:=chr(ascii(letter)+1);
end loop;
end;
/
***********************************************************************************************************************************************************************
无名语句块
数据库对象
存储过程,自定义函数,触发器
//////////////////////////////
过程比函数用的多
//////////////////////////////
存储过程:
格式:
create or replace procedure v_1(名称 in out 类型, 名称 in out 类型, ...) is
begin
end v_1;
参数:in 可不加,不加是默认是in,只能往里传递参数,在begin-end块里不可以赋值;out:往外传数据,在begin-end块里可以赋值;in out 既可以向里传数据,也可以向外传数据,在begin-end块里可以赋值。
-----过程的两种执行方式:
1:在PL/SQL中编译完后,execute 过程名;
2:在无名语句块中执行:
begin
过程名;
end;
/
--【训练1】 创建一个显示雇员总人数的存储过程。
create or replace procedure pro_1
is
v_count number(10);
begin
select count(*) into v_count from emp;
dbms_output.put_line('雇员总人数:'||v_count);
end;
/
--【训练3】 编写显示雇员信息的存储过程EMP_LIST,并引用pro_1存储过程。
create or replace procedure EMP_LIST
is
cursor c1 is select * from emp;
begin
for c_emp in c1 loop
dbms_output.put_line(c_emp.ename);
end loop;
pro_1;
end;
/
--【训练1】 编写给雇员增加工资的存储过程CHANGE_SALARY,
--通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
create or replace procedure CHANGE_SALARY(v_empno in number,v_sal number)
is
begin
update emp set sal=sal+v_sal where empno=v_empno;
if SQL%found then
commit;
else
rollback;
end if;
end;
/
--【训练3】 使用OUT类型的参数返回存储过程的结果。
create or replace procedure pro_2(v_count out number)
is
begin
select count(*) into v_count from emp;
end;
/
----执行:
set serveroutput on
declare
v_c number(10);
begin
pro_2(v_c);
dbms_output.put_line(v_c);
end;
/
--【训练4】 使用IN OUT类型的参数,给电话号码增加区码。
CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
AS
BEGIN
P_HPONE_NUM:='022-'||P_HPONE_NUM;
END;
/
-----执行:
set serveroutput on
declare
v_phone varchar2(20);
begin
v_phone:='12345678';
add_region(v_phone);
dbms_output.put_line(v_phone);
end;
/
********************************************************************************************************************************************
函数:
create or replace function fun_1(b varchar2)
return varchar2
is
a varchar2(10):='11111';
begin
a:=a||b;
return(a);
end;
/
调用:
set serveroutput on
declare
b varchar2(10):='aaa';
c varchar2(10);
begin
c:=fun_1(b);
dbms_output.put_line(c);
end;
/
注意:Warning: Function created with compilation errors
出现这种错误的时候调试:
show errors;
****************************************************************************************************************************************************************************
触发器:tigger
--可以对表和视图穿件触发器
----------------------------------------------------------------
-------触发器不可以有commit与rollback,可不可以调用含有commit或rollback的过程与函数
----------------------------------------------------------------
触发事件
1、DML事件--DML触发器
insert
delete
update
--------------------------------------
触发时间:before,after
触发器有两种:行级触发器/语句级触发器
行级触发器: 有for each row
语句级触发器: 没有for each row
--------------------------------------
:new调用新的数据
:old调用旧的数据
:new.empno
-------判断是什么事件-------------
INSERTING(insert)、DELETING(delete)、UPDATING(update)
用法:
如果是插入,则...
if INSERTING then...
----------设置触发器是否可用-----------
alter trigger cascade_update disable;
alter trigger cascade_update enable;
替代触发器 instead of
2、DDL事件
create
drop
alter
3、数据库事件
startup 启动数据库
logon 登录
logoff 退出
shutdown 关闭数据库
---------DML触发器-----------------
1、DML触发器(行级触发器)
--【1】 创建包含插入、删除、修改多种触发事件的触发器DML_LOG,对EMP表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。
create or replace trigger DML_LOG
before
insert or update or delete
on emp
for each row
declare
-- local variables here
begin
if inserting then
insert into logs values(seq_logs.nextval,'emp','insert',:new.empno,sysdate,user);
elsif updating then
insert into logs values(seq_logs.nextval,'emp','update',:new.empno,sysdate,user);
else
insert into logs values(seq_logs.nextval,'emp','delete',:old.empno,sysdate,user);
end if;
end;
/
【2】修改、删除刚刚插入的雇员记录,提交后检查LOGS表的结果。
【3】为DEPT表创建同样的触发器,使用LOGS表进行记录,并检验结果。
-------------------含有条件的触发器-----------------------------
----注意:when条件里的new与old前面不可以加冒号,而其他地方则必须加
【4】 创建一个行级触发器LOG_SAL,记录对职务为CLERK的雇员工资的修改,且修改幅度超过200时才进行记录。用WHEN条件限定触发器。
create or replace trigger log_sal
before
update
on emp
for each row
when (new.job='CLERK' and (abs(new.sal-old.sal)>200))
declare
-- local variables here
begin
insert into logs1 values(seq_logs.nextval,'雇员'||:new.ename||'的原工资:'||:old.sal||'新工资:'||:new.sal);
end;
/
-----------------引发应用程序异常---------------------
-----异常的处理:raise_application_error(代码,提示消息)。
----------------------------------------------------------------
--【5】 创建触发器CHECK_SAL,当对职务为CLERK的雇员的工资修改超出500至2000的范围时,进行限制。
create or replace trigger CHECK_SAL
before
update
on emp
for each row
declare
-- local variables here
begin
IF :new.job='CLERK' AND (:new.sal<500 OR :new.sal>2000) THEN
raise_application_error(-20001,'修改不符合范围');
end if;
end;
/
--【6】 创建一个行级触发器CASCADE_UPDATE,当修改部门编号时,EMP表的相关行的部门编号也自动修改。该触发器称为级联修改触发器。
create or replace trigger CASCADE_UPDATE
after
update of deptno
on dept
for each row
declare
-- local variables here
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
/
--【7】 将插入的雇员的名字变成以大写字母开头。
CREATE OR REPLACE TRIGGER INITCAP
BEFORE
INSERT
ON EMP
FOR EACH ROW
BEGIN
:new.ename:=INITCAP(:new.ename);
END;
语句级触发器:(没有FOR EACH ROW )
--【1】 创建一个语句级触发器CHECK_TIME,限定对表EMP的修改时间为周一至周五的早11点至晚5点。
create or replace trigger check_time
before
insert or update or delete
on emp
declare
-- local variables here
begin
if to_char(sysdate,'DAY') in ('SAT,SUN') or
to_char(sysdate,'HH24')<'09' or
to_char(sysdate,'HH24')>'17' then
raise_application_error(-20002,'error');
end if;
end;
/
-----------------------------------------
--【1】替代触发器:(针对视图操作的)
-----------------------------------------
先创建一个视图:
create or replace view v_emp as
select ename,sal,deptno from emp
再对视图进行操作:
create or replace trigger change_emp
instead of insert
on v_emp
declare
begin
insert into emp(empno,ename,sal,deptno) values(seq_emp_empno.nextval,:new.ename,:new.sal,:new.deptno);
end;
/
当执行如下语句时就可以顺利进行了
insert into v_emp values('fga',4454,10);
-----------------------------------
数据库触发器:
【1】 创建触发器,对本次数据库启动以来的用户登录时间进行记录,
每次数据库启动后,先清空该表。
CREATE TABLE userlog (
USERNAME VARCHAR2(20),
LOGON_TIME DATE);
CREATE OR REPLACE TRIGGER INIT_LOGON
AFTER
STARTUP
ON DATABASE
BEGIN
DELETE FROM userlog;
END;
CREATE OR REPLACE TRIGGER DATABASE_LOGON
AFTER
LOGON
ON DATABASE
BEGIN
INSERT INTO userlog
VALUES(sys.login_user,sysdate);
END;
【2】 创建STUDENT_LOGON模式级触发器,专门记录scott账户的登录时间:
CREATE OR REPLACE TRIGGER STUDENT_LOGON
AFTER
LOGON
ON scott.schema
BEGIN
INSERT INTO userlog
VALUES(sys.login_user,sysdate);
END;
【1】 通过触发器阻止对emp表的删除。
CREATE OR REPLACE TRIGGER NODROP_EMP
BEFORE
DROP
ON SCHEMA
BEGIN
IF Sys.Dictionary_obj_name='EMP' THEN
RAISE_APPLICATION_ERROR(-20005,'错误信息:不能删除emp表!');
END IF;
END;
*****************************************************************包**包**包******************************************************************************************************
包:
可以通过 包名.过程(函数)名 进行调用
包:v1
【1】 创建管理雇员信息的包EMPLOYE,它具有从EMP表获得雇员信息,
修改雇员名称,修改雇员工资和写回EMP表的功能。
CREATE OR REPLACE PACKAGE EMPLOYE --包头部分
IS
PROCEDURE SHOW_DETAIL;
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);
PROCEDURE SAVE_EMPLOYE;
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);
END EMPLOYE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分
IS
EMPLOYE EMP%ROWTYPE;
-------------- 显示雇员信息 ---------------
PROCEDURE SHOW_DETAIL
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
END SHOW_DETAIL;
----------------- 从EMP表取得一个雇员 --------------------
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)
AS
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END GET_EMPLOYE;
---------------------- 保存雇员到EMP表 --------------------------
PROCEDURE SAVE_EMPLOYE
AS
BEGIN
UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=
EMPLOYE.EMPNO;
DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');
END SAVE_EMPLOYE;
---------------------------- 修改雇员名称 ------------------------------
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)
AS
BEGIN
EMPLOYE.ENAME:=P_NEWNAME;
DBMS_OUTPUT.PUT_LINE('修改名称完成!');
END CHANGE_NAME;
---------------------------- 修改雇员工资 --------------------------
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)
AS
BEGIN
EMPLOYE.SAL:=P_NEWSAL;
DBMS_OUTPUT.PUT_LINE('修改工资完成!');
END CHANGE_SAL;
END EMPLOYE;
- oracle学习总结----oracle常用命令
- oracle学习总结(二)
- ORACLE 8023学习总结
- oracle学习总结(二)
- oracle学习总结(二)
- oracle学习总结(一)
- oracle学习总结(二)
- Oracle 学习总结
- oracle学习总结(二)
- 【oracle学习】简单总结
- Oracle数据库学习总结
- 宝宝Oracle学习总结
- oracle index学习总结
- oracle的学习总结
- oracle学习总结
- Oracle数据库学习总结
- oracle学习总结
- Oracle学习总结
- wget使用方法
- 汇编语言入门(在debug中编辑和调试程序)
- 【MySQL】Linux + MySQL数据库和结构管理(三)
- android webview 复制文本
- wordpress使用coreseek(sphinx)全文索引
- oracle学习总结
- EBS-R12-检测发票状态
- android webview 销毁
- spring中的各种通知
- oracle for update和for update nowait的区别
- 图片(使用Lumia920T)
- hibernate 乐观锁与悲观锁使用
- 第十四周 项目1抽象基类
- Android: NDK编程入门笔记