一、删除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‘,‘123456‘)) ); 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(‘1111111111111111‘,‘22222222222‘)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‘,‘123456‘), address_typ(‘4 Hill Street‘,‘Lost town‘,‘CA‘,‘54321‘) ) ); 可以看到,该行有两个地址,但是可以在嵌套表中存储任意数目的地址. 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‘,‘55513‘)); -----下例更改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‘,‘54321‘) where value(addr)=address_typ(‘2 State Street‘,‘Beantown‘,‘MA‘,‘12345‘); -----最后一个例子删除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‘,‘54321‘); ========================================================= ----------在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‘,‘22212‘), address_typ(‘20 State‘,‘Blue twon‘,‘FL‘,‘22213‘))); 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 )