oracle详细使用

最新推荐文章于 2021-04-03 07:20:03 发布
最新推荐文章于 2021-04-03 07:20:03 发布
阅读量692 收藏 7
点赞数 1
分类专栏： 数据库 文章标签： oracle 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44002865/article/details/106929063
版权

oracle

文章目录

1、基础

设置环境参数

  • 设置一行显示的字符数
    • set linesize n
  • 设置每一页的行数
    • set pagesize n
  • 返回结果太多时，可用来滚动查询
    • set pause on
    • set pause off



编辑类命令

edit  --全屏编辑器，用edit编辑缓冲区内容或文件内容
edit 文件名

@ /Start --读取文件到缓冲区，并执行文件命令
--脚本script：常用的sql语句，pl/sql程序块，存储在安装目录下，bin文件夹下，.sql 可以反复执行，用Start 和@ 来执行。
@ c:\oracle \a.sql; 文件不在bin目录下，就用绝对路径；
@ a.sql;    文件在bin目录下。

list n  --列出缓冲区内容

append text --向当前行追加内容
    
change --用新文本代替当前行文本
change / 旧文本/新文本

input text --在当前行后面添加文本

del n  --删除第n行

save  --将当前缓冲区的sql命令存入文件，默认路径：product\10.1.1\db_1\bin\
save 文件名 replace/append  ，默认的文件.sql

get   --把文件装入缓冲区 ，与save相反，，get 文件名

spool  --复制输出结果到文件，或把查询结果发送到处打印机，直到使用spool off为止；
spool Scott.txt append

prompt  --在屏幕上输出一行数据，在脚本文件和用户间传递信息
prompt prompttext；



报表

表示

  • clear:消除使用列的格式；

  • clear columns:消除所有的列格式；

  • format：改变列的数据显示格式；

  • format格式

    • A n ：为可变长字符串的列设置宽度，超过会自动换行；
    • 9：设置number列的显示格式，999 999
    • $:浮动的货币符号，
    • L:本地货币符号,￥
    • .：小数点位置，999.99
    • ，：千位分隔符，9,999.99

  • heading text：设置列标题为text；

  • justify {left,center,right}对列标题设置输出对齐方式；

  • null text：当列为空值时，显示的内容text；

  • wrapped：换行；

  • print：显示列标题；

  • noprint：隐藏列标题

    --将Scott.emp 的empno设为“员工编号”，number的格式为：9999，hiredate列设为标题居中，sal的输出格式：$9,999..999
column empno heading '员工编号' format 9999
column ename heading '员工姓名' format A10
column mgr heading '上级编号' format 9999
column hiredate heading '雇用日期' justify center
column sal heading '员工工资' format $9,999.999
select empno,ename,mgr,hiredate,sal from emp;



创建报表

  • ttile＆btitle

    • ttile [printspec [text|variable]]|[on|off] 指定出现在报表中每一页顶端的页眉；
    • btitle [printspec [text|variable]]|[on|off] 指定出现在报表中每一页底端的页脚；
    --查询emp表时，使用页眉页脚，放在一个文件report.sql中
report.sql：
ttitle left '日期：' _date  center  '执行Scott.emp报表'  right '页：' format 999 skip 2 sql.pno
btitle center '谢谢您的使用！'
set echo off
set verify off
set pagesize 40
set linesize 100
clear columns
column empno heading '员工编号' format 9999
column ename heading '员工姓名' format A10
column mgr heading '上级编号' format 9999
column hiredate heading '雇用日期' justify center
column sal heading '员工工资' format $9,999.999
select empno,ename,mgr,hiredate,sal from emp;
clear columns
ttitle off
btitle off

  • 计算小计

    • break：可以让sqlplus根据列值的范围分隔输出结果，使重复的列值不显示；
    • compute：可以计算一列的值，并在最后输出显示；
    --用break和compute命令将scott.emp表的输出结果根据deptno列进行分组，然后对每组的sal列进行求和计算；
break on deptno
compute sum of sal on deptno
select empno,ename,mgr,sal,deptno from emp order by deptno;


--创建报表，统计emp表中各部门的人数
ttitle left '日期：' _date  center  '执行Scott.emp报表'  
btitle left '谢谢您的使用！' center '页：' format 999 skip 2 sql.pno
set echo off
set verify off
set pagesize 40
set linesize 100
clear columns
column empno heading '员工编号' format 9999
column ename heading '员工姓名' format A10
column mgr heading '上级编号' format 9999
column hiredate heading '雇用日期' justify center
column sal heading '员工工资' format $9,999.999
column deptno heading '部门编号' format 9999

break on deptno
compute count label '部门人数' of empno on deptno
select empno,ename,mgr,sal,deptno from emp order by deptno;

clear columns
ttitle off
btitle off





2、管理表空间

基本表空间

  • 查看表空间

    select tablespace_name,logging,allocation_type,extent_management,segment_space_management 
from dba_tablespaces 
where tablespace_name='MYSPACE';

    -- 查询数据文件的信息
select tablespace_name,file_name,bytes 
from dba_data_files
where tablespace_name='MYSPACE';

  • 创建表空间

    create [temporary|undo] Tablespace 表空间名
   [datafile|tempfile '文件名' Size  大小k|m  [reuse]
   [autoextend off|on
    [next 扩展区大小k|m Maxsize 最大大小k|m]]

    create tablespace fff
 datafile 'e:\~fzk\oracle\fff.dbf' size 100m
  autoextend on next 15m maxsize 456m;

  • 修改表空间

    -- 将表空间名 myspace 改成 online
alter tablespace myspace online;

    -- 修改数据文件（d:\jsj1174.dbf）大小
alter database datafile 'd:\jsj1174.dbf' resize 1174m;

    -- 增加表空间(jsj1174)的数据文件(d:\jsj1175.dbf)
alter tablespace jsj1174 add datafile 'd:\jsj1175.dbf'  size 10m
autoextend on next 2m maxsize 20m;

  • 删除表空间

    drop  tablespace  表空间名
 [including contents [and datafiles]]
 
-- 实例
drop tablespace myspace including contents and datafiles;

    -- 删除表空间中的数据文件
alter tablespace myspace drop datafile 'E:\MYSPACE2.DBF';





3、用户管理

  • 显示所有账户的状态

    select username,account_status from dba_users;

  • 给账户解锁

    alter user dip account unlock;

  • 修改账户密码

    alter user outln identified by orcl;



创建用户

  • 步骤：分配表空间以及大小；创建用户，授予权限；

    create user 用户名
identified by 密码
[default tablespace 表空间名]
[temporary tablespace 表空间名]
[quota 数值 |unlimited on 表空间名]
[profile 文件名]
[password expire]  将用户口令设为过期，提示用户在第一次登录时更改口令
[account lock|unlock];


-- 实例
create  user fzk
identified by fzk
/*
default tablespace users 
temporary tablespace temp
quota 1024k on users
password expire
account lock;
*/



修改用户

  • 修改参数，密码，口令过期，状态，用户表空间，删除用户

    alter user 用户名 identified by 新口令；

alter user zyr identified by zyr;

--修改用户口令过期：
alter user 用户名 password expire;

--解锁、上锁：
alter user 用户名 account lock|unlock;

--新密码
alter user zyr identified  by zyr111;

--密码过期：
alter user zyr password expire;

--上锁：
alter  user  zyr account lock;

--解锁：
alter  user  zyr account unlock;

--修改用户的表空间：
alter user 用户名 
default tablespace users
temporary tablespace temp；

--删除用户：
drop user 用户名;

--如果该用户在数据库中创建了内容，要cascade级联删除，
drop user 用户名 cascade； 

--删除用户zyr：
drop user zyr cascade；



权限

  • 系统权限：对整个Oracle的操作权限，由dba授予用户，连接创建管理所有对象
  • 对象权限：用户对数据库中对象的操作权限，如对某一个表的插入修改删除等权限

查看系统权限

-- 只能查看当前用户的系统权限；
select * from user_sys_privs;  

-- 能查看所有用户的系统权限；
select * from dba_sys_privs;

授予权限

  • 授予系统权限

    grant 权限 to 用户名|角色 [with admin option];

--实例
grant create session,create table to zyr with admin option;
grant dba to fzk with admin option;

  • 收回系统权限

    revoke 权限 from 用户名;

--实例
revoke create session, create table from zyr;

  • 授予对象权限

    grant 对象权限 on 对象名 to 用户名|角色名 [with grant option];

--授予fzk 对scott.emp表增删改查的权限
grant select,insert,update,delete on scott.emp to fzk with grant option;

  • 收回对象权限

    -- 收回fzk 对scott.emp表的查询权限
revoke select on scott.emp from fzk;





4、常用的数据类型

字符数据类型

  • CHAR： CHAR数据类型存储固定长度的字符值
  • VARCHAR2：存储可变长度的字符串
  • LONG：LONG数据类型可以存放2GB的字符数据



数字数据类型

  • Number(m, n)：m表示数字中的有效位,n表示数字小数点右边的位数
    • Number（7，2） 12345.67
  • int
  • integer
  • small int
  • numeric



日期数据类型

  • Oracle标准日期格式为：DD-MON-YY HH：MI：SS
  • date
  • to_date(‘时间’, ‘类型’)
    • to_date(‘2012-2-12’,‘yyyy-mm-dd’)





5、管理表

创建表

create table 表名(
 列名  数据类型  [default  默认值][[constraint  约束名] 约束方式]
 ……
)[tablespace 表空间名]


--在自己的模式下创建type表格：
create table type(
  typeid number(4)  primary key,
  typename nvarchar2(20) unique not null
);

  • 利用子查询创建表

    Create table v1
As
Select --查询语句


--将表格的结构和内容全部复制到新表中
create table typee
as 
select * from type;


--复制表结构，不复制数据
create table type2
as 
select * from type
where 1=2;


--创建一个表，保存工资高于8000元的员工的员工号、员工姓名和部门号。 
CREATE TABLE sub_emp1l(empno,empname, salary, department)
 AS
 SELECT empno_empname,sal,depno
 FROM emp 
 WHERE sal>8000;



修改表

  • 管理表中的列

    --给表格type增加一列 typedef，类型nvarchar2(50)；
alter table type add  typedef  nvarchar2(50);

--将表格type中的 typedef列删除；
alter table type drop column typedef;

--将表格type中的列 typedef更名为typedefinition；
alter  table  type rename  column  typedef  to  typedefinition;

--将表格type中的列 typedefinition的数据类型改为char(50)；
alter table type modify  typedefinition  char(50);
--不可访问，仍存在；不要随便使用！！
alter table type set unused(typedefinition); 
--删除不可访问的数据项
alter table type drop  unused column;

  • 重命名表

    alter table type  rename  to  booktype;
 
--移动表到新的表空间：
alter table type  move tablespace users;

--查询users表空间：
Select table_name,tablespace_name 
from user_tables 
where tablespace_name='USERS';



删除表

drop table table_name [cascade constraints] [purge];

--删除type表
drop table type;



约束

  • 表中约束

    • 2大类：表级完整性和列级完整性
    • 6小类：非空，唯一，check，default，主码，外码约束

查看约束

  • 查看约束结构

    desc 表名

  • 查看表上的约束

    --查看book表上的约束
column column_name format a20;
column constraint_name format a20;

select a.table_name, a.column_name, a.constraint_name, b.constraint_type
from user_cons_columns a,user_constraints b
where a.table_name='book' and a.constraint_name=b.constraint_name;

约束的使用

  • 非空约束
    • 非空：not null
      • 定义表时指定约束： 列名 数据类型 [constraint 约束名] not null;
      • 定义表后修改约束： alter table 表名 modify 列名 [constraint 约束名] not null;
      • 删除非空约束： alter table 表名 modify 列名 null;
  • 唯一性约束：unique
    • 对于unique约束，如果没有not null，它允许有多个空值；
    • 定义表时指定约束： 列名 数据类型 [constraint 约束名] unique;
    • 定义表后修改约束： alter table 表名 add [constraint 约束名] unique(列名);
    • 删除约束：
      • alter table 表名 drop unique(列名);
      • alter table 表名 drop constraint 约束名;
  • 主码：primary key (unique not null)
    • 定义表时指定约束： 列名 数据类型 [constraint 约束名] primary key(列名);
    • 定义表后修改约束： alter table 表名 add [constraint 约束名] primary key(列名);
    • 删除约束： alter table 表名 drop constraint 约束名;
  • 检查约束check
    • check 用于指定一个条件，对列值进行检查；
    • 建表时指定约束：列名 列数据类型 [constraint 约束名] check (检查条件);
    • 建表后添加约束： alter table 表名 add [constraint 约束名] check (检查条件);
  • 外码约束：foreign key
    • 建表时在列后指定约束：列名 列数据类型 [constraint 约束名] references 表名(列名);
    • 建表时在所有列之后添上约束： [constraint 约束名] foreign key (列名) references 表名(列名);
    • 建表后添加约束： alter table 表名 add [constraint 约束名] foreign key (列名) references 表名(列名);
    • 有外码时的删除数据：
      • 级联删除：删除父表中的一条记录时，子表中相应的数据
        • cascade：级联删除子表中相应的记录行
        • set null：将子表中相应的记录行的列值设为空值
        • not action：禁止执行删除操作，是默认选项；



索引(index)

  • 创建索引

    • 如果定义列时加了unique约束，Oracle会自动建立一个唯一索引

    • 简单索引

      create unique index 索引名 on table 表名(列)；

create  table  reader(	
 readerid number(10) primary key,
 readerclass char(7) unique not null,
 readername varchar2(10) not null,
 readersex  char(2) not null check(readersex  in ('男','女'))
);

--在reader表的readername列上创建一个索引：name_index
create index name_index on reader(readername);

  • 基于函数的索引

    --小写
create index name_lower_index on reader (lower(readername));

  • 位图索引

    --bitmap 位图

--sex列
create bitmap index sex_index on student(sex);

  • 管理索引

    --重命名索引： 
alter index ……rename to ……;

--合并索引：数据更新频繁时，合并索引释放空间;
alter  index ……coalesce [deallocate|unused];

--重建索引：清除存储碎片，同时改变索引的空间；
alter index ……rebuild ;

--监视索引：
alter index ……monitoring|nomonitoring usage;

--通过v$object_usage查看（监视索引后可以查看）
select index_name,table_name,monitoring,used from  v$object_usage;

--删除索引：
--基于约束条件的索引（unique）：要删除或禁用约束；
--用户建立的索引：
drop index ……;
drop index sex_index;



视图(view)

  • 创建视图

    create [or replace] [force|noforce] view viewname 
as 
select语句 

or replace --若视图已存在，则进行替换；
force --若基表不存在，仍然保存定义；


--简单视图
--在Scott.emp 建立一个视图：要求具有create view权限；
create  or replace view emp_view
as
select empno,ename,job,sal,deptno from emp where sal>=3000;

--查询
select * from emp_view;


--创建复杂视图：多个表，函数或数学计算的视图，列名必须取别名；
--创建一个emp表的视图emp_view2,对sal进行数学计算，提高10%大于3000；
create or replace view  emp_view2
as
select empno,ename,sal,sal*1.1 new_sal,deptno 
from emp where sal*1.1>=3000;

  • 删除视图

    drop view viewname

  • 修改视图

    alter view viewname 
as 
select语句



序列(sequence)

  • Oracle查询所有序列

    --注意事项：
--1. 必须以管理员身份登录；
--2. sequence_owner必须为大写，不管你的用户名是否大写。只有大写才能识别。
--查看当前用户的所有序列   
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='FZK';  

--查询当前用户的序列总数   
select count(*) from dba_sequences where sequence_owner='SYS';  
  
--示例：   
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences    
where sequence_owner='fzk';

  • 创建序列

    create sequence 序列名
[increment by n]
[start with n]
[maxvalue n|nomaxvalue]
[minvalue n|nominvalue]
[cycle |nocycle]
[cache cache_number|nocache];--默认是20个

--创建一个myseq 的序列,从1开始，每次加1
create sequence myseq start with 1 increment by 1 nocache nocycle order;

  • 序列的使用

    • nextvalue：取得序列的下一个内容；
    • currvalue：取得序列的当前内容；
    --建立表
create table student(
 id number(10) primary key,
 snumber char(7) unique not null,
 name varchar2(10) not null,
 sex  char(2) not null check(sex in ('男','女'))
);

--手工插入多行数据：
insert into student  values(myseq.nextval,myseq.currval,'fh','女');

--查看输入的数据行；
select * from student;

  • 修改删除序列

    alter   ……;
drop sequence……;



同义词(synonym)

  • 查看所有同义词

    select * from dba_synonyms where owner='FZK';

  • 创建同义词

    create [public] synonym 同义词名称 for 用户名.表名称;

--例如：
Create public synonym emp1 for scott.emp;
Create synonym emp2 for scott.emp;

  • 删除同义词

    drop [public ]synonym 同义词名称;





6、plsql

块的语法

  • 由变量声明、程序代码和异常处理代码3部分组成

    DECLARE    --标记声明部分 ，用来定义常量、变量、类型和游标等
     <变量名1>  <数据类型1>;
     <变量名2>  <数据类型2>;
     ……
     <变量名n>  <数据类型n>;
BEGIN      --标记程序体部分开始 
……           --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
……           --此处用来编写异常处理代码
END;        --标记程序体部分结束



PL/SQL中常用数据类型

分类数据类型
数字类型NUMBER
字符类型VARCHAR2、CHAR
日期类型DATE、TIMESTAMP
行标识类型ROWID、UROWID
布尔类型BOOLEAN（TRUE、FALSE、NULL）
LOB类型CLOB、BLOB、NCLOB、BFILE
引用类型REF CURSOR，REF object_type。
记录类型RECORD
集合类型TABLE、VARRAY
  • 数字类型
    • number，pls_integer,binary integer(带符号整数），natural（非负整数）
      • number(p,s)
      • number(7,2)
  • 字符类型：
    • varchar2，char，long ，nchar，nvarchar2
    • 数据库变量和pl/sql变量是两个不同的概念，在建表是用数据库变量，比如用 varchar2（20）
  • 日期类型
    • date
    • To_date，
    • To_char
  • 布尔类型
    • TRUE，FALSE，null



常量

--声明常量，基本格式：
<常量名> constant <数据类型> := <值>;

--关键字constant表示声明的是常量。
--声明一个程序的版本信息常量conVersion：
Conversion constant VARCHAR2(20) := '2.0.01';

--声明一个显示成绩的常量为72：
score constant number ：=72；



变量

  • 变量的定义

    变量名 类型 [[not null] {:=|default} value];
--赋值符号： 
：=
--例如
tid number(5) not null :=0


--在程序的运行过程中，对变量进行赋值操作.
DECLARE
    Database VARCHAR2(50);
BEGIN
  Database := ' Oracle 19c ';
  dbms_output.put_line('我的数据库是:'||Database);
end;
/
--输出： 我的数据库是: Oracle 19c


--定义变量
DECLARE
 v1 NUMBER(4);
 v2 NUMBER(4) NOT NULL :=10;
 v3 CONSTANT NUMBER(4) DEFAULT 100;
BEGIN
  IF v1 IS NULL THEN 
      DBMS_OUTPUT.PUT_LINE('V1 IS NULL! ');
  END IF;
  DBMS_OUTPUT.PUT_LINE(v2||' '||v3);
END; 
/

  • 运算符

    • 字符运算符：并置运算符||，把几个字符串连载一起，
    • 布尔运算符：or，and，not



%TYPE 类型

  • 用于隐式的将变量的类型指定为对应列的数据类型；只针对表中的某一列

    变量名  表名.列名%type  [[not null] {:=dafault}value];

--使用%type来定义数据类型
set SERVEROUTPUT on;
declare 
	emp_number constant emp.empno%type:=8888;
	emp_name emp.ename%type;
	emp_job emp.job%type;
	emp_sal emp.sal%type;
begin
	select ename ,job ,sal
	into emp_name,emp_job,emp_sal
	from emp
	where empno=emp_number;
	DBMS_OUTPUT.PUT_LINE ('查询员工的编号为：'||emp_number);
	DBMS_OUTPUT.PUT_LINE ('查询员工的name为：'||emp_name);
	DBMS_OUTPUT.PUT_LINE ('查询员工的job为：'||emp_job);
	DBMS_OUTPUT.PUT_LINE ('查询员工的sal为：'||emp_sal);
end;
/



%ROWTYPE 类型

  • 该类型的变量存储表中的一行数据

    变量名  表名%ROWTYPE ;

--修改例2 ：
set SERVEROUTPUT on;
declare 
emp_number constant emp.empno%type:=8888;
oneemp emp%rowtype;
begin
	select *
	into oneemp
	from emp
	where empno=emp_number;
    DBMS_OUTPUT.PUT_LINE ('查询员工的编号为：'||emp_number);
    DBMS_OUTPUT.PUT_LINE ('查询员工的name为：'||oneemp.ename);
    DBMS_OUTPUT.PUT_LINE ('查询员工的job为：'||oneemp.job);
    DBMS_OUTPUT.PUT_LINE ('查询员工的sal为：'||oneemp.sal);
    DBMS_OUTPUT.PUT_LINE ('查询员工的bumenzhg为：'||oneemp.mgr);
end;
/



记录类型

  • 记录类型与表的行结构相似，可以存储有一个或多个字段组成的一行数据

    TYPE  record_name IS RECORD  (
    field_name date_type [[not null] {:=dafault}|value]
    ……
);

--创建一个记录类型，定义一个变量，并为该变量赋值：
set SERVEROUTPUT on;
declare 
TYPE  emp_type IS RECORD  (
    empno  number(4),
    ename   varchar2(10),
    job varchar2(9),
    sal number(7,2)
);
oneemp emp_type;
begin
    select empno ,ename,job,sal
    into oneemp
    from emp
    where empno=7900;
    DBMS_OUTPUT.PUT_LINE ('查询员工的编号为：'||oneemp.empno);
    DBMS_OUTPUT.PUT_LINE ('查询员工的name为：'||oneemp.ename);
    DBMS_OUTPUT.PUT_LINE ('查询员工的job为：'||oneemp.job);
    DBMS_OUTPUT.PUT_LINE ('查询员工的sal为：'||oneemp.sal);
end;
/



表类型

  • 是对记录类型的扩展，允许处理多行数据，类似于表

    TYPE  table_name  IS TABLE OF  data_type [ NOT NULL ] INDEX BY BINARY_INTEGER ;
--说明：
--table_name  表类型名
--IS TABLE  表示创建的是表类型。
--data_type  可以是任何合法的PL/SQL数据类型，例如varchar2。
--INDEX BY BINARY_INTEGER  指定系统创建一个主键索引，用于引用表类型变量中的特定行。


--创建一个表类型，直接对表类型变量赋值并输出：
set SERVEROUTPUT on;
declare
    TYPE  my_emp  IS table of  emp%rowtype index by binary_integer;
    oneemp my_emp;
begin
     oneemp(1).empno:=6800;
     oneemp(1).ename:='zyr';
     oneemp(1).sal:=5500;
     oneemp(2).empno:=6900;
     oneemp(2).ename:='zzz';
     oneemp(2).sal:=6500;
	 DBMS_OUTPUT.PUT_LINE ('员工的编号为：'||oneemp(1).empno||'   员工的姓名为：'||oneemp(1).ename||'  员工的工资为：'||oneemp(1).sal);
end;
/



动态操作

--动态输入员工编号，修改员工工资；
set SERVEROUTPUT on;
DECLARE 
  v_empno NUMBER(4);
BEGIN
  v_empno:=&x; --输入
  UPDATE emp SET sal=sal+100 
  WHERE empno=v_empno;
END; 
/
/*
运行结果：
输入 x 的值:  8800
原值    4:   v_empno:=&x;
新值    4:   v_empno:=8800;
*/



异常

异常情况名错误代码描述
CURSOR_ALREADY_OPENORA-06511尝试打开已经打开的游标
INVALID_CURSORORA-01001不合法的游标操作（如要打开已经关闭的游标）
NO_DATA_FOUNDORA-01403没有发现数据
TOO_MANY_ROWSORA-01422一个SELECT INTO语句匹配多个数据行
INVALID_NUMBERORA-01722转换成数字失败 （‘X’）
VALUE_ERRORORA-06502截断、算法或转换错误，通常出现在赋值错误
ZERO_DIVIDEORA-01476除数为0
ROWTYPE_MISMATCHORA-06504主机游标变量与PL/SQL游标变量类型不匹配
DUP_VAL_ON_INDEXORA-00001违反唯一性约束或主键约束
SYS_INVALID_ROWIDORA-01410转换成ROWID失败
TIMEOUT_ON_RESOURCEORA-00051在等待资源中出现超时
LOGIN_DENIEDORA-01017无效用户名/密码
CASE_NOT_FOUNDORA-06592没有匹配的WHEN子句
NOT_LOGGED_ONORA-01012没有与数据库建立连接
STORAGE_ERRORORA-06500PL/SQL内部错误
PROGRAM_ERRORORA-06501PL/SQL内部错误

  • 最常见的异常：

    • no_data_found ：没有数据
    • too_many_rows：返回多行匹配的数据
    • zero_divide :0溢出
    EXCEPTION
  WHEN <异常情况名> THEN
    <异常处理代码>
  WHEN <异常情况名> THEN
    <异常处理代码>
  ……
  WHEN OTHERS THEN
    <异常处理代码>
    
    
--数据库操作有关的异常处理代码：
SET SERVEROUTPUT ON;
DECLARE
    var_UserName VARCHAR(40);
BEGIN
    SELECT UserName INTO var_UserName
	FROM dba_Users WHERE User_ID = 0;
	DBMS_OUTPUT.PUT_LINE('There is no exception!The username is:'||var_username);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('没有数据');
       WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('返回多行匹配的数据');
        WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('错误情况不明');
END;
/



条件语句

  • if

    IF <条件表达式> THEN
  <执行语句> …… <执行语句n>
[ELSIF <条件表达式> THEN
  <执行语句> …… <执行语句n>
 ……
 ELSE
  <执行语句>]
END IF;


--演示IF语句的使用方法：
SET ServerOutput ON;
DECLARE
  Num INTEGER := -11; 
BEGIN
  IF Num < 0 THEN
    dbms_output.put_line('负数');
  ELSIF Num >0 THEN
    dbms_output.put_line('正数');
  ELSE
    dbms_output.put_line('0');
  END IF;
END;

  • case

    CASE <变量>
  WHEN <表达式1> THEN 值1
  WHEN <表达式2> THEN 值2
  ……
  WHEN <表达式n> THEN 值n
  ELSE 值n + 1
END;


--简单case语句，在语句中使用单一条件，
case 表达式 （1个变量）
 when 选择1 then result1;
 when 选择2 then result2;
…………
else result；
end case;


--搜索case语句,在语句中使用多种条件，
case
  when 选择1 then result1;
  when 选择2 then result2;
…………
  else result；
end case;


--使用CASE语句根据给定的整数输出对应的星期值：
SET ServerOutput ON;
DECLARE
  varDAY INTEGER := 3; 
  Result VARCHAR2(20);
BEGIN
  Result := CASE varDAY
    WHEN 1 THEN '星期一'
    WHEN 2 THEN '星期二'
    WHEN 3 THEN '星期三'
    WHEN 4 THEN '星期四'
    WHEN 5 THEN '星期五'
    WHEN 6 THEN '星期六'
    WHEN 7 THEN '星期七'
    ELSE '数据越界'
  END;
  dbms_output.put_line(Result);
END;


--用select输出今天是星期几？
SELECT  
  CASE WHEN TO_CHAR(SYSDATE, 'D') = '1' THEN '星期日'
       WHEN TO_CHAR(SYSDATE, 'D') = '2' THEN '星期一'
       WHEN TO_CHAR(SYSDATE, 'D') = '3' THEN '星期二'
       WHEN TO_CHAR(SYSDATE, 'D') = '4' THEN '星期三'
       WHEN TO_CHAR(SYSDATE, 'D') = '5' THEN '星期四'
       WHEN TO_CHAR(SYSDATE, 'D') = '6' THEN '星期五'
       WHEN TO_CHAR(SYSDATE, 'D') = '7' THEN '星期六'
  END
FROM  dual;



循环语句

  • LOOP…EXIT…END

    LOOP
  <程序块1>
  IF <条件表达式> THEN
    EXIT
  END IF
  <程序块2>  
END LOOP;


--LOOP…EXIT…END语句的示例程序：
SET ServerOutput ON;
DECLARE
  v_Num INTEGER := 1; 
  v_Sum INTEGER := 0;
BEGIN
  LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    IF v_Num = 3 THEN
      EXIT;
    END IF;
    dbms_output.put_line(' + ');
    v_Num := v_Num + 1;
  END LOOP;
  dbms_output.put_line(' = ');
  dbms_output.put_line(v_Sum);
END;

  • LOOP…EXIT WHEN…END

    LOOP
  <程序块1>
  EXIT WHEN <条件表达式> 
  <程序块2>
END LOOP;


--用LOOP…EXIT WHEN…END语句来实现：
SET ServerOutput ON;
DECLARE
  v_Num INTEGER := 1; 
  v_Sum INTEGER := 0;
BEGIN
  LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    EXIT WHEN v_Num = 3;
    dbms_output.put_line(' + ');
    v_Num := v_Num + 1;
  END LOOP;
  dbms_output.put_line(' = ');
  dbms_output.put_line(v_Sum);
END;

  • WHILE…LOOP…END LOOP

    WHILE <条件表达式>
LOOP
  <程序块>
END LOOP;


--用WHILE…LOOP…END LOOP语句来实现：
SET ServerOutput ON;
DECLARE
  v_Num INTEGER := 1; 
  v_Sum INTEGER := 0;
BEGIN
  WHILE v_Num <= 3
  LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    IF v_Num < 3 THEN
      dbms_output.put_line(' + ');
    END IF;
    v_Num := v_Num + 1;
  END LOOP;
  dbms_output.put_line(' = ');
  dbms_output.put_line(v_Sum);
END;
/

  • FOR…IN…LOOP…END LOOP

    FOR <循环变量> IN <初始值> .．<终止值>
LOOP
  <程序块>
END LOOP;


--用FOR…IN…LOOP…END LOOP语句来实现，代码如下：
SET ServerOutput ON;
DECLARE
  v_Num INTEGER; 
  v_Sum INTEGER := 0;
BEGIN
  FOR v_Num IN 1..3
  LOOP
    v_Sum := v_Sum + v_Num;
    dbms_output.put_line(v_Num);
    IF v_Num < 3 THEN
      dbms_output.put_line(' + ');
    END IF;
  END LOOP;
  dbms_output.put_line(' = ');
  dbms_output.put_line(v_Sum);
END;





7、游标（CURSOR）

游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果

  • 显式游标属性
    • %ISOPEN：布尔型。如果游标已经打开，返回TRUE,否则为FALSE。
    • %FOUND：布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;
    • %NOTFOUND：布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;
    • %ROWCOUNT：数值型，返回到目前为止从游标缓冲区检索的元组数。
    • %BULK_ROWCOUNT（i）：数值型，用于取得FOR ALL语句执行批绑定操作时第i个元素所影响的行数



步骤

  • 定义游标

    CURSOR 游标名称 IS select语句 ;

--实例
DELCARE 
CURSOR C_EMP IS SELECT empno,ename,salary FROM emp WHERE salary>=2000  ORDERBY ename; 
........ 
BEGIN

  • 打开游标

    OPEN 游标名称;

--实例
OPEN C_EMP;

  • 检索游标即从游标提取数据

    FETCH 游标名称 INTO 参数...;

--实例
FETCH c_emp INTO v_ename,v_salary;

  • 关闭游标

    CLOSE 游标名称;

--实例
CLOSE C_EMP;

--查询员工信息；
SET SERVERoUTPUT ON 
DECLARE 
v_ename EMP.ENAME %TYPE; 
v_salary EMP.SAL %TYPE; 
CURSOR c_emp IS SELECT ename,sal FROM emp; --定义游标
BEGIN 
    OPEN c_emp;  --打开游标
    FETCH c_emp INTO v_ename,v_salary;  --检索游标
    DBMS_OUTPUT.PUT_LINE('Salary of Employee '||v_ename||' is '||v_salary); 
    FETCH c_emp INTO v_ename,v_salary;  --检索游标
    DBMS_OUTPUT.PUT_LINE('Salary of Employee '||v_ename||' is '||v_salary); 
    FETCH c_emp INTO v_ename,v_salary;  --检索游标
    DBMS_OUTPUT.PUT_LINE('Salary of Employee '||v_ename||' is '||v_salary); 
    CLOSE c_emp;  --关闭游标
END; 
/



游标的循环

利用loop循环检索游标

DECLARE
 CURSOR cursor_name IS SELECT…;  --定义游标
BEGIN
   OPEN cursor_name;  --打开游标
   LOOP  --loop循环
      FETCH…INTO…;  --检索游标
      EXIT WHEN cursor_name%NOTFOUND;  --判断是否没有数据，退出
      ……
   END LOOP;  
   CLOSE cursor_name;  --关闭游标
END; 


--使用循环重新写上面的程序例1： 
SET SERVEROUTPUT ON 
DECLARE 
v_ename EMP.ENAME%TYPE; 
v_salary EMP.SAL%TYPE; 
CURSOR c_emp IS SELECT ename,sal FROM emp; 
BEGIN 
    OPEN c_emp; 
    LOOP 
        FETCH c_emp INTO v_ename,v_salary; 
        EXIT WHEN c_emp%NOTFOUND; 
        DBMS_OUTPUT.PUT_LINE('the '||c_emp%rowcount ||' Salary of Employee '||v_ename||' is '||v_salary); 
    END loop;
    close c_emp;
end;
/

利用WHILE循环检索游标

DECLARE
CURSOR cursor_name IS SELECT…;
BEGIN
    OPEN cursor_name;
    FETCH…INTO…;
    WHILE cursor_name%FOUND LOOP
        FETCH…INTO…;
        ……
    END LOOP;
    CLOSE cursor;
END;


--利用WHILE循环统计并输出各个部门的平均工资。
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,  avg(sal) avgsal FROM emp  GROUP BY deptno;
  v_dept c_dept_stat%ROWTYPE;
BEGIN
  OPEN c_dept_stat;
  FETCH c_dept_stat INTO v_dept; 
  WHILE c_dept_stat%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('The department number is :'||v_dept.deptno||' '|| 'The department varage salary is:'||v_dept.avgsal);
    FETCH c_dept_stat INTO v_dept;
  END LOOP;
  CLOSE c_dept_stat;
END; 
/

利用FOR循环检索游标

  • 定义游标

    DECLARE
CURSOR cursor_name IS SELECT…;
BEGIN
    FOR loop_variable IN cursor_name LOOP
      …………
    END LOOP;
END;


--利用FOR循环统计并输出各个部门的平均工资。
DECLARE
CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
BEGIN
    FOR v_dept IN c_dept_stat LOOP
    	DBMS_OUTPUT.PUT_LINE('The department number is :'||v_dept.deptno||' '|| 'The department varage salary is:'||v_dept.avgsal);
    END LOOP; 
END; 
/

  • 不定义游标

    FOR record_name IN select语句 LOOP 
	………… 
ENDLOOP;

--不定义游标直接利用FOR循环统计并输出各个部门的平均工资。
BEGIN
    FOR v_dept IN  (SELECT deptno,avg(sal)  avgsal FROM emp GROUP BY deptno) 
    LOOP
    	DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
    END LOOP; 
END; 
/



带参数的游标

CURSOR cursor_name[(parameter[,parameter],...)] IS select语句


--统计各部门的部门详情及员工人数，工资总和; 
set serveroutput on
DECLARE 
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; 
CURSOR c_emp (p_dept number) IS SELECT empno,ename,sal FROM emp WHERE deptno=p_dept ORDER BY ename ;
v_tot_salary  EMP.SAL%TYPE; 
BEGIN 
    FOR current_cursor IN c_dept LOOP 
        DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
        DBMS_OUTPUT.PUT_LINE('Department:'||current_cursor.deptno||'-'||current_cursor.dname); 
        v_tot_salary:=0; 
        FOR r_emp IN c_emp (current_cursor.deptno) LOOP 
            DBMS_OUTPUT.PUT_LINE('empno:'|| r_emp.empno||',Name:'||r_emp.ename||',salary:'||r_emp.sal); 
            v_tot_salary:=v_tot_salary + r_emp.sal; 
        END LOOP; 
        DBMS_OUTPUT.PUT_LINE('         ');
        DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'||v_tot_salary); 
        DBMS_OUTPUT.PUT_LINE('         ');
    END LOOP; 
END; 
/



使用游标更新数据

  • 要想使用游标更新数据，就需要在声明游标时加上

    for update of 列名 nowait;

  • 在使用更新语句update，delete时要使用

    where current of 游标名;

--更新部门编号为20的员工工资，增加10%；
set serveroutput on
DEClaRE 
cursor emp_cur is select empno,ename,job,sal from emp where deptno=20 for update of sal nowait;
begin
    FOR current_cursor IN emp_cur
    loop
    	dbms_output.put_line('the'||emp_cur%rowcount||current_cursor.empno||current_cursor.ename||current_cursor.sal);
    end loop;
    FOR current_cursor IN emp_cur
    loop
    	update emp set sal=sal*1.1 where current of emp_cur;
    end loop;
    FOR current_cursor IN emp_cur
    loop
    	dbms_output.put_line('the'||emp_cur%rowcount||current_cursor.empno||current_cursor.ename||current_cursor.sal);
    end loop;
end;
/





8、存储过程(PROCEDURE)

参数的模式

  • IN（默认参数模式）：只能读该参数，而不能修改该参数
  • OUT：读/写操作
  • IN OUT：可读可写



创建存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[, parameter2_name [mode] datatype [DEFAULT|:=value],…])
AS|IS
   /* 变量声明部分 */
BEGIN
   /*执行部分*/ 
EXCEPTION
   /*异常处理部分*/ 
END[procedure_name];

不带参数的存储过程

create procedure update_emp 
as
begin 
	update emp set ename='candy' where empno=7934;
end;
/

带参数(in)的存储过程

create or replace procedure update_emp2 
(emp_num in number,emp_name in varchar2)
as
begin 
	update emp set ename=emp_name  where empno=emp_num;
end;
/


--创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。
CREATE OR REPLACE PROCEDURE proc_showemp(p_deptno in number)
AS
v_sal emp.sal%TYPE;
BEGIN
    SELECT avg(sal) INTO v_sal FROM emp  WHERE deptno=p_deptno ;
    DBMS_OUTPUT.PUT_LINE(p_deptno|| 'the department average salary is: '||v_sal);
    
    FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND sal>v_sal) LOOP
    	DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);
    END LOOP;

    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    	DBMS_OUTPUT.PUT_LINE('The department doesn"t exists! ');
END ;
/

带参数(out)的存储过程

create or replace procedure select_emp (emp_num in number,emp_name out varchar2)
as
begin 
    select ename into emp_name from emp  where empno=emp_num;
    exception
    	when no_data_found  then
    	DBMS_OUTPUT.PUT_LINE('The employee doesn"t exists! ');
end;
/

带参数(in out)的存储过程

创建一个存储过程exchange_v,交换两个变量的值；
create or replace procedure exchange_v(value1 in  out number,value2 in  out number)
as
    t1 number;
    t2 number;
begin 
    t1:=value1;
    t2:=value2;
    value1:=t2;
    value2:=t1;
end;
/



调用存储过程

--exec不带参数
exec 存储名()


--exec带参数
exec 存储名(参数)


--块
DECLARE
	--参数
BEGIN
	...
    方法名([参数]);  --调用
    ...
END; 
/

--块的实例
DECLARE
	v_name emp.ename%TYPE;
BEGIN
	select_emp(7934,v_name);
	DBMS_OUTPUT.PUT_LINE(v_name);
END; 
/





9、函数(FUNCTION)

函数与存储过程相似，可以接受用户的传值，也可以返回值，而且必须有一个返回值

  • 注意：
    • 在函数定义的头部，参数列表之后，必须包含一个RETURN语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。
    • 在函数体的定义中，必须至少包含一个RETURN 语句，来指明函数返回值。也可以有多个RETURN语句，但最终只有一个RETURN语句被执行。



创建函数

CREATE [OR REPLACE] FUNCTION function_name 
(parameter1_name [mode] datatype  [DEFAULT|:=value]
[, parameter2_name [mode] datatype  [DEFAULT|:=value],…])
RETURN return_datatype 
AS|IS
    /* 变量声明部分 */
BEGIN
     /* 执行部分 */ 
EXCEPTION
      /* 异常处理部分 */ 
END [function_name];


--创建函数，get_name,通过empno返回该员工的姓名；
create or replace function  get_name (emp_num in EMP.EMPNO%TYPE)
return emp.ename%type  --返回类型
as
emp_name emp.ename%type;
begin 
    select ename into emp_name from emp  where empno=emp_num;
    return emp_name;  --返回数据
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('NO DATE! ');
end;
/



函数的调用

  • 返回一个值

    select  函数名(参数)  from dual;


--创建名为“FUNC_MAXSAL”的函数，以部门编号为参数，返回部门最高工资。
CREATE OR REPLACE FUNCTION func_maxsal(  p_deptno emp.deptno%TYPE)
	return emp.sal%TYPE  --返回类型
AS
	v_maxsal emp.sal%TYPE;
BEGIN
    SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno;
    RETURN v_maxsal;  --返回数据
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        	DBMS_OUTPUT.PUT_LINE('The deptno is invalid! ');
END func_maxsal;
/

--调用：
select  func_maxsal(20)  from dual;

  • 返回多个值，使用OUT或IN OUT模式参数

    --创建一个名为“FUNC_DEPT”的函数，以部门号为参数，返回部门名、部门人数及部门平均工资。 
CREATE OR REPLACE FUNCTION func_dept(
        p_deptno dept.deptno%TYPE,
        p_num OUT NUMBER,
        p_avgsal OUT NUMBER)
	RETURN dept.dname%TYPE
AS
	v_dname dept.dname%TYPE;
BEGIN
    SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno;
    SELECT count(*),avg(sal) INTO p_num,p_avgsal  FROM emp WHERE deptno=p_deptno;
    RETURN v_dname;
    EXCEPTION 
    	WHEN NO_DATA_FOUND THEN
    		DBMS_OUTPUT.PUT_LINE('The deptno is invalid! ');
    	WHEN others THEN
    		DBMS_OUTPUT.PUT_LINE('There are too many employees! ');
END func_dept;
/


--通过上面函数的调用，输出各个部门的最高工资, 输出各个部门名、部门人数及平均工资，最高工资。 
DECLARE
v_avgsal emp.sal%TYPE;
v_num    NUMBER;
v_dname  dept.dname%TYPE;
BEGIN
    FOR v_dept IN (SELECT DISTINCT deptno FROM  emp WHERE deptno IS NOT NULL) LOOP
        v_dname:=func_dept(v_dept.deptno,v_num, v_avgsal);               
        DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dname||' '|| v_avgsal||' '||v_num);
    END LOOP;
END;
/





10、触发器(TRIGGER)

创建DML触发器

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER |instead of }  --BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式
{INSERT | DELETE | UPDATE [OF column [, column …]]}
{ON [schema.]table_name | [schema.]view_name |database]}
[FOR EACH ROW ]  --触发器为行触发器
[enable|disable]
[WHEN condition]  --触发约束条件
[declare declaration_statements]
begin
	trigger_body{PL/SQL_BLOCK | CALL procedure_name;}
end;



语句级DML触发器

--限制对emp表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改emp表。
CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE ON emp
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '12:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改emp表');
 END IF;
END;
/


--为emp表创建一个触发器“TRG_EMP_DEPT_STAT”，当执行插入或删除,或者修改部门编号操作时，统计操作后各个部门员工人数；当执行更新工资操作时，统计更新后各个部门员工平均工资。

CREATE OR REPLACE TRIGGER trg_emp_dept_stat
AFTER  
INSERT OR DELETE OR UPDATE OF sal OR UPDATE OF deptno ON emp
BEGIN
  IF INSERTING OR DELETING  or updating('deptno') THEN 
     DBMS_OUTPUT.PUT_LINE('the trigger trg_emp_dept_stat is enabled!');
     FOR v_dept IN (SELECT deptno, count(*)  v_count   FROM emp  GROUP BY deptno  order BY deptno ) LOOP
        DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'  '||v_dept. v_count);
      END LOOP;
  ELSIF UPDATING('sal') THEN
     DBMS_OUTPUT.PUT_LINE('the trigger trg_emp_dept_stat is enabled!');
     FOR v_deptsal IN (SELECT deptno ,avg(sal)  v_sal FROM emp   GROUP BY deptno order BY deptno ) LOOP
      DBMS_OUTPUT.PUT_LINE(v_deptsal.deptno||'  '||v_deptsal.v_sal);
    END LOOP;
  END IF;
END trg_emp_dept_stat;
/



行级DML触发器

  • :old和**:new**标识符

    触发事件:old:new
    INSERT未定义，所有字段都为NULL当语句完成时，被插入的记录
    UPDATE更新前原始记录当语句完成时，更新后的记录
    DELETE记录被删除前的原始值未定义，所有字段都为NULL

  • 引用方式

    • :old.field和:new.field （执行部分）
    • old.field 和new.field (WHEN条件中)
--建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 

CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE  --指定触发时机为删除操作前触发
   DELETE or update                   
   ON  emp 
   FOR EACH ROW   --说明创建的是行级触发器                   
BEGIN
   --将修改前数据插入到日志记录表 emp_his ,以供监督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
  DBMS_OUTPUT.PUT_LINE('the trigger tr_del_emp is running!');
END;
/


--为emp表创建一个名为“TRG_EMP_DML_ROW”的触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
  IF INSERTING THEN 
     DBMS_OUTPUT.PUT_LINE(:new.deptno||'welcome '||:new.empno||' '||:new.ename||:new.sal);
  ELSIF UPDATING THEN
     DBMS_OUTPUT.PUT_LINE(:old.empno ||'the old salary is:'||:old.sal||'   the new salary is:'||:new.sal);
  ELSE
     DBMS_OUTPUT.PUT_LINE(:old.empno||' '||:old.ename||'id deleted!');
  END IF;
END trg_emp_dml_row;
/


--限定只对部门号为10的记录进行行触发器操作。
CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE 
UPDATE OF sal ,comm  OR DELETE ON emp
FOR EACH ROW
WHEN (old.deptno = 10)
BEGIN
    CASE
        WHEN UPDATING ('sal') THEN
            IF :new.sal < :old.sal THEN
            	RAISE_APPLICATION_ERROR(-20001, '部门10的人员的工资不能降');
            END IF;
        WHEN UPDATING ('comm') THEN
            IF :new.comm < :old.comm  THEN
            	RAISE_APPLICATION_ERROR(-20002, '部门10的人员的奖金不能降');
            END IF;
        WHEN DELETING THEN
        	RAISE_APPLICATION_ERROR(-20003, '不能删除部门10的人员记录');
    END CASE;
END; 
/



删除触发器

DROP TRIGGER trigger_name;



启动或禁用触发器

--数据库TRIGGER的这两种状态可以互相转换。格式为：
--DISABLE ：关闭
--ENABLE ：开启
ALTER TIGGER trigger_name [DISABLE | ENABLE ];

--例：
ALTER TRIGGER emp_view_delete DISABLE;

--ALTER TRIGGER语句一次改变一个触发器的状态，而ALTER TABLE语句一次能够改变指定表相关的所有触发器的使用状态。 
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

--例：使表EMP 上的所有TRIGGER 失效：
ALTER TABLE emp DISABLE ALL TRIGGERS;
code@fzk
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
C#实现SQL使用COMPUTE （附完整源代码）
希望我的博客，能帮上你解决学习中工作中所遇到的问题
04-15 131
C#实现SQL使用COMPUTE （附完整源代码）
oracle监控oswatch详细使用教学
01-29
oracle监控oswatch详细使用教学
oracle命令使用说明
09-01
很实用的oracle常用命令及用法，包含用户权限分配及权限设置，数据库备份还原等命令
Oracle 命令使用大全
weixin_33943836的博客
04-23 113
我的电脑是Mac，使用docker作为容器，安装的是oracle 11g。 一、用户与表空间 1、登陆与基本使用 系统级用户有sys system,其中sys比system更高级，sys必须已系统管理员身份才能登陆。 1、打开 sql-plus $ sqlplus 复制代码2、输入用户名和密码 这里我们可以选择sys和system(密码是我们自己设定的哦)。 3、切换sys用户 $ conn...
Oracle数据库的使用与基本讲解
TimeMoments的博客
02-02 810
Oracle数据库的使用 一、VMware VMware是一款功能强大的虚拟机软件，且深受广大程程序员喜爱，但是它是收费的 PS: 与之相似的软件virtualBox（免费的，但功能一般） 二、Oracle 1.Oracle的安装 ​ Oracle数据库的安装步骤 2.Oracle的简介 是Oracle公司出产测一款关系型数据库，且性能全球排名第一，且适应于大型商业项目，且是按点收费的，且默认的端口号是1521，且是支持pl/sql数据库脚本编程的 PS：Oracle分为客户端与服务端
Oracle 函数收集及使用详情
逸轩
07-08 234
substr 函数：截取字符串 语法：SUBSTR(string，start，[length]) string：表示源字符串，即要截取的字符串。 start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。 length：可选项，表示截取字符串长度。 实例： SELECT SUBSTR('Hello SQL!', 1) FROM demo --截取所有字符串，返回'Hello SQL!' SELECT SUBSTR('Hello...
oracle使用手册
yulungggg的博客
03-01 1029
转自 http://www.cnblogs.com/zfc2201/archive/2011/08/17/2143436.html 3个默认的帐户：         sys          change_on_install      网络管理员       [as sysdba]         system       manage            本地管理员      
oracle安装详细教程
03-01
Oracle 安装详细教程 Oracle 是一种关系型数据库管理系统，广泛应用于企业级数据存储和管理中。本资源摘要信息将详细介绍 Oracle 安装的详细教程，包括 Linux 环境配置准备、Oracle 环境配置步骤等。 一、Linux ...
存储过程_oracle详细使用手册(带图).doc
09-28
存储过程_oracle详细使用手册(带图).doc
oracle存储过程超详细使用手册
05-28
oracle存储过程超详细使用手册，内容很详细，绝对受用
oracle安装详细教程.md
最新发布
04-02
oracle
Toad for oracle 12 使用说明书
05-08
Toad for oracle 12 使用说明书,安装操作帮助文档。精简介绍如何toad功能如何安装操作使用。
oracle操作手册
10-27
1． 正常关闭 SVRMGR>shutdown normal; 这种方式下关闭数据库在关闭前检查所有的连接，并且发出命令后不允许再有新的连接，在等所有用户断开连接后关闭数据库。在此方式下关闭的数据库下次启动时不需要任何恢复过程。 2． 立即关闭 SVRMGR>shutdown immediate; 这种方式下关闭数据库并不等待用户断开连接，而是由系统断开与用户的连接，然后关闭数据库。 3． 异常关闭 SVRMGR>shutdown abort; 这种方式下关闭数据库系统不做任何的检查与回退操作而直接将数据库现场撤消。
Oracle常用操作(项目中积累的经验)
04-06
Oracle常用的但是又容易犯错的，都是在开发的过程中遇到问题积累的经验，希望对大家有用
Oracle连接和使用.pdf
09-06
本文详细介绍和阐述了 Oracle的连接和使用方法，并对连接和使用过程中的注意事项、关键知识点等进行了重点标注和详尽解析，以便于读者进行深入学习和理解。本文在作者测试通过的基础上，采用图文相结合的格式和方式...
Oracle安装详细步骤
11-21
Oracle安装详细步骤
数据库：Oracle使用指南
金融科技和数据治理
05-24 492
我们都知道Oracle是传统数据库提供商，有很强的数据库方面技术，同时又收购了SUN下面的Java，MySQL数据库等，虽然主流的数据库厂商基本上都是支持SQL标准的，但是各自数据库厂商又添加了各自自己的特性，使得在实际使用过程中还是存在较大差异的，我们今天主要讲解下Oracle在日常使用中遇到的问题。 日期。有时候我们需要数据库能够根据系统时间自动获取年、月、日等信息，这个需求还是很普遍的。 ...
oracle使用操作手册,Oracle入门操作指南
weixin_39524959的博客
04-03 424
1.System.Data.OracleClient 和 System.Data.OleDb 命名空间虽然通过这两个命名空间的类都可以访问 Oracle 数据库，但和 SQL Server 类似的(System.Data.SqlClient 命名空间的类效率要比 System.Data.OleDb 命名空间中的类高一些)，System.Data.OracleClient 命名空间中的类要比 Sys...
Oracle数据库
云别
11-06 220
Oracle数据库 Oracle的体系 1. 数据库 1. Oracle 数据库是数据的物理存储。这就包括（数据文件ORA或者 DBF、控制文件、联机日 志、参数文件）。其实Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统 只有一个库。可以看作是Oracle 就只有一个大数据库。 2. 实例 1. 一个 Oracle 实例（Orac...
Oracle使用培训.ppt
11-12
此外，对于数据库备份和还原的内容，文档详细介绍了在Oracle数据库中如何进行数据备份和还原操作，包括使用RMAN工具进行备份和还原、备份策略的制定和优化等方面的知识，帮助用户了解数据库备份和还原的基本原理和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值