oracle

一、           Introduction

1.       Oracle的特点

Oracle   Database，又叫做Oracle      RDBMS，即oracle关系型数据管理系统。和mysql一样同属于甲骨文公司的关系数据库管理系统。它提供开放的、全面的、近乎完整的信息管理，是目前世界上流行的数管系统，可移植性好、使用方便、功能强大、使用机器环境广、效率高、可靠性好，使得它在数据库领域一直处于领先地位。Oracle数据库由一个oracle数据库和多个oracle实例组成，其中oracle数据库是硬盘上的文件，oracle实例是读取到内存中的镜像。开发人员通过操作实例来操作数据库。数据库和实例可以是1对1的关系，也可以是1对多的关系，如果是1对多的关系就是数据库的集群。

集群就是指多个内存的镜像，优点是1，load balance，负载均衡，将大量的访问量平均到集群中的多个实例上，缓解处理访问的压力；2，fail over，失败迁移，当某个实例在处理访问需求时，如果出现异常失败，则可以迁移到其他实例继续处理，提高了可靠性。也提高了访问的安全性，只要多个内存镜像中有一个可用，则可以访问数据库。Oracle的集群专业上称为RAC，real application  cluster。

2.       Oracle的高性能受益于oracle的体系结构。

数据库文件的组成，一个数据库文件中，以.dbf为后缀的文件，称为数据文件，是数据库的物理存储单位。表空间，是oracle对物理数据库上相关数据文件的逻辑映射。一个数据库在逻辑上被划分称一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间。数据库的数据存储于表空间，实际上是在一个或者多个数据文件中，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件。如果要删除这个数据文件，只能删除其属于的表空间才行。以.ctl为后缀的文件是控制文件。以.log为后缀的文件是日志文件。

操作数据库时，就是将数据库文件读到内存中，形成实例。实例通过操作系统的进程操作数据库文件，比如读和写的进程，还有很多其他进程。一个实例包括一个SGA，system global  area，系统的全局区和若干个PGA，proccess global  area，进程的全局去。每个pga对应一个客户端，也就是说客户端只能操作pga，不能操作sga。当客户端在pga的操作提交后，pga的操作就提交到sga，然后通过操作系统的进程提交到数据库。这种提交方式称为两阶段提交，由于客户端提交的pga统一提交到sga，再一次性提交到数据库，相比分别提交到数据库，就提高了效率。

客户端通过操作实例，来操作数据库。

3.       Oracle的用户

Oracle只有一个庞大的数据库，默认的名字是orcl，这个数据库可以有多个用户。每个用户下面有若个个表。比如scott用户，下面有4张表，SALGRADE（工资级别表），BONUS，DEPT（部门表），EMP（员工表）。

二、           Oracle数据库的安装

从官网下载oracle11g企业版，一路安装，在安装完成时，有一个口令管理页面，点击口令管理，接触scott和hr用户的锁定，并设置口令。安装完成。

在cmd中验证oracle是否安装成功。输入：sqlplus scott/tiger。空格后的是用户名和密码。出现版本信息则安装成功。exit，可以退出oracle数据库。如果使用的虚拟机安装的oracle数据库，则在本机连接虚拟机的oracle时，请输入命令：sqlplus scott/tiger@192.168.137.xxx:1521/orcl。@后面是虚拟机的ip地址，1521是连接orcal的端口号。

安装成功后，可以通过cmd命令查看一些oracle的服务的状态。Lsnrctl status 查看oracle监听器的状态。

三、           Sql和sqlplus命令的区别

在cmd命令行中，sql语句书写在sqlplus中，两者的区别是：

另外，orcale  10g中有isqlplus，用来描述表结构，编辑和执行sql语句。是将sql保存在文件中并将sql语句执行结果保存在文件中，在保存的文件中执行sql语句，也可以将文本文件装入sqlplus编辑窗口。通过本地地址可以访问isqlplus，如http://localhost:5560/isqlplus/  但是，从11g之后就没有这个isqlplus的服务了。

四、           Oracle的基本查询

(一)基础查询语句

录屏开始：spool  d:\基本查询.txt

录屏结束：spool  off

注意：录屏期间不要退出sql，有结束命令，才生成屏录的文本。

查询当前用户：show  user

当前用户下的表：select  *  from tab;

查看某张表的结构：desc  emp

清屏命令：windows下：host cls；linux下：host clear

(二)基本查询语句

1.       查询所有信息：select *  from  emp;

2.       查询表的行宽：show linesize

3.       设置表的行宽：set linesize  100

4.       设置每页的行数：set pagesize  24

5.       设置表的列宽：如果列的字符为字符串，col ename  for  a8；如果列的字符为数字，col sal  for  9。

6.       执行上一条sql语句：/

7.       按照列名查询：select empno,ename,job,hiredate from  emp;

8.       修改写错的sql语句

1)       使用c命令

使用c命令修改写错的sql语句，适用于cmdlineInterface。

比如：select ename

form emp;

第2行的form写错了。那么首先输入2，然后输入c /form/from，然后输入/，再次执行。

2)       使用ed命令

输入ed，回车出弹出一个编辑页面，在这个页面修改好后直接保存，关掉就可以了。然后在cmd命令中，输入/执行修改的命令。

9.       Sql支持运算：select empo,ename,sal,sal*12  from  emp;

查询是给列取别名：select empno  as  “员工号”, ename “姓名”,sal  月薪  from emp;

关于别名3种方法的区别：as  “员工号”和  “姓名”两者没有区别；这两种方式与没有双引号的方式的区别是：没有引号的方式在字符之间输入空格或者sql的关键字会表示特定的含义，可能会导致sql语句错误。

如：select empno  as  “员工号”, ename “姓名”,sal  月   薪 from emp;或者select empno  as  “员工号”, ename “姓名”,sal  月select薪  from emp;会报错的。而select empno  as  “员工号”, ename “姓select名”,sal 月薪  from emp;并没有错。

10.   过滤重复关键字：distinct。如：select distinct  deptno  from emp;注意：distinct作用与后面所有的列，如：select distinct  deptno,sal  from emp;

11.   连接符：||。

||的作用是合成字符串。Sql语句中用单引号括住的是字符串，sql查询的结果，不论是字符、数字还是日期，也是字符串。另外，在sql语句中，日期或者字符只能在单引号中出现。而双引号中表示的是列的别名。

在mysql中有连接函数concat，oracle中也可以使用concat()，如果要连接的内容不在某个表中，可以使用oracle提供的dual表。Dual表又叫伪表。如：select concat(‘ll’,’aa’)  from  dual;

如果使用||，如：select ‘hh’||’aaaa’  哈哈  from dual;如：select ename||’的薪水是’||sal  薪水  from emp;

(三)条件查询和排序

1.       where关键字表示按照某个或某些条件查询

select  * from  emp  where deptno=10;

在where查询条件中，字符和日期都要包含在单引号中，字符的大小写敏感，日期的格式敏感，日期的默认格式是DD-MON-RR。

如查询名字是tom的：select *  from  emp where  ename=’tom’;

如查询入职日期是17-11月-81，即81年11月17号的员工。select *  from  emp where hiredate=’17-11月-81’;为了满足开发需求，有时需要修改查询的日期的格式，修改方式是：首先可以，显示oracle的一些参数，select *  from  v$nls_parameters;为了便于查看可以设置下行宽，set linesize  200;其中的NLS_DATE_FORMAT，表示日期格式的参数。然后，修改方式是：alter session  set  NLS_DATE_FORMAT=’yyyy-mm-dd’;修改完毕后，可是测试：select  *  from emp  where  deptno=’1981-11-11’;。注意上述修改中alter  session，表示在本次会话中有效，如果退出这次sql查询，即exit后，则修改的内容恢复默认，如果不想只是这次会话有效，可是使用alter system，表示在系统运行期间有效。

2.       where查询可以使用的比较运算符

注意：在模糊查询中，可以使用ESCAPE标识符，选择’%’和’_’符号。%表示一个或零个的任意字符，_表示任意一个字符。

比如：

select  *  from emp  where  sal<=2000;
 
select  * from  emp  where sal  between  1000  and  2000;
 
select  * from  emp  where deptno  in(10,20);
 
select  * from  emp  wehre ename  like  ‘%张%’;

注意：如果查询名字中含有下划线_的名字，则取like ‘%_%’是查不到的，那么，就需要使用转义字符\，要使用escape ‘\’声明转义字符。所以，正确的语句是：select *  from  emp where  ename  like  ‘%\_%’  escape ‘\’;

3.       排序

使用关键字order  by。默认为升序。降序使用desc。order by后面可以跟列，表达式，别名，序号（列的序号）。

1)       按照某列排序，如：select *  from  emp order  by  sal;

2)       按照表达式排序，如：select *  from  emp order  by  sal*12;

3)       按照别名排序，如：select sal*12  年薪  from emp  order  by  年薪  desc;

4)       按照序号排序，如：select ename,sal,sal*12  from  emp order  by  3 desc;

order  by也可以给多个列排序。如：select *  from  emp order  by  deptno,sal;select  * from  emp  order by  deptno,sal  decs;

总结：order  by作用于后面所有的列，先按照第一个列排序，然后后面的列；desc只作用离他最近的列。

有时候，根据前端业务的需求，需要将排序的null的列放到查询结果的后面，特别是在desc的时候，null会出现在最前面，因为在oracle中nul最大。Oracle提供了处理这个问题的语法，如：select *  from  emp order  by  comm  nulls  last;

五、           函数

数学上，函数是一种定义域与值域在对应法则下的关系式。软件中的函数也是指对应法则的关系式，简单的说就是一个计算方法，如求最大值，最小值，平均数等。软件中，使用函数为了简化操作。Sql中根据值域的行数，分为单行函数和多行函数。

(一)单行函数

1.       字符函数

1)       大小写控制函数

LOWER\UPPER\INITCAP

select  lower(‘helSDF’),小写  upper(‘llhsksl’)  大写,initcap(‘aaa’)  首字母大写  fromdual;

2)       字符控制函数

CONCAT：连接字符串。select  cancat(‘hlkj’,’ljs连接’)  连接 from  dual;

SUBSTR：求字符串的子串。

Substr(a,b)：从a中的b位置开始取。select substr(‘ljlj sdf’,4)  子串  from dual;

Substr(a,b,c)：从a中的b位置开始取c位。select substr(‘ljlj ljsljdf’,4,4)  子串  from dual;

LENGTH/LENGTHB：求字符串的长度。

       Length：字符数。Select length(‘aaa aaa’)  字符,lengthb(‘aaaaaa’)  字节 from  dual;

       Lengthb：字节数。Select length(‘aaa 啊啊啊’)  字符,lengthb(‘aaa啊啊啊’)  字节  from dual;

INSTR：在一个字符串中查找另一个字符串。Select  instr(‘ljlj’,’l’)  from dual;

LPAD/RPAD：左填充/右填充。Select  lpad(‘abc’,10,’abcd’),rpad(‘abc’,12,’dlgc’)  from dual;

TRIM：去掉前后指定的字符。Select  trim(‘a’  from  ‘aljslkdjfa’)  from dual;

REPLACE：替换为。Select  replace(‘abdcdsg’,’a’,’*’)  from dual;

2.       数值函数

ROUND：四舍五入。Select  round(22.222,2),round(22.343,1),round(22.343,0),round(22.343,-1),round(22.343,-2)  from dual;

TRUNC：截断。Select  trunk(22.232,2), trunk(22.232,1),trunk(22.232,0), trunk(22.232,-1), trunk(22.232,-2)  from dual;

MOD：求余。Select  mod(1400,300)  from dual;

3.       日期函数

关键字：sysdate，当前时间。Select sysdate  from  dual;

额外介绍，关于格式转换函数，将某个转换为一定的格式：select to_char(sysdate,’yyyy-mm-dd  hh24:mi:ss’)  from dual;

日期的+/-运算：select (sysdate-1)  昨天,sysdate  今天,(sysdate+1)  明天 from  dual;

利用日期的减法运算计算工龄：

       Select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365  年 from  emp;

MONTHS_BETWEEN：两个日期相差的月数。

       Select ename,hiredate,(sysdate-hiredate)/30 估计值,months_between(sysdate,hiredate)  准确值 from  emp;

ADD_MONTHS：向指定日期中加上若干月数。

       Select add_months(sysdate,111)  fromdual;

NEXT_DAY：指定日期的下一个日期。

       Select next_day(sysdate,’星期四’) from  dual;

       Next_day应用于oracle数据库的异地容灾部署。比如每周五将数据备份到异地的数据库。这里涉及到分布式数据库和触发器、快照的知识。

LAST_DAY：本月的最后一天。

       Select last_day(sysdate)  from  dual;

ROUND：日期四舍五入。Select round(systdate,’month’)  fromdual;select  round(sysdate,’year’)  from dual;

TRUNC：日期截断。Select  trunc(sysdate,’month’)  from dual;select  trunc(sysdate,’year’)  from dual;

4.       转换函数

在不同的数值类型之间完成转换。包括隐式和显式两种。隐式数据类型是oralce数据库自动转换的。

显式类型转换函数：

1)       TO_CHAR：将对象按照格式进行转换。

日期的格式列表：

将日期转换为字符串：Select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss “今天是”day’) from  dual;

数字的格式列表：

查询员工的薪水：要求格式为两位小数、千位符、本地货币。Select to_char(sal,’L9,999.99’) from  emp;

2)       TO_NUMBER

将字符串转换成数字。To_number(char,’format_model’)

Select  to_number(‘￥234.234.23’,’L9,999.99’)  from dual;

3)       TO_DATE

将字符串转换成日期。To_date(char,’format_model’)

Select  to_date(‘1981-02-21’,’yyyy-mm-dd’)  from dual;

5.       通用函数

通用函数是适用与任何数据类型，也适用于空值的函数。

NVL：滤空函数。Select  nvl(comm,0)  from emp;

NVL2：带2的nvl是没有待nvl的增强。Nvl2(a,b,c)，当a=null的时候，返回c；否则返回b。select nvl2(comm,comm,0)  from  emp;

NULLIF：nullif(a,b)，判断参数是否相等，当a=b的时候，返回null；否则返回a。select nullif(‘ab’,’ab’),nullif(‘ab’,’ac’) from  dual;

COALESCE：coalesce(a,b,c,d,…,x)，从左往右找到第一个不为空的值。Select coalesce(comm,sal)  from  emp;

6.       条件表达式

根据条件的不同，执行不同的操作，就形成了条件表达式。比如根据员工的职位涨不同的工资，列出涨前与涨后的报表。

两种方法，

一种是sql99的语法，case表达式，相对繁琐。

Case  expr when  expr1  then expr11  when  expr2 then  expr21  when expr3  then  expr31 else  exprx  end;

Select  ename,job,sal 涨前,case  job  when  ‘PRESIDENT’  then sal+1000  when  ‘MANAGER’ then  sal+600  else sal+100  end  涨后 from  emp;

另一种是使用oracle自己的语法，DECODE函数。

Decode(cod|expression,search1,result1,search2,result2,…,default)

Select  ename,job,sal 涨前,decode(job,’PRESIDENT’,sal+10,’MANAGER’,sal+5,sal+1)  涨后 from  emp;

(二)多行函数

多行函数，又叫做组函数或者分组函数，就是作用域一组数据，并返回一组数据的函数。一般，组函数会忽略空值，nvl函数可以保持组函数不忽略空值。

1.       常用的多行函数。AVG;COUNT;MAX;MIN;SUM。

1)       求员工总人数：select count(*)  from  emp;

2)       求员工的平均工资：select sum(sal)/count(*),avg(sal) from  emp;

3)       当函数的参数中含有null值时，

比如求员工的平均奖金：select sum(comm)/count(*),sum(comm)/count(comm),avg(comm)  from emp;其中正确算法是sum(comm)/count(*)，其他两种修正后是：sum(comm)/count(nvl(comm,0)；avg(nvl(comm,0))。

4)       在函数中使用distinct关键字，将distince写到括号里面。如：select count(distinct  deptno)  from emp;

2.       不常用的多行函数。WM_CONCAT(column)

行转列。Var_concat(column)

比如：select  deptno,wm_concat(ename)  namelist from  emp  group by  deptno;

3.       分组函数

使用group  by表达式。

总结查询中sql表达式的顺序，在一条sql中可能出现where条件表达式，group by表达式，order  by表达式，这些式子有一定的顺序。

即：select column,funciont()  from  table where  condition  group gy  expression  order by  column;

1)       求每个部门的平均工资，人数。Select count(ename),avg(sal)  from  emp gourp  by  deptno;

注意：在select列表中所有未包含在组函数中的列都应该包含在group by字句中。但是包含在group  by字句中的列不必包含在select列表中。如：select a,b,c,func(x)  from  emp group  by  a,b,c;select a,b,c,func(x)  from  emp group  by  a,b,c,d,e;

2)       按照多个列分组。按照部门中不同的职位求总工资。Select deptno,job,sum(sal)  from  emp group  by  deptno,job order  by  deptno;

多个列分组，先按照第一列分，如果相同，再按照第二个列分，以此类推。

4.       过滤分组

1)       过滤分组，是过滤分组后的结果，使用having子句。Having子句放在group by之后，order  by之前。如下格式：

select  column,funciont()  from table  where  condition group  gy  expression having  group_condition  order by  column;

2)       求每个部门的平均工资大于2000的。Select deptno,avg(sal)  from  emp group  by  deptno having  avg(sal)>2000;

3)       Having子句有点类似where子句，它们的区别是：where子句后不能使用多行函数。

某些查询下，where和having可以通用。查询20号部门的平均工资：select avg(sal)  from  emp where  deptno=20;select  avg(sal) from  emp  group by  deptno  having deptno=20;二者相比，使用where子句的性能更高。

4)       Group  by子句的增强。

有如下3条select语句。

Select  deptno,sum(sal)  from emp  group  by deptno,job;
 
Select  deptno,sum(sal)  from emp  group  by deptno;
 
Select  sum(sal) from  emp;

使用group  by增强将上述3条sql语句加起来：

Select  deptno,job,sum(sal)  from emp  group  by rollup(deptno,job);

Group  by rollup(a,b)的作用相当于group by  a,b+group  by  a+没有group  by。

使用group  by的增强做出来的效果类似报表，也是说sqlplus支持报表查询。对于上述rollup语句的查询结果，使用break on  deptno  skip  2，可以使输出的格式更优化。其中，break  on表示相同的参数显示依次，skip  2，不同的deptno之间空2行。Break null，可以取消break  on的设置。

六、           多表查询

多表查询，是在多表中进行的查询。多表查询建立在笛卡尔积的基础上，通俗的说，笛卡尔积就是将两个表或多个表的所有行数相乘，列数相加得到一个新的表。然而，通过笛卡尔积得到的表并不能直接使用。多表查询以笛卡尔积表为基础，根据连接条件，从中筛选出需要的数据记录。多表查询的连接条件至少需要n-1个，n为表的个数。Oracle的多表查询有：等值连接，不等值连接，外连接和自连接4中类型。

(一)等值连接

凡是连接条件中间是=的，就是等值连接。如select *  from  emp  e,dept  d where  e.deptno=d.deptno;

(二)不等值连接

凡是连接条件中间不是=的，就是不等值连接，如select e.ename,e.sal,s.grade  from  emp  e,salgrade  s where  e.sal  between s.losal  and  s.hisal;

(三)外连接

举个例子：按部门统计员工信息，要求查出部门号、部门名称、人数。Select d.deptno  部门号,d.dname  部门名称,count(e.empno)  人数  from emp  e,dept  d where  e.deptno=d.deptno  group by  d.deptno,  d.dname;这个是使用等值连接，查询到的结果，这个结果是不对的，因为它不能显示出没有员工的部门的人数。所以，为了解决这个问题，要使用外连接。

外连接，意义是将某些连接条件下不成立的记录也包含在最后的结果中。外连接包含左连接和右连接。

左外连接，当连接条件where e.deptno=d.deptno不成立时，等号左边的表仍然包含在结果中。写法是：where e.deptno=d.deptno(+)

左外连接，当连接条件where e.deptno=d.deptno不成立时，等号右边的表仍然包含在结果中。写法是：where e.deptno(+)=d.deptno

使用外连接再次查询上述例子：Select d.deptno  部门号,d.dname  部门名称,count(e.empno)  人数  from  emp  e,dept d  where  e.deptno(+)=d.deptno  group by  d.deptno,  d.dname;

(四)自连接

自连接，就是本表连接本表。例子：查询员工姓名以及经理姓名。Select e.ename  员工,b.ename  经理 from  emp  e,emp b  where  e.mgr=b.empno;

通过上述例子可见，自连接可以认为是通过表的别名，将同一张表视为多张表。

自连接的笛卡尔积基础是表的行数的平方，如果表很大，那么，自连接的查询基础将相当大，所以，从效率的角度讲，自连接不适合做大表查询。

(五)层次查询

层次查询，可以解决自连接不适合做大表查询的问题。自连接的弊端在于多表查询依赖于笛卡尔积，而层次查询避免这个问题的关键之一就在于层次查询是单表查询。层次查询依据的是分叉树原理。

层次查询使用connect  by  …  start  with语法。同时使用oracle提供的伪列level，即表示层次的深度的列，如：select level,empno,ename,mgr  from  emp connect  by  prior empno=mgr  start  with mgr  is  null order  by  1;

层次查询也是一种优化的解决方案。然后，层次查询不能显示上述自连接那样直观的显示员工名和经理名的结果。

小结，每种查询都有优缺点，需要根据实际需求权衡使用。

七、           子查询

(一)子查询，就是以一个查询的结果为查询的范围的查询。

举例子如：查询工资比scott高的员工信息。首先需要查询到scott的工资是多少，通过select sal  from  emp where  ename=’SCOTT’;得到其工资3000，然后查询工资比3000高的工资，select *  from  emp where  sal>3000;这就需要两步来完成一个查询。Sql的查询规范允许将两步合为一条sql语句，就是子查询，语法是select list  from  table where  expr  (select list  from  table where  expr);。通过子查询实现上述查询：select  *  from emp  where  sal>(select  sal from  emp  where ename=’SCOTT’);。

子查询，也叫内查询，在主查询之前一次执行完成。子查询的结果被主查询使用。

(二)使用子查询时，请注意

1)、使用括号，2)、合理的书写格式（清晰的换行），3)、使用子查询的位置（where\having\select\from等后面），4)、特别注意不可以在group  by后面使用子查询，5)、主查询和子查询可以不是同一张表：只要子查询返回的结果，主查询可以使用即可，6)、一般不在子查询中排序，但在top-n分析中，必须对子查询排序，7)、注意from后面的子查询，8)、一般先执行子查询，再执行主查询，但相关子查询例外，9)、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符，10)、子查询中的null。

(三)分别在where\having\select\from后面使用子查询

1.       Where

查询工资比scott高的员工信息：select *  from  emp where  sal>(select  sal from  emp  where ename=’SCOTT’);

2.       Select

Select后面使用子查询必须是单行子查询，不能是多行子查询。因为oracle是关系型数据库，采用二维表也就是行和列的形式保存数据，如果在select后面是多行子查询，也就是返回多行结果，则这个多行结果需要与其他每列的一行存在于一行中，则是不可行的，所以不能使用多行子查询。

Select  empno,ename,sal,(select  job from  emp  where empno=7839)  子查询  from  emp;

3.       Having

查询最低工资的部门比10号部门高的部门号及其最低工资。

Select  deptno,min(sal)  from emp  group  by deptno  having  min(sal)>(select  min(sal) from  emp  where deptno=20);

4.       From

查询员工号、姓名、月薪：Select  *  from (select  empno,ename,sal  from emp);

(四)主查询和子查询可以不是同一张表，只要子查询的返回结果，主查询可以使用即可

查询部门名称是sales的员工。Select ename  from  emp where  deptno=(select  deptno from  dept  where dname=’SALES’);

上述需求可以使用多表查询得到同样的结果。Select e.*   from  emp  e,dept d  where  e.deptno=d.deptno  and d.dname=’SALES’;

(五)一般不对子查询排序，但是在top-n分析中，必须对子查询排序

Top-n就是查出符合条件的前n个。如果不对子查询排序，就没有意义求的前n个了。

(六)一般先执行子查询，再执行主查询，但相关子查询例外

(七)单行子查询只能使用单行操作符，多行子查询只能使用多行操作符

根据返回结果，可以分为单行子查询和多行子查询，返回一行结果是单行子查询，返回多行结果是多行子查询。

1.       单行子查询

单行操作符

例子如：

select ename,job,sal  from  emp where  job=(select  job from  emp  where empno=7566)  and  sal>(select  sal from  emp  where empno=7782);其中job后的=就是单行操作符，=后面的子查询只能是单行子查询。

错误使用单行子查询的例子：select empno,ename  from  emp where  sal=(select  min(sal) from  emp  group by  deptno);错误原因是单行子查询返回不止一行结果。

2.       多行子查询

多行操作符

1)       使用in：查询部门名称是SALES和ACCOUNTING的员工。

Select  * from  emp  where deptno  in(select  deptno from  dept  where dname=’SALES’  or  dname=’ACCOUNTING’);这个需求可以通过多表查询解决。Select  *  from emp  e,dept  d where  e.deptno=d.deptno  and (d.dname=’SALES’  or  d.dname=’ACCOUNTING’);

2)       使用any：查询工资比30号部门任意一个员工高的员工信息

Select  * from  emp  where sal>any(select  sal  from emp  where  deptno=30);

可以使用min()实现上述需求。Select *  from  emp where  sal>(select  min(sal) from  emp  where deptno=30);

3)       使用all：查询工资比30号部门所有员工都高的员工信息

Select  * from  emp  where sal>all(select  sal  from emp  where  deptno=30);

可以使用max()实现上述需求。Select *  from  emp where  sal>(select  max(sal) from  emp  where deptno=30);

4)       多行子查询中的null

查询不是老板的员工信息。Select  *  from emp  where  empno not  in(select  mgr from  emp);实际上有结果，但是查询的没有结果。因为not  in后面的集合中有null值，not  in的运算规则是并且运算，即如果a  not  in(10,20,null)运算的过程是a!=10 and  a!=20  and a!=null，因为只有null!=null才为真，其他一些非null!=null都是假的，所以，a not  in(10,20,null)是假的，所以，上述多行子查询本应该有结果但是由于这个原因而没有结果。通过过滤掉not in中集合的null可以修改这条sql查询。Select *  from  emp where  empno  not in(select  mgr  from emp  where  mgr is  not  null);

查询是老板的员工信息。Select  *  from emp  where  empno in(select  mgr  from emp);有正确的结果。同样的，in后面跟集合也有它的运算规则，即如果a in(10,20,null)的运算过程是a=10 or  a=20  or a=null，显然这个运算会有结果的。

(八)集合运算

集合元算，实际上就是数学上的交集、并集、差集运算。

集合运算的语法字。

1.       并集：Union/union all

Select  * from  emp  where deptno=10  union  select *  from  emp where  deptno=20;

2.       集合运算的语法

Select  deptno,job,sum(sal)  from emp  group  by  deptno,job  union select  deptno,sum(sal)  from emp  group  by deptno  union  select sum(sal)  from  emp;

这条集合语句是错误的。这就涉及到集合运算需要注意的问题：

1)       参与运算的各个集合必须列数相同且类型一致。

2)       采用第一个集合作为最后的表头。

3)       Order  by永远在最后

4)       使用括号改变顺序。

根据集合运算的语法，可将上述错误的sql改为：select deptno,job,sum(sal)  from  emp group  by  deptno,job union  select  deptno,to_char(null),sum(sal)  from emp  group  by deptno  union  select to_number(null),to_char(null),sum(sal) from  emp;

3.       交集

Intersect。查询薪水位于级别1（700-1300）和级别2（1201-1400）的员工的信息。

Select  ename,sal from  emp  where sal  between  700 and  1300  intersect select  ename,sal  from emp  where  sal between  1201  and 1400;

4.       差集

Minus。查询薪水同时位于级别1（700-1300），但不属于级别2（1201-1400）的员工信息。

Select  ename,sal from  emp  where sal  between  700 and  1300  minus select  ename,sal  from emp  where  sal  between 1201  and  1400;

八、           Sqlplus中打开sql执行的时间

当两条或者多条sql语句都可以完成某个需求时，可以通过打开sqlplus中的sql执行时间查看效率。语法是：set timing  on。

比如对于这两条sql语句：

Select  deptno,job,sum(sal)  from emp  group  by rollup(deptno,job);执行用时00.01s。

select  deptno,job,sum(sal)  from emp  group  by deptno,job  union  select deptno,to_char(null),sum(sal) from  emp  group by  deptno  union select to_number(null),to_char(null),sum(sal) from  emp;同一台电脑执行用时00.02s。

可见使用group  by增强用时更短，效率更高。

通过set  timing  off可以关闭sql执行时间的显示。

九、           Sql语句解决null值问题

1.       包含null的表达式都为null

select  empno,ename,sal,sal*12,comm,sal*12+comm  from emp;

比如某部门的员工有月薪和奖金两列，有的员工有奖金，有的没有奖金（奖金列为null），如果计算年薪时加上奖金，则奖金列为null的员工的年薪为null。这是不合理的。

可以使用oracle的虑空函数。nvl和nvl2。

nvl(a,b)，当a为null，返回b，否则返回a。

使用nvl后，select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)  from emp;

2.       null永远!=null

select  * from  emp  where  comm=null;

比如上述查询奖金为空的员工，是查不到的，因为null值永远不等于null。

如果判断等于null，请使用is。

select  * from  emp  where comm  is  null;

3.       如果集合中含有null，不能使用not in，但可以使用in。

比如：select  *  from emp  where  deptno not  in(10,20,null);这是查不到任何结果的。

但是select  *  from emp  where  deptno in(10,20,null);

4.       null的排序

有时候，根据前端业务的需求，需要将排序的null的列放到查询结果的后面，特别是在desc的时候，null会出现在最前面，因为在oracle中nul最大。Oracle提供了处理这个问题的语法，如：select *  from  emp order  by  comm  nulls  last;

十、           sql的执行计划

查询sql执行计划的语法：explain plan  for  sqlsentence。

比如：解释计划，explain plan  for  select  * from  emp  where deptno=10;查看计划，select *  from  table(dbms_xplan.display);

十一、   sql的数据处理操作

按照操作的类型，sql语言包括：DML，

(一)DML，data  manipulation laguage  数据库操作语言，select insert  update  delete。

1.       Select

见前述。

2.       Insert

语法1：insert into  table(column,column,…)  values (value,value,…);

比如：insert  into  emp(empno,ename,sal)  values(100,’LKL’,200);

语法2：使用地址符&。Insert into  table(column,column,…)  values (&column,&column,…);

比如：insert into  emp(empno,ename,sal)  values(&empno,&ename,&sal);当执行后会需要依次输入相关列的值。

地址符&的用法不局限与insert，其他关键字中也可以使用，比如：select ename,sal,&t  from  emp;执行后，会要求输入列的值。

地址符&也可以用于表名。如：select *  from  &t;执行后，会要求输入表的值。

语法3：一次插入多条记录。

使用create  table  emp5 as  select  * from  emp  where 1=2;

需求：一次性将emp中，所有10号部门的员工插入emp5中。

可以在insert语句中使用子查询。语法：insert into  table(column,column,…)  select subtable  ….;或者insert  into table  select…;

上述需求的语句：insert into  emp5  select *  from  emp where  deptno=10;

如果需要海量插入数据，可以使用以下方法

1)       Oracle提供了数据泵（PLSQL程序）

Dbms_datapump（程序包）

2)       SQL*loader

3)       外部表

3.       Update

全部修改：update table  set  column=value,column=value,column=value,…。

局部修改：update table  set  column=value,column=value,…where…。

Update中也可以使用子查询。

Update  emp set  job=(select  job from  emp  where empno=7782),sal=(select  sal  from emp  where  empno=7782) where  empno=7934;

更新中可能会发生数据的完整性错误。数据的完整性指的是创建在表上的约束。

4.       Delete

语法：delete from  table  where  …。

在删除中如果不指定删除条件的话就会删除所有的数据。

Truncate  table删除数据。

Truncate和delete删除的区别：

1),delete是dml语句，truncate是ddl语句。Dml可以rollback，ddl不可以rollback。delete删除的数据可以rollback；

2),delete删除可能产生碎片，碎片会影响查询的速度，truncate不会产生碎片；

去掉碎片的方法：alter table  表名  move;数据的导入和导出，导出：exp/expdp，导入：imp/impdp。

3),truncate是先摧毁表结构，再重构表结构。

4),delete不释放空间，truncate会释放空间。

5),delete可以闪回（flashback），truncate不可以闪回。比如使用delete删除的数据提交了，不能rollback了，oracle中几乎所有的操作都是可逆的。

6),delete的删除速度比truncate快。原因是oracle的非常重要的功能undo数据（还原数据）。

特别注意：插入、更新和删除会引起数据的变化，必须考虑数据的完整性。

Set  feedback off：关闭回显。

5.       事务

1)       事务的概念略。Oracle中事务的提交是：commit，回滚是：rollback。

2)       Oracle中事务的起始的标识是第一条dml语句。结束的标识是：提交，显式：commit；隐式：正常退出，ddl语句或者dcl语句。回滚的标识，显式：rollback，隐式：非正常退出，掉电，宕机。

3)       Oracle的保存点：使用savepoint。

比如：

create  table testsavepoint(id  number,name  varchar(20));
 
insert  into testsavepoint  values(1,’haha’);
 
insert  into testsavepoint  values(2,’hlkj’);
 
savepoint  a;
 
insert  into testsavepoint  values(3,’lkjl’);
 
rollback  to savepoint  a;

4)       事务的隔离级别

在oracle中只支持3种隔离级别，即READ COMMITED、SERIALIZABLE和READ ONLY。默认的隔离级别是READ  COMMITED。

设置隔离级别的语法：set transaction  read  only;设置隔离级别前需要先提交到没有提交的语句。

(二)DDL，data  definition language  数据定义语言，create  table，alter  table，truncate table，drop  table。Create\drop，view，sequence，index，synonyn(同义词)。

(三)DCL，data  control language  数据控制语言，grant（授权），revoke（撤销权限）

十二、   Sql优化的原则

1.       尽量使用列名，而不是*。因为如果是*，则数据库需要根据表来查询*号代表的是哪些列，这降低了效率。

2.       where语句解析顺序，从右往左。

如果是and连接时，根据从右往左判断的原则，尽量把可能为假的放在右边，当右边为假则省略往左的判断；如果是or连接时，同样根据这个原则，尽量把可能为真的放在右边，如果为真则省略往做的判断。

3.       如果where语句和having语句都可以使用的话，尽量使用where。

在sql语句中，where子句需要写在group by之前，having子句需要写在group by之后，这使得：Having是在分组的基础上过滤分组，它的逻辑是先分组再过滤；where语句正好相反，是先过滤再分组。假如数据库的数据很庞大，比如100万条，此时查询部门号为10的平均工资，如果先分组则需要先将这100万条数据分组，然后过滤出部门为10的；如果先过滤出部门为10的，则不需要对100万条进行分组，二则都是要基本相同的过滤工作的，但是分组的工作却因为过滤的前后执行有所不同。显然使用where先过滤，有助于提高性能。

4.       子查询和多表查询都可以使用的时候，尽量使用多表查询。

5.       尽量不要使用集合运算，因为集合运算会随着参与集合运算的集合变多而运算变多。

十三、   Sql练习题

1.       查询表中工资最高的前三名

获取前n名，需要使用到oracle里面的rownum伪列。Select rownum,ename,sal  from  emp where  rownum<=3  order by  sal;这条sql是错误的。

注意rownum的语法规则：1),rownum永远按照默认的顺序生成。就是这个表原来的顺序，即使排序后也是按照这个顺序。2),rownum只能使用<和<=，不能使用>和>=。

这是一个top-n分析的需求。首先需要对薪水进行排序，select *  from  emp order  by  sal desc;然后，将这个排好序的表放到from后面，作为排序的表。如：select  rownum,empno,ename,sal  from(select *  from  emp order  by  sal desc)  where  rownum<=3;

关于rownum只能使用<和<=，不能使用>和>=的例子，分页查询。比如查询每页4条，第3页就是行号从9到12，select rownum,empno,ename,sal  from  emp where  rownum>=9  and rownum<=12;因为oracle是行式数据库，取了第1行才能取第2行，数据库永远从1开始。所以，对于行号>或者>=某个数的永远不成立，所以查不到结果。那么，oracle的分页的方法是：select *  from  (select rownum  r,e1.*  from (select  *  from emp  order  by sal)  e1  where rownum<=8)  where  r>=5;实际上相当于，将最里面的行用外面的表套嵌住，从而使得伪劣称为套嵌表的实际列，然后就可以使用>或者>=来运算行。

Oracle中的表分为标准表、临时表和索引表。

关于临时表，有两种创建方式：1，create global  temporary  table temp;2，自动创建，比如排序的时候，这个时候就产生了行号的问题，当某个表排好序后就产生一个临时表，如果单独的只有一表，我们看到的是临时表的结果，然后临时表使用的行号依然是原表的行号而不是从1开始新排的，如果我们需要使用排好序的表，但是行号有不符合要求，那么，就可以套嵌一层表包裹住临时表，并显示行，这个时候就从1开始重新排行号。

临时表的作用是：当事务或者会话结束的时候，表中的数据自动删除。比如：

当事务结束的时候，会删除表的例子。

Create  global temporary  table  temp1(id number,name  varchar(20));创建完成后oracle自动开启事务。
 
Insert  into temp1  values(1,’haha’);
 
Insert  into temp1  values(1,’sbsd’);
 
Select  * from  temp1;
 
Commit;
 
Select  * from  temp1;

执行到这里，发现数据没有了。

2.       找到员工表中薪水大于本部门平均薪水的员工

Select  e.empno,e.ename,e.sal,t.avgsal  from  emp e,(select  deptno,avg(sal)  avgsal  from emp  group  by  deptno)  t  where e.sal>t.avgsal and  e.deptno=t.deptno;

这个查询，也可以使用相关子查询。

相关子查询，将主查询中的值作为参数传递给子查询。使用相关子查询的方法：select empno,ename,sal,(select avg(sal)  from  emp where  deptno=e.deptno)  avgsal from  emp  e where  sal>(select  avg(sal) from  emp  where deptno=e.deptno);

3.       统计每年入职的员工人数

不使用子查询的解答：

Select  count(*) total,sum(decode(to_char(hiredate,’yyyy’),’1980’,1,0))  “1980”, sum(decode(to_char(hiredate,’yyyy’),’1981’,1,0))  “1981”, sum(decode(to_char(hiredate,’yyyy’),’1982’,1,0))   “1982”,  sum(decode(to_char(hiredate,’yyyy’),’1987’,1,0))  “1987” from  emp;

使用子查询的解答：

Select  (select count(*)  from  emp)  total,(select  count(*) from  emp  where to_char(hiredate,’yyyy’)=1980)  “1980”,(select  count(*)  from emp  where  to_char(hiredate,’yyyy’)=1981)  “1981”, (select  count(*) from  emp  where to_char(hiredate,’yyyy’)=1982)  “1982”,(select  count(*)  from emp  where  to_char(hiredate,’yyyy’)=1987)  “1987”  from  dual;

十四、   使用DDL语句管理表

DDL语句是管理数据库的对象的语言。常用的数据库对象有：表、视图、序列、索引、同义词、存储过程、存储函数、触发器、包和包体。

(一)表

表是一种比较重要的数据库对象。

表空间，是Oracle数据库的逻辑单元。表空间由多个数据文件组成。表空间中可以创建表。表由系统分配存放在数据文件上。表和其他的数据对象都是存储在用户下的，这也是oracle数据库与其他数据库的区别。

创建表空间的语法：create tablespace  haha  datafile ‘d:\space1.dbf’  size  100m autoextend  on  next 10m;datafile指表空间对应的数据文件，size定义表空间的初始大小，autoextend on  自动增长，当表空间存储都占满时，自增，next指定一次自动增长的大小。创建表空间需要权限。

创建用户：create user  aa  identified by  bb  default tablespace  space1;identified  by  后边是用户的密码，default tablespace  后边是表空间。

1.       创建表

1)       语法：create table  [schema,]table  (column datatype  [default  expr],column datatype  [default  expr],…);default表示默认值。

例子：create table  test1  (id number,name  varchar(20));

2)       创建表的时候可以指定的列的类型。

关于rowid。Rowid是伪列，比如：select rowid,empno,sal  from  emp;通过行地址是可以找到对应的记录的。比如：select *  from  emp where  rowid=’xxx’;

3)       使用子查询创建表的语法：create table  tt(column,column,…)  as subquery;

create  table emp10  as  select *  from  emp where  1=2;上述的例子，条件为假，那么就只是复制了表结构，不复制表数据。

Create  table empinfo  as  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname  from  emp e,dept  d  where e.deptno=d.deptno;

2.       修改表

1)       增减新列

Alter  table test1  add  photo blob;

2)       修改列

Alter table  test1  modify name  varchar2(40);

3)       删除列

Alter table  test1  drop column  photo;

4)       重命名列

Alter table  test1  rename column  name  to usern;

5)       重命名表

Rename  test1 to  test2;

3.       删除表

1)       语法：Drop table  t;

查询当前用户已经存在的表。Select  *  from tab;

Drop并没有真正的将表删除掉，而是放到数据库的回收站了。查看回收站show recyclebin;清空回收站purge recyclebin;

Purge即不经过回收站清除表，如drop  table  t purge;

可以通过回收站中的被删除的表的名字，将原来表中的数据查询出。查询的表的名字加上双引号，如：select *  from  “BIN$ZErsyYZkTWGd/vBsdk30fg==$0”;

注意：使用回收站时，要知道，在oracle中管理员没有回收站。回收站只针对普通用户。

Oracle获取管理员的命令：sqlplus sys/1234  as  sysdba;sys是数据库名，1234是密码。这个用户名和密码可以是任意的。提供正确的用户名和密码（就是安装oracle时设置的密码）叫做密码认证。没有提供正确的用户名和密码的方式叫做主机认证，或者外部认证。主机认证的方式是通过设置计算机的本地用户组中的ORA_DBA的用户来修改。

Show  user，显示当前用户。

2)       使用闪回可以将oracle回收站中的数据恢复。

10g中闪回操作有6种，11g中闪回操作共有7种。

闪回删除。语法：flashback table  t  to before  drop;

闪回数据归档。语法：

闪回表。

闪回查询。

闪回版本查询。

闪回事务查询。

闪回数据库。

4.       约束

Oracle中约束的级别有两种，一个是列级约束，一个是表级约束。一般是指列级约束。表级约束的例子比如联合组件。常用的列级约束有主键约束（primary key），非空约束（not  null），唯一约束（unique），外键约束（foreign  key），检查性约束（check）。

1)       检查性约束

即检查约束的的列只能是某些情况，如性别只能是男或者女，使用英文的话，只能是male或者female。比如：create table  test2(id  number,name varchar2(20),gender  varchar2(2)  check  (gender  in(‘男’,’女’)),sal number  check(sal>0));插入数据检验：insert  into  test2 values(1,’haha’,’来’,-1);

2)       主键约束

主键约束在id上使用，本身默认不为空，在建表的时候指定。

如：create table  p(id  number(10) primary  key,name  varchar2(20)); 

注意：主键不可以重复，是系统自动分配的约束的名字。而且通过主键查询数据最快，因为主键是一个唯一性索引。

3)       非空约束

使用非空约束，可以指定字段不可以为空。

Create  table p2(id  number(10)  not null,name  varchar2(20)  not null);

4)       唯一约束

表中的一个字段的内容是唯一的。

Create  table p3(id number(10),name varchar(20)  unique);

5)       外键约束

外键是两张表的约束，可以保证关联数据的完整性。

Foreign  key：在子表中，定义了一个表级的约束。

References：指定表和父表中的列。

On  delete cascade：当删除父表时，级联删除子表记录。

On  delete set  null：将子表的相关依赖记录的外键置为null。

Create  table order2(id  number(10)  primary key,price  number(10,2));

Create  table order_detail(did  number(10),name  varchar(20),foreign  key  (did)  references order2(id));

注意：主表的外键必须是副表的主键。

创建约束的时候使用关键字：constraint emp_sal_min  …。Constraint的中文意思即约束。使用constraint创建约束后为约束定义名称，比如：create table  p4(id  number(10),gender  varchar2(2) check  (gender  in(‘男’,’女’)),sal number(10,2),constraint  p4_sal_min  check(sal>0));使用insert  into p4  (1,’非’,-11);测试使用约束关键字命名的效果。

使用所有约束的例子：

Create  table stu(sid  number  constraint stu_pk  primary  key,sname varchar2(20)  constraint  stu_name_notnull  not null,gender  varchar2(2)  constraint stu_gender  check(gender  in(‘男’,’女’)),email varchar2(40)  constraint  stu_email_uni unique  constraint  stu_email_notnull  not null,deptno  number  constraint stu_fk  references  dept(deptno) on  delete  set null);

使用insert插入数据检验：insert into  stu  values(1,’hah’,’男’,’hah@hah.com’,10);insert  into stu  values(2,’mil’, ’男’,’hah@hah.com’,10);

(二)视图

1)       视图就是封装了一条复杂查询的语句。视图是一个虚表。最大的优点就是简化复杂的查询。

语法：create  [or  replace] [force[noforce]  view  v [(alias,alias,…)]  as  subquery [with  check  option[constraint  cons]] [with  read  only[constraint  cons]];with read  only表示只读。

With  check option，表示操作视图中有的表的数据，而不能操作视图中没有的表的数。比如：create view  view10  as select  *  from emp  where  deptno=10 with  check  option;对于insert into  view10  (empno,deptno)  values(22,10);是正确的，但是insert into  view10  (empno,deptno)  values(23,20);是错误的。

创建使用视图需要管理员，即sys管理员授权。语句是：grant create  view  to scott;

比如：create  view  empinfoview as  select  e.empno,e.ename,e.sal,e.sal*12  annsal,d.dname  from emp  e,dept  d where  e.deptno=d.deptno;

创建视图后，查看视图：desc  empinfoview;查看视图中的数据：select *  from  empinfoview;

2)       不建议通过视图对表中的数据进行修改，因为会受到很多限制。

这些限制有：当视图定义中包含组函数、group by、distinct、rowmun时不能使用delete；当视图定义中包含组函数、group by、distinct、rowmun、列的定义为表达式时不能使用update；当视图定义中包含组函数、group by、distinct、rowmun、列的定义为表达式、表中非空的列在视图定义中未包括时不能使用insert；所以，尽量不要使用视图做这些修改操作。可以使用with read  only屏蔽对视图的dml操作。

3)       删除视图

删除视图只能删除视图的定义，并不会删除基表的数据。Drop view  v1;

(三)序列

序列可供多个用户用来产生唯一数值的数据库对象。作用：自动提供唯一的数值，共享对象，主要用于提供主键值，将序列值装入内存可以提高访问效率。效率方面，因为序列实际是一个数组，而且是存放在内存中的，访问内存比访问硬盘要快，所以效率高。

在很多数据库中都存在一个自动增长的列，比如mysql中的auto_increment，如果现在要想在oracle中完成自动增长的功能，则只能依靠序列完成，需要用户手工完成处理。并且oracle将序列值装入内存可以提高访问效率。

语法：create sequence  sequence  [increment by  n]  [start with  n]  [{maxvlues n|nomaxvalue}] [{cycle|nocyle}]  [{cache   n|nocache}];

比如：create sequence  dept_id  increment by  2  start with  122  maxvalue 300  nocache  nocycle;

创建一张表测试序列。Create table  testseq(id  number,name varchar(20));

序列提供了一下两个操作：nextval，取得序列的下一个内容；currval，取得序列的当前内容。在插入数据时需要自增的主键可以这样使用：insert into  testseq  values(dept_id.nextval,’哈哈’);

序列可能产生裂缝（断续的序号）的原因：回滚，系统异常，多个表共用一个序列。系统异常，比如停电，因为序列在内存中，如果停电，序列就没了。

(四)索引

索引是用于加速数据存取的数据对象。类似于书的目录。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引分为单列索引和多列索引。索引表中放的是rowid，行地址。

1.       单列索引

单列索引就是基于单个列建立的索引。语法：create index  in1  on table1(column1,…);

比如：create index  myindex  on  emp(deptno);

2.       复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同。比如：create index  myindex2  on emp(deptno,job); create index  myindex2  on emp(job,deptno);

适合创建索引的情况：列中数据值分布范围很广，列经常在where子句或连接条件中出现，表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%。

不适合创建索引的情况：表很小，列不经常在where子句或连接条件中出现，查询的数据大于 2%到4%，表经常更新。

(五)同义词

同义词就是别名的意思。

语法：Create [public]  synonym  synonym for  object;使用public，表示这个同义词是共有同义词，就是所有人都可以用的。

比如：作为scott用户是不能直接就访问hr用户里面的表的，但是sys授权后就可以了。语法：grant select  on  hr.employees to  scott;

授权之后，scott就可以访问hr的表了。Select count(*)  from  hr.employees;那么，给hr.employees起的别名叫做同义词。Create synonym  hrem  for hr.employees;同样创建同义词的时候需要管理员授权，语法：grant create  synonym  to scott;授权成功后就可以创建同义词。同义词的意义就是用户透明，提高用户数据的安全性。

十五、   PL/SQL编程语言

(一)简介

Procedure  language/sql，plsql是oracle对sql语言的过程化扩展，指在sql命令语言中增加了过程处理语句（如分支、循环等），是sql语言具有过程处理能力。把sql语言的数据操作能力与过程语言的数据处理能力结合起来，使得plsql面向过程但比过程语言简单、高效、灵活和实用。

举例说明：根据职位给员工涨工资。

如果使用jdbc操作：

ResultSet  rs=”select empno,job  from  emp”;
 
While(rs.next()){
 
       Int eno=rs.getInt(“empno”);
 
       String job=rs.getString(“job”);
 
       If(“PRESIDENT”.equals(job){
 
              Update  emp set  sal=sal+1000  where empno=eno;
 
       }else if(“MANAGER”.equals(job){
 
              Update  emp set  sal=sal+500  where empno=eno;
 
       }else{
 
              Update  emp set  sal=sal+100  where empno=eno;
 
       }
 
}

但是我们要使用plsql来完成这些使用sql难以完成的操作，因为plsql是对sql的扩展形成的程序，它比java要快，效率要高；plsql关系到其他数据库对象存储过程等。

Plsql例子：

Declare
 
       --说明部分
 
Begin
 
       --程序主体
 
       Dbms_output.put_line(‘hhhll’);
 
End;
 
/

默认情况下oracle的输出开关是关闭的，打开语法是：set serveroutput  on。

在命令行可以查看plsql中的程序包的简介。语法：desc dbms_output。另外也可以从oracle官网下载相关的学习资料。

为了方便和生产，需要使用plsql  developer之类的ide进行plsql的开发操作。

请安装pl/sql develeper。

(二)语法

1.       语法结构

Declare
 
       --说明部分（变量说明，光标申明，例外说明）
 
Begin
 
       --语句序列（dml语句）
 
Exception
 
       --例外处理语句
 
End;
 
/

2.       常量和变量的定义

变量类型：char、varchar2、date、number、boolean、long。

比如：var1 char(10);married  Boolean:=true;psal  number(7,2);

注意：plsql中的:=表示赋值，相当于java中的=，plsql中的=表示相等，相当于java中的==。

另外还以使用引用型变量和记录型变量。

引用型变量例子：my_name emp.ename%type;表示my_name的类型与emp表中ename列的类型一样。

变量的赋值可以通过:=，也是通过select 列名或表达式  into  变量。

Declare
 
       --定义变量保存姓名和薪水
 
       Pname emp.ename%type;
 
       Psal emp.sal%type;
 
Begin
 
       Select ename,sal  into  pname,psal from  emp  where empno=7839;
 
       --注意into表示赋值，后面变量顺序与前面顺序一致。
 
       Dbms_output.put_line(pname||’的薪水是’||psal);
 
End;

记录型变量例子：emp_rec emp%rowtype;rowtype表示得到一行。可以通过变量名.列名，引用行中的列。如：emp_rec.ename。

Declare
 
       --定义记录型变量
 
       Emp_rec emp%rowtype;
 
Begin
 
       Select *  into  emp_rec from  emp  where empno=7839;
 
       Dbms_output.put_line(emp_rec.ename||’的薪水是’||emp_rec.sal);
 
End;

3.       If语句

语法：

1)        

If  条件 then  语句1;

语句2;

End  if;

2)        

       If 条件  then  语句序列1;

       Else 语句序列2;

       End if;

3)        

If  条件  then  语句;

Elsif  语句  then  语句;

Else  语句;

End  if;

例子：判断用户从键盘输的数字。

--接收键盘输入
 
--变量num：是一个地址值，在该地址上保存了输入的变量的值
 
Accept  num prompt  ‘请输入一个数字’;
 
Declare
 
       --定义变量的保存输入的数字
 
       Pnum number  :=#
 
Begin
 
       If pmun  =  0 then  dbms_output.put_line(‘输入的是0’);
 
              Elsif  pmun  =  1 then  dbms_output.put_line(‘输入的是1’);
 
              Elsif  pmun =  2  then dbms_output.put_line(‘输入的是2’);
 
              Else  dbms_output.put_line(‘其他’);
 
       End if;
 
End;

4.       循环

语法：

1)        

While total<=2500
 
Loop
 
…
 
Total:=total+sal;
 
End  loop;

2)        

Loop
 
Exit[when  条件];
 
…
 
End  loop;

3)        

For  I  in 1..3
 
Loop
 
语句序列;
 
End  loop;

其中1..3表示从1到3，一共3次。

例子：打印输出1到10。

Declare
 
       Pnum number:=1;
 
Begin
 
       Loop
 
              --退出条件
 
              Exit  when pnum>10;
 
              Dbms_output.put_line(pnum);
 
              Pnum:=pnum+1;
 
       End loop;
 
End;

(三)光标

也叫游标，可以存储查询返回的多条数据。

语法：cursor  游标名[(参数名  数据类型,参数名  数据类型,…)]  is  select …;

例如：cursor  c1  is select  ename  from emp;

光标的属性：1，%isopen 是否打开；2，%rowcount 影响的行数；3，%found 是否取到值；4，%found 是否没有取到值。

游标的使用步骤：1，打开游标：open c1;2，取一行游标的值：fetch  c1  into pjob;3，关闭游标：close  c1;4，游标的结束方式：exit  when  c1%notfound。注意：获取游标的值时，存放值的标量如pjob的数据类型必须和job的类型一致。如：pjob emp.empjob%type;

例子：查询和打印员工的姓名和薪水。

Declare
 
       --定义光标
 
       Cursor cemp  is  select ename,sal  from  emp;
 
       Pname emp.ename%type;
 
       Psal emp.sal%type;
 
Begin
 
       Open cemp;
 
       Loop
 
              Fetch  cemp into  pname,psal;
 
              Exit  when  cemp%notfound;
 
              Dbms_output.put_line(pname||’的薪水是’||psal);
 
       End loop;
 
       Close cemp;
 
End;

综合使用if，循环解决根据职位给员工涨工资的问题。

Declare
 
       Cursor cemp  is  select empno,job  from  emp;
 
       Pempno emp.empno%type;
 
       Pjob  emp.job%type;
 
Begin
 
       Open cemp;
 
              Loop
 
                     Fetch  cemp into  pempno,pjob;
 
                     Exit  when cemp%notfound;
 
                     If  pjob= ‘PRESIDENT’  then update  emp  set sal=sal+1000  where  empno=pempno;
 
                     Elsif  pjob=’MANAGER’  then update  emp  set sal=sal+500  where empno=pempno;
 
                     Else  update emp  set  sal=sal+100 where  empno=pempno;
 
                     End  if;
 
              End  loop;
 
       Close cemp;
 
       Commit;
 
       Dbms_output.put_line(‘完成’);
 
End;

Orcale的默认隔离级别是read  committed。读提交，表示如果当下的事务不提交，另外的事务是不能读取到当下事务的结果的。所以，这里要提交后才能查询到涨后的结果。上面的事务要提交。

关于使用光标的参数的例子。

查询某个部门的员工姓名。

Declare
 
       --形参
 
       Cursor  cemp(dno number)  is  select ename  from  emp where  deptno=dno;
 
       Pname emp.ename%type;
 
Begin
 
       --实参
 
       Open cemp(10);
 
       Loop
 
              Fetch  cemp into  pename;
 
              Exit  when cemp%notfound;
 
      
 
              Dbms_output.put_line(pname);
 
 
 
       End loop;
 
       Close cemp;
 
End;

(四)例外

例外是程序提供的一种功能，用来增强程序的健壮性和容错性。

1.       系统定义的异常有如下：

注意：timeout_on_resource这个异常比较典型的例子就是在分布式数据库中。分布式数据库数据物理上被存放在网络的多个节点上，逻辑上是一个整体。

例子：被0除的异常。

Declare
 
       Pnum number;
 
Begin
 
       Pnum:=1/0;
 
Exception
 
       When zero_divide  then  dbms_output.put_line(‘1，0不能做分母’);
 
                                                        Dbms_output.put_line(‘2，0不能做分母’);
 
       When value_error  then  dbms_output.put_line(‘算数或者转换错误’);
 
       When others  then  dbms_output.put_line(‘其他例外’);
 
End;

2.       用户也可以自定义异常，在声明中来定义异常。以变量的形式定义异常。

语法：

Delare
 
       My_job char(10);
 
       V_sal emp.sal%type;
 
       No_data exception;
 
       Cursor c1  is  select distinct  job  from emp  order  by job;

如果遇到异常就抛出raise  no_data;raise相当与java中的throw，抛出异常。

例子：查询50号部门的员工。

显然，没有50号这个部门，所有查询的结果是没有的。

Declare
 
       Cursor cemp  is  select ename  from  emp where  deptno=50;
 
       Pname emp.ename%type;
 
       No_emp_found  exception;
 
Begin
 
       Open cemp;
 
       Fetch cemp  into  pname;
 
       If cemp%notfound  then     Raise no_emp_found;
 
       End if;
 
       Close cemp;
 
Exception
 
       When no_emp_found  then  dbms_output.put_line(‘没有找到员工’);
 
       When others  then  dbms_output.put_line(‘其他例外’);
 
End;

(五)实例

解决问题的基本思路：需求分析>>概要设计、详细设计>>编码>>测试>>上线>>维护。通常称这个模型为瀑布模型。

1.       统计每年入职的员工人数

思路：需要什么sql语句？需要哪些变量，变量的初始值，变量最终的值如何得到？

Sql：所有员工入职的年份，select to_char(hiredate,’yyyy’) from  emp。

那么，得到一个集合，这就需要光标，然后就需要循环，退出条件是notfound。

变量：count80 number:=0;然后在循环中判断光标的值。其他年份类似。

解答：

Declare
 
       Cursor cemp  is  select to_char(hiredate,’yyyy’) from  emp;
 
       Phiredate varchar2(4);
 
       Count80 number:=0;
 
       Count81 number:=0;
 
       Count82 number:=0;
 
       Count87 number:=0;
 
Begin
 
       Open cemp;
 
       Loop
 
              Fetch  cemp into  phiredate;
 
              Exit  when cemp%notfound;
 
              If phiredate=’1980’  then  count80:=count80+1;
 
              Elsif  phiredate=’1981’  then count81:=count81+1;
 
              Elsif  phiredate=’1982’  then count82:=count82+1;
 
              Else  count87:=count87+1;
 
              End  if;
 
       End loop;
 
       Close cemp;
 
       Dbms_output.put_line(‘total:’||(count80+count81+count82+count87));
 
       Dbms_output.put_line(‘1980:’||count80);
 
       Dbms_output.put_line(‘1981:’||count81);
 
       Dbms_output.put_line(‘1982:’||count82);
 
       Dbms_output.put_line(‘1987:’||count87);
 
End;

2.       为员工涨工资。从最低工资调起没人涨10%，但是工资总额不能超过5万员，请计算涨工资的人数和涨工资后的工资总额，并输出涨工资人数及工资总额。

Sql:select  empno,sal from  order  by sal;

光标，循环，退出：总额>5万或者涨完了。

变量：涨工资的人数：countemp number:=0;涨后的工资总额：saltotal number:=两种方法（1）select sum(sal)  into  salTotal from  emp;（2）涨后=涨前+sal*0.1。在对比多个方法中，哪个方法好的时候，遵循的原则就是效率和安全，在这里，显然不访问数据库是更高效和安全的，所以（2）方法更好。

解答：

Declare
 
       Cursor cemp  is  select empno,sal  from  emp order  by  sal;
 
       Pempno emp.empno%type;
 
       Psal emp.sal%type;
 
       Countemp number:=0;
 
       Saltotal number;
 
Begin
 
       Select sum(sal)  into  saltotal from  emp;
 
       Open cemp;
 
       Loop
 
              Exit  when saltotal>50000;
 
              Fetch  cemp  into pempno,psal;
 
              Exit  when cemp%notfound;
 
              If (saltotal+psal*0.1)>50000  then
 
              Update  emp set  sal=sal*1.1  where empno=pempno;
 
              Countemp:=countemp+1;
 
              Saltotal:=saltotal+psal*0.1;
 
              Else  exit;
 
              End  if;
 
       End loop;
 
       Close cemp;
 
       Commit;
 
       Dbms_output.put_line(‘人数是：’||countemp||’总额是：’||saltotal);
 
End;

3.       用pl/sql语言编写一个程序，实现按部门分段（6000以上，（6000，3000）、3000元一下）统计各工资段的职工人数、以及各部门的工资总额（工资总额中不包括奖金）。

为了得到一定的结果格式的持久化，需要先创建一张表。Create table  empsal(deptno  number,count1 number,count2  number,count3  number ,saltotal  number);

Sql：得到部门：select  deptno  from dept;光标，一层循环。

部门中员工中的薪水：select  sal  from emp  where  deptno=?;带参数的光标，内层循环。

变量：count1 number;count2  number;count3  number;

部门的工资总额：saltotal number:=0;最终值有两种方法：（1）select sum(sal)  into  saltotal from  emp  where deptno=?（2）累加。

解答：

Declare
 
       Cursor cdept  is  select deptno  from  dept;
 
       Pdeptno dept.deptno%type;
 
      Cursor  cemp(dno number)  is  select sal  from  emp where  deptno=dno;
 
       Psal emp.sal%type;
 
       Count1 number;count2  number;count3  number;
 
       Saltotal number:=0;
 
Begin
 
       Open cdept;
 
       Loop
 
              Fetch  cdept into  pdeptno;
 
              Exit  when cdept%notfound;
 
              Count1:=0;count2:=0;count3:=0;
 
              Select  sum(sal) into  saltotal  from emp  where  deptno=pdeptno;
 
              Open  cemp(pdeptno);
 
              Loop
 
                     Fetch  cemp into  psal;
 
                     Exit  when cemp%notfound;
 
                     If  psal<3000 then  count1:=count1+1;
 
                            Elsif  psal>=3000 and  psal<6000  then count2:=count2+1;
 
                            Else  count3:=count3+1;
 
                     End  if;
 
              End  loop;
 
              Close  cemp;
 
              Insert  into empsal  values(pdeptno,count1,count2,count3,nvl(saltotal,0));
 
       End loop;
 
       Close cdept;
 
       Dbms_output.put_line(‘完成’);
 
End;

十六、   建立在plsql基础上的数据库对象

(一)存储过程

是指存储在数据库中供所有用户程序调用的子程序。存储过程不能返回值。

1.       语法：

Create  [or replace]  procedure  过程名（参数列表）

       As

              Plsql子程序体;

其中as也可以是is。相当于plsql中的declare，用于定义变量或者光标等。

例子：

Create  or replace  procedure  hahaha
 
As
 
       --说明部分
 
Begin
 
       Dbms_output.put_line(‘hahha’);
 
End;

2.       调用存储过程

1)       使用excuate命令，缩写为exec，如exec hahaha();

2)       在plsql中调用存储过程，如begin hahaha();hahaha();  end;

3.       带参数的存储过程

例如：给指定的员工涨100元工资，并打印涨前和涨后的薪水。

Create  or replace  procedure  raisesal(eno in  number) 
 
As
 
       Psal emp.sal%type;
 
Begin
 
       Select sal  into  psal from  emp  where  empno=eno;
 
       Update emp  set  sal=sal+100 where  empno=eno;
 
       Dbms_output.put_line(‘涨前是：’||psal||’涨后是：’||(psal+100));
 
End  raisesal;

注意：参数表中的in，表示是输入参数。提交和回滚最好在调用中执行。

测试：使用plsql。Begin raisesal(7839);  raise(21);  end;  /

4.       练习

给指定的员工增加指定额度的工资（注意：传递多个参数）

Create  or replace  procedure  raisesal2(eno in  number,rate  in number)
 
Is
 
       Psal emp.sal%type;
 
Begin
 
       Select sal  into  psal from  emp  where empno=eno;
 
       Update emp  set  sal=sal+rate where  empno=eno;
 
       Dbms_output.put_line(‘员工号是：’||emo||’涨前是：’||psal||’涨后是：’||(psal+rate));
 
End;

(二)存储函数

函数是一个命名的存储过程，可带参数，并返回一个计算值。函数和过程的结构类似，但必须有一个returned子句。函数说明要指定函数名、结果值的类型，以及参数型等。

语法：

Create  [or replace]  function  函数名（参数列表）

Return  函数值类型

As

       Plsql子程序体;

例子：查询某个员工的年收入

Create  or  replace  function annu(eno  in  number)
 
Return  number
 
Is
 
       Psal emp.sal%type;
 
       Pcomm emp.comm%type;
 
Begin
 
       Select sal,comm  Into  psal,pcomm from  emp  where empno=eno;
 
       Return psal*12+nvl(pcomm,0);
 
End  annu;

(三)过程和函数中的in和out

过程和函数都可以通过out指定一个或多个输出参数。使用out参数，在过程和函数中实现返回多个值。

例子：查询某个员工的姓名、薪水和职位。

Create  or replace  procedure  empinfo(eno in  number,pname  out varchar2,psal  out  number,pjob out  varchar2)
 
Is
 
Begin
 
       Select ename,sal,job  into  pname,psal,pjob  from emp  where  empno=eno;
 
End  empinfo;

测试：

Begin
 
       Empinfo(eno =>21,
 
                     Pname=>:panme,
 
                     Psal=>:psal,
 
                     Pjob=>:pjob);
 
End;

注意：在plsql中=>也表示赋值。

Out参数使得过程和函数没有区别，但是为了能够兼容老的版本，函数依然被延用。

(四)在java中调用存储过程和存储函数

在jdbc中调用存储过程，需要使用的接口是CallableStatement。

java.sql
接口 CallableStatement

所有超级接口：

PreparedStatement, Statement, Wrapper

public interface CallableStatement

extends PreparedStatement

用于执行 SQL 存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

   {call <procedure-name>[(<arg1>,<arg2>, ...)]}

IN 参数值是使用继承自PreparedStatement 的 set 方法设置的。在执行存储过程之前，必须注册所有 OUT 参数的类型；它们的值是在执行后通过此类提供的 get 方法获取的。

CallableStatement 可以返回一个 ResultSet 对象或多个 ResultSet 对象。多个 ResultSet 对象是使用继承自 Statement 的操作处理的。

为了获得最大的可移植性，某一调用的 ResultSet 对象和更新计数应该在获得输出参数的值之前处理。

1.       Java中使用oracle存储过程和函数基本步骤：

1)       创建java项目，拷贝jar包到lib。Oracle的jdbc的支持jar包在安装oracle的目录里面。

2)       编写一个jdbcutils

public class JDBCUtils {
 
       private static Stringdriver="oracle.jdbc.OracleDriver";
 
       private static Stringurl="jdbc:oracle:thin:@localhost:1521/orcl";
 
       private static Stringuser="scott";
 
       private static Stringpassword="tiger";
 
       static{
 
              try {
 
                     Class.forName(driver);
 
              } catch(ClassNotFoundException e) {
 
                     e.printStackTrace();
 
              }
 
       }
 
       public static ConnectiongetConnection(){
 
              try {
 
                     returnDriverManager.getConnection(url, user,password);
 
              } catch(SQLException e) {
 
                     e.printStackTrace();
 
              }
 
              return null;
 
       }
 
       public static voidrelease(Connection conn,Statement st,ResultSet rs){
 
              if(rs!=null){
 
                     try {
 
                            rs.close();
 
                     } catch(SQLException e) {
 
                            e.printStackTrace();
 
                     }finally{
 
                            rs=null;
 
                            //置空的原因是为了使得垃圾回收机制回收它。
 
                            //java的gc不受代码的控制，不能通过java代码干预gc。
 
                     }                   
 
              }
 
              if(st!=null){
 
                     try {
 
                            st.close();
 
                     } catch(SQLException e) {
 
                            e.printStackTrace();
 
                     }finally{
 
                            st=null;
 
                     }                   
 
              }
 
              if(conn!=null){
 
                     try {
 
                            conn.close();
 
                     } catch(SQLException e) {
 
                            e.printStackTrace();
 
                     }finally{
 
                            conn=null;
 
                     }                   
 
              }
 
       }
 
}

3)       编写测试代码

public class TestOracle {
 
   @Test
 
   public voidtestProcedure(){
 
      //{call<procedure-name>[(<arg1>,<arg2>, ...)]}
 
      String sql="{callempinfo2(?,?,?,?)";
 
      Connection conn=null;
 
      CallableStatement cs=null;
 
      try{
 
         conn=JDBCUtils.getConnection();
 
         cs=conn.prepareCall(sql);
 
         //对于输入参数赋值
 
         cs.setInt(1, 7839);
 
         //对于输出参数，需要申明
 
         cs.registerOutParameter(2, OracleTypes.VARCHAR);
 
         cs.registerOutParameter(3, OracleTypes.NUMBER);
 
         cs.registerOutParameter(4, OracleTypes.VARCHAR);
 
         cs.execute();
 
         String name=cs.getString(2);
 
         double sal=cs.getDouble(3);
 
         String job=cs.getString(4);
 
         System.out.println(name+"--"+sal+"--"+job);
 
      }catch(Exception e){
 
         e.printStackTrace();
 
      }finally{
 
         JDBCUtils.release(conn, cs, null);
 
      }
 
   }
 
  
 
   @Test
 
   public voidtestFunction(){
 
      //{?= call<procedure-name>[(<arg1>,<arg2>, ...)]}
 
      String sql="{?=callannu(?)}";
 
      Connection conn=null;
 
      CallableStatement cs=null;
 
      try{
 
         conn=JDBCUtils.getConnection();
 
         cs=conn.prepareCall(sql);
 
         cs.registerOutParameter(1, OracleTypes.NUMBER);
 
         cs.setInt(2, 7839);
 
         cs.execute();
 
         double annu=cs.getDouble(1);
 
         System.out.println(annu);
 
      }catch(Exception e){
 
         e.printStackTrace();
 
      }finally{
 
         JDBCUtils.release(conn, cs, null);
 
      }
 
   }
 
}

(五)包结构、包体

包结构，将光标、过程或者函数等对象打包，用来简化操作。包结构分为包头和包体。包结构和包体是另外两个常用的oracle对象。

使用关键子type可以自定义一个类型。

语法例子：

--包头

Create  or replace  package  mypackage
 
as
 
       type empcursor  is  ref cursor;
 
       procedure queryemplist(dno  in  number,emplist  out empcursor);
 
end  mypackage;

--包体

Create  or replace  package  body mypackage
 
As
 
Procedure  queryemplist(dno  in number,emplist  out  empcursor)
 
       As
 
       Begin
 
              Open  emplist for  select  * from  emp  where deptno=dno;
 
       End  queryemplist;
 
End  mypackage;

通过例子说明。

1.       查询某个员工的所有信息

2.       查询某个部门中的所有员工信息

查询所有信息，结果是一个集合。可以在out参数中使用光标。在out中使用光标可以通过申明包结构来简化操作。

Create or  replace  package mypackage
 
Is
 
       Type empcursor  is ref  cursor;
 
       Procedure queryemplist  (dno in  number,emplist  out empcursor);
 
      
 
End  mypackage;
 
Create  or  replace  package body  mypackage  is
 
       Procedure  queryemplist(dno  in number,emplist  out  empcursor)
 
       As
 
       Begin
 
              Open  emplist for  select  * from  emp  where deptno=dno;
 
       End;
 
End  mypackage;

通过desc  mypackage可以查看程序包。

在java中调用程序包中的存储过程或者函数。

   @Test
 
   public voidtestpackage(){
 
      String sql = "{callmypackage.queryemplist(?,?)";
 
      Connection conn=null;
 
      CallableStatement cs=null;
 
      ResultSet rs=null;
 
      try{
 
         conn=JDBCUtils.getConnection();
 
         cs=conn.prepareCall(sql);
 
         cs.setInt(1, 10);
 
         cs.registerOutParameter(2, OracleTypes.CURSOR);
 
         cs.execute();
 
         rs=((OracleCallableStatement)cs).getCursor(2);
 
         while(rs.next()){
 
            String name=rs.getString("ename");
 
            double sal=rs.getDouble("sal");
 
            System.out.println(name+"--"+sal);
 
         }
 
      }catch(Exception e){
 
         e.printStackTrace();
 
      }finally{
 
         JDBCUtils.release(conn, cs, rs);
 
      }
 
   }

(六)触发器

1.       定义

数据库触发器是一个与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。

例子：

每当插入新员工后，自动打印“成功插入新员工”。

Create  trigger ftrigger
 
After  insert
 
On  emp 
 
Declare
 
Begin
 
       Dbms_output.put_line(‘成功插入’);
 
End;
 
/

测试：insert into emp(empno,ename,sal,deptno) values(22,’hll’,200,10);

2.       触发器的类型。

语句级触发器，在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

行级触发器（for  each  row），触发语句作用的一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值的状态。

3.       触发器的作用

数据确认、实施复杂的安全性检查，做审计、跟踪表上所做的数据操作等，数据的备份和同步。

审计的种类：强制审计、标准审计、基于值的审计（使用触发器的方式）、细粒度审计、管理员审计。

数据备份的例子：next_day函数的应用，每个星期一自动备份表中的数据，使用到分布式数据库，触发器或者快照。

4.       语法

Create  [or replace]  trigger  触发器名

｛before|after｝

｛delete|insert|update[of  列名]｝

On  表名

[for  each row  [where]]

Plsql子程序

5.       例子

1)       关于实施安全性检查的例子：禁止在非工作时间插入新员工。

非工作时间包括：周末，to_char(sysdate,’day’) in  (‘星期六’,’星期日’)，to_number(to_char(sysdate,’hh24’))  not between  9  and  17。

Create  or replace  trigger  securityemp
 
Before  insert
 
On  emp
 
Begin
 
       If  to_char(sysdate,’day’)  in  (‘星期六’,’星期三’)  or  to_number(to_char(sysdate,’hh24’))  not between  9  and  17  then
 
       Raise_application_error(-20001,’不能在非工作时间插入新员工’);
 
       End  if;
 
End  securityemp;

创建触发器后，通过insert进行测试。

2)       关于数据确认的例子。

涨工资，要求涨后的薪水不能少于涨前的薪水。

这是一个行级触发器。

要使用:old和:new。:old表示操作某行前的值，:new表示操作某行后的值。

Create  or replace  trigger  checksal
 
Before  update
 
On  emp
 
For  each row
 
Begin
 
       If  :new.sal<:old.sal  then    
 
              Raise_application_error(-20002,’涨后的薪水不能少于涨前的薪水。涨前：’||:old.sal||’涨后：’||:new.sal);
 
       End  if;
 
End  checksal;

测试，使用update。

十七、   典型面试题

1.       提供表，有姓名和工资，得出一个查询表，显示3列，姓名，工资，他前一行的人的工资。

关于提供的表：create  table  test1(id number(10),name varchar2(20),money  number);

考点，相关子查询。

Select  id,name,money,(select  money  from  test1  where id=t.id-1)  money1  from test1  t;

2.       提供两张表，一张是课程id和选了这个课的学生id，一张是学生id和学生姓名。要求查询出课程id和班级里的学生的姓名。

create table pm_ci
 
 (ci_id varchar(20) primary key,
 
  stu_ids varchar(100));
 
insert into pm_civalues('1','1,2,3,4');
 
insert into pm_civalues('2','1,4');
 
 create table pm_stu
 
 (stu_id varchar(20) primary key,
 
  stu_name varchar(20));
 
insert into pm_stuvalues('1','张三');
 
insert into pm_stuvalues('2','李四');
 
insert into pm_stuvalues('3','王五');
 
insert into pm_stuvalues('4','赵六');
 
commit;

考点，多表查询。

解答：

Select  ci_id,wm_concat(stu_name)  namelist 
 
from(select  c.ci_id,s.stu_name
 
from  pm_ci  c,pm_stu s
 
where instr(c.stu_ids,stu_id)>0)
 
group  by ci_id;
 
wm_concat()，作用将n列数据放入1行中。