oracle学习总结

最新推荐文章于 2022-03-22 17:33:34 发布

快乐崇拜234

最新推荐文章于 2022-03-22 17:33:34 发布

阅读量1.4k

点赞数

分类专栏： oracle 文章标签： Oracle 数据库

本文链接：https://blog.csdn.net/liubenlong007/article/details/9006913

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

普通用户：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;

快乐崇拜234

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle学习总结

普通用户：scott/tiger数据库管理员：system/managerOS管理员：sys/change_on_install\基本命令：开始-运行-sqlplusw或sqlplus可以打开sql plus工具显示用户：show user 切换用户：conn 用户名/密码@orcl 查找所有表：select * from tab where tabtype='
复制链接

扫一扫