很不错的oracle 帮助手册

开发中快速查找的好帮手，覆盖面全，查找方便，有实例：

Oracle函数
单行函数
字符函数
LOWER: 将字符转化成大写：对非字符无影响
UPER : 将字符转化成小写：对非字符无影响
CONCAT: 将字符串连接，相当于||
SUBSTR : 取得字串
LENGTH: 求长度
INITCAP :　将每个单词的第一个字母大写其它字母小写返回
INSTR : 求出现的位置

SELECT E.ENAME, LOWER(E.ENAME)
FROM EMP E
WHERE LOWER(E.ENAME)='smith'//转化成小写


SELECT MIN(SAL)//求最小值 （基于数值型的）
FROM EMP;

SELECT *
FROM EMP E
WHERE INITCAP(E.ENAME)='Ward'//首字母大写，其余小写

SELECT E.EMPNO,E.ENAME,E.JOB, CONCAT(E.ENAME,E.JOB) AS CON ,//连接（基于任
何类型的）


LENGTH(E.ENAME) AS LEN ,INSTR(E.JOB,'S') AS "IN" //求出现的位置
FROM EMP E
WHERE SUBSTR(E.JOB,1,5)='SALES';//取子串
使用数字函数
round//进行四舍五入
trunc:// 将值截断到指定的小数位
MOD//返回相除后的余数


SELECT TRUNC(49.536 ,1) AS "小数点后一位" ,TRUNC(49.536 ,0)AS "个位",TRUNC(49.536 ,-1) "十位"
FROM SYS.DUAL
//trunc: 将值截断到指定的小数位
SELECT E.ENAME, E.SAL, MOD(E.SAL,300) AS "除以300后的余数"
FROM EMP E
WHERE E.SAL IS NOT NULL;
MOD//返回相除后的余数

SELECT ROUND(45.945,2) "小数点后两位",
ROUND(45.945,0) "个位",
ROUND(45.945,-1) "十位"
FROM SYS.DUAL ;
round//进行四舍五入
使用日期函数
ADD_MONTHS(,＜i＞)
　返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，
将会截去小数点后面的部分。
LAST_DAY()
　函数返回包含日期d的月份的最后一天
缺省的日期格式是 DD-MON-YY
SYSDATE
　函数没有参数，返回当前日期和时间。

SELECT SYSDATE AS "时间"
FROM SYS.DUAL;

-- 日期-日期得到两个日期的差(是一个数值类型)
SELECT E.ENAME , (SYSDATE-E.HIREDATE)/7 AS "工作的周数"
FROM EMP E
WHERE E.ENAME IS NOT NULL;
SYSDATE//返回当前日期和时间的函数


SELECT E.ENAME , ROUND ( (SYSDATE-E.HIREDATE)/7,0) AS "工作的周数"
FROM EMP E
WHERE E.ENAME IS NOT NULL;

Round//取得按年或月四舍五入得到的新日期

日期+数值得到一个新的日期
SELECT E.ENAME,E.HIREDATE 雇用日期,(E.HIREDATE + 90) AS "转正日期"
FROM EMP E
WHERE E.ENAME IS NOT NULL;


SELECT E.ENAME ,MONTHS_BETWEEN(SYSDATE,E.HIREDATE) AS "工作的月数"
FROM EMP E;
MONTHS_BETWEEN//两日期相差多少月

转换函数和日期型
SELECT E.ENAME ,E.HIREDATE
FROM EMP E
WHERE E.HIREDATE > TO_DATE('23-9-1982','DD-MM-YYYY')


-- 日期类型转化为文本类型 ,TO_CHAR()函数的使用
SELECT E.ENAME ,e.hiredate, TO_CHAR(E.HIREDATE,'YYYY-MM-DD:DAY') AS "日期"
FROM EMP E
WHERE E.ENAME='SMITH'

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM') 时间
FROM SYS.DUAL;


-- 使用9的时候,如果该位没有数字,则该位不显示
-- 使用0的时候,如果该位没有数字,则该位显示为0
SELECT E.ENAME ,e.sal,TO_CHAR(E.SAL,'$99,999.99') AS "薪水"
FROM EMP E
WHERE E.ENAME IS NOT NULL;

SELECT E.ENAME ,e.sal,TO_CHAR(E.SAL,'L00,000.99') AS "薪水" ,TO_CHAR(E.SAL,'$99,999.99') "薪水2"
FROM EMP E
WHERE E.ENAME IS NOT NULL;


SELECT TO_DATE('1999-3-31','YYYY-MM-DD') AS 日期
FROM SYS.DUAL


SELECT *
FROM EMP E
WHERE E.HIREDATE < TO_DATE('1981-12-17','YYYY-MM-DD')


SELECT *
FROM EMP E
WHERE E.HIREDATE < '17-12月-1981'


SELECT TO_NUMBER('$123,456.98','$999,999.99') AS "NUMBER"
FROM
SYS.DUAL


SELECT * FROM EMP for update


SELECT E.ENAME ,E.SAL , e.comm ,(E.SAL * 12 + E.COMM) AS "年收入"
FROM EMP E
WHERE E.ENAME IS NOT NULL;

-- 使用NVL函数,如果该字段为null,就用后面给的0替换该null值
SELECT E.ENAME ,E.SAL ,E.SAL * 12 ,E.COMM,(E.SAL * 12 + NVL(E.COMM,0)) AS "年收入"
FROM EMP E
WHERE E.ENAME IS NOT NULL;


空值的应用NVL NVL2 NULL
SELECT NVL(E.ENAME,'无名氏') as "姓名",E.SAL
FROM EMP E
NVL(COMM,0) //如果comm字段为null,就用0替换该null


SELECT * FROM EMP E FOR UPDATE;


-- 如果E.ENAME是null,就显示无名氏
-- 如果E.ENAME不是null,就显示E.ENAME
SELECT E.ENAME, NVL2(E.ENAME,E.ENAME,'无名氏') AS "NAME"
FROM EMP E
ORDER BY E.ENAME;
NVL2//如果expr不为Null,返回expr1, 为Null,返回expr2

SELECT * FROM EMP FOR UPDATE;

SELECT ENAME, LENGTH(ENAME) "expr1",
ENAME, LENGTH(ENAME) "expr2",
NULLIF(LENGTH(ENAME), LENGTH(JOB)) result
FROM EMP;
NULLIF//比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。


Case decode语句
SELECT E.ENAME ,E.JOB,E.SAL,
CASE E.JOB
WHEN 'CLERK' THEN 1.10 * E.SAL
WHEN 'MANAGER' THEN 1.3 * E.SAL
WHEN 'SALESMAN' THEN 1.45 * E.SAL
ELSE E.SAL
END AS "修订工资数"
FROM EMP E
WHERE E.ENAME='SMITH';


SELECT E.ENAME,E.JOB ,E.SAL ,
DECODE(E.JOB, 'CLERK',E.SAL * 1.1,
'SALESMAN',E.SAL * 1.2,
'MANAGER' ,E.SAL * 1.4,
E.SAL)
AS "工资修订数"
FROM EMP E


SELECT E.ENAME,E.JOB,NVL(TO_CHAR(E.JOB), '还没有工作') AS "结果"
FROM EMP E
WHERE E.JOB IS NULL;


多行函数
-- AVG,SUM只能针对数值类型
SELECT MIN(E.SAL) "最低工资",
MAX(E.SAL) "最高工资",
AVG(E.SAL) "平均工资" ,
SUM(E.SAL) "工资总和"
FROM EMP E;

MIN,MAX可以用于任何数据类型
SELECT MAX(E.HIREDATE) ,MIN(E.HIREDATE)
FROM EMP E


SELECT MAX(E.HIREDATE),MIN(E.HIREDATE)
FROM EMP E;


SELECT * FROM EMP FOR UPDATE;
WHERE COMM IS NOT NULL;

SELECT * FROM EMP;

Count 统计数目
SELECT COUNT(*) FROM EMP;
COUNT(*)求出所有符 合条件的记录条数，包含有重复的


SELECT COUNT(COMM) FROM EMP;
COUNT(字段)这是求出所有符合条件并且字段值是
非空的记录数，包含有重复的

SELECT COUNT(DISTINCT E.JOB) FROM EMP E;


SELECT COUNT(COMM)
FROM EMP;


SELECT JOB FROM EMP;


-- 查询job字段非空的数据的总数
SELECT COUNT(JOB)
FROM EMP;

-- 查询job字段非空的并且数据不重复的总数
SELECT COUNT(DISTINCT (JOB))
FROM EMP;


SELECT * FROM EMP FOR UPDATE;


SELECT SUM(E.COMM) "佣金总和" ,count(E.COMM) "总条数", AVG(E.COMM) "平均佣金" FROM EMP E

SELECT AVG(E.COMM), SUM(E.COMM) ,COUNT(E.COMM)
FROM EMP E

SELECT AVG(E.COMM)
FROM EMP E

SELECT sum(E.COMM)
FROM EMP E ;

SELECT AVG(NVL(E.COMM,0))
FROM EMP E ;

SELECT * FROM EMP FOR UPDATE;
分组函数GROUP BY
使用GROUP BY子句将表中的数据分成多个小组。分组后的数据执行组函数计算，结果返回给客户。最终的结果自动按照分组字段进行升序排列

-- 出现在查询列表中的字段,要么出现在组函数中,要么出现在GROUP BY字句中
-- (另一种情况,可以只出现在GROUP BY字句中)
SELECT E.DEPTNO,AVG(E.SAL) AS "AVG"
FROM EMP E
GROUP BY E.DEPTNO
ORDER BY "AVG" ASC;

SELECT * FROM EMP;

SELECT AVG(E.SAL) AS "AVG"
FROM EMP E
GROUP BY E.DEPTNO
ORDER BY "AVG"

SELECT E.DEPTNO,E.JOB,SUM(E.SAL)
FROM EMP E
GROUP BY E.DEPTNO,E.JOB
order BY E.DEPTNO “AVG”/“DESC”;


-- 出现查询列表中的字段,要末出现在组函数中,要末出现在GROUP BY 子句中(必需要出现一次,不能都不出现)
SELECT E.DEPTNO,COUNT(E.ENAME)
FROM EMP E
GROUP BY E.DEPTNO


-- 原意是给分组函数加上限制条件,但是不能使用Where子句
SELECT E.DEPTNO,AVG(E.SAL)
FROM EMP E
WHERE AVG(E.SAL) > 3000
GROUP BY E.DEPTNO


SELECT E.DEPTNO,COUNT(E.ENAME)
FROM EMP E
GROUP BY E.DEPTNO;

SELECT E.DEPTNO,AVG(E.SAL)
FROM EMP E
WHERE AVG(E.SAL) > 3000
GROUP BY E.DEPTNO;


SELECT E.DEPTNO,MAX(E.SAL)
FROM EMP E
GROUP BY E.DEPTNO
HAVING MAX(E.SAL) >=3000;


SELECT E.JOB,SUM(E.SAL) AS "工资总和"
FROM EMP E
WHERE E.JOB IN('SALESMAN','MANAGER','CLERK')
GROUP BY E.JOB
HAVING SUM(E.SAL) > 3000
ORDER BY SUM(E.SAL);
对组的过滤不能出现在WHERE子句中，而是要使用查询语句的另一个子句：HAVING


SELECT AVG(E.SAL)
FROM EMP E
GROUP BY DEPTNO;

SELECT MAX(AVG(E.SAL))
FROM EMP E
GROUP BY E.DEPTNO


SELECT E.ENAME, E.JOB ,NVL(TO_CHAR(E.JOB),'还没有工作') AS "工作"
FROM EMP E
组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处理空值
SELECT MAX(E.SAL) AS "MAXSAL",MIN(E.SAL) AS "MINSAL",AVG(E.SAL) AS "AVGSQL" ,SUM(E.SAL) AS "SUMSAY"
FROM EMP E

SELECT MAX(E.ENAME) AS "MAXNAME" ,MIN(E.ENAME) AS "MINNAME"
FROM EMP E

SELECT COUNT(*)
FROM EMP E


SELECT COUNT(DISTINCT (E.JOB))
FROM EMP E


SELECT AVG(E.COMM) ,COUNT(E.COMM),SUM(E.COMM)
FROM EMP E

SELECT AVG(NVL(E.COMM,0))
FROM EMP E
完整的SELECT查询语句的语法及执行顺序
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
在整个语句执行的过程中，首先执行的是WHERE语句，
对表中的数据进行过滤；符合条件的数据通过GROUP BY
进行分组；分组的数据通过HAVING子句进行组函数过滤；
最终的结果通过ORDER BY 进行排序，排序后的结果返
回给客户


SELECT AVG(E.SAL) AS "AVG"
FROM EMP E
GROUP BY E.DEPTNO
--ORDER BY "AVG"/“DESC”


SELECT E.DEPTNO,E.JOB ,SUM(E.SAL)
FROM EMP E
GROUP BY E.DEPTNO,E.JOB


SELECT E.DEPTNO, COUNT(E.ENAME)
FROM EMP E
GROUP BY E.DEPTNO


SELECT E.DEPTNO,AVG(E.SAL)
FROM EMP E
WHERE AVG(E.SAL) > 3000
GROUP BY E.DEPTNO


SELECT E.DEPTNO, MAX(E.SAL)
FROM EMP E
GROUP BY E.DEPTNO
HAVING MAX(E.SAL) > 3000


SELECT E.JOB,SUM(E.SAL)
FROM EMP E
WHERE E.JOB IN ('CLERK','SALESMAN','MANAGER')
GROUP BY E.JOB
HAVING SUM(E.SAL) > 3000
ORDER BY SUM(E.SAL);

函数的嵌套
SELECT MAX(AVG(E.SAL))
FROM EMP E
GROUP BY E.DEPTNO ;

-- 查询所有的数据
SELECT * FROM EMP

QUERY查询语句
Oracle 8i以前的老标准的多表连接
 等值连接
 非等值连接
 外连接
 自连接

为了连接n个表，至少需要n-1个连接条件
当多个表中有重名列时，必须在列的名字前加上表名作为前缀，以便能够清晰的表明字段来自那个表

等值连接(＝)
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.ENAME='ALLEN';


SELECT E.ENAME,E.DEPTNO,D.DNAME,D.LOCATION_ID,L.LOCNAME
FROM EMP E,DEPT D,LOCATIONS L
WHERE E.DEPTNO=D.DEPTNO AND D.LOCATION_ID=L.LOCID AND E.ENAME='FORD';
非等值练级（<,>,<>,<=,>=,between\and,in,like）
SELECT E.EMPNO,E.SAL,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL


外连接（+）
--右连接，显示左边的表
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO (+);

--左连接，显示右边的表
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO ;
自连接
SELECT E.EMPNO,E.ENAME,M.EMPNO "经理编号",M.ENAME "经理名称"

FROM EMP E,EMP M

WHERE E.MGR = M.EMPNO ;

Oracle 9i以后的新标准多表连接
 CROSS　JOIN——它在两个表格中创建了一个笛卡尔积，就象是在Oracle8i中没写WHERE时一样
 NATURAL JOIN——这是一个很有用的Oracle9i的句法，它通过从WHERE子句中自动连接标准来改善SQL的稳定性。表示作自然连接
 USING子句——它可以通过名字来具体指定连接
 ON子句——这个句法允许在两个表中为连接具体指定列名
 LEFT OUTER JOIN——它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回空
 RIGHT OUTER JOIN——它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回空
 FULL OUTER JOIN——它返回的是两个表格中所有的行，用空填满每一个空格。这在Oracle8i中则没有相应的此种句法

交叉连接（cross join）
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
CROSS JOIN DEPT D;

自然连接（natural join）
SELECT E.ENAME,DEPTNO,D.DNAME
FROM EMP E
NATURAL JOIN DEPT D;


SELECT D.DEPTNO,D.DNAME,L.LOCNAME
FROM DEPT D
NATURAL JOIN LOCATIONS L ;

INNER内链接(inner join..on…)即等值连接
SELECT E.EMPNO,E.ENAME, D.DEPTNO,D.DNAME,D.LOCATION_ID,L.LOCNAME
FROM DEPT D
INNER JOIN LOCATIONS L ON(D.LOCATION_ID=L.LOCID)
INNER JOIN EMP E ON(E.DEPTNO=D.DEPTNO) ;


Join..using
SELECT E.EMPNO,E.ENAME,DEPTNO,D.DNAME
FROM EMP E
JOIN DEPT D USING(DEPTNO);
左外连接（left outer join...on..）

SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

右外连接（right outer join ..on..）
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
right OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

全外连接(full outer join)

SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
FULL OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

联合查询（Union）
--去掉重复的记录

SELECT D.DEPTNO, D.DNAME,D.LOCATION_ID FROM DEPT D
UNION
SELECT BK.DEPTNO , BK.DNAME,BK.LOCATION_ID FROM DEPT_BK BK;

--UNION 不去掉重复的记录

SELECT D.DEPTNO, D.DNAME,D.LOCATION_ID FROM DEPT D
UNION ALL
SELECT BK.DEPTNO , BK.DNAME,BK.LOCATION_ID FROM DEPT_BK BK;


子查询
 为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）称为子查询
 可以给自查询起个别名：
单行子查询（<,>,<>,<=,>=,between\and,in,like）

SELECT E.*
FROM EMP E
WHERE E.SAL < (SELECT SAL FROM EMP WHERE ENAME='ALLEN');


SELECT E.EMPNO,E.ENAME,E.JOB
FROM EMP E
WHERE E.JOB = ( SELECT E.JOB
FROM EMP E
WHERE E.EMPNO=7566
) ;


SELECT E.ENAME,E.SAL
FROM EMP E
WHERE E.SAL > (SELECT MIN(E.SAL)
FROM EMP E
GROUP BY DEPTNO
);


SELECT E.EMPNO,E.ENAME,E.JOB
FROM EMP E
WHERE E.JOB = (SELECT E.JOB
FROM EMP E
WHERE E.EMPNO=8566
) ;
多行子查询（in,all,any）
--in
SELECT E.*
FROM EMP E
WHERE E.JOB IN (SELECT SAL FROM EMP WHERE SAL >2000 )


< ALL 是小于子查询的最小值
-- > ALL 大于子查询的最大值
SELECT E.SAL
FROM EMP E
WHERE E.SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO=30)


< ANY 是小于子查询的最大值
-- > ANY 大于子查询的最小值
SELECT E.SAL
FROM EMP E
WHERE E.SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO=30)


SELECT E.DEPTNO,MIN(E.SAL)
FROM EMP E
GROUP BY E.DEPTNO
HAVING MIN(E.SAL) > (SELECT MIN(E.SAL)
FROM EMP E
WHERE E.DEPTNO='20' );


SELECT * FROM DEPT
FOR UPDATE


DML记录操作语句(insert,delete,update,merge)
INSERT（插入记录）
INSERT INTO DEPT (DEPTNO,DNAME,LOCATION_ID) VALUES(41,'技术部',1000);

INSERT INTO DEPT (DEPTNO,DNAME,LOCATION_ID) VALUES(42,'软件部',null);

INSERT INTO DEPT (DEPTNO,DNAME) VALUES(43,'软件一部');

INSERT INTO DEPT_BK VALUES (44,'人事一部',1000);
DELETE（删除记录）
DELETE [FROM] DEPT_BK WHERE DEPTNO > 40;

DELETE EMP;//删除所有的记录

INSERT INTO DEPT_BK SELECT * FROM DEPT;

UPDATE（修改记录）
UPDATE DEPT SET DNAME='人事部',LOCATION_ID = 1000 WHERE DEPTNO=30;

SELECT * FROM DEPT_BK;

INSERT INTO EMP (EMPNO,HIREDATE) VALUES (8899,'12-6月-1984');
INSERT INTO EMP (EMPNO,HIREDATE) VALUES (8890, TO_DATE('1980-12-23','YYYY-MM-DD'));

MERGE
根据条件在表中执行修改或插入数据的功能，如果插入的数据行在目的表中存在就执行UPDATE，如果是不存在则执行INSERT:

MERGE INTO DEPT_BK D
USING DEPT S ON(D.DEPTNO=S.DEPTNO)
WHEN MATCHED THEN
UPDATE SET D.DNAME = S.DNAME,D.LOCATION_ID = S.LOCATION_ID
WHEN NOT MATCHED THEN
INSERT VALUES (S.DEPTNO,S.DNAME,S.LOCATION_ID);
事物控制语句（commit/rollback）

INSERT INTO DEPT VALUES (50,'开发',NULL);

SAVEPOINT A;

DELETE FROM DEPT D WHERE D.DEPTNO > 30;

SAVEPOINT B;

UPDATE DEPT D SET D.DNAME='人事部' where d.deptno=10;

SAVEPOINT C;

ROLLBACK TO B;

COMMIT;

ROLLBACK TO A;


DDL语句 （create/drop/alter）

drop（表）

DROP TABLE TEST;

create （表）

CREATE TABLE TEST(
N1 NUMBER(4),
N2 NUMBER(4,2),
DATE1 DATE
);
子查询建表（AS）

CREATE TABLE EMP_BK
AS
SELECT * FROM EMP;


CREATE TABLE DEPT_BK
(D_ID,D_NAME)
AS
SELECT DEPTNO,DNAME FROM DEPT;

alter（表字段）
增加字段（add）
ALTER TABLE TEST ADD SEX CHAR(1);

修改字段(modify)
ALTER TABLE TEST MODIFY SEX char(10) default '男' ;

删除字段（drop）

ALTER TABLE emp DROP COLUMN AGE;

truncate(清除表中所有的记录)
--是DDL语句，效率高，不可以回滚，而DELETE语句可以ROLLBACK

TRUNCATE TABLE TEST;

rename(改变对象名称)
--对象的所有者才能修改对象的名字

RENAME TEST_1 TO TEST;

完整性约束和数据对象
对象名称 描述
表 基本的数据存储对象，以行和列的形式存在
约束 执行数据校验，保证数据完整性的对象
视图 一个或多个表数据的显示
索引 用于提高查询的速度
同义词 对象的别名


Oracle 支持下面五类完整性约束:
NOT NULL 非空
UNIQUE Key 唯一键
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 检察
非空、惟一性约束（not null / unique）
CREATE TABLE STUDENT(
ID NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) CONSTRAINTS NAME_NN NOT NULL,
EMAIL_1 VARCHAR2(50) ,
EMAIL_2 VARCHAR2(50) ,
CONSTRAINTS EMAIL_UNIQUE UNIQUE (EMAIL_1,EMAIL_2)
);

联合主键约束(primary key)
CREATE TABLE STU(
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
CONSTRAINTS STU_PK PRIMARY KEY (FIRST_NAME,LAST_NAME)
)


外键约束(foreign key)

--在外键约束下,在建表的时候,先建主表,然后建立字表
CREATE TABLE DEPT_1(
DEPT_ID NUMBER(4) PRIMARY KEY,
DEPT_NAME VARCHAR2(20)
);

CREATE TABLE EMP_1(
E_ID NUMBER(4) PRIMARY KEY,
E_NAME VARCHAR2(20),
D_ID NUMBER(4),
CONSTRAINTS DEPT_1_EMP_1_FK FOREIGN KEY (D_ID) REFERENCES DEPT_1 (DEPT_ID)
);


--在外键约束下,在删除表的时候,先删除子表,然后删词主表
DROP TABLE EMP_1;
DROP TABLE DEPT_1;

--在外键约束下,在添加数据的时候,先添加主表的数据,在添加字表的数据
INSERT INTO EMP_1 VALUES (4000,'张三',1000);
INSERT INTO EMP_1 VALUES (4001,'张四',1000);
INSERT INTO EMP_1 VALUES (4002,'张五',null);
INSERT INTO DEPT_1 VALUES (1000,'人事部');

SELECT * FROM EMP_1;
SELECT * FROM DEPT_1;

--在外键约束下,在删除数据的时候,先删除字表的数据,在删除主表的数据
DELETE FROM EMP_1 ;
DELETE FROM DEPT_1;

check约束
CREATE TABLE EMPL(
E_ID NUMBER PRIMARY KEY,
E_NAME VARCHAR2(20) NOT NULL,
E_SEX VARCHAR2(4) ,
E_SAL NUMBER(8,2),
E_GRADE VARCHAR2(30) NOT NULL,
CONSTRAINT EMPL_MIN_SAL CHECK (E_SAL >0),
CONSTRAINT AVAL_SEX CHECK (E_SEX IN ('男','女'))
--CHECK (E_SEX IN ('男','女'))
);

INSERT INTO EMPL VALUES (1,'张三','男',2000);
INSERT INTO EMPL VALUES (2,'张四','女',-22000);
INSERT INTO EMPL VALUES (3,'张五','女',-100);


--可增加或删除约束，但不能直接修改
--增加约束
Alter Table emp_s
Add Constraint email_uk Unique (email);

--删除约束
ALTER TABLE DEPT_1 DROP PRIMARY KEY CASCADE;


索引（Index）
1. 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.

CREATE INDEX EMP_INDEX_ENAME
ON EMP (ENAME);

视图（view）
--在CREATE VIEW 语句中字段与子查询中的字段必须匹配.

CREATE OR REPLACE VIEW V_EMP_SAL
AS
SELECT E.EMPNO,E.ENAME,E.SAL
FROM EMP E
WHERE E.ENAME IS NOT NULL
WITH READ ONLY;

--通过设置WITH READ ONLY选项可以禁止对视图执行DML操作.
CREATE OR REPLACE VIEW V_EMP_INFO
AS
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE
FROM EMP E
WHERE E.ENAME IS NOT NULL

--删除视图(drop)
DROP VIEW V_EMP_SAL;

INSERT INTO V_EMP_SAL VALUES (9003,'AAA','2000.00');


TOP-N ( rownum / rowid )和行内视图
--rownum来说它是oracle系统顺序分配为从查询返回的行的编号
--rowid是物理地址，用于定位oracle中具体数据的物理存储位置

SELECT ROWNUM ,ENAME,SAL
FROM (SELECT E.ENAME,E.SAL
FROM EMP E
WHERE E.ENAME IS NOT NULL AND E.SAL IS NOT NULL
ORDER BY E.SAL ASC)
WHERE ROWNUM <=3 ;


SELECT ROWNUM ,ENAME, HIREDATE
FROM (SELECT E.ENAME,E.HIREDATE
FROM EMP E
WHERE E.ENAME IS NOT NULL AND E.SAL IS NOT NULL
ORDER BY E.HIREDATE ASC)
WHERE ROWNUM <=3 ;
--给rownum起个别名可以对其进行大于（>）操作
SELECT aa ,ENAME, HIREDATE
FROM (SELECT rownum aa,E.ENAME,E.HIREDATE
FROM EMP E
WHERE E.ENAME IS NOT NULL AND E.SAL IS NOT NULL
ORDER BY E.HIREDATE ASC)
WHERE aa>3and aa<5;

PL/SQL程序块
主要有四类：
过程 执行特定操作
函数 进行复杂计算,返回计算的结果
包 将逻辑上相关的过程和函数组织在一起
触发器 事件触发，执行相应操作


PL/SQL子程序
PL/SQL子程序主要有两种类型
1. 存储过程(PROCEDUER):用来完成某些操作的任务
2. 函数(FUNCTION):用来作复杂的计算
PL/SQL子程序,是基于PL/SQL块的结构的,只是比PL/SQL块多了子程序头部的定义.使用PL/SQL子程序,使的PL/SQL程序易于维护,而且可以重复的使用
声明部分不再以DECLARE作为开头,而是以IS开始，不需要再使用DECLARE开始声明部分,IS就表示了声明部分的开始

匿名块
语法：
DECLARE
变量、常量声明;
BEGIN
pl/sql_block;
EXCEPTIONS
异常捕捉;
END;


1.使用SELECT 命令，可以从数据库中取出单行数据,语法略有变化select..into
2.使用DML(insert/delete/update)命令，修改数据库中的行,没有变化
3.通过EXECUTE IMMEDIATE，执行DDL和DCL语句

declare
v_1 varchar2(20) ;
begin
--v_1 := USER||': '||TO_CHAR(SYSDATE);

v_1 := USER||': '||SYSDATE;
dbms_output.put_line(v_1);
end;
%type/%rowtype

declare
v_id employees.empl_id%type := 1005;
v_hiredate employees.hire_date%type := '23-4月-1978';
v_sal employees.salary%type := 3000;
v_deptid employees.department_id%type := 2000;
BEGIN
insert into employees values(v_id,v_hiredate,v_sal,v_deptid);
commit;
END;


declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where empl_id = 1003;
dbms_output.put_line(v_emp.empl_id || '----' || v_emp.hire_date ||
'----' || v_emp.salary || '----' ||
v_emp.department_id);
end;


嵌套块
declare
v_weight number(3) := 100;
begin
-----------------嵌套块---------------------
declare
v_weight number(3) := 1;
begin
v_weight := v_weight +1;
dbms_output.put_line('在嵌套块中v_weight的值是:'|| v_weight);
end;
--------------------------------------------
v_weight := v_weight +1;
dbms_output.put_line('在嵌套块外v_weight的值是:' || v_weight);
end;
select..into
declare
v_hire_date employees.hire_date%type;
v_department_id employees.department_id%type;
begin
select hire_date,department_id into v_hire_date,v_department_id
from employees where empl_id =1000;
dbms_output.put_line (v_hire_date ||'----' || v_department_id );
end;

declare
v_avg_salary employees.salary%type;
begin
select avg(salary) into v_avg_salary from employees;
dbms_output.put_line('平均工资是' || ':' || v_avg_salary);
end;


使用EXECUTE IMMEDIATE执行DDL语句
begin

execute immediate 'create table temp(
id number(4) primary key,
name varchar2(20)
)';
execute immediate 'drop table temp';
end;


流程控制语句
条件控制结构(IF语句)
循环控制语句
基本循环
FOR循环
WHILE循环
EXIT语句

if-then..elsif-then..else..
declare
v1 number(4) := 100;
v2 number(4) := 101;
v3 boolean := (v1=v2);
begin
if(v3) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;


declare
V_SAL EMP.SAL%TYPE;
begin
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = 7698;
IF (V_SAL < 1000) THEN
UPDATE EMP SET COMM = V_SAL * 0.8 WHERE EMPNO = 7698;
ELSIF (V_SAL < 2000) THEN
UPDATE EMP SET COMM = V_SAL * 1.0 WHERE EMPNO = 7698;
ELSE
UPDATE EMP SET COMM = V_SAL * 1.2 WHERE EMPNO = 7698;
END IF;
COMMIT;
end;

循环语句
简单循环(loop..exit when..end loop;)
FOR 循环(for index in lower_bound..upper_bound LOOP....end loop; )
WHILE 循环(while..loop..end loop;)


loop简单循环

BEGIN
DELETE FROM TEST;
FOR V_COUNT IN REVERSE 1 .. 10 LOOP
INSERT INTO TEST VALUES (V_COUNT, 'aaaa');
END LOOP;
COMMIT;
END;


DROP TABLE TEST;
CREATE TABLE TEST(
ID NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(10)
);

DECLARE
V_COUNT NUMBER(2) := 0;
BEGIN
DELETE FROM TEST;
LOOP
INSERT INTO TEST VALUES (V_COUNT, 'AAAA');
V_COUNT := V_COUNT + 1;
EXIT WHEN V_COUNT >= 10;
END LOOP;
COMMIT;
END;


DECLARE
V_COUNT NUMBER(3) := 0;
BEGIN
DELETE FROM TEST;
LOOP
INSERT INTO TEST VALUES (V_COUNT, 'bbb');
V_COUNT := V_COUNT + 1;
exit when v_count<10;
END LOOP;
COMMIT;
END;
for循环

BEGIN
DELETE FROM TEST;
FOR V_COUNT IN REVERSE 1 .. 10 LOOP
INSERT INTO TEST VALUES (V_COUNT, 'aaaa');
END LOOP;
COMMIT;
END;

WHILE循环
DECLARE
V_COUNT NUMBER(3) := 0;
BEGIN
DELETE FROM TEST;
WHILE V_COUNT < 10 LOOP
INSERT INTO TEST VALUES (V_COUNT, 'bbb');
V_COUNT := V_COUNT + 1;
END LOOP;
COMMIT;
END;


SQL 游标
隐式游标(SQL%ROWCOUNT /SQL%FOUND/SQL%NOTFOUND/SQL%ISOPEN)

显式游标(%ROWCOUNT /%FOUND/%NOTFOUND/%ISOPEN)

隐式SQL 游标
DECLARE
V_COUNT NUMBER(3);
BEGIN
DELETE FROM EMP E WHERE E.DEPTNO = 30;
V_COUNT := SQL%ROWCOUNT;
DBMS_OUTPUT.put_line('总共删除数据：' || v_count || ' 条');
COMMIT;
END;
/

显式游标
--取一条
DECLARE
V_EMP_RECORD EMP%ROWTYPE;
CURSOR EMP_CUR IS
SELECT * FROM EMP;
BEGIN
OPEN EMP_CUR;
FETCH EMP_CUR
INTO V_EMP_RECORD;
DBMS_OUTPUT.put_line(V_EMP_RECORD.EMPNO || V_EMP_RECORD.ENAME);
CLOSE EMP_CUR;
END;
/

--loop
DECLARE
V_EMP_RECORD EMP%ROWTYPE;
CURSOR EMP_CUR IS SELECT * FROM EMP;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO V_EMP_RECORD;
EXIT WHEN EMP_CUR%NOTFOUND;
DBMS_OUTPUT.put_line('----'||V_EMP_RECORD.EMPNO ||V_EMP_RECORD.ENAME);
END LOOP;


CLOSE EMP_CUR;
DBMS_OUTPUT.put_line('总共有数据:' ||EMP_CUR%rowcount );
END;
/
--while
DECLARE
V_EMP_RECORD EMP%ROWTYPE;
CURSOR EMP_CUR IS
SELECT * FROM EMP;
BEGIN
OPEN EMP_CUR;
FETCH EMP_CUR
INTO V_EMP_RECORD;
WHILE EMP_CUR%FOUND LOOP
DBMS_OUTPUT.put_line(V_EMP_RECORD.EMPNO || V_EMP_RECORD.ENAME);
FETCH EMP_CUR
INTO V_EMP_RECORD;
END LOOP;
DBMS_OUTPUT.put_line('共有数据：' || EMP_CUR%ROWCOUNT);
CLOSE EMP_CUR;
END;
/


--for循环
DECLARE
CURSOR EMP_CUR IS SELECT * FROM EMP ;
BEGIN
FOR V_EMP IN EMP_CUR LOOP //不用声明V_EMP
DBMS_OUTPUT.put_line(V_EMP.EMPNO|| V_EMP.ENAME);
END LOOP;
END;


--带参数的游标
 在调用时，通过给定不同的参数得到不同的结果集


DECLARE
V_EMP_RECORD EMP%ROWTYPE;
CURSOR EMP_CUR( V_DEPTNO NUMBER ) IS SELECT * FROM EMP
WHERE DEPTNO=V_DEPTNO;
BEGIN
OPEN EMP_CUR(30);
LOOP
FETCH EMP_CUR INTO V_EMP_RECORD;
EXIT WHEN EMP_CUR%NOTFOUND;
DBMS_OUTPUT.put_line(V_EMP_RECORD.EMPNO ||
V_EMP_RECORD.ENAME);
END LOOP;
CLOSE EMP_CUR;
END;

-- WHERE CURRENT OF
DECLARE
CURSOR EMP_CUR IS
SELECT EMPNO, SAL FROM EMP WHERE COMM IS NULL FOR UPDATE;
V_COMM NUMBER(8, 2);
BEGIN
FOR V_EMP_RECORD IN EMP_CUR LOOP
IF V_EMP_RECORD.SAL < 1000 THEN
V_COMM := V_EMP_RECORD.SAL * 0.15;
ELSIF V_EMP_RECORD.SAL < 2000 THEN
V_COMM := V_EMP_RECORD.SAL * 0.25;
ELSIF V_EMP_RECORD.SAL < 3000 THEN
V_COMM := V_EMP_RECORD.SAL * 0.30;
ELSE
V_COMM := V_EMP_RECORD.SAL * 0.35;
END IF;
UPDATE EMP SET COMM = V_COMM WHERE CURRENT OF EMP_CUR;
END LOOP;
COMMIT;
END;


存储过程（procedure..is..begin..end;）
-- 准备数据
drop table empl;
create table empl(e_id number(5), e_name varchar2(20), e_salary number(8,2) );

创建存储过程
--
drop procedure insert_empl;
CREATE OR REPLACE PROCEDURE insert_empl(V_ID [in] NUMBER,
V_NAME VARCHAR2,
V_SAL IN NUMBER) IS
v_1 number(4);--声明的变量
BEGIN
v_1 := 1000;
INSERT INTO EMPL VALUES (V_ID, V_NAME, V_SAL);
COMMIT;
DBMS_OUTPUT.put_line('数据插入成功！' || v_1);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('发生异常');
END;

-- 执行存储过程
INSERT_EMPL(1,'AAA',3300); -- 在command窗口指向

BEGIN
INSERT_EMPL(2,'BBB',4000);
END;

不同参数模式的存储过程

IN OUT IN-OUT
默认 必须指定 必须指定
值被：
传递给子程序 返回到调用环境 传递给子程序，返回到调用环境
参数形式：
常量 未初始化的变量 初始化的变量.
可以是表达式， 必须是一个变量 必须是一个变量
常量，或者是
初始化的变量


create or replace procedure pararm_test(v_in in varchar2,
v_out out varchar2,
v_in_out in out varchar2) is
v_localtion varchar2(20);
begin
v_localtion := v_in;
dbms_output.put_line(v_in_out);
v_out := '从存储过程中返回的'; ----out类型的--- 参数’;
v_in_out := '从存储过程中返回的' ;----int_out类型的--- 参数’;
dbms_output.put_line(v_in);
end;


/ --调用存储过程
set serveroutput on; --在command windows 执行dbms_output.put_line语句中可以打印出来数据

declare
v_in_par varchar2(20) := 'in类型参数';
v_in_out_par varchar2(255);
v_out_par varchar2(255) := 'in_out类型参数';-- 也可以不初始化
begin
pararm_test(v_in_par, v_out_par, v_in_out_par);
dbms_output.put_line(v_in_out_par);
dbms_output.put_line(v_out_par);
end;

2.

CREATE OR REPLACE PROCEDURE insert_out(v_name varchar2,
v_age integer,
v_sex char,
mes in out varchar2,flag out varchar2) IS
in_out varchar2(20);
BEGIN
INSERT INTO STUDENT VALUES (STU_PK.NEXTVAL, v_name, v_age, v_sex);
in_out := mes;
mes := '已经插入成功';
dbms_output.put_line(in_out);
flag := '已经返回';
COMMIT;
END;

declare
in_out_mes varchar2(20) := '已经传值进去了';
out_flag varchar2(10);
begin
insert_out('rrr',13,'女',in_out_mes,out_flag);
dbms_output.put_line(in_out_mes);
dbms_output.put_line(out_flag);
end;

银行转账业务的存储过程：
Create or replace procedure change_count(id1 in number,
id2 in number,
money in number,
out_ret out varchar2) is
id1_exist number;
id2_exist number;
id1_price_temp counts.price%type;
id2_price_temp counts.price%type;
begin
select count(*) into id1_exist from counts where id = id1;
select count(*) into id2_exist from counts where id = id2;
if (id1_exist = 1 and id2_exist = 1) then
select price into id1_price_temp from counts where id = id1;
select price into id2_price_temp from counts where id = id2;
if (id1_price_temp >= money) then
update counts set price = id1_price_temp - money where id = id1;
update counts set price = id2_price_temp + money where id = id2;
commit;
else
--转出帐号余额不足
out_ret := '400';
end if;
else
--id1 或者id2帐号不存在
out_ret := '300';
end if;
end change_count;


函数（function）
--创建
CREATE OR REPLACE FUNCTION tax
(v_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF v_value < 1000 THEN
RETURN (v_value * .10);
ELSE
RETURN (v_value * .15);
END IF
END tax;

--调用
DECLARE
V_VALUES_OUT NUMBER(10);
BEGIN
V_VALUES_OUT := tax(10000);
dbms_output.put_line(V_VALUES_OUT);
END;
/

SELECT sal, tax(SAL) as "税金" FROM EMP;


包（package）

-----------包的声明和使用1-------------------------
DROP TABLE employees;
CREATE TABLE employees(
id number(5) primary key,
name varchar2(30),
commission_pct number(3,2 )
);

insert into employees values (1,'张一',0.13);
insert into employees values (2,'张二',0.23);
insert into employees values (3,'张三',0.33);
insert into employees values (4,'张四',0.43);
commit;

select * from employees;

--创建包头
CREATE OR REPLACE PACKAGE comm_package IS
g_comm NUMBER := 0.10;
PROCEDURE reset_comm (p_comm IN NUMBER);
END comm_package;
/

--创建包体
CREATE OR REPLACE PACKAGE BODY comm_package
IS


-------------在包体中定义的局部函数--------------
/*
如果输入的参数p_comm大于employees表中最大的commission_pct
字段,则函数返回FALSE,否则函数返回TRUE
*/
FUNCTION validate_comm (p_comm IN NUMBER)
RETURN BOOLEAN
IS
v_max_comm NUMBER;
BEGIN
SELECT MAX(commission_pct)
INTO v_max_comm
FROM employees;
IF p_comm > v_max_comm THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF;
END validate_comm;
---------在包体中定义的局部函数:结束------------

------------完成在包体中声明的过程--------------
PROCEDURE reset_comm (p_comm IN NUMBER)
IS
BEGIN
IF validate_comm(p_comm) THEN
g_comm:=p_comm;
ELSE
RAISE_APPLICATION_ERROR(-20210, '不合理的表达式');
END IF;
END reset_comm;
----------完成在包体中声明的过程:结束------------

END comm_package;

--测试包
EXECUTE comm_package.reset_comm(1);
EXECUTE comm_package.reset_comm(.33);


-- 测试包里定义的公共变量
begin
comm_package.reset_comm(0.15);
dbms_output.put_line('g_comm = ' || comm_package.g_comm );
end;
/


--以scott登陆,测试包
EXECUTE test.comm_package.reset_comm(0.15);
begin
test.comm_package.reset_comm(0.15);
dbms_output.put_line('g_comm = ' || test.comm_package.g_comm );
end;
/


-----------包的声明和使用2-------------------------
drop table employee;
create table employee(
id number (5),
name varchar2(30),
salary number(8,2)
);

insert into employee values(1,'张一',3000);
insert into employee values(2,'张二',3400);
insert into employee values(3,'张三',5600);
commit;

create or replace package tax_pkg as
function tax(v_value in number) return number;
end tax_pkg;
/

create or replace package body tax_pkg
as
-------------包体中的函数执行部分---------------
function tax(v_value in number) return number
is
begin
if v_value < 1000 then
return (v_value * 0);
elsif v_value < 5000 then
return (v_value * 0.10);
elsif v_value <10000 then
return (v_value * 0.15);
else
return (v_value * 0.20);
end if;
end tax;
-------------包体中的函数:结束-----------------
end tax_pkg;
/

-- 测试包中定义的函数
select salary,tax_pkg.tax(salary) from employee;
--删除包
包头和包体是两种数据库对象，可以独立的存在
在删除时，可以分别删除；在删除包体时，包头不会受到影响。但是在删除包头时，相关的包体也会被删除
DROP PACKAGE packae_name;
DROP PACKAGE BODY packae_name;


触发器（trigger）
在Oracle数据库中主要有二种触发器类型：
DML触发器(INSERT，UPDATE，DELETE三种触发器)
触发的时机包括：对表来说有before或aftet触发，对视图来说有INSTEAD OF

系统触发器

对表操作时间限制的触发器

CREATE OR REPLACE TRIGGER secure_emp_1 --这里不能有IS
BEFORE INSERT ON employees -- 这里没有分号
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('STA','SUN') OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00' )) THEN
RAISE_APPLICATION_ERROR(-20500,'你只能在工作时间对表进行操作');
END IF;
END;

语句级DML触发器
CREATE OR REPLACE TRIGGER secure_emp_2
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
--如果当前时间是周六或周日 或者时间不在8:00-18:00之间
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR(-20501, '你只能在工作时间删除员工表的数据');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR(-20500, '你只能在工作时间插入员工表的数据.');
ELSIF UPDATING('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503, '你只能在工作时间更新员工表的数据');
ELSE
RAISE_APPLICATION_ERROR(-20504, '你只能在工作事件操作员工表的数据.');
END IF;
END IF;
END;
行级DML触发器（for each row）
FOR EACH ROW：表明对表中的每行数据操作时都会处分这个触
发器REFERENCING子句是说明触发器替换值的前缀名，默认替换
前的前缀名为old，替换后的前缀名为NeW。也可以自己声明替换前
后变量的的前缀规则
2.
 在行级触发器中，可以通过old和new这两种前缀来引用DML操作前后的两种值。
 在插入事件中可以使用new，但是不能使用old
 在更新时间中可以使用old来引用老的书籍，使用new来引用新的数据
 在删除时间中，只能使用old前缀


--1、编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况--

CREATE OR REPLACE TRIGGER EMP_SAL
AFTER UPDATE OF SAL ON EMP FOR EACH ROW--语句级触发器
BEGIN
DBMS_OUTPUT.put_line(:OLD.ENAME||'更新前的工资为：'||:OLD.SAL||'更新后的工资情况为：'||:NEW.SAL);
END;

UPDATE EMP E SET E.SAL=2200 WHERE E.JOB='MANAGER' ;--只有在update sal的时候才会触动触发器

UPDATE EMP E SET E.EMPNO=11 WHERE E.ENAME='CLACK' ;--不会触动触发器

SELECT * FROM EMP;

--2、编写一个数据库触发器，它允许用户只在上午9:00到下午5:00之间执行dml任务-----
CREATE OR REPLACE TRIGGER EMP_DML
BEFORE INSERT OR DELETE OR UPDATE ON EMP
FOR EACH ROW
DECLARE—-可以出现declare 语句
V_TIME VARCHAR2(10);
BEGIN
V_TIME := TO_CHAR(SYSDATE, 'HH24');
IF (TO_NUMBER(V_TIME) < 9 OR TO_NUMBER(V_TIME) > 17) THEN
raise_application_error(-20008, '现在不允许执行DML任务');--编码可变
END IF;
END;

DELETE FROM EMP;

--3、编写一个数据库触发器，当任何时候某个部门从"dept"中删除时，
-----该触发器将从"emp"表中删除该部门的所有雇员

create or replace trigger dept_emp_delete
before delete on dept
for each row
declare
d_depno dept.deptno%type;
begin
d_depno :=:old.deptno;
delete from emp where emp.deptno=d_depno;
end;

delete from dept where dept.deptno=20;
instead of 对视图操作
create or replace trigger new_view_emp_dept
instead of insert on v_empl for each row—视图
begin
if inserting then
insert into departments values(:new.department_id,:new.dept_name);
insert into employees –转换成对表的操作
values(:new.employee_id,:new.employee_name,:new.department_id);
end if;
end;
管理触发器


 启用或者禁用某个触发器
 ALTER TRIGGER trigger_name DISABLE | ENABLE

 启用或者禁用某个对象上的所有触发器
 ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

 重编译触发器
 ALTER TRIGGER trigger_name COMPILE


数据库系统级触发器（登录/退出触发器）
CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging on');
END;


CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging off');
END;


PL/SQL 中的异常处理
捕获异常：语法
1.预定义的oracle 数据库错误
2.非预定义的oracle 数据库错误
3.用户定义的错误


EXCEPTION
WHEN exception1 THEN..WHEN OTHERS THEN..

预定义异常
 预定义异常就是Oracle中已经预先定义好名称的异常

DECLARE
V_EMP_RECORD EMP%ROWTYPE;
V_1 NUMBER(4,2);

BEGIN
V_1 := 12; --这里会发生一个异常
SELECT * INTO V_EMP_RECORD FROM EMP WHERE DEPTNO=10; --这里会发生一个数据过多的异常 ，但不被执行
DBMS_OUTPUT.put_line('---------------------');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('没有合适的数据异常');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('数据过多的异常');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.put_line('赋值异常');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('遇到其他的异常');
END;
非预定义异常
 为了捕获非预定义的异常,必须先创建一个异常名称,然后将错误编号和刚创建的异常关联起来

DECLARE
V_EMP_REMAINING EXCEPTION;
PRAGMA EXCEPTION_INIT(V_EMP_REMAINING, -02292);
BEGIN
DELETE FROM DEPT WHERE DEPTNO =20;
EXCEPTION
WHEN V_EMP_REMAINING THEN
DBMS_OUTPUT.put_line('违反完整性约束');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('其他错误');
END;

用户定义异常(raise exception)
 而用户定义异常是对数据库的操作不符合用户的业务时,人为定义的异常.这类异常不是数据库的错误,所以没有对应的错误代码.而且数据库在执行时不会主动的认为是异常.


drop table sm_emp;
CREATE TABLE sm_emp(
no char(4),
name char(10),
salary number(6,2),
phone char(8)
);

--insert TOM
INSERT INTO sm_emp VALUES ('001','TOM',999.99,'62543678');
INSERT INTO sm_emp VALUES ('002','TOM2',999.99,'62543678');
INSERT INTO sm_emp VALUES ('003','TOM3',999.99,NULL);

commit;

--如果用户的电话为null,则认为发生一个异常

DECLARE
CURSOR C_SM IS
SELECT * FROM SM_EMP;
PHONE_EXCEPTION EXCEPTION;
V_NAME SM_EMP.NAME%TYPE;
BEGIN
FOR V_EMP IN C_SM LOOP
IF (V_EMP.PHONE IS NULL) THEN
V_NAME := V_EMP.NAME;
RAISE PHONE_EXCEPTION;
END IF;
END LOOP;
EXCEPTION
WHEN PHONE_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE(V_NAME || '的电话不能为空');
END;

--为了能够记录发生的异常信息，Oracle提供了两个函数
1.SQLCODE
返回错误代码，NUMBER类型
2. SQLERRM
返回与错误代码关联的消息；VARCHAR2类型

建立一个错误日志表(SQLERRM\ SQLCODE)

DROP TABLE ERR_LOG;

CREATE TABLE ERR_LOG (
CODE NUMBER(10),
MESSAGE VARCHAR2(255),
ERRDATE DATE
);

drop table test;
create table test (
id number(5) primary key ,
name varchar2(20),
salary number(8,2)
);

DECLARE
V_CODE ERR_LOG.CODE%TYPE;
V_MESSAGE ERR_LOG.MESSAGE%TYPE;
V_DATE ERR_LOG.ERRDATE%TYPE;
BEGIN
INSERT INTO TEST VALUES (1, 'AAA', 3000);
INSERT INTO TEST VALUES (1, 'BBB', 2000); --这里将会出现主键重复异常
EXCEPTION
WHEN OTHERS THEN
V_CODE := SQLCODE;
V_MESSAGE := SQLERRM;
V_DATE := SYSDATE;
INSERT INTO ERR_LOG VALUES (V_CODE, V_MESSAGE, V_DATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('发生错误');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

SELECT * FROM DEPT;
SELECT * FROM ERR_LOG;