文档说明：该文档编辑环境为PLSQL DEVELOPER sql窗口，故文档优点在于，可以直接拷贝整个该文档（标题除外）到sql窗口即可直接运行，方便进行测试和修改。页数较多，但以代码为主，文字较简洁。考虑文档实用性未对文档做过多的格式编排。
最佳观看效果SQL窗口！
目录
专题六：动态sql（主要是本地动态sql，带参数）...35
专题一：伪列（ROWID，ROWNUM）
--2013-06-29
--伪列（ROWID，ROWNUM）-----------------------------------------------------------------------------------------------
--感性认识，输出这两个伪列，看其形式
selectrowid,rownumfrom scott.emp;
selectrowid,rownumfrom scott.empwhererownumbetween1and5;
select *from emp;
-----------------------------------------ROWNUM----------------------------------------
--rownum主要实现分页显示
--基本用法见下
--1.rownum 对于某特定值得查询
--rownum=1可以作为限定条件，但是rownum=n(n>1)不行
--因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，
--所以无法查到rownum = n（n>1的自然数）。
--成功
select *from emp whererownum =1;
--不成功
select *from emp whererownum =3;
--成功，显示前两条数据。
select * from empwhererownum !=3;
--2.rownum 对于大于某值得查询
--如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，
--原因是由于rownum是一个总是从1开始的伪列，Oracle认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录
--falied 原因同上
select *from emp whererownum >3;
--解决方法：使用子查询
--成功
select *from (selectrownumno, empno, enamefrom emp ) whereno >2;
--3.rownum 对于小于某值得查询
--如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n（(n>1的自然数）
--的条件认为是成立的，所以可以找到记录
--falied 原因同上
select *from emp whererownum <3;
--select * from emp where rownum != 3;
--解决方法：使用子查询
select * from (selectrownumno, empno, ename from emp )whereno >2;
--rownum的编号，是按照插入数据库的顺序编号的
--下面这个例子可以看出
selectrownum, salfrom emp orderby empnodesc;
----------------------------------ROWNUM-------------------------------------------------
-------------------------------------ROWID--------------------------------------------------
--概念：
--rowid就是唯一标志记录物理位置的一个id，rowid由file#+block#+row#组成，--占用6个bytes的空间，
--10 bit 的 file#，22bit 的 block#，16 bit 的 row#。
--分类：
--物理ROWID和逻辑ROWID。
--rowid的使用是记录的详细地址，用于快速定位数据
--格式：
--数据对象编号，文件编号，块编号，行编号（OOOOOO FFF RRR0)占10bytes空间
--1.快速删除重复的行
createtable temp_empasselectdistinct *from emp;
truncatetable emp;
insertinto empselect * from temp_emp;
--查找大量重复记录
select empnofrom emp groupby empnohavingcount(*)>1;
select *from emp whererowidin(selectMin(rowid)from emp groupby empno);
--查找少量重复数据
select *from emp a whererowid<>(selectmax(rowid)from emp where empno= a.empno);
--删除重复记录
delete empwhere empno in (select empnofrom emp groupby empnohavingcount(*)>1)
androwidnotin(selectmin(rowid)from emp groupby empnohavingcount(*)>1);
--根据ROWID来实现分页
--解释如下
--1.将表按empno降序排列
--2.检索出前10条
--3.检索出8到10这几条的rowid
--4.检索出rowid在上一步的结果中的数据并且按照cid降序排列
select *from emp whererowidin(select rid from (selectrownum rn,rid
from(selectrowid rid,deptnofrom emp orderby ciddesc)
whererownum<10)where rn>8)orderby deptnodesc;
---------------------------------------ROWID--------------------------------------------
专题二：索引（单值，多值）
--2013-06-30
--索引（单列、多列）
--使用到的查询语句
--1.查看一下sql执行计划来查看索引有没有起作用
select *fromglobal_name;
--2.查看索引个数和类别
select *from user_indexeswhere table_name='emp';
--3.查看索引被索引的字段
select *from user_ind_columnswhere index_name=upper('&index_name');
--作用概述
--通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。
--如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。
--关于索引的一些注意事项
--关于限制索引
--在SQL中使用一些限制条件导致所以没有被使用的情况。以下下面讨论一些常见的问题：
--1.使用不等于操作符（<>、!=）
--下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
selectid,namefrom testindexwhereid <>99999;
--解决方案，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
--特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
selectid,namefrom testindex
wherecust_rating <9999or cust_rating >9999;
--2 使用IS NULL或IS NOT NULL带来的问题
--使用IS NULL或IS NOT NULL同样会限制索引的使用
--3 使用函数
--如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
--下面的查询不会使用索引（只要它不是基于函数的索引）
selectempno,ename,deptnofromemp wheretrunc(hiredate)='01-MAY-81';
--解决方案如下，这样就可以通过索引进行查找。
select empno,ename,deptnofrom emp wherehiredate<(to_date('01-MAY-81')+0.9999);
--通过sql执行计划，查看索引是否作用的一些设置
altersessionset statistics_level='ALL';
select *fromtable(dbms_xplan.display);
setautotracetraceexplain;
select *from emp;
--创建表
createtable testindex(
idinteger,
namevarchar2(20)
);
--向表中插入数据100万条，耗时36.7s
declare inumber;
begin
for i in1 ..1000000loop
insertinto testindexvalues (i,to_char(i));
endloop;
end;
select *from testindex;
selectcount(*)from testindex;
--创建索引
createindex idindexon testindex(id);--耗时2.969s
selectobject_name,object_typefrom user_objects;
--查看索引信息 创建成功
select *from user_indexeswhere table_name='testindex';
select *from user_ind_columnswhere index_name=upper('&index_name');
select *from testindex whereid =999999; --响应时间0.078s
dropindex idindex; --删除索引
select *from testindex whereid =999999; --响应时间0.094s
--重复执行以上查询，可以看出索引节省时间
-------------------------sqlplus做----------------------------
创建表：
SQL> createtable dex (idint,sexchar(1),namechar(10));
Table created.
向表中插入10000条数据
SQL> begin
for iin1..10000
loop
insertinto dexvalues(i,'M','chongshi');
endloop;
commit;
end;
/
PL/SQLprocedure successfullycompleted.
查看表记录
select *from dex;
创建索引：
SQL> createindex dex_idx1on dex(id);
Index created.
查看创建的表与索引
SQL> selectobject_name,object_typefrom user_objects;
OBJECT_NAME OBJECT_TYPE
---------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
SQL> createindex dex_index2on dex(sex,name);
Index created.
SQL> select object_name,object_typefrom user_objects;
OBJECT_NAME OBJECT_TYPE
---------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX
-------------------------sqlplus做----------------------------
专题三：分区表
--2013-07-6
--分区表
--概念及作用
--Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，
------放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段
------（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过
------在查询时直接指定分区的方法来进行查询。
--何时分区
---When to Partition a Table什么时候需要分区表，官网的2个建议如下：
------1）Tables greater than 2GB should always be considered forpartitioning.（即：表的大小超过2GB）
------2）Tables containing historical data, in which new data isadded into the newest partition.
------A typical example is a historical table where onlythe current month's data is updatable
------and the other 11 months are read only.（即：对于新旧数据分离时可采用。典型例子只对月前数据更新，可采用分区表）
--优点：
------（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
------（2）可以对单独的分区进行备份和恢复；
------（3）可以将分区映射到不同的物理磁盘上，来分散IO；
------（4）提高可管理性、可用性和性能。
--类型：
------（1）范围分区（range）；
------（2）哈希分区（hash）；
------（3）列表分区（list）；
------（4）范围－哈希复合分区（range-hash）；
------（5）范围－列表复合分区（range-list）
--一.Range分区：
--Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所
------在的range分区中。
------如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，
------以及分区的范围值。在按时间分区时，如果某些记录暂无法预测范围，可以创建
------maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
--如：
createtable test1 (idnumber,timedate)partitionbyrange (time)
(
partition p1valueslessthan (to_date('2010-10-1','yyyy-mm-dd')),
partition p2valueslessthan (to_date('2010-11-1','yyyy-mm-dd')),
partition p3valueslessthan (to_date('2010-12-1','yyyy-mm-dd')),
partition p4valueslessthan (maxvalue)
)
selectsysdatefrom dual;
insertinto test1values(2, to_date('2010-5-30','yyyy-mm-dd'));
insertinto test1values(3, to_date('2012-5-30','yyyy-mm-dd'));
insertinto test1values(4, to_date('2013-5-30','yyyy-mm-dd'));
--以下来自sqlplus
SQL> select * from test1wheretime > to_date('2010-12-1','yyyy-mm-dd');
执行计划：会直接查询第四部分，从而加快了访问速度。其它操作与此相仿：insert update等
----------------------------------------------------------
Planhashvalue:3115573686
--------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 7 | 154 | 3 (0)|00:00:01 |
| |
| 1 | PARTITIONRANGESINGLE| | 7 | 154 | 3 (0)|00:00:01 |
4 | 4 |
|* 2 | TABLEACCESSFULL | TEST1 | 7 | 154 | 3 (0)|00:00:01 |
4 | 4 |
--以上来自sqlplus--------------------------------------------------------------
--二.Hash分区：
------对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将
------表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制
------也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
--如：--在这里，如果需要可以为每个分区指定表空间。
createtable test2
(
transaction_id numberprimarykey,
item_id number(8)notnull
)
partitionbyhash(transaction_id)
(
partition part_01,
partition part_02,
partition part_03
);
select *from test2;
insertinto test2values(1,2);
insertinto test2values(2,7);
insertinto test2values(3,6);
insertinto test2values(4,999);
--三.List分区：
------List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时
------指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。在分区时必须确定分区列可能存在的值，一旦
------插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存
------储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
--在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
--如：
createtable test3
(
id varchar2(15byte) notnull,
code varchar2(4byte)
)
partitionbylist (code)
(
partition t_list025values ('025'),
partition t_list372values ('372') ,
partition t_list510values ('510'),
partition p_othervalues (default)
)
select *from test3;
--四.组合分区：
------如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，
------即组合分区的方式。组合分区呢在10g中有两种：range-hash，range-list。注意顺序，根分区只能是range分区，
------子分区可以是hash分区或list分区。
--如：range--hash
createtable test4
(
transaction_id numberprimarykey,
transaction_date date
)
partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)
subpartitions3--这里用来指定分区从属的表空间 store in(tablespace01,tablespace02,tablespace03)
(
partition part_01valueslessthan(to_date('2009-01-01','yyyy-mm-dd')),
partition part_02valueslessthan(to_date('2010-01-01','yyyy-mm-dd')),
partition part_03valueslessthan(maxvalue)
);
--如：range--hash
createtable emp_sub_template (
deptno number,
empname varchar(32),
grade number
)
partitionbyrange(deptno)subpartitionbyhash(empname)
subpartitiontemplate
(subpartition a1,--这里用于指定表空间格式：tablespace ts2,
subpartition a2,
subpartition a3,
subpartition a4
)
(partition p1valueslessthan (1000),
partition p2valueslessthan (2000),
partition p3valueslessthan (maxvalue)
);
--如：range--list
createtablequarterly_regional_sales
(deptno number, item_novarchar2(20),
txn_date date, txn_amountnumber, state varchar2(2))
tablespace ts4
partitionbyrange (txn_date)
subpartitionbylist (state)
(partition q1_1999valueslessthan (to_date('1-apr-1999','dd-mon-yyyy'))
(subpartition q1_1999_northwestvalues ('or','wa'),
subpartition q1_1999_southwestvalues ('az','ut', 'nm'),
subpartition q1_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q1_1999_southeastvalues ('fl','ga'),
subpartition q1_1999_northcentralvalues ('sd','wi'),
subpartition q1_1999_southcentralvalues ('ok','tx')
),
partition q2_1999valueslessthan ( to_date('1-jul-1999','dd-mon-yyyy'))
(subpartition q2_1999_northwestvalues ('or','wa'),
subpartition q2_1999_southwestvalues ('az','ut', 'nm'),
subpartition q2_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q2_1999_southeastvalues ('fl','ga'),
subpartition q2_1999_northcentralvalues ('sd','wi'),
subpartition q2_1999_southcentralvalues ('ok','tx')
),
partition q3_1999valueslessthan (to_date('1-oct-1999','dd-mon-yyyy'))
(subpartition q3_1999_northwestvalues ('or','wa'),
subpartition q3_1999_southwestvalues ('az','ut', 'nm'),
subpartition q3_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q3_1999_southeastvalues ('fl','ga'),
subpartition q3_1999_northcentralvalues ('sd','wi'),
subpartition q3_1999_southcentralvalues ('ok','tx')
),
partition q4_1999valueslessthan ( to_date('1-jan-2000','dd-mon-yyyy'))
(subpartition q4_1999_northwestvalues ('or','wa'),
subpartition q4_1999_southwestvalues ('az','ut', 'nm'),
subpartition q4_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q4_1999_southeastvalues ('fl','ga'),
subpartition q4_1999_northcentralvalues ('sd','wi'),
subpartition q4_1999_southcentralvalues ('ok','tx')
)
);
--四.分区表的索引
------分区索引分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理,而全局索引比较快。
------Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。
------分区索引分2类：有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引:
--4.1 Local 本地索引一定是分区索引
--对于local索引，当表的分区发生变化时，索引的维护由Oracle自动进行。
--注意事项：
--（1）局部索引一定是分区索引，分区键等同于表的分区键。
--（2）前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
--（3）局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
--（4）局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
--（5）位图索引必须是局部分区索引。
--（6）局部索引多应用于数据仓库环境中。
--（7） B树索引和位图索引都可以分区，但是HASH索引不可以被分区。
--如：
createindex index_local_idon test1(id)local;
--和下面SQL效果相同，因为local索引就是分区索引：
createindex index_local_idon n test1(id)
local (
partition t_list556
partition p_other
)
createindex index_local_id2on test1(time)local;
selectindex_name,table_name,partitioning_type,locality,ALIGNMENT
from user_part_indexeswhere table_name='test1'
--4.2 Global索引
------对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。全局分区索引只能是B树索引，
--到目前为止(10gR2)，oracle只支持有前缀的全局索引。
--注意事项：
--（1）全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
--（2）全局索引可以依附于分区表；也可以依附于非分区表。
--（3）全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
--（4）全局索引多应用于oltp系统中。
--（5）全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。
--（6） oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
--（7）表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
--Oracle只支持2中类型的全局分区索引：
--range partitioned和 Hash Partitioned.
--示例1全局索引，全局索引对所有分区类型都支持：
createindex ix_custaddr_global_idon custaddr(id)global;
--示例2：全局分区索引，只支持Range分区和Hash 分区：
--（1）创建2个测试分区表：
createtable pdba (idnumber,timedate)partitionbyrange (time)
(
partition p1valueslessthan (to_date('2010-10-1','yyyy-mm-dd')),
partition p2valueslessthan (to_date('2010-11-1','yyyy-mm-dd')),
partition p3valueslessthan (to_date('2010-12-1','yyyy-mm-dd')),
partition p4valueslessthan (maxvalue)
);
createtable Thash
(
idnumberprimarykey,
item_id number(8)notnull
)
partitionbyhash(id)
(
partition part_01,
partition part_02,
partition part_03
);
--（2）创建分区索引
--示例2：全局分区索引
createindex i_id_globalon PDBA(id)global
partitionbyrange(id)
(partition p1valueslessthan (200),
partition p2valueslessthan (maxvalue)
);
--索引已创建。
createindex i_time_globalon PDBA(time)global
partitionbyrange(time)
(partition p1valueslessthan (TO_DATE('2010-12-1','YYYY-MM-DD')),
partition p2valueslessthan (maxvalue)
);
--索引已创建。
--有前缀的分区索引
selectindex_name,table_name,partitioning_type,locality,ALIGNMENT
fromuser_part_indexeswheretable_name='PDBA';
index_name table_name
partition localialignment
------------------------------ ---------- --------------- ------------
i_id_global pdba range global prefixed
i_time_global pdba range global prefixed
专题四：大对象
--2013-07-06
--大对象
--概念及其分类
--在oracle中，有4个大对象（lobs）类型可用，分别是blob,clob,bfile,nclob。
-- 下面是对lob数据类型的简单介绍。
-- l blob:二进制lob，为二进制数据，最长可达4GB，存贮在数据库中。
-- 2 clob:字符lob,字符数据,最长可以达到4GB,存贮在数据库中。
-- 3 bfile:二进制文件;存贮在数据库之外的只读型二进制数据，最大长度由操作系统限制。
-- 4 nclob：支持对字节字符集合(nultibyte characterset)的一个clob列。
、
--oracle中可以用多种方法来检索或操作lob数据。通常的处理方法是通过dbms_lob包。
--下以该方法演示
--一、在oracle开发环境中我们可以用dbms_lob包来处理！dbms_lob包功能强大，简单应用。既可以用来读取
--内部的lob对象，也可以用来处理bfile对象。但处理两者之间，还有一点差别。处理内部lob对象（blob,
--clob）时，可以进行读和写，但处理外部lob对象bfile时，只能进行读操作，写的操作可以用pl/sql处理。
--另外用sql也可以处理lob，但要注意sql仅可以处理整个lob，不能操作lob的数据片。
--对于pl/sql，下面介绍一种技巧，用动态的pl/sql语句处理clob对象来传替表名！
--在dbms_lob包中内建了read(),append,write()，erase()，copy(),getlength()，substr()等函数，下面加以演示
--可以很方便地操作lob对象。
-- 动态PL/SQL,对CLOB字段操作可传递表名table_name，表的唯一标志字段名field_id，clob字段名field_name记录号v_id，
-- 开始处理字符的位置v_pos，传入的字符串变量v_clob
-- 修改CLOB的PL/SQL过程：updateclob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
--l 用法说明：
--在插入或修改以前，先把其它字段插入或修改，CLOB字段设置为空empty_clob()，
--然后调用以上的过程插入大于2048到32766个字符。
--如果需要插入大于32767个字符，编一个循环即可解决问题。
--查询CLOB的PL/SQL函数：getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
-- l 用法说明：
-- 用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;
-- 可以从CLOB字段中取2000个字符到partstr中，
-- 编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
--二，大对象数据的录入
--2.1，声明LOB类型列
--创建所需表格
droptable tLob
CreateTable tLob (
noNumber(4),
nameVarChar2(10),
resumeCLob,
photo BLob,
recordBFile
)
Lob (resume,photo)StoreAs (
--Tablespace bigbig --指定存储的表空间这里使用默认表空间
Chunk6k --指定数据块大小
DisableStorageInRow
);
INSERTINTO tlob
VALUES (2,
'BIG',
'CLOB大对象列',
EMPTY_BLOB (),
BFILENAME ('D:','Winter.jpg')
);
--2，操作大对象数据的过程
-- 操作会改变数据库中原有数据，需要加上Updata锁锁上指定数据列，修改完后提交事务。
--①：DBMS_LOB.Write()：
--将指定数量的数据写入LOB的过程。
-- DBMS_LOB.Write(被写入LOB,写入长度(指写入LOB数据)，写入起始位置(指被写入LOB)，写入LOB数据);
DECLARE
varC CLOB;
vWStr VARCHAR2 (1000);
vStrt NUMBER (4);
LN NUMBER (4);
BEGIN
vWStr := 'CLOB11';
LN := LENGTH (vWStr);
vStrt := 5;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.Write (varC,
LN,
vStrt,
vWStr);
DBMS_OUTPUT.put_line ('改写结果为: ' ||varC);
COMMIT;
END;
--②：DBMS_LOB.Append()：
--将指定的LOB数据追加到指定的LOB数据后的过程。
-- DBMS_LOB.Append(LOB数据，LOB数据)；
DECLARE
varC CLOB;
vAStr VARCHAR2 (1000);
BEGIN
vAStr := '，这是大对象列';
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.Append (varC, vAStr);
COMMIT;
DBMS_OUTPUT.put_line ('追加结果为: ' ||varC);
END;
--③：DBMS_LOB.Erase()：
--删除LOB数据中指定位置的部分数据的过程；
-- DBMS_LOB.Erase(LOB数据，指定删除长度,开始删除位置)；
DECLARE
varC CLOB;
LN NUMBER (4);
strt NUMBER (4);
BEGIN
LN := 1;
strt := 5;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.ERASE (varC, LN, strt);
COMMIT;
DBMS_OUTPUT.put_line ('擦除结果为: ' ||varC);
END;
-- ④：DBMS_LOB.Trim()：
--截断LOB数据中从第一位置开始指定长度的部分数据的过程；
-- DBMS_LOB.Trim(LOB数据，截断长度)；
DECLARE
varC CLOB;
LN NUMBER (4);
BEGIN
LN := 4;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.TRIM (varC, LN);
COMMIT;
DBMS_OUTPUT.put_line ('截断结果为: ' ||varC);
END;
-- ⑤：DBMS_LOB.Copy()：
--从指定位置开始将源LOB复制到目标LOB；
-- DBMS_LOB.Copy(源LOB，目标LOB，复制源LOB长度，复制到目标LOB开始位置，复制源LOB开始位置)
DECLARE
vDEST_LOB CLOB;
vSRC_LOB CLOB;
AMOUNT NUMBER;
DEST_OFFSET NUMBER;
SRC_OFFSET NUMBER;
BEGIN
SELECT resume
INTO vDEST_LOB
FROM tlob
WHERE no =2
FORUPDATE ;
SELECT resume
INTO vSRC_LOB
FROM tlob
WHERE no =2;
AMOUNT := DBMS_LOB.GetLength(vSRC_LOB);
DEST_OFFSET := DBMS_LOB.GetLength(vDEST_LOB) +1;
SRC_OFFSET := 1;
DBMS_LOB.COPY (vDEST_LOB,
vSRC_LOB,
AMOUNT,
DEST_OFFSET,
SRC_OFFSET);
DBMS_OUTPUT.put_line ('拷贝结果为: ' ||vDEST_LOB);
END;
--三，图片的存储或二进制文件的存储
--操作步骤
-- 1，先插入普通数据，遇到大对象列使用empty_blob()构造空的指针。
droptable MyLob;
CreateTable MyLob
(
nonumber(8)primarykey,
fname varchar2(30),
myfile blob
)
Lob (myfile)StoreAs
(
Chunk15K
DisableStorageInRow
);
INSERTINTO MyLob
VALUES (1,'Winter.jpg', EMPTY_BLOB ());
-- 2，创建逻辑目录MYDIR这里只能用大写形式或者加写在“”内
CreateorreplaceDirectory MYDIRAs'D:\Pic';
COMMIT;
-- 3，声明一个BLOB类型变量，使用select into语句让其指向到empty_blob()构造空的指针所指向的存储空间
-- 格式：SELECT myfile INTO BLOB类型变量 FROM myLob WHERE no = 1 FOR UPDATE;
-- 4，声明一个BFile类型变量，关联逻辑目录和物理目录文件，使用 BFileName()将其指向到待存储的文件。
-- 格式：BFile类型变量 :=BFileName('MYDIR','IMG_0210.JPG');
--5，使用DBMS_LOB.open()方法将BFile类型变量所指向的文件打开
-- 格式：DBMS_LOB.Open(BFile类型变量);
--6，使用DBMS_LOB.loadfromfile()方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
-- 格式：DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));
--7，使用DBMS_LOB.close()方法将bfile的变量所指向的文件关闭
-- 格式：DBMS_LOB.Close(BFile类型变量);
--8，提交事务
-- Commit;
-- 例子;
Declare
varB blob;
varF Bfile;
Begin
select myfileinto varB from myLob whereno =1forupdate;
varF := bfilename('MYDIR','Winter.jpg');
DBMS_LOB.open(varF);
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--查看文件大小
Declare
varB blob;
Begin
select myfileinto varB from myLob whereno =1;
DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
End;
--如片图片图片图片
-- 例子：创建过程存储图片或二进制文件
CreateOrReplaceProcedure setBLOB(vFileNamevarchar2)
As
varF bfile;
varB blob;
vno number(8);
Begin
varF := bfilename('MYDIR',vFileName);
DBMS_LOB.open(varF);
selectmax(no)into vno from myLob;
if vnoisnullthen
vno := 1;
else
vno := vno + 1;
endif;
insertinto myLobvalues(vno,vFileName,empty_blob());
select myFileinto varB from myLob whereno = vnoforupdate;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
select *from myLob
--执行过程命令窗口中个执行 sql窗口不支持该EXEC
EXEC setBLOB('WWinter.jpg');
--查看文件大小
Declare
varB blob;
Begin
select myfileinto varB from myLob whereno =6;
DBMS_output.put_line('长度为: '||DBMS_LOB.getlength(varB));
End;
--以下是java中查询CLOB的例子
--未测试
-- //获得数据库连接
-- Connectioncon = ConnectionFactory.getConnection();
-- con.setAutoCommit(false);
-- Statement st= con.createStatement();
-- //不需要“for update”
-- ResultSet rs= st.executeQuery("select CLOBATTR from TESTCLOB where ID=1");
-- if(rs.next())
-- {
-- java.sql.Clob clob = rs.getClob("CLOBATTR");
-- ReaderinStream = clob.getCharacterStream();
-- char[] c= new char[(int) clob.length()];
-- inStream.read(c);
-- //data是读出并需要返回的数据，类型是String
-- data =new String(c);
-- inStream.close();
-- }
-- inStream.close();
-- con.commit();
-- con.close();
专题五：集合（联合数组、嵌套表、Varrays）
--2013-07-06
--索引（单列、多列）
--ORACLE 的集合分为三类
--1.index_by 表即：
-- 联合数组 :类似于 C 语言中的数组
-- 语法格式 :
-- TYPE tabletype
-- IS
-- TABLE OF TYPEINDEX BY BINARY_INTEGER
--说明 :
--tabletype,type 是要定义的 index_by表的类型 .
--例 :
droptable test;
createtable test(
idinteger,
namevarchar2(10)
);
createorreplaceTYPE test_name -- 类型名
IS
TABLEOF test.name%TYPE
INDEXBY BINARU_INTEGER;/*INDEX的类型只能是 BINARU_INTEGER*/
--在声明了类型和变量后 ,就可以用下列语句使用 index_by表中的单个元素 .
--v_name(index);
/*
说明 :
index 是指表中第几个元素
注意 :
index 的数据类型属于 BINARU_INTEGER
1) 元素赋值
可以用下面的语句给表中的元素赋值
declare
v_name test_name; --声明变量
BEGIN
v_name(1):='qnma';
v_name(2):='cjh';
END;
*/
DECLARE
TYPE test_name -- 类型名
IS
TABLEOF test.name%TYPE
INDEXBYBINARY_INTEGER;/*INDEX的类型只能是 BINARU_INTEGER*/
v_name test_name; --声明变量
BEGIN
v_name(1):='qnma';
v_name(2):='cjh';
v_name(-2):='sss';
DBMS_OUTPUT.PUT_LINE('v_name(1)='||v_name(1));
DBMS_OUTPUT.PUT_LINE('v_name(2)='||v_name(2));
DBMS_OUTPUT.PUT_LINE('v_name(-2)='||v_name(-2));
END;
/*
注意 :
index_by 表中的元素不是按特定的顺序排序的 .这与 C 语言不同 .index_by表元素个数只受 BINARY_INTEGER
类型的限制 ,即 index 的范围是 -214483647~~+214483647.index只要在此范围内就是合法的 .
--2）以唯一字符串作为联合数组索引
--如下例所示
DECLARE
TYPE months_varrayISVARRAY(12)OFSTRING(9CHAR);
TYPE calendar_tableISTABLEOFVARCHAR2(9CHAR)
INDEXBYVARCHAR2(9CHAR);
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT =0THEN
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
FOR iIN month.FIRST..month.LASTLOOP
calendar(month(i)) := to_char(i);
DBMS_OUTPUT.PUT_LINE('Index ['||month(i)||'] is ['||i||']');
ENDLOOP;
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignmentloop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
FOR iIN month.FIRST..month.LASTLOOP
DBMS_OUTPUT.PUT_LINE('Index ['||month(i)||'] is ['||calendar(month(i))||']');
ENDLOOP;
ENDIF;
END;
2) 调用不存在的元素
调用任何元素前 ,必须首先给该元素赋值 .否则会产生异常如下所示 :
*/
DECLARE
TYPE test
ISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER ;
v_test test;
BEGIN
FOR v_countIN1..5LOOP
v_test(v_count):= v_count*10;
ENDLOOP;
FOR v_countIN1..5LOOP
DBMS_OUTPUT.PUT_LINE(v_test(v_count));
ENDLOOP;
END;
--2. 嵌套表
--嵌套表与 index_by非常相似 , 唯一不同的就是没有 INDEX BYBINARU_INTEGER
/*
1) 嵌套表的初始化与 index_by表完全不同 . 在声明了类型之后 ,再声明一个 index_by 表变量类型 .
如果没有给表赋值 ,那么此表就是一个空的 index_by表 . 但是以后可以往 index_by表中加入元素 ;
而声明了嵌套表变量类型时 ,如果嵌套表中没有任何元素 , 那么它就会自动初始化为 NULL, 并且是只读的 .
如果还想往嵌套表里加入元素 ,系统就会报错 .
*/
-- 如 :
DECLARE
TYPE test
IS
TABLEOFVARCHAR(20);
v_test test:=test('Tom','jack','ROSE'); --声明时直接赋值
BEGIN
--v_test(4):='dd'; --加入会报错
FOR v_countIN1..3LOOP
DBMS_OUTPUT.PUT_LINE(v_test(v_count));
ENDLOOP;
END;
--2) 元素序列 :
--index_by 表中的元素是无序的 ,而嵌套表中的元素是有序的 . 嵌套表元素的 index 是
--从 1开始依次递增的 .
--3. 可变数组
/*
语法格式 :
DECLARE type_name
IS
VARRAY |VARRAY ARRAY
(maximun _size)OF element_type [NOT NULL]
说明 :
type_name 是可变数组的名称 ,maxmun_size是指可变数组元素个数的最大值 .element_type是数组元素的数据
类型 .可变数组的可变指的是定义了数组的最大上限后 ,数组元素的个数可以在这个最大上限内变化 ,但是不
得超过最大上限 .当数组元素的个数超过了最大上限后 , 系统就会报错 . 以下定义了一个可变数组 :
*/
/*
DECLARE
TYPE dates
IS VARCHAY(7) OF VARCHAR2(10);
TYPE months
IS VARRAY(12) OF VARCHAR2(10);
*/
--注意
--与嵌套表一样 ,可变数组也需要初始化 . 初始化需要注意的是 : 赋值的数量必须保证不大于可变数组的最大上限 .
--以下是完整实例 :
DECLARE
TYPE dates
ISVARRAY(7)OFVARCHAR2(10);
TYPE months
ISVARRAY(12)OFVARCHAR2(10);
v_dates dates:=dates('Monday','Tuesday','Wednesday') ;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_dates(1));
DBMS_OUTPUT.PUT_LINE(v_dates(2));
DBMS_OUTPUT.PUT_LINE(v_dates(3));
END;
--以下介绍集合中常用的各种方法
/*
有关集合中的方法 :
index_by 表 ,嵌套表 , 可变数组本身都是对象 ,因此它们都有自己的属性和方法 . 集合的属性和方法的调用
同其他对象调用一样 :Object.Attribute或 Object.Method.
下面是集合类常用的属性和方法 :
*/
--1.COUNT 属性 :返回集合中的数组元素个数
DECLARE
TYPEnameISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER;
TYPE pwdISTABLEOFVARCHAR2(20);
TYPE datesISVARRAY(7)OFVARCHAR2(20);--容量为 7
v_name name;
v_pwd pwd:=pwd('10000','12345','22','yes','no');
v_dates dates :=dates('Monday','Sunday');
BEGIN
v_name(1):='Tom';
v_name(-1):='Jack';
v_name(4):='Rose';
DBMS_OUTPUT.PUT_LINE('The index_by count is :'||v_name.count);
DBMS_OUTPUT.PUT_LINE('The nested count is :'||v_pwd.count);
DBMS_OUTPUT.PUT_LINE('The varray count is :'||v_dates.count);--返回的是可变数组的实际元素个数
END;
/*
执行结果为 :
The index_by count is:3
The nested count is:5
The varray count is:2
*/
--2.DELETE 方法
/*
DELETE 可以删除集合中的一个或多个元素 ,需要注意的是 , 由于 DELETE方法执行的删除操作的大小固定 ,
所以对于可变数组来说没有 DELETE方法 .DELETE 方法有 3种方式
1)DELETE : 不带参数的 DELETE方法 , 表示将整个集合删除
2)DELETE(x): 将集合中第 x个位置的元素删除 .index 起始位置从 1 开始 .
3)DELETE(x,y): 将集合中从第 x个元素到第 y 个元素之间的所有元素删除 .
注意 : 执行 DELETE 方法后 ,集合的 COUNT 值将会立刻发生变化 ,而且当删除的元素不存在时 ,
系统不会报错 ,而是跳过此元素 , 继续执行下一步操作 .
3.EXISTS 属性 :
用于判断集合中的元素是否存在 .语法格式为 :
EXISTS(X) -- 判断位于 X 位置的元素是否存在 ,存在返回 TRUE, 否则返回 FALSE
注意 :
即使是该位置是 NULL,也会返回 TRUE
4.EXTEND 方法 :
用于将元素加到集合的末端 .具体有以下 3 种方式 :
1)EXTEND: 不带参数的 EXTEND是将一个 NULL 元素加入到集合的末端
2)EXTEND(x): 将 x个 NULL 元素加入到集合的末端
3)EXTEND(x,y): 将 x个 y 的元素加入到集合到末端 .
注意 : 由于 index_by 表的随意性 ,因此 EXTEND 方法只针对嵌套表和可变数组有效 .
5.FIRST 和 LAST属性 :
FIRST 用来返回集合的第一个元素 ,LAST用于返回集合中的最后一个元素 .
6.LIMIT:
LIMIT 用来返回集合的最大元素个数 ,由于嵌套表没有有限 , 所以嵌套表使用 LIMIT 时 ,总是返回 NULL
注意 :COUNT是返回集合中的实际个数 , 而 LIMIT 是返回集合中的容量 .
7.NEXT 和 PRIOR属性 :
使用 NEXT,PRIOR时 , 它的后面都会跟一个参数
语法格式 :
NEXT(X): 表示的返回位置为 X处的元素后面的那个元素 , 也就是返回位置为 X 的下一个元素
PRIOR(X): 同上一个相反 ,返回的是位置为 X 的前一个元素 .
*/
--通常使用 NEXT和 PRIOR 与 FIRST和 LAST 一起使用 ,用来处理循环 .
--例 :
DECLARE
TYPE pwdISTABLEOFVARCHAR2(20);
v_pwd pwd:= pwd('10000','12345','22','yes','no','ok','all','hello','right','left','football');
v_count integer;
BEGIN
v_count:=v_pwd.FIRST;
WHILEv_count<=v_pwd.LASTLOOP
DBMS_OUTPUT.PUT_LINE(v_pwd(v_count));
v_count:=v_pwd.NEXT(v_count);
ENDLOOP;
END;
/*
执行结果为 :
10000
12345
22
yes
no
ok
all
hello
right
left
football
*/
/*
8.TRIM 方法 :
用于删除集合末端的元素 ,其具体形式如下
1)TRIM: 不带参数的 TRIM从集合中末端删除一个元素
2)TRIM(X) 是从集合的末端删除 X个元素 , 其中 X要小于集合的 COUNT 数
注意 : 与 EXTEND 一样 ,由于 index_by 表中元素的随意性 , 因此 TRIM方法只对嵌套表和可变数组有效 .
*/
专题六：动态sql（主要是本地动态sql，带参数）
--2013-07-06
--动态sql（主要是本地动态sql，带参数）
/*
1．静态SQLSQL与动态SQL及各自原理说明
Oracle编译PL/SQL程序块分为两个种：其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，
大多数的编译情况属于这种类型；
(原理)另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如
当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询
条件后才能提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。
*/
/*
动态SQL既然是一种”不确定”的SQL，那其执行就有其相应的特点。
Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：
*/
--Excute immediate 动态SQL语句 using绑定参数列表 returning into输出参数列表;
/*
对这一语句作如下说明：
1)动态SQL是指DDL和不确定的DML（即带参数的DML）
2)绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（实际上占位符，
可以理解为函数里面的形式参数）进行绑定。
3)输出参数列表为动态SQL语句执行后返回的参数列表。
4)由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。
*/
--下面列举一个实例：
/*
--设数据库的emp1表，其数据为如下：
ID
NAME
SALARY
要求：
1．创建该表并输入相应的数据。
2．根据特定ID可以查询到其姓名和薪水的信息。
3．根据大于特定的薪水的查询相应的员工信息。
根据前面的要求，可以分别创建三个过程（均使用动态SQL）来实现：
*/
--过程一：创建该表并输入相应的数据
select *from emp1;
droptable emp1;
createorreplaceprocedure create_tableas
begin
executeimmediate
'create table emp1(id number,
name varchar2(10),
salary number )';--动态SQL为DDL语句
executeimmediate'insert into emp1 values (100,' ||'''jacky''' || ',5600)';
executeimmediate'insert into emp1 values (101,' ||'''rose''' || ',3000)';
executeimmediate'insert into emp1 values (102,' ||'''john''' || ',4500)';
commit;
endcreate_table;
--过程调用
call create_table();
select *from emp1;
--过程二：根据特定ID可以查询到其姓名和薪水的信息。
createorreplaceprocedure find_info(p_idnumber) as
v_name varchar2(10);
v_salary number;
begin
executeimmediate
'select name,salary from emp1 whereid=:1'
using p_id
returninginto v_name,v_salary;--动态SQL为查询语句
--dbms_output.put_line(v_name ||'的收入为：'||to_char(v_salary))；
--exception
--when others then
--dbms_output.put_line('找不到相应数据')；
endfind_info;
call find_info(101);
--过程三：根据大于特定的薪水的查询相应的员工信息
createorreplaceprocedure find_emp1(p_salarynumber) as
r_emp1 emp1%rowtype;
type c_typeisrefcursor;
c1 c_type;
begin
open c1 for
'select * from emp1 where salary >:1'
usingp_salary;
loop
fetch c1into r_emp1;
exitwhen c1%notfound;
dbms_output.put_line('薪水大于'||to_char(p_salary)||'的员工为：');
dbms_output.put_line('ID为'to_char(r_emp1)||'其姓名为：'||r_emp1.name);
endloop;
close c1;
endcreate_table;
callfind_emp1(1000);
/*
注意：在过程二中的动态SQL语句使用了占位符“:1“，其实它相当于函数的形式参数，使用”：“作为前缀，
然后使用using语句将p_id在运行时刻将:1给替换掉，这里p_id相当于函数里的实参。另外过程三中打开
的游标为动态游标，它也属于动态SQL的范畴，其整个编译和开发的过程与execute immediate执行的过
程很类似，这里就不在赘述了。
*/
--3．动态SQL语句开发技巧
/*
原理中提到动态SQL的执行是以损失系统性能来换取其灵活性的，所以对它进行一定程度的优化也是必要的。
技巧一：尽量使用类似的SQL语句，这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存，
那么在下一次执行类似语句时就直接调用缓存中已解析过的语句，以此来提高执行效率。
技巧二：当涉及到集合单元的时候，尽量使用批联编。比如需要对id为100和101的员工的薪水加薪10％，
一般情况下应该为如下形式：
*/
declare
type num_listisvarray(20)ofnumber;
v_id num_list :=num_list(100,101);
begin
--...
for iin v_id.first .. v_id.lastloop
-- ...
executeimmediate'update emp1
set =salary*1.2
where id=:1 '
using v_id(i);
endloop;
end;
--批处理
-- 对于上面的处理，当数据量大的时候就会显得比较慢，那么如果采用批联编的话，则整个集合首先一次性的
--传入到SQL引擎中进行处理，这样比单独处理效率要高的多，进行批联编处理的代码如下：
declare
type num_listisvarray(20)ofnumber;
v_id num_list :=num_list(100,101);
begin
--...
forall iin v_id.first .. v_id.lastloop
--...
executeimmediate'update emp1
set =salary*1.2
where id=:1 '
using v_id(i);
endloop;
end;
/*
这里是使用forall来进行批联编，这里将批联编处理的情形作一个小结：
1) 如果一个循环内执行了insert，delete，update等语句引用了集合元素，那么可以将其移动到一个forall语句中。
2) 如果select into，fetch into或returning into 子句引用了一个集合，应该使用bulk collect子句进行合并。
3) 如有可能，应该使用主机数组来实现在程序和数据库服务器之间传递参数。
*/
/*
技巧三：使用NOCOPY编译器来提高PL/SQL性能。缺省情况下，out类型和in out类型的参数是由值传递的方式进行的。
但是对于大的对象类型或者集合类型的参数传递而言，其希望损耗将是很大的，为了减少损耗，可以采用引
用传递的方式，
即在进行参数声明的时候引用NOCOPY关键字来说明即可到达这样的效果。比如创建一个过程：
*/
--create or replace procedure test(p_object in nocopysquare)
--...
--end;
--其中square为一个大的对象类型。这样只是传递一个地址，而不是传递整个对象了。显然这样的处理也是提高了效率。
/*
4．小结
本文对动态SQL的编译原理、开发过程以及开发技巧的讨论，通过本文的介绍后，相信读者对动态SQL程序开发有了
一个总体的认识，为今后深入的工作打下一个良好的基础。
*/
题目一：基础复习
可将以下内容复制到sql窗口执行
--创建存储过程
createorreplaceprocedure update_emp
(v_dept innumber,v_percentinnumberdefault10)is
begin
update emp
set comm=100
wheredeptno=v_dept;
dbms_output.put_line('name:');
end;
/
--过程调用
--execute update_emp(10,15);
declare
v_dept number;
begin
select a.deptno
into v_dept
from emp a
where job='PRESIDENT';
update_emp(v_dept);
end;
/
callupdate_emp(10,15);
--仅在命令窗口中可执行
--execute update_emp(10,15);
--update emp set comm=0 where deptno=10;
select *from emp;
select *from dept;
select a.ename,b.ename from emp a, emp bwhere a.empno='7369'and a.mgr = b.empno
--1.函数根据empno查询姓名，以及直接上司的姓名
createorreplacefunction select_ename(empno1invarchar2, ename1 outvarchar2, ename2outvarchar2)returnintegeris
Resultinteger;
begin
select a.ename, b.enameinto ename1, ename2 from emp a, emp bwhere a.empno='7369'and a.mgr = b.empno;
return(Result);
endselect_ename;
/
--调用方法
declare
ename1 VARCHAR2(10);
ename2 VARCHAR2(10);
issucce integer;
begin
--select_ename('9369', ename1, ename2);
issucce:=select_ename('9369', ename1, ename2);
dbms_output.put_line('所查人：'||ename1||'上司：'||ename2);
end;
/
--2.游标在匿名块中根据部门号检索emp中的职员信息。
declare
cursorrecord
isselect *from emp where deptno='20';
record_row record%rowtype;
begin
openrecord;
loop
fetchrecordinto record_row;
dbms_output.put_line(record_row.empno||' '||record_row.ename||' '||record_row.job||' '||
record_row.hiredate||' '||record_row.sal||' '||record_row.comm||' '||record_row.deptno);
exitwhenrecord%notfound;
endloop;
end;
/
--3.创建表dept_avgsal(deptno,avgsal,nums_low)
createtable dept_avgsal(deptnointeger, avgsal integer, nums_low integer);
--用存储过程完成以下功能：
--查询emp中各个部门的部门号，各个部门的平均工资及部门内低于平均工资的职员的个数。
--将查出来的值插入到dept_avgsal表中。
createorreplaceprocedure update_emp
(v_dept innumber,v_percentinnumberdefault10)is
declare
cursorrecord
isselect a.deptno,count(*) num from emp a,
(select deptno,avg(sal)as avgsal from emp groupby deptno) b
where a.deptno=b.deptno
and a.sal<b.avgsal
groupby a.deptno
orderby a.deptno;
record_row record%rowtype;
avgsal integer;
begin
openrecord;
loop
fetchrecordinto record_row;
selectavg(sal)into avgsal from emp where deptno =record_row.deptno;
insertinto dept_avgsalvalues(record_row.deptno,avgsal, record_row.num);
exitwhenrecord%notfound;
endloop;
end;
/
--结果验证
select *from dept_avgsal;
--4.对于emp表要求控制每个部门的工资总和不能超过20000元，如果超过则应当拒绝其操作。
--用触发器和异常内置函数实现这个功能。
createorreplacetrigger update_emp_sal
beforeupdateon emp
foreachrow
declare
sum_sal number(7,2);
begin
select nvl(sum(sal),0)into sum_sal from emp
where deptno=:new.deptno;
if(sum_sal+:new.sal)>20000then
raise_application_error(-20000,'emp表要求控制每个部门的工资总和不能超过20000元');
endif;
endupdate_emp_sal;
createorreplacetrigger update_emp_sal
beforeupdateon emp
foreachrow
declare
sal number(7,2);--emp.sal%type;
begin
select nvl(sum(sal),0)into sal from emp
where deptno=:new.deptno;
if(sal+:new.sal)>20000then
raise_application_error(-20010,'Error:the salary of '||:new.deptno||' is over 20000yuan!');
endif;
endupdate_emp_sal;
--drop trigger update_emp_sal;
update empset sal=10000where ename='KING';
insertinto empvalues('1',10000);
、
题目二：基础复习
可将以下内容复制到sql窗口执行
--select rowid, rownum from scott.emp where rownumbetween 1 and 5;
--创建存储过程
createorreplaceprocedure update_emp
(v_dept innumber,v_percentinnumberdefault10)is
begin
update emp
set comm=100
wheredeptno=v_dept;
dbms_output.put_line('name:');
end;
/
--过程调用
--execute update_emp(10,15);
declare
v_dept number;
begin
select a.deptno
into v_dept
from emp a
where job='PRESIDENT';
update_emp(v_dept);
end;
/
callupdate_emp(10,15);
--仅在命令窗口中可执行
--execute update_emp(10,15);
--update emp set comm=0 where deptno=10;
select *from emp;
select *from dept;
--1、查询员工名字以“N”结尾并且名字是5个字符的员工信息。
select *from emp where enamelike'____N';
--2、查询员工人数多于2个的部门编号。
select deptnofrom emp groupby deptnohavingcount(*)>2;
--3、查询员工King的所有直接下属的编号、名字信息。
select empno,enamefrom emp where mgr = (select empnofrom emp where ename='KING');
--4、统计各部门的员工人数，显示部门编号，部门名，员工人数。
selectemp.deptno部门编号, count(*)人数, dname 部门名
from emp,dept
whereemp.deptno=dept.deptno
groupby emp.deptno, dname
havingcount(*)>2;
--5、统计各部门的平均工资并从高到低排序。
select deptno部门编号, avg(sal)平均工资
from emp
groupby deptno
orderbyavg(sal)desc
--6、查询1987年开始工作的员工信息。（写出2种不同的写法）
select *from emp where to_char(hiredate,'yyyy')='1980';
--7、查询RESEARCH部门所有员工的编号，名字。（写出4种不同的写法)
select deptno,enamefrom emp where deptno = (select deptnofrom dept where dname='RESEARCH');
selectemp.deptno, enamefrom emp,dept where dname='RESEARCH'and emp.deptno =dept.deptno
--8、查询平均工资低于2000的部门的所有员工的编号、名字。
select empno员工编号, ename 员工姓名
from emp
where deptnoin (
select deptnofrom emp groupby deptnohavingavg(sal) <2000
)
--9、SCOTT所在部门的所有员工工资增加30%。
update emp
set sal =sal*1.3
where deptno =(
select deptnofrom emp where ename ='SCOTT'
)
select a.ename,b.ename from emp a, emp bwhere a.empno='7369'and a.mgr = b.empno
createorreplacefunction select_ename(empno1invarchar2, ename1 OUTVARCHAR2(10), ename2OUTVARCHAR2(10))
RETURN integer is
begin
select a.ename,b.enameinto ename1, ename2 from emp a, emp bwhere a.empno='7369'and a.mgr = b.empno;
return(1);
endselect_ename;
/
declare
ename1 VARCHAR2(10);
ename2 VARCHAR2(10);
issucce integer;
begin
--select_ename('9369', ename1, ename2);
select_ename('9369', ename1,ename2);
dbms_output.put_line('所查人：'||ename1||'上司：'||ename2);
end;
/
1030
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
