java基础知识总结8(数据库篇1)
2017-08-07 09:39 tlnshuju 阅读(412) 评论(0) 编辑 收藏 举报一、 Oracle的安装(windowXP、win7、Linux)和卸载
1.1 Oracle的安装
1.1.1 在WindowsXP、Win7下安装
第一：解压win32_11gR2_database_1of2、win32_11gR2_database_2of2，生成detabase文件夹
第二：安装oracle
A、点击setup图标就可以，注意：安装文件夹不要含有中文
B、在弹出的第一个界面中取消更新选择项。点击下一步
C、在弹出的警告框中选择是
D、选择创建和配置数据库选项，下一步
E、选择桌面类安装，点击下一步
F、弹出的窗体中输入全局数据库名：orcl
输入管理口令：bluedot
默认的管理员是：sys和system
G、点完毕。開始安装数据库，出现进度条
H、口令管理
I、设置口令
J、完毕安装
1.2 Oracle的卸载：
1、 開始－＞设置－＞控制面板－＞管理工具－＞服务 停止全部Oracle服务。
2、 開始－＞程序－＞Oracle - OraHome81－＞Oracle Installation Products－＞ Universal Installer，单击“卸载产品”-“全部展开”，选中除“OraDb11g_home1”外的全部文件夹，删除。
3、 运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。
4、 运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动这个列表。删除全部Oracle入口(以oracle或OraWeb开头的键)。
5、 运行refedit。HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，删除全部Oracle入口。
e
6、 删除HKEY_CLASSES_ROOT文件夹下全部以Ora、Oracle、Orcl或EnumOra为前缀的键。
7、 删除HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs中全部以oracle开头的键。
8、删除HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中除Microsoft ODBC for Oracle注冊表键以外的全部含有Oracle的键。
9、我的电脑–>属性–>高级–>环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定。
10、从桌面上、STARTUP（启动）组、程序菜单中，删除全部有关Oracle的组和图标。
11、删除全部与Oracle相关的文件夹(假设删不掉，重新启动计算机后再删就能够了)包括：
1.C:\Program file\Oracle文件夹。
2.ORACLE_BASE文件夹(oracle的安装文件夹)。
3.C:\WINDOWS\system32\config\systemprofile\Oracle文件夹。
4.C:\Users\Administrator\Oracle或C:\Documents and Settings\Administrator\Oracle文件夹。
5.C:\WINDOWS下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。
6.C:\WINDOWS下的WIN.INI文件里若有[ORACLE]的标记段，删除该段。
12、如有必要。删除全部Oracle相关的ODBC的DSN
13、到事件查看器中，删除Oracle相关的日志 说明： 假设有个别DLL文件无法删除的情况。则不用理会，又一次启动，開始新的安装。安装时，选择一个新的文件夹，则，安装完毕并又一次启动后，老的文件夹及文件就能够删除掉了。
二、 用户管理
2.1 创建用户
注：创建用户仅仅能在管理员下完毕
CREATE USER username IDENTIFIED BY password。
|-CREATE USER demo IDENTIFIED BY 123456；
2.2 用户分类
|-管理员和普通用户
|-管理员
|-超级管理员：sys/bluedot
|-管理员：system/bluedot
|-普通用户：scott/tiger
hr/hr
|–常见角色：sysdba、sysoper
2.3 用户登录
2.3.1 在命令行窗体登录[c/s]
步骤：
运行 sqlplus /nolog
conn demo/123456
2.3.2 另外的一种登录方式【B/S】
输入网址—-https://localhost:1158/em
输入usernamepassword进入主界面
2.4 改动用户password
注：改动password必须要在级别高的用户下进行改动
ALTER USER username IDENTIFIED BY password；
conn sys/bluedot as sysdba
ALTER USER demo IDENTIFIED BY 654321；
2.5 查询用户
2.5.1查看用户信息
1、SELECT * FROM DBA_USERS;——–查看全部用户的详细信息
2、SELECT * FROM ALL_USERS;——-查看全部用户简要信息
3、SELECT * FROM USER_USERS;————查看当前用户的所用信息
2.5.2 查看用户或角色系统权限（直接赋值给用户或角色的系统权限）
SELECT * FROM DBA_SYS_PRIVS;———-全部
SELECT * FROM USER_SYS_PRIVS; ———当前用户
2.5.3 查看角色（登录用户拥有的角色）所包括的的权限
SELECT * FROM ROLE_SYS_PRIVS;
2.5.4 查看用户对象权限
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
2.5.5 查看全部角色
SELECT * FROM DBA_ROLES;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
2.5.6 查看哪些用户有sysdba或sysoper系统权限(查询时须要相应权限)
SELECT * FROM V$PWFILE_USERS;
2.5.7 查看Oracle提供的系统权限
SELECT name FROM SYS.SYSTEM_PRIVSILEGE_MAP;
2.6 password失效
提示用户第一次连接的时候须要改动password。让用户的password到期
|- ALTER USER username PASSWORD expire ;
2.7 授权
GRANT 权限/角色 TO 用户
给 demo 用户以创建 session 的权限：GRANT create session TO demo;
角色：————-角色就是一堆权限的集合
Create role myrole;
Grant create table to myrole;
Drop role myrole; 删除角色
１．CONNECT, RESOURCE, DBA
这些提前定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划。而不要简单的使用这些预定角色。将来的版本号中这些角色可能不会作为提前定义角色。
２．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE。 SELECT_CATALOG_ROLE 这些角色主要用于訪问数据字典视图和包。
３．EXP_FULL_DATABASE， IMP_FULL_DATABASE 这两个角色用于数据导入导出工具的使用。
GRANT 权限（select、update、insert、delete） ON schema.table TO 用户
|- GRANT select ON scott.emp TO test ;
|- Grant all on scott.emp to test; –将表相关的全部权限付给 test
|- Grant update(ename) on emp to test; 能够控制到列(还有 insert)
2.8 收权
REVOKE 权限/角色 ON schema.table FROM 用户
|- REVOKE select ON scott.emp FROM test ;
2.9 锁住一个用户
ALTER USER username ACCOUNT LOCK|UNLOCK
|- ALTER USER test ACCOUNT LOCK ;
|- ALTER USER test ACCOUNT UNLOCK ;
2.10 删除用户
|-DROP USER username；
|-Drop user demo;
假设该用户以下已经存在表等一些数据库对象。则必须用级联删除
|-DROP USER username CASCADE;
|-Drop user demo cascade;
备注：帮助
help index
help conn ——–显示详细的
eidt—————进入编辑文档
三、 Oracle的体系结构
3.1 Oracle数据库的总体架构 （DBA）
┌──────────────────────────────┐
┌────┐ │ Instance │
│ User │ │ ┌──────────────────────────┐ │
│ process│ │ │ ┌────────┐ SGA │ │
└────┘ │ │ │ Shared Pool │ │ │
↓ │ │ │ ┌─────┐ │ ┌────┐ ┌────┐ │ │
↓ │ │ │ │Library │ │ │Database│ │ Redo │ │ │
┌────┐ │ │ │ │ Cache │ │ │Buffer │ │ Log │ │ │
│ Server │ │ │ │ └─────┘ │ │Cache │ │ Buffer │ │ │
│process │ │ │ │ ┌─────┐ │ │ │ │ │ │ │
└────┘ │ │ │ │Data Dict │ │ └────┘ └────┘ │ │
↓ │ │ │ │ Cache │ │ │ │
→→→→→→│ │ │ └─────┘ │ │ │
│ │ └────────┘ │ │
│ └──────────────────────────┘ │
│ ┌──┐ ┌──┐ ┌──┐ ┌──┐ ┌──┐ ┌───┐│
│ │PMON│ │SNON│ │DBWR│ │LGWR│ │CHPT│ │OTHERS││
│ └──┘ └──┘ └──┘ └──┘ └──┘ └───┘│
└──────────────────────────────┘
↑ ↓
┌─────┐ ┌───────────────────┐
│Parameter │ │┌───┐ ┌────┐ ┌────┐│
│ file │ ││Data │ │control │ │Redo Log││ ┌─────┐
└─────┘ ││files │ │ files │ │ files ││ │Archived │
┌─────┐ │└───┘ └────┘ └────┘│ │Log files │
│ Password │ │ Database │ └─────┘
│ file │ │ │
└─────┘ └───────────────────┘
由上图可知，Oracle数据库由实例和数据库组成。
3.2 数据库存储结构
3.2.1 数据库存储结构
Oracle数据库有物理结构和逻辑结构。数据库的物理结构是数据库中的操作系统文件的集合。
数据库的物理结构由数据文件、控制文件和重做日志文件组成。
1、数据文件：数据文件是数据的存储仓库。
2、重做日志文件：重做日志文件包括对数据库所做的更改记录，在发生问题时能够恢复数据。
重做日志按时间顺序存储应用于数据库的一连串的变更向量。当中仅包括重建（重做）全部已完毕工作的最少限度信息。假设数据文件受损，则能够将这些变更向量应用于数据文件备份来重做工作，将它恢复到发生问题的那一刻前的状态。重做日志文件包括联机重做日志文件（对于连续的数据库操作时必须的）和归档日志文件（对于数据库操作是可选的，但对于时间点恢复是必须的）。
3、控制文件：控制文件包括维护和验证数据库完整性的必要的信息。
控制文件虽小，但作用非常大。
它包括指向数据库其余部分的指针：联机重做日志文件和数据文件的位置。以及更新的归档日志文件的位置。它还存储着维护数据库完整性所需的信息。控制文件只是数MB，却起着至关关键的数据。
除了三个必须的文件外数据库还能有其它非必须的文件如：參数文件、口令文件及归档日志文件。
1、实例參数文件：当启动oracle实例时，SGA结构会依据此參数文件的设置内置到内存，后台进程会据此启动。
2、口令文件：用户通过提交username和口令来建立会话。
Oracle依据存储在数据字典的用户定义对username和口令进行验证。
3、归档重做日志文件：当重做日志文件满时将重做日志文件进行归档以便还原数据文件备份。
3.2.2 Oracle数据库结构的16个要点（表空间–>段–>区–>块）
1、一个数据文件仅仅能归到某一个表空间上，每一个表空间能够含一个或多个数据文件。包括系统数据和用户数据。
2、表空间是包括一个或多个数据文件的逻辑结构。用于存放数据库表、索引、回滚段等对象的磁盘逻辑空间
3、数据库文件是存放实际数据的物理文件。包括实例和数据库。
4、数据文件能够在创建表空间时创建，也能够以添加的方式创建。
5、数据文件的大小一般与操作系统限制有关。
6、控制文件是Oracle的关键文件，主要存放数据文件、日志文件和数据库的基本信息，一般在数据打开时訪问。
7、日志文件在数据库活动时使用。
8、暂时表空间是用于存放排序段的磁间；暂时表空间由一个或多个暂时文件组成。
9、归档日志文件由归档进程将联机日志文件读出并写到一个路径上的文件。
10、Oracle实例由一组后台进程和内存结构组成。
11、Oracle实例的内存结构常叫系统全局区。简称SGA。
12、DBA_开头的数据字典存放的字符信息都是大写，而V
16、SGA分为数据缓冲区、共享池和日志缓冲区。
3.2.3 Oracle逻辑结构及表空间
1.ORACLE逻辑结构
ORACLE将数据逻辑地存放在表空间。物理地存放在数据文件里。
一个表空间不论什么一个时刻仅仅能属于一个数据库。
数据库——表空间——段——区——ORACLE块 每一个数据库由一个或多个表空间组成。至少一个。 每一个表空间基于一个或多个操作系统的数据文件，至少一个。一个操作系统的数据文件仅仅能属于一个表空间。一个表空间能够存放一个或多个段 segment。每一个段由一个或多个区段extent组成。
每一个区段由一个或多个连续的ORACLE数据库块组成。 每一个ORACLE数据块由一个或多个连续的操作系统数据块组成。 每一个操作系统数据文件由一个或多个区段组成，由一个或多个操作系统数据块组成。
⑴、表空间（tablespace） 表空间是数据库中最大的逻辑单位，每一个表空间由一个或多个数据文件组成。一个数据文件仅仅能与一个表空间相联系。
每一个数据库都有一个SYSTEM表空间，该表空间是在数据库创建或数据库安装时自己主动创建的，用于存储系统的数据字典表，程序系统单元，过程函数，包和触发器等，也可用于存储用户数据表，索引对象。表空间具有在线（online）和离线（offline）属性，能够将除SYSTME以外的其它不论什么表空间置为离线。
⑵、段（segment） 数据库的段能够分为四类：数据段、索引段、回退段和暂时段。 ⑶、区 区是磁盘空间分配的最小单位。磁盘按区划分，每次至少分配一个区。区存储与段中。它由连续的数据块组成。
⑷、数据块 数据块是数据库中最小的数据组织单位与管理单位，是数据文件磁盘存储空间单位。也是数据库I/O的最小单位，数据块大小由DB_BLOCK_SIZE參数决定，不同的Oracle版本号DB_BLOCK_SIZE的默认值是不同的。 ⑸、模式对象 模式对象是一种应用，包括：表、聚簇、视图、索引序列生成器、同义词、哈希、程序单元、数据库链等。 最后。在来说一下Oracle的用户、表空间和数据文件的关系： 一个用户能够使用一个或多个表空间，一个表空间也能够供多个用户使用。用户和表空间没有隶属关系，表空间是一个用来管理数据存储的逻辑概念。表空间仅仅是和数据文件发生关系，数据文件是物理的，一个表空间能够包括多个数据文件。而一个数据文件仅仅能隶属一个表空间。 总结：解释数据库、表空间、数据文件、表、数据的最好办法就是想象一个装满东西的柜子。数据库事实上就是柜子，柜中的抽屉是表空间。抽屉中的文件夹是数据文件，文件夹中的纸是表，写在纸上的信息就是数据。 2.两类表空间： 系统SYSTEM表空间 非系统表空间 NON-SYSTEM表空间 系统SYSTEM表空间与数据库一起建立，在系统表空间中有数据字典，系统还原段。
能够存放用户数据可是不建议。
非系统表空间NON-SYSTEM表空间 由管理员创建。能够方便管理。
3.3 实例的总体架构
实例总体架构图：
┌──────────────────────────────┐
│ Instance │
│ ┌──────────────────────────┐ │
│ │ ┌────────┐ SGA │ │
│ │ │ Shared Pool │ │ │
│ │ │ ┌─────┐ │ ┌────┐ ┌────┐ │ │
│ │ │ │Library │ │ │Database│ │ Redo │ │ │
│ │ │ │ Cache │ │ │Buffer │ │ Log │ │ │
│ │ │ └─────┘ │ │Cache │ │ Buffer │ │ │
│ │ │ ┌─────┐ │ │ │ │ │ │ │
│ │ │ │Data Dict │ │ └────┘ └────┘ │ │
│ │ │ │ Cache │ │ │ │
│ │ │ └─────┘ │ │ │
│ │ └────────┘ │ │
│ └──────────────────────────┘ │
│ ┌──┐ ┌──┐ ┌──┐ ┌──┐ ┌──┐ ┌───┐│
│ │PMON│ │SNON│ │DBWR│ │LGWR│ │CHPT│ │OTHERS││
│ └──┘ └──┘ └──┘ └──┘ └──┘ └───┘│
└──────────────────────────────┘
实例由内存和后台进程组成，它暂时存在于RAM和CPU中。当关闭运行的实例时，实例将随即消失。数据库由磁盘上的物理文件组成，无论在运行状态还是停止状态。这些文件就一直存在。因此。实例的生命周期就是其在内存中存在的时间。能够启动和停止。一旦创建数据库，数据库将永久存在。通俗的讲数据库就相当于平时安装某个程序所生成的安装文件夹，而实例就是运行某个程序时所须要的进程及消耗的内存。
Oracle的内存架构包括两部分系统全局区（SGA）和程序全局区（PGA）。
3.3.1 程序全局区
3.3.2 系统全局区
在操作系统提供的共享内存段实现的内存结构称为系统全局区（SGA）。SGA在实例启动时分配。在关闭时释放。在一定范围内，能够在实例运行时通过自己主动方式或响应DBA的指令。又一次调整11g实例中的SGA及当中的组件的大小。
由上图可知SGA至少包括三种数据结构：数据库缓冲区缓存、日志缓冲区及共享池。还可能包括：大池、JAVA池。能够使用show sga，查看sga的状态。
1、共享池
a.库缓存是内存区域，按其已分析的格式存储近期运行的代码。
分析就是将编程人员编写的代码转换为可运行的代码，这是oracle依据须要运行的一个过程。通过将代码缓存在共享池。能够在不又一次分析的情况下重用，极大地提高性能。
b.数据字典缓存有时称为“行缓存”。它存储近期使用的对象定义：表、索引、用户和其它元数据定义的描写叙述。
c.PL/SQL区：存储的PL/SQL对象是过程、函数、打包的过程、打包的函数、对象类型定义和触发器。
2、数据库缓冲区
数据库缓冲区是oracle用来运行SQL的工作区域。
3、日志缓冲区
日志缓冲区是小型的、用于短期存储将写入到磁盘上的重做日志的变更向量的暂时区域。日志缓冲区在启动实例时分配，假设不又一次启动实例，就不能在随后调整其大小。
后台进程有：
1、PMON—–程序监控器
2、SMON—–系统监控区
3、DBWR—–数据写进程
4、LGWR—–日志写进程
5、CKPT—–检查点进程
6、Others—归档进程
四、SQL语法基础（DDL、DCL、TCL、DML）
SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，IBM 公司最早使用在其开发的数据库系统中。
1986 年 10 月，美国 ANSI 对 SQL 进行规范后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)，1987 年得到国际标准组织的支持下成为国际标准。只是各种通行的数据库系统在事实上践过程中都对 SQL 规范作了某些编改和扩充。所以，实际上不同数据库系统之间的 SQL 语言不能全然相互通用
DML 语句（数据操作语言）Insert、Update、 Delete、Select
DDL 语句（数据定义语言）Create、Alter、 Drop
DCL 语句（数据控制语言）Grant、Revoke
TCL 事务控制语句 Commit 、Rollback、Savepoint
4.1 入门语句
普通用户连接： Conn scott/tiger
超级管理员连接： Conn “sys/bluesot as sysdba”
Disconnect; 断开连接————-disc
Save c:\1.txt 把 SQL 存到文件
Ed c:\1.txt 编辑 SQL 语句
@ c:\1.txt 运行 SQL 语句
Desc emp; 描写叙述 Emp 结构
Select * from tab; 查看该用户下的全部对象
Show user; 显示当前用户
假设在 sys 用户下： 查询 Select * from emp; 会报错，原因：emp 是属于 scott，所以此时必须使用：select * from scott.emp; / 运行上一条语句
4.2 DDL（数据定义语言）—-改变表结构
4.2.1 创建表
CREATE TABLE name （
tid VARCHAR2(5)，
tname VARCHAR2(20)，
tdate DATE。
as VARCHAR(7,2)
）。
4.2.2 加入一列
ALTER TABLE 表名 ADD 列名 属性;
ALTER TABLE student ADD age number(5);
4.2.4 删除一列
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE student DROP COLUMN age;
4.2.5 改动一列的属性
ALTER TABLE table_name MODIFY column_name type；
改动列名
ALTER TABLE table_name RENAME COLUMN columnname TO newname;
改动表名
ALTER TABLE table_name RENAME TO newname;
4.2.6 查看表中数据
DESC table_name;
4.2.7 删除表
DROP TABLE table_name;
4.3 DCL（数据控制语言）
4.3.1 授权
GRANT 权限/角色 TO 用户
给 demo 用户以创建 session 的权限：GRANT create session TO demo;
角色：————-角色就是一堆权限的集合Create role myrole;
Grant create table to myrole;
Drop role myrole; 删除角色
１．CONNECT, RESOURCE, DBA
这些提前定义角色主要是为了向后兼容。
其主要是用于数据库管理。
oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本号中这些角色可能不会作为提前定义角色。
２．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE 这些角色主要用于訪问数据字典视图和包。
３．EXP_FULL_DATABASE， IMP_FULL_DATABASE 这两个角色用于数据导入导出工具的使用。
GRANT 权限（select、update、insert、delete） ON schema.table TO 用户
|- GRANT select ON scott.emp TO test ;
|- Grant all on scott.emp to test; –将表相关的全部权限付给 test
|- Grant update(ename) on emp to test; 能够控制到列(还有 insert)
4.3.2 收权
REVOKE 权限/角色 ON schema.table FROM 用户
|- REVOKE select ON scott.emp FROM test ;
4.4 TCL（事务控制语言）
事务的概念：事务是一系列对数据库操作命令的集合，它有边界（commit—commit）
事务的特征：A ——原子性—–不可切割性——-要么运行要么不运行
C——一致性—–rollback
I——-隔离性—–锁的机制来保证的，锁有粒度【表、行】———仅仅读、改动锁
上锁—SELECT * FROM DEMO1 FOR UPDATE;
释放——commit、rollback
D —–持久性——-commit
系统时间—–sysdate
to_date（‘2013/11/09’，‘yyyy/mm/dd’）——–改动日期格式
转换函数
1、To_char
select to_char(sysdate,’yyyy’) from dual;
select to_char(sysdate,’fmyyyy-mm-dd’) from dual;
select to_char(sal,’L999,999,999’) from emp;
select to_char(sysdate,’D’) from dual;//返回星期
2、To_number
select to_number(‘13’)+to_number(‘14’) from dual;
3、To_date
Select to_date(‘20090210’,‘yyyyMMdd’) from dual;
4.4.1 ROLLBACK
回滚——回滚到它上面的离它近期的commit
4.4.2 COMMIT
提交———–将数据缓冲区的数据提交到文件里去
4.4.3 SAVEPOINT——保存点
回滚点样例：
INSERT INTO DEMO1(TID,TNAME) VALUES(11,’AS’);
SAVEPOINT P1;
INSERT INTO DEMO1(TID,TNAME) VALUES(22,’AS’);
INSERT INTO DEMO1(TID,TNAME) VALUES(33,’AS’);
SAVEPOINT P2;
INSERT INTO DEMO1(TID,TNAME) VALUES(44,’AS’);
ROLLBACK TO P2;
COMMIT;
INSERT INTO DEMO1(TID,TNAME) VALUES(55,’AS’);
ROLLBACK TO P1;———-无法回滚
————查询结果：11,22,33 由于55没有提交所以没有写入文件
4.5 DML（数据操作语言）———改变数据结构
4.5.1 insert 语句
INSERT INTO table_name() VALUES();
INSERT INTO table_name VALUES();
插入空值时，用三种格式
1、INSERT INTO demo VALUES(”);
2、INSERT INTO demo VALUES(’ ‘);
3、INSERT INTO demo VALUES(NULL);
INSERT INTO demo(tid,tname,tdate) VALUES(1,null,sysdate);
INSERT INTO demo(tid,tname,tdate) VALUES(1,”,to_date(sysdate,’yyyy-mm-dd’));
INSERT INTO demo VALUES(1,”,to_date(‘2013/11/11’,’yyyy/mm/dd’));
注意：
1、字符串类型的字段值必须用单引號括起来。如：‘rain’;
2、假设字段值里包括单引號须要进行字符串转换，把它替换成两个单引號‘’，如：‘”c”’ 数据库中将插入‘c’；
3、字符串类型的字段值超过定义长度会报错，最好在插入前进行长度校验；
4、日期字段的字段值能够使用当前数据库的系统时间sysdate。精确到秒。
5、INSERT时假设要用到从1開始自己主动增长的序列号，应该先建立一个序列号。
4.5.2 update 语句
UPDATE table_name SET column_name=值 WHERE 查询条件
UPDATE demo SET tname=’张三’ WHERE tid=1;
COMMIT；——是更新生效
4.5.3 delete 语句—–不能回退
DELETE TABLE table_name；————–仅仅能删除数据，不能释放空间
TRUNCATE TABLE table_name。—————删除数据并释放空间
DELETE TABLE demo;
TRUNCATE TABLE demo;
4.5.4 select 语句
A 、简单的 Select 语句
SELECT * FROM table_name；
SELECT * FROM demo;
B、空值 is null
SELECT * FROM demo where tname is null;
第五章 Select查询
结构： 运行顺序
SELECT * 5
FROM table_name 1
WHERE —–分组前的条件 2
GROUP BY 3
HAVING —–分组后的条件 4
ORDER BY column_name ASC/DESC; 6 —————————– 子句、聚合函数、友好列 列名 AS 别名
5.1 简单查询
5.1.1 查看表结构
DESC table_name；
DESC emp;
5.1.2查看全部的列
SELECT * FROM table_name;
SELECT * FROM emp;
5.1.3 查看指定列
SELECT 列名 FROM table_name;
SELECT empno FROM emp;
5.1.4 查看指定行
SELECT * FROM table_name WHERE column_name=值；
SELECT * FROM emp WHERE empno=7369;
5.1.5 使用算术表达式 + 、- 、/ 、*
SELECT ename,sal+1 FROM EMP;
ENAME SAL+1
SMITH 801
ALLEN 1601
WARD 1251
SELECT ename,sal-2 FROM EMP;
ENAME SAL-1
SMITH 799
ALLEN 1599
WARD 1249
SELECT ename,sal*2 FROM EMP;
ENAME SAL*2
SMITH 1600
ALLEN 3200
WARD 2500
SELECT ename,sal/2 FROM EMP;
ENAME SAL/2
SMITH 400
ALLEN 800
WARD 625
nvl(comm,0)的意思是。假设comm中有值，则nvl(comm,0)=comm; comm中无值。则nvl(comm,0)=0
5.1.3 连接运算符 ||
SELECT ‘how’||’do’ ||sno;
5.1.4 使用字段别名 AS
SELECT * FROM DEMO1 “Asd”;——-带引號能够保证输入的结果有大写和小写
SELECT * FROM DEMO1 asd;
5.1.5 空值 IS NULL、 IS NOT NULL
SQL>SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL IN(SELECT ENAME,sal FROM emp WHERE ENAME LIKE ‘_O%’) ;
5.1.6 去除反复行 DISTINCT
SQL>SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO
SQL>SELECT DEPTNO FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO
5.1.7 查询结果排序 ORDER BY ASC/DESC
逆序：
SQL> SELECT * FROM emp WHERE job=’CLERK’ OR ename=’MILLER’ ORDER BY SAL DESC;
顺序：
SQL> SELECT * FROM emp WHERE job=’CLERK’ OR ename=’MILLER’ ORDER BY SAL ASC;
SQL> SELECT * FROM emp WHERE job=’CLERK’ OR ename=’MILLER’ ORDER BY SAL ;
5.1.8 关系运算符 >、 < 、=、(!= or <>) MOD(模，类似于%)、BETWEEN AND、 NOT BETWEEN AND
SQL> SELECT DISTINCT MGR FROM emp WHERE MGR<>7788;
SQL>SELECT DISTINCT MGR FROM emp WHERE MGR BETWEEN 7788 AND 7902;
SQL>SELECT * FROM emp WHERE MOD(DEPTNO,100)=2;
SQL>SELECT * FROM EMP1 WHERE EMPNO NOT BETWEEN 7369 AND 7521;
SQL>SELECT * FROM EMP1 WHERE EMPNO>=7369 AND EMPNO<=7521;
5.1.9 操作 IN、NOT IN
SQL> SELECT ENAME,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP1 GROUP BY DEPTNO);
5.1.10 模糊查询 LIKE、NOT LIKE—-仅仅针对字符型
% 表示零或多个字符
_ 表示一个字符
对于特殊符号可使用ESCAPE 标识符来查找
select * from emp where ename like ‘%_%’ escape ‘’
上面的escape表示*后面的那个符号不当成特殊字符处理，就是查找普通的_符号
5.1.11 逻辑运算符 AND、OR、NOT
SQL> select * from emp where job=’CLERK’ OR ename=’MILLER’;
SQL> select * from emp where job=’CLERK’ AND ename=’MILLER’;
5.1.12 ANY、ALL
SQL>SELECT * FROM EMP WHERE EMPNO IN(7369,7521,7782);
SQL>SELECT * FROM EMP WHERE EMPNO >ANY(7369,7521,7782);–大于min
SQL>SELECT * FROM EMP WHERE EMPNO >ALL(7369,7521,7782);–大于max
SQL>SELECT * FROM EMP WHERE EMPNO< ANY(7369,7521,7782);–小于max
SQL>SELECT * FROM EMP WHERE EMPNO< ALL(7369,7521,7782);–小于min
5.1.13 练习
1、选择在部门30中员工的全部信息
Select * from emp where deptno=30;
2、列出职位为（MANAGER）的员工的编号，姓名
Select empno,ename from emp where job =”Manager”;
3、找出奖金高于工资的员工
Select * from emp where comm>sal;
4、找出每一个员工奖金和工资的总和
Select sal+comm,ename from emp;
5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
Select * from emp where (deptno=10 and job=?
MANAGER?) or (deptno=20 and job=?CLERK?
);
6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
Select * from emp where deptno=10 and job not in(‘MANAGER’) and sal>=2000;
7、找出有奖金的员工的不同工作
Select distinct job from emp where comm is not null and comm>0;
8、找出没有奖金或者奖金低于500的员工
Select * from emp where comm<500 or comm is null;
9、显示雇员姓名，依据其服务年限。将最老的雇员排在最前面
select ename from emp order by hiredate ;
10、SQL>SELECT DEPTNO,MAX(SAL) AS tot,
MIN(COMM) AS MCOMM,
SUM(COMM) SUMC,
TRUNC(AVG(SAL+NVL(COMM,0)))TAVG,
ROUND(AVG(SAL+NVL(COMM,0)),2)RAVG,
COUNT(*)
FROM EMP
HAVING COUNT(*) >3
GROUP BY DEPTNO
ORDER BY DEPTNO
11、SQL>SELECT ENAME,SAL
FROM EMP
WHERE SAL IN(SELECT MAX(SAL)
FROM EMP1
GROUP BY DEPTNO);
5.2 多表查询
INNER JOIN:内连接
JOIN: 假设表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回全部的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回全部的行
FULL JOIN: 仅仅要当中一个表中存在匹配。就返回行
5.2.1、笛卡尔集(Cross Join)————列相加，行相乘
Select * from emp,dept;
5.2.2、等值连接(Equijoin)(Natural join..on)———隐式内联
select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno;
5.2.3、非等值连接(Non-Equijoin)
select ename,empno,grade from emp,salgrade where sal between losal and hisal;
5.2.4、自联接(Self join)
select column_name from table_name1,table_name2 where 条件;
select e.empno,e.ename,m.empno,m.ename from emp e,emp m where m.mgr = e.empno;
5.2.5、内联接（Inner Join）———-显式外联
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
5.2.6、 左外联接（Left Outer Join ）
左外连接：在内联接的基础上。添加左表中没有匹配的行，也就是说，左表中的记录总会出如今终于结果集中
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+);
1、先通过from自句推断左表和右表。
2、接着看加号作用在那个表别名上。
3、假设作用在右表上，则为左外连接，否则为右外连接
select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno;
5.2.7、右外联接（Right Outer Join）
右外连接：在内联接的基础上，添加右表中没有匹配的行。也就是说，右表中的记录总会出如今终于结果集中
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
右外联接转换为内联接
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name WHERE table_name1.column_name is not null;
select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid(+)=s1.sid;
select empno,ename,dname from emp right outer join dept on emp.deptno= dept.deptno;
select * from emp1 e right join dept d on e.deptno=d.deptno where e.deptno is not null;
5.2.8、全外联接（Full Outer Join）
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
select empno,ename,dname from emp full outer join dept on emp.deptno = dept.deptno;
5.2.9、集合操作
· UNION：并集，全部的内容都查询。反复的显示一次
·UNION ALL：并集，全部的内容都显示，包括反复的
· INTERSECT：交集：仅仅显示反复的
· MINUS：差集：仅仅显示对方没有的（跟顺序是有关系的）
SELECT column_name(s) FROM table_name1
UNION/INTERSECT/MINUS
SELECT column_name(s) FROM table_name2
首先建立一张仅仅包括 20 部门员工信息的表：
CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20 ;
1、验证 UNION 及 UNION ALL
UNION：SELECT * FROM emp UNION SELECT * FROM emp20 ;
使用此语句反复的内容不再显示了
UNION ALL：SELECT * FROM emp UNION ALL SELECT * FROM emp20 ;
反复的内容依旧显示
2、验证 INTERSECT
SELECT * FROM emp INTERSECT SELECT * FROM emp20 ; 仅仅显示了两个表中彼此反复的记录。
MINUS：返回差异的记录
SELECT * FROM emp MINUS SELECT * FROM emp20 ; 仅仅显示了两张表中的不同记录满链接也能够用以下的方式来表示：
select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+) union
select t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id
5.3 子查询
5.3.1、单行子查询
select * from emp
where sal > (select sal from emp where empno = 7566);
5.3.2、 子查询空值/多值问题
假设子查询未返回不论什么行，则主查询也不会返回不论什么结果
(空值)select * from emp where sal > (select sal from emp where empno = 8888);
假设子查询返回单行结果，则为单行子查询。能够在主查询中对其使用相应的单行记录比較运算符
(正常)select * from emp where sal > (select sal from emp where empno = 7566);
假设子查询返回多行结果，则为多行子查询，此时不同意对其使用单行记录比較运算符
(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法—-错误的
5.3.3、 多行子查询
select * from emp where sal > any(select avg(sal) from emp group by deptno);
select * from emp where sal > all(select avg(sal) from emp group by deptno);
select * from emp where job in (select job from emp where ename = ‘MARTIN’ or ename = ‘SMITH’);
5.3.4、 分页查询
Oracle分页
①採用rownumkeyword(三层嵌套)
SELECT * FROM(
SELECT A.*,ROWNUM num FROM (
SELECT * FROM t_order)A
WHERE ROWNUM<=15)
WHERE num>=5; –返回第5-15行数据
②採用row_number解析函数进行分页(效率更高)
SELECT xx.* FROM(
SELECT t.*,row_number() over(ORDER BY o_id)AS num
FROM t_order t
)xx
WHERE num BETWEEN 5 AND 15;
ROWID和ROWNUM的差别
1、ROWID是物理地址。用于定位ORACLE中详细数据的物理存储位置是唯一的18位物理编号，而ROWNUM则是依据sql查询后得到的结果自己主动加上去的
2、ROWNUM是暂时的而且总是从1開始排起，而ROWID是永久的。
解析函数能用格式
函数() over(pertion by 字段 order by 字段);
ROW_NUMBER()： Row_number函数返回一个唯一的值，当碰到同样数据时，排名依照记录集中记录的顺序依次递增。 row_number()和rownum几乎相同，功能更强一点（能够在各个分组内从1开时排序），由于row_number()是分析函数而rownum是伪列所以row_number()一定要over而rownum不能over。
RANK()：Rank函数返回一个唯一的值。除非遇到同样的数据，此时全部同样数据的排名是一样的，同一时候会在最后一条同样记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
DENSE_RANK()：Dense_rank函数返回一个唯一的值，除非当碰到同样数据。此时全部同样数据的排名都是一样的。
dense_rank()是连续排序。有两个第二名时仍然跟着第三名。他和row_number的差别在于row_number是没有反复值的。
dense_rank()是连续排序，有两个第二名时仍然跟着第三名。他和row_number的差别在于row_number是没有反复值的。
以下举个样例：
SQL> select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
10 30 1216858
5 2 1224992
9 24 1224992
9 23 1224992
8 18 1253840
【3】row_number()、rank()、dense_rank()这三个分析函数的差别实例
SQL>SELECT empno, deptno, SUM(sal) total,
RANK() OVER(ORDER BY SUM(sal) DESC) RANK,
dense_rank() OVER(ORDER BY SUM(sal) DESC)dense_rank,
row_number() OVER(ORDER BY SUM(sal) DESC)row_number
FROM emp1 GROUP BY empno, deptno;
1 7839 10 5000 1 1 1
2 7902 20 3000 2 2 2
3 7788 20 3000 2 2 3
4 7566 20 2975 4 3 4
5 7698 30 2850 5 4 5
6 7782 10 2450 6 5 6
7 7499 30 1600 7 6 7
比較上面3种不同的策略，我们在选择的时候就要依据客户的需求来定夺了：
①假如客户就仅仅须要指定数目的记录。那么採用row_number是最简单的，但有漏掉的记录的危急
②假如客户须要全部达到排名水平的记录，那么採用rank或dense_rank是不错的选择。至于选择哪一种则看客户的须要。选择dense_rank或得到最大的记录
Mysql分页採用limtkeyword
select * from t_order limit 5,10; #返回第6-15行数据
select * from t_order limit 5; #返回前5行
select * from t_order limit 0,5; #返回前5行
Mssql 2000分页採用topkeyword(20005以上版本号也支持keywordrownum)
Select top 10 * from t_order where id not in (select id from t_order where id>5 ); //返回第6到15行数据当中10表示取10记录 5表示从第5条记录開始取
sql server 分页採用topkeyword
5.3.5、 in 、exists
EXISTS 的运行流程
select * from t1 where exists ( select null from t2 where y = x )
能够理解为:
for x in ( select * from t1 ) loop
if ( exists ( select null from t2 where y = x.x ) then
OUTPUT THE RECORD
end if。
end loop；
对于 in 和 exists 的性能差别:
假设子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in,反之假设外层的主查询记录较少，子查询中的表大。又有索引时使用 exists。
事实上我们区分 in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键）。假设是 exists，那么以外层表为驱动表，先被訪问，假设是 IN，那么先运行子查询，所以我们会以驱动表的高速返回为目标，那么就会考虑到索引及结果集的关系了 另外 IN 是不正确 NULL 进行处理
如：
select 1 from dual where not in (0,1,2,null) 为空
第六章 约束
约束就是指对插入数据的各种限制，比如：人员的姓名不能为空。人的年龄仅仅能在0~150 岁之间。约束能够对数据库中的数据进行保护。
约束能够在建表的时候直接声明，也能够为已建好的表加入约束。
6.1、NOT NULL：非空约束 比如：姓名不能为空
CREATE TABLE person(
pid NUMBER ,
name VARCHAR2(30) NOT NULL
) ;
alter table emp
– 插入数据
INSERT INTO person(pid,name) VALUES (11,’张三’);
– 错误的数据，会受到约束限制，无法插入
INSERT INTO person(pid) VALUES (12);
6.2、 PRIMARY KEY：主键约束
· 不能反复，不能为空 · 比如：身份证号不能为空。 如今假设pid字段不能为空，且不能反复。
DROP TABLE person ;
CREATE TABLE person
(
pid NUMBER PRIMARY KEY , name VARCHAR(30) NOT NULL
) ;
– 插入数据
INSERT INTO person(pid,name) VALUES (11,’张三’);
– 主键反复了
INSERT INTO person(pid,name) VALUES (11,’李四’);
6.3、UNIQUE：唯一约束。值不能反复（空值除外） 人员中有电话号码，电话号码不能反复。
DROP TABLE person ;
CREATE TABLE person
(
pid NUMBER PRIMARY KEY NOT NULL , name VARCHAR(30) NOT NULL , tel VARCHAR(50) UNIQUE
) ;
– 插入数据
INSERT INTO person(pid,name,tel) VALUES (11,’张三’,’1234567’);
– 电话反复了
INSERT INTO person(pid,name,tel) VALUES (12,’李四’,’1234567’);
6.4、CHECK：条件约束，插入的数据必须满足某些条件
比如：人员有年龄，年龄的取值仅仅能是 0~150 岁之间
DROP TABLE person ;
CREATE TABLE person(
pid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR2(30) NOT NULL ,
tel VARCHAR2(50) NOT NULL UNIQUE ,
age NUMBER CHECK(age BETWEEN 0 AND 150)
) ;
– 插入数据
INSERT INTO person(pid,name,tel,age) VALUES (11,’张三’,’1234567’,30);
– 年龄的输入错误
INSERT INTO person(pid,name,tel,age) VALUES (12,’李四’,’2345678’,-100);
alter table product
add constriant chk_product_unitprice check(unitprice>0);
6.5、Foreign Key：外键
比如：有以下一种情况：
· 一个人有非常多本书：
|- Person 表
|- Book 表：而且book 中的每一条记录表示一本书的信息，一本书的信息属于一个人
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(50) ,
– 书应该属于一个人 pid NUMBER
) ;
假设使用了以上的表直接创建，则插入以下的记录有效：
INSERT INTO book(bid,name,pid) VALUES(1001,’JAVA’,12) ;
以上的代码没有不论什么错误，可是没有不论什么意义。由于一本书应该属于一个人，所以在此处的pid的取值应该与person 表中的pid一致。
此时就须要外键的支持。改动book 的表结构
DROP TABLE book ;
CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL , name VARCHAR(50) ,
– 书应该属于一个人 pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
– 建立约束：book_pid_fk，与person中的pid 为主-外键关系
–CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;
INSERT INTO book(bid,name,pid) VALUES(1001,’JAVA’,12) ;
6.6、级联删除
此时假设想完毕删除person 表的数据同一时候自己主动删除掉book 表的数据操作，则必须使用级联删除。
在建立外键的时候必须指定级联删除（ON DELETE CASCADE）。
CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL , name VARCHAR(50) ,
– 书应该属于一个人
pid NUMBER ,
– 建立约束：book_pid_fk，与person中的pid 为主-外键关系
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
) ;
DROP TABLE book ;
DROP TABLE person ;
CREATE TABLE person(
pid NUMBER ,
name VARCHAR(30) NOT NULL ,
tel VARCHAR(50) ,
age NUMBER
) ;
CREATE TABLE book(
bid NUMBER ,
name VARCHAR2(50) ,
pid NUMBER
) ;
以上两张表中没有不论什么约束，以下使用 alter 命令为表加入约束
ALTER TABLE table_name ADD CONSTRAINT column_name 约束;
1、 为两个表加入主键：
· person 表 pid 为主键：
ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid) ;
· book 表 bid 为主键：
ALTER TABLE book ADD CONSTRAINT book_bid_pk PRIMARY KEY(bid) ;
2、 为 person 表中的 tel 加入唯一约束：
ALTER TABLE person ADD CONSTRAINT person_tel_uk UNIQUE(tel) ;
3、 为 person 表中的 age 加入检查约束：
ALTER TABLE person ADD CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND
150) ;
4、 为 book 表中的 pid 加入与 person 的主-外键约束，要求带级联删除
ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY (pid)
REFERENCES person(pid) ON DELETE CASCADE ;
Q:怎样用alter加入非空约束
A:用 check约束
6.7、删除约束：
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk ;
alter table student drop unique(tel);
6.8、 启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk ;
6.9、 禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk ;
第七章 SQL函数
7.1 单行函数
1、字符函数
Upper ——-大写
SELECT Upper (‘abcde’) FROM dual ;
SELECT * FROM emp WHERE ename=UPPER(‘smith’) ;
Lower ——–小写
SELECT lower(‘ABCDE’) FROM dual ;
Initcap——–首字母大写
Select initcap(ename) from emp;
Concat—-联接
Select concat(‘a’,’b’) from dual;
Select ‘a’||’b’ from dual;
Substr——截取
Select substr(‘abcde’,length(‘abcde’)-2) from dual;
Select substr(‘abcde’,-3,3) from dual;
Length——长度
Select length(ename) from emp;
Replace——替代
Select replace(ename,’a’,’A’) from emp;
Instr———- indexof
Select instr(‘Hello World’,’or’) from dual;
Lpad————左側填充 lpad() *****Smith
lpad(‘Smith’,10,’*’)
Rpad————-右側填充 rpad()Smith*****
rpad(‘Smith’,10,’*’)
Trim————-过滤首尾空格 trim() Mr Smith
trim(’ Mr Smith ‘)
2、数值函数
Round
select round(412,-2) from dual; –400
select round(412.313,2) from dual;
Mod
select MOD(412,3) from dual;
Trunc
select trunc(412.13,-2) from dual;
3、日期函数
Months_between()
select months_between(sysdate,hiredate) from emp;
Add_months()
select add_months(sysdate,1) from dual;
Next_day()
select next_day(sysdate,’星期一’) from dual;
Last_day
select last_day(sysdate) from dual;
4.4、转换函数
To_char
select to_char(sysdate,’yyyy’) from dual; select to_char(sysdate,’fmyyyy-mm-dd’) from dual; select to_char(sal,’L999,999,999’) from emp; select to_char(sysdate,’D’) from dual;//返回星期
To_number
select to_number(‘13’)+to_number(‘14’) from dual;
To_date
Select to_date(?
20090210?
,?yyyyMMdd?) from dual;
5、通用函数
NVL()函数
select nvl(comm,0) from emp;
NULLIF()函数
假设表达式 exp1 与 exp2 的值相等则返回 null。否则返回 exp1 的值
NVL2()函数 select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;
COALESCE()函数
依次考察各參数表达式，遇到非 null 值即停止并返回该值。
select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入 from emp;
CASE 表达式——区间
SQL>select empno,
ename,
sal,
case deptno
when 10 then ‘財务部’
when 20 then ‘研发部’
when 30 then ‘销售部’
else ‘未知部门’
end 部门
from emp;
SQL>SELECT e.*,
CASE
WHEN sal>=3000 THEN ‘高’
WHEN sal>=2000 AND sal<3000 THEN ‘中’
WHEN sal<2000 AND sal>0 THEN ‘高’
ELSE ‘un’
END ca
FROM emp1 e;
DECODE()函数————离散的值
SQL>select empno, ename, sal,
decode(deptno,
10, ‘財务部’,
20, ‘研发部’,
30, ‘销售部’,
‘未知部门’) 部门
from emp;
单行函数嵌套
select empno, lpad(initcap(trim(ename)),10,’ ‘) name, job, sal from emp;
7.2 分组函数
1、COUNT
假设数据库表的没有数据，count(*)返回的不是 null。而是 0
2、Avg，max，min，sum
3、nvl—-分组函数与空值
分组函数省略列中的空值
select avg(comm) from emp; select sum(comm) from emp;
可使用 NVL()函数强制分组函数处理空值 select avg(nvl(comm, 0)) from emp;
4、GROUP BY 子句
出如今 SELECT 列表中的字段或者出如今 order by 后面的字段，假设不是包括在分组函数中。那么该字段必须同一时候在 GROUP BY 子句中出现。包括在 GROUP BY 子句中的字段则不必须出如今 SELECT 列表中。
可使用 where 字句限定查询条件,可使用 Order by 子句指定排序方式
假设没有 GROUP BY 子句，SELECT 列表中不同意出现字段（单行函数）与分组函数混用的情况。
select empno, sal from emp; //合法 select avg(sal) from emp; //合法 select empno, initcap(ename), avg(sal) from emp; //非法
不同意在 WHERE 子句中使用分组函数。 select deptno, avg(sal) from emp where avg(sal) > 2000; group by deptno;
5、HAVING 子句
select deptno, job, avg(sal)
from emp
where hiredate >= to_date(‘1981-05-01’,’yyyy-mm-dd’) group by deptno,job having avg(sal) > 1200 order by deptno,job;
6、分组函数嵌套
select max(avg(sal)) from emp group by deptno;
第八章 PLSQL变量与常量
1、声明变量
变量一般都在PL/SQL块的声明部分声明。引用变量前必须首先声明，要在运行或异常处理部分使用变量，那么变量必须首先在声明部分进行声明。
声明变量的语法例如以下：
Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]
注意:能够在声明变量的同一时候给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。
2、给变量赋值
给变量赋值有两种方式：
. 直接给变量赋值
X:=200;
Y=Y+(X*20);
. 通过SQL SELECT INTO 或FETCH INTO给变量赋值
SELECT SUM(SALARY),SUM(SALARY*0.1)
INTO TOTAL_SALARY,TATAL_COMMISSION
FROM EMPLOYEE
WHERE DEPT=10;
3、常量
常量与变量类似，但常量的值在程序内部不能改变，常量的值在定义时赋予，，他的声明方式与变量类似。但必须包括keywordCONSTANT。
常量和变量都可被定义为SQL和用户定义的数据类型。
ZERO_VALUE CONSTANT NUMBER:=0;
4、标量(scalar)数据类型
标量(scalar)数据类型没有内部组件。他们大致可分为以下四类：
. number
. char
. date/time
. boolean
表1 Numer
Datatype Range Subtypes description
BINARY_INTEGER -214748-2147483647 NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE
用于存储单字节整数。
要求存储长度低于NUMBER值。
用于限制范围的子类型(SUBTYPE):
NATURAL:用于非负数
POSITIVE:仅仅用于正数
NATURALN:仅仅用于非负数和非NULL值
POSITIVEN:仅仅用于正数，不能用于NULL值
SIGNTYPE:仅仅有值:-1、0或1.
NUMBER 1.0E-130-9.99E125 DEC
DECIMAL
DOUBLE
PRECISION
FLOAT
INTEGERIC
INT
NUMERIC
REAL
SMALLINT 存储数字值，包括整数和浮点数。
能够选择精度和刻度方式，语法：
number[（[,]）]。
缺省的精度是38,scale是0.
PLS_INTEGER -2147483647-2147483647 与BINARY_INTEGER基本同样。但採用机器运算时，PLS_INTEGER提供更好的性能 。
表2 字符数据类型
datatype rang subtype description
CHAR 最大长度32767字节 CHARACTER 存储定长字符串。假设长度没有确定，缺省是1
LONG 最大长度2147483647字节 存储可变长度字符串
RAW 最大长度32767字节 用于存储二进制数据和字节字符串，当在两个数据库之间进行传递时，RAW数据不在字符集之间进行转换。
LONGRAW 最大长度2147483647 与LONG数据类型类似，同样他也不能在字符集之间进行转换。
ROWID 18个字节 与数据库ROWID伪列类型同样。能够存储一个行标示符，能够将行标示符看作数据库中每一行的唯一键值。
VARCHAR2 最大长度32767字节 STRINGVARCHAR 与VARCHAR数据类型类似，存储可变长度的字符串。声明方法与VARCHAR同样
表3 DATE和BOOLEAN
datatype range description
BOOLEAN TRUE/FALSE 存储逻辑值TRUE或FALSE,无參数
DATE 01/01/4712 BC 存储固定长的日期和时间值。日期值中包括时间
LOB数据类型
LOB(大对象,Large object) 数据类型用于存储类似图像。声音这种大型数据对象，LOB数据对象能够是二进制数据也能够是字符数据，其最大长度不超过4G。LOB数据类型支持随意訪问方式，LONG仅仅支持顺序訪问方式。
LOB存储在一个单独的位置上。同一时候一个”LOB定位符”(LOB locator)存储在原始的表中，该定位符是一个指向实际数据的指针。
在PL/SQL中操作LOB数据对象使用ORACLE提供的包DBMS_LOB.LOB数据类型可分为以下四类：
. BFILE—————-二进制文件
. BLOB————–二进制对象
. CLOB—————字符型对象
. NCLOB————-nchar类型对象
操作符
PL/SQL有一系列操作符。
操作符分为以下几类：
. 算术操作符
. 关系操作符
. 比較操作符
. 逻辑操作符
算术操作符如表4所看到的
operator operation
+ 加
- 减
/ 除
* 乘
** 乘方
关系操作符主要用于条件推断语句或用于where子串中，关系操作符检查条件和结果是否为true或false,
表5PL/SQL中的关系操作符
operator operation
< 小于操作符
<= 小于或等于操作符
大于操作符
= 大于或等于操作符
= 等于操作符
!= 不等于操作符
<> 不等于操作符
:= 赋值操作符
表6 显示的是比較操作符
operator operation
IS NULL 假设操作数为NULL返回TRUE
LIKE 比較字符串值
BETWEEN 验证值是否在范围之内
IN 验证操作数在设定的一系列值中
表7显示的是逻辑操作符
operator operation
AND 两个条件都必须满足
OR 仅仅要满足两个条件中的一个
NOT 取反
第九章 PL/SQL——–动态SQL
PL/SQL动态SQL(原创)
概念：动态SQL，编译阶段无法明白SQL命令。仅仅有在运行阶段才干被识别
动态SQL和静态SQL两者的异同
静态SQL为直接嵌入到PL/SQL中的代码。而动态SQL在运行时，依据不同的情况产生不同的SQL语句。
静态SQL在运行前编译。一次编译，多次运行。动态SQL同样在运行前编译。但每次运行须要又一次编译。
静态SQL能够使用同样的运行计划，对于确定的任务而言，静态SQL更具有高效性，但缺乏灵活性；动态SQL使用了不同的运行计划。效率不如静态SQL。但能够解决复杂的问题。
动态SQLeasy产生SQL注入。为数据库安全带来隐患。
动态SQL语句的几种方法
a.使用EXECUTE IMMEDIATE语句
包括DDL语句，DCL语句，DML语句以及单行的SELECT 语句。
该方法不能用于处理多行查询语句。
b.使用OPEN-FOR。FETCH和CLOSE语句
对于处理动态多行的查询操作，能够使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据，终于使用CLOSE语句关闭游标。
c.使用批量动态SQL
即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。
d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL
动态SQL的语法
以下是动态SQL经常使用的语法之中的一个
EXECUTE IMMEDIATE dynamic_SQL_string
[INTO defined_variable1, defined_variable2, …]
[USING [IN | OUT | IN OUT] bind_argument1, bind_argument2,…]
[{RETURNING | RETURN} field1, field2, … INTO bind_argument1,
bind_argument2, …]
语法描写叙述
dynamic_SQL_string：存放指定的SQL语句或PL/SQL块的字符串变量
defined_variable1：用于存放单行查询结果。使用时必须使用INTOkeyword，类似于使用
SELECT ename INTO v_name FROM scott.emp；
仅仅只是在动态SQL时，将INTO defined_variable1移出到dynamic_SQL_string语句之外。
bind_argument1：用于给动态SQL语句传入或传出參数，使用时必须使用USINGkeyword，IN表示传入的參数，OUT表示传出的參数，IN OUT则既能够传入，也可传出。
RETURNING | RETURN 子句也是存放SQL动态返回值的变量。
使用要点
a.EXECUTE IMMEDIATE运行DML时，不会提交该DML事务，须要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。
b.EXECUTE IMMEDIATE运行DDL,DCL时会自己主动提交其运行的事务。
c.对于多行结果集的查询，须要使用游标变量或批量动态SQL，或者使用暂时表来实现。
d.当运行SQL时，其尾部不须要使用分号，当运行PL/SQL 代码时，其尾部须要使用分号。
f.动态SQL中的占位符以冒号开头，紧跟随意字母或数字表示。
动态SQL的使用(DDL，DCL，DML以及单行结果集)
DECLARE
v_table VARCHAR2(30):=’emp1’;
v_sql LONG:=’SELECT ename FROM emp1 WHERE empno=:1’;–占位符\能够用不论什么符号（数字或字母）
–v_no emp1.empno%TYPE:=’&请输入号码’;–&是变量绑定符 绑定的数据是字符串类型时必须要加引號
–v_no emp1.empno%TYPE:=&请输入号码;
v_name emp1.ename%TYPE;
BEGIN
–EXECUTE IMMEDIATE v_sql INTO v_name USING IN v_no;
dbms_output.put_line(‘ename=’||v_name);
–EXECUTE IMMEDIATE ‘create table emp2 as select * from emp’;
–EXECUTE IMMEDIATE ‘drop table ‘||v_table;
–EXECUTE IMMEDIATE ‘alter table emp enable row movement’;
END;
BULK子句和动态SQL的使用
动态SQL中使用BULK子句的语法
EXECUTE IMMEDIATE dynamic_string –dynamic_string用于存放动态SQL字符串
[BULK COLLECT INTO define_variable[,define_variable…]] –存放查询结果的集合变量
[USING bind_argument[,argument…]] –使用參数传递给动态SQL
[{RETURNING | RETURN} –返回子句
BULK COLLECT INTO return_variable[,return_variable…]]; –存放返回结果的集合变量
使用bulk collect into子句处理动态SQL中T的多行查询能够加快处理速度，从而提高应用程序的性能。当使用bulk子句时。集合类型能够是PL/SQL所支持的索引表、嵌套表和VARRY。但集合元 素必须使用SQL数据类型。经常使用的三种语句支持BULK子句，分别为EXECUTE IMMEDIATE，FETCH 和FORALL。
使用EXECUTE IMMEDIATE 结合BULK子句处理多行查询。使用了BULK COLLECT INTO来传递结果。
SQL>declare
type ename_table_type is table of emp1.ename%type index by binary_integer;
type sal_table_type is table OF emp1.sal%type index by binary_integer;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
v_dno number := 7369;
begin
sql_stat := ‘select ename,sal from emp1 where deptno = :v_dno’; –动态DQL语句，未使用RETURNING子句
execute immediate sql_stat
bulk collect into ename_table,sal_table using v_dno;
for i in 1..ename_table.count loop
dbms_output.put_line(‘Employee ’ || ename_table(i) || ’ Salary is: ’ || sal_table(i));
end loop;
end;
使用FETCH子句结合BULK子句处理多行结果集
以下的演示样例中首先定义了游标类型。游标变量以及复合类型，复合变量，接下来从动态SQL中OPEN游标，然后使用FETCH将结果存放到复合变量中。即使用OPEN。FETCH取代了EXECUTE IMMEDIATE来完毕动态SQL的运行。
SQL> declare
2 type empcurtype is ref cursor;
3 emp_cv empcurtype;
4 type ename_table_type is table of tb2.ename%type index by binary_integer;
5 ename_table ename_table_type;
6 sql_stat varchar2(120);
7 begin
8 sql_stat := ‘select ename from tb2 where deptno = :dno’;
9 open emp_cv for sql_stat using &dno;
10 fetch emp_cv bulk collect into ename_table;
11 for i in 1..ename_table.count loop
12 dbms_output.put_line(‘Employee Name: ’ || ename_table(i));
13 end loop;
14 close emp_cv;
15* end;
在FORALL语句中使用BULK子句
以下是FORALL子句的语法
FORALL index IN lower bound..upper bound –FORALL循环计数
EXECUTE IMMEDIATE dynamic_string –结合EXECUTE IMMEDIATE来运行动态SQL语句
USING bind_argument | bind_argument(index) –绑定输入參数
[bind_argument | bind_argument(index)]…
[{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument…]]; –绑定返回结果集
FORALL子句同意为动态SQL输入变量。但FORALL子句仅支持的DML(INSERT,DELETE,UPDATE)语句，不支持动态的SELECT语句。
以下的演示样例中，首先声明了两个复合类型以及复合变量。接下来为复合变量ename_table赋值。以形成动态SQL语句。紧接着使用FORALL子句结合EXECUTE IMMEDIATE 来提取结果集。
SQL> declare
2 type ename_table_type is table of tb2.ename%type;
3 type sal_table_type is table of tb2.sal%type;
4 ename_table ename_table_type;
5 sal_table sal_table_type;
6 sql_stat varchar2(100);
7 begin
8 ename_table := ename_table_type(‘BLAKE’,’FORD’,’MILLER’);
9 sql_stat := ‘update tb2 set sal = sal * 1.1 where ename = :1’
10 || ’ returning sal into :2’;
11 forall i in 1..ename_table.count
12 execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
13 for j in 1..sal_table.count loop
14 dbms_output.put_line(‘The ’ || ename_table(j) || ”” || ‘s new salalry is ’ || sal_table(j));
15 end loop;
16* end;
常见错误
1、使用动态DDL时。不能使用绑定变量。
EXECUTE IMMEDIATE ‘CREATE TABLE dsa ‘||’as select * from :1’ USING IN v_table;
解决的方法。将绑定变量直接拼接。例如以下：
EXECUTE IMMEDIATE ‘CREATE TABLE dsa ‘||’as select * from ‘|| v_table;
2、不能使用schema对象作为绑定參数，以下的演示样例中，动态SQL语句查询须要传递表名，因此收到了错误提示。
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM：tb_name ’ into v_count;
解决的方法。将绑定变量直接拼接，例如以下：
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ’ || tb_name into v_count;
3、动态SQL块不能使用分号结束(;)
execute immediate ‘select count(*) from emp;’ –此处多出了分号，应该去掉
4、动态PL/SQL块不能使用正斜杠来结束块，可是块结尾处必须要使用分号(;)
SQL> declare
2 plsql_block varchar2(300);
3 begin
4 plsql_block := ‘Declare ’ ||
5 ’ v_date date; ’ ||
6 ’ begin ’ ||
7 ’ select sysdate into v_date from dual; ’ ||
8 ’ dbms_output.put_line(to_char(v_date,”yyyy-mm-dd”)); ’ ||
9 ’ end;
10 /’; –此处多出了/。应该将其去掉
11 execute immediate plsql_block;
12* end;
4、空值传递的问题
以下的演示样例中对表tb_emp更新，并将空值更新到sal列，直接使用USING NULL收到错误提示。
execute immediate sql_stat using null,v_empno;
正确的处理方法
v_sal tb2.sal%type; –声明一个新变量，但不赋值
execute immediate sql_stat using v_sal,v_empno;
5、日期和字符型必须要使用引號来处理
DECLARE
sql_stat VARCHAR2(100);
v_date DATE :=TO_DATE(‘2013-11-21’,’yyyy-mm-dd’);
v_empno NUMBER :=7900;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
sql_stat := ‘SELECT ename,sal FROM emp WHERE hiredate=:v_date’;
EXECUTE IMMEDIATE sql_stat
INTO v_ename,v_sal
USING v_date;
DBMS_OUTPUT.PUT_LINE(‘Employee Name ‘||v_ename||’, sal is ‘||v_sal);
END;
6、单行SELECT 查询不能使用RETURNING INTO返回
以下的演示样例中，使用了动态的单行SELECT查询，而且使用了RETURNING子句来返回值。事实上。RETURNING coloumn_name INTO 子句仅仅支持对DML结果集的返回，因此。收到了错误提示。
SQL>declare
v_empno emp.empno%type := &empno;
v_ename emp.ename%type;
begin
execute immediate ‘select ename from emp where empno = :eno’ into v_ename using v_empno;
dbms_output.put_line(‘Employee name: ’ || v_ename);
end;
第十章 PL/SQL———游标
游标的使用
在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
4.1 游标概念
对于不同的SQL语句，游标的使用情况不同：
SQL语句
游标
非查询语句
隐式的
结果是单行的查询语句
隐式的或显示的
结果是多行的查询语句
显示的
游标名%属性名
显式游标的名字右用户定义，隐式游标名为SQL
隐式游标仅仅是一个状态
4.1.1 处理显式游标
显式游标处理
语法格式：
CURSOR cursor_name is select * from emp；
OPEN cursor_name;
FETCH cursor_name INTO variables_list;
CLOSE cursor_name;
例1：
DECLARE
v_deptno NUMBER:=&inputno;
v_row emp1%ROWTYPE;
CURSOR v_cursor IS SELECT * FROM emp1 WHERE deptno=v_deptno;
BEGIN
–打开
OPEN v_cursor;
–提取
LOOP
FETCH v_cursor INTO v_row;–先提取
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line(‘Employee Name: ’ || v_row.ename || ’ ,Salary: ’ || v_row.sal);
END LOOP;
–关闭
CLOSE v_cursor;
END;
显式游标处理需四个 PL/SQL步骤:
l 定义/声明游标：就是定义一个游标名，以及与其相相应的SELECT 语句。
格式：CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]IS
select_statement;
select_statement;
游标參数仅仅能为输入參数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时。不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。
[RETURN datatype]是可选的，表示游标返回数据的数据。假设选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。
通常是记录数据类型或带“%ROWTYPE”的数据。
l 打开游标：就是运行游标所相应的SELECT 语句，将其查询结果放入工作区。而且指针指向工作区的首部，标识游标结果集合。假设游标查询语句中带有FOR UPDATE选项。OPEN 语句还将锁定数据库表中游标结果集合相应的数据行。
格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向游标传递參数时，能够使用与函数參数同样的传值方法，即位置表示法和名称表示法。
PL/SQL 程序不能用OPEN 语句反复打开一个游标。
l 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
运行FETCH语句时，每次返回一个数据行，然后自己主动将游标移动指向下一个数据行。当检索到最后一行数据时，假设再次运行FETCH语句。将操作失败。并将游标属性%NOTFOUND置为TRUE。
所以每次运行完FETCH语句后，检查游标属性%NOTFOUND就能够推断FETCH语句是否运行成功并返回一个数据行，以便确定是否给相应的变量赋了值。
l 对该记录进行处理；
l 继续处理，直到活动集合中没有记录；
l 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效。不能再使用FETCH 语句取当中数据。关闭后的游标能够使用OPEN 语句又一次打开。
格式：
CLOSE cursor_name;
注：定义的游标不能有INTO 子句。
例1. 查询前10名员工的信息。
DECLARE
CURSOR c_cursor
IS SELECT first_name || last_name, Salary FROM EMPLOYEES WHERE rownum<11;
v_ename EMPLOYEES.first_name%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||’—’||to_char(v_sal) );
FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
END;
2.游标属性
Cursor_name%FOUND 布尔型属性。当近期一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN 布尔型属性。当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
例2：没有參数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_j_id employees.job_id%TYPE;
CURSOR c1 –声明游标,没有參数没有返回值
IS
SELECT first_name, job_id FROM employees WHERE department_id = 20;
BEGIN
OPEN c1; –打开游标
LOOP
FETCH c1 INTO v_f_name, v_j_id; –提取游标
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||’的岗位是’||v_j_id);
ELSE
DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了’);
EXIT;
END IF;
END LOOP;
CLOSE c1; –关闭游标
END;
例3：有參数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_h_date employees.hire_date%TYPE;
CURSOR c2(dept_id NUMBER, j_id VARCHAR2) –声明游标,有參数没有返回值
IS
SELECT first_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c2(90, ‘AD_VP’); –打开游标,传递參数值
LOOP
FETCH c2 INTO v_f_name, v_h_date; –提取游标
IF c2%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||’的雇佣日期是’||v_h_date);
ELSE
DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了’);
EXIT;
END IF;
END LOOP;
CLOSE c2; –关闭游标
END;
例4：有參数且有返回值的游标。
DECLARE
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE;
v_emp_record EMP_RECORD_TYPE;
CURSOR c3(dept_id NUMBER, j_id VARCHAR2) –声明游标,有參数有返回值
RETURN EMP_RECORD_TYPE
IS
SELECT first_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c3(j_id => ‘AD_VP’, dept_id => 90); –打开游标,传递參数值
LOOP
FETCH c3 INTO v_emp_record; –提取游标
IF c3%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||’的雇佣日期是’||v_emp_record.h_date);
ELSE
DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了’);
EXIT;
END IF;
END LOOP;
CLOSE c3; –关闭游标
END;
例5：基于游标定义记录变量。
DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) –声明游标,有參数没有返回值
IS
SELECT first_name f_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id;
–基于游标定义记录变量，比声明记录类型变量要方便。不easy出错
v_emp_record c4%ROWTYPE;
BEGIN
OPEN c4(90, ‘AD_VP’); –打开游标,传递參数值
LOOP
FETCH c4 INTO v_emp_record; –提取游标
IF c4%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||’的雇佣日期是’||v_emp_record.hire_date);
ELSE
DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了’);
EXIT;
END IF;
END LOOP;
CLOSE c4; –关闭游标
END;
3. 游标的FOR循环
PL/SQL语言提供了游标FOR循环语句。自己主动运行游标的OPEN、FETCH、CLOSE语句和循环语句的功能。当进入循环时。游标FOR循环语句自己主动打开游标。并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自己主动提取下一行数据供程序处理，当提取完结果集合中的全部数据行后结束循环。并自己主动关闭游标。
格式：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
– 游标数据处理代码
END LOOP;
当中：
index_variable为游标FOR 循环语句隐含声明的索引变量。该变量为记录变量，其结构与游标查询语句返回的结构集合的结构同样。在程序中能够通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名同样。
假设在游标查询语句的选择列表中存在计算列。则必须为这些计算列指定别名后才干通过游标FOR 循环语句中的索引变量来訪问这些列数据。
注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。
例6:当所声明的游标带有參数时，通过游标FOR 循环语句为游标传递參数。
DECLARE
CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
IS
SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘当dept_no參数值为30：’);
FOR c1_rec IN c_cursor(30) LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||’—’||c1_rec.location_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||’使用默认的dept_no參数值10：’);
FOR c1_rec IN c_cursor LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||’—’||c1_rec.location_id);
END LOOP;
END;
例7：PL/SQL还同意在游标FOR循环语句中使用子查询来实现游标的功能。
BEGIN
–隐含打开游标
FOR r IN (SELECT * FROM emp1 WHERE deptno=v_deptno) LOOP
–隐含运行一个FETCH语句
dbms_output.put_line(‘Employee Name: ’ || r.ename || ’ ,Salary: ’ || r.sal);
–隐含监測c_sal%NOTFOUND
END LOOP;
–隐含关闭游标
END;
4.1.2 处理隐式游标
对于非查询语句，如改动、删除操作，则由ORACLE 系统自己主动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。
对于隐式游标的操作。如定义、打开、取值及关闭操作，都由ORACLE 系统自己主动地完毕。无需用户进行处理。
用户仅仅能通过隐式游标的相关属性，来完毕相应的操作。在隐式游标的工作区中。所存放的数据是与用户自己定义的显示游标无关的、最新处理的一条SQL 语句所包括的数据。
格式调用为： SQL%
注：INSERT, UPDATE, DELETE, SELECT（单查询） 语句中不必明白定义游标。
隐式游标属性
属性
值
SELECT
INSERT
UPDATE
DELETE
SQL%ISOPEN
FALSE
FALSE
FALSE
FALSE
SQL%FOUND
TRUE
有结果
成功
成功
SQL%FOUND
FALSE
没结果
失败
失败
SQL%NOTFUOND
TRUE
没结果
失败
失败
SQL%NOTFOUND
FALSE
有结果
成功
失败
SQL%ROWCOUNT
返回行数。仅仅为1
插入的行数
改动的行数
删除的行数
例8: 通过隐式游标SQL的%ROWCOUNT属性来了解改动了多少行。
DECLARE
v_rows NUMBER;
BEGIN
–更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = ‘AD_VP’;
–获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(
DBMS_OUTPUT.PUT_LINE(‘更新了’||v_rows||’个雇员的工资’);
–回退更新，以便使数据库的数据保持原样
ROLLBACK;
END;
4.1.3 使用游标更新和删除数据
游标改动和删除操作是指在游标定位下，改动或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中相应数据行的全部列和部分列。
为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。
该需求迫使ORACLE锁定游标结果集合的行，能够防止其它事务处理更新或删除同样的行，直到您的事务处理提交或回退为止。
语法：
SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
假设使用 FOR UPDATE 声明游标。则可在DELETE和UPDATE 语句中使用
WHERE CURRENT OF cursor_name子句，改动或删除游标结果集合当前行相应的数据库表中的数据行。
例9：从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为 1500。
DECLARE
V_deptno employees.department_id
V_deptno employees.department_id%TYPE :=&p_deptno;
CURSOR emp_cursor
IS
SELECT employees.employee_id, employees.salary FROM employees WHERE employees.department_id=v_deptno FOR UPDATE NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.salary < 1500 THEN
UPDATE employees SET salary=1500 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
COMMIT;
END;
4.2 游标变量
与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。
但与游标不同的是。游标变量是动态的，而游标是静态的。游标仅仅能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连。它能够指向不同查询语句的内存处理区域（但不能同一时候指向多个内存处理区域，在某一时刻仅仅能与一个查询语句相连），仅仅要这些查询语句的返回类型兼容就可以。
4.2.1 声明游标变量
游标变量为一个指针，它属于參照类型，所以在声明游标变量类型之前必须先定义游标变量类型。
在PL/SQL中。能够在块、子程序和包的声明区域内定义游标变量类型。
语法格式为：
1.定义游标变量
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
2.打开游标变量
OPEN cursor_variable FOR dynamic_string
[USING bind_argument[,bind_argument]…]
3.循环提取数据
FETCH cursor_variable INTO {var1[,var2]…| record_variable};
EXIT WHEN cursor_variable%NOTFOUND
4.关闭游标变量
CLOSE cursor_variable;
例10：使用游标变量（没有RETURN子句）
DECLARE
–定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR;
–声明一个游标变量
c1 EMP_CURSOR_TYPE;
–声明两个记录变量
v_emp_record employees%ROWTYPE;
v_reg_record regions%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||’的雇佣日期是’||v_emp_record.hire_date);
END LOOP;
–将同一个游标变量相应到还有一个SELECT语句
OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
LOOP
FETCH c1 INTO v_reg_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'||v_reg_record.region_name);
END LOOP;
CLOSE c1;
END;
例11：使用游标变量（有RETURN子句）
DECLARE
–定义一个与employees表中的这几个列同样的记录数据类型
TYPE emp_record_type IS RECORD(
f_name employees.first_name
f_name employees.first_name%TYPE,
h_date employees.hire_date
h_date employees.hire_date%TYPE,
j_id employees.job_id
j_id employees.job_id%TYPE);
–声明一个该记录数据类型的记录变量
v_emp_record EMP_RECORD_TYPE;
–定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR
RETURN EMP_RECORD_TYPE;
–声明一个游标变量
c1 EMP_CURSOR_TYPE;
BEGIN
OPEN c1 FOR SELECT first_name, hire_date, job_id FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘雇员名称：’||v_emp_record.f_name||’ 雇佣日期：’||v_emp_record.h_date||’ 岗位：’||v_emp_record.j_id);
END LOOP;
CLOSE c1;
END;
