oracle笔记

wotier

贡献于2011-12-03

字数:0 关键词: Oracle 数据库服务器

ORACLE 笔记 1 / 63 ORACLE 笔记 前言 ORACLE 是目前处理海量数据使用范围最广的数据库软件,功能非常强大。在日常工作 中每个程序员都会用到。本文愿起一个抛砖引玉的作用,与大家一起交流,共同学习。 联系方式 Gtalk(email):chengyalei@gmail.com QQ:7003406 版本历史 版本 作者/修改者 日期 备注 1.0 程亚雷 2011/08/26 一至五章 1.1 程亚雷 2011/09/02 六至八章 1.2 程亚雷 2011/09/09 九至十章 1.3 程亚雷 2011/09/15 十至十三章 1.4 程亚雷 2011/10/07 十四至十五章 目录 ORACLE 笔记 .............................................................................................................................. 1 前言 ........................................................................................................................................... 1 联系方式 ................................................................................................................................... 1 版本历史 ................................................................................................................................... 1 目录 ........................................................................................................................................... 1 第一章 系统安装 ..................................................................................................................... 7 第二章 常用命令 ..................................................................................................................... 7 2.1 连接命令 ........................................................................................................................... 7 2.2 修改密码 ........................................................................................................................... 7 2.3 输出暂停 ........................................................................................................................... 8 2.4 文件操作命令 ................................................................................................................... 8 2.4.1 start 和@ .............................................................................................................. 8 2.4.2 edit ....................................................................................................................... 8 ORACLE 笔记 2 / 63 2.4.3 spool ..................................................................................................................... 8 2.4.4 sqlldr ................................................................................................................... 8 2.5 交互式命令 ....................................................................................................................... 9 2.6 时间输出命令 ................................................................................................................... 9 第三章 输出格式化 ................................................................................................................. 9 3.1 设定 sqlplus 行的长度 ................................................................................................... 9 3.2 指定分页的尺寸 ............................................................................................................... 9 第四章 用户管理 ..................................................................................................................... 9 4.1 创建用户 ........................................................................................................................... 9 4.2 删除用户 ......................................................................................................................... 10 4.3 给用户授权 ..................................................................................................................... 10 4.4 收回授权 ......................................................................................................................... 11 4.5 权限传递 ......................................................................................................................... 11 4.6 profile ........................................................................................................................... 12 4.6.1 账户锁定 ............................................................................................................. 12 4.6.2 给用户解锁 ......................................................................................................... 12 4.6.3 还原 profile ...................................................................................................... 12 4.6.4 终止口令 ............................................................................................................. 12 4.6.5 口令历史 ............................................................................................................. 12 4.6.6 删除 profile ...................................................................................................... 13 第五章 表的管理 ................................................................................................................... 13 5.1 命名规则 ......................................................................................................................... 13 5.2 数据类型 ......................................................................................................................... 13 5.2.1 字符型 ................................................................................................................. 13 5.2.2 数字型 ................................................................................................................. 13 5.2.3 日期类型 ............................................................................................................. 14 5.2.4 图片类型 ............................................................................................................. 14 5.3 建表 ................................................................................................................................. 14 5.3.1 表 1 ...................................................................................................................... 14 5.3.2 表 2 ...................................................................................................................... 14 5.4 修改表 ............................................................................................................................. 14 5.4.1 加字段 ................................................................................................................. 14 5.4.2 改长度 ................................................................................................................. 14 5.4.3 改字段的类型/名字 ........................................................................................... 14 5.4.4 删字段 ................................................................................................................. 14 5.4.5 改表名 ................................................................................................................. 14 5.4.6 删除表 ................................................................................................................. 15 5.4.7 清理被 drop 掉的表 ........................................................................................... 15 5.5 添加数据 ......................................................................................................................... 15 5.5.1 所有字段都插入 ................................................................................................. 15 5.5.2 插入部分字段 ..................................................................................................... 15 5.5.3 插入空值 ............................................................................................................. 15 5.5.4 修改一个字段 ..................................................................................................... 15 ORACLE 笔记 3 / 63 5.5.5 修改多个字段 ..................................................................................................... 15 5.5.6 从自身复制 ......................................................................................................... 15 5.5.7 修改含有 null 的值 ........................................................................................... 15 5.5.8 删除数据 ............................................................................................................. 15 第六章 数据查询 ................................................................................................................... 17 6.1 例表说明 ......................................................................................................................... 17 6.2 简单查询 ......................................................................................................................... 17 6.2.1 查看表结构 ......................................................................................................... 17 6.2.2 查询所有列 ......................................................................................................... 17 6.2.3 查询指定列 ......................................................................................................... 17 6.2.4 去重 ..................................................................................................................... 17 6.2.5 算数运算符 ......................................................................................................... 17 6.2.6 使用别名 ............................................................................................................. 17 6.2.7 处理 null 值 ....................................................................................................... 17 6.2.8 like 操作符 ........................................................................................................ 17 6.2.9 in ......................................................................................................................... 18 6.2.10 exists ............................................................................................................... 18 6.2.11 is null ............................................................................................................. 18 6.2.12 逻辑操作符 ....................................................................................................... 18 6.2.13 order by ........................................................................................................... 18 6.2.14 使用列的别名排序 ........................................................................................... 18 6.3 复杂查询 ......................................................................................................................... 18 6.3.1 数据分组 ............................................................................................................. 18 6.3.2 多表查询 ............................................................................................................. 19 6.3.3 联接查询 ............................................................................................................. 19 6.3.4 子查询 ................................................................................................................. 20 6.3.5 合并查询 ............................................................................................................. 22 第七章 操作数据 ................................................................................................................... 23 7.1 使用特定格式插入日期 ................................................................................................. 23 7.2 使用子查询插入数据 ..................................................................................................... 23 7.2.1 介绍 ..................................................................................................................... 23 7.2.2 说明 ..................................................................................................................... 23 7.3 使用子查询更新数据 ..................................................................................................... 23 7.3.1 介绍 ..................................................................................................................... 23 7.3.2 说明 ..................................................................................................................... 23 第八章 事务处理 ................................................................................................................... 24 8.1 什么是事务 ..................................................................................................................... 24 8.2 事务和锁 ......................................................................................................................... 24 8.3 提交事务 ......................................................................................................................... 24 8.4 回退事务 ......................................................................................................................... 24 8.5 事务的几个重要操作 ..................................................................................................... 24 5.5.1 设置保存点 ......................................................................................................... 24 5.5.2 取消部分事务 ..................................................................................................... 24 ORACLE 笔记 4 / 63 5.5.3 取消全部事务 ..................................................................................................... 25 8.6 Java 程序中如何使用事务 ............................................................................................ 25 8.7 只读事务 ......................................................................................................................... 25 8.7.1 说明 ..................................................................................................................... 25 8.7.2 案例 ..................................................................................................................... 25 8.7.3 设置只读事务 ..................................................................................................... 25 第九章 SQL 函数 .................................................................................................................... 25 9.1 字符串函数 ..................................................................................................................... 25 9.1.1 lower(char) ....................................................................................................... 25 9.1.2 upper(char) ....................................................................................................... 25 9.1.3 length(char) ..................................................................................................... 25 9.1.4 substr(char,m,n) ............................................................................................. 26 9.1.5 函数组合使用 ..................................................................................................... 26 9.1.6 replace(char,search_str,replace_str) ..................................................... 26 9.2 数学函数 ......................................................................................................................... 26 9.2.1 round(n,m) ......................................................................................................... 26 9.2.2 trunc(n,m) ......................................................................................................... 26 9.2.3 mod(m,n) ............................................................................................................. 26 9.2.4 floor(n) ............................................................................................................. 26 9.2.5 ceil(n) ............................................................................................................... 26 9.3 日期函数 ......................................................................................................................... 27 9.3.1 sysdate ............................................................................................................... 27 9.3.2 add_months(d,n) ............................................................................................... 27 9.3.3 last_day(d) ....................................................................................................... 27 9.4 转换函数 ......................................................................................................................... 27 9.4.1 功能介绍 ............................................................................................................. 27 9.4.2 to_char ............................................................................................................... 27 9.4.3 to_date ............................................................................................................... 28 9.5 系统函数 ......................................................................................................................... 29 9.5.1 sys_context ....................................................................................................... 29 第十章 数据库管理 ............................................................................................................... 29 10.1 数据库管理员 ............................................................................................................... 29 10.1.1 介绍 ................................................................................................................... 29 10.1.2 管理员职责 ....................................................................................................... 29 10.1.3 sys、system ..................................................................................................... 30 10.1.4 sysdba 和 sysoper 权限区别 .......................................................................... 30 10.1.5 dba 权限用户 .................................................................................................... 30 10.1.6 管理初始化参数 ............................................................................................... 30 10.2 逻辑备份与恢复 ........................................................................................................... 31 10.2.1 介绍 ................................................................................................................... 31 10.2.2 数据导出 ........................................................................................................... 31 10.2.3 数据导入 ........................................................................................................... 32 10.3 数据字典和动态性能视图 ........................................................................................... 33 ORACLE 笔记 5 / 63 10.3.1 介绍 ................................................................................................................... 33 10.3.2 数据字典 ........................................................................................................... 33 10.3.3 动态性能视图 ................................................................................................... 35 10.4 管理表空间和数据文件 ............................................................................................... 35 10.4.1 表空间介绍 ....................................................................................................... 35 10.4.2 数据库的逻辑结构 ........................................................................................... 35 10.4.3 建立表空间 ....................................................................................................... 35 10.4.4 使用表空间 ....................................................................................................... 36 10.4.5 改变表空间的状态 ........................................................................................... 36 10.4.6 删除表空间 ....................................................................................................... 36 10.4.7 扩展表空间 ....................................................................................................... 36 10.4.8 移动数据文件 ................................................................................................... 37 第十一章 约束 ....................................................................................................................... 38 11.1 介绍 ............................................................................................................................... 38 11.2 约束 ............................................................................................................................... 38 11.3 使用 ............................................................................................................................... 38 11.3.1 not null(非空) ............................................................................................... 38 11.3.2 unique(惟一) ................................................................................................... 38 11.3.3 primary key(主键) ......................................................................................... 38 11.3.4 foreign key(外键) ......................................................................................... 38 11.3.5 check ................................................................................................................. 38 11.4 实际用例 ....................................................................................................................... 39 11.4.1 案例 1 ................................................................................................................ 39 11.4.2 案例 2 ................................................................................................................ 40 11.5 维护 ............................................................................................................................... 41 11.5.1 删除约束 ........................................................................................................... 41 11.5.2 显示约束信息 ................................................................................................... 41 11.6 列级定义表级定义 ....................................................................................................... 41 11.6.1 列级定义 ........................................................................................................... 41 11.6.2 表级定义 ........................................................................................................... 41 第十二章 索引 ....................................................................................................................... 42 12.1 原理介绍 ....................................................................................................................... 42 12.2 创建索引 ....................................................................................................................... 42 12.2.1 单列索引 ........................................................................................................... 42 12.2.2 复合索引 ........................................................................................................... 42 12.3 使用原则 ....................................................................................................................... 42 12.4 索引的缺点 ................................................................................................................... 42 12.5 显示索引信息 ............................................................................................................... 43 12.5.1 显示表的所用索引 ........................................................................................... 43 12.5.2 显示索引列 ....................................................................................................... 43 第十三章 权限和角色 ........................................................................................................... 43 13.1 介绍 ............................................................................................................................... 43 13.2 权限 ............................................................................................................................... 43 ORACLE 笔记 6 / 63 13.2.1 系统权限 ........................................................................................................... 43 13.2.2 对象权限 ........................................................................................................... 44 13.3 角色 ............................................................................................................................... 46 13.3.1 介绍 ................................................................................................................... 46 13.3.2 预定义角色 ....................................................................................................... 46 13.3.3 自定义角色 ....................................................................................................... 46 13.3.4 角色授权 ........................................................................................................... 46 13.3.5 显示角色信息 ................................................................................................... 47 第十四章 pl/sql 基础 .......................................................................................................... 47 14.1 介绍 ............................................................................................................................... 47 14.1.1 pl/sql 是什么 .................................................................................................. 47 14.1.2 优点 ................................................................................................................... 47 14.1.3 缺点 ................................................................................................................... 47 14.1.4 开发工具 ........................................................................................................... 48 14.2 简单分类 ....................................................................................................................... 48 14.3 编写规范 ....................................................................................................................... 49 14.3.1 注释 ................................................................................................................... 49 14.3.2 命名规范 ........................................................................................................... 49 14.4 pl/sql 块 ...................................................................................................................... 49 14.4.1 块结构示意图 ................................................................................................... 49 14.4.2 只包含执行部分的 pl/sql 块 ......................................................................... 49 14.4.3 包含定义和执行部分的 pl/sql 块 ................................................................. 50 14.4.4 包含定义和执行以及异常处理部分的 pl/sql 块 ......................................... 50 14.5 pl/sql 分类 .................................................................................................................. 50 14.5.1 过程 ................................................................................................................... 50 14.5.2 函数 ................................................................................................................... 51 14.5.3 包 ....................................................................................................................... 51 14.5.4 触发器 ............................................................................................................... 52 14.6 定义并使用变量 ........................................................................................................... 53 14.6.1 标量(scalar) ................................................................................................... 53 14.6.2 复合变量(composite) ..................................................................................... 54 14.6.3 参照变量(reference) ..................................................................................... 55 第十五章 pl/sql 进阶 .......................................................................................................... 56 15.1 控制结构 ....................................................................................................................... 56 15.1.1 条件分支语句 ................................................................................................... 56 15.1.2 循环结构 ........................................................................................................... 57 15.1.3 顺序控制 ........................................................................................................... 58 15.2 编写分页过程 ............................................................................................................... 59 15.2.1 无返回值的过程 ............................................................................................... 59 15.2.2 有返回值的过程(非列表) ............................................................................... 60 15.2.3 有返回值的过程(列表) ................................................................................... 60 15.2.4 有返回值的过程(列表) ................................................................................... 61 15.3 视图 ............................................................................................................................... 62 ORACLE 笔记 7 / 63 15.3.1 介绍 ................................................................................................................... 62 15.3.2 视图与表的区别 ............................................................................................... 63 第一章 系统安装 Oracle 安装会自动生成两个用户:sys、system 1)、 Oracle11g 安装的时候有两个压缩包,将第二个解压到第一个文件件下,找到安装文件 执行安装即可 2)、 sys 用户是超级用户,具有 Oracle 里面的最高权限,具有 sysdba 角色,有 create database 权限,该用户默认的密码是 manager 3)、 system 用户是管理操作员,权限也很大,具有 sysoper 角色,没有 create database 权限, 该用户默认的密码是 change_on_install 4)、一般讲,对数据库维护,使用 system 登陆就可以 5)、目前在市面上的 Oracle 相关书籍、网络课程等,一般都使用 emp dept 等表和 scott 用 户做演示例子;但在 oracle11g 中,默认没有 scott 用户和 emp dept 等表,如需要可以在 %Oracle_Home%\product\11.2.0\dbhome_1\RDBMS\ADMIN 下找到 utlsampl.sql 文件,执行即 可 第二章 常用命令 2.1 连接命令 conn [ect] 用法:conn 用户名/密码@网络服务名 [as sysdba/sysoper] SQL>conn sys/m1234@orcl as sysdba 当用特权用户登录时,必须带上 as sysdba 或者 as sysoper 2.2 修改密码 SQL>passw sys 使用 sysdba 权限登录,可指定修改其他用户的密码(无需输入旧密码) SQL>passw 使用普通权限登录,可修改当前用户的密码(需要输入旧密码) ORACLE 笔记 8 / 63 SQL> alter user scott identified by m123; 2.3 输出暂停 SQL> set pause on; 设置完成之后,敲入 ENTER 键,SQL Plus 将自动显示下一屏 2.4 文件操作命令 2.4.1 start 和@ 说明:运行 SQL 脚本 案例:SQL>@ d:\a.sql 或者 SQL>start d:\a.sql 2.4.2 edit 说明:可以编辑指定的 SQL 脚本 案例:SQL>edit d:\a.sql 2.4.3 spool 说明:可以将 SQL*PLUS 屏幕上的内容输出到指定文件中去 案例: Step1、SQL>spool d:\b.sql; Step2、SQL>select * from emp; Step3、SQL>spool off; 此操作会将 select * from emp; 的结果保存在 d:\b.sql;文件中 2.4.4 sqlldr 说明:sqlldr 可以从一个文件中以很高的速度插入数据到一个表 格式:sqlldr username/password@sid control=*.ctl 例如 data.txt 中,有如下记录: 1,70060105583, 2,70060105591, 3,30000001473, 4,30000001607, …………………………… 现在要将 data.txt 中的数据导入到表 t0916 中 Step1、创建表 t916: SQL> create table t0915(id number(5), khh number(20)); Step2、写 control 文件,将文件存为 data.ctl: load data infile 'D:\temp\data.txt' ORACLE 笔记 9 / 63 append into table t0916 fields terminated by ',' (id, khh) Step3、在 cmd 下执行 sqlldr 命令,导入数据: sqlldr scott/m123@orcl control=D:\temp\data.ctl 此操作会将 data.txt 中的数据插入到表 t0916 中; 2.5 交互式命令 & 说明:可以替代变量,而该变量在执行时,需要用户输入 案例:SQL>select * from emp where job=’&job’; 2.6 时间输出命令 SQL> set timing on; 可输出 SQL 语句所用的执行时间 第三章 输出格式化 3.1 设定 sqlplus 行的长度 SQL> show linesize; SQL> set linesize 100; 3.2 指定分页的尺寸 SQL> show pagesize SQL> set pagesize 20 第四章 用户管理 4.1 创建用户 概述:创建用户使用 create user 语句,一般具有 dba 权限才能使用 案例:SQL>create user test identified by m123; ORACLE 笔记 10 / 63 4.2 删除用户 概述:一般以 dba 身份去删除某个用户,如果用其他用户去删除用户则需要 drop user 权限 案例:SQL>drop user 用户名 【cascade】 说明:如果要删除的用户,已经创建了表,那么就需要在删除时,带一个参数 cascade 4.3 给用户授权 SQL>grant connect to test;(授予用户登陆的角色) 说明: Oracle 的权限分为系统权限与对象权限;系统权限是指用户对数据库的相关权限(建库、 建表、建索引、登陆等),对象权限是指用户对其他用户的数据对象操作的权限。 数据对象:指用户创建的表、视图、存储过程、触发器等 角色:权限的批量定义。角色有分两种:自定义角色、预定义角色;connect 属于预定 义角色的一种,具有 7 种权限。 预定义角色举例:dba(具有管理员权限)、resource(可以在任何一个表空间建表)、connect 用例:让 test 用户可以去查询 emp 表 SQL>grant select on emp to test; SQL>select * from sys.emp; (切换到 test 用户,查询时需要带上所属者.表名) 图例: ORACLE 笔记 11 / 63 4.4 收回授权 revoke 权限 on 表 from 用户 SQL>revoke select on emp from test; 4.5 权限传递 需求说明:希望用户具有这个权限的同时,还可以把权限授予给其他用户 1) 如果要传递的是对象权限,就在授权语句后面加 with grant option: SQL>grant select on emp to test with grant option; 2) 如果要传递的是系统权限,就在授权语句后面加 with admin option: SQL>grant connect to zhangsan with admin option; 问题:如果 sys 把 test 对 emp 表的查询权限回收了,zhangsan 还能继续查询 emp 表吗? zhangsan 的权限也会被回收 权限(200 多) 系统权限 对象权限 指用户对数据库的相关权限: (建库、建表、建索引、登陆等) 指用户对其他用户的数据对象 访问(操作)的权限 比如表、视图、过程………… 角色 connect、resource、dba 等 (相当于权限的批量授权) 文字说明 权限 1 权限 2 权限 3 预定义角色 自定义角色 Select、insert、update、 delete、all、create index ……… ORACLE 笔记 12 / 63 4.6 profile 使用 profile 管理用户口令 说明:profile 是口令限制,资源限制的命令集合,当建立数据库时,oracle 会自动建立名称 为 default 的 profile.当建立用户没有指定 profile 选项,oracle 就会将 default 分配用用户。 4.6.1 账户锁定 概述: 指定该用户登录时最多可输入密码的次数,可可以指定用户锁定的时间(天),一般用 dba 的身份去执行该命令 例子: 指定 test 最用只能尝试 3 此登录,锁定时间为 2 天 创建规则: SQL>create profile lock_account limit failed_login_attempts 3 password_lock_time 2; 给用户指定规则: SQL>alter user test profile lock_account; 4.6.2 给用户解锁 SQL>alter user test account unlock; 4.6.3 还原 profile SQL>alter user test profile default; 4.6.4 终止口令 为了让用户定期修改密码,可以使用终止口令的指令来完成,同样这个口令也需要以 dba 身份来操作 例子: 给 test 用户创建一个 profile 文件,要求 test 每隔 10 天修改自己的登录密码,宽限期为 2 天。 SQL>create profile change_pwd limit password_life_time 10 password_grace_time 2; SQL>alter user test profile change_pwd; 4.6.5 口令历史 如果希望用户在修改密码时候,不能使用以前使用过的密码,可以启用口令历史 SQL>create profile pwd_history limit password_life_time 10 password_grace_time 2 ORACLE 笔记 13 / 63 password_reuser_time 10 --10 天后可以重用 SQL>alter user test profile pwd_history; 4.6.6 删除 profile SQL>drop profile pws_history【cascade】 cascade 会把相关级联的东西都删去 第五章 表的管理 5.1 命名规则 表和列名: 1) 必须以字母开头 2) 长度不能超过 30 个字符 3) 不能使用 oracle 保留字 4)只能使用 A-Z,a-z,0-9,$,#等字符 5.2 数据类型 5.2.1 字符型 共有三种:  char char 是定长的,最大 2000 字符 例如:char(10) ‘张三’,前面四个字符存放张三,后面六个字符为空 char 虽然是定长型的,但是效率很高,查询效率极快  varchar2 varchar2 是变长的,最大 4000 字符 例如:varchar2(10) ‘张三’,oracle 分配四个字符,这样更节省空间  clob 字符型大对象,最大 4G 5.2.2 数字型 Oracle 中的数字类型,全部用 number 表示: number 范围 -10 的 38 次方到 10 的 38 次方;可以表示整数,也可以表示小数 number(5,2) 表示一个小数有 5 位有效数,2 位小数;范围-999.99 到 999.99 ORACLE 笔记 14 / 63 number(5)表示一个五位整数;范围-99999 到 99999 5.2.3 日期类型  date 包含年月日时分秒  timestamp 时间戳类型.是 oracle9i 对 date 类型的扩展,精度比较高 5.2.4 图片类型 blob 二进制数据 可以存放图片/声音/文件等 5.3 建表 5.3.1 表 1 SQL> create table student( --表名 xh number(4), --学号 xm varchar2(20), --姓名 sex char(2), --性别 birthday date, --生日 sal number(7,2) --奖金 ); 5.3.2 表 2 SQL> create table class( calssid number(2), cname varchar2(20) ); 5.4 修改表 5.4.1 加字段 SQL>alter table student add(classid number(2)); 5.4.2 改长度 SQL>alter table student modify(xm varchar2(30)); 5.4.3 改字段的类型/名字 SQL>alter table student modify (xm char(30)); 修改时此列不能有数据 5.4.4 删字段 SQL>alter table student drop column sal; 5.4.5 改表名 ORACLE 笔记 15 / 63 SQL>rename student to stu; 5.4.6 删除表 SQL>drop table stu; 5.4.7 清理被 drop 掉的表 SQL> purge recyclebin; 5.5 添加数据 5.5.1 所有字段都插入 SQL> insert into student values(1,'张三','男','01-5 月-05',10,1); 说明: oracle 中默认的日期格式是:’DD-MON-YY’,两位的年:'01-5 月-05'表示 2005 年 5 月 1 日 修改日期的默认格式: SQL> alter session set nls_date_format = 'yyyy-MM-dd'; 修改后,可以使用如下语句添加日期类型: SQL> insert into student values(2,'李四','男','1905-05-06',10,1); 5.5.2 插入部分字段 SQL> insert into student(xh,xm,sex) values(3,'王五','女'); 5.5.3 插入空值 SQL> insert into student(xh,xm,sex,birthday) values(4,'赵六','男',null); 5.5.4 修改一个字段 SQL> update student set sal=sal*2 where sex='男'; 5.5.5 修改多个字段 SQL> update student set sal=sal*2,classid=2 where sex='男'; 5.5.6 从自身复制 SQL> insert into student(xh,xm,sex,birthday) select * from student; 5.5.7 修改含有 null 的值 SQL> update student set sal=13.231 where birthday is null; 5.5.8 删除数据 SQL> delete from student; 说明:会删除所有记录,表结构还在,写在日志中,可以恢复,速度慢 ORACLE 笔记 16 / 63 恢复说明: SQL> savepoint save_student; SQL> delete from student; SQL> rollback to save_student; SQL> drop table student; 说明:删除表的结构和数据 SQL> truncate table student; 说明:删除表中的所有记录,表结构还在,不写日志,无法找回删除记录,速度快 ORACLE 笔记 17 / 63 第六章 数据查询 6.1 例表说明 Emp 表各字段含义如下: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO Id 号 姓名 岗位 上级 Id 入职时间 工资 奖金 部门 6.2 简单查询 6.2.1 查看表结构 SQL> desc dept; 6.2.2 查询所有列 SQL> select * from dept; 6.2.3 查询指定列 SQL> select eanme from emp; 6.2.4 去重 SQL> select distinct deptno,job from emp; 6.2.5 算数运算符 SQL> select ename, sal*12 from emp; 6.2.6 使用别名 SQL> select ename 姓名, sal*12 年工资 from emp; SQL> select ename 姓名,sal*12+comm*12 年工资 from emp; 6.2.7 处理 null 值 在 Oracle 中,使用算数运算符时,当其中一个值为 null,整个结果也会为 null 使用 nvl 函数: SQL> select ename 姓名,sal*12+nvl(comm,0)*12 年工资 from emp; nvl(comm,0)的意思是,如果 comm 是 null,就用 0 替代;如果不是 null,就用自身值 6.2.8 like 操作符 %表示 0 到多个字符;_表示单个字符 ORACLE 笔记 18 / 63 6.2.9 in SQL> select * from emp where empno in(123,234); 6.2.10 exists SQL> select * from emp where exists(select deptno from dept where emp.deptno=dept.deptno and dept.deptno=10); 6.2.11 is null SQL> select * from emp where empno mgr is null; 6.2.12 逻辑操作符 SQL> select * from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’; 6.2.13 order by SQL> select * from emp order by sal desc; SQL> select * from emp order by deptno, sal desc; 6.2.14 使用列的别名排序 SQL> select ename, sal*12 年薪 from emp order by 年薪; 6.3 复杂查询 6.3.1 数据分组 max、min、avg、sum、count  显示所有员工中最高工资和最低工资 SQL> select max(sal),min(sal) from emp;  显示工资最高的那个员工的姓名 SQL> select ename,sal from emp where sal=(select max(sal) from emp);  显示所有员工的平均工资和工资总和 SQL> select avg(sal), sum(sal) from emp;  显示工资高于平均工资的员工信息 SQL> select * from emp where sal>(select avg(sal) from emp); 6.3.2 group by 和 having group by 用户对查询的结果分组 having 用于限制分组显示结果 ORACLE 笔记 19 / 63  显示每个部门的平均工资和最高工资 SQL> select avg(sal),max(sal),deptno from emp group by deptno;  显示每个部门每种岗位的平均工资和最高工资 SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,job;  显示平均工资低于 2000 的部门号和它的平均工资 SQL> select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000; 需要注意: 如果在select语句中同时包含有group by, having, order by;那么他们的顺序只能是group by, having, order by 6.3.2 多表查询  显示雇员名,雇员工资以及所在部门的名字【笛卡尔集】 SQL> select a1.ename, a1.sal, a2.dname from emp a1, dept a2 where a1.deptno=a2.deptno; 多表查询的条件数量至少不少于表的个数-1  显示部门号为10的部门名、员工名和工资 SQL> select a1.dname, a2.ename, a2.sal from dept a1, emp a2 where a1.deptno=a2.deptno and a1.deptno=10;  显示员工的姓名、工资、工资级别 SQL> select a1.ename, a1.sal, a2.grade from emp a1, salgrade a2 where a1.sal between a2.losal and a2.hisal;  显示某个员工的上级领导的姓名,比如“FORD”的上级 SQL> select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno and worker.ename='FORD'; 6.3.3 联接查询 联接查询用于指定多数据源(表、视图)之间如何组合,以形成最终的数据。  自然连接 将两个数据源中具有相同名称的列进行连接,使用 natural join SQL> select * from student natural join student_score; ORACLE 笔记 20 / 63  内连接 内联接是用比较运算符比较要联接列的值的联接,使用 join 或inner join。 例如下边语句: SQL> select * from student join student_score on student.id=student_score.id; 说明:只返回符合条件的student和student_score的列。  外连接 外连接将其中一个数据源指定为基表,另一个数据源可以看做附表。在最终的数据中, 一定含有基表中的数据,而附表中的数据是否出现,依具体的连接条件而定。 外连接分为三种:左连接、右连接、完全连接 1)左连接:使用left join 或 left outer join 左向外联接的结果集包括left join子句中指定的左表的所有行,而不仅仅是联接列 所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选 择列表列均为空值(null) SQL> select * from student left join student_score on student.id=student_score.id; 说明:包含student的所有子句,根据指定条件返回student_score相应的字段,不符合的 以null显示。 2)右连接:使用right join 或 right outer join 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中 没有匹配行,则将为左表返回空值。 SQL> select * from student right join student_score on student.id=student_score.id; 说明:包含student_score的所有子句,根据指定条件返回student相应的字段,不符合的 以null显示。 3)完全连接:使用 full join 或 full outer join 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一 个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。 SQL> select * from student full join student_score on student.id=student_score.id; 说明:返回左右连接的和。 6.3.4 子查询  什么是子查询 子查询是指嵌套在其他SQL语句中的Select语句,也叫嵌套查询  单行子查询 是指返回一行数据的子查询语句 例如:显示与SMITH同一部门的所有员工 ORACLE 笔记 21 / 63 SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');  多行子查询 是指返回多行数据的子查询 例如:显示包含部门10中职位的雇员信息 SQL> select * from emp where job in(select job from emp where deptno=10);  在多行子查询中使用 all 操作符 显示比部门30的员工最高工资高的员工姓名、工资和部门号 SQL> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30); SQL> select ename,sal, deptno from emp where sal > (select max(sal) from emp where deptno=30);  在多行子查询中使用 any 操作符 显示比部门30的员工最低工资高的员工姓名、工资和部门号 SQL> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30); SQL> select ename,sal, deptno from emp where sal > (select min(sal) from emp where deptno=30);  多列子查询 是指查询返回多个列数据的子查询语句 查询与SMITH的部门和岗位完全相同的所有雇员 SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');  在From字句中使用子查询 显示高于自己部门平均工资的员工信息 Step1、查询出各个部门的平均工资和部门号 SQL> select deptno, avg(sal) mysal from emp group by deptno; Step2、把上边的查询结果看做是一张子表 SQL> select a1.ename, a1.sal, a1.deptno, a2.mysal from emp a1, (select deptno, avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal; 说明:当在From字句中使用子查询时,该子查询会被作为一个视图来对待,因此也叫 内嵌视图,当在From字句中使用子查询时,必须给子查询指定别名,给表指定别名的时候 不要加 as ORACLE 笔记 22 / 63  分页查询 按雇员ID号倒序取出第6条到第10条数据 Setp1、查询所有记录 SQL> select * from emp; Step2、显示出来行的ID号(rownum) SQL> select a1.*, rownum rm from (select * from emp) a1; Step3、将所有记录看成一个内嵌视图,并设置rownum条件 SQL> select a1.*, rownum rn from (select * from emp) a1 where rownum <=10; Step4、将rownum结果集再看成一个内嵌视图,设置rownum条件 SQL> select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum <=10) where rn>=6; 其中最内层的查询select * from emp表示不进行翻页的原始查询语句。rownum <= 10和 rn>= 6 控制分页查询的每页的范围 说明: 1) 如要指定查询列,只需修改最里层的子查询即可 2) 如要指定排序列,只需修改最里层的子查询即可 Step5、指定排序字段 SQL> select * from (select a1.*, rownum rn from (select * from emp order by empno desc) a1 where rownum <=10) where rn>=6;  用查询结果创建新表 这是一种快捷的建表方法,此命令会创建表,并导入原表数据 SQL> create table result_emp(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp; 6.3.5 合并查询 有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号:union、 union all、intersect、minus 1)union 用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中重复行。 SQL> select ename, sal, job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER'; 2)union all 和union相似,但是它不会取消重复行,用于取得两个结果的并集 SQL> select ename, sal, job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER'; 3)intersect ORACLE 笔记 23 / 63 用于取得两个结果的交集 SQL> select ename, sal, job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER'; 3)minus 用于取得两个结果集的差集;它只会显示存在第一个集合中,而不存在第二个集合中的数据 SQL> select ename, sal, job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER'; 第七章 操作数据 7.1 使用特定格式插入日期 使用to_date函数,按照yyyy-mm-dd格式插入日期 SQL> insert into emp values(7935,'irke','MANAGER',7839,to_date('1987-01-22','yyyy-mm-dd'), 2000.00,null,10); 7.2 使用子查询插入数据 7.2.1 介绍 使用values插入数据时,一次只能插入一行;使用子查询插入数据,一条insert语句可以 插入大量数据;当处理行迁移或者装载外部表数据到数据库时,可以使用子查询来插入数据。 7.2.2 说明 Setp1、新建表 SQL> create table insert_emp(id number(4),name varchar2(50),dept number(5)); Setp2、导入 emp 表 10 号部门的数据到 insert_emp 表中 SQL> insert into insert_emp(id,name,dept) select empno,ename,deptno from emp where deptno=10; 7.3 使用子查询更新数据 7.3.1 介绍 使用 update 语句更新数据时,既可以使用表达式或者数值直接修改数据,也可以使用 子查询修改数据 7.3.2 说明 希望员工 BLAKE 的岗位、工资、补助与 SMITH 一样 SQL> update emp set (job, sal, comm)=(select job,sal,comm from emp where ORACLE 笔记 24 / 63 ename='SMITH') where ename='BLAKE'; 第八章 事务处理 8.1 什么是事务 事务用于保证数据的一致性,它由一组相关的 dml 语句自称,该组的 dml 语句要么全 部成功,要么全部失败。 DML 语句简单说,就是增加、删除、修改语句 例如:网上银行就是典型的例子,要用事务来处理,用以保证数据的一致性。 8.2 事务和锁 当执行事务操作时(DML 语句),oracle 会在被作用的表上加锁,防止其他用户修改表 的结构和数据。 8.3 提交事务 执行 commit 语句可以提交事务,当执行了 commit 语句之后,会确认事务的变化、结 束事务、删除保存点、释放锁,当使用 commit 语句结束事务之后,其他会话可以查看到事 务变化后的新数据 8.4 回退事务 需要再说一下保存点(savepoint)的概念和作用,保存点是事务中的一点,用于取消部 分事务,当结束事务时,会自动删除该事务所定义的所有保存点。 事务回退有一个前提,只在事务没有提交(commit)的情况下生效,否则无论设置多 少个保存点,都是不能回退的。事务一旦提交,之前设置的保存点会全部失效。 8.5 事务的几个重要操作 5.5.1 设置保存点 savepoint p1 5.5.2 取消部分事务 rollback to p1 ORACLE 笔记 25 / 63 5.5.3 取消全部事务 rollback 8.6 Java 程序中如何使用事务 比如从一个账户里减掉 10 块钱,在另外一个账户上加 10 块钱 看代码,代码中举例 JDBC 事务 8.7 只读事务 8.7.1 说明 只读事务是指允许执行查询的操作,而不允许执行其他任何 DML 操作的事务,使用只 读事务可以确保用户只能取得某事某点的数据。 8.7.2 案例 假定机票代理店每天 18 点开始统计今天的销售情况,这时可以使用只读事务,在设置 了只读事务后,尽管其他会话可能提交新的事务,但是只读事务将不会取得最新数据的变化, 从而可以确保取得特定时间点的数据信息。 8.7.3 设置只读事务 set transaction read only 系统管理员(sys)设置只读事务,scott 用户给 emp 表插入一条数据,看 sys 是否能看 到最 scott 插入的最近数据 第九章 SQL 函数 9.1 字符串函数 9.1.1 lower(char) 说明:将字符串转换为小写格式 需求:将所有员工的名字按小写的方式显示 SQL> select lower(ename) from emp; 9.1.2 upper(char) 说明:将字符串转换为大写格式 需求:将所有员工的名字按大写的方式显示 SQL> select upper(ename) from emp; 9.1.3 length(char) ORACLE 笔记 26 / 63 说明:返回字符串的长度 需求:显示正好为 5 个字符的员工的姓名 SQL> select * from emp where length(ename)=5; 9.1.4 substr(char,m,n) 说明:取字符串的子串 需求:显示所有员工姓名的前三个字符 SQL> select ename, substr(ename,1,3) from emp; 9.1.5 函数组合使用 需求:以首字母大写,其他字母小写的方式显示所有员工的姓名 SQL> select ename, upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; || 是合并的意思 9.1.6 replace(char,search_str,replace_str) 说明:替换字符串值 需求:显示所有员工的姓名,用 a 替换所有 A SQL> select ename, replace(ename,'A','a') from emp; 9.2 数学函数 9.2.1 round(n,m) 说明:用于执行四舍五入。如果省掉 m,则四舍五入到整数;如果 m 是正数,则四舍五入 到小数点的 m 位后,如果 m 是负数,则四舍五入到小数点的 m 位前 SQL> select ename, round(sal) from emp where ename='wangwu'; SQL> select ename, round(sal,1) from emp where ename='wangwu'; 9.2.2 trunc(n,m) 说明:用于截取数字。如果省掉 m,就截去小数部分,如果 m 是正数就截取到小数点的 m 位后,如果 m 是负数,则截取到小数点的前 m 位 SQL> select ename, trunc(sal) from emp where ename='wangwu'; SQL> select ename, trunc(sal,1) from emp where ename='wangwu'; 9.2.3 mod(m,n) 说明:取模操作,即显示余数 SQL> select mod(10,3) from dual; 9.2.4 floor(n) 说明:向下取整。返回小于或是等于 n 的最大整数 SQL> select ename, floor(sal),sal from emp where ename='wangwu'; 9.2.5 ceil(n) ORACLE 笔记 27 / 63 说明:向上取整。返回大于或是等于 n 的最小整数 SQL> select ename, ceil(sal),sal from emp where ename='wangwu'; 9.3 日期函数 日期函数用于处理 date 类型数据,默认情况下日期格式是 dd-mon-yy,即 18-7 月-99 9.3.1 sysdate 说明:返回系统时间 SQL> select sysdate from dual; 需求:显示每个员工加入公司的天数 SQL> select ename, hiredate, trunc(sysdate-hiredate) 入职天数 from emp; 9.3.2 add_months(d,n) 说明:加月份 需求 1:查找已经入职 8 个月多的员工 SQL> select * from emp where sysdate>add_months(hiredate,8); sysdate 表示系统时间,add_months(hiredate,8)表示雇佣时间加上八个月以后的时间 需求 2:显示满 10 年服务年限的员工的姓名和受雇日期 SQL> select * from emp where sysdate>=add_months(hiredate,12*10); 9.3.3 last_day(d) 说明:返回指定日在所在月份的最后一天 需求:找出各月倒数第 3 日天受雇的所有员工 SQL> select ename, hiredate, last_day(hiredate) from emp where hiredate = last_day(hiredate)-2; 9.4 转换函数 9.4.1 功能介绍 转换函数用于将数据类型从一种转为另外一种,在某些情况下,oracle server 允许值的 数据类型和实际的不一样,这时 oracle server 会隐含的转化数据类型 比如: SQL> create table t1 (id varchar2(10)); SQL> insert into t1 values(1); 这样 oracle 就会自动将 1 转换成 ’1’ 注意:尽管 oracle 可以进行隐含的数据类型的转换,但是它并不适应所有的情况,为了提高 程序的可靠性,应该使用转换函数进行转换。 9.4.2 to_char ORACLE 笔记 28 / 63 说明:将其他类型转换为字符串  需求1:按照指定格式显示日期 Step1、插入一条带有时分秒时间的记录 SQL> insert into emp values(9996,'zhaoliu','MANAGER',7839,sysdate, 1200,138,10); Step2、按照指定格式查询显示日期 SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp; 时间格式说明:  需求2:按照指定货币符号显示薪水 SQL> select ename, sal, to_char(sal,'L99,999.99') from emp; 说明:L 是 local,代表本地货币符号;99999.99 的意思是,显示五位整数,后面带两位小数 货币格式说明:  需求3:显示1980年入职的员工 SQL> select ename, hiredate from emp where to_char(hiredate,'yyyy')=1980;  需求4:显示12月份入职的员工 SQL> select ename, hiredate from emp where to_char(hiredate,'mm')=12; 9.4.3 to_date 说明:用于将字符串转成 date 类型的数据 yyyy mm dd:年月日 hh24:24 小时制 mi:分钟 ss:秒 9:显示数字,并忽略前面 0 0:显示数字,如位数不足,则用 0 补齐 .:在指定位置显示小数点 ,:在指定位置显示逗号 $:在数字前加美元 L:在数字前加本地货币符号 C:在数字前加国际货币符号 G:在指定位置显示组分隔符 D:在指定位置显示小数点符号 ORACLE 笔记 29 / 63 9.5 系统函数 9.5.1 sys_context  terminal 当前会话客户所对应的终端的标识符  language 语言  db_name 当前数据库名称  nls_date_format 当前会话客户所对应的日期格式  session_user 当前会话客户所对应的数据库用户名  current_schema 当前会话客户所对应的默认方案名  host 返回数据库所在主机的名称  使用方法 返回数据库所在主机的名称 SQL> select sys_context('userenv','db_name') from dual; 第十章 数据库管理 10.1 数据库管理员 10.1.1 介绍 每个 oracle 数据库应该至少有一名数据库管理员,对于一个大的数据库,还可能需要多 个 dba 分别担负不同的管理责任 10.1.2 管理员职责 1)安装和升级数据库 2)建库、建表空间、建表、建视图、建索引等 3)指定并实施备份与恢复计划 ORACLE 笔记 30 / 63 4)数据库权限管理,调优,故障排除 5)编写 sql 语句、存储过程、触发器、规则、约束、包 10.1.3 sys、system 管理数据库的用户主要是 sys 和 system,两个用户的主要区别: 1)存储的数据的重要性不同 sys: 所有 oracle 的数据字典的基表和视图都存放在 sys 用户中,这些基表和视图对于 oracle 运行是至关重要的,由数据库自己维护,任何用户都不能手动更改。sys 用户拥有 sysdba、 sysoper、dba 角色或权限,是 oracle 权限最高的用户。 system: 用于存放次一级的内部数据,比如 oracle 的一些特性或工具的管理信息。system 用户拥 有 sysdba、dba 角色或系统权限。 2)权限的不同 sys 用户必须以 as sysdba 或者 as sysoper 形式登陆,不能以 normal 方式登陆数据库 system 如果正常登陆,其实就是一个普通的 dba 用户,但是如果以 as sysdba 登陆,实际上 它是作为 sys 用户登陆的,从登陆信息可以看出来。 10.1.4 sysdba 和 sysoper 权限区别 系统权限 sysdba sysoper startup(启动数据库) √ √ shutdown(关闭数据库) √ √ alter databse open/mount/backup √ √ 改变字符集 √ × create database √ × drop database √ × create spfile √ √ alter database archivlog(归档日志) √ √ alter database recover(恢复数据库) √ 只能完全恢复,不能执行不完全 恢复 拥有 restricted session(会话限制)权限 √ √ 可以让用户作为 sys 用户连接 √ 可以进行一些基本的操作,不能 查看用户数据 登陆之后用户是 sys √ 登陆之后用户是 public 10.1.5 dba 权限用户 特权用户可以执行启动实例,关闭实例;而 dba 用户只有在启动数据库之后才能进行各 种管理工作。 10.1.6 管理初始化参数 初始化参数用户设置实例或是数据库的特征,初始化参数大概有 200 多个,并且每个初 ORACLE 笔记 31 / 63 始化参数都有默认值。  显示参数 show parameter 命令 SQL> show parameter; SQL> show parameter cpu_count;  修改参数 可以到%Oracle_Home% \admin\orcl\pfile 下修改 init.ora 文件中的相关参数 10.2 逻辑备份与恢复 10.2.1 介绍 逻辑备份是指使用工具 export(exp)将数据对象的结构和数据导出到文件的过程;逻辑恢 复是指使用工具 import(imp)把备份的文件恢复。 物理备份可以在数据库打开或者关闭的状态下进行,逻辑备份只能在 open 的状态下进 行。 10.2.2 数据导出 导出具体分为:导出表,导出方案,导出数据库三种 导出使用 exp 命令来完成,exp 命令的常用选项有:  导出表 1)导出自己的表 exp userid=scott/m123@orcl tables=(emp) file=d:\export\scott_emp.dmp 说明:tables 参数中可以携带多张表 2)导出其他用户的表 如果要导出其他用户的表,则需要 dba 或 exp_full_database 权限,比如 system 用户就 可以导出其他用户的表 exp userid=system/manager@orcl tables=scott.emp file=d:\export\system_scott_emp.dmp 3)导出表结构 只导出表结构,不导出数据 userid:用于指定执行导出操作的用户名,口令,连接字符串 tables:用于指定执行导出操作的表 owner:用于指定执行导出操作的方案 full=y:用于指定执行导出整个文件 inctype:用于指定执行导出操作的增量类型 rows:用于指定执行导出操作是否要导出表中的数据 file:用于指定导出文件名 direct:用于指定是否直接导出 ORACLE 笔记 32 / 63 exp userid=scott/m123@orcl tables=emp rows=n file=d:\export\scott_desc_emp.dmp 4)使用直接导出方式 这种方式比常规导出方式要快,当数据量大时,可以考虑 exp userid=scott/m123@orcl tables=emp direct=y file=d:\export\scott_direct_emp.dmp  导出方案 导出方案是指使用export工具导出一个或多个方案中的所有对象(表、视图、索引、约 束)和数据,并存储在文件中。 1)导出自己的方案 exp userid=scott/m123@orcl owner=scott file=d:\export\scott.dmp 2)导出其他方案 如果用户要导出其他方案,则需要 dba 或 exp_full_database 权限,比如 system 用户就 可以导出其他用户的方案 exp userid=system/manager@orcl owner=(system,scott) file=d:\export\system_scott.dmp  导出数据库 导出所有数据库中的对象及数据,需要dba或exp_full_database权限 exp userid=system/manager@orcl full=y inctype=complete file=d:\export\all_db.dmp 10.2.3 数据导入 数据导入就是使用 import(imp)将文件中的对象和数据导入到数据库中,导入要使用的 文件必须是 export 所导出的文件。与导出相似,导入也分为导入表、方案、数据库三种方 式。导入使用 imp 命令来完成,imp 命令的常用选项有:  导入表 1)导入自己的表 imp userid=scott/m123@orcl tables=(emp) file=d:\export\scott_emp.dmp userid:用于指定执行导入操作的用户名,口令,连接字符串 tables:用于指定执行导入操作的表 formuse:用于指定源用户 touser:用于指定目标用户 full=y:用于指定执行导入整个文件 inctype:用于指定执行导入操作的增量类型 rows:用于指定执行导入操作是否要导入表中的数据 file:用于指定导入文件名 ignone:如果表存在,则只导入数据 ORACLE 笔记 33 / 63 说明:tables 参数中可以携带多张表 2)导入其他用户的表 如果要导入其他用户的表,则需要 dba 或 exp_full_database 权限,比如 system 用户就 可以导入其他用户的表 imp userid=system/manager@orcl tables=emp touser=scott file= d:\export\system_scott_emp.dmp 3)导入表结构 只导入表结构,不导入数据 imp userid=scott/m123@orcl tables=emp rows=n file=d:\export\scott_desc_emp.dmp 4)导入数据 如果对象(表)已经存在,则指导入数据 imp userid=scott/m123@orcl tables=emp ignore=y file=d:\export\scott_emp.dmp  导入方案 导入方案是指使用 import 工具将文件中的对象和数据导入到一个或多个方案中。如果 要导入其他方案,则需要 dba 或 exp_full_database 权限。 1)导入自己的方案 imp userid=scott/m123@orcl file=d:\export\scott.dmp 2)导入其他方案 imp userid=system/manager@orcl fromuser=system touse=scott file=d:\export\system_scott.dmp  导入数据库 导入所有数据库中的对象及数据,需要dba或exp_full_database权限 imp userid=system/manager@orcl full=y file=d:\export\all_db.dmp 10.3 数据字典和动态性能视图 10.3.1 介绍 数据字典是 oracle 中数据库中最重要的组成部分,它提供了数据库的一些系统信息。 动态性能视图记载了例程启动后的相关信息。 10.3.2 数据字典 数据字典记录了数据库的系统信息,它是只读表个视图的集合,数据字典的所有者为 sys 用户。用户只能在数据字典上执行查询操作,而其维护和修改是由系统自动完成的。 数据字典的组成: ORACLE 笔记 34 / 63 包括数据字典基表和数据字典视图,其中基表存储数据库的基本信息,普通用户不能直 接访问数据字典的基表,数据字典视图是基于数据字典基表建立,普通用户可以通过查询视 图来取得系统信息。数据字典视图主要包括:user_xxx、all_xxx、dba_xxx 三种类型。  user_tables 用于显示当前用户所拥有的所有表,它只返回用户所对应方案的所有表。 比如:SQL> select * from user_tables;  all_tables 用于显示当前用户可以访问的所有表,它不仅返回当前用户方案的所有表,还会访问当 前用户可以访问的其他用户的表。 比如:SQL> select * from all_tables;  dba_tables 它会显示所有方案拥有的数据库表,但是查询这种动态视图要求用户必须是 dba 角色或 者具有 select any table 系统权限 比如:当用 sys 用户查询 dba_tables 时,会返回 sys、system、scott…方案对应的数据库表 SQL> select * from dba_tables;  用户名、权限、角色 在建立用户时,oracle 会把用户的信息存放到数据字典中,当给用户授予权限或是角色 时,oracle 会将权限和角色的信息存放到数据字典。 通过查询 dba_user 可以显示所有数据库用户的详细信息; 通过查询 dba_sys_privs 可以显示用户具有的系统权限 通过查询 dba_tab_privs 可以显示用户具有的对象权限 通过查询 dba_col_privs 可以显示用户具有的列权限 通过查询 dba_role_privs 可以显示用户具有的角色 几个常用查询: 1)查询 scott 拥有哪些角色 SQL> select * from dba_role_privs where grantee='SCOTT'; 2)查询 Oracle 中具有的系统权限列表 SQL> select * from system_privilege_map order by name; 3)查询 Oracle 中具有的对象权限列表 SQL> select distinct privilege from dba_tab_privs; 4)显示当前用户可以访问的所有数据字典视图 SQL> select * from dict where comments like '%grant%'; 5)显示当前数据库的全称 SQL> select * from global_name; 6)查询某个角色包含的权限(系统权限+对象权限) Step1:查询某个角色包含哪些系统权限 SQL> select * from dba_sys_privs where grantee='CONNECT'; 或 ORACLE 笔记 35 / 63 SQL> select * from role_sys_privs where role='CONNECT'; Step2 查询某个角色包含哪些对象权限 SQL> select * from dba_tab_privs where grantee='CONNECT'; 7)查询 Oracle 中具有多少种角色 SQL> select * from dba_roles;  其他说明 数据字典记录有 oracle 数据库的所有系统信息,通过查询数据字典可以取得系统信息 比如: 1)对象定义情况 2)对象占用空间大小 3)列信息 4)约束信息 ...... 10.3.3 动态性能视图 动态性能视图用于记录当前例程的活动信息,当启动 oracle 时,系统会建立动态性能视 图;当停止 oracle 时,系统会删除动态性能视图。oracle 的所有动态性能视图都是以 v_$开 始的,动态性能视图的所有者为 sys,一般情况下,由 dba 或是特权用户来查询动态性能视 图。 在实际运用中较少涉及,此处简单做以上介绍。 10.4 管理表空间和数据文件 10.4.1 表空间介绍 表空间是数据库的逻辑组成部分。从物理上来讲,数据库数据存放在数据文件中;从逻 辑上讲,则是存放在表空间中,表空间由一个或是多个数据文件组成。通过表空间可以达到 以下作用: 1) 控制数据库占用的磁盘空间 2) DBA 可以将不同数据库类型部署到不同的位置,这样有利于提高 IO 性能,同时利 于备份和恢复等操作。 10.4.2 数据库的逻辑结构 Oracle 中逻辑结构包括表、段、区和块。 数据库由表空间构成,表空间由段构成,段由区构成,区由块构成,这样可以提高数据 库的效率。 10.4.3 建立表空间 建立表空间是使用 create tablespace 命令完成的,一般情况下,建立表空间是 dba 来执行的;如果使用其他用户来创建表空间,用户需要具 create tablespace 权限。 创建语句: SQL> create tablespace data01 datafile ORACLE 笔记 36 / 63 'E:\app\Administrator\oradata\orcl\data01.dbf' size 20m uniform size 128k; 说明: 建立名称为 data01 的表空间,并为该表空间建立名为 data01.dbf 的数据文件,区的大 小为 128k,最大 20m 10.4.4 使用表空间 SQL> create table mypart(deptno number(2), dname varchar2(14), loc varchar2(13)) tablespace data01; 10.4.5 改变表空间的状态 当建立表空间时,表空间出于联联机(online)状态,此时该表空间是可以访问的,并 且是可读写的。但是在进行系统维护时,可能需要改变表空间的状态,一般情况下,由特权 用户或是 dba 来操作。 1)使表空间脱机 SQL> alter tablespace data01 offline; 2)使表空间联机 SQL> alter tablespace data01 online; 3)只读表空间 当建立表空间时,表空间可以读写,如果不希望在该表空间上执行 update、delete、insert 操作,可以将表空间修改为只读。 SQL> alter tablespace data01 read only; 恢复可读写状态: SQL> alter tablespace data01 read write; 4)显示表空间下的所有表 SQL> select * from all_tables where tablespace_name='DATA01'; 5)查询表属于哪个表空间 SQL> select tablespace_name, table_name from user_tables where table_name='EMP'; system 是系统表空间,不能设置为只读。 10.4.6 删除表空间 一般情况下,由特权用户或是 dba 来操作,如果是其他用户操作,要求有 drop tablespace 权限。 SQL> drop tablespace data01 including contents and datafiles; 说明:including contents 表示删除表空间时候,删除该表空间的所有数据库对象, datafiles 表示将数据库文件也删除。 10.4.7 扩展表空间 表空间是由数据文件组成的,表空间的大小实际上就是数据文件相加后的大小。假定表 table1 存放在表空间 data01 上,初始大小是 2m,当数据满 2m 之后,如果再向表中插入数 ORACLE 笔记 37 / 63 据,就会显示空间不足的错误。 实际举例说明: Step1、建立一个表空间 sp01,最大 2m SQL> create tablespace sp01 datafile 'E:\app\Administrator\oradata\orcl\sp01.dbf' size 2m uniform size 128k; Step2、在表空间上建一个表 mydept,结构和 dept 表一样 SQL> create table mydept(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace sp01; Step3、向表中插入数据,直到数据超出表空间大小,这时会提示错误。 SQL> insert into mydept values(001,'zs','bj'); SQL> insert into mydept select * from mydept; Step4、扩展表空间,为其增加更多的存储空间。有三种方法: 1)增加数据文件 SQL> alter tablespace sp01 add datafile 'E:\app\Administrator\oradata\orcl\sp02.dbf' size 20m; 2)增加数据文件的大小(1) SQL> alter database datafile 'E:\app\Administrator\oradata\orcl\sp01.dbf' resize 20m; 3)增加数据文件的大小(2) SQL> alter database datafile 'E:\app\Administrator\oradata\orcl\sp01.dbf' autoextend on next 10m maxsize 500m; 10.4.8 移动数据文件 有时,比如数据文件所在的磁盘损坏了,为了能重新使用,需要将这些文件的副本移动 到其他磁盘,然后恢复。 以移动 sp01.dbf 为例说明: Step1、确定数据文件所在的表空间 SQL> select tablespace_name from dba_data_files where file_name='E:\APP\ADMINISTRATOR\ORADATA\ORCL\SP01.DBF'; Step2、使表空间脱机 SQL> alter tablespace sp01 offline; Step3、移动数据文件到指定的目标位置 SQL> host move 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\SP01.DBF' 'E:\APP\SP01.DBF'; Step4、逻辑修改 alter tablespace sp01 rename datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\SP01.DBF' to 'E:\app\sp01.dbf' ORACLE 笔记 38 / 63 Step5、使表空间联机 SQL> alter tablespace sp01 online; 第十一章 约束 11.1 介绍 为确保数据库数据遵从一定的规则,在 oracle 中,可以使用约束、触发器、应用程序(过 程、函数)三种方式来实现,在这三种方法中,因为约束易于维护,并且具有良好的性能, 所以作为维护数据完整性的首选。 11.2 约束 约束用于确保数据库满足特定的规则。在 oracle 中,约束包括:not null(非空)、 unique(惟一)、primary key(主键)、foreign key(外键)、check(检查) 11.3 使用 11.3.1 not null(非空) 如果在列上定义了 not null,那么在插入数据时,必须为列提供数据。 11.3.2 unique(惟一) 定义了惟一约束后,该列值是不能重复的,但是可以为 null。 11.3.3 primary key(主键) 用于惟一的标示表行的数据,当定义了主键约束后,该列不但不能重复而且不能为 null。 一张表最多只能有一个主键,但是可以有多个 unique 约束。 11.3.4 foreign key(外键) 用于定义主表和从表主键的关系,外键约束要定义在从表上,主表则必须具有主键约束 或是 unique 约束,当定义外键约束后,要求外键约束列数据必须在主表的主键列存在或是 为 null 11.3.5 check 用于强制行数据必须满足的条件。假定在 sal 列上定义了 check 约束,并要求 sal 列值在 1000~2000 之间。 ORACLE 笔记 39 / 63 11.4 实际用例 11.4.1 案例 1 假设设计一个购物系统的表结构,记录客户及其的购物情况,由以下三个表组成: 商品(goods): 字段名 字段类型 约束 字段说明 goodsId char(8) primary key 商品号 goodsName varchar2(30) 无 商品名 unitprice number(10,2) check(unitprice>0) 单价 category varchar2(8) 无 商品类别 provider varchar2(30) 无 供应商 客户(customer): 字段名 字段类型 约束 字段说明 customerId char(8) primary key 客户号 name varchar2(50) not null 姓名 address varchar2(80) 无 住址 email varchar2(50) unique 邮箱 sex char(2) defaule ‘男’ check (sex in( ‘男’,’女’)) 性别 cardId char(18) 无 身份证 购买(purchase): 字段名 字段类型 约束 字段说明 customerId char(8) references customer(customerId) 客户号 goodsId char(8) references goods(goodsId) 商品号 nums number(2) check (nums between 1 and 30) 购买数量 要求如下: 1)定义好每个表的主外键 2)客户的姓名不能为空值 3)单价必须大于 0 4)购买数量必须在 1 到 30 之间 5)邮箱不能重复 6)客户的性别必须是男或者女,默认是男  创建商品表 SQL> create table goods(goodsId char(8) primary key, ORACLE 笔记 40 / 63 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(80), email varchar2(50) unique, sex char(2) default '男' check(sex in('男','女')), cardId char(18));  创建购买表 SQL> create table purchase (customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(2) check (nums between 1 and 30)); 11.4.2 案例 2 如果在建表时忘记建立必要的约束,可以在建表后使用 alter table 命令为表增加约 束。需要注意:增加 not null 约束时,需要使用 modify 选项,其他四种约束使用 add 选项。 要求如下: 1)定义好每个表的主外键 2)客户的姓名不能为空值 3)单价必须大于 0 4)购买数量必须在 1 到 30 之间 5)邮箱不能重复 6)客户的性别必须是男或者女,默认是男 -------------------------------------------------------------- 7)增加商品名也不能为空 8)增加身份证不能重复 9)增加客户的住址只能是“长宁”、“普陀”、“黄埔”、“徐汇”、“闵行”、“浦东”  限制商品名 SQL> alter table goods modify(goodsName not null);  限制身份证 SQL> alter table customer add constraint card_unique unique(cardId);  限制住址 SQL> alter table customer add constraint address_check check(address in ('长宁','普陀','黄埔','徐汇','闵行','浦东')); ORACLE 笔记 41 / 63 11.5 维护 11.5.1 删除约束 当不需要某个约束时,可以删除 SQL> alter table customer drop constraint address_check; 在删除主键约束的时候,可能会有错误,比如: SQL> alter table customer drop primary key; 如果两张表存在主从关系,那么在删除主表的主键约束时,必须带上 cascade 参数 SQL> alter table customer drop primary key cascade; 11.5.2 显示约束信息  显示约束信息 通过查询数据字段视图 user_constraints,可以显示当前用户所有的约束信息 SQL> select * from user_constraints where table_name='CUSTOMER';  显示约束列 通过查询数据字段视图 user_cons_columns,可以显示约束名所对应的表列信息 SQL> select * from user_cons_columns where constraint_name='ADDRESS_CHECK';  通过工具 例如 pl/sql developer 11.6 列级定义表级定义 11.6.1 列级定义 列级定义是指在定义列的同时定义约束。比如在 dept_02 表定义主键约束: SQL> create table dept_02 (deptno number(2) constraint dept02_pk primary key, dname varchar2(14),loc varchar2(13)); 11.6.2 表级定义 表级定义是指在定义了所有列后,在定义约束。需要注意:not null 约束只能在列级 上定义。 以在建立emp_02表时定义主键约束和外键约束为例: SQL> create table emp_02 (empno number(4),name varchar2(10),deptno number(2), constraint emp02_pk primary key (empno), constraint emp02_fk foreign key (deptno) references dept_02(deptno)); ORACLE 笔记 42 / 63 第十二章 索引 12.1 原理介绍 索引是用于加速数据存储的数据对象,合理的使用索引可以大大降低 IO 次数,从而提 高数据库访问性能。 12.2 创建索引 12.2.1 单列索引 单列索引是基于单个列所创建的索引,比如: create index 索引名 on 表名(列名) SQL> create index c_name on customer(name); 12.2.2 复合索引 复合索引是基于两列或多列的索引。在同一张表上可以有多个索引,但是要求列的组合 必须不同,比如: SQL> create index emp_index1 on emp(ename,job); SQL> create index emp_index2 on emp(job,ename); 12.3 使用原则 1)在大表上建索引才有意义 2)在where字句或是连接条件上经常引用的列上建立索引 3)索引的层次不要超过四层 12.4 索引的缺点 索引有一些先天不足: 1)建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。 2)更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维护数据和 索引的一致性。 比如在如下字段建立索引应该是不恰当的: 1)很少或从不作为查询条件的字段 2)逻辑型的字段,如男或女(是或否)等。 ORACLE 笔记 43 / 63 12.5 显示索引信息 12.5.1 显示表的所用索引 通过查询数据字典视图 dba_indexes 和 user_indexes,可以显示索引信息,其中 dba_indexes 用于显示数据库所有的索引信息,而 user_indexes 用于显示当前用户的索 引信息。 SQL> select index_name,index_type from user_indexes where table_name='表名'; 12.5.2 显示索引列 通过查询视图 user_ind_columns,可以显示索引对应的列的信息 SQL> select table_name,column_name from user_ind_columns where index_name=’索引名’; 第十三章 权限和角色 13.1 介绍 Oracle 刚刚创建用户时,用户没有任何权限,也不能执行任何操作。如果要执行某种特 定的数据库操作,则必须为其授予系统的权限;如果用户要访问其他方案的对象,则必须为 其授予对象权限,为了简化权限的管理,可以使用角色。 13.2 权限 权限是指指定特定类型 SQL 命令或是访问其他方案对象的权力,包括系统权限和对象 权限两种。 13.2.1 系统权限  系统权限介绍 系统权限是指执行特定类型SQl命令的权力,它用于控制用户可以执行的一个或是一组 数据库操作。比如当用户具有create table权限时,可以在其方案中建表,当用户具有 create any table 权限时,可以在任何方案中建表。Oracle 11g提供了208种系统权 限。 常用的有: 权限 说明 create session 连接数据库 create table 建表 create view 建视图 create procedure 建过程、函数、包 ORACLE 笔记 44 / 63 create cluster 建簇 create public synonym 建同义词 create trigger 建触发器  显示系统权限 SQL> select * from system_privilege_map order by name;  授予系统权限 一般情况下,授予系统权限是由dba完成的,如果用其他用户来授予系统权限,则要求 该用户必须具有grant any privilege权限。在授予系统权限时,可以带有with admin option 选项,这样,被授予权限的用户或是角色还可以继续传递权限。 举例说明: 1)创建两个用户ken,tom.初始阶段他们没有任何权限,如果登录,是会报错的。 SQL> create user ken identified by ken; SQL> create user tom identified by tom; 2)给用户ken授权 SQL> grant create session,create table to ken with admin option; SQL> grant create view to ken; 3)给用户tom授权 SQL> conn ken/ken@orcl; SQL> grant create session,create table to tom with admin option; SQL> grant create view to tom;(权限不足)  回收系统权限 一般情况下,回收系统权限是由dba完成的,使用revoke命令来完成。当回收了系统 权限后,用户就不能执行相应的操作了,需要特别注意系统权限联级收回问题。 举例说明: 用sys执行如下操作后,tom是不是还能登录? SQL> revoke create session from ken; ken可以正常登录,证明系统权限不是采用级联回收的 13.2.2 对象权限  对象权限介绍 对象权限是指访问其他方案的权利,用户可以直接访问自己方案的对象,但是如果要访 问别的方案的对象,则必须具有对象的权限。例如smith用户要访问scott.emp表,则必 须在scott.emp表上具有对应的权限 常用的有: 权限 说明 alter 修改 delete 删除 select 查询 insert 添加 update 修改 ORACLE 笔记 45 / 63 index 索引 references 引用 execute 执行  显示对象权限 SQL> select distinct privilege from dba_tab_privs;  授予对象权限 用grant命令来完成。对象权限可以授予用户,角色和public。在授予权限时,如果 带有with grant option选项,则权限可以继续传递。with grant option选项不能 被授予角色 1)普通授权 SQL> grant select on emp to ken; 2)授予列控制 希望ken只可以修改scott.emp的sal字段 SQL> grant updaet on emp(sal) to ken; 希望ken只可以查询scott.emp的ename,sal字段 SQL> grant select on emp(ename,sal) to ken; 3)授予alter权限. 如果ken用户要修改scott.emp表的接口,则必须授予alter权限 SQL> grant alter on emp to ken; 4)授予execute权限. 如果用户想要执行其他方案的包/过程/函数,需要有execute权限。比如为了让ken用 户可以执行包dbms_transaction,可以授execute权限。 SQL> grant execute on dbms_transaction to ken; 5)授予index权限. 如果用户想要在别的方案的表上建立索引,则必须具有index对象权限。 SQL> grant index on scott.emp to ken; 6)使用with grant option选项 该选项只能被授予给用户,不能授予角色 SQL> grant select on scott.emp to ken with grant option;  回收对象权限 SQL> revoke select on emp from ken; 对象权限,采用联级回收机制。 ORACLE 笔记 46 / 63 13.3 角色 13.3.1 介绍 角色就是相关权限的命令集合。使用角色的主要目的就是为了简化权限的管理。角色分 为预定义角色和自定义角色。 13.3.2 预定义角色 是指 Oracle 提供的角色,每种角色都用于执行一些特定的管理任务。  connect connect 角色具有 create session 权限,通过下边命令可查看 SQL> select * from dba_sys_privs where grantee='CONNECT';  resource 当建立一个用户后,多数情况需要给用户 connect 和 resource 角色,通过下面命令 可查看 resource 角色具有哪些权限: SQL> select * from dba_sys_privs where grantee='RESOURCE';  dba dba 角色不具备 sysdba 和 sysoper 的特权(启动和关闭数据库),通过下边命令可 查看 dba 角色具有哪些权限: SQL> select * from dba_sys_privs where grantee='DBA'; 13.3.3 自定义角色 自定义角色就是自己定义的角色,根据自己的需要来定义。一般是 dba 来建立,如果用 别的用户来建立,需要具有 create role 权限,在建立角色时可以指定验证方式(不验 证、数据库验证等)  建立角色(不验证) 如果角色是公用的角色,可以采用不验证的方式建立角色。 SQL> create role 角色名 not identified; SQL> create role role_1 not identified;  建立角色(验证) 采用这样的方式时,角色名、口令存放在数据库中。当激活该角色时,必须提供口令。 在建立这种角色时,需要为其提供口令。 SQL> create role 角色名 identified by 口令; 13.3.4 角色授权 当建立角色时,角色没有任何权限,为了使得角色完成特定任务,必须为其授予相应的 系统权限和对象权限。  给角色授权 ORACLE 笔记 47 / 63 给角色授予权限和给用户授权没有太多区别,但是需要注意:系统权限的 umlimited tablespace 和对象权限的 with grant option 选项是不能授予角色的。 SQL> conn sys/m123@orcl as sysdba; SQL> grant create session to role_1 with admin option; SQL> conn scott/m123@orcl; SQL> grant select on scott.emp to role_1; SQL> grant insert,update,delete on scott.emp to role_1;  分配角色 一般分配角色是由 dba 来完成的,如果其他用户需要分配角色,要求该用户必须具有 grant any role 的系统权限。 SQL> conn sys/m123@orcl as sysdba; SQL> grant role_1 to ken;  删除角色 删除分配角色是由 dba 来完成的,如果其他用户需要删除角色,要求该用户必须具有 drop any role 的系统权限。角色删除后,对应用户的权限会消失。 SQL> drop role role_1; 13.3.5 显示角色信息 见 10.3.2 第十四章 pl/sql 基础 14.1 介绍 开发人员使用 pl/sql 编写应用模块时,不仅需要掌握 sql 语句的编写方法,还需要 掌握 pl/sql 语句及语法规则。pl/sql 编程可以使用变量和逻辑控制语句,从而可以编写 非常有用的功能模块。使用 pl/sql 可以轻松的完成非常复杂的查询要求。 14.1.1 pl/sql 是什么 pl/sql(procedural language/sql)是 oracle 在标准 sql 语言上的扩展, pl/sql 不仅允许嵌入 sql 语言,还可以定义变量和常量,允许使用条件语句和循环语句, 允许使用例外处理各种错误。功能非常强大,是 oracle 的精华部分。 14.1.2 优点 1)提高应用程序的运行性能 2)模块化的设计思想 3)减少网络传输量 4)提高安全性 14.1.3 缺点 ORACLE 笔记 48 / 63 1)几乎不能移植 14.1.4 开发工具 1)sqlplus(w) 编写一个存储过程,可以向某表添加记录 Step1、创建一个简单的表 SQL> create table mytest(name varchar2(30),passwd varchar2(30)); Step2、创建过程 SQL> create procedure t_pro1 is begin --执行部分 insert into mytest values('zhangsan','11'); end; / Step3、调用过程 SQL> exec t_pro1 或 SQL> call t_pro1 2)pl/sql developer 编写一个存储过程,可以删除某表记录 Step1、创建过程 SQL> create or replace procedure t_pro2 is begin delete from mytest where name='zhangsan'; end; / Step2、调用过程 SQL> exec t_pro2; 14.2 简单分类 过程 函数 触发器 包 块(编程) ORACLE 笔记 49 / 63 14.3 编写规范 14.3.1 注释 单行注释 – 多行注释 /*……*/ 14.3.2 命名规范 1)定义变量时,建议用 v_作为前缀 2)定义常量时,建议用 c_作为前缀 3)定义例外时,建议用 e_作为前缀 4)定义游标时,建议用_cursor 作为后缀 14.4 pl/sql 块 块是 pl/sql 的基本程序但愿,编写 pl/sql 程序实际上就是编写 pl/sql 块。盐完 成相对简单的应用功能,可能只需要编写一个 pl/sql 块;但是如果想要实现复杂的功能, 可能需要在一个 pl/sql 中嵌套其他的 pl/sql 块。 14.4.1 块结构示意图 pl/sql 块由三个部分构成:定义部分、执行部分、例外处理部分。如下所示: declear /*定义部分—定义常量、变量、游标、例外、复杂数据类型*/ begin /*执行部分—要执行的 pl/sql 语句和 sql 语句*/ exception /*异常处理部分—处理运行期间的各种错误*/ end; 说明如下: 定义部分从 declear 开始,该部分为可选内容;执行部分是必须的;异常处理部分为 可选;结束部分必选。 14.4.2 只包含执行部分的 pl/sql 块 1)打开输出选项: SQL> set serveroutput on; 2)执行过程: 相关说明: begin dbms_output.put_line('Hello'); end; / ORACLE 笔记 50 / 63 dbms_output 是 oracle 提供的包(类似 Java 的开发包),包里边包含一些过程, put_line 就是 dbms_output 包的一个过程。 14.4.3 包含定义和执行部分的 pl/sql 块 执行过程: 相关说明: 1)&表示要接收从控制台输入的变量值 2)将查询结果赋给变量 v_ename,并输出变量值 14.4.4 包含定义和执行以及异常处理部分的 pl/sql 块 为了避免 pl/sql 程序的运行错误,提高 pl/sql 的健壮性,应该对可能的错误进行 捕获,类似于 Java 中的异常处理。 比如输入的是不存在的雇员号,应该做异常处理: 相关说明: Oracle 事先预定义了一些异常,not_data_found 就是找不到数据的异常 14.5 pl/sql 分类 14.5.1 过程 过程用于执行特定的操作。当建立过程时,既可以指定输入参数(in),也可以指定输 出参数(out)。使用输入参数,可以将数据传递到执行部分;使用输出参数,可以将执行部 分的数据传递到应用环境。使用 create procedure 命令来创建过程。 declare v_ename varchar2(5); --定义字符串变量 v_sal number(7,2); --定义字符串变量 begin select ename,sal into v_ename,v_sal from emp where empno=&empno; dbms_output.put_line('员工名:' || v_ename || ' 工资:' || v_sal); exception --异常处理 when no_data_found then dbms_output.put_line('编号有误'); end; / declare v_ename varchar2(5); --定义字符串变量 v_sal number(7,2); --定义字符串变量 begin select ename,sal into v_ename,v_sal from emp where empno=&empno; dbms_output.put_line('员工名:' || v_ename || ' 工资:' || v_sal); end; / ORACLE 笔记 51 / 63 要求: 1)编写过程,可以输入员工姓名、新工资,以达到修改员工薪资目的 2)在 Java 中调用存储过程 Step1、创建过程 Step2、调用过程 SQL> exec t_pro3('wangwu',5200); Step3、Java 调用 14.5.2 函数 函数用于返回特定的数据,当建立函数时,在函数头部必须包含 return 子句,而在 函数体内必须包含 return 语句返回的数据。用 create function 来创建函数 举例: Step1、创建函数 Step2、在 sqlplus 中调用函数 SQL> var sal number; SQL> call t_fun1('zhaoliu') into:sal; SQL> print sal 也可以 SQL> select t_fun1('zhaoliu') from dual; 在 Java 中调用: SQL> select t_fun1('zhaoliu') from dual; 可以通过 rs.getDouble(1)得到返回的结果 14.5.3 包 包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。  创建包 --输入员工姓名,返回年薪 create or replace function t_fun1(p_name varchar2) return number is yearSal number(7,2); begin select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=p_name; return yearSal; end; / --定义过程名及参数 create procedure t_pro3 (p_name varchar2, p_sal number) is begin update emp set sal=p_sal where ename=p_name; end; / ORACLE 笔记 52 / 63 使用 create package 来创建包: 包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码。包体用于 实现包规范中的过程和函数。  创建包体 使用 create package body 来创建包体:  调用包 SQL> exec t_pac1.t_pro3('wangwu',3000); SQL> select t_pac1.t_fun1('wangwu') from dual; 14.5.4 触发器 触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的 操作,常用的触发器事件包括 insert,update,delete 语句,触发器实际上就是一个 pl/sql 块。使用 create trigger 来建立触发器。 触发器非常有用,可维护数据库的安全和一致性,后面做详细介绍。 --给包实现包体 create or replace package body t_pac1 is --实现过程 procedure t_pro3 (p_name varchar2, p_sal number) is begin update emp set sal=p_sal where ename=p_name; end; --实现函数 function t_fun1(p_name varchar2) return number is yearSal number(7,2); begin select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=p_name; return yearSal; end; --结束 end; / create package t_pac1 is procedure t_pro3 (p_name varchar2, p_sal number); function t_fun1(p_name varchar2) return number; end; / ORACLE 笔记 53 / 63 14.6 定义并使用变量 在编写 pl/sql 程序时,可以定义变量和常量;包括有四个大类: 1)标量类型(scalar) 2)复合类型(composite) 3)参照类型(reference) 4)lob(large-object) 14.6.1 标量(scalar)  介绍 在编写pl/sql块时,如果要使用变量,需在定义部分先定义。 定义变量和常量的语法如下: identified [constant] datatype [not null] [:=|defalut expr] 1)字符串 定义一个字符串 v_ename varchar2(10); 2)小数 定义一个小数,范围-9999.99~9999.99 v_sal number(6,2); 3)赋初始值 定义一个小数并给一个初始值为 5.4 := 是 pl/sql 的赋值号 v_comm number(6,2) := 5.4; 4)日期类型 定义一个日期类型的数据 v_hiredate date; 5)布尔类型 定义一个布尔类型的变量,不能为空,初始值为 false; v_valid boolean not null default false; identified: 名称 constant: 指定常量.需要指定它的初始值,且其值是不能改变的 datatype:数据类型 not null:指定变量值不能为 null := 给变量或是常量指定初始值 defalut: 用于指定初始值 expr: 指定初始值的 pl/sql 表达式,可是文本值、变量、函数等 ORACLE 笔记 54 / 63  使用 以输入员工号,显示员工姓名、工资、个人所得税(税率设固定值为0.03)为例。说明 变量的使用:  %type类型 对于上面的pl/sql块有一个问题: 如果员工的姓名超过了5个字符,就会有错误,为了降低pl/sql程序的维护工作量,可以 使用%type属性定义变量,这样它会按照数据库列来确定定义的变量的类型和长度。 格式: 标识符名 表名.列名%type; 14.6.2 复合变量(composite) 用于存放多个值的变量。主要包括: 1)pl/sql记录 2)pl/sql表 3)嵌套表 4)varray  pl/sql记录 相当于Java中的类。当引用pl/sql记录成员时,必须要加记录变量作为前缀,具体如 下: 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=&员 工号; v_tax_sal := v_sal*c_tax_rate; --计算所得税 dbms_output.put_line('姓名:' || v_ename || ' 工资:' || v_sal || ' 所得税:' || v_tax_sal); end; / declare type emp_record_type is record( ename emp.ename%type, salary emp.sal%type, job emp.job%type); my_record emp_record_type; begin select ename,sal,job into my_record from emp where empno=7788; dbms_output.put_line('员工:' || my_record.ename); end; / ORACLE 笔记 55 / 63  pl/sql表 相当于Java中的数组。下标可以为负数,并且没有限制。实例如下: 14.6.3 参照变量(reference) 参照变量是指用于存放数值指针的变量。通过使用参照变量,可以使得应用程序共享相 同对象,从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor) 和对象类型变量(ref obj_type)两种参照变量类型。  游标变量 在定义游标时,不需要指定相应的select语句,但是在使用时(open时)需要指定 select语句。实例如下: 编写一个块.输入部门号,显示该部门所有员工姓名和工资 declare --创建一个数组,用于存放ename,下标是整数 type t_table_type is table of emp.ename%type index by binary_integer; t_table t_table_type; begin select ename into t_table(0) from emp where empno=7788; dbms_output.put_line('员工名:' || t_table(0)); end; / declare type t_emp_cursor is ref cursor; --定义游标类型 t_cursor t_emp_cursor; --定义游标变量 v_ename emp.ename%type; --定义变量 v_sal emp.sal%type; begin --把游标t_cursor指向一个结果集(和select结合) open t_cursor for select ename,sal from emp where deptno=&部门号; --循环取出 loop fetch t_cursor into v_ename,v_sal; --判断t_cursor为空,并做退出 exit when t_cursor%notfound; dbms_output.put_line('名字:' || v_ename || ' 工资:' || v_sal); end loop; --关闭游标 close t_cursor; end; / ORACLE 笔记 56 / 63 第十五章 pl/sql 进阶 15.1 控制结构 和其他编程语言一样,pl/sql同样具有控制结构功能。包括:条件语句、循环结构、 顺序控制结构等 15.1.1 条件分支语句 pl/sql 提供了三种条件分支语句,分别是: if -- then if – then -- else if – then –- elsif -- then  if-then 简单的条件判断.编写一个过程,可以输入员工名,如果员工的工资低于 2000,就给 他增加 10%  if-then-else 二重条件分支.编写一个过程,可以输入员工名,如果员工的补助不是 0,就在原来的 基础上加 100;如果补助为 0,就把补助设为 200 create or replace procedure t_pro4(p_name varchar2) is --定义变量 v_sal emp.sal%type; begin select sal into v_sal from emp where ename=p_name; --判断语句 if v_sal<2000 then update emp set sal=sal*1.1 where ename=p_name; end if; end; / create procedure t_pro5(p_name varchar2) is --定义变量 v_comm emp.comm%type; begin select comm into v_comm from emp where ename=p_name; --判断语句 if v_comm<>0 then update emp set comm=comm+100 where ename=p_name; else ORACLE 笔记 57 / 63  if-then-elsif-else 多重条件分支.编写一个过程,可以输入员工编号,如果员工的职位是 PRESIDENT,就 给他的工资加 1000,如果员工的职位是 MANAGER,就给他的工资加 500,其他职位的员 工工资增加 200. 15.1.2 循环结构  loop 是 pl/sql 中最简单的循环语句,以 loop 开头,以 end loop 结尾,这种循环至少 会被执行一次,有点类似 Java 中的 do-while 有一张users表,表结构如下: 字段名 字段类型 字段说明 userId number(8) 用户 Id userName varchar2(30) 用户名 编写一个过程,并循环添加10个用户到users表中,userId从1开始递增: update emp set comm=comm+200 where ename=p_name; end if; end; / create procedure t_pro6(p_empno number) is --定义变量 v_job emp.job%type; begin select job into v_job from emp where empno=p_empno; --判断语句 if v_job='PRESIDENT' then update emp set sal=sal+1000 where empno=p_empno; elsif v_job='MANAGER' then update emp set sal=sal+500 where empno=p_empno; else update emp set sal=sal+200 where empno=p_empno; end if; end; / ORACLE 笔记 58 / 63  while while循环只在条件为true时,才会执行。以while..loop开始,以end loop结束 编写一个过程,并循环添加10个用户到users表中,userId从11开始递增:  for for循环的基本结构如下 控制变量num,在隐含中就在不停的增加 15.1.3 顺序控制 create or replace procedure t_pro7 is v_num number := 1; begin --循环 loop insert into users values(v_num, 'user-'||v_num); --判断是否要退出 exit when v_num=10; --自增长 v_num := v_num+1; end loop; end; / create or replace procedure t_pro8 is v_num number := 11; begin while v_num<=20 loop insert into users values(v_num, 'user-'||v_num); v_num := v_num+1; end loop; end; / --for循环 create or replace procedure t_pro9 is begin for num in reverse 21..30 loop insert into users values(num, 'user-'||num); end loop; end; / ORACLE 笔记 59 / 63  goto goto语句用于跳转到特定符号去执行语句.基本语法如下: goto label,其中label是已经定义好的标号名  null null语句不会执行任何操作,并会直接将控制传递到下一条语句。使用null语句的主 要好处是可以提高程序的可读性 15.2 编写分页过程 15.2.1 无返回值的过程 从简单到复杂,来说明分页过程的编写。 案例: 现有一张book表,表结构如下: 字段名 字段类型 字段说明 declare i number := 1; begin loop dbms_output.put_line('输出 i=' || i); if i=10 then goto end_loop; end if; i:=i+1; end loop; <> dbms_output.put_line('循环结束'); end; / declare v_sal emp.sal%type; v_ename emp.ename%type; begin select ename,sal into v_ename,v_sal from emp where empno=&empno; if v_sal<3000 then update emp set comm=sal*0.1 where ename=v_ename; else null; end if; end; / ORACLE 笔记 60 / 63 bookId number(10) 书号 bookName varchar2(30) 书名 publish varchar2(30) 出版社 要求编写一个过程,可以向表中添加数据,通过Java调用 15.2.2 有返回值的过程(非列表) 编写一个过程,输入员工的编号,返回姓名 15.2.3 有返回值的过程(列表) 编写一个过程,输入部门号,返回该部门下所有员工的信息。 Oracle所有返回值都是通过out参数来替代的,列表同样如此;但是由于是返回的是列表, 就不能用一般的参数了,必须要用到package.  建包  建过程 create or replace procedure t_page (p_bookId in number, p_bookName in varchar2, p_publish in varchar2) is begin insert into book values(p_bookId,p_bookName,p_publish); end; / create or replace procedure t_page (p_empno in varchar2,p_ename out varchar2) is begin select ename into p_ename from emp where empno=p_empno; end; / create or replace package t_pac_page as --定义一个名为test_cursor的游标 type test_cursor is ref cursor; end t_pac_page; / create or replace procedure t_page (p_deptno in number,p_cursor out t_pac_page.test_cursor) is begin open p_cursor for select * from emp where deptno=p_deptno; ORACLE 笔记 61 / 63  Java调用 15.2.4 有返回值的过程(列表) 编写一个过程,输入表名、每页显示记录数,当前页。返回总记录数,总页数,和返回 的结果集。  Java调用 end t_page; / create or replace procedure t_page ( --定义三个输入变量:表名,每页显示记录数,当前页 p_tname in varchar2,p_size in number,p_no in number, --定义三个输出变量:总记录数,总页数,结果集 p_rows out number,p_page out number,p_cusor out t_pac_page.test_cursor ) is --定义部分 v_sql varchar2(1000); --定义SQL语句,字符串类型 v_begin number:= (p_no-1)*p_size+1; --从哪里开始取 v_end number:= p_no*p_size; --取到哪个位置 begin --执行部分 v_sql:='select * from (select t1.*, rownum rn from (select * from '|| p_tname ||') t1 where rownum <='|| v_end ||') where rn >='|| v_begin; --把游标和SQL语句关联 open p_cusor for v_sql; --计算总记录数(p_rows),总页数(p_page) v_sql:='select count(*) from '|| p_tname; --组织一个SQL语句 --执行SQL语句,并把返回值赋给p_rows execute immediate v_sql into p_rows; --计算p_page if mod(p_rows,p_size)=0 then p_page:=p_rows/p_size; else p_page:=p_rows/p_size+1; end if; end; / ORACLE 笔记 62 / 63 15.3 视图 15.3.1 介绍 视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的 列和行数据。但是,视图并不在数据库中以存储的数据集形式存在。行和列数据来自由定义 private static void 分页过程() throws Exception { Connection conn = null; CallableStatement stmt = null; Class.forName(className); conn = DriverManager.getConnection(url, user, password); String sql = "{call t_page(?,?,?,?,?,?)}"; stmt = conn.prepareCall(sql); stmt.setString(1, "emp"); // 表名 stmt.setInt(2, 5); // 每页显示条数 stmt.setInt(3, 1); // 页码 stmt.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGE R); // 总记录数 stmt.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGE R); // 总页数 stmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); // 结果集 stmt.execute(); // 取出数据 int p_rows = stmt.getInt(4); // 总记录数 int p_page = stmt.getInt(5); // 总页数 ResultSet rs = (ResultSet)stmt.getObject(6); // 结果集 System.out.println("表名:emp"); System.out.println("每页显示条数:5"); System.out.println("页码:1"); System.out.println("总记录数:" + p_rows); System.out.println("总页数:" + p_page); System.out.println("数据列表:"); while(rs.next()) { System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3)); } } ORACLE 笔记 63 / 63 视图的查询所引用的表,并且在引用视图时动态生成。 15.3.2 视图与表的区别 1)表需要占用磁盘空间,视图不需要 2)视图不能添加索引 3)使用视图可以简化复杂查询 4)视图有利于提高安全性 5)查询速度比表慢

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 5 人已下载

下载文档

相关文档