SQL PL/SQL总结（转）

1、set verify on/off 控制输出行不显示old和new

2、set define ‘&’定义变量字符

3、set echo off/on 禁止或者启用sql脚本中的sql语句和命令

4、accept varname vartype format a../$... prompt ‘....’；
undefine varname

5、变量中被定义为&1,&2意味者第一个变量和第二个变量

6、ttitle和btitle 代表的页眉和页脚

7、break on 和 compute 用于为列添加小计

8、内联视图 select colnum from （select colum from tablename where condition）；

9、union:两个表不重复的所有行，intersect：两个表共有的行，minus：第一个表有而第二个表没有的行。

10、translate（x，from_string，to_string）用作密码文件的形成。

11、decode（value，search_value，result，default_value）条件查询，可以实现if的功能或者case的功能。

12、case
        when condition then result1
        when condition then result2
        ····
        when condition then resultN
        else default_result
    end   注意case一般存在from前面

13、使用connect by和start with可以实现层次化查询，可以通过lpad命令实现目录树的功能，lpad（x，num）它指用x字符在左边填充num个。

14、1、rollup可以为每个分组返回小计记录，cube，可以返回每一个列组合的小计记录，同时在末尾加上总计记录，他们都是group by的一种扩展。2、还有一个就是grouping函数，它可以接受一列，但是只能爱使用rollup和cube的查询中使用，当需要返回空值的地方显示某个值时，就很有用，当列为空返回1，非空返回0。3、因此我们可以通过decode和grouping来指定非空行显示什么，空行显示什么，还有一种类似的功能就是通过nvl和nvl2也可以实现decode和grouping相结合的功能。4、还可以通过grouping sets只把小计记录显示出来，可以用来作为统计的信息显示。5、group_id()用来消除group by字句返回的重复记录，group_id()不接受任何参数。

15、可以从一个表向另外一个表复制，通过insert into table1（列的种类）select ··· from table2 where condition。

16、子表引用父表，子表中不能含有父表中没有的列值，但是父表中可以含有子表中没有的列值。

17、在创建表列时，可以指定default ''来定义默认值。

18、注意merge的活用
    merge into table1
    using table2 on （条件下）
    when matched then
   result1
    when not matched then
  result2；       matched就是条件吻合的情况，not matched就是条件不吻合的情况

19、使用dbms包的闪回查询，一种是时间戳，一种是scn
 execute dbms_flashback.enable_at_time（sysdate － ？/ 1440）；
 execute dbms_flashback.enable_at_system_change_number（scn号）；

20、系统授权可以传递使用with admin option，对象授权可以传递是使用with grant option
可以用sys权限创建synonym（同义词）来让public都访问。

21、修改列的注意事项：1、修改列的长度，条件是，改列的类型的长度可以修改，而且只有表中还没有任何行或者所有列都为空值时才可以减小列的长度。2、修改数字列的精度，同样也而且只有表中还没有任何行或者所有列都为空值时才可以减小数字列的精度。3、修改列的数据类型，如果表中还没有任何行或列为控制，就可以将列修改为任何一种数据类型（包括更短的数据类型），否则，就只能将列的数据类型修改为一种兼容的数据类型，但条件时没有缩短列的长度。4、使用列的默认值，默认值只适用于新插入表中的行。

22、禁用和启动约束，alter table tablename disable/enable constraint constraint_name

23、如果需要删除表的所有行就需要使用truncate，而不是delete，应为truncate回重置表的存储空间，以准备接受新行，执行truncate语句不需要在数据库中使用任何undo空间，也不需要执行commit命令使删除操作永久化。

24、使用序列
    create sequence name
 start with start_num
  increment by increment_num
 maxvalue max_num
 minvalue min_num
 cycle|nocycle
 cache cache_num|nocache
 order|noorder
    cycle用于指定是否循环的，cache则指定要保留在内存中的整数的个数，order则是确保按照请求次序生成整数。其中noorder为默认值,这里注意，一个序列包含了两个伪列，为currval和nextval，在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化，也就是说，必须先nextval，才能currval。可以用序列填充表的主键，修改序列的注意：不能修改序列的初值，序列的最小值不能大于当前值，序列的最大值不能小于当前值

25、一般都将表和索引存储到不同的表空间
    create index index_name on table_name（column_name，····） tablespace name；
    如果某列的值几乎都是唯一的，而且用where使用改列查询所返回的行都小于该表总行的10％，则改列就非常适合与创建索引。
    如果要基于函数的索引，就必须将参数query_rewrite_enabled设置为true；通过alter system来修改。每个表创建，就会给主键自动增加一个索引。

26、注意视图中不存储数据，它只是会访问基表中的行。
    create or replace view force/noforce view_name as 查询语句 [with (check option| read only) constraint constraint_name]；
    在视图上插入行就相当于在基表中插入，但是只能对简单视图执行DML操作，复杂视图不支持DML操作。而且在插入行时，还要满足创建视图时大的条件。用create or replace就可以用来彻底替换一个视图，alter view可以用来修改视图的约束。比如alter view name drop constraint ····

27、块结构
    declare
 declaration_statements
    begin
 executable_statements
    exception
 exception_handing_statements
    end；

28、dbms_output.put_line('')输出字符用的。它必须设定set serveroutput on 才可以看到。

29、%type可以用来定义变量的类型比如说 column1_name table_name.column1_name%type,它的意思就是column1_name的类型和表table_name中的column1_name列的类型一致

30、PL/SQL中使用的条件逻辑 简单循环
    if condition1 then          loop    
 statement1   statements
    elsif condition2 then end loop；可以在循环中设定exit when condition 跳出
 statement2  while循环
    else   while condition loop
 statement3   statements
    endif； 可以嵌套if语句 end loop；
       
     for循环
 for loop_variable in [reverse] lower_number..upper_number loop
 statements
 end loop;  默认的是增加值，使用reverse就可以减少1

31、游标，1、首先声明一些变量结果保存列值。一般都采用%type形式，用来和表中的列一样的类型 declare v_column table.column%type 2、声明游标，一般都和select组合在一起，如cursor cursor_name is select_statement；3、打开游标，直接open cursor_name 4、就可以从游标中取得记录了，fetch cursor_name into variable..,其中variable是declare中声明的变量，而且一般是放在循环中一行一行的读取，并设定exit when cursor_name%notfound时跳出。5、最后一步一定要注意，用完游标要关闭open cursor_name。注意：一般将游标和for循环合用，因为这样可以增强在游标中访问记录的能力，而且使用for循环不用显式地打开和关闭游标，连声明都一起省略了。

32、创建过程 create or replace procedure proceduce_name (parameter_name in | out |in out type,.....) is | as  
      begin
      procedure_body
      end;
    这里in是默认地，它指在程序运行地时候已经具有值，而且在程序体中这个值不会改变，而out定义参数只是在过程体内部赋值。in out是指参数在程序运行时可能已经具有值，但是在过程体中也可以修改的。如果发生错误，可以通过show error来显示出来。

33、创建对象类型 create or replace type type_name as object（...），一个对象类型中可以引用已经建立好地对象类型比如说 column type_name 就可以指定column地属性为type_name
在创建过程中，可以使用member function 函数 return 值；它和过程类似，唯一地区别式程序通常不返回值。用类型建立表 create table table_name （column type_name）或者create table table_name of type_name；对象表还有一个特点就是可以为对象表之间地关系建立模型，而不是使用外键。使用ref例如 column ref type_name scope is table_name；这里地scope is是将对象引用限制在特定表中地对象上。
    可以用set describe depth num 来显示信息地深度，通过desc就可以看到类型包含类型地信息了。SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

34、修改.sql脚本的执行路径，dos下可以通过修改sqlpath的方法来实现，就是set sqlpaht＝路径1；路径2····，可以在sqlplus中编辑缺省的脚本目录，通过
sql>set editfile 目录就可以了。

35、从sqlplus环境切换到操作系统命令提示符下，可以采用host命令或者“！”，host通用，！使用于linux和unix系统中但在windows下不被支持。 。

36、安装帮助文件可以通过启动$ORACLE_HOME\sqlplus\admin\help\helpbld.sql和hlpbld.sql来呼用helpus.sql建立，也可以呼用helpdrop.sql来删除。

37、调用show all可以查看所有的环境变量，show error用于显示当前在创建函数、存储过程、触发器、包等对象的错误信息。用show parameter parametername来显示初始化参数的值。
show rel来显示数据库的版本、show sga显示sga的大小，show user显示当前用户。

38. &与&&的区别。
&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。&&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。

39、@与@@的区别是什么。
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。
@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。

40、在查询语句中‘＃’的活用，如：就是如果突然忘了表的结果，可以通过#desc 表名先来显示表的名字
SQL> select deptno, empno, ename
2 from emp
3 where
4 #desc emp
Name Null? Type
----------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

4 sal > 4000;

41、制作脚本的另类方法：
set echo off
set feedback off
set newpage none
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set heading  off 
set timing off
set numwidth 38
SPOOL  c:\具体的文件名 
     你要运行的sql语句例如（SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;）可以做一个删除当前用户的所有表
SPOOL OFF。
这里注意（1）当前session是否对修改的数据进行自动提交 SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]} 
 （2）在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 SQL> SET ECHO {ON|OFF}
 （3）是否显示当前sql语句查询或修改的行数 SQL> SET FEED[BACK] {6|n|ON|OFF} 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数。 
 （4）是否显示列标题 SQL> SET HEA[DING] {ON|OFF} 当set heading off 时，在每页的上面不显示列标题，而是以空白行代替 
 （5）设置一行可以容纳的字符数 SQL> SET LIN[ESIZE] {80|n} 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。 
 （6）设置页与页之间的分隔 SQL> SET NEWP[AGE] {1|n|NONE} 当set newpage 0 时，会在每页的开头有一个小的黑方框。当set newpage n 时，会在页和页之间隔着n个空行。当set newpage none 时，会在页和页之间没有任何间隔。 
 （7）显示时，用text值代替NULL值 SQL> SET NULL text 
 （8）设置一页有多少行数 SQL> SET PAGES[IZE] {24|n} 如果设为0，则所有的输出内容为一页并且不显示列标题 
 （9）是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。SQL> SET SERVEROUT[PUT] {ON|OFF} 在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。 
 （10）当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。 SQL> SET WRA[P] {ON|OFF} 当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。 
 （11）是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。SQL> SET TERM[OUT] {ON|OFF} 在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。 
 （12）将SPOOL输出中每行后面多余的空格去掉。SQL> SET TRIMS[OUT] {ON|OFF} 
 （13）显示每个sql语句花费的执行时间 set TIMING {ON|OFF}

42、动态生成spool命令所需的文件名
在我们上面的例子中，spool命令所需要的文件名都是固定的。有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？
column dat1 new_value filename;
select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;  
spool c:\&&filename.txt  
select * from dept;
spool off;

43、常用的oracle元数据查询
sequence:  sql='select sequence_name from user_sequences order by sequence_name'
table:  sql='select table_name from user_tables order by table_name'
view:   sql='select view_name from user_views order by view_name'
PROCEDURE: sql='select object_name from user_objects where object_type=value'
FUNCTION:  sql='select object_name from user_objects where object_type= value'
PACKAGE:   sql='select object_name from user_objects where object_type= value'
TYPE:   sql='select object_name from user_objects where object_type= value'

44、Oracle 表空间相关的系统表
dba_tablespaces, dba_data_files, dba_temp_files, dba_free_space, dba_segments

45、Show feedback 检查feedback的行数
    Show numwidth 修改数值的宽度
    Show autocommit 自动提交工作的能力，即使没有下达commit命令，有些操作（如quit、exit）以及数据定义语言（ddl）的命令也会使提交发生。

46、注意delete与truncate，delete可以提交或回滚删除操作，而truncate自动删除表中的所有记录。Truncate命令的操作结果使部能被回滚或提交的，截除的记录不能被恢复。也不能靠执行闪回请求来找回被截除的数据。

47、一下往一个表中插入多行，要借助select和union两个语句
insert into test(id,name)
select * from
(select 101，‘swust’ from dual
 union
 select 102，‘student’from dual）；

48、修改系统时间显示模式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> alter session set nls_date_format='dd-mon-yyyy';
Session altered.
SQL> alter session set nls_date_language='SIMPLIFIED CHINESE';
SQL> select sysdate from dual;
SYSDATE
----------------
17-8月 -2007
SQL> alter session set nls_date_language='AMERICAN';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------
17-aug-2007

49、把一个表从一个表空间转移到另一个表空间
首先,使用下面的命令移动:
alter table table_name move tablespace tablespace_name;
然后,如果有索引的话必须重建索引:
alter index index_name rebuild tablespace tablespace_name;

50、在oracle中，把一个表空间的所有索引换到另一个表空间呢？

比如：例子1、在linux下操作的。
 1、建立一个select_index.sql，里面写入以下内容。
 SELECT 'ALTER index ' ||index_NAME|| ' REBUILD TABLESPACE INDEX_ISTQZDEV;' FROM USER_INDEXES;  
 2、执行sqlplus username/password @select_index.sql > index.log
 3、gawk '/ALTER index/ {print $0}' > alter_index.sql
 4、在执行一下 sqlplus username/password @alter_index.sql .
 5、表空间的所有索引都到另一个专门储存index的表空间里面去了
      
      例子2、将swust用户在SYSTEM表空间的全部索引移动到student表空间.
 1、用以下的内容编辑成一个脚本:SELECTINDEX.SQL
 /*
 * name: selectindex.sql
 * function: move the indexes of swust
 *     from system tablespace to student tablespace;
 * 
 */
 connect sys/syspassword as sysdba;
 set pagesize 20000
 set heading OFF
 spool /export/home/swust/rebuildindex.sql;
 select 'ALTER INDEX swust.' ||index_name|| ' REBUILD TABLESPACE student;'
       from all_indexes
       where wner='SWUST'and tablespace_name='SYSTEM';
 spool off
 2、在SQLPLUS中执行上述脚本,可在/export/home/swust/生成rebuilindex.sql
 3、在SQLPLUS中执行rebuildindex.sql, 可以将所有swust用户在SYSTEM表空间中的索引移动到student表空间

51、删除表空间 drop tablespace tablespace_name[including contents [and datafiles]]
    删除用户   drop user user_name cascade
    删除表的注意事项：在删除一个表中的全部数据时，须使用TRUNCATE TABLE 表名;因为用Drop TABLE，Delete * FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次Drop，Delete操作后，该TABLESPACE上百兆的空间就被耗光了。

52、从一个已知表建立另一个表 
 create table table_name as select * from user.table_name where....

53、返回表的几条记录
    比如返回前N条记录，select * from table_name where rownum    要返回第m－n条记录 select * from table_name where rownum         minus
         select * from table_name where rownum        （所以结果就为1－n－（1－m））为m－n条了。
    注意：只能用以上符号(,>=,=,Between...and，会提示this is stop key!的错误的。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件是不成立，因此也就查不到记录。 
    还有一种方法比如： select * from （ select rownum r from table_name where rownum<=n
     order by name ）where r>10；也可以返回表的m－n条记录
    这是因为它先排序了再选择的,也就是内存排序外层选，rownum是随着结构集生成的，一旦生成了就不会变化了，rownum是在查询集合产生的过程中产生的伪列。

54、在oracle中建立一个编号会自动增加的字段，以利于查询
    建立序列，create sequence checkup_no_seq nocycle maxvalue 99999999 start with 1；

55、查看对象的依赖关系：
    查看视图：dba_dependencies 记录了相关的依赖关系 查东西不知道要查看哪个视图时，可以在DBA_Objects里看，select object_name from dba_objects where object_name like '%swust%'(假如查看swust相关,注意一般都是大写哦)，然后DESC一下就大体上知道了。

56、rpad（str1，n，str2），将str1用str2从右端开始补充字符（字符的个数为n－str1字符的个数，这里n是总长度），通常用在创建，固定长度的记录时补充空格，例如select rpad（xxw，5，‘1’）from dual；结果为xxw11。
    initcap（char）将插入中每个单元的第一个字母改为大写，select initcap(today is sunday) from dual; 结果为Today Is Sunday；
    ceil（n）返回大于或者等于n的最近整数，floor（n）返回小于或者等于n的最近整数。round（n，m）将n四舍五入并保留小数点后的m位，power（m，n）返回m的n次幂。
    oracle中默认的纪年方式，在00－49前默认为20，在50－99前默认为19。在插入期间如果没有指定时间，就会默认为午夜用00:00:00表示。

57、内部连接时要返回在两个表中特定列的值匹配的记录，与之不同的是，外部连接的返回结果是从一个表中找出对应与另一个表，没有匹配值的记录。自连接用于作单个表内部的关联。表中的行被返回来连接到同一个表。
    union将返回两表中无任何重复的所有行，而union all可以允许返回两表中所有行，而intersect返回的是两个表中都有的行（但要注意使用它可能会产生严重的性能问题）。而minus返回第一个表有而第2个表中没有的行。

58、oracle数据库如何增加表空间大小
有两种方法，一种是为表空间增加数据文件
alter tablespace users add datafile 'c:\oracle\ora81\oradata\sid\xxw02.dbf' size 100M;
另一种方法是增加表空间原有数据文件尺寸：
alter database datafile 'c:\oracle\ora81\oradata\\sid\users.dbf' resize 1000M;

59、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
也可以已经知道b列值如：select b，max(a) from tablename where b=值 group by b order by 2；

60、显示文章、提交人和最后回复时间
select a.title,a.username,c.adddate from table a,(select max(adddate) adddate from table b where b.title=a.title) c

61、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

62、行列转换实例
实例一、 
 表xxw有三个字段  no --序列  num --检查项目  value  --值
 数据分别如下：
 no　　　  num'　　  value
 -------   --------  --------
 11　　　　　1　　　　0.50
 11　　　　　2　　　　0.21
 11　　　　　3　　　　0.25
 12　　　　　1　　　　0.24
 12　　　　　2　　　　0.30
 12　　　　　3　　　　0.22 
                            
 实现功能  创建视图时移动行值为列值
 create or replace view change_view
 as 
 select no 序号,
         sum(decode(num,1, value)) 检测项目1,
         sum(decode(num,2, value)) 检测项目2, 
         sum(decode(num,3, value)) 检测项目3 
 from xxw group by no;
 
 序号      检测项目1　　检测项目2　　检测项目3
 11　　　　　0.50　　　　0.21　　　　　0.25
 12　　　　　0.24　　　　0.30　　　　　0.22

实例二、
 表、视图结构转化
 现有一个商品销售表sale，表结构为：
 month　　　 char(6)　　　　　 --月份
 sell　　　　number(10,2)　　　 --月销售金额

 现有数据为：
 200001　　1000
 200002　　1100
 200003　　1200
 200004　　1300
 200005　　1400
 200006　　1500
 200007　　1600
 200101　　1100
 200202　　1200
 200301　　1300

 想要转化为以下结构的数据：
 year　　　char(4)　　　　　     --年份
 ------------   ---------------------         -------------------
 month1　　number(10,2)　　　--1月销售金额
 month2　　number(10,2)　　　--2月销售金额
 month3　　number(10,2)　　　--3月销售金额
 month4　　number(10,2)　　　--4月销售金额
 month5　　number(10,2)　　　--5月销售金额
 month6　　number(10,2)　　　--6月销售金额
 month7　　number(10,2)　　　--7月销售金额
 month8　　number(10,2)　　　--8月销售金额
 month9　　number(10,2)　　　--9月销售金额
 month10　 number(10,2)　　   --10月销售金额
 month11　 number(10,2)　　   --11月销售金额
 month12　 number(10,2)　　   --12月销售金额
 
 结构转化的SQL语句为：
 create or replace view
 new_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
 as
　　　　select 
　　　　substrb(month,1,4),
　　　　sum(decode(substrb(month,5,2),'01',sell,0)),
　　　　sum(decode(substrb(month,5,2),'02',sell,0)),
　　　　sum(decode(substrb(month,5,2),'03',sell,0)),
　　　　sum(decode(substrb(month,5,2),'04',sell,0)),
　　　　sum(decode(substrb(month,5,2),'05',sell,0)),
　　　　sum(decode(substrb(month,5,2),'06',sell,0)),
　　　　sum(decode(substrb(month,5,2),'07',sell,0)),
　　　　sum(decode(substrb(month,5,2),'08',sell,0)),
　　　　sum(decode(substrb(month,5,2),'09',sell,0)),
　　　　sum(decode(substrb(month,5,2),'10',sell,0)),
　　　　sum(decode(substrb(month,5,2),'11',sell,0)),
　　　　sum(decode(substrb(month,5,2),'12',sell,0))
　　　　from sale
　　　　group by substrb(month,1,4);
 体会：要用decode /group by/ order by/sign/sum来实现不同报表的生成

实例三、
 CASE应用
 no       depart        zhi       sex
        1         部门a        800        1 男
        2         部门b        900        2 女
        3         部门a        400        1 男
        4         部门d        1400       2 女
        5         部门e        1200       1 男
        6         部门f        500        1 男
        7         部门a        300        2 女
        8         部门d        1000       1 男
        9         部门d        1230       2 女
        10        部门b        2000       2 女
        11        部门c        2000       1 男
        12        部门b        1200       1 男

   SELECT depart as 部门,COUNT(no) as 人数,
     SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
     SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
     SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小于800元,
     SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)                    /*用*来实现功能，因为第一部分排除了800元的*/
         WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
         WHEN 800  THEN 1 ELSE 0 END)) as 从800至999,         /*注意别名不能以数字开头*/
     SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
         WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
         WHEN 1000 THEN 1 ELSE 0 END)) as 从1000元至1199元,
     SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
      +(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大于1200元
 FroM xxw 
 GROUP BY depart；

 部门     人数       男       女   小于800元 从800至999 从1000元至1199元   大于1200元
 部门a        3        2        1        2        1           0                  0
 部门b        3        1        2        0        1           0                  2
 部门c        1        1        0        0        0           0                  1
 部门d        3        1        2        0        0           1                  2
 部门e        1        1        0        0        0           0                  1
 部门f        1        1        0        1        0           0                  0

63、如果要查看一个表中的rowid伪列和所有的列，用select rowid，* from tablename；就会提示错误，因此可以用别名来实现
    如select rowid，a.* from tablename a；

64、提取当前日期的年月日，可以如下select extract（year/month/day from sysdate） from dual；

65、显示员工工资排名
 SELECT ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
    同样显示员工工资排名但是是按部门分类
 SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
 但是它存在一个缺陷就是，如果部门中有工资相同的那么sal_order不会相同只能继续排下区，如：1、2、3、4、····
        但是使用rank()就可以实现同排名如：
 SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
 它就弥补了这个缺陷排名就可以如：1、2、2、4、5、·····
    因此要找到每个部门工资最高的人(只选一个)
 SELECT DEPTNO, ENAME, SAL FROM
           (SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP)
 WHERE SAL_ORDER < 2;
    而要找到每个部门工资最高的人(包括并列第一) 
 SELECT DEPTNO, ENAME, SAL FROM
            (SELECT DEPTNO, ENAME, SAL, RANK() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP)
 WHERE SAL_ORDER < 2;

66、创建分区表类型如下：
CREATE TABLE Sales_Details
(
  Prod_Id VARCHAR2 (5),
  Sale_Date DATE NOT NULL,
  Cost NUMBER 
)
PARTITION BY RANGE (Sale_Date)
(
  PARTITION sd1 VALUES LESS THAN (TO_DATE('01/4月/2001', 'DD/MON/YYYY')),
  PARTITION sd2 VALUES LESS THAN (TO_DATE('01/7月/2001', 'DD/MON/YYYY')),
  PARTITION sd3 VALUES LESS THAN (TO_DATE('01/9月/2001', 'DD/MON/YYYY')),
  PARTITION sd4 VALUES LESS THAN (TO_DATE('01/1月/2002', 'DD/MON/YYYY'))
);

CREATE TABLE EmpDetail
(
EmpID VARCHAR2 (5),
EmpName VARCHAR2 (15),
Department VARCHAR2 (10)
)
PARTITION BY HASH (EmpID)
(
PARTITION Eid1,
PARTITION Eid2,
PARTITION Eid3
);

CREATE TABLE DEPARTMENT
(
DepID varchar2 (5),
Dept_Name varchar2 (20)
)
PARTITION BY LIST (Dept_Name)
(
Partition D1 values ('会计部'),
Partition D2 values ('管理层'),
Partition D3 values ('人力资源部')
);

CREATE TABLE BranchDetails1
(
Branch_ID varchar2 (5) not null,
Branch_name varchar2(10) not null,
Address varchar2(20)
)
PARTITION BY RANGE (Branch_Id)
SUBPARTITION BY HASH (Branch_name)
SUBPARTITIONS 6
(
PARTITION S1 values LESS THAN ('B005'),
PARTITION S2 VALUES LESS THAN ('B010'),
PARTITION S3 VALUES LESS THAN ('B015'),
PARTITION S4 VALUES LESS THAN ('B020')
);
同时可以制定保存的表空间。

67、重新编译视图alter view name_view compile;

68、查看可更新的列 SELECT * FROM user_updatable_columns WHERE table_name；

69、手机号(service_id)
代理商(dealer_id)
缴费次数(pays_count)
缴费金额(payfee_sum)
最后缴费时间(last_pay)
手机号和代理商为主键,一个手机号可能多条记录,查询要求:
找出一个手机号缴费次数最多的记录;
若两个的代理商最大缴费次数一样,取缴费金额多的记录;
若缴费次数和续费金额一样多,取缴费时间最大的记录.
有什么好的方法实现。
SELECT * FROM (SELECT a.*, rank() over(partition by service_id ORDER BY pays_count DESC, payfee_sum DESC, t_pay DESC) as rn FROM fl_wx_payfee_count) a WHERE rn = 1;

70、sys_context('context','attribute');用于返回应用上下文的特定属性值，其中context为应用上下文名，而attribute则用于制定属性名。如

SQL> select sys_context('userenv','session_user') "数据库用户"
  2  ,sys_context('userenv','os_user') "OS用户"
  3  from dual;

数据库用户          OS用户
------------------- ----------------------------
SCOTT               Administrator

记住两个转换函数一个scn_totimestamp(number)根据输入的scn值返回对应的大概日期时间，number用于指定scn值。
timestamp_scn(timestamp)根据输入的timestamp返回对应的scn值，其中timestamp用于指定日期时间。
greatest(expr1,expr2,.....)用于返回表达式中值最大的一个。least(expr1,expr2,....)用于返回表达式中值最小的一个。
nullif(expr1,expr2)用于比较表达式expr1和expr2，如果二者相等就返回null，否则就返回expr1。

71、sys_connect_by_path(column,char):只适用于层次查询，用于返回从根到节点的列值路径，例如：

SQL> select lpad(' ',2*(level-1))||sys_connect_by_path(ename,'/') "Path"
  2  from emp start with ename='SCOTT'
  3  connect by prior empno=mgr;

Path
--------------------------------------------------------------------------------
/SCOTT
  /SCOTT/ADAMS

72、UID用于返回当前会话所对应的用户的ID号

SQL> select uid from dual;

       UID
----------
        57

73、同理user就是用于返回当前会话所对应的数据库用户名。

SQL> select user from dual;

USER
------------------------------
SCOTT

74、USERENV(parameter) 用于返回当前会话上下文的属性信息。其中parameter可以区isdba、language、terminal、client_info。例如：
SQL> select userenv('isdba'),userenv('language'), userenv('terminal') from dual;

USERENV('ISDBA') USERENV('LANGUAGE')                   USERENV('TERMINAL')
---------------- ------------------------------------- -----------------------
FALSE            SIMPLIFIED CHINESE_CHINA.ZHS16GBK     67F6A7B4F468

75、vsize（expr）用于返回oracle内部存储expr的实际字节数。如果expr为null那么就返回为null，但是该函数只能在sql语句中使用，例如：

SQL> select ename,vsize(comm) from emp;

ENAME      VSIZE(COMM)
---------- -----------
SMITH      
ALLEN                2
WARD                 2

76、percent_rank（expr1，expr2，···）within group（order by expr1，expr2，·）
用于返回特定数值在统计级别中所占的比例。

SQL> select percent_rank(3000) within group(order by sal)
  2  percent from emp;

   PERCENT
----------
0.78571428

77、而percentile_cont（percent_expr）within group （order by expr），则正好相反，它是已经知道处于某个百分点，来确定数值。（连续分布模型确定）

SQL> select percentile_cont(0.6) within group(order by sal)
  2  value from emp;

     VALUE
----------
      2476

    还有percentile_disc（percent_expr）within group （order by expr），功能同上，但是它是（离散分布模型确定）

SQL> select percentile_disc(0.6) within group(order by sal)
  2  value from emp;

     VALUE
----------
      2695

78、rank（expr1，expr2，··）within group（order by expr1，expr2，···）则用于返回特定数值在统计数值后所占的等级。

SQL> select rank(800) within group(order by sal)
  2  rank from emp;

      RANK
----------
         1

80、在pl/sql块中嵌入select语句，格式注意
select select_list
 into (variable_name1, variable_name2,·····)
from table where condition;
注意：在pl/sql直接使用select into 语句，该语句必须要返回一条数据，并且只能返回一条数据，否则会触发例外的。

81、在pl/sql中插入数据，如果使用values子句插入的
insert into table(···) values( ···);注意每次只能插入一条数据，而且必须为表的主键列和not null 提供数据，较为麻烦，因此采用在pl/sql中使用子查询插入数据。如：
insert into table select * from table where condition；

82、在pl/sql中更新数据，可以使用表达式更新列值。
update table set column＝value where condition；只要注意满足类型长度约束即可。
也可以使用子查询来更新数据。例如：
update table set （column1，column2）＝（select ··· from table where condition） where column＝（select ··· from table where condition）；

83、在pl/sql中删除数据，可以使用变量来删除数据
delete from table where column＝变量，或者使用子查询来删除数据，如：
delete from table where column ＝（select ···· from table where condition）；

84、在pl/sql中执行上述4中操作时候，oracle会相应分配上下文区（context area），这是我们就可以使用游标指向上下文区的指针。
要注意sql游标属性，分为sql%found，sql%nofound，sql%rowcount，sql%isopen。
pl/sql在执行上述4个操作，oracle会隐含地打开游标，并且执行完后，又会隐含地关闭游标。sql%rowcount的功能是用于返回sql语句所作用的总计行数。

85、自定义pl/sql记录，语法如下：（定义某几列）
type type_name is record （
filed1，filed2，···）；
指定变量 type_name；
或者使用%rowtype定义记录变量，它可以基于表或者视图定义记录变量。使用它，记录成员得名称和类型与表或者视图列得名称和类型完全相同。（完全定义）

86、在select into语句中使用pl/sql，一种情况使用记录变量如：
declare
     type test is record
     ( ename emp.ename%type,
       sal emp.sal%type,
       deptno emp.deptno%type);
     emp_record test;
begin
     select ename,sal,deptno into emp_record from emp
     where empno=&no;
     dbms_output.put_line(emp_record.ename);
     dbms_output.put_line(emp_record.deptno);
end;
但是要注意选择列表中的列和表达式的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配才可以。（不能多不能少）
    二种是使用记录成员，这样选择列表后列和表达式的顺序可以任意指定，但是记录成员需要与之匹配。例如
declare
     type test is record
     ( ename emp.ename%type,
       sal emp.sal%type,
       deptno emp.deptno%type);
     emp_record test;
begin
     select ename,sal into emp_record.ename,emp_record.sal
     from emp where empno=&no;
     dbms_output.put_line(emp_record.ename);
     dbms_output.put_line(emp_record.sal);
end;

87、在insert语句中也可以使用pl/sql记录，
一种在values子句中使用记录变量
declare
     type test is record
     ( ename emp.ename%type,
       sal emp.sal%type,
       deptno emp.deptno%type);
     emp_record test;
begin
     select ename,sal into emp_record.ename,emp_record.sal
     from emp where empno=&no;
     dbms_output.put_line(emp_record.ename);
     dbms_output.put_line(emp_record.sal);
end;
二种在values种使用记录成员，列的顺序可以任意指定，但记录成员需要与之匹配。

88、在update语句种使用pl/sql记录
一种在set子句中使用记录变量，列的顺序、个数、类型必须要与之记录成员的顺序、个数、类型完全匹配。
declare
     dept_record dept%rowtype;
begin
     dept_record.deptno :=30;
     dept_record.dname := 'MANAGE';
     dept_record.loc :='CHINA';
     update dept set row=dept_record where deptno=30;
end;
另一种就是使用记录成员，那样列的顺序可以是任意指定，但记录成员需要与之匹配。

89、索引表，又称pl/sql表，这里注意高级语言数组的元素个数是有限制的，并且下标不能为负值，但是索引标的元素个数是没有限制的，而且下标可以为负值，但是索引表只能作为pl/sql复合数据类型使用，而不能作为表列的数据类型使用。如：
type typename is table of element_type
[not null] index by key_type；
identifier typename；

90、嵌套表，高级语言的数组的元素下标从0或者1开始，而且元素个数有限值，但是嵌套表下标是从1开始，没有元素个数限制，而且高级语言的数组元素值是顺序的，而嵌套表元素的数组元素值可以是稀疏的。注意：索引表不能作为表列的数据类型使用，但是嵌套表可以定义如下：
type typename is table of element_type；
identifier typename；
当使用嵌套表时，必须首先使用其构造方法初始化嵌套表。
declare
       type ename_type_name is table of emp.ename%type;
       ename_record ename_type_name;
begin
       ename_record :=ename_type_name('a','a','a'); 初始化很重要，要不报错
       select ename into ename_record(2) from emp
       where empno=&no;
       dbms_output.put_line(ename_record(2));
end;
在表列中使用嵌套表，例如：
SQL> create type phone_type is table of varchar(20);
  2  /

Type created

SQL> create table employ (
  2  id number(4),name varchar2(10),sal number(6,2),
  3  phone phone_type
  4  ) nested table phone store as phone_table;

Table created
往嵌套表中插入数据
begin
     insert into employ values(1,'scott',800,
     phone_type('123456','654321'));
end；

然后通过下面方式来查看嵌套表：
declare
       phone_table phone_type;
begin
       select phone into phone_table
       from employ where id=1;
       for i in 1..phone_table.count loop
           dbms_output.put_line('电话号码'||phone_table(i));
       end loop;
end;

更新嵌套表的数据，使用构造方法初始化该变量就可以了。
declare
       phone_table phone_type:=phone_type('13758',
       '45789','65846');
begin
       update employ set phone=phone_table
       where id=1;
end;

91、变长数组（varray），它可以作为表列的数据类型使用。与高级语言数组非常类似，元素下标从1开始，而且有限制。如：
type typename is varray(size_limit) of element_type [not null];
identifier typename;
使用varray元素时，必须要使用其构造方法初始化varray元素。但是不用全部初始化，用到那就初始化到那个地方
declare
      type ename_table_type is varray(20) of emp.ename%type;
      ename_record ename_table_type:=ename_table_type('a');
begin
       select ename into ename_record(1) from emp  如果要用到2，就要初始化到2
       where empno=&no;
       dbms_output.put_line('雇员:'||ename_record(1));
end;

在表中使用varray同上类似如：
SQL> create type phone_type is varray(20) of varchar(20);
  2  /
SQL> create table employ (
  2  id number(4),name varchar2(10),sal number(6,2),
  3  phone phone_type
  4  );

92、可以将pl/sql记录和pl/sql集合结合起来使用，从而能够处理多行多列的数据例如：
declare
      type ename_table_type is table of emp%rowtype
      index by binary_integer;
      ename_table ename_table_type;
begin
       select * into ename_table(1) from emp
       where empno=&no;
       dbms_output.put_line(ename_table(1).ename);
end;

93、我们也可以通过以上来实现多级集合这样来实现多维数组功能，就是可以在一个varray类型中再继续嵌套一个varray类型。
declare
      type first_varray_type is varray(10) of int;
      type second_varray_type is varray(10) of first_varray_type;
      num second_varray_type:=second_varray_type(
      first_varray_type(1,2,3),
      first_varray_type(4,5));
begin
       dbms_output.put_line('显示二维数组元素');
       for i in 1..num.count loop   注意上限的表达形式
           for j in 1..num(i).count loop   注意上限的表达形式
               dbms_output.put_line('num('||i||','||j||')='||num(i)(j));注意这种在字符串中显示i，j的方法。
           end loop;
       end loop;
end;
同理对于多级嵌套表和多级索引表如下：
declare
      type first_varray_type is table of int;
      type second_varray_type is table of first_varray_type;
      num second_varray_type:=second_varray_type(
      first_varray_type(1,2,3),
      first_varray_type(4,5));

declare
      type first_varray_type is table of int
      index by binary_integer;
      type second_varray_type is table of first_varray_type
      index by binary_integer;
      num second_varray_type;

94、集合方法，它是oracle提供用于操纵集合变量的内置函数或者过程，其中exists，count，limit，first，next，prior，next是函数，而extend，trim，delete则是过程。集合方法调用如下 collection_name.method_name[(parameters)]，注意集合方法只能在pl/sql语句中使用，而不能在sql语句中调用，另外集合方法中extend和trim只适用于嵌套表和varray。
exists用于确定集合元素是否存在。
count用于返回当前集合变量中的元素总个数。
limit用于返回集合元素的作大个数。
first和last用于返回集合变量第一个元素的下标和最后一个元素的下标。
prior和next用于返回当前集合元素的前一个元素的下标和后一个元素的下标。
extend用于扩展集合变量的尺寸，并为他们增加元素。有extend，extend(n),extend(n,i)形式，第一个extend为集合变量添加一个null元素，第二个extend(n)为集合变量添加n个null元素，而第三个extend(n,i)则为集合变量添加n个元素（其中元素值与第i个元素相同）。
trim用于从集合尾部删除元素，有trim和trim(n)两种形式，trim用于从集合尾部删除一个元素，而trim(n)用于从集合尾部删除n个元素。
delete用于删除集合元素，同样有delete，delete(n),delete(m,n)三种形式，其中delete用于删除所有元素，delete(n)用于删除集合变量的第n个元素，而delete(m,n)用于删除结合变量从m到n的所有元素。

95、集合赋值，当使用嵌套表和varray时，通过执行insert，update，fetch，select，赋值语句，用户可以将一个集合的数据赋值给另一个集合，在给嵌套表时还可以使用set，multiset union，multiset intersect，multiset except等集合操作符号，其中
set操作符用于取消嵌套表中的重复值。
multiset union用于取得两个嵌套表的并集默认会包含重复值（带distinct操作符可以取消重复结果）。如：集合1 multiset union distinct 集合2
multiset intersect用于取得两个嵌套表的交集。
multiset except用于取得两个嵌套表的差集。

将一个集合的数据赋值给另一个集合，使用赋值语句(:=)将源集合中的数据赋值给目标集合时，会自动清除目标集合原有的数据，将源集合的数据赋值给该目标集合。注意：当进行集合赋值时，源集合和目标集合的数据类型必须完全一致，如果集合元素数据类型一致，但集合类型不一致，那也不能进行赋值。
declare
       type first_varray_type is varray(5) of varchar2(10);
       type second_varray_type is varray(5) of varchar2(10);
       test1 first_varray_type;
       test2 second_varray_type;
begin
       test1 :=first_varray_type('scott','aa');
       test2 :=test1;
end;
这就会提示错误。
给集合赋值null值时，可以使用delete和trim，也可以将一个null集合变量赋值给目标集合变量。
dbms_output.put_line打印出换行，而dbms_output.put不换行。

96、比较集合，可以使用cardinality,submultiset of,member of,is a set,is empty等来比较集合变量。其中

cardinality用于返回嵌套表变量的元素个数。和count的功能几乎相同

submultiset of 用于确定一个嵌套表是否为另一个嵌套表的子集。
如：if test1 submultiset of test2 then···检测test1是否是test2的子集。

member of 用于检测特定数据是否为嵌套表的元素。
如：if var member of test1 then····检测变量var是不是test1的元素。

is a set用于检测嵌套表是否包含重复的元素值。
如：if test is a set then····检测test里面包不包含重复值。如果为真则表明无重复值

is empty用于检测嵌套表是否为null。
如：if test_table is empty then····。

比较两个嵌套表是否相同，如if test_table1 比较操作符 test_table2 then····只能用＝或者!＝,而且只能用于嵌套表。

97、批量绑定，举例，首先是不批量插入数据例如：
declare 
      type id_record_type is table of number(6)
      index by binary_integer;
      type name_record_type is table of varchar2(10)
      index by binary_integer;
      id_record id_record_type;
      name_record name_record_type;
      start_time number(10);
      end_time number(10);
begin
      for i in 1..5000 loop
          id_record(i) :=i;
          name_record(i) :='test'||to_char(i);
      end loop;
      start_time :=dbms_utility.get_time;
      for i in 1..id_record.count loop
          insert into test values (id_record(i),name_record(i));
      end loop;
      end_time :=dbms_utility.get_time;
      dbms_output.put_line('总计时间'||to_char((end_time-start_time)/100)||'秒');
      commit;
end;
相当于执行了5000次insert into 指令所以耗费时间较长，在来看批量输入只需要使用一条insert语句就可以输入5000行数据，因此时间断很多。如：
begin
      for i in 1..5000 loop
          id_record(i) :=i;
          name_record(i) :='test'||to_char(i);
      end loop;
      start_time :=dbms_utility.get_time;
      forall i in 1..id_record.count  很关键一条语句。
          insert into test values (id_record(i),name_record(i)); 
      end_time :=dbms_utility.get_time;
      dbms_output.put_line('总计时间'||to_char((end_time-start_time)/100)||'秒');
      commit;
end;
因此我们可以看出使用批量绑定的速度要远远优于不使用批量绑定的，批量绑定是使用bulk collect子句和forall语句完成的，其中：

98、forall语句只适用于执行批量的dml操作，可以是连续的元素也可以是不连续的。
第一种形式 forall index in lower_bound..upper_bound sql_statement;
例如：forall i in 1..id_record.count  其中i必须是连续的。
      insert into test values (id_record(i),name_record(i));
      update test set name=name_record(i) where id=id_record(i);
      delete from test where id_record(i);

第二种形式 forall index in indices of collection [between lower_bound and upper_bound] sql_statement;
例如：forall i in indices of id_record   这个i可以是不连续的。
      delete from test where id=id_record(i);

第三种形式 forall index in values of index_collection sql_statement;该子句用于从其他结合变量中取得集合下标（index）值。
例如：forall i in values of index_point 其中index_point里面记录了i的值
      insert into test values(id_record(i),name_record(i));

99、sql%bulk_rowcount，它是转为forall语句提供，就是取得在执行批量操作的时候第i个元素所作用的行，如：dbms_oupput.put_line('第一个元素作用行数'||sql%bulk_rowcount(1))。

100、bull collect用于取得批量数据，该子句只能用于select语句，fetch语句和dml返回子句中。用法：....bulk collect into collection_name1,collection_name2···.....。

第一种在select into中使用，可以一次将select语句的多行结果检索到集合变量中。
如：select * bulk collect into emp_table from emp where deptno＝&no; (很有用要注意····)

第二中在dml的返回子句中使用，通过returning来实现。
如：delete from emp where deptno=&no returning ename bulk collect into ename_table;

101、pl/sql包含隐含游标和显示游标两种游标类型，其中隐含游标用于处理select into和dml语句，而显示游标则专门用于处理select语句返回的多行数据。
显示游标
第一步：定义游标 cursor cursor_name is select_statement;
第二步：打开游标 open cursor_name;
第三步：提取数据 fetch cursor_name into varable1,varable2,····;
   fetch cursor_name bulk collect into collect1,collect2,····;
第四步：关闭游标 close cursor_name；

显示游标的属性%isopen,%found,%notfound,%rowcount。其中%rowcount用于返回当前行为止已经提取到的实际行数。

102、第一种使用fetch...into语句
declare 
      cursor emp_cursor is
             select ename,sal from emp where deptno=10;
      v_ename emp.ename%type;
      v_sal emp.sal%type;
begin
      open emp_cursor;
      loop
          fetch emp_cursor into v_ename,v_sal;
          exit when emp_cursor%notfound;
          dbms_output.put_line(v_ename||' '||v_sal);
      end loop;
      close emp_cursor;
end;
fetch...into每次只能处理一行数据。要处理多行就要通过loop循环来实现。

第二种，使用fetch.....bulk collect into来提取数据。
declare 
      cursor emp_cursor is
             select ename from emp where deptno=10;
      type ename_table_type is table of varchar2(10);
      ename_table ename_table_type;
begin
      open emp_cursor;
      fetch emp_cursor bulk collect into ename_table;
      for i in 1..ename_table.count loop
          dbms_output.put_line(ename_table(i));
      end loop;
      close emp_cursor;
end;
这样一次就可以提取出所有的数据。

第三种 使用fetch....bulk collect into...limit提取，默认情况下会提取结果集的所有数据。如果结果集含有大量数据，并且使用varray集合变量接收数据，那么可能需要限制每次提取的行数。
如：fetch emp_cursor bulk collect into ename_table
    limit rows；每次提取rows行数据。

第四种 就是使用游标属性来实现。

第五种 就是基于游标的记录变量，使用记录变量存放游标数据
declare 
      cursor emp_cursor is
             select ename,sal from emp;
      emp_record emp_cursor%rowtype;
begin
      open emp_cursor;
      loop
          fetch emp_cursor into emp_record;
          exit when emp_cursor%notfound;
          dbms_output.put_line(emp_record.ename||' '||emp_record.sal);
      end loop;
      close emp_cursor;
end;

103、参数游标就是指带有参数的游标，语法：
cursor cursor_name(parameter_name datatype) is select_statement;
但要注意，定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的where子句中引用该参数，否则就失去定义参数游标的意义。
declare 
      cursor emp_cursor(no number) is
             select ename from emp where deptno=no; where子句中必须引用参数
      v_ename emp.ename%type;
begin
      open emp_cursor(10); 指定参数
      loop
          fetch emp_cursor into v_ename;
          exit when emp_cursor%notfound;
          dbms_output.put_line(v_ename);
      end loop;
      close emp_cursor;
end;

104、如果要通过游标更新或者删除数据，必须有for update子句，语法如：
cursor cursor_name(parameter datatype) is select_statement for update [of column] [nowait]; 这里的nowait子句用于指定不等待锁。在提取了游标数据之后，为了更新或者删除当前游标行数据，必须在update或delete中引用where current of 子句例如：
update table_name set column=.... where current of cursor_name;
delete from table_name where current of cursor_name;例如
declare 
      cursor emp_cursor is
             select ename,sal from emp
      for update; 
      v_ename emp.ename%type;
      v_sal emp.sal%type;
begin
      open emp_cursor;
      loop
          fetch emp_cursor into v_ename,v_sal;
          exit when emp_cursor%notfound;
          if v_sal<200 then
          update emp set sal=sal+2000 where current of emp_cursor;
          end if;
          dbms_output.put_line(v_ename||v_sal);
      end loop;
      close emp_cursor;
end;

使用of子句在特定表加共享锁，如果游标子查询设计到多张表，那么在默认情况下会在所有修改表上加行共享锁。为了在特定的表上下，可以使用of子句，如下：
declare 
      cursor emp_cursor is
             select ename,sal,dname,emp.deptno 
             from emp,dept
             where emp.deptno=dept.deptno
             for update of emp.deptno;  注意共享了deptno这一列
      emp_record emp_cursor%rowtype;
begin
      open emp_cursor;
      loop
          fetch emp_cursor into emp_record;
          exit when emp_cursor%notfound;
          if emp_record.deptno=30 then
          update emp set sal=sal+2000 where current of emp_cursor;
          end if;
          dbms_output.put_line(emp_record.ename||' '||emp_record.sal
                               ||' '||emp_record.dname);
      end loop;
      close emp_cursor;
end;

如果在后面继续指定nowait的话，即时当前会话已经加了锁，但是也可以忽略，避免了等待。

105、游标的for循环，语法：
for record_name in cursor_name loop
statement1;statement2;····
end loop;
在执行循环体内容之前，oracle会隐含地打开游标，并且每循环一次提取一次数据，在提取了所有数据之后，会自动退出循环并隐含地关闭游标。
一种情况：
declare 
      cursor emp_cursor is
             select ename,sal from emp;   
      emp_record emp%rowtype;
begin
      for emp_record in emp_cursor loop
          dbms_output.put_line('第'||emp_cursor%rowcount||

原文地址：http://blog.oracle.com.cn/html/80/266780-26690.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26794549/viewspace-720242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26794549/viewspace-720242/