oracle学习总结

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

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值