oracle基本知识
1. sql*plus的常用命令
1.1. 连接命令
1.conn[ect]
用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper
2.disc[onnect]
说明: 该命令用来断开与当前数据库的连接
3.passw[ord]
说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
4.show user
说明: 显示当前用户名
5.exit
说明: 该命令会断开与数据库的连接，同时会退出sql*plus
1.2. 文件操作命令
1.start和@
说明: 运行sql脚本
案例: sql>@ d:\a.sql或是sql>start d:\a.sql
2.edit
说明: 该命令可以编辑指定的sql脚本 案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开 3.spool
说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d:\b.sql 并输入 sql>spool off
1.3. 交互式命令
1.&
说明：可以替代变量，而该变量在执行时，需要用户输入。 select * from emp where job='&job'；
2.edit
说明：该命令可以编辑指定的sql脚本 案例：SQL>edit d:\a.sql
3.spool
说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 spool d:\b.sql 并输入 spool off
1.4. 显示和设置环境变量
概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
1.linesize
说明：设置显示行的宽度，默认是80个字符
show linesize
set linesize 90
2.pagesize
说明：设置每页显示的行数目，默认是14 用法和linesize一样 至于其它环境参数的使用也是大同小异
2. oracle用户管理
2.1. 创建用户
概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。
create user 用户名 identified by 密码;
2.2. 给用户修改密码
概述：如果给自己修改密码可以直接使用 password 用户名 如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
SQL> alter user 用户名 identified by 新密码
2.3. 删除用户
概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。
drop user 用户名 【cascade】
在删除用户时，注意： 如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade;
2.4. 用户管理案例
概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。 为了给讲清楚用户的管理，这里我给大家举一个案例。
SQL> conn xiaoming/m12;
ERROR: ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied 警告: 您不再连接到 ORACLE。
SQL> show user; USER 为 "" SQL> conn system/p; 已连接。
SQL> grant connect to xiaoming; 授权成功。
SQL> conn xiaoming/m12; 已连接。
注意：grant connect to xiaoming;在这里，准确的讲，connect不是权限，而是角色。
现在说下对象权限，现在要做这么件事情：
希望xiaoming用户可以去查询emp表
grant select on emp to xiaoming
希望xiaoming用户可以去修改scott的emp表
grant update on emp to xiaoming
希望xiaoming用户可以去修改/删除，查询，添加scott的emp表
grant all on emp to xiaoming
scott希望收回xiaoming对emp表的查询权限
revoke select on emp from xiaoming //对权限的维护。
希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限继续给别人。
--如果是对象权限，就加入 with grant option grant
select on emp to xiaoming with grant option
我的操作过程：
SQL> conn scott/tiger; 已连接
SQL> grant select on scott.emp to xiaoming with grant option; 授权成功。
SQL> conn system/p; 已连接。
SQL> create user xiaohong identified by m123; 用户已创建。
SQL> grant connect to xiaohong; 授权成功。
SQL> conn xiaoming/m12; 已连接。
SQL> grant select on scott.emp to xiaohong; 授权成功。
--如果是系统权限。
system给xiaoming权限时：
grant connect to xiaoming with admin option
问题：如果scott把xiaoming对emp表的查询权限回收，那么xiaohong会怎样？
答案：被回收。
下面是我的操作过程：
SQL> conn scott/tiger; 已连接。
SQL> revoke select on emp from xiaoming; 撤销成功。
SQL> conn xiaohong/m123; 已连接。
SQL> select * from scott.emp; select * from scott.emp
第 1 行出现错误: ORA-00942: 表或视图不存在 结果显示：小红受到诛连了。。
3. oracle表的管理(数据类型，表创建删除，数据CRUD操作)
3.1. 表名和列的命名规则
· 必须以字母开头 长度不能超过30个字符 不能使用oracle的保留字 只能使用如下字符 A-Z，a-z，0-9，$,#等
3.2. oracle支持的数据类型
3.2.1. 字符类
char 定长 最大2000个字符。
例子：char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全 如‘小韩 ’
varchar2(20) 变长 最大4000个字符。
例子：varchar2（10） ‘小韩’ oracle分配四个字符。这样可以节省空间。
clob(character large object) 字符型大对象 最大4G
char 查询的速度极快浪费空间，查询比较多的数据用。
varchar 节省空间
3.2.2. 数字型
number范围 -10的38次方 到 10的38次方 可以表示整数，也可以表示小数
number(5,2) 表示一位小数有5位有效数，2位小数 范围：-999.99到999.99
number(5) 表示一个5位整数 范围99999到-99999
3.2.3. 日期类型
date 包含年月日和时分秒 oracle默认格式 1-1月-1999
timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。 n
3.2.4. 图片
blob 二进制数据 可以存放图片/声音 4G
一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
3.3. 建表
--学生表
create table student
(
xh number(4), --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
--班级表
CREATE TABLE class
(
classId NUMBER(2),
cName VARCHAR2(40)
);
3.4. 修改表
3.4.1. 添加一个字段
SQL>ALTER TABLE student add (classId NUMBER(2)); n
3.4.2. 修改一个字段的长度
SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30)); n
3.4.3. 修改字段的类型/或是名字（不能有数据） 不建议做
SQL>ALTER TABLE student modify (xm CHAR(30)); n
3.4.4. 删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)
SQL>ALTER TABLE student DROP COLUMN sal;
3.4.5. 修改表的名字 很少有这种需求
SQL>RENAME student TO stu; n
3.5. 删除表
SQL>DROP TABLE student;
3.6. 添加数据
3.6.1. 所有字段都插入数据
INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05', 10);
oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份 yy 2位的年 ‘09-6月-99’ 1999年6月9日
修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';
修改后，可以用我们熟悉的格式添加日期类型： INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10);
3.6.2. 插入部分字段
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女');
插入空值
INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);
select * from student where birthday is null;
如果要查询birthday不为null,则应该这样写：
select * from student where birthday is not null;
3.7. 修改数据
3.7.1. 修改一个字段
UPDATE student SET sex = '女' WHERE xh = 'A001'; n
3.7.2. 修改多个字段
UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';
3.7.3. 修改含有null值的数据
不要用 = null 而是用 is null；
SELECT * FROM student WHERE birthday IS null;
3.8. 删除数据
3.8.1. 删除所有记录，表结构还在
DELETE FROM student;
写日志，可以恢复的，速度慢。 Delete 的数据可以恢复。
savepoint a; --创建保存点
DELETE FROM student;
rollback to a; --恢复到保存点 一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。
3.8.2. 删除表的结构和数据
DROP TABLE student;
delete from student WHERE xh = 'A001'; --删除一条记录；
3.8.3. 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记
truncate TABLE student;
--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
3.9. oracle表查询
3.9.1. 查看表结构
DESC emp; n
3.9.2. 查询所有列
SELECT * FROM dept;
切忌动不动就用select *
INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;
3.9.3. 查询指定列
SELECT ename, sal, job, deptno FROM emp; n
3.9.4. 取消重复行DISTINCT
SELECT DISTINCT deptno, job FROM emp;
tp:1.distinct必须放于第一位
2.后面跟多个列，作用于所有
3.9.5. 处理null值 nvl
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;
如果comm为null则comm取0 n
3.9.6. 使用列的别名
SELECT ename "姓名", sal*12 AS "年收入" FROM emp;
3.9.7. 连接字符串（||）
SELECT ename || ' is a ' || job FROM emp;
3.9.8. 使用where子句
问题：如何显示工资高于3000的 员工？ SELECT * FROM emp WHERE sal > 3000;
问题：如何查找1982.1.1后入职的员工？ SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 问题：如何显示工资在2000到3000的员工？ SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;
3.9.9. 使用like操作符
%：表示0到多个字符
_：表示任意单个字符
问题：如何显示首字符为S的员工姓名和工资？
SELECT ename,sal FROM emp WHERE ename like 'S%';
如何显示第三个字符为大写O的所有员工的姓名和工资？
SELECT ename,sal FROM emp WHERE ename like '__O%'; n
3.9.10. 在where条件中使用in
问题：如何显示empno为7844, 7839,123,456 的雇员情况？
SELECT * FROM emp WHERE empno in (7844, 7839,123,456); n
3.9.11. 使用is null的操作符
问题：如何显示没有上级的雇员的情况？
SELECT * FROM emp WHERE mgr is null;
3.9.12. 使用逻辑操作符号
问题：查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J？
SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; n
3.9.13. 使用order by 字句 默认asc
问题：如何按照工资的从低到高的顺序显示雇员的信息？
SELECT * FROM emp ORDER by sal;
问题：按照部门号升序而雇员的工资降序排列
SELECT * FROM emp ORDER by deptno, sal DESC;
3.9.14. 使用列的别名排序
问题：按年薪排序 select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;
3.10. 表复杂查询
3.10.1. 数据分组max，min， avg， sum， count
问题：如何显示所有员工中最高工资和最低工资？
SELECT MAX(sal),min(sal) FROM emp e;
最高工资那个人是谁？
select ename, sal from emp where sal=(select max(sal) from emp);
注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数..如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。
问题：如何计算总共有多少员工问题：如何 扩展要求： 查询最高工资员工的名字，工作岗位
SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp);
显示工资高于平均工资的员工信息
SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); n
3.10.2. group by 和 having子句
group by用于对查询的结果分组统计， having子句用于限制分组显示结果。
问题：如何显示每个部门的平均工资和最高工资？
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;
（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）
问题：显示每个部门的每种岗位的平均工资和最低工资？
SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;
问题：显示平均工资低于2000的部门号和它的平均工资？
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; n
3.10.3. 对数据分组的总结
1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 这里deptno就一定要出现在group by 中
3.11. 多表查询
说明 多表查询是指基于两个和两个以上的表或是视图的查询。
问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？
规定：多表查询的条件是 至少不能少于 表的个数-1 才能排除笛卡尔集 （如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
问题：显示部门号为10的部门名、员工名和工资？
SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 问题：显示各个员工的姓名，工资及工资的级别？
SQL>select * from salgrade;
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; （注意：如果用group by，一定要把e.deptno放到查询列里面） n
3.12. 自连接
自连接是指在同一张表的连接查询
问题：显示某个员工的上级领导的姓名？
比如显示员工‘FORD’的上级
SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';
3.13. 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。 n
3.13.1. 单行子查询
单行子查询是指只返回一行数据的子查询语句
显示与SMITH同部门的所有员工？
思路： 1 查询出SMITH的部门号 select deptno from emp WHERE ename = 'SMITH';
2 显示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。 n
3.13.2. 多行子查询
多行子查询指返回多行数据的子查询
如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
SELECT DISTINCT job FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);
（注意：不能用job=..，因为等号=是一对一的） n
在多行子查询中使用all操作符
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？
SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);
扩展要求： 别的查询方法：
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
执行效率上， 函数高得多 n
在多行子查询中使用any操作符
如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？
SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);
扩展要求： 别的查询方法：
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); n
3.13.3. 多列子查询
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。
如何查询与SMITH的部门和岗位完全相同的所有雇员。
SELECT deptno, job FROM emp WHERE ename = 'SMITH';
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); n
3.13.4. 在from子句中使用子查询
如何显示高于自己部门平均工资的员工的信息
思路： 1. 查出各个部门的平均工资和部门号
SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;
2. 把上面的查询结果看做是一张子表
3. SELECT e.ename, e.deptno, e.sal, ds.mysal
FROM emp e,
(SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds
WHERE e.deptno = ds.deptno
AND e.sal > ds.mysal;
tp：
当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。 别名不能用as
3.14. 分页查询
按雇员的id号升序取出 oracle的分页一共有三种方式
3.14.1. 根据rowid来分
select * from t_xiaoxi
where rowid in
(select rid from
(select rownum rn, rid from
(select rowid rid, cid from t_xiaoxi order by cid desc)
where rownum<10000)
where rn>9980) order by cid desc;
执行时间0.03秒
3.14.2. 按分析函数来分
select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t)
where rk<10000 and rk>9980;
执行时间1.01秒
3.14.3. *按rownum来分
select * from (
select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10
)where rn>=5;
分析：
1. rownum 分页 SELECT * FROM emp;
2. 显示rownum[oracle分配的]
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;
rn相当于Oracle分配的行的ID号
3. 挑选出6—10条记录 先查出1-10条记录
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10;
如果后面加上rownum>=6是不行的，
4. 然后查出6-10条记录
SELECT * FROM
(SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10)
WHERE rn >= 6;
几个查询变化
a. 指定查询列
只需要修改最里层的子查询
SELECT * FROM
(SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10)
WHERE rn >= 6;
b. 排序查询
只需要修改最里层的子查询
SELECT * FROM
(SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10)
WHERE rn >= 6;
3.15. 用查询结果创建新表
CREATE TABLE mytable (id, name, sal, job, deptno)
as SELECT empno, ename, sal, job, deptno FROM emp;
3.16. 合并查询
为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus
多用于数据量比较大的数据局库，运行速度快。
3.16.1. union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SELECT ename, sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
3.16.2. union all
该操作符与union相似，但是它不会取消重复行，而且不会排序。
SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
3.16.3. intersect
使用该操作符用于取得两个结果集的交集。
SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
3.16.4. minus
使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。
SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
4. oracle中事务处理
4.1. 事务概念
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml(数据操作语言，增删改，没有查询)语句要么全部成功，要么全部失败。n
4.2. 事务的几个重要操作
1.设置保存点 savepoint a
2.取消部分事务 rollback to a
3.取消全部事务 rollback
4.提交事务 commit
注意：这个回退事务，必须是没有commit前使用的；如果没有手动执行commit,而是exit了，那么会自动提交 n
4.3. 只读事务
允许执行查询的操作，而不允许执行任何其它dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务。在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。 n
设置只读事务 set transaction read only;
5. oracle的函数
5.1. 字符函数
5.1.1. lower(char)
将字符串转化为小写的格式。 u
5.1.2. upper(char)
将字符串转化为大写的格式。 u
5.1.3. length(char)
返回字符串的长度。 u
5.1.4. substr(string,start,count)
取字符串的子串 n代表取n个的意思，不是代表取到第n个 u
5.1.5. instr(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
5.1.6. ltrim和rtrim
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
5.1.7. 实例
问题：将所有员工的名字按小写的方式显示
SQL> select lower(ename) from emp;
问题：将所有员工的名字按大写的方式显示。
SQL> select upper(ename) from emp;
问题：显示正好为5个字符的员工的姓名。
SQL> select * from emp where length(ename)=5;
问题：显示所有员工姓名的前三个字符。
SQL> select substr(ename,1,3) from emp;
问题：以首字母大写,后面小写的方式显示所有员工的姓名。
SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
问题：以首字母小写,后面大写的方式显示所有员工的姓名。
SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;
问题：显示所有员工的姓名，用“我是老虎”替换所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
5.2. 数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round
5.2.1. round(n,[m])
该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。 u
5.2.2. trunc(n,[m])
该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。 u
5.2.3. floor(n)
返回小于或是等于n的最大整数 u
5.2.4. ceil(n)
返回大于或是等于n的最小整数
问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。
SQL> select trunc(sal/30), ename from emp;
SQL> select floor(sal/30), ename from emp;
在做oracle测试的时候，可以使用dual表 select mod(10,2) from dual;结果是0 select mod(10,3) from dual;结果是1
5.2.5. abs(n)
返回数字n的绝对值
select abs(-13) from dual;
5.2.6. acos(n)
返回数字的反余弦值
5.2.7. asin(n)
返回数字的反正弦值
5.2.8. atan(n)
返回数字的反正切值
5.2.9. exp(n)
返回e的n次幂
5.2.10. log(m,n)
返回对数值
5.2.11. power(m,n)
返回m的n次幂
5.3. 日期函数
日期函数用于处理date类型的数据。 默认情况下日期格式是dd-mon-yy 即12-7月-78
5.3.1. sysdate
该函数返回系统时间
5.3.2. add_months(d,n)
问题：查找已经入职8个月多的员工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
5.3.3. last_day(d)
返回指定日期所在月份的最后一天
5.3.4. 实例
问题：显示满10年服务年限的员工的姓名和受雇日期。
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
问题：对于每个员工，显示其加入公司的天数。
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;
问题：找出各月倒数第3天受雇的所有员工。
SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
5.4. 转换函数
5.4.1. to_char(date,’format’)
问题：日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;
yy：两位数字的年份 2004-->04
yyyy：四位数字的年份 2004年
mm：两位数字的月份 8月-->08
dd：两位数字的天 30号-->30
hh24： 8点-->20
hh12：8点-->08
mi、ss-->显示分钟\秒
问题：显示薪水的时候，把本地货币单位加在前面
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp; 问题：显示1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
问题：显示所有12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12; n
5.4.2. to_date(string,’format’)
函数to_date用于将字符串转换成date类型的数据。
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual
5.4.3. decode
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式
6. 数据库管理，表的逻辑备份与恢复
6.1. sys，system用户
6.1.1. 存储的数据重要性不同
sys：
所有oracle的数据字典的基表和视图都存放在sys用户中，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper角色或权限，是oracle权限最高的用户。
system：
用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。
system用户拥有dba，sysdba角色或系统权限。
sysdba可以建数据库，sysoper不能建数据库
6.1.2. 权限的不同
sys用户必须以as sysdba或as sysoper形式登录。不能以normal方式登录数据库
system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的
sysdba>sysoper>dba 可以看到：只要是sysoper拥有的权限，sysdba都有（它们的最大区别是：sysdba可以创建数据库，sysoper不可以创建数据库）
6.2. 管理初始化参数
6.2.1. 显示初始化参数
show parameter命令 l
6.2.2. 修改参数
需要说明的如果你希望修改这些初始化的参数，可以到文件D:\oracle\admin\myoral\pfile\init.ora文件中去修改比如要修改实例的名字
6.3. 导出
6.3.1. 介绍
导出具体的分为：导出表，导出方案，导出数据库三种方式。
导出使用exp命令来完成的，
该命令常用的选项有：
userid： 用于指定执行导出操作的用户名，口令，连接字符串
tables： 用于指定执行导出操作的表
owner： 用于指定执行导出操作的方案
full=y： 用于指定执行导出操作的数据库
inctype： 用于指定执行导出操作的增量类型
rows： 用于指定执行导出操作是否要导出表中的数据
file： 用于指定导出文件名
6.3.2. 导出自己的表
exp userid=scott/tiger@myoral tables=(emp,dept) file=d:\e1.dmp
1.导出其它方案的表 如果用户要导出其它方案的表，则需要dba的权限或是exp_full_database的权限
比如system就可以导出scott的表
E:\oracle\ora92\bin>exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.emp
特别说明：在导入和导出的时候，要到oracle目录的bin目录下。
6.3.3. 导出表的结构
exp userid=scott/tiger@accp tables=(emp) file=d:\e3.dmp rows=n
使用直接导出方式
exp userid=scott/tiger@accp tables=(emp) file=d:\e4.dmp direct=y
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。 这时需要数据库的字符集要与客户端字符集完全一致，否则会报错... n
6.3.4. 导出方案
导出方案是指使用export工具导出一个方案或是多个方案中的所有对象（表，索引，约束...）和数据。并存放到文件中。
6.3.5. 导出自己的方案
exp userid=scott/tiger@myorcl owner=scott file=d:\scott.dmp
6.3.6. 导出其它方案
如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限
比如system用户就可以导出任何方案
exp userid=system/manager@myorcl owner=(system,scott) file=d:\system.dmp n
6.3.7. 导出数据库
导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限 增量备份（好处是第一次备份后，第二次备份就快很多了）
exp userid=system/manager@myorcl full=y inctype=complete file=d:\all.dmp
6.4. 导入
6.4.1. 介绍
导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件。
与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。
imp常用的选项有
userid： 用于指定执行导入操作的用户名，口令，连接字符串
tables： 用于指定执行导入操作的表
formuser： 用于指定源用户
touser： 用于指定目标用户
file： 用于指定导入文件名
full=y： 用于指定执行导入整个文件
inctype： 用于指定执行导入操作的增量类型
rows： 指定是否要导入表行（数据）
ignore： 如果表存在，则只导入数据 n
6.4.2. 导入自己的表
imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp
6.4.3. 导入表到其它用户
要求该用户具有dba的权限，或是imp_full_database
imp userid=system/tiger@myorcl tables=(emp) file=d:\xx.dmp touser=scott
6.4.4. 导入表的结构
只导入表的结构而不导入数据
imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp rows=n
6.4.5. 导入数据
如果对象已经存在可以只导入表的数据
imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp ignore=y n
6.4.6. 导入方案
导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba的权限，或者imp_full_database
6.4.7. 导入自身的方案
imp userid=scott/tiger file=d:\xxx.dmp
6.4.8. 导入其它方案
要求该用户具有dba的权限
imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott n
6.4.9. 导入数据库
在默认情况下，当导入数据库时，会导入所有对象结构和数据，
imp userid=system/manager full=y file=d:\xxx.dmp
6.5. 数据字典和动态性能视图
6.5.1. 介绍
数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。 动态性能视图记载了例程启动后的相关信息。 n
数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。 用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成的。
6.5.2. 数据字典的组成
数据字典包括数据字典基表和数据字典视图
基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。
数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。
数据字典视图主要包括user_xxx，all_xxx，dba_xxx三种类型。
6.5.3. 常用数据字典视图
user_tables
用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表
select table_name from user_tables; n
all_tables
用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其它方案的表
select table_name from all_tables; n
dba_tables
它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table系统权限。
例如：当用system用户查询数据字典视图dba_tables时，会返回system，sys，scott...方案所对应的数据库表。
6.5.4. 用户名，权限，角色
在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，oracle会将权限和角色的信息存放到数据字典。
dba_users可以显示所有数据库用户的详细信息；
dba_sys_privs，可以显示用户所具有的系统权限；
dba_tab_privs，可以显示用户具有的对象权限；
dba_col_privs可以显示用户具有的列权限；
dba_role_privs可以显示用户所具有的角色。
例如：要查看scott具有的角色，可查询dba_role_privs；
SQL> select * from dba_role_privs where grantee='SCOTT';
//查询orale中所有的系统权限，一般是dba
select * from system_privilege_map order by name;
//查询oracle中所有对象权限，一般是dba
select distinct privilege from dba_tab_privs;
//查询oracle中所有的角色，一般是dba
select * from dba_roles;
//查询数据库的表空间
select tablespace_name from dba_tablespaces;
问题1：如何查询一个角色包括的权限？
a.一个角色包含的系统权限
select * from dba_sys_privs where grantee='角色名' 或者
select * from role_sys_privs where role='角色名'
b.一个角色包含的对象权限
select * from dba_tab_privs where grantee='角色名'
问题2：oracle究竟有多少种角色？
SQL> select * from dba_roles;
问题3：如何查看某个用户，具有什么样的角色？
select * from dba_role_privs where grantee='用户名' n
显示当前用户可以访问的所有数据字典视图。
select * from dict where comments like '%grant%'; n
显示当前数据库的全称
select * from global_name; n
6.5.5. 动态性能视图
动态性能视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图；当停止oracle server时，系统会删除动态性能视图。oracle的所有动态性能视图都是以v_$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且其同义词是以V$开始的，例如v_$datafile的同义词为v$datafile;动态性能视图的所有者为sys，一般情况下，由dba或是特权用户来查询动态性能视图。
7. 数据库管理
7.1. 表空间和数据文件
表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。
7.2. 数据库的逻辑结构
oracle中逻辑结构包括表空间、段、区和块。 数据库由表空间构成，而表空间又是由段构成，而段又是由区构成，而区又是由oracle块构成的这样的一种结构
7.3. 表空间
7.3.1. 介绍
表空间用于从逻辑上组织数据库的数据。数据库逻辑上是由一个或是多个表空间组成的。
通过表空间可以达到以下作用：
1. 控制数据库占用的磁盘空间
2. dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。
7.3.2. 建立表空间
crate tablespace
一般情况下，建立表空间是特权用户或是dba来执行的，如果用其它用户来创建表空间，则用户必须要具有create tablespace的系统权限。 n
create tablespace data01 datafile 'd:\test\dada01.dbf' size 20m uniform size 128k;
说明：执行完上述命令后，会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k n
7.3.3. 使用数据表空间
create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13)) tablespace data01;
7.3.4. 改变表空间状态
当建立表空间时，表空间处于联机的（online）状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句。但是在进行系统维护或是数据维护时，可能需要改变表空间的状态。一般情况下，由特权用户或是dba来操作。
1. 使表空间脱机
alter tablespace 表空间名 offline;
2. 使表空间联机
alter tablespace 表空间名 online;
3. 只读表空间
当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读
alter tablespace 表空间名 read only;
(修改为可写是 alter tablespace 表空间名 read write;) n
知道表空间名，显示该表空间包括的所有表
select * from all_tables where tablespace_name=’表空间名’;
知道表名，查看该表属于那个表空间
select tablespace_name, table_name from user_tables where table_name=’emp’;
4. 使表空间可读写
alter tablespace 表空间名 read write; n
7.3.5. 删除表空间
一般情况下，由特权用户或是dba来操作，如果是其它用户操作，那么要求用户具有drop tablespace系统权限。
drop tablespace ‘表空间’ including contents and datafiles;
说明：including contents表示删除表空间时，删除该空间的所有数据库对象，
而datafiles表示将数据库文件也删除。 n
7.3.6. 扩展表空间
表空间是由数据文件组成的，表空间的大小实际上就是数据文件相加后的大小。那么我们可以想象，假定表employee存放到data01表空间上，初始大小就是2M，当数据满2M空间后，如果在向employee表插入数据，这样就会显示空间不足的错误。
案例说明：
1. 建立一个表空间 sp01
2. 在该表空间上建立一个普通表 mydment 其结构和dept一样
3. 向该表中加入数据 insert into mydment select * from dept;
4. 当一定时候就会出现无法扩展的问题，怎么办？
5. 就扩展该表空间，为其增加更多的存储空间。
有三种方法：
1. 增加数据文件
SQL> alter tablespace sp01 add datafile ‘d:\test\sp01.dbf’ size 20m;
2. 增加数据文件的大小
SQL> alter tablespace 表空间名 ‘d:\test\sp01.dbf’ resize 20m;
这里需要注意的是数据文件的大小不要超过500m。
3. 设置文件的自动增长。
SQL> alter tablespace 表空间名 ‘d:\test\sp01.dbf’ autoextend on next 10m maxsize 500m; n
7.3.7. 移动数据文件
下面以移动数据文件sp01.dbf为例来说明：
1. 确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name=’d:\test\sp01.dbf’;
2. 使表空间脱机 确保数据文件的一致性，将表空间转变为offline的状态。
alter tablespace sp01(表空间名) offline;
3. 使用命令移动数据文件到指定的目标位置
host move d:\test\sp01.dbf c:\test\sp01.dbf
4. 在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改
alter tablespace sp01 rename datafile ‘d:\test\sp01.dbf’ to ‘c:\test\sp01.dbf’;
5. 使得表空间联机 在移动了数据文件后，为了使用户可以访问该表空间，必须将其转变为online状态。 alter tablespace sp01(表空间名) online; n
7.3.8. 显示表空间信息
select tablespace_name from dba_tablespaces; n
7.3.9. 显示表空间所包含的数据文件
select file_name, bytes from dba_data_files where tablespce_name=’表空间’; n
8. 约束
8.1. 约束种类
在oracle中，约束包括：not null、 unique， primary key， foreign key，和check五种。
not null（非空） 如果在列上定义了not null，那么当插入数据时，必须为列提供数据。 n
unique（唯一） 当定义了唯一约束后，该列值是不能重复的，但是可以为null。 n
primary key（主键） 用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。 需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。 n
foreign key（外键） 用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键
SQL> create table goods(
goodsId char(8) primary key, --主键
goodsName varchar2(30)，
unitprice number(10,2) check(unitprice>0),
category varchar2(8), provider varchar2(30)
);
SQL> create table customer(
customerId char(8) primary key, --主键
name varchar2(50) not null, --不为空
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in ('男'，'女'))， -- 一个char能存半个汉字，两位char能存一个汉字 cardId char(18) );
8.2. 删除约束
alter table 表名 drop constraint 约束名称；
8.3. 显示约束信息
8.3.1. 显示约束信息
select constraint_name, constraint_type, status, validated from user_constraints where table_name = '表名';
8.3.2. 显示约束列
select column_name, position from user_cons_columns where constraint_name = '约束名';
8.4. 列级定义
列级定义是在定义列的同时定义约束。 如果在department表定义主键约束
create table department4(dept_id number(12) constraint pk_department primary key, name varchar2(12), loc varchar2(12)); n
8.5. 表级定义
表级定义是指在定义了所有列后，再定义约束。
这里需要注意： not null约束只能在列级上定义。
create table employee2(emp_id number(4), name varchar2(15), dept_id number(2),
constraint pk_employee primary key (emp_id), constraint fk_department foreign key (dept_id) references department4(dept_id));
9. 索引
9.1. 创建索引
9.1.1. 单列索引
单列索引是基于单个列所建立的索引，
create index 索引名 on 表名(列名); n
9.1.2. 复合索引
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同
create index emp_idx1 on emp (ename, job);
create index emp_idx1 on emp (job, ename);
9.2. 使用原则
1. 在大表上建立索引才有意义
2. 在where子句或是连接条件上经常引用的列上建立索引
3. 索引的层次不要超过4层
9.3. 索引的缺点
1. 建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。
2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
如下字段建立索引应该是不恰当的：
1. 很少或从不引用的字段；
2. 逻辑型的字段，如男或女（是或否）等。
9.4. 显示索引信息
dba_indexs用于显示数据库所有的索引信息
user_indexs用于显示当前用户的索引信息
select index_name, index_type from user_indexes where table_name = '表名'; n
显示索引列
select table_name, column_name from user_ind_columns where index_name = 'IND_ENAME';
10. 权限
权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。
10.1. 系统权限
10.1.1. 常用系统权限
create session 连接数据库
create table 建表 create view 建视图
create public synonym 建同义词
create procedure 建过程、函数、包
create trigger 建触发器
create cluster 建簇 n
10.1.2. 显示系统权限
select * from system_privilege_map order by name; n
10.1.3. 授予系统权限
一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。
10.2. 对象权限
10.2.1. 常用对象权限
alter 修改 delete 删除 select 查询 insert 添加 update 修改 index 索引 references 引用 execute 执行 n
10.2.2. 显示对象权限
SQL> conn system/manager;
SQL> select distinct privilege from dba_tab_privs;
SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';
10.2.3. 授予对象权限
1). 希望monkey可以查询scott.emp表的数据
grant select on emp to monkey;
2). 希望monkey可以修改scott.emp的表数据
grant update on emp to monkey;
3). 希望monkey可以删除scott.emp的表数据
grant delete on emp to monkey;
4). 更加简单的方法
grant all on emp to monkey;
5). 如果black用户要修改scott.emp表的结构，则必须授予alter对象权限
SQL> conn scott/tiger
SQL> grant alter on emp to blake;
6). 授予execute权限 如果用户想要执行其它方案的包/过程/函数，则须有execute权限。
SQL> conn system/manager
SQL> grant execute on dbms_transaction to ken;
7). 授予index权限 如果想在别的方案的表上建立索引，则必须具有index对象权限。
SQL> conn scott/tiger
SQL> grant index on scott.emp to blake;
8). 使用with grant option选项 该选项用于转授对象权限。
SQL> conn scott/tiger;
SQL> grant select on emp to blake with grant option;
SQL> conn black/shunping
SQL> grant select on scott.emp to jones; n
11. 角色
11.1. 预定义角色
预定义角色是指oracle所提供的角色
常用的预定义角色connect，resource，dba
11.1.1. connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了
那么connect角色包含以下系统权限
alter session create cluster create database link create session create table create view create sequence
11.1.2. resource角色
resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。
resource角色包含以下系统权限
create cluster create indextype create table create sequence create type create procedure create trigger
11.1.3. dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。
11.2. 自定义角色
一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式（不验证，数据库验证等）。
11.2.1. 建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色。
create role 角色名 not identified;
11.2.2. 建立角色（数据库验证）
采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。
create role 角色名 identified by 密码;
11.3. 角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。
1.给角色授权
系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
SQL> conn system/manager;
SQL> grant create session to 角色名 with admin option
SQL> conn scott/tiger@myoral;
SQL> grant select on scott.emp to 角色名;
SQL> grant insert, update, delete on scott.emp to
11.4. 分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。
SQL> conn system/manager;
SQL> grant 角色名 to blake with admin option;
因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。 n
11.5. 删除角色
使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。
SQL> conn system/manager;
SQL> drop role 角色名;
如果角色被删除，那么被授予角色的用户不具有之前角色里的权限 n
11.6. 显示角色信息
11.6.1. 显示所有角色
SQL> select * from dba_roles;
11.6.2. 显示角色具有的系统权限
SQL> select privilege, admin_option from role_sys_privs where role='角色名';
11.6.3. 显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
11.6.4. 显示用户具有的角色
通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色
SQL> select granted_role, default_role from dba_role_privs where grantee = ‘用户名’;
12. PL/SQL
12.1. 简单分类
12.2. 编写规范
1.注释 单行注释 --
select * from emp where empno=7788; --取得员工信息
多行注释 /*...*/来划分
2.标志符号的命名规范
1).当定义变量时，建议用v_作为前缀v_sal
2).当定义常量时，建议用c_作为前缀c_rate
3).当定义游标时，建议用_cursor作为后缀emp_cursor
4).当定义例外时，建议用e_作为前缀e_error
12.3. pl/sql块
12.3.1. 介绍
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。 n
12.3.2. 块结构
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。
如下所示：
declare
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql语句和sql语句*/
exception /*例外处理部分——处理运行的各种错误*/
end;
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
例外处理部分是从exception开始的，该部分是可选的。
12.3.3. pl/sql块的实例
(1)只包括执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
end;
相关说明： dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。
(2)包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5); --定义字符串变量
begin
select ename into v_ename from emp where empno=&aa;
dbms_output.put_line('雇员名：'||v_ename);
end;
(3)包含定义部分，执行部分和例外处理部分
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名是：'||v_ename||' 工资：'||v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('朋友，你的编号输入有误！')；
end;
/
12.4. pl/sql分类
-- 过程，函数，包，触发器
12.4.1. 过程
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。
create procedure sp_pro3(spName varchar2, newSal number) is --不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java写方法时的参数一样
begin
--执行部分，根据用户名去修改工资
update emp set sal=newSal where ename=spName;
end;
/
12.4.2. 函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数
--输入雇员的姓名，返回该雇员的年薪
create function annual_incomec(name varchar2)
return number is
annual_salazy number(7,2);
begin
--执行部分
select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;
return annual_salazy;
end;
/
如果函数创建过程有编译错误，可以使用show error;命令显示错误 在sqlplus中调用函数
SQL> var income number
SQL> call annual_incomec('scott') into: income;
SQL> print income
12.4.3. 包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1.我们可以使用create package命令来创建包。
--创建一个包sp_package --声明该包有一个过程update_sal --声明该包有一个函数annual_income create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
2.建立包体可以使用create package body命令
--给包sp_package实现包体
create or replace package body sp_package is
procedure update_sal(name varchar2, newsal number)
is
begin
update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2) return number is
annual_salary number;
begin
select sal * 12 + nvl(comm, 0) into annual_salary from emp
where ename = name;
return annual_salary;
end;
end;
/
3.如何调用包的过程或是函数 当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。
SQL> call sp_package.update_sal('SCOTT', 1500);
12.4.4. 触发器
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
12.5. 标量类型(scalar)
12.5.1. 语法
identifier [constant] datatype [not null] [:=| default expr]
identifier : 名称
constant ：指定常量。需要指定它的初始值，且其值是不能改变的
datatype ：数据类型
not null ：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。 n
12.5.2. 标量定义的案例
1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围 -9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false; n
12.5.3. 使用标量
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal := v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||' 交税：'||v_tax_sal);
end;
/ n
12.5.4. 使用%type类型
使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
标识符名 表名.列名%type;
v_ename emp.ename%type;
12.6. 复合变量(composite)
用于存放多个值的变量。主要包括这几种： 1.pl/sql记录 2.pl/sql表 3.嵌套表 4.varray n
12.6.1. pl/sql记录
declare
--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。
type emp_record_type is record
(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--定义了一个sp_record变量，这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename, sal, job into sp_record from emp where empno = 7788;
dbms_output.put_line ('员工名:' || sp_record.name);
end; n
12.6.2. pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。
declare
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type
--index by binary_integer 表示下标是整数
type sp_table_type is table of emp.ename%type
index by binary_integer;
--定义了一个sp_table变量，这个变量的类型是sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7788;
dbms_output.put_line('员工名:' || sp_table(-1));
end;
说明：
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788;变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下： ORA-01422:实际返回的行数超出请求的行数 解决方法是：使用参照变量
12.7. 参照变量
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。 n
12.7.1. ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。
实例如下：
declare
--定义游标sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
end loop;
end;
/
12.8. 条件控制
12.8.1. 一重条件分支
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spName;
--判断
if v_sal<2000 then
update emp set sal=sal+sal*10% where ename=spName;
end if;
end;
/ n
12.8.2. 二重条件分支 if – then – else
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_comm emp.comm%type;
begin
--执行
select comm into v_comm from emp where ename=spName;
--判断
if v_comm<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
/ n
12.8.3. 多重条件分支 if – then – elsif – then
create or replace procedure sp_pro6(spNo number) is
--定义
v_job emp.job%type;
begin
--执行
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
/ n
12.9. 循环语句
12.9.1. loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
create or replace procedure sp_pro6(spName varchar2) is
--定义 :=表示赋值
v_num number:=1;
begin
loop
insert into users values(v_num,spName);
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
/ n
12.9.2. while循环
对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop结束。
create or replace procedure sp_pro6(spName varchar2) is
--定义 :=表示赋值
v_num number:=11;
begin
while v_num<=20 loop
--执行
insert into users values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
/ n
12.9.3. for循环
begin
for i in reverse 1..10 loop
insert into users values (i, 'shunping');
end loop;
end;
12.10. PL/SQL分页
编写分页过程 n
--oracle的分页
select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;
--在分页时，大家可以把下面的sql语句当做一个模板使用
select * from
(select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--开发一个包 --建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--开始编写分页的过程
create or replace procedure fenye
(
tableName in varchar2,
Pagesize in number,--一页显示记录数
pageNow in number,
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
--执行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql语句
v_sql:='select count(*) from '||tableName;
--执行sql,并把返回的值，赋给myrows
execute inmediate v_sql into myrows;
--计算myPageCount
--if myrows%Pagesize=0 then这样写是错的
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1
end if;
--关闭游标
close p_cursor;
end;
/
要求按照薪水从低到高排序，然后取出6-10 过程的执行部分做下改动，如下：
begin
--执行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
12.11. 例外处理
例外的分类 oracle将例外分为预定义例外，非预定义例外和自定义例外三种。
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与oracle错误无关的其它情况 n
declare
--定义
v_ename emp.ename%type;
begin
--
select ename into v_ename from emp where empno=&gno;
dbms_output.put_line('名字：'||v_ename)
exception
when no_data_found then
dbms_output.put_line('编号没有！');
/
常用的预定义例外：
case_not_found
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外
cursor_already_open
当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open
declare
cursor emp_cursor is select ename, sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游标已经打开');
end;
/ n
dup_val_on_index
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外
begin
insert into dept values (10, '公关部', '北京');
exception
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出现重复值');
end; n
invalid_cursor 当试图在不合法的游标上执行操作时，会触发该例外
invalid_number 当输入的数据有误时，会触发该例外 比如：数字100写成了loo就会触发该例外
begin
update emp set sal= sal + 'loo';
exception
when invalid_number then
dbms_output.put_line('输入的数字不正确');
end;
no_data_found
下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp when ename='&name';
exception
when no_data_found then
dbms_output.put_line('不存在该员工')；
end; n
too_many_rows
当执行select into语句时，如果返回超过了一行，则会触发该例外。
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('返回了多行');
end; n
zero_divide
当执行2/0语句时，则会触发该例外。 n
value_error
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error，比如：
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno = &no1;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('变量尺寸不足');
end; n
13. oracle视图
视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。（视图不是真实存在磁盘上的）
13.1. 视与表的区别
1.表需要占用磁盘空间，视图不需要
2.视图不能添加索引（所以查询速度略微慢点）
3.使用视图可以简化，复杂查询
4.视图的使用利于提高安全性 比如：不同用户查看不同视图
13.2. 创建视图
create view 视图名 as select 语句 [with read only] n
13.3. 创建或修改视图
create or replace view 视图名 as select 语句 [with read only] n
13.4. 删除视图
drop view 视图名
create view myview as select * from emp where sal<1000;
create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
视图之间也可以做联合查询
8049
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
