database DML

JOIN和UNION区别

join 是两张表做交连后里面条件相同的部分记录产生一个记录集，

union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。union只是将两个结果联结起来一起显示，并不是联结两个表

JOIN用于按照ON条件联接两个表，主要有四种：

INNER JOIN：内部联接两个表中的记录，仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。我理解的是只要记录不符合ON条件，就不会显示在结果集内。

LEFT JOIN / LEFT OUTER JOIN：外部联接两个表中的记录，并包含左表中的全部记录。如果左表的某记录在右表中没有匹配记录，则在相关联的结果集中右表的所有选择列表列均为空值。理解为即使不符合ON条件，左表中的记录也全部显示出来，且结果集中该类记录的右表字段为空值。

RIGHT JOIN / RIGHT OUTER JOIN：外部联接两个表中的记录，并包含右表中的全部记录。简单说就是和LEFTJOIN反过来。

FULL JOIN / FULL OUTER JOIN：完整外部联接返回左表和右表中的所有行。就是LEFTJOIN和RIGHTJOIN和合并，左右两表的数据都全部显示。

Cross Join交叉连接(即是笛卡尔积)

JOIN的基本语法：

Select table1.* FROM table1 JOIN table2 ON table1.id=table2.id where taleb1.name='XX'

sql写法

内连接innerjoin：

SELECT msp.name, party.name FROM msp JOIN party ON party=code

或

SELECT msp.name, party.name  FROM msp inner JOIN party ON party=code

 

左连接leftjoin ：

SELECT msp.name, party.name FROM msp LEFT JOIN party ONparty=code

 

右连接rightjoin ：

SELECT msp.name, party.name  FROM msp RIGHT JOIN party ONmsp.party=party.code

 

全连接(fulljoin)：

SELECT msp.name, party.name   FROM msp FULL JOIN party ON msp.party=party.code

 

 UNION运算符

将两个或更多查询的结果集组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION的结果集列名与UNION运算符中第一个Select语句的结果集的列名相同。另一个Select语句的结果集列名将被忽略。

UNION 内部的SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT 语句中的列的顺序必须相同。

其中两种不同的用法是UNION和 UNIONALL，区别在于UNION从结果集中删除重复的行。如果使用UNIONALL 将包含所有行并且将不删除重复的行。

 

UNION和UNIONALL的区别：

union 检查重复

union all 不做检查

比如select 'a' union select 'a' 输出就是一行a

比如select 'a' union all select 'a' 输出就是两行 a

 

除了 union all 外，其他的集合运算（union、minus、intersect）都不支持 lob 类型列。

原因是lob字段不能做groupby ，而union中需要使用groupby过滤到重复纪录。

另一种方法：

需要把其转换为字符串来进行处理. dbms_lob.substr(clobcloum,2000,1)

 

select id, name from cims_supplier　where name like 'A%'

union

select cdbregno as id, dbms_lob.substr(supplier_name,2000,1) as name from acdsupplier_moltablewhere supplier_name like 'A%'

cims_supplier表中的name是varchar2类型, 而acdsupplier_moltable表中的supplier_name为clob类型, 这样就可以union了.

 

当然排序也可以了.

select * from t1 order bydbms_lob.substr(clobcloum,2000,1);

a. 并集UNION

SELECT column1, column2 FROM table1 UNION SELECTcolumn1, column2 FROM table2

b. 交集JOIN

SELECT * FROM table1 AS a JOIN table2 b ON a.name=b.name

c. 差集NOTIN

SELECT * FROM table1 WHERE name NOT IN(SELECT name FROMtable2)

d. 笛卡尔积

SELECT * FROM table1 CROSS JOIN table2

或

SELECT * FROM table1,table2

                                                                               

Store_Information 表格				Internet Sales 表格
store_name	Sales	Date		Date	Sales	Amout
Los Angeles	$1500	Jan-05-1999		Jan-07-1999	$250	100
San Diego	$250	Jan-07-1999		Jan-10-1999	$535	200
Los Angeles	$300	Jan-08-1999		Jan-11-1999	$320	300
Boston	$700	Jan-08-1999		Jan-12-1999	$750	400

 

SELECT Date FROM Store_Information UNION  SELECT Date FROM Internet_Sales		SELECT Date FROM Store_Information UNION ALL  SELECT Date FROM Internet_Sales
Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999		Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 Jan-07-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999

 

Select * from Store_Information t1 join Internet_Sales t2on t1.data = t2.dat

 

store_name	Sales	Date	Amout
San Diego	$250	Jan-07-1999	200

 

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
求和函数——SUM() 计数函数——COUNT() 最大/最小值函数—MAX()/MIN() 均值函数——AVG()
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。
SELECT DEPT, salary AS MAXIMUM　FROM STAFF　GROUP BY DEPT　此sql不对。因为salary不是group by或函数

某个员工信息表结构和数据如下：
  id  name  dept  salary  edlevel  hiredate
   1 张三 开发部 2000 3 2009-10-11
   2 李四 开发部 2500 3 2009-10-01
   3 王五 设计部 2600 5 2010-10-02
   4 王六 设计部 2300 4 2010-10-03
   5 马七 设计部 2100 4 2010-10-06
   6 赵八 销售部 3000 5 2010-10-05
   7 钱九 销售部 3100 7 2010-10-07
   8 孙十 销售部 3500 7 2010-10-06
例如，我想列出每个部门最高薪水的结果，sql语句如下：
SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT
查询结果如下：
      DEPT  MAXIMUM
      开发部 2500
      设计部 2600
      销售部 3500
解释一下这个结果：
1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY DEPT中包含的列DEPT。
2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。
注意：计算的是每个部门（由 GROUP BY 子句定义的组）而不是整个公司的 MAX(SALARY)。
例如，查询每个部门的总的薪水数
SELECT DEPT, sum( SALARY ) AS total FROM STAFF GROUP BY DEPT
查询结果如下：
DEPT  total
开发部 4500
设计部 7000
销售部 9600
将 WHERE 子句与 GROUP BY 子句一起使用
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。
例如，查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL
查询结果如下：
  DEPT  EDLEVEL  MAXIMUM
 设计部 4 2300
 设计部 5 2600
 销售部 5 3000
 销售部 7 3500
注意：在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对DEPT和EDLEVEL的每个唯一组合各返回一行。
在GROUP BY子句之后使用HAVING子句
HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。

WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
GROUP BY 子句用来分组 WHERE 子句的输出。
HAVING 子句用来从分组的结果中筛选行。
对于可以在分组操作之前或之后应用的任何搜索条件，在 WHERE 子句中指定它们会更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。

可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。为此，在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。每个谓词将组特性（如AVG(SALARY)）与下列之一进行比较：
例如：寻找雇员数超过2个的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT( * ) >2
ORDER BY DEPT
查询结果如下：
  DEPT  MAXIMUM  MINIMUM
      设计部 2600 2100
      销售部 3500 3000
例如：寻找雇员平均工资大于3000的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING AVG( SALARY ) >3000
ORDER BY DEPT
查询结果如下：
  DEPT  MAXIMUM  MINIMUM
      销售部 3500 3000

 Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字串。
SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston';
--'EastBoston'

SELECT region_name || ' ' || store_name FROM Geography WHERE store_name = 'Boston';
--'East Boston'

instr函数与substr函数
instr函数用于从指定的位置开始，从大型对象中查找第N个与模式匹配的字符串。
用于查找内部大对象中的字符串的instr函数语法如下：
dbms_lob.instr(
lob_loc in blob,
pattern in raw,
offset in integer := 1;
nth in integer := 1)
return integer;

dbms_lob.instr(
lob_loc in clob character set any_cs,
pattern in varchar2 character set lob_loc%charset,
offset in integer:=1,
nth in integer := 1)
return integer;

lob_loc为内部大对象的定位器
pattern是要匹配的模式
offset是要搜索匹配文件的开始位置
nth是要进行的第N次匹配

instr( string1, string2 [, start_position [, nth_appearance ] ] )
string1 源字符串，要在此字符串中查找。
string2 要在string1中查找的字符串.
start_position 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
如果String2在String1中没有找到，instr函数返回0.
示例：
SELECT instr('syranmo','s') FROM dual; -- 返回 1
SELECT instr('syranmo','ra') FROM dual;  -- 返回 3
SELECT instr('syran mo','a',1,2) FROM dual;  -- 返回 0
根据条件，由于a 只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！
SELECT instr('syranmo','an',-1,1) FROM dual;  -- 返回 4
就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4
SELECT instr('abc','d') FROM dual;  -- 返回 0
注：也可利用此函数来检查String1中是否包含String2，如果返回0表示不包含，否则表示包含。

如果我有一份资料，上面都是一些员工的工号（字段：CODE），可是我现在要查询出他们的所有员工情况，例如名字，部门，职业等等，这里举例是两个员工，工号分别是’A10001′，’A10002′，其中假设staff是员工表，那正常的做法就如下：
SELECT  code , name , dept, occupation FROM staff  WHERE code IN ('A10001','A10002');
或者：
SELECT  code , name , dept, occupation FROM staff  WHERE code = 'A10001' OR code = 'A10002';

有时候员工比较多，我们对于那个’觉得比较麻烦，于是就想，可以一次性导出来么？这时候你就可以用instr函数，如下：
SELECT  code , name , dept, occupation FROM staff  WHERE instr('A10001,A10002',code)>0;

SELECT code, name, dept, occupation  FROM staff  WHERE instr(code, '001') > 0;
等同于
SELECT code, name, dept, occupation  FROM staff  WHERE code LIKE '%001%' ;


substr函数
substr函数用于从大对象中抽取指定数码的字节。当我们只需要大对象的一部分时，通常使用这个函数。
操作内部大对象的substr函数语法如下：
dbms_lob.substr(
  lob_loc in blob,
  amount in integer := 32767,
  offset in integer := 1)
return raw;

dbms_lob.substr(
  lob_loc in clob character set any_cs,
  amount in integer := 32767,
  offset in integer := 1)
return varchar2 character set lob_loc%charset;
其中各个参数的含义如下：
lob_loc是substr函数要操作的大型对象定位器
amount是要从大型对象中抽取的字节数
offset 是指从大型对象的什么位置开始抽取数据。
如果从大型对象中抽取数据成功，则这个函数返回一个 raw 值。如果有以下情况，则返回null：
 1 任何输入参数尾null
 2 amount < 1
 3 amount > 32767
 4 offset < 1
 5 offset > LOBMAXSIZE

 
取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )
substr('This is a test', 6, 2)     would return 'is'
substr('This is a test', 6)     would return 'is a test'
substr('TechOnTheNet', -3, 3)     would return 'Net'
substr('TechOnTheNet', -6, 3)     would return 'The'
substr('abcde',-6) = null
substr('abcde',-5) = 'abcde'
substr('abcde',-4) = 'bcde'
substr('abcde',-0) = 'abcde'

 

Oracle LOWER() 和 UPPER()函数的使用方法

一列返回行不区分大小写

这是常见的做法，使列不区分大小写，以确保您所希望返回所有行。

SELECT * FROM COURSES WHERE LOWER(education_delivery_method) = 'classroom'

 Oracle Connect By用法
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：
select ... from <TableName> where <Conditional-1>
start with <Conditional-2>  connect by <Conditional-3>

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
<Conditional-3>：是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。
 
早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为根节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
 
1． 树结构的描述
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
SELECT 。。。
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH]；
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
2． 关于PRIOR
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
3． 定义查找起始节点
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
4．使用LEVEL
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。
5．节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
6．排序显示
象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。

数据库表结构如下：
create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);
1)select * from t2 start with root_id = 0 connect by prior id = root_id;
如果connect by prior中的prior被省略，则查询将不进行深层递归。如：
2)select * from t2 start with root_id = 0 connect by id = root_id;
如果1)能查询出10条记录，2)只能查询出2条记录

ADD_MONTHS:加月
add_months(sysdate,1)加一个月  
add_months(sysdate,-1)减一个月   
select to_char(add_months(to_date('20110101', 'yyyymmdd'), 1), 'YYMMDD') from dual;
日期与数字就可相加
加日：SQL>   SELECT   SYSDATE,SYSDATE+1   FROM   DUAL;   
日期与日期之间不能相加
SQL>select sysdate+sysdate from dual;   错
日期与日期可以相减  
SQL>select sysdate-sysdate from dual;

1.TRUNC for dates(日期)
TRUNC函数返回以指定元素格式截去一部分的日期值。
其具体的语法格式如下：
TRUNC（date, [fmt]）
其中：
date 为必要参数，是输入的一个日期值
fmt 参数可忽略，是日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去
下面是该函数的使用情况：
TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')
='24-Nov-1999 12:00:00 am'
2.TRUNC for number(数字)
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89 (即取整)
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为 1即取整到十分位，如果是-1，则是取整到十位，以此类推；如果所设置的参数为负数，且负数的位数大于整数的字节数的话，则返回为0。如：TRUNC(89.985,-3)=0。

alter table emp_reg add constraint emp_pk primarykey(empno);
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGNKEY (Id_P) REFERENCES Persons(Id_P)
Oracle创建序列，Oracle序列的语法格式为：
CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [{CYCLE|NOCYCLE}] [{CACHEn|NOCACHE}];

1)INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负select dbms_metadata.get_ddl('SEQUENCE',u.object_name)from user_objects u where object_type='SEQUENCE'值，则代表Oracle序列的值是按照此步长递减的。
2)START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
3)MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的 27次方;对于递减序列，最大值是-1。
4)MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE 是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。
5)CYCLE 和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
6)CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。如果指定CACHE 值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉(shutdownabort),cache中的sequence就会丢失. 所以可以在createsequence的时候用nocache防止这种情况。

删除Oracle序列的语法
DROP SEQUENCE sequence_name;
修改SEQUENCE
ALTER SEQUENCE [user.]sequence_name [INCREMENT BY n]
　　[MAXVALUEn| NOMAXVALUE ]　　[MINVALUEn | NOMINVALUE]
获得SEQUENCE
SELECT sql_sequence_name.nextval FROM dual(dual是oracle中的哑元表，因oracle的select要有from语句(sqlserver则不需要)，所以不需要从表或视图中取值的select 语句，就跟fromdual)
sql_sequence_name.CURRVAL    sql_sequence_name.NEXTVAL
第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的 INCREMENTBY值，然后返回增加后的值。CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用 CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。

导出SEQUENCE
SELECT SEQUENCE_NAME,INCREMENT_BY,MIN_VALUE,MAXVALUECYCLE_FLAG,ORDER_FLAG, CACHE_SIZE, LAST_NUMBER FROM user_SEQUENCES;
结果
ARTCOMMENT_SEQ  1341  999999999999999999999999999  Y Y 20 10001;

可以使用 sequence的地方：
① 不包含子查询、snapshot、VIEW的 SELECT语句
②INSERT语句的子查询中
③INSERT 语句的VALUES中
④UPDATE 的SET中
假设有表TEST，其主键为 TEST_ID
create sequence sql_sequence_name increment by 1 startwith 1  minvalue 1 nomaxvalue nocylce

INSERT INTO TEST VALUES (sql_sequence_name.nextval,LEWIS, CLERK,7902, SYSDATE, 1200, NULL, 20);
或者建立触发器，当有数据插入表TEST时，使用Oracle序列为其去的递增的主键值
create trigger TRG_TEST before insert on TEST
for each row
begin
select sql_sequence_name.nextval into :new.TEST_ID fromdual;
end;

索引的创建语法：
CREATE UNIUQE | BITMAP INDEX<schema>.<index_name>
    ON<schema>.<table_name>(<column_name> | <expression> ASC | DESC,<column_name> | <expression> ASC | DESC,...)
     TABLESPACE<tablespace_name>
     STORAGE<storage_settings>
     LOGGING |NOLOGGING
     COMPUTE STATISTICS
     NOCOMPRESS |COMPRESS<nn>
     NOSORT |REVERSE
     PARTITION |GLOBAL PARTITION<partition_setting>
相关说明
1）UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name>| <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING| NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTESTATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS| COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT| REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION| NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区
索引的修改
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]
REBUILD 是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。
DBA经常用REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。
删除索引
DROP INDEX [schema.]indexname

索引特点：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引不足：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
应该建索引列的特点：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
不应该建索引列的特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

限制索引
使用不等于操作符（<>、!=）     
  下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。    
   selectcust_Id,cust_name from customers where cust_rating <> 'aa';       
    把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。       
  selectcust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating> 'aa';
  特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
使用ISNULL 或ISNOT NULL
   使用ISNULL 或ISNOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
使用函数
   如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）
 selectempno,ename,deptno from emp  where  trunc(hiredate)='01-MAY-81';
   把上面的语句改成下面的语句，这样就可以通过索引进行查找。
 selectempno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);
比较不匹配的数据类型      
    也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个VARCHAR2类型,在account_number字段上有索引。
下面的语句将执行全表扫描：
 selectbank_name,address,city,state,zip from banks where account_number = 990354;
  Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
 selectbank_name,address,city,state,zip from banks where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次“全表扫描”。
查询索引
查询 DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问 USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！

ORACLE ROWID
通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。

索引的类型
B-树索引(默认)    位图索引  HASH索引     索引编排表 反转键索引 基于函数的索引  分区索引  本地和全局索引
 
Mysql分页采用limt关键字
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分页采用top关键字(20005以上版本也支持关键字rownum)
Select top 10 * from t_order where id not in (select idfrom t_order where id>5 ); //返回第6到15行数据
其中10表示取10记录 5表示从第5条记录开始取

Oracle 分页
１采用rownum关键字(三层嵌套)
SELECT * FROM(
    SELECTA.*,ROWNUM  num FROM (SELECT * FROMt_order)A  WHERE  ROWNUM<=15
    ) WHERE num>=5;
--返回第5-15行数据

２采用 row_number解析函数进行分页(效率更高)
SELECT A.* FROM(
    SELECT t.*,row_number() over(ORDER BY o_id)AS num  FROM t_order t
    )A  WHERE num BETWEEN 5 AND 15;
--返回第5-15行数据

解析函数能用格式
row_number() 从1开始，为每一条分组记录返回一个数字
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序
根据部门分组，显示每个部门的工资等级
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

３根据ROWID来分
select * from t_xiaoxi where rowid in(
select rid from (
select rownum rn,rid from(
select rowid rid,cid from　t_xiaoxi  order by cid desc)  where rownum<10000
) where rn>9980
)order by cid desc;

MySQL支持４种隔离级别。默认的隔离级别是可重复读
更改会话级的隔离级 set sessiontx_isolation='read-uncommitted';
更改系统级的隔离级别 set globaltx_isolation='read-uncommitted';
select @@global.tx_isolation,@@tx_isolation;

Oracle数据库支持 READCOMMITTED 和SERIALIZABLE这两种事务隔离级别。Oracle默认使用的是READCOMMITTED
SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READCOMMITTED|REPEATABLE READ|SERIALIZABLE]

MySQL在5.0以前并不支持存储过程
MySQL 存储过程创建的格式：CREATE PROCEDURE 过程名([过程参数[,...]])[特性 ...] 过程体

这里先举个例子：
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc1(OUT s int)
     -> BEGIN
     -> SELECT COUNT(*) INTO s FROM user;
     -> END
    -> //
mysql> DELIMITER ;
（1）这里需要注意的是DELIMITER//和 DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识
IN  输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
变量定义
DECLARE variable_name [,variable_name...] datatype[DEFAULT value];
其中，datatype为MySQL的数据类型，如:int,float, date, varchar(length)
例如:
   1. DECLARE l_int int unsigned default 4000000;
   2. DECLARE l_numeric  number(8,2) DEFAULT 9.95;
   3. DECLAREl _date  date DEFAULT '1999-12-31';
   4. DECLAREl _datetime  datetime DEFAULT '1999-12-3123:59:59';
   5. DECLAREl _varchar  varchar(255) DEFAULT 'Thiswill not be padded';  
变量赋值
 SET 变量名 = 表达式值[,variable_name = expression ...]
注释
双模杠：--　　该风格一般用于单行注释
MySQL存储过程的调用
用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。
mysql> CALL proc1(10);

MySQL 存储过程的查询(3种方法)
select name from mysql.proc where db=’数据库名’;
select routine_name from information_schema.routines whereroutine_schema='数据库名';
show procedure status where db='数据库名';

查看存储过程的详细
SHOW CREATE PROCEDURE 数据库.存储过程名;
MySQL存储过程的修改             ALTER PROCEDURE
MySQL存储过程的删除             DROP PROCEDURE

MYSQL触发器
当执行delete、update或insert操作时，可以使用触发器来触发某些操作。
创建触发器：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
其中trigger_name是触发器名，
trigger_time:BEFORE,AFTER
trigger_event:INSERT、 UPDATE、DELETE
tbl_name:关联的表名
注意，INSERT除了插入操作，loaddata也能激活该事件。对于同一trigger_event,不能有两个相同trigger_time的触发器。
trigger_stmt：触发器被激活时执行的语句，可以使用单条语句，也可以使用BEGIN——END这样的复合语句。
删除触发器：
DROP TROGGER trigger_name

1.MySQL触发器针对行来操作，因此当处理大数据集的时候可能效率很低。
2. 触发器不能保证原子性，例如在MYISAM中，当一个更新触发器在更新一个表后，触发对另外一个表的更新，若触发器失败，不会回滚第一个表的更新。 InnoDB中的触发器和操作则是在一个事务中完成，是原子操作。
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACHROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;


mysql触发器new old:
"NEW.column_name"或者"OLD.column_name".这样在技术上处理（NEW| OLD . column_name）新和旧的列名属于创建了过渡变量（"transitionvariables"）。
对于INSERT语句,只有 NEW是合法的；对于DELETE语句，只有OLD才合法；
而UPDATE语句可以在和NEW以及OLD同时使用。

CREATE TRIGGER tr1 BEFORE UPDATE ON t22  
FOR EACH ROW  
BEGIN  
SET @old = OLD.s1;  
SET @new = NEW.s1;  
END;
现在如果t21表中的s1列的值是55，那么执行了"UPDATEt21 SET s1 = s1 + 1"之后@old的值会变成55，而@new的值将会变成56。


Mysql创建索引
1．ALTER TABLE
ALTER TABLE 用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name(column_list)   
ALTER TABLE table_name ADD UNIQUE (column_list)   
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2．CREATE INDEX
CREATE INDEX 可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)   
CREATE UNIQUE INDEX index_name ON table_name(column_list)
删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATEINDEX语句，DROPINDEX可以在ALTERTABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
查看索引
mysql> show index from tblname;
mysql> show keys from tblname;

索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%aaa%” 不会使用索引而like“aaa%”可以使用索引。
不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
不使用NOTIN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOTIN可以NOTEXISTS代替，id<>3则可使用id>3or id<3来代替。
Mysql视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE |TEMPTABLE}]
    VIEW view_name[(column_list)]
    AS select_statement
    [WITH [CASCADED| LOCAL] CHECK OPTION]
在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。
表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。
视图必须具有唯一的列名，不得有重复，就像基表那样。默认情况下，由SELECT语句检索的列名将用作视图列名。要想为视图列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的ID。column_list中的名称数目必须等于SELECT语句检索的列数。
SELECT语句检索的列可以是对表列的简单引用。也可以是使用函数、常量值、操作符等的表达式。
mysql> CREATE VIEW v AS SELECT qty, price, qty*priceAS value FROM t;

if-then -else语句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc2(IN parameter int)
     -> begin
     -> declare var int;
     -> set var=parameter+1;
     -> if var =0 then
     -> update tset s1=s1+1;  
     -> else
     -> update tset s1=s1+2;
     -> endif;
     ->end;
     -> //
mysql > DELIMITER ;

case语句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3 (in parameter int)
     -> begin
     -> declarevar int;
     -> set var=parameter+1;
     -> case var
     -> when 0 then  
     -> insert into t values(17);
     -> when 1 then  
     -> insert into t values(18);
     ->else  
     -> insert into t values(19);
     -> endcase;
     ->end;
     -> //
mysql > DELIMITER ;

根据国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
SELECT  SUM(population),
CASE country  WHEN '中国' THEN '亚洲'
  WHEN '印度' THEN '亚洲'
  WHEN '日本' THEN '亚洲'
  WHEN '美国' THEN '北美洲'
  WHEN '加拿大'  THEN '北美洲'
  WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END
FROM    Table_A
GROUP BY
CASE country
WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲 '
WHEN '加拿大'   THEN '北美洲'
WHEN '墨西哥'   THEN '北美洲'
ELSE '其他' END;

同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下:
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)  FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;

按照国家和性别进行分组
SELECT  country,
SUM( CASE WHEN sex = '1' THEN  population ELSE 0 END),  --男性人口
SUM( CASE WHEN sex = '2' THEN  population ELSE 0 END)   --女性人口
FROM  Table_A  GROUP BY country;

UPDATE  Personnel
SET salary =
CASE WHEN salary >= 5000  　                THEN salary * 0.9
     WHEN salary >= 2000 AND salary < 4600  THEN salary * 1.15
ELSE salary END;

SELECT std_id  FROM Studentclass  GROUP BY std_id  HAVING COUNT(*) = 1;

下面举个简单的例子来说明。例如表 students(id, name ,birthday, sex, grade)，要求按每个年级统计男生和女生的数量各是多少

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1     
                            ELSE NULL
                        END) 男生数,
               COUNT (CASE WHEN sex = 2 THEN 1
                            ELSE NULL
                       END) 女生数
FROM students
GROUP BY grade;

循环语句while …  end while
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc4()
     -> begin
     -> declare var int;
     -> set var=0;
     -> while var<6 do
     -> insert into t values(var);
     -> set var=var+1;
     -> endwhile;
     ->end;
     -> //
mysql > DELIMITER ;


Oracle 视图
CREATE[OR REPLACE][FORCE][NOFORCE]VIEW view_name
[(column_name)[,….n]]
AS
Select_statement
[WITH CHECK OPTION[CONSTRAINT constraint_name]]
[WITH READ ONLY]

说明:
view_name : 视图的名字
column_name: 视图中的列名
在下列情况下, 必须指定视图列的名称
* 由算术表达式, 系统内置函数或者常量得到的列
* 共享同一个表名连接得到的列
* 希望视图中的列名与表中的列名不同的时候
REPLACE: 如果创建视图时, 已经存在此视图, 则重新创建此视图, 相当于覆盖
FORCE: 强制创建视图,无论的视图所依赖的基表否存在或是否有权限创建
NOFORCE: 只有基表存在且具有创建视图权限时, 才可以创建视图
WITH CHECK OPTION 指出在视图上所进行的修改都要符合select_statement 所指定的限制条件
WITH READ ONLY 只允许查看视图

删除视图:
DROP VIEW   view_nam
Oracle 游标
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
Cursor类型包含三种:隐式Cursor，显式Cursor和RefCursor（动态Cursor）。
隐式Cursor:
1).对于Select…INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DMLSql语句，就是隐式 Cursor。例如：Select/Update / Insert/Delete操作。
2)作用：可以通过隐式Cusor的属性来了解操作的状态和结果，从而达到流程的控制。Cursor的属性包含：
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND  布尔型  值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
3) 隐式Cursor是系统自动打开和关闭Cursor.
显式Cursor：
（1） 对于从数据库中提取多行数据，就需要使用显式Cursor。显式Cursor的属性包含：
%ROWCOUNT   整型  获得FETCH语句返回的数据行数
%FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND   布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真，否则为假
（2） 对于显式游标的运用分为四个步骤：
定义游标---Cursor  [Cursor Name] IS;
打开游标 ---Open  [Cursor Name];
操作数据---Fetch  [Cursor name]
关闭游标 ---Close[Cursor Name],这个Step绝对不可以遗漏。
declare  cursor c_job is
       select empno,ename,job,sal from emp  where job='MANAGER';
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
        dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
       end loop;
end;

declare  cursor c_job is
       select empno,ename,job,sal from emp where job='MANAGER';
       --定义一个游标变量
       c_row c_job%rowtype;
begin
       open c_job;
         loop
           fetch c_job into c_row;--提取一行数据到c_row
           exit when c_job%notfound;
          dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
         end loop;     
      close c_job;--关闭游标
end;

Ref Cursor（动态游标）：
1） 与隐式Cursor,显式Cursor的区别：RefCursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor，是静态的，在编译期间就决定数据结果集。
2）Ref cursor的使用:
Type [Cursor type name] is ref cursor
Define 动态的Sql语句
Open cursor
操作数据---Fetch  [Cursor name]
Close Cursor
Set server output on;

Declare
    type cur_type is ref cursor;
    cur_policy cur_type;
    sqlStr varchar2(500);
    rec_policy t_contract_master%rowtype;
begin
     sqlStr:= 'select cm.policy_code, cm.applicant_id,cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm
     where cm.liability_state = 2 and cm.policy_type = 1 and cm.policy_cate in (2,3,4) andrownum < 5  order by cm.policy_codedesc ';
     open cur_policy for sqlStr;
  loop
       fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id,rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;
       exit when cur_policy%notfound;         
      Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);
  end loop;
close cur_policy;     
end;
 

Oracle 触发器
create trigger biufer_employees_department_id
 before insert or update
  of department_id
  on employees
 referencing old as old_value
     new as new_value
 for each row
 when(new_value.department_id<>80 )
begin
 :new_value.commission_pct :=0;
end;

触发器的组成部分：
1、 触发器名称
create trigger biufer_employees_department_id
命名习惯：
biufer（beforeinsert update for each row）
employees 表名
department_id 列名
2、 触发语句
比如：
表或视图上的DML语句　DDL语句　数据库关闭或启动,startupshutdown 等等
before insert or update
  of department_id
  on employees
 referencing old as old_value
     new as new_value
 for each row
说明：
1、 无论是否规定了department_id，对employees表进行insert的时候
2、 对employees表的department_id列进行update的时候
3、 触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列 department_id不等于80的时候，触发器就会执行。其中的new_value是代表跟新之后的值。
4、 触发操作
是触发器的主体
begin
 :new_value.commission_pct :=0;
end;
主体很简单，就是将更新后的commission_pct列置为0
触发：
insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct)
values( 12345,’Chen’,’Donny’, sysdate, 12,‘donny@hotmail.com’,60,10000,.25);
触发器类型：
1、 语句触发器
2、 行触发器
3、INSTEAD OF 触发器
4、 系统条件触发器
5、 用户事件触发器
1、 语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。
对修改表的时间、人物进行日志记录。
在 employees_copy表上建立语句触发器，在触发器中填充employees_log表。
Create or replace trigger biud_employee_copy
  Before insert or update or delete
   O nemployees_copy
 Declare  L_action employees_log.action%type;
 Begin
  if inserting then
  l_action:=’Insert’;
  elsif updatingthen
   l_action:=’Update’;
  elsif deletingthen
  l_action:=’Delete’;
  else
  raise_application_error(-20001,’You should never ever get this error.’);
  Insert intoemployees_log(Who,action,when) Values( user, l_action,sysdate);
 End;

2、 行触发器
是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：
1、 定义语句中包含FOREACH ROW子句
2、 在BEFORE……FOREACH ROW触发器中，用户可以引用受到影响的行值。
比如：
create trigger biufer_employees_department_id
 before insert or update
  of department_id
  on employees_copy
 referencing old as old_value
     new as new_value
 for each row
 when(new_value.department_id<>80 )
begin
 :new_value.commission_pct :=0;
end;

Referencing 子句：
执行DML语句之前的值的默认名称是:old ,之后的值是:new
insert 操作只有:new
delete 操作只有:old
update 操作两者都有
referencing子句只是将new 和old重命名为new_value和old_value，目的是避免混淆。比如操作一个名为new的表时。
3、 INSTEAD OF 触发器更新视图
Create or replace view company_phone_book as
 Selectfirst_name||’, ’||last_name name, email, phone_number,
employee_id emp_id
From hr.employees;
尝试更新email和name
update hr.company_phone_book set name=’Chen1,Donny1’where emp_id=100
create or replace trigger update_name_company_phone_book
INSTEAD OF
Update on hr.company_phone_book
Begin
 Updatehr.employees
  Setemployee_id=:new.emp_id,
  First_name=substr(:new.name, instr(:new.name,’,’)+2),
   last_name=substr(:new.name,1,instr(:new.name,’,’)-1),
  phone_number=:new.phone_number,
   email=:new.email
 whereemployee_id=:old.emp_id;
end;
4、 系统事件触发器

系统事件：数据库启动、关闭，服务器错误
create trigger ad_startup
 after startup
  on database
begin
 -- do some stuff
end;

5、 用户事件触发器
用户事件：用户登陆、注销，CREATE/ ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE /
create or replace trigger log_drop_trigger
 before drop ondonny.schema
begin
 insert intodroped_objects values(
 ora_dict_obj_name,  -- 与触发器相关的函数
 ora_dict_obj_type,
  sysdate);
end;


禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;
事务处理：
在触发器中，不能使用commit/ rollback
因为ddl 语句具有隐式的commit，所以也不允许使用

Oracle存储过程
创建存储过程
create or replace procedure test(var_name_1 in type,var_name_2out type) as
-- 声明变量(变量名 变量类型)
begin
--存储过程的执行体
end;

变量赋值
变量名:= 值;
create or replace procedure test(workDate in Date) as
x number(4,2);
y array;
cusor_1 Cursor is select std_name from student where  ...;
cursor_2 Cursor;
 begin
 x := 1;
 y := new array();
 select class_nameinto cursor_2 from class where ...;
end;

create or replace procedure runbyparmeters  (isal in emp.sal%type,　sname out varchar,sjob in outvarchar)as
icount number;
begin
    selectcount(*) into icount from emp where sal>isal and job=sjob;
    if icount=1 then
    ....
    else
    ....
    endif;
exception
    when too_many_rows then
    DBMS_OUTPUT.PUT_LINE('返回值多于1行');
    when others then
    DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end;