oracle 基础知识（三）

一、删除oracle

进入注册表到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleOraHome91TNSListener ImagePath关键值有？

试试
1、在“控制面板”/“管理工具”/“服务”中将所有ORACLE的服务停止。
2、将以下注册表键删除：HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE。
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/ORACLE....
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/ORACLE....
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet003/ORACLE....
3、直接删除ORACLE所在的目录。

安装不成功我一般的解决办法是：
1、用oracle系统的卸载工具先把全部组建卸载；
2、在“控制面板”/“管理工具”/“服务”中将所有oracle的服务停止
3、删除oracle目录
4、重起
5、再次检查“控制面板”/“管理工具”/“服务”中将所有oracle的服务,若有则全部停止
6、重新安装
一般好象都可以，有朋友也提到要修改注册表，我平时不改好象也行
如何完完整整干干净净的把oracle删除掉？

1、开始－＞设置－＞控制面板－＞管理工具－＞服务
   停止所有Oracle服务。

2、开始－＞程序－＞Oracle - OraHome81－＞Oracle Installation Products－＞Universal Installer
   卸装所有Oracle产品

3、运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。

4、运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动这个列表，删除所有Oracle入口

5、从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标

6、重新启动计算机，重起后才能完全删除Oracle所在目录

7、删除与Oracle有关的文件，选择Oracle所在的缺省目录C:\Oracle，删除这个入口目录及所有子目录，

　 并从Windows 2000目录（一般为C:\WINNT）下删除以下文件

　 ORACLE.INI、oradim80.INI

8、WIN.INI文件中若有[ORACLE]的标记段，删除该段
---------------------------------------------------------------
删除过程
  1：在管理工——服务里面         停止所有oracle的服务
  2：删除D:\ORACLE文件夹
  3：删除C:\Program   Files\Oracle文件夹
  4：删除开始——程序里面的oracle文件
  5：运行regedit,删除HKEY_LOCAL_MACHINE\SOFTWARE\下的ORACLE键
  6：运行regedit,删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services下所有以oracle打头的键   

  到现在为止，你还是可以发现在服务里面还有oracle的服务存在，必须把他删除，否则你再安装oracle时就会出现什么服务已经存在的错误提示了！！而这些服务是在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\Root下以LEGACY_ORACLE打头的，而且你选种按delete删除时系统会提示你一个错误！！不让你删除！！！现提供具体删除方法，win2000的如下：   

  7：运行regedt32注意了，不是regedit！！！！在HKEY_LOCAL_MACHINE那页找到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\Root先选中ROOT然后点菜单上的安全—权限把自己添加到里面，并且权限设置为完全控制，确定后再删除ROOT下所有LEGACY_ORACLE打头的键，同样的方法也可以把HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001和HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002两个下面\Enum\Root下所有LEGACY_ORACLE打头的键删除，重新启动计算机可以发现，服务里面的那些都没有了！！
  7：在winXP中就比较简单了，还是运行regedit，找到LEGACY_ORACLE打头的键后右击，选择权限，同样将everyone设置为完全控制就可以删除了！删除后重新启动一下就可以了！！   

  8：重新安装新的oracle吧！！！SID一样也不会有什么错误的！！！
------------------------------------------------------------------
oracle在win2k下的安装与卸载的详细说明     

  在WinNT/2000上的安装过程
  1、安装前先做好注册表的备份工作；
  2、确认Oracle安装版本号
  3、确认系统配置
  系统基本要求：
  内存：   128   M以上
  交换空间   系统内存的2倍
  光驱设备   支持ISO   9600格式
  硬盘空间   典型安装NTFS下，ORACLE_HOME   DIRVE>1.2G
  操作系统   SOLARIS   2.5，LINUX   2.2   ，NT4.0+SP6以上
  4、安装
  插入光盘——>自动运行——>Setup——>进入welcome窗口next   step——>确认安装目标路径——>选择要安装的产品
  ——>选择安装类型(可先选择自主安装，选择产品语言后，再选择上一步，选择典型安装)
  ——>输入全局数据库名——>安装——>下一步
  安装时如果要安装ORACLE   WEBDB，不要接受的Oracle安装路径及其位置的默认值。   

  在WinNT/2000上的反安装过程
  如果不注意，可能不能正确删除ORALE
  1、使用Oracle   Universal   Installer反安装Oracle组件
  包括三种情况：
  a、安装中途取消安装
  b、安装过程中计算机重启了
  c、安装过程未完成
  处理方法：只须删除硬盘上Oracle_home目录，因为此时未往注册表中写入信息   

  2、删除Net8,Oracle   Internet   Directory,Oracle8i   Database   and   Registry   entries
  必须先停止Oracle的服务
  重装Net8过程：
  a、regedit
  b、HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services--->删除OracleHOME_NAMETNSListener
  c、exit   

  删除Oracle   Internet   Directory
  C:\>OIDCTL   CONNECT=NET_SERVICE_NAME   SERVER=OIDLDAPD   INSTRANCE=SERVER   INSTANCE_NUMBER   STOP
  C:\>OIDMON   STOP
  C:\>OIDMON   REMOVE
  后面的步骤同下   

  删除Oracle8i   Database   and   Registry   entries
  a、start--->programes--->Oracle--->Database   Adminstrator--->Database   Configuration   Assistant
  b、delete   a   database--->next
  c、regedit--->删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleServiceSID   

  删除组件
  start--->programe--->Oracle   Installer   Appears--->Universal   Installer--->选择要删除的组件--->确定   

  手工清除所有的Oracle组件
  1、以administrator帐户登录
  2、停止所有的oracle服务(start-->setting-->control   panel-->administrator   tool-->service)
  3、修改注册表regedit
  4、HKEY_CLASS_ROOT-->删除所有以ORACLE/ORAL表示的关键字
  5、HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE删除ORACLE关键字
  6、删除HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中的Oracle选项
  7、删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application下的Oracle选项
  8、删除HKEY_CURRENT_USER\SOFTWARE\ORACLE下的以oracle/orcl开头的选项
  9、删除HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBCINST.INI下的Oracle关键字
  10、退出注册表
  11、Start   --->   Settings   --->   Control   Panel   --->   系统--->高级--->   Environment   tab.
  12、选择   the   system   variable   path   编辑   the   Path   variable.
  13、删除所有   Oracle   entries   from   the   path.   例：if   JRE   was   installed   by   Oracle,   remove   the   %ORACLE_HOME%\BIN   path   and   the   JRE   path.   You   may   see   a   path   similar   to   this   one:
  C:\ORACLE\ORA81\BIN;G:\PROGRAM   FILES\ORACLE\JRE\1.1.7\BIN
  14、退出控制面板
  15、进入SYSTEM_DRIVE:\WINNT\PROFILES\ALL   USERS\START   MENU\PROGRAMS.
  16、删除图标
  Oracle   -   HOME_NAME
  Oracle   Installation   Products
  where   HOME_NAME   is   the   previous   Oracle   home   name.
  17、删除   SYSTEM_DRIVE:\Program   Files\Oracle目录
  18、删除所有硬盘上   ORACLE_BASE目录
  19、重启计算机     

  以上译自oracle随机文挡，如有不当之处请指正！   

  根据个人经验，删除的步骤最好为：
  1、通过Oracle   Installation   Products   卸载已安装产品
  2、在注册表中删除oracle,orcl,ora打头的注册项，可能有部分删除不了，不用管。
  3、重起系统
  4、删除系统下c:\Program   Files\Oracle目录和ORACLE_BASE目录     

  另：如果愿意，可将安装之前的注册表备冯文件导入试一下。   

  你没有彻底删除oracle，按如下方法可以彻底删除数据库   

  NT环境下：
  1、以NT的Administrator   登陆
  2、通过控制面版-〉服务，停掉所有ORACLE服务
  3、打开注册表（REGEDIT命令），删除
  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
  4、打开注册表中的   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services     

  删除以ORACLE开头的所有服务
  5、删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\A
  pplication下
  以ORACLE开头的所有服务
  6、关闭REGEDIT
  7、打开控制面版-〉系统-〉环境变量
  8、如果ORACLE安装了JRE,就删除%ORACLE_HOME%\BIN和JRE路径。
  比如   \ORACLE\ORA81\BIN;G:   \Program   Files\Oracle\jre\1.1.7\bin
  9、删除\Winnt\Profiles\All   Users\Start   Menu\Programs\Oracle   和
  \Winnt\Profiles\All   Users\Start   Menu\Programs\Oracle
  10、删除\Program   Files\Oracle
  11、重新启动机器
  12、删除所有ORACLE目录（在注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORA
  CLE_HOMES下）     

  在   Windows   95   /   Windows   98:     

  1.   打开注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.     

  2.删除前面步骤中的第3步中的ORACLE     

  3.   删除AUTOEXEC.BAT   中的   %ORACLE_HOME%\bin   和   JRE     

  4.   删除所有ORACLE目录     

  5.   \Program   Files\Oracle     

  6.   \Windows\Start   Menu\Programs\Oracle   -   <HOME>
  \Windows\StartMenu\Programs\Oracle     

  7.   重新启动
----------------------------------------------------------------
很多朋友只用了oracle的删除，但很多情况下，他会发现重新安装时，点了下一步安装界面就消失了，往往无奈下只好重装系统，其实只是你数据库没删干净，删干净就不会出现这种情况了。

　　实现方法：

　　1、开始->设置->控制面板->管理工具->服务

　　停止所有Oracle服务。

　　2、开始->程序->Oracle - OraHome81->Oracle Installation Products->

　　Universal Installer

　　卸装所有Oracle产品，但Universal Installer本身不能被删除

　　5、运行regedit，选择HKEY_LOCAL_MACHINESOFTWAREORACLE，按del键删除这个入口。

　　6、运行regedit，选择HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices，滚动

　　这个列表，删除所有Oracle入口。

　　7、运行refedit，

　　HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication，

　　删除所有Oracle入口。

　　8、开始->设置->控制面板->系统->高级->环境变量

　　删除环境变量CLASSPATH和PATH中有关Oracle的设定

　　9、从桌面上、STARTUP(启动)组、程序菜单中，删除所有有关Oracle的组和图标

　　10、删除Program FilesOracle目录

　　11、重新启动计算机，重起后才能完全删除Oracle所在目录

　　12、删除与Oracle有关的文件，选择Oracle所在的缺省目录C:Oracle，删除这个入

　　口目录及所有子目录，并从Windows 2000目录(一般为C:WINNT)下删除以下文

　　件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。

　　13、WIN.INI文件中若有[ORACLE]的标记段，删除该段

　　14、如有必要，删除所有Oracle相关的ODBC的DSN

　　15、到事件查看器中，删除Oracle相关的日志

　　说明：

　　如果有个别DLL文件无法删除的情况，则不用理会，重新启动，开始新的安装，

　　安装时，选择一个新的目录，则，安装完毕并重新启动后，老的目录及文件就可以删除掉了

二、oracle数据库对象

数据库对象同JAVA中的对象是一样的.有属性和方法.
oracle创建对象类型
create or replace TYPE address_typ AS OBJECT
(
   street varchar2(15),
   city varchar2(15),
   state char(2),
   zip varchar2(5)
);
create or replace type person_typ as object
(
   id number,
   nanm varchar2(10),
   dob date,
   phone varchar2(12),
   address address_typ--引用上面的对象类型.(对象类型可以相互引用)
);
现在写一个带有函数的对象类型.这个对象用于表示产品.注意声明了一个函数
get_sell_by_date().这个函数根据days_valid属性和当前日期,返回产品的最晚售出时间.
create or replace product_typ as object
(
  id number,
  name varchar2(15),
  description varchar2(22),
  price number(5,2),
  days_valid  number,
  MEMBER Function get_sell_by_date Return Date
);
Member function子句用于声明get_sell_by_dat函数,
Member procedure子句用于声明一个过程.
---由于在对象类型product_typ 中声明了方法(函数),所以必须为product_typ创建对象体(body).下面是创建product_typ的对象体.
create or replace type body product_typ as
   Member function get_sell_by_date Return date is
   v_sell_by_date date;
  begin
     select days_valid+sysdate into v_sell_by_date from dual;
     return v_sell_by_date;
  end get_sell_by_date;
end product_typ;
可以看出,函数通过将days_valid属性与当前日期相加,计算并返回产品当前最晚必须售出时间.
---可以通过describe来获取有关对象类型的信息
describe product_typ;
--------------------------------------------------------
2.用对象类型定义列对象和对象表
对象类型可以用来定义表中的列,这种列称为列对象(Column object).同时,当对象类型包含嵌入对象类型时,嵌入对象类型也是一个列对象.person_typ(见上面)就是这样一个例子.
创建products表:--这种表是包含列对象的表
create table products
(
  product product_typ,
  quantity_in_stock number--库存数量
);
对象表:使用对象类型来定义整个表.这种表叫对象表.
下面这个例子创建两个对象表object_products和object_customers,分别使用product_typ和person_typ定义.注意OF子句用于将每个表标识为对象表:
 例子:
create table object_products of product_typ;
create table object_customers of person_typ;
对象表与包含列对象的表这两者的区别是:
后者可以具有多列.
-------对象引用和对象标识符
对象表的另一个不同之处在于可以使用对象引用(object reference)为对象表之间的关系建立模型,而不是使用外键.对象引用使用REF类型进行定义,通常都用作指向对象表中对象的指针.对象表中的每个对象都具有惟一的对象标识符(object identified,OID),它可以存储在REF列中.下面创建表purchases,其中包含了两个REF列:
  create table purchases
  (
    id number primary key,
    customer REF person_typ SCOPE IS object_cusotmers,
    product REF product_typ SCOPE IS object_products
  );
 SCOPE IS子句将对象引用限制在特定表中的对象上.例如列customer被限制在指向ojbect_customers表中的对象.
==========================================================================
下面对products,object_products,object_customers,purchases表进行DML操作.
------------------------------
-----对products(包含列对象的表)进行DML操作
1.插入
insert into products(product,quantity_in_stock)
  values(product_typ(1,'pasta','20 oz bag of pasta',3.95,10),50);
2.查询
--全查询
select * from products;product列对象的属性显示在product_typ的构造函数中.
--查询单个列对象:注意product对象的id属性的表被赋予别名p
select p.product from products p where  p.product.id=1;
--查询列对象中声明的方法(函数和过程)
在prodcut_typ对象类型中包含get_selll_by_date()函数,用于计算并返回某件产品的最晚售出时间.
select p.product.get_sell_by_date() from products p;
3.修改
注意访问product列对象时使用了表别名
update products p set p.product.description='30 oz bag of pasta'
 where p.product.id=1;
4.删除
delete from products p
 where p.product.id=2;
-------对object_products(对象表)表进行DML操作
1.插入(两种形式同sql一样)
insert into object_products
values(product_typ(1,'pasta','20 oz bag of past',3.86,10));
insert into object_products (id,name,description,price,days_valid)
  values(2,'sardines','12 oz box of sardines',2.99,5);
2.选择记录
select * from object_products;
 可以用Oracle数据库内置的value()函数从对象表中查询记录.这种方法将记录看作真正的对象,并在对象类型的构造函数中返回对象的属性.value()函数接受表别名作参数.
select value(op) from object_products op;
3.更新
update object_products set description='25 oz bag of pasta' where id=1;
4.删除记录
delete from object_products where id=2;
---------对object_customers(对象表)进行DML操作
1.插入
insert into object_customers values
(
  person_typ(1,'John','Brown','01-FEB-1955','800-555-1211',
           address_typ('2 stat street','Beantown','MA',''))
);
2.查询
object_customers表是person_typ的对象表;person_typ包含嵌入的address_typ列对象address.
---全查询
select * from object_customers;
---查询一条记录
select * from object_customers oc where oc.id=1;--id是person_typ中的id
---对嵌入的列对象的state属性的查询
select * from object_customers oc
   where oc.address.state='MA';
3.更新和删除同对object_products的操作一样(略)
--------对purchases(这种表是包含列对象的表,但是较特殊的一种.这包含的列对象是对象表(通过REF),而不是对象类型)表进行DML操作
1.插入
  对象表中的每一条记录都具有惟一的对象标识符,这个标识符可保存在REF列中.可通过REF()函数访问这个对象标识符,并将返回的对象标识符保存在REF列中.
insert into purchases (id,costome,product)
 values(1,(select REF(oc) from object_customers oc where oc.id=1),
 (select REF(op) from object_products op where op.id=1));
这个例子记录的是顾客#1购买了产品.
2.查询(选择记录)
--全查询
注意customer和product列中包含很长的数字字母串,这些是指向object_customers和object_products表中记录的对象标识符.
select * from purchases;
--访问customer和product列所指向的记录
可以使用DEREF()函数通过REF列的值访问它所指向的对象表中的记录;这个函数接受REF列作为参数.
select DEREF(customer),DEFER(product) from purchases;
3.更新
--修改purchases表中的一条记录.注意product列的值被改为指向object_products表中的产品#2
update purchases set product=(select REF(op) from object_products op
    where op.id=2) where id=1;

===============================
------------在PL/SQL中使用对象
程序包product_package中
包含一个get_products()函数，返回object_products表中的对象
一个insert_product()过程,向ojbect_products表中增加一个对象.
----product_package
程序包规范
create or replace package product_package as
Type ref_cursor_typ IS REF CORSOR
function get_products return ref_cursor_typ;
procedure insert_product(
    p_id in ojbect_products.id%type,
     p_name in object_products.name%type,
    p_descirption in object_products.description%type,
     p_price in object_products.price%type,
    p_days_valid in object_products.days_valid%type
);
end product_package;
程序包主体
create or replace package body product_package as
     function get_products return ref_cursor_typ
     is
     products_ref_cursor ref_cursor_typ;
     begin
       open products_ref_cursor FOR select value(op) from object_products op;
       return products_ref_cursor;
     end get_products;
    procedure insert_product(
    p_id in ojbect_products.id%type,
     p_name in object_products.name%type,
    p_descirption in object_products.description%type,
     p_price in object_products.price%type,
    p_days_valid in object_products.days_valid%type
    )
     as
     product product_typ:=product_typ(p_id,p_name,p_description,p_price,p_days_valid);
    begin
      insert into object_products values(product);
      commit;
    exception
      when others then
        rollback;
    end insert_product;
end product_package;

说明:get_products()函数使用value将object_products表的内容作为product_typ对象返回.
下面是对过程和函数的调用
call product_package.insert_product(4,'sals','15 oz jar of salsa',1.50,20);
select product_package.get_products from dual;

三、动态创建表和传值

在SQL里面不能直接用参数做表名,要先连成字符串才执行
是动态建表和动态传值.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[CreateTable]
@Tablename nvarchar(100),
@Tablenamedp nvarchar(100) as

DECLARE @SQL nvarchar(3250)
DECLARE @SQL1 nvarchar(3250)
SET @SQL = '
CREATE TABLE '+@Tablename+' (
    [HtNo] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Dhr] [int] NULL,
    [Shr] [int] NULL,
    [Dhdh] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY
)
'
SET @SQL1='
CREATE TABLE '+@Tablenamedp+'(
    [Id] [bigint] NOT NULL  identity PRIMARY KEY,
    [Dhdh] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [HtspId] [bigint] NULL,
    [Sl] [decimal](10, 4) NULL,
    [Jj] [money] NULL,
    [Jxsl] [decimal](18, 4) NULL
)
'
exec sp_executesql @SQL
exec sp_executesql @SQL1
============
sp_executesql 这个是系统存储过程
@SQL==变量.

四、复制表结构和改表名

改表名:rename tablename1 to tablename2;
改字段名：alter table tablename rename column age to age2;
复制表的结构给另一个新表。
create table cc as select * from aa;
复制表的一个字段给另一个表
insert into bb.age select age from age
insert into bb select * from aa;
==========
数据字典视图，USER_SOURCE 包含存储过程的代码文本。
USER_SOURCE

五、集合、数组和嵌套表

oracle中的集合
1.变长数组(varray).2.嵌套表.3.关联数组(以前称为索引表)
-----------------------------
=======变长数组(varray)
1.创建变长数组
  使用create type语句创建变长数组类型,并在创建该类型时指定最大大小和存储在数组
中的元素类型.如下示例.创建一个名为varray_address_typ的变长数组类型,可以最多存储2个varchar2 字符串:
create type varray_address_typ as varray(2) of varchar2(50);
每个(共2个)varchar2可以用来表示我们虚构商店的一个顾客客的地址.地址可以是顾客装运的地址,即收到产品的地方,另一个是他们的付账地址,即收到产品帐单的地方.
2.使用变长数组类型定义表列
create table customers_with_varray
(
   id integer primary key,
   name varchar2(10),
   address varray_address_typ
)
3.获得变长数组信息
describe varray_address_typ;
describe customers_with_varray;
4.使用insert插入表
insert into customers_with_varray values
(1,'aa',varray_address_typ('南昌市二径路','南昌市三径路'));
5.查找变长数组元素
使用select语句查找变长数组元素.
select * from customers_with_varray;
6.更改变长数组元素.
   变长数组元素只能整体进行更改.这意味着,如果想要更改一个元素,必须提供变长数组的所有元素.
update customers_with_varray set
  address=varray_address_typ('','')where id=1;
-----------------------------------------------------------------
==================嵌套表
  嵌套表是任意数量元素的一个有序集合,所有元素是同一数据类型.嵌套表有单个列,该列的类型可以是内置的数据库类型,也可以是以前创建过的对象类型.如果嵌套表的列类型,都是对象类型,则表可以被看作一个多列表,对象的每个属性是一列.可以插入,更改和删除嵌套表的单个元素.
1.创建嵌套表类型
-----address_typ这是一个对象类型,相当于java中的vo
 create type address_typ as OBJECT
(
  street varchar2(15),
  city varchar2(15),
  state char(2),
  zip varchar2(5)
)
-使用create type语句创建嵌套表类型,下例创建了嵌套表类型nested_table_addres_typ,用于存储address_typ对象类型
create type nested_table_address_typ as table of addres_typ;
2.使用嵌套表类型定义表列
  一旦已经定义了嵌套表类型,就可以使用它定义表列.例如表customers_with_nested_table使用nested_table_address_typ定义列addresses;
create table customers_with_nested_table
(
  id integer primary key,
  name varchar2(10),
  addresses nested_table_address_typ
)
NESTED TABLE
   addresses
STORE AS nested_addresses;
  NESTED TABLE子句标识了嵌套表列的名称(addresses),STORE AS子句指定了实际嵌套表
的名称(nested_addresses).可以独立于包含嵌套表的父表访问嵌套表.
3.获得嵌套表信息
describe nested_table_address_typ;
user_nested_tables;
all_nested_tables获得能访问的所有表的信息.
4.填充嵌套表元素
注意,使用nested_table_address_typ和address_typ构造函数指定了地址
insert into customers_with_nested_table values(
   1,'zhangsai',nested_table_addres_typ(
    address_typ('2 State Street ','Beantown','MA',''),
        address_typ('4 Hill Street','Lost town','CA','')
    )
);
可以看到,该行有两个地址,但是可以在嵌套表中存储任意数目的地址.
5.查找嵌套表元素
使用select语句查找嵌套表元素
select * from customers_with_nested_table;
6.更改嵌套表元素
 和变长数组不同,嵌套表的元素可单独更改:可以插入,更改和删除嵌套表元素.
使用table子句和一个查找嵌套表的子查询可以完成这些操作.
-----下例在customer_with_nested_table中顾客#1的addresses嵌套表列的末尾插入一个地址.
insert into table
( select addresses form customers_with_nested_table where id=1)
 values(addres_typ('5 Main','Uptown','NY',''));
-----下例更改customers_with_nested_table中顾客#1的第一个地址.注意,使用别名addr标识
别第一个址,然后设置它:
 update table
(select addresses from customers_with_nested_table where id=1)
addr set value(addr)=address_typ
('1 Market street ','Main town','MA','')
where value(addr)=address_typ('2 State Street','Beantown','MA','');
-----最后一个例子删除customers_with_nested_table中顾客#1的第二个地址
delete from table
(select addresses from customers_with_nested_table where id=1)
addr where value(addr)=address_typ('4 Hill Street','Lost town','CA','');

=========================================================
----------在PL/SQL中使用集合
--1.操作变长数组
程序包varray_package
函数:get_customers(),它返回customers_with_varray表的相应行
过程:insert_customer().它在customers_with_varray表中添加一行.
create or replace package varray_package as
 type ref_cursor_typ is ref corsor;
 function get_customers return ref_cursor_typ;
 procedure insert_customer
(
  p_id in customers_with_varray.id%type,
  p_name in customers_with_varray.name%type,
  p_addresses in customers_with_varray.addresses%type
);
end varray_package;

create or replace package body varray_package as
 function get_customers return ref_cursor is
   customers_ref_cursor ref_cursor_typ;
 begin
  open customers_ref_cursor fro
    select * from customers_with_varray;
  return customers_ref_cursor;
 end get_customers;

 procedure insert_customer
 (
  p_id in customers_with_varray.id%type,
  p_name in customers_with_varray.name%type,
  p_addresses in customers_with_varray.addresses%type
) as
 begin
  insert into customers_with_varray
   values(p_id,p_name,p.addresses);
  commit;
  exception
   when others then
     rollback;
 end insert_customer;
end varray_package;
--测试
call varray_package.insert_customer(1,'james',
    varray_addres_typ('10 Main street,Green Town,CA,22212',
                         '20 State street,Blue Town FL,22213'));
varray_package.get_products(),从customers_with_varray中检索行
select varray_package.get_customers from dual;
================
---------操作嵌套表
程序包:nested_table_package它包含
函数:get_customers(),它返回表customers_with_table的相应的列
过程:insert_customer(),向customers_with_table表中添加一行

create or replace package nested_table_package  as
 type ref_cursor_typ is ref corsor;
 function get_customers return ref_cursor_typ;
 procedure insert_customer
(
  p_id in customers_with_varray.id%type,
  p_name in customers_with_varray.name%type,
  p_addresses in customers_with_varray.addresses%type
);
end varray_package;

create or replace package body nested_table_package as
   function get_customers return ref_cursor_typ is
     customers_ref_cursor ref_cursor_typ
   begin
     open customers_ref_cursor for
          select * from customers_with_nested_table;
     return customers_ref_cursor;
   end get_customers;

   procedure insert_customer
  (
     p_id in customers_with_varray.id%type,
  p_name in customers_with_varray.name%type,
  p_addresses in customers_with_varray.addresses%type
  ) is
  begin
   insert into customers_with_table
   values(p_id,p_name,p.addresses);
   commit;
   exception
    when others then
      rollback;
  end insert_customer;
end nested_table_package;
--测试
call nested_table_package.insert_customer(2,'James',
    nested_table_address_typ(
              address_typ('10 Main','Green twon','CA',''),
              address_typ('20 State','Blue twon','FL','')));
nested_table_package.get_products(),从customers_with_varray中检索行
select nested_table_package.get_customers from dual;
---------------------------
集合方法
count:返回集合的元素数目.
delete:删除集合中的元素.有三种形式.删除所有元素
delete(n):删除第n个元素
delete(n,m):删除从第n个到第m个元素
exists(n):如果集合的第n个元素存在,返回true;
extend:增加集合的大小.有三种形式.添加一个元素,设置为空值.
extend(n):添加n个元素,设置为空值.
extend(n,m):添加n个元素,设置为m
first:返回集合的第一个(最小的)索引号.如果集合为空,返回空值.
last:返回集合的最后一个(最大的)索引号.如果集合为空,返回空值.
next(n):返回n后面的元素的索引号,如果n后面没有元素,返回空值.
prior(n)返回n前面的元素的索引号,如果n前面没有元素,返回空值.
trim:删除集合末尾的元素.有两种形式:删除末尾的一个元素
trim(n):删除末尾n个元素.

---例子
create or replace procedure display_addresses
(addresses_par nested_table_address_typ)
as
count_var integer;
begin
  dbms_output.put_line(addresses_par.count);
 for count_var in 1..addresses_par.count
 loop
  dbms_output.put_line('Address #'|| count_var||':');
  dbms_output.put_line(addresses_par(count_var).street|| ',');
    dbms_output.put_line(addresses_par(count_var).city|| ',');
 end loop;
end display_addresses;
====上述语句addresses_par.count相对应的有其他的集合
addresses_par.delete;
addresses_par.extend;等等.

六、日期操作

select * from dual
--显示星期几(数字)一周中的第几天--这种写法是按西方算法星期四是5.
select to_char(sysdate,'D') as aa from dual;
--一周中的第几天(中文显示(如星期四))
select to_char(sysdate,'DAY') as aa from dual;
select to_char(sysdate,'dy') as bb from dual;
--一月中的第几天
select to_char(sysdate,'DD') as aa from dual;
--一年中的第几天`
select to_char(sysdate,'DDD') as aa from dual;
--显示全日期
select to_char(sysdate,'yyyy MONTH DAY') as aa from dual;
--显示日期格式.
select to_char(sysdate,'YYYY-MM-DD') as aa from dual;
--显示月份(中文)
select to_char(sysdate,'MON') as aa from dual;
select to_char(sysdate,'month') as aa from dual;
select t0_char(sysdate,'MONTH') as aa from dual;
--显示年份(数字)
select to_char(sysdate,'yyyy') as aa from dual;
--显示一月中的第几周(数字)
select to_char(sysdate,'W') as bb from dual;
--24小时显示
select to_char(sysdate,'HH24') as bb from dual;
--12小时显示
select to_char(sysdate,'HH') as bb from dual;
--显示分钟
select to_char(sysdate,'MI') as bb from dual;
--显示秒钟
select to_char(sysdate,'ss') as bb from dual;

七、合并多行记录的字符串

怎么合并多行记录的字符串，一直是oracle新手喜欢问的SQL问题之一，关于这个问题的帖子我看过不下30个了，现在就对这个问题，进行一个总结。
   什么是合并多行字符串（连接字符串）呢，例如：
SQL> desc test;
Name    Type         Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y
CITY    VARCHAR2(20) Y                         

SQL> select * from test;

COUNTRY              CITY
-------------------- --------------------
中国                 台北
中国                 香港
中国                 上海
日本                 东京
日本                 大阪
要求得到如下结果集：
-------    --------------------
中国       台北,香港,上海
日本       东京，大阪
  实际就是对字符实现一个聚合功能，我很奇怪为什么Oracle没有提供官方的聚合函数来实现它呢：）
  下面就对几种经常提及的解决方案进行分析（有一个评测标准最高★★★★★）：
1.被集合字段范围小且固定型 灵活性★   性能★★★★ 难度 ★
  这种方法的原理在于你已经知道CITY字段的值有几种，且还不算太多，如果太多这个SQL就会相当的

长。。看例子：
SQL> select t.country,
  2  MAX(decode(t.city,'台北',t.city||',',NULL)) ||
  3  MAX(decode(t.city,'香港',t.city||',',NULL))||
  4  MAX(decode(t.city,'上海',t.city||',',NULL))||
  5  MAX(decode(t.city,'东京',t.city||',',NULL))||
  6  MAX(decode(t.city,'大阪',t.city||',',NULL))
  7  from test t GROUP BY t.country
  8  /

COUNTRY              MAX(DECODE(T.CITY,'台北',T.CIT
-------------------- ------------------------------
中国                 台北,香港,上海,
日本                 东京,大阪,
  大家一看，估计就明白了（如果不明白，好好补习MAX DECODE和分组）。这种方法无愧为最笨的方法

，但是对某些应用来说，最有效的方法也许就是它。
2.固定表固定字段函数法 灵活性★★ 性能★★★★ 难度 ★★
  此法必须预先知道是哪个表，也就是说一个表就得写一个函数，不过方法1的一个取值就要便捷多了。在大多数应用中，也不会存在大量这种合并字符串的需求。废话完毕，看下面：
  定义一个函数
create or replace function str_list( str_in in varchar2 )--分类字段
  return varchar2
is
      str_list  varchar2(4000) default null;--连接后字符串
      str  varchar2(20) default null;--连接符号
begin
      for x in ( select TEST.CITY from TEST where TEST.COUNTRY = str_in ) loop
          str_list := str_list || str || to_char(x.city);
          str := ', ';
      end loop;
      return str_list;
end;
使用：
SQL> select DISTINCT(T.country),list_func1(t.country) from test t;

COUNTRY              LIST_FUNC1(T.COUNTRY)
-------------------- ----------------
中国                 台北, 香港, 上海
日本                 东京, 大阪

SQL> select t.country,str_list(t.country) from test t GROUP BY t.country;

COUNTRY              STR_LIST(T.COUNTRY)
-------------------- -----------------------
中国                 台北, 香港, 上海
日本                 东京, 大阪
这个时候，使用分组和求唯一都可以满足要求。它的原理就是，根据唯一的分组字段country，在函数里面再次查询该字段对应的所有被合并列，使用PL/SQL将其合并输出。
3.灵活表函数法 灵活性★★★ 性能★★★ 难度 ★★★
  该方法是在方法2的基础上，使用动态SQL，将表名和字段名称传入，从而达到灵活的目的。
  create or replace function str_list2( key_name in varchar2,
                    key  in varchar2,
                    coname in varchar2,
                    tname     in varchar2 )
 return varchar2
as
    type rc is ref cursor;
    str    varchar2(4000);
    sep    varchar2(2);
    val    varchar2(4000);
    cur    rc;
begin
    open cur for 'select '||coname||'
                    from '|| tname || '
                    where ' || key_name || ' = :x '
                using key;
    loop
        fetch cur into val;
        exit when cur%notfound;
        str := str || sep || val;
        sep := ', ';
    end loop;
    close cur;
    return str;
end;
SQL> select test.country,
  2  str_list2('COUNTRY', test.country, 'CITY', 'TEST') emplist
  3  from test
  4   group by test.country
  5  /

COUNTRY              EMPLIST
-------------------- -----------------
中国                 台北, 香港, 上海
日本                 东京, 大阪
4.一条SQL法 灵活性★★★★ 性能★★ 难度 ★★★★
  一条SQL的法则是某位大师提出的，大家曾经在某个时期都乐此不彼的寻求各种的问题一条SQL法，但是大师的意思似乎被曲解，很多性能差，可读性差，灵活差的SQL都是这个原则产物，所谓画虎不成反成犬类。不过，解决问题始终是第一原则，这里还是给出一个比较有代表性的一条SQL方法。
SELECT country,max(substr(city,2)) city
  FROM
(SELECT country,sys_connect_by_path(city,',') city
 FROM
(SELECT country,city,country||rn rchild,country||(rn-1) rfather
  FROM
  (SELECT  test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY 

test.city) rn
  FROM test))
CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
 GROUP BY country;
  下面分步解析，有4个FROM，就有4次结果集的操作。
   step 1 给记录加上序号rn
  SQL> SELECT  test.country ,test.city,row_number() over (PARTITION BY test.country ORDER 

BY test.city) rn
  2    FROM test
  3  /

COUNTRY              CITY                         RN
-------------------- -------------------- ----------
日本                 大阪                          1
日本                 东京                          2
中国                 上海                          1
中国                 台北                          2
中国                 香港                          3
  step 2 创造子节点父节点
SQL> SELECT country,city,country||rn rchild,country||(rn-1) rfather
  2    FROM
  3    (SELECT  test.country ,test.city,row_number() over (PARTITION BY test.country ORDER 

BY test.city) rn
  4    FROM test)
  5  /
 日本 大阪 日本1 日本0
 日本 东京 日本2 日本1
 中国 上海 中国1 中国0
 中国 台北 中国2 中国1
 中国 香港 中国3 中国2
  step 3 利用sys_connect_by_path生成结果集
SELECT country,sys_connect_by_path(city,',') city
 FROM
(SELECT country,city,country||rn rchild,country||(rn-1) rfather
  FROM
  (SELECT  test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY 

test.city) rn
  FROM test))
CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0'
 日本 ,大阪
 日本 ,大阪,东京
 中国 ,上海
 中国 ,上海,台北
 中国 ,上海,台北,香港
  step 4 最终步骤，筛选结果集合
SQL> SELECT country,max(substr(city,2)) city
  2    FROM
  3  (SELECT country,sys_connect_by_path(city,',') city
  4   FROM
  5  (SELECT country,city,country||rn rchild,country||(rn-1) rfather
  6    FROM
  7    (SELECT  test.country ,test.city,row_number() over (PARTITION BY test.country ORDER 

BY test.city) rn
  8    FROM test))
  9  CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
 10   GROUP BY country;

COUNTRY              CITY
-------------------- -------
中国                 上海,台北,香港
日本                 大阪,东京

  可谓是，7歪8搞，最后还是弄出来了，呵呵。
5.自定义聚合函数 灵活性★★★★★ 性能★★★★★ 难度 ★★★★★
  最后一个方法是我认为“王道”的方法，自定义聚合函数。
  就如何我在本开始说的，为啥oracle没有这种聚合函数呢？我也不知道，但oracle提供了聚合函数的

API可以让我方便的自己定义聚合函数。
  详细可以看Oracle Data Catridge guide这个文档。连接如下：
  http://www.oracle.com.cn/other/9ionlinedoc/appdev.920/a96595/toc.htm
下面给出一个简单的例子：
SQL> SELECT t.country,strcat(t.city) FROM test t GROUP BY t.country;

COUNTRY              STRCAT(T.CITY)
-------------------- ------------------
日本                 东京,大阪
中国                 台北,香港,上海
简单吧，和官方的函数一样的便捷高效。
函数：
CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
TYPE:
create or replace type strcat_type as object (
    cat_string varchar2(4000),
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return 

number,
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) 

return number,
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out 

varchar2,flags in number) return number
)