oracle 基础知识(三)

 

一、删除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 基础知识(三)

二、oracle数据库对象

 

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;
oracle 基础知识(三)

三、动态创建表和传值

 

oracle 基础知识(三)
在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==变量.
oracle 基础知识(三)

四、复制表结构和改表名

oracle 基础知识(三)
改表名: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 基础知识(三)

五、集合、数组和嵌套表

oracle 基础知识(三)
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;等等.
oracle 基础知识(三)

六、日期操作

oracle 基础知识(三)
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 基础知识(三)

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

oracle 基础知识(三)
怎么合并多行记录的字符串,一直是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
)
oracle 基础知识(三)

oracle 基础知识(三)

上一篇:PostgreSQL 8.1 中文文档


下一篇:Photoshop简单调制朴实且艺术的写真照片