Oracle官方手册：https://www.oracle.com/technetwork/cn/indexes/documentation/index.html
Oracle异常列表：https://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm
* 常用预定义异常：https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#i7014
-- rownum 是从1开始的 所以 分页是 >(当前页-1)*每页显示的数量 ，<= 当前页*每页显示数量
MySQL中分页是从0开始的 所以 分页是 >= (当前页-1)*每页显示的数量， < 当前页*每页显示数量
创建用户: create user "用户名" identified by "密码";
e.g:
create user tom identified by tom;
--新建一个用户“tom”，密码为“tom”
删除用户: drop user tom[cascade（级联删除用户所有相关的对象）]
e.g:
drop user tom cascade;
修改用户: --修改密码
alert user tom identified by "new password"
--锁定
alter user tom account lock;
--解锁
alter user tom account unlock;
用户授权:
grant "权限" to "用户名";
--常用权限：create table（建表）、create view（建视图）、create public synonym（建同义词）、create procedure（建过程、函数、包）、create trigger（建触发器）、insert/delete/update/select
e.g:
grant connect，resource to tom; -- 授予tom用户操作及登录oracle的权限
grant select on t_user to tom; -- 授予tom用户查询t_user表的权限
grant all privileges to tom; --授予tom用户所有权限
撤销权限:
revoke "权限" from "用户名";
e.g:
revoke connect from tom;--撤消tom用户登录oracle的权限
sqlplus 常用命令：
# 不登录到数据库服务器
sqlplus /nolog
# 不需要listener进程
sqlplus / as sysdba
# 登录数据库
connect username/password
# 显示当前登录用户
show user
# 查看当前用户的角色
select * from user_role_privs;
# 查找用户下的所有表
select * from tab;
# 查看系统拥有哪些用户
select * from all_users;
创建表空间
CREATE TABLESPACE 表空间名
DATAFILE '数据文件路径'
SIZE (表空间大小，单位为K或者M)
[AUTOEXTEND ON](是否自动扩展) ;
e.g:
create tablespace tom_space
datafile 'C:\oracle\tom_space.dbf'
size 1000M
autoextend off;
查看表空间
dba_tablespaces --表空间所存的表
dba_data_files --数据文件存放的表
DBA_TEMP_FILES --临时文件信息
修改表空间
-- 修改表空间状态，联机、脱机(脱机状态下表空间不可用)
alter tablespace tablespace_name online|offline;
-- 设置只读或可读写的状态(只有在联机状态才可以更改,默认的联机状态就是读写状态)
alter tablespace tablespace_name read only | read write;
-- 增加数据文件
alter tablespace tablespace_name add datafile ‘xx.dbf’ size xx;
删除表空间
-- 从数据字典中删除表空间
-- including contents 数据文件一起删除
drop tablespace tablespace_name[including contents];
创建用户指定表空间
create user 用户名 identified by 密码 default tablespace 表空间表
e.g:
create user test_17h1 identified by test_17h1 default tablespace tom_space;
-- 为用户添加表空间
alter user tom default tablespace tom_space;
创建表
CREATE TABLE [schema.]table_name
(
column_name datatype [DEFAULT expr][,…]
);
e.g:
create table t_user(
id integer primary key,
name varchar2(100) not null,
sex varchar2(1) not null
);
-- 根据查询结果创建表
CREATE TABLE table_name
[(column1,column2…)]
AS subquery;
e.g:
create table t_user as select * from table_name;
修改表
--添加一个字段
alter table t_user add (age number(2));
--修改一个字段的长度
alter table t_user modify (name varchar2(50));
--删除一个字段
alter table t_user drop column age;
--修改表的名字
rename t_user to t_u;
删除表
drop table table_name
索引
--unique表示唯一索引,bitmap，创建位图索引
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASC|DESC],…] | [express])[TABLESPACE tablespace_name]--指定索引在数据块中空闲空间
[PCTFREE n1]
[STORAGE (INITIAL n2)]--表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLOGGING]
[NOLINE]--表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
[NOSORT];
e.g:
create index user_name_index on t_user(name)--复合索引
create index user_name_index on t_user(name,sex)
--修改索引
alter index user_name_index rename to uname_index;--合并索引
alter index user_name_index coalesce;--重建索引
alter index user_name_index rebuild;--删除索引
drop index user_name_index;--查看索引
select * from all_indexes;--分析
alter index index_name validate structure;--查询碎片，如果pct_used的值过低，说明存在碎片，可重建索引
select name,pct_used from index_stats where name='index_name';
B树索引 （默认索引，保存排序过的索引列和对应的rowid值）
B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
* 能够适应精确查询、模糊查询和比较查询
* 列基数（列不重复值的个数）大时适合使用B数索引
位图索引
* 创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行 使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索 引中的映射函数完成位到行的ROWID的转换
* 对于基数小的列适合简历位图索引
反向键索引
反向键索引反转索引列键值的每个字节，为了实现索引的均匀分配，避免b树不平衡通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上 创建索引时使用REVERSE关键字,适用于某列值前面相同，后几位不同的情况
create index index_name on table_name (column_name) reverse;某一列的值： 1001 1002 1003 1004 1005 1006 1007
索引转化： 1001 2001 3001 4001 5001 6001 7001
函数索引
* 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
create index upp_name_index on t_user (upper(name));
建索引的原则：
* 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
* 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
* 小表不要建立索引
* 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
* 列中有很多空值，但经常查询该列上非空记录时应该建立索引
* 经常进行连接查询的列应该创建索引
* 使用create index时要将最常查询的列放在最前面
* LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
* 限制表中索引的数量
不会使用索引的查询：
* 使用通配符，如：where name like "%abc%"
* 不要在索引列上使用not、<>,使用其它方式代替，如：age <> 100，改成 age > 100 and age < 100
* 索引上使用空值比较将停止使用索引,如：name is null
* 索引列上不要使用函数，如：where upper(name) = “aa”
约束：约束是在表上强制执行的数据校验规则，主要用于保证数据库的完整性
主键约束（primary key） 主键约束 相当于 UNIQUE+NOT NULL
alter table table_name add constraint pk_name primary key(column_name);
唯一性约束（unique） 唯一性约束保证每一行的唯一性
alter table table_name add constraint uni_name unique(column_name);
非空约束（Not null）
alter table table_name modify column_name not null;
外键约束（foreign key） * 外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性
-- on delete cascade 可级联删除
alter table table_name add constraint fk_name foreign key(column_name) references table_name (cloum_name) on delete cascade;-- 将对应子表中的所有匹配行的外键约束列置为NULL
alter table table_name add constraint fk_name foreign key(column_name) references table_name (cloum_name) on delete set null;
检查约束（check）
alter table table_name add constraint ck_name check(sex in('男','女'));
约束创建
-- 创建表时添加约束
create table T_USER
(
ID INTEGER not null,
NAME VARCHAR2(100) not null,
SEX VARCHAR2(1) not null,
clsid NUMBER,
CONSTRAINT pk_uid PRIMARY KEY(ID),
CONSTRAINT uq_uName UNIQUE(NAME),
CONSTRAINT fk_clsid FOREIGN KEY (clsid) REFERENCES tb_class(clsid)
);
--建表之后添加约束
alter table table_name add constraint ck_name check(sex in('男','女'));
alter table table_name add constraint uni_name unique(column_name);
--删除约束
ALTER TABLE table DROP constraint_name;
--查看约束
select * from USER_CONSTRAINTS;
select * from USER_CONS_COLUMNS;
SQL语言共分为四大类:
* 数据查询语言 DQL (Data query language)
* 数据操纵语言 DML (Data Manipulation Language)
* 数据定义语言 DDL (Data Definition Language)
* 数据控制语句 DCL (Data Control Language)
DML
insert
INSERT INTO 表（列1 ，列2 …） VALUES （表达式1 ，表达式2 .. ) ;
INSERT INTO 表1 （列1 ，列2 …） SELECT 列1 ，列2 ••• FROM 表2 WHERE 条件；
delete
DELETE FROM 表名称 WHERE 条件;
update
UPDATE 表名称 SET 列名称=表达式1, 列名称=表达式2 WHERE 条件;
UPDATE 表名称 SET (列1，列2)=（值1、值2） WHERE 条件;
DDL
create
create index upp_name_index on t_user (upper(name));
CREATE TABLE [schema.]table_name...
drop
drop index user_name_index;
drop table table_name
alter
alter table t_user add (age number(2));
alter index user_name_index rebuild;
DCL
grant
grant create table to user;
CREATE SESSION 连接到数据库上
CREATE SEQUENCE 创建序列，序列是一系列数字，通常用来自动填充主键列
CREATE SYNONYM 创建同名对象
CREATE TABLE 创建表
CREATE ANY TABLE 在任何模式中创建表
DROP TABLE 删除表
DROP ANY TABLE 删除任何模式中的表
CREATE PROCEDURE 创建存储过程
EXECUTE ANY PROCEDURE 执行任何模式中的存储过程
CREATE USER 创建用户
DROP USER 删除用户
CREATE VIEW 创建视
revoke
revoke create table from user;
DQL
select
-- 基本语法
SELECT{*, column [alias],...} FROM table;
-- 字符串的连接操作符
SELECT last_name || ‘work at ’|| job_id FROM employees;
-- 取别名
select last_name as "姓名" from employees;
-- DISTINCT
SELECT DISTINCT department_id FROM employees;
-- 限制条件
SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHERE condition];
-- BETWEEN and
SELECT last_name, salary FROM employees WHERE salary BETWEEN 1000 AND 1500;
-- IN
SELECT employee_id,last_name FROM employees WHERE manager_id IN (7902, 7566, 7788);
-- LIKE:(%)可表示零或多个字符,( _ ) 可表示一个字符
SELECT last_name FROM employees WHERE last_name LIKE '_A%';
-- IS NULL
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
逻辑运算符：
* AND:如果组合条件都是TRUE,结果为TRUE
* OR:如果组合条件一边是TRUE，结果为TRUE
* NOT:如果条件为FALSE,结果为TRUE
排序：order by 字段 asc(升序)/desc(降序)
聚合函数
* COUNT(*|列名)：统计行数
* AVG(数值型列名）：平均值
* SUM(数值型列名）：求和
* MAX（列名）：最大值
* MIN（列名）：最小值
分组函数 group by
SELECT column, group_function FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];
e.g:
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno;
* HAVING子句
* HAVING子句用来对分组后的结果再进行条件过滤
* where和having都是用来做条件限定的,但是having只能用在group by之后
* WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数
SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
e.g:
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno having AVG(sal)>100;
多表联查
笛卡尔积：* CROSS JOIN：CROSS JOIN产生了一个笛卡尔积（Cartesian product），就象是在连接两个表格时忘记加入一个WHERE子句一样
SELECT ename,loc FROM emp CROSS JOIN dept;
-- 等价于
SELECT ename,loc FROM emp，dept;
内联接： inner join .. on..
外联接：
左外联接：
* left outer join 或者 left join
* 左外连接就是在等值连接的基础上加上主表中的未匹配数据
select * from t_user t left outer join t_dept d on t.dept_id = d.id;
-- 等价于
select * from t_user t , t_dept d where t.dept_id = d.id(+);-- + 号在右边, 以左边为准, 右边补齐
右外联接：
* right outer join 或者 right join
* 右外连接是在等值连接的基础上加上被连接表的不匹配数据
-- + 号在左边, 以右边为准, 左边补齐
select * from t_user t , t_dept d where t.dept_id(+) = d.id;
-- 等价于
select * from t_user t right join t_dept d on t.dept_id = d.id;
全外连接：
* full outer join 或者 full join
* 全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上
select * from t_user t full join t_dept d on t.dept_id = d.id;
关于使用（+）的一些注意事项
* （+）操作符只能出现在where子句中，并且不能与outer join语法同时使用
* 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符
* （+）操作符只适用于列，而不能用在表达式上
* （+）操作符不能与or和in操作符一起使用
* （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接
* UNION
* UNION 指令的目的是将两个 SQL 语句的结果合并起来
* 两个 SQL 语句所产生的栏位需要是同样的（两段sql所查询的列必须一致）
select * from t_user t left join t_dept d on t.dept_id = d.id
union
select * from t_user t right join t_dept d on t.dept_id = d.id
* UNION ALL
* UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复
select * from t_user t left join t_dept d on t.dept_id = d.id
union all
select * from t_user t right join t_dept d on t.dept_id = d.id
* INTERSECT
* 与union相反，取两段sql的交集内容
select * from t_user t left join t_dept d on t.dept_id = d.id
INTERSECT
select * from t_user t right join t_dept d on t.dept_id = d.id
子查询(嵌套查询)
* 所谓子查询，即一个select语句中嵌套了另外的一个或者多个select语句,嵌套查询先执行，然后将结果传递给主查询
* 单行子查询:向外部返回的结果为空或者返回一行
select * from t_user t where t.dept_id =(select d.id from t_dept d where d.name='17H1')
* 多行子查询:向外部返回的结果为空、一行、或者多行
* IN:与列表中的任一成员相等
* ANY:与子查询返回的每一个值比较
* ALL:与子查询返回的所有值比较
使用 ANY 和 ALL 时 在 ANY/ALL 前面需要添加运算符（>,<,=）
select * from t_user t where t.dept_id in (select d.id from t_dept d where d.name='17H1')
MySQL中为 inset into values(),(),(); //Oracle 不支持
Oracle中为 批量增加：insert all into t_user values(1,'张三','男',20) into t_user values(2,'李四','女',22) into t_user values(3,'王五','男',30) into t_user values(4,'赵四','男',24) select 1 from dual;
看执行计划
在PL/SQL 中选中语句 按 F5
在 sqlplus 中
set time on; (说明：打开时间显示）（可选）
set autotrace on; (说明：打开自动分析统计，并显示SQL语句的运行结果）
set autotrace traceonly; (说明：打开自动分析统计，不显示SQL语句的运行结果）
set time off; (说明：关闭时间显示）
set autotrace off;
Oracle-PLSQL
PL/SQL是 Procedure Language & Structured Query Language 的缩写,PL/SQL是对SQL语言存储过程语言的扩展，它是一种过程处理语言。
PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分
* 无名块或匿名块（anonymous）
* 动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用
* 命名块（named）
* 是带有名称的匿名块，这个名称就是标签
* 子程序（subprogram）
* 存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们
* 触发器（Trigger）
* 当数据库发生操作时，会触发一些事件，从而自动执行相应的程序
* 程序包/包（package）
* 存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程
DECLARE --声明部分
声明语句
BEGIN --执行部分
执行语句
EXCEPTION --异常处理部分
执行语句
END;
声明语句：
变量声明：
--普通变量
v_变量名 数据类型 := 值;-- := 表示赋值
--特殊类型======字段%type
v_name t_user.name%type --表示name的类型和t_user.ename的类型相同
--赋值
select t.name into v_name from t_user t where rownum = 1
--表类型===========%rowtype
v_user t_user%rowtype --表示v_user的类型为t_user表的行类型;也有 .name; .sex; .age ;等属性
--赋值
select t.* into v_user from t_user t where rownum = 1
--常量声明
<变量名> CONSTANT 类型:=初始值;
C_query_size constant number(3):=100;
Type 定义
declare
TYPE type_user IS RECORD(
user_id t_user.id%type,
user_name t_user.name%type,
dept_name t_dept.name%type
);-- 自定义一个type:type_user
v_user type_user;--定义一个type_user类型的变量
begin
-- 为v_user变量赋值
select u.id,u.name,d.name into v_user from t_user u,t_dept d where u.dept_id = d.id;
dbms_output.put_line(v_user.user_id);
dbms_output.put_line(v_user.user_name);
dbms_output.put_line(v_user.dept_name);
end;
全局变量声明：
VARIABLE <变量名> 类型;
e.g:
VARIABLE num number;
--使用全局变量
:<变量名>
e.g:
:num:=100;
用户交互输入：
v_name := '&name';
e.g:
declare
v_name varchar2(100);
begin
v_name := '&name';
dbms_output.put_line(v_name);
end;
条件控制语句:
IF <条件1> THEN
语句
[
ELSIF <条件n> THEN 注意是ELSIF
语句
]
[
ELSE
语句
]
END IF;
流程控制语句：
CASE variable
WHEN value1 THEN statements1;
WHEN value2 THEN statements2;
……
WHEN valuen THEN statementsn;
[ELSE else_statements;]
END CASE;
循环控制语句：
LOOP
语句;
EXIT WHEN <条件>
END LOOP;
--
WHILE <条件>
LOOP
语句;
END LOOP;
--
FOR <循环变量> IN 下限..上限
LOOP
语句;
END LOOP;
异常处理：
EXCEPTION
WHEN <异常类型> THEN
语句;
WHEN OTHERS THEN
语句;
END;
e.g:
declare
E_self exception; -- 自定义异常
begin
EXECUTE IMMEDIATE 'select 123 from dual where :1 = :2' using 2,2;
RAISE E_self; -- 抛出异常
exception when E_self then -- 捕获异常
dbms_output.put_line(SQLCODE||':'||SQLERRM);--SQLCODE：异常码，SQLERRM：信息
end;
* oracle异常提示中英文切换
alter session set nls_language='american';
alter session set NLS_LANGUAGE='SIMPLIFIED CHINESE';
非预定异常：
declare
v_excep Exception;
--将定义的异常v_excep与异常编码-20088绑定在一起
PRAGMA EXCEPTION_INIT(v_excep, -20088);
begin
-- RAISE_APPLICATION_ERROR函数，重置异常信息
-- RAISE_APPLICATION_ERROR（异常编码，异常信息），异常编码：-20000~-20999
RAISE_APPLICATION_ERROR(-20088, 'test_error');
exception
when v_excep THEN
DBMS_OUTPUT.put_line(SQLCODE||':'||SQLERRM);
end;
常用预定义异常：
timeout_on_resource Oracle在等待资源时发生超时的现象
access_into_null 视图给一个没有初始化的对象赋值
case_not_found 在CASE语句中没有WHEN子句被选择，并且没有ELSE子句
invalid_number 视图将一个非有效的字符串转换成数字
loggin_denied 使用无效的用户名和口令登录Oracle
no_data_found 查询语句无返回数据，或者引用了一个被删除的元素，或者引用了一个没有被初始化的元素
......
动态SQL
--dynamic_sql：表示一个SQL语句或者一个PL/SQL语句块的字符串表达式
EXCEUTE IMMEDIATE dynamic_sql;e.g:
declare
begin
EXECUTE IMMEDIATE 'select 123 from dual where :1 = :2' using 2,2;
end;
-- ：1，：2 占位符，使用using关键字传参
事务
* 原子性（Atomicity）
* 事务是一个完整的操作，事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
* 一致性（Consistency）
* 当事务完成时，数据必须处于一致状态
* 隔离性（Isolation）
* 并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
* 持久性（Durability）
* 事务完成后，它对数据库的修改被永久保持
-- 设置事务的隔离级别
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED|READ COMMITTED
|REPEATABLE READ|SERIALIZABLE]
例：
Set transaction read only
Set transaction read write
Set transaction isolation level read committed
Set transaction isolation level serializable
开启 Oracle 事务：
-- 开启事务
SAVEPOINT a; --设置事务存储点
--提交事务
COMMIT
--回滚部分事务
ROLLBACK to a
--回滚所有事务
ROLLBACK
e.g:
declare
E_self exception;begin
savepoint a;
EXECUTE IMMEDIATE 'select 123 from dual where :1 = :2' using 2,2;
RAISE E_self;
commit;
exception
when E_self then
dbms_output.put_line(SQLCODE || ':' || SQLERRM);
rollback to a;
end;
数据异常
1. 脏读
* 当一个事务修改数据时，另一事务读取了该数据，但是第一个事务由于某种原因取消对数据修改，使数据返回了原状态，这是第二个事务读取的数据与数据库中数据不一致
2. 不可重复读
* 是指一个事务读取数据库中的数据后，另一个事务则更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生了改变，这就是不可重复读取。不可重复读取所导致的结果就是一个事务前后两次读取的数据不相同
3. 幻读
* 如果一个事务基于某个条件读取数据后，另一个事务则更新了同一个表中的数据，这时第一个事务再次读取数据时，根据搜索的条件返回了不同的行，这就是幻读
锁：
多用户数据库使用某种形式的数据锁定来解决与数据并发性，一致性和完整性相关的问题。锁是防止访问相同资源的事务之间的破坏性交互的机制
排它锁（Exclusive Locks，即X锁）
* 当数据对象被加上排它锁时，其他的事务不能对它修改
lock table table_name in exclusive mode;
共享锁（Share Locks，即S锁）
* 数据对象可以被其他事务读取，但不能修改
lock table table_name in share mode;
行级共享锁（RS锁）
* 通过select … from for update语句添加
行级排他锁（RX锁）
* DML操作时会自动在被更新的表上添加RX锁
LOCK table table_name IN
[row exclusive] MODE
[NOWAIT];
共享行级排他锁（SRX锁）
* 该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁
--命令添加SRX锁
lock table table_name in share row exclusive mode
Oracle数据库锁细分为
* DML lock（data locks，数据锁）：用于保护数据的完整性
* 包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁
* DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）
* Internal locks 和latches（内部锁与闩）：保护内部数据库结构
* Distributed locks（分布式锁）：用于OPS（并行服务器）中
* PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中
查看死锁与解锁
* 查看死锁
SELECT * FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1)
* 查看导致死锁的sql
SELECT s.sid, q.sql_text FROM v$sqltext q, v$session s
WHERE q.address = s.sql_address
AND s.sid in (SELECT sid FROM v$lock)
ORDER BY piece;
* kill session
-- 执行拼的sql，杀死session
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
Oracle数据库分页的三种方法
-- 不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
-- 所以直接用只能从1开始
-- rownum >10 没有记录，因为第一条不满足去掉的话，第二条的rownum又成了1，所以永远没有满足条件的记录。
select * from student where rownum>=1;
--如果想要用rownum不从1开始，需按下面方法使用
select a1.* from (select student.*,rownum rn from student) a1 where rn >5;
--分页查询一
select * from (select a1.*,rownum rn from (select * from student) a1 where rownum <=5) where rn>=2;
--分页查询二
select a1.* from (select student.*,rownum rn from student where rownum <=5) a1 where rn >=3;
--分页查询三
select a1.* from (select student.*,rownum rn from student) a1 where rn between 3 and 5;
| -- 实现分页 declare i_page_size integer := '&每页显示数量'; i_page_current integer := '&当前页'; begin IF i_page_size is not null and i_page_current is not null THEN -- dbms_output.put_line('每页显示数量：' || i_page_size || ' 起始页数：' || i_pages); IF i_page_size >0 and i_page_current >0 THEN dbms_output.put_line('每页显示数量：' || i_page_size || ' 当前页：' || i_page_current ); -- select * from (select t.* ,rownum rm from t_test t) tab where tab.rm >(i_page_current-1)*i_page_size and tab.rm <= i_page_size*i_page_current; for i in (select * from (select t.* ,rownum rm from t_test t) tab where tab.rm > (i_page_current-1)* i_page_size and tab.rm <= i_page_size*i_page_current) loop dbms_output.put_line(i.id || ' '||i.name); end loop; ELSE dbms_output.put_line('请输入合法数据'); END IF; ELSE dbms_output.put_line('请输入数据'); END IF; end; -- rownum 是从1开始的 所以 分页是 >(当前页-1)*每页显示的数量 ，<= 当前页*每页显示数量 |
Oracle 的数据对象 包括：table 表、view 视图、sequence 序列、index 索引、synonym 同义词
View 视图： 是一种 虚表。视图建立在已有表的基础上，视图赖以建立的这些表称为 基表。
特点： 经常查询操作形成的结果，具有普通表的结构，不能实现数据的存储，对视图的修改将会影响实际的数据表，
对单表的视图进行 CURD 操作将会影响到基表的数据，对多表关联的视图进行 CURD 操作会报错。
语法：
create [or replace] [force|noforce] View view [(alias[,alias]..)] as subquery
[with check option [constraint constraint]]
[with read only [constraint constraint]];
force : 子查询不一定存在
noforce：子查询存在（默认）
check option: 必须满足的视图的约束
with read only:只能做查询操作
例如：
create or replace force view my_view as select t.id,t.name from t_test t where t.id=100;
删除视图：
drop view 视图名；
-- 同义词
create public synonym t_d_Class for Darryl_Tang.t_Class;
-- 表示使用 t_d_Class 就相当于使用 Darryl_Tang.t_Class;
select * from t_d_Class; <==> select * from Darryl_Tang.t_Class;
--查询所有同义词
select * from dba_synonyms;
--删除同义词
drop synonym t_d_Class;
select sys_guid() from dual; -- 自动生成一个32位的ID
-- sequence 序列
-- 序列是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值（类型为数字）
--创建序列：
create sequence 序列名
[increment by 步长] -- 序列的步长
[start with 开始值] -- 开始值
[{maxvalue 最大值| nomaxvalue}] --最大值，nomaxvalue 默认选项，无最大值，10的27次方
[{minvalue 最小值| nominvalue}] --最小值，nominvalue 默认选项
[{cycle|nocycle}] -- 序列达到限制值时，是否循环，cycle 代表循环
[{cache 缓存大小|nocache}] -- 对序列进行内存缓存存放，n 表示内存缓存空间大小
create sequence user_seq
increment by 1
start with 1
cycle
cache 20;
sequence_name.nextval; --序列的下一个值
sequence_name.currval; --序列当前值
select user_seq.nextval from dual;
--修改序列
alter sequence seq_name
[increment by 步长]
[{maxvalue 最大值| nomaxvalue}]
[{minvalue 最小值| nominvalue}]
[{cycle|nocycle}]
[{cache 缓存大小|nocache}]
--删除序列
drop sequence seq_name;
drop sequence user_seq;
| -- Oracle 的数据对象包括：table 表、view 视图、sequence 序列、Index 索引、synonym 同义词 -- View 视图 是一种 虚表。视图建立在已有表的基础上，视图赖以建立的这些表称为 基表。 -- 特点：经过查询操作形成的结果，具有普通表的结构，不能实现数据的存储，对视图的修改将会影响实际的数据表 -- 对单表的 CURD操作将会影响到基表的数据， 对多表关联的 CURD操作不会影响到基表的数据 /*create [or replace] [force|noforce] View view [(alias[,alias]..)] as subquery [with check option [constraint constraint]] [with read only [constraint constraint]]; force : 子查询不一定存在 noforce：子查询存在（默认） check option: 必须满足的视图的约束 with read only:只能做查询操作*/ create or replace force view test_view as select t.id,t.name from t_test t; select * from t_test; --查询视图 select * from test_view; -- 删除视图 drop view test_view; insert into t_test values(100,'李四'); insert into t_test values(100,'王伟'); insert into t_test values(100,'成立'); create or replace force view my_view as select t.id,t.name from t_test t where t.id=100; select * from my_view; drop view my_view; -- 增加 会增加进基表 insert into my_view values (5,'视图'); -- 删除视图中的数据 会将基表中的数据也删除 delete from my_view where name = 'ttt10'; -- 修改视图中数据 会将基表中的数据也修改 update my_view set id=101 where name = '成立'; select * from tab; select * from t_user; insert all into t_user values(100,'用户','M',20) into t_user values (101,'测试','F',20) select 1 from dual; create or replace force view test_user_view as select t.id,t.name,u.id u_id,u.name u_name,u.sex,u.age from t_test t,t_user u where t.id = 100 and u.id = 100; select * from test_user_view; drop view test_user_view; -- update test_user_view v set v.name='删除',v.u_name = '删除' where v.id = 100; -- 同义词 create public synonym t_d_Class for Darryl_Tang.t_Class; -- 表示使用 t_d_Class 就相当于使用 Darryl_Tang.t_Class; select * from t_d_Class; <==> select * from Darryl_Tang.t_Class; --查询所有同义词 select * from dba_synonyms; --删除同义词 drop synonym t_d_Class; select sys_guid() from dual; -- 自动生成一个32位的ID -- sequence 序列 -- 序列是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值（类型为数字） --创建序列： create sequence 序列名 [increment by 步长] -- 序列的步长 [start with 开始值] -- 开始值 [{maxvalue 最大值| nomaxvalue}] --最大值，nomaxvalue 默认选项，无最大值，10的27次方 [{minvalue 最小值| nominvalue}] --最小值，nominvalue 默认选项 [{cycle|nocycle}] -- 序列达到限制值时，是否循环，cycle 代表循环 [{cache 缓存大小|nocache}] -- 对序列进行内存缓存存放，n 表示内存缓存空间大小 create sequence user_seq increment by 1 start with 1 maxvalue 999999999 minvalue 1 cycle cache 20; sequence_name.nextval; --序列的下一个值 sequence_name.currval; --序列当前值 select user_seq.nextval from dual; --修改序列 alter sequence seq_name [increment by 步长] [{maxvalue 最大值| nomaxvalue}] [{minvalue 最小值| nominvalue}] [{cycle|nocycle}] [{cache 缓存大小|nocache}] --删除序列 drop sequence seq_name; drop sequence user_seq; select * from tab; select * from t_student; create table t_tang ( id integer primary key, name varchar2(100) ); insert into t_tang values(user_seq.nextval,'李四'); select * from t_tang; |
单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果
聚合函数(多行函数)：聚合函数同时可以对多行数据进行操作，并返回一个结果
单行函数
字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式
ASCII(X)：返回字符X的ASCII码
CONCAT(X,Y)：连接字符串X和Y
INSTR(X,STR[,START][,N)：从X中查找str，可以指定从start开始，也可以指定从n开始
LENGTH(X)：返回X的长度
LOWER(X)：X转换成小写
UPPER(X)：X转换成大写
SUBSTR(X,start[,length])：返回X的字串，从start开始，截取length个字符，缺省length默认到结尾
REPLACE(X,old,new)：在X中查找old，并替换成new
TRIM([TRIM_STR FROM]X)：把X的两边截去trim_str字符串，缺省截去空格
数字函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式
ABS(X)：X的绝对值
ACOS(X)：X的反余弦
COS(X)：余弦
CEIL(X)：大于或等于X的最小值
FLOOR(X)：小于或等于X的最大值
LOG(X,Y)：X为底Y的对数
MOD(X,Y)：X除以Y的余数
POWER(X,Y)：X的Y次幂
ROUND(X[,Y])：X在第Y位四舍五入
TRUNC(X[,Y])：X在第Y位截断
转换函数
转换函数将值从一种数据类型转换为另外一种数据类型
TO_CHAR(d|n[,fmt])：把日期和数字转换为制定格式的字符串。Fmt是格式化字符串
TO_DATE(X,[,fmt])：把一个字符串以fmt格式转换成一个日期类型
TO_NUMBER(X,[,fmt])
日期函数
日期函数对日期进行运算。常用的日期函数有
ADD_MONTHS(d,n)：在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期
LAST_DAY(d)：返回指定日期当月的最后一天
EXTRACT(fmt FROM d)：提取日期中的特定部分，fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND
ROUND(d[,fmt])：返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天，fmt可以为：YEAR、MONTH、DAY
其它函数
NVL(X,VALUE)：如果X为空，返回value，否则返回X
NVL2(x,value1,value2)：如果x非空，返回value1，否则返回value2
DECODE
--把base_expr与后面的每个compare 进行比较,如果匹配返回相应的value.如果没有发生匹配,则返回default
DECODE(base_expr,comparel,valuel,
Compare2,value2,
…
default)
GREATEST
-- 回一组表达式中的最大值,即比较字符的编码大小.
GREATEST(expr1[,expr2]…)
e.g:
select greatest(’AA’,’AB’,’AC’) from dual;
LEAST
--获得参数中最小的表达式
LEAST(expr1[,:expr2]…)
e.g:
select LEAST(’AA’,’AB’,’AC’) from dual;
CASE
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
聚合函数
聚合函数同时对一组数据进行操作返回一行结果，比如计算一组数据的总和，平均值
AVG：平均值
SUM：求合
MIN、MAX：最小，最大
COUNT：统计
自定义函数
PL/SQL定义的DECLARE匿名块，不能传递参数，只能执行一次，没有名称，也不被存储在数据库中，还不能其它应用程序调用。所以就有了比匿名块功能更强大的函数，函数也可以看做是一个匿名块，它是PL/SQL的子程序，它有自己的名字，可以传递参数，并且绝对会返回一个值给调用方
创建函数
create [or replace] function [schema.]function_name
(函数参数列表) --参数有IN、OUT、IN OUT三种类型；IN代表需要输入的参数，OUT代表要返回的参数，IN OUT代表即是输入参数也是返回参数。
return datetype--返回类型
[is | as ]---任选一个，后面跟pLSQL代码块
begin
--执行的SQL
end;
e.g:
create or replace function f_count_user(
i_name in varchar2,--in 入参
i_sex out varchar2 --out 出参，in out :即可做入参又可做出参
)
return number
is
v_count number;
begin
if i_name is not null and i_sex is not null then
select count(1) into v_count from t_user where name = i_name and sex = i_sex;
end if;
i_sex:='bbbb';
return v_count;
end f_count_user;
查询函数信息
--用户拥有的触发器、存储过程、函数查询表
select * from User_Procedures;
--查询用户拥有触发器、存储过程、函数的源码
select * from User_Source;
游标(CURSOR)
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。
隐式游标
* DML操作会使用隐式游标
* SELECT ... INTO ...会使用隐式游标
* 隐式游标的属性
* SQL%ROWCOUNT：代表DML语句成功执行的数据行数
* SQL%FOUND：值为TRUE代表插入、删除、更新或单行查询操作成功
* SQL%NOTFOUND：与SQL%FOUND属性返回值相反
* SQL%ISOPEN：DML执行过程中为真，结束后为假
declare
begin
update t_user set name='ccc' where id = 2;
IF SQL%FOUND THEN
dbms_output.put_line('success');
commit;
else
dbms_output.put_line('error');
end if;
end;
显式游标
* 定义游标
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句;
e.g:
declare
Cursor cursor_user is select * from t_user;--声名游标
v_user t_user%rowtype;
begin
open cursor_user;--打开游标
loop
fetch cursor_user into v_user;--读取游标
exit when cursor_user%NOTFOUND;
dbms_output.put_line(v_user.name);
end loop;
CLOSE cursor_user;--关闭游标
end;
-- 使用for循环：for循环会自动打开关闭游标
declare
Cursor cursor_user is select * from t_user;
v_user t_user%rowtype;
begin
for user_record in cursor_user loop
dbms_output.put_line(user_record.name);
end loop;
end;
* 游标传参
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句;
e.g:
declare
Cursor cursor_user(p_name varchar2,p_sex t_user.sex%type)
is select * from t_user where name=p_name and sex = p_sex;
v_user t_user%rowtype;
begin
open cursor_user('ccc','M');--打开游标
loop
fetch cursor_user into v_user;--读取游标
exit when cursor_user%NOTFOUND;
dbms_output.put_line(v_user.name);
end loop;
CLOSE cursor_user;--关闭游标
end;
*动态游标
* 在执行时，动态指定要制定的SELECT语句，动态游标又分为强类型ref游标和弱类型ref游标两种
* 强类型ref游标
* TYPE <游标名> IS REF CURSOR RETURN<返回类型>;
* 只能关联一张表
* 弱类型ref游标
* TYPE <游标名> IS REF CURSOR;
* 可以关联多张表
declare
-- 定义强类型的游标TYPE
TYPE type_user IS REF CURSOR RETURN t_user%ROWTYPE;
cursor_user type_user;
v_user t_user%rowtype;
-- 定义弱类型的游标TYPE
TYPE type_use IS REF CURSOR;
cursor_use type_use;
v_dept t_dept%rowtype;
begin
-- 打开一个强类型的游标，只能是t_user类型的数据
open cursor_user for select * from t_user;
-- 打开一个弱类型的游标，数据类型可以是多个表
open cursor_use for select * from t_dept d;
loop
fetch cursor_user into v_user;
exit when cursor_user%notfound;
dbms_output.put_line(v_user.name);
end loop;
loop
fetch cursor_use into v_dept;
exit when cursor_use%notfound;
dbms_output.put_line(v_dept.name);
end loop;
end;
| 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果 聚合函数(多行函数)：聚合函数同时可以对多行数据进行操作，并返回一个结果 单行函数 字符函数 字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式 ASCII(X)：返回字符X的ASCII码 CONCAT(X,Y)：连接字符串X和Y INSTR(X,STR[,START][,N)：从X中查找str，可以指定从start开始，也可以指定从n开始 LENGTH(X)：返回X的长度 LOWER(X)：X转换成小写 UPPER(X)：X转换成大写 SUBSTR(X,start[,length])：返回X的字串，从start开始，截取length个字符，缺省length默认到结尾 REPLACE(X,old,new)：在X中查找old，并替换成new TRIM([TRIM_STR FROM]X)：把X的两边截去trim_str字符串，缺省截去空格
数字函数 数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式 ABS(X)：X的绝对值 ACOS(X)：X的反余弦 COS(X)：余弦 CEIL(X)：大于或等于X的最小值 FLOOR(X)：小于或等于X的最大值 LOG(X,Y)：X为底Y的对数 MOD(X,Y)：X除以Y的余数 POWER(X,Y)：X的Y次幂 ROUND(X[,Y])：X在第Y位四舍五入 TRUNC(X[,Y])：X在第Y位截断
转换函数 转换函数将值从一种数据类型转换为另外一种数据类型 TO_CHAR(d|n[,fmt])：把日期和数字转换为制定格式的字符串。Fmt是格式化字符串 TO_DATE(X,[,fmt])：把一个字符串以fmt格式转换成一个日期类型 TO_NUMBER(X,[,fmt])
日期函数 日期函数对日期进行运算。常用的日期函数有 ADD_MONTHS(d,n)：在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期 LAST_DAY(d)：返回指定日期当月的最后一天 EXTRACT(fmt FROM d)：提取日期中的特定部分，fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND ROUND(d[,fmt])：返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天，fmt可以为：YEAR、MONTH、DAY 其它函数 NVL(X,VALUE)：如果X为空，返回value，否则返回X NVL2(x,value1,value2)：如果x非空，返回value1，否则返回value2 DECODE --把base_expr与后面的每个compare 进行比较,如果匹配返回相应的value.如果没有发生匹配,则返回default DECODE(base_expr,comparel,valuel, Compare2,value2, … default) GREATEST -- 回一组表达式中的最大值,即比较字符的编码大小. GREATEST(expr1[,expr2]…) e.g: select greatest(’AA’,’AB’,’AC’) from dual; LEAST --获得参数中最小的表达式 LEAST(expr1[,:expr2]…) e.g: select LEAST(’AA’,’AB’,’AC’) from dual; CASE CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 聚合函数 聚合函数同时对一组数据进行操作返回一行结果，比如计算一组数据的总和，平均值 AVG：平均值 SUM：求合 MIN、MAX：最小，最大 COUNT：统计
自定义函数 PL/SQL定义的DECLARE匿名块，不能传递参数，只能执行一次，没有名称，也不被存储在数据库中，还不能其它应用程序调用。所以就有了比匿名块功能更强大的函数，函数也可以看做是一个匿名块，它是PL/SQL的子程序，它有自己的名字，可以传递参数，并且绝对会返回一个值给调用方 创建函数 create [or replace] function [schema.]function_name (函数参数列表) --参数有IN、OUT、IN OUT三种类型；IN代表需要输入的参数，OUT代表要返回的参数，IN OUT代表即是输入参数也是返回参数。 return datetype--返回类型 [is | as ]---任选一个，后面跟pLSQL代码块 begin --执行的SQL end; e.g: create or replace function f_count_user( i_name in varchar2,--in 入参 i_sex out varchar2 --out 出参，in out :即可做入参又可做出参 ) return number is v_count number; begin if i_name is not null and i_sex is not null then select count(1) into v_count from t_user where name = i_name and sex = i_sex; end if; i_sex:='bbbb'; return v_count; end f_count_user; 查询函数信息 --用户拥有的触发器、存储过程、函数查询表 select * from User_Procedures; --查询用户拥有触发器、存储过程、函数的源码 select * from User_Source;
-- 定义函数 create or replace function set_user_age(i_name in varchar2,o_id out varchar2) return boolean --返回类型 is begin for i in(select t.id from t_user t where t.name = i_name ) loop o_id := o_id || '-' || i.id; end loop; update t_user t set t.age = 110 where t.name = i_name; commit; return true; end;
select * from t_user; -- 调用函数 declare v_success boolean; v_id varchar2(100); begin v_success := set_user_age('zs',v_id); dbms_output.put_line(v_id); end; -- ASCII(X)：返回字符X的ASCII码 select ascii('a') from dual; -- CONCAT(X,Y)：连接字符串X和Y select concat('a','b') from dual; -- INSTR(X,STR[,START][,N)：从X中查找str，可以指定从start开始，也可以指定从n开始 select instr('abc','b',1) from dual; -- LENGTH(X)：返回X的长度 select length('abcdf') from dual; -- LOWER(X)：X转换成小写 select lower('ABD') from dual; -- UPPER(X)：X转换成大写 select upper('bdgc') from dual; -- SUBSTR(X,start[,length])：返回X的字串，从start开始，截取length个字符，缺省length默认到结尾 select substr('abcdfg','2','2') from dual; -- REPLACE(X,old,new)：在X中查找old，并替换成new select replace('abcdefg','c','oooo') from dual; -- TRIM([TRIM_STR FROM]X)：把X的两边截去trim_str字符串，缺省截去空格 select trim( ' akkkkkkc ') from dual; -- ABS(X)：X的绝对值 select abs(-22) from dual; -- ACOS(X)：X的反余弦 select acos(0.25) from dual; -- COS(X)：余弦 select cos(45) from dual; -- CEIL(X)：大于或等于X的最小值 select ceil(20.5) from dual;
-- FLOOR(X)：小于或等于X的最大值 select floor(20.5) from dual; -- LOG(X,Y)：X为底Y的对数 select log(2,3) from dual; -- MOD(X,Y)：X除以Y的余数 select mod(3,2) from dual; -- POWER(X,Y)：X的Y次幂 select power(2,3) from dual; -- ROUND(X[,Y])：X在第Y位四舍五入 select round(20.241,2) from dual; -- TRUNC(X[,Y])：X在第Y位截断 select trunc(20.1245,2) from dual; -- TO_CHAR(d|n[,fmt])：把日期和数字转换为制定格式的字符串。Fmt是格式化字符串 select to_char(sysdate,'YYYY "年" MM "月" DD "日" HH24:MI:SS') from dual; -- TO_DATE(X,[,fmt])：把一个字符串以fmt格式转换成一个日期类型 select to_date(sysdate) from dual; -- TO_NUMBER(X,[,fmt]) select to_number(20) from dual; select * from t_user; -- 游标 -- 隐式游标 declare begin update t_user set name = '张三' where id=1; if SQL%found then dbms_output.put_line('success'); commit; else dbms_output.put_line('error'); end if; end; -- SQL%ROWCOUNT：代表DML语句成功执行的数据行数 -- SQL%FOUND：值为TRUE代表插入、删除、更新或单行查询操作成功 -- SQL%NOTFOUND：与SQL%FOUND属性返回值相反 -- SQL%ISOPEN：DML执行过程中为真，结束后为假 -- 显式游标 -- CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句; declare cursor my_cursor is select * from t_user; --声明游标 v_user t_user%rowtype; begin open my_cursor; -- 打开游标 loop fetch my_cursor into v_user; --读取游标 exit when my_cursor%Notfound; dbms_output.put_line(v_user.name); end loop; Close my_cursor; -- 关闭游标 end; -- 使用for循环。for循环会自动打开关闭游标 declare cursor my_cursor is select * from t_user; v_user t_user%rowtype; begin for user_record in my_cursor loop dbms_output.put_line(user_record.name); end loop; end; select * from t_user; -- CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句; declare cursor my_cursor (p_name varchar2,p_sex t_user.sex%type) is select * from t_user where name=p_name and sex=p_sex; v_user t_user%rowtype; begin open my_cursor('ls','F'); -- 打开游标 loop fetch my_cursor into v_user; --读取游标 exit when my_cursor%notfound; dbms_output.put_line(v_user.name|| ' '||v_user.sex); end loop; Close my_cursor; --关闭游标 end; declare -- 定义强类型的游标类型 Type type_user is ref Cursor return t_user%rowtype; my_cursor type_user; v_user t_user%rowtype;
-- 定义弱类型的游标类型 Type type_users is ref Cursor; my_cursors type_users; v_dept t_dept%rowtype; begin -- 打开一个强类型的游标，只能是 t_user 类型的数据 open my_cursor for select * from t_user; -- 打开一个弱类型的游标，数据类型可以是多个表 open my_cursors for select * from t_dept; loop fetch my_cursor into v_user; exit when my_cursor%notfound; dbms_output.put_line(v_user.name || ' ' || v_user.sex); end loop; loop fetch my_cursors into v_dept; exit when my_cursors%notfound; dbms_output.put_line(v_dept.name || ' '|| v_dept.id); end loop; end; |
存储过程(PROCEDURE)
它由一组SQL语句和其他PL / SQL结构，组合在一起，存储在数据库中的，并运行以解决特定问题或执行一组相关的任务的单元。
* 创建过程
create or replace procedure 过程名[（参数：参数名 in/out/in out 类型，....）]as--定义变量
begin
-- sql....
EXCEPTION
......end 过程名;
e.g:
create or replace procedure p_user_proce
as
v_label varchar2(100):='success';
begin
dbms_output.put_line(v_label);
end p_user_proce;
-- 调用存储过程
call p_user_proce();
-- 在程序块里或者函数、过程中调用，直接引用过程名便可
p_user_proce（）;
| --创建员工表 create table t_emp( id number(5) not null primary key, emp_id number(10) not null, name varchar2(200) not null, pay number(10,2) default 0, dept_id varchar2(20) not null, entry_date date default sysdate ); --创建部门表 Create table t_dept( Id number(5) not null primary key, Name varchar2(200) not null, remark varchar2(500) ); --写入测试数据 insert into t_emp values(3,10003,'李三',3500,5,to_date('20170302','yyyy/mm/dd')); insert into t_emp values(4,10004,'李四',4500,3,to_date('20170201','yyyy/mm/dd')); insert into t_emp values(5,10005,'李五',2500,4,to_date('20170102','yyyy/mm/dd')); insert into t_emp values(6,10006,'张一',5500,3,to_date('20170402','yyyy/mm/dd')); insert into t_emp values(7,10007,'张二',7500,7,to_date('20170205','yyyy/mm/dd')); insert into t_emp values(8,10008,'张三',7500,6,to_date('20170209','yyyy/mm/dd')); insert into t_emp values(9,10009,'张四',2500,8,to_date('20170112','yyyy/mm/dd')); insert into t_emp values(10,10010,'张五',12500,9,to_date('20170312','yyyy/mm/dd')); insert into t_emp values(11,10011,'白一',3500,10,to_date('20170209','yyyy/mm/dd')); insert into t_emp values(12,10012,'白二',6500,4,to_date('20170112','yyyy/mm/dd')); insert into t_emp values(13,10013,'白三',5500,9,to_date('20170310','yyyy/mm/dd')); insert into t_emp values(14,10014,'白四',8500,7,to_date('20170412','yyyy/mm/dd')); insert into t_emp values(15,10015,'白五',4500,10,to_date('20170212','yyyy/mm/dd')); insert into t_emp values(16,10015,'白6',4500,10,to_date('20170213','yyyy/mm/dd')); insert into t_emp values(17,10015,'白7',4500,10,to_date('20170214','yyyy/mm/dd')); insert into t_emp values(18,10015,'白8',4500,10,to_date('20170215','yyyy/mm/dd')); insert into t_emp values(19,10015,'白9',4500,10,to_date('20170201','yyyy/mm/dd')); insert into t_emp values(20,10015,'白10',4500,10,to_date('20170202','yyyy/mm/dd')); insert into t_emp values(21,10015,'白11',4500,10,to_date('20170205','yyyy/mm/dd')); insert into t_emp values(22,10015,'白12',4500,10,to_date('20170208','yyyy/mm/dd')); insert into t_emp values(23,10015,'白13',4500,10,to_date('20170217','yyyy/mm/dd')); insert into t_emp values(24,10015,'白14',4500,10,to_date('20170218','yyyy/mm/dd')); insert into t_emp values(25,10015,'白15',4500,10,to_date('20170219','yyyy/mm/dd')); insert into t_emp values(26,10015,'白16',4500,10,to_date('20170220','yyyy/mm/dd')); insert into t_emp values(27,10015,'白17',4500,10,to_date('20170207','yyyy/mm/dd')); insert into t_emp values(28,10015,'白18',4500,10,to_date('20170206','yyyy/mm/dd')); insert into t_dept values(1,'1号部门',null); insert into t_dept values(2,'2号部门',null); insert into t_dept values(3,'3号部门',null); insert into t_dept values(4,'4号部门',null); insert into t_dept values(5,'5号部门',null); insert into t_dept values(6,'6号部门',null); insert into t_dept values(7,'7号部门',null); insert into t_dept values(8,'8号部门',null); insert into t_dept values(9,'9号部门',null); insert into t_dept values(10,'10号部门',null); --题目： 1：创建一个存储过程，以员工号为参数，输出该员工的工资 -- 创建存储过程 create or replace procedure get_emp_pay (i_emp_id in number,o_pay out number) as v_id t_emp.id%type := i_emp_id; v_pay t_emp.pay%type; begin select pay into v_pay from t_emp where id = v_id; dbms_output.put_line(v_pay); o_pay := v_pay; end; -- 调用 declare v_id t_emp.id%type := '&员工ID'; v_pay t_emp.pay%type; begin get_emp_pay(v_id,v_pay); dbms_output.put_line(v_pay); end; 2：创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门， 则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250； 若属于其他部门，则增加300 select * from t_dept; select * from t_emp e,t_dept d where e.dept_id = d.id; -- 创建存储过程 create or replace procedure update_emp_pay(i_emp_id in number) as v_id t_emp.id%type := i_emp_id; v_dept_id t_emp.dept_id%type; begin select dept_id into v_dept_id from t_emp where id = v_id; -- dbms_output.put_line(v_dept_id); case v_dept_id when 10 then update t_emp set pay = pay+150 where dept_id = v_dept_id; when 20 then update t_emp set pay = pay+200 where dept_id = v_dept_id; when 30 then update t_emp set pay = pay+250 where dept_id = v_dept_id; else update t_emp set pay = pay+300 where dept_id = v_dept_id and id = v_id; end case; commit; end; -- 调用 declare v_id t_emp.id%type := '&请输入需要加工资的员工ID'; begin update_emp_pay(v_id); end; update t_emp set pay = pay+300 where dept_id not in (10,20,30);
select * from t_emp e,t_dept d where e.dept_id = d.id; select pay from t_emp where dept_id not in (10,20,30); select * from t_emp where dept_id = 3; update t_emp set pay = pay- 150 where dept_id = 3;
3：创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回） select * from t_emp; -- 创建存储过程 create or replace procedure get_emp_work_year (i_emp_id in number,o_work_year out number) as v_id t_emp.id%type := i_emp_id; v_entry_date t_emp.entry_date%type; v_year number; v_now number; begin select entry_date into v_entry_date from t_emp where id = v_id; select EXTRACT(year from v_entry_date) into v_year from dual; select extract(year from sysdate) into v_now from dual; o_work_year := v_now - v_year; end; -- 调用 declare v_id t_emp.id%type := '&要查询的员工ID'; v_work_year number; begin get_emp_work_year(v_id,v_work_year); dbms_output.put_line(v_id || '号员工，工作了:' || v_work_year || '年'); end; 4：创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息 -- 创建存储过程 create or replace procedure find_earliest_emps(i_dept_id in number) as v_dept_id t_emp.dept_id%type := i_dept_id; begin for i in (select e.* from (select t_emp.*,rownum rn from t_emp where dept_id = i_dept_id and rownum >0 order by entry_date) e where rn<=10) loop dbms_output.put_line(i.id || ' ' || i.emp_id || ' ' || i.name || ' '||i.pay || ' '||i.dept_id || ' '||i.entry_date ); end loop; end; -- 调用 declare v_dept_id t_emp.dept_id%type := '&部门编号'; begin find_earliest_emps(v_dept_id); end; select * from t_emp;
5：创建一个函数，以员工号为参数，返回该员工的工资 -- 创建存储过程 create or replace procedure find_pay_by_emp_id (i_emp_id in number,o_pay out number) as v_emp_id t_emp.emp_id%type := i_emp_id; v_pay t_emp.pay%type; begin select pay into v_pay from t_emp where emp_id = v_emp_id; o_pay := v_pay; end; -- 调用 declare v_emp_id t_emp.emp_id%type := '&员工号'; v_pay t_emp.pay%type; begin find_pay_by_emp_id(v_emp_id,v_pay); dbms_output.put_line(v_pay); end; -- 创建函数 create or replace function f_find_pay_by_emp_id (i_emp_id in number) return number is v_pay t_emp.pay%type; begin select pay into v_pay from t_emp where emp_id = i_emp_id; return v_pay; end; -- 调用函数 declare v_emp_id t_emp.emp_id%type := '&员工号'; v_pay t_emp.pay%type; begin select f_find_pay_by_emp_id(v_emp_id) into v_pay from dual; dbms_output.put_line(v_pay); end;
6：创建一个函数，以部门号为参数，返回该部门的平均工资 -- 创建存储过程 create or replace procedure avg_pay_by_dept_id(i_dept_id in number,o_avg_pay out number) as v_dept_id t_emp.dept_id%type := i_dept_id; v_avg_pay t_emp.pay%type; begin select avg(pay) into v_avg_pay from t_emp where dept_id = v_dept_id; o_avg_pay := v_avg_pay; end; -- 调用 declare v_dept_id t_emp.dept_id%type := '&部门号'; v_avg_pay t_emp.pay%type; begin avg_pay_by_dept_id(v_dept_id,v_avg_pay); dbms_output.put_line(v_dept_id || '号部门的平均工资为: '||v_avg_pay); end; --创建函数 create or replace function f_avg_pay_by_dept_id(i_dept_id in number) return number is v_avg_pay t_emp.pay%type; begin select avg(pay) into v_avg_pay from t_emp where dept_id = i_dept_id; return v_avg_pay; end; -- 调用函数 declare v_dept_id t_emp.dept_id%type := '&部门号'; v_avg_pay t_emp.pay%type; begin select f_avg_pay_by_dept_id(v_dept_id) into v_avg_pay from dual; dbms_output.put_line(v_dept_id || '号部门的平均工资为: '||v_avg_pay); end; 7：创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资 -- 创建函数 create or replace function f_avg_pay_by_emp_id (i_emp_id in number) return number is v_avg_pay t_emp.pay%type; begin select avg(pay) into v_avg_pay from t_emp where dept_id = (select dept_id from t_emp where id = i_emp_id ); return v_avg_pay; end; -- 调用函数 declare v_emp_id t_emp.dept_id%type := '&员工号'; v_avg_pay t_emp.pay%type; begin select f_avg_pay_by_dept_id(v_emp_id) into v_avg_pay from dual; dbms_output.put_line(v_emp_id || '号所在部门的平均工资为: '||v_avg_pay); end;
8：创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。?? 如果修改成功，则显示“员工由……号部门调入……号部门”； 如果不存在该员工，则显示“员工号不存在，请输入正确的员工号。”； 如果不存在该部门，则显示“该部门不存在，请输入正确的部门号。”。 -- 创建存储过程 create or replace procedure update_emp_dept(i_emp_id in number,i_dept_id in number) as v_emp_id t_emp.id%type := i_emp_id; v_dept_id t_dept.id%type := i_dept_id; v_old_dept_id t_emp.dept_id%type; v_dept number; --v_old_dept_id t_emp.dept_id%type; begin select dept_id into v_old_dept_id from t_emp where id = i_emp_id; select count(1) into v_dept from t_dept where id = v_dept_id; if v_old_dept_id <1 then dbms_output.put_line('员工号不存在，请输入正确的员工号'); elsif v_dept <1 then dbms_output.put_line('该部门不存在，请输入正确的部门号'); else update t_emp set dept_id = v_dept_id where id = i_emp_id; if SQL%Found then dbms_output.put_line('员工由'||v_old_dept_id||'号部门调入了'||v_dept_id||'号部门'); commit; else dbms_output.put_line('修改员工信息失败'); end if; end if; end; -- 调用存储过程 declare v_emp number := '&员工号'; v_dept number := '&要调入的部门号'; begin update_emp_dept(v_emp,v_dept); end; select * from t_emp; 9：创建一个存储过程，以一个整数为参数，输入工资最高的前几个（参数值）员工的信息 -- 创建存储过程 create or replace procedure find_pay_order(i_pay in number,i_number number) as v_pay number := i_pay; v_number number := i_number; begin for i in ( select e2.* from (select e1.*,rownum rn from (select * from t_emp where pay >= i_pay order by pay desc) e1) e2 where rn <= i_number ) loop dbms_output.put_line(i.id || ' ' || i.emp_id || ' ' || i.name || ' '||i.pay || ' '||i.dept_id || ' '|| i.entry_date ); end loop; end; -- 调用 declare v_pay number := '&请输入整数'; v_number number := '&需要查询的个数'; begin find_pay_order(v_pay,v_number); end; 10：创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息 -- 创建存储过程 create or replace procedure find_emp_info(i_max_pay in number,i_min_pay in number) as declare v_max_pay number := '&最大值'; v_min_pay number := '&最小值'; begin find_emp_info(v_max_pay,v_min_pay); end; |
包（PACKAGE）
包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合。它具有面向对象程序设计语言的特点，是对这些PL/SQL 程序设计元素的封装。
包的优点：
1. 模块化
2. 更轻松的应用设计
3. 信息隐藏
4. 更好的性能
定义包头：
CREATE OR REPLACE PACKAGE 包名
AS--定义变量，声明过程、函数、游标
END 包名;
e.g:
CREATE OR REPLACE PACKAGE emp_admin
AS
TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
CURSOR desc_salary RETURN EmpRecTyp;
invalid_salary EXCEPTION;
PROCEDURE fire_employee (emp_id NUMBER);
FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;
* 定义包体
CREATE OR REPLACE PACKAGE BODY 包名
AS--定义过程、函数、游标具体实现
END 包名;
e.g:
CREATE OR REPLACE PACKAGE emp_admin
AS
PROCEDURE fire_employee (emp_id NUMBER)
is
--定义局部变量
begin
--sql....
end fire_employee;
FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp
is
--定义局部变量
begin
--sql....
end nth_highest_salary;
END emp_admin;
* 调用包
-- 包名.过程名，包名.函数名，包名.常量名
packge_name.proce_name/packge_name.function_name
e.g:
declare
v_name varchar2(100):='test';
v_sex varchar2(10);
v_user t_user%rowtype;
begin
pack_user.p_user_proce();
v_user := poack_user.f_count_user(v_name,v_sex);
end;
| -- 定义包头 create or replace package pack_test is -- Author : ADMINISTRATOR -- Created : 2019/1/17 星期四 下午 5:00:21 -- Purpose :
-- Public type declarations -- type <TypeName> is <Datatype>;
-- Public constant declarations --<ConstantName> constant <Datatype> := <Value>; -- Public variable declarations --<VariableName> <Datatype>; -- Public function and procedure declarations --function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; -- 1. procedure get_emp_pay (i_emp_id in number,o_pay out number);
-- 2. procedure update_emp_pay(i_emp_id in number); -- 3. procedure get_emp_work_year (i_emp_id in number,o_work_year out number);
-- 4. procedure find_earliest_emps(i_dept_id in number);
-- 5. function f_find_pay_by_emp_id (i_emp_id in number) return number;
-- 6. function f_avg_pay_by_dept_id(i_dept_id in number) return number;
-- 7. function f_avg_pay_by_emp_id (i_emp_id in number) return number;
-- 8. procedure update_emp_dept(i_emp_id in number,i_dept_id in number);
-- 9. procedure find_pay_order(i_pay in number,i_number number);
-- 10. procedure find_emp_info(i_max_pay in number,i_min_pay in number); end pack_test; -- 定义包体 CREATE OR REPLACE PACKAGE BODY pack_test AS--定义过程、函数、游标具体实现 -- 1. procedure get_emp_pay (i_emp_id in number,o_pay out number) as v_id t_emp.id%type := i_emp_id; v_pay t_emp.pay%type; begin select pay into v_pay from t_emp where id = v_id; dbms_output.put_line(v_pay); o_pay := v_pay; end; -- 2. procedure update_emp_pay(i_emp_id in number) as v_id t_emp.id%type := i_emp_id; v_dept_id t_emp.dept_id%type; begin select dept_id into v_dept_id from t_emp where id = v_id; -- dbms_output.put_line(v_dept_id); case v_dept_id when 10 then update t_emp set pay = pay+150 where dept_id = v_dept_id; when 20 then update t_emp set pay = pay+200 where dept_id = v_dept_id; when 30 then update t_emp set pay = pay+250 where dept_id = v_dept_id; else update t_emp set pay = pay+300 where dept_id = v_dept_id and id = v_id; end case; commit; end;
-- 3. procedure get_emp_work_year (i_emp_id in number,o_work_year out number) as v_id t_emp.id%type := i_emp_id; v_entry_date t_emp.entry_date%type; v_year number; v_now number; begin select entry_date into v_entry_date from t_emp where id = v_id; select EXTRACT(year from v_entry_date) into v_year from dual; select extract(year from sysdate) into v_now from dual; o_work_year := v_now - v_year; end; -- 4. procedure find_earliest_emps(i_dept_id in number) as v_dept_id t_emp.dept_id%type := i_dept_id; begin for i in (select e.* from (select t_emp.*,rownum rn from t_emp where dept_id = i_dept_id and rownum >0 order by entry_date) e where rn<=10) loop dbms_output.put_line(i.id || ' ' || i.emp_id || ' ' || i.name || ' '||i.pay || ' '||i.dept_id || ' '||i.entry_date ); end loop; end; -- 5. function f_find_pay_by_emp_id (i_emp_id in number) return number is v_pay t_emp.pay%type; begin select pay into v_pay from t_emp where emp_id = i_emp_id; return v_pay; end; -- 6. function f_avg_pay_by_dept_id(i_dept_id in number) return number is v_avg_pay t_emp.pay%type; begin select avg(pay) into v_avg_pay from t_emp where dept_id = i_dept_id; return v_avg_pay; end; -- 7. function f_avg_pay_by_emp_id (i_emp_id in number) return number is v_avg_pay t_emp.pay%type; begin select avg(pay) into v_avg_pay from t_emp where dept_id = (select dept_id from t_emp where id = i_emp_id ); return v_avg_pay; end; -- 8. procedure update_emp_dept(i_emp_id in number,i_dept_id in number) as v_emp_id t_emp.id%type := i_emp_id; v_dept_id t_dept.id%type := i_dept_id; v_old_dept_id t_emp.dept_id%type; v_dept number; --v_old_dept_id t_emp.dept_id%type; begin select dept_id into v_old_dept_id from t_emp where id = i_emp_id; select count(1) into v_dept from t_dept where id = v_dept_id; if v_old_dept_id <1 then dbms_output.put_line('员工号不存在，请输入正确的员工号'); elsif v_dept <1 then dbms_output.put_line('该部门不存在，请输入正确的部门号'); else update t_emp set dept_id = v_dept_id where id = i_emp_id; if SQL%Found then dbms_output.put_line('员工由'||v_old_dept_id||'号部门调入了'||v_dept_id||'号部门'); commit; else dbms_output.put_line('修改员工信息失败'); end if; end if; end; -- 9. procedure find_pay_order(i_pay in number,i_number number) as v_pay number := i_pay; v_number number := i_number; begin for i in ( select e2.* from (select e1.*,rownum rn from (select * from t_emp where pay >= i_pay order by pay desc) e1) e2 where rn <= i_number ) loop dbms_output.put_line(i.id || ' ' || i.emp_id || ' ' || i.name || ' '||i.pay || ' '||i.dept_id || ' '|| i.entry_date ); end loop; end; -- 10. procedure find_emp_info(i_max_pay in number,i_min_pay in number) as END pack_test; -- 调用包 declare v_id t_emp.id%type := '&员工ID'; v_pay t_emp.pay%type; begin pack_test.get_emp_pay(v_id,v_pay); end; |
触发器（TRIGGER）
触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行,即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。
ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作,ORACLE将触发器的功能扩展到了DDL触发，包括数据库的启动与关闭等系统事件。
触发器的组成：
* 触发事件：如INSERT, UPDATE, DELETE
* 触发时间：即事件BEFORE或AFTER
* 触发器本身：即该TRIGGER 被触发之后的执行SQL
* 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器
* 语句级(STATEMENT)触发器：是指当触发事件发生时，该触发器只执行一次；
* 行级(ROW)触发器：当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
触发器类型：
* DML触发器 ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行（行级）或语句（级）操作上进行触发
--DML触发器
CREATE [OR REPLACE] TIGGER
触发器名
触发时间< before | after >
触发事件< insert | update | delete>
ON 表名/视图名
[FOR EACH ROW] --加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器
[WHEN CONDITION] -- 执行条件
BEGIN
pl/sql语句
END;
* 系统触发器 可以在ORACLE数据库系统的事件中进行触发，如执行DDL或ORACLE系统的启动与关闭等系统事件
create or replace trigger [schema.]trigger_name
{before | after}
--database_event_list表示一个说多个数据库事件，多个事件中间用or分开
{database_event_list}
--database表示数据库级触发器；schema表示用户级触发器
on {database | [schema.]SCHEMA}
-- WHEN CONDITION 执行条件
[WHEN CONDITION]
begin
pl/sql....
end;
* instead of触发器 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT|DELETE|UPDATE [OF COLUMN...]}
[OR {INSERT| DELETE| UPDATE [OF COLUMN...]}]
ON VIEW_NAME
[REFFERENCING{OLD [AS] OLD | NEW [AS] NEW| PARENT AS PARENT}]
-- 可以指定相关名称，当前的默认相关名称为OLD和NEW,应用相关名称时需要加：
[FOR EACH ROW]
--instead of 触发器只能在行级上触发，因为没有必要指定
[WHEN CONDITION]
DECLARE
BEGIN
END;
* DDL触发器 触发事件时数据库对象的创建和修改
create or replace trigger [schema.]trigger_name
{before | after}
--DDL_EVENT_LIST表示一个或多个DDL事件，多个事件中间用or分开
{DDL_EVENT_LIST}
--database表示数据库级触发器；schema表示用户级触发器
on {database | [schema.]SCHEMA}
-- WHEN CONDITION 执行条件
[WHEN CONDITION]
begin
pl/sql....
end;
触发器注意事项：
* 触发器不接受参数
* 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾
* 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大
* 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用
* 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）
* 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了
* 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句
* 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列
* 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别
--DML触发器
create or replace trigger trg_up_user
after
update
ON t_loyal
FOR EACH ROW
BEGIN
dbms_output.put_line(:old.name);
END trg_up_user;
--系统触发器（DDL触发器）
create or replace trigger trg_drop_znsd--在做删除动作之前触发
before drop on SCHEMA
--满足条件触发
when (ora_dict_obj_name <> 'T_ZNSD')
DECLARE
begin--ora_sysevent：系统事件
--ora_dict_obj_owner：操作对象所属用户
--ora_dict_obj_name：操作对象名称
DBMS_OUTPUT.put_line(ora_sysevent||'--'||ora_dict_obj_owner||'--'||ora_dict_obj_name);
RAISE_APPLICATION_ERROR(-20022,'can not drop table');
end;
--登录触发器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO log_event (user_name, address, logon_date)
VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;--退出触发器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_event (user_name, address, logoff_date)
VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;
--instead of触发器
CREATE OR REPLACE TRIGGER trg_add_loyal_view_cname
INSTEAD OF INSERT ON loyal_view
FOR EACH ROW
DECLARE
v_c_id number;
BEGIN
select t.c_id into v_c_id from t_class t where t.class_name = :new.class_name;
if v_c_id is null then
v_c_id := seq_class_id.nextVal();
insert into t_class(v_c_id,:new.class_name);
end if;
insert into t_student(id,name,sex,age,c_id)
values(seq_stu_id.nextVal(),:new.name,:new.sex,:new.age,v_c_id);
END ;
| -- DML 触发器 -- 只能写 DML 语句 create or replace trigger trg_update_user after update on t_user for each row declare begin dbms_output.put_line(:new.name || '---'||:old.name); RAISE_APPLICATION_ERROR(-20022,'不能更新数据'); end trg_update_user; select * from t_user; update t_user set name = '测试trigger' where id = 1;
-- instead of 替代触发器
create or replace force view my_view as select s.id,s.name,s.age,c.cid,c.classname,c.classdesc from t_student s,t_class c where s.cid = c.cid; -- 报错 -- 创建 instead of 替代触发器 分开进行增加 insert into my_view(id,name,age,cid,classname,classdesc) values(17,'触发器2',20,1006,'1802','1802'); -- 创建 instead of替代触发器 create or replace trigger trg_add_view instead of insert on my_view for each row declare v_studentCount number; v_classCount number; begin -- 判断要增加的学生是否存在 select count(id) into v_studentCount from t_student where id = :new.id;
-- 判断要增加的班级是否存在 select count(cid) into v_classCount from t_class where cid = :new.cid;
if v_classCount = 0 then --班级不存在 insert into t_class values(:new.cid,:new.classname,:new.classdesc); dbms_output.put_line('增加班级成功'); end if; if v_studentCount = 0 then -- 学生不存在 insert into t_student(id,name,age,cid) values(:new.id,:new.name,:new.age,:new.cid); dbms_output.put_line('增加学生成功'); else dbms_output.put_line('学生已存在'); end if; end trg_add_view; |
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
