开发中快速查找的好帮手，覆盖面全，查找方便，有实例：
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;
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;
