oracle自定义类型

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/a275838263/article/details/51062782

最近整理里常见数据库的数据类型,发现o racle有一个自定义字段类型。简单的研究了一下:
 
1、定义一个类型
Sql代码 
CREATE OR REPLACE TYPE PropertyValue AS OBJECT ( 
  number_value  number, 
  string_value varchar2(2000), 
  date_value date, 
   
  MEMBER FUNCTION getNumberValue RETURN number, 
  MEMBER FUNCTION getStringValue RETURN varchar2, 
  MEMBER FUNCTION getDateValue RETURN date 
); 
 
2、编写类型的方法
Sql代码 
CREATE OR REPLACE TYPE BODY PropertyValue AS 
  MEMBER FUNCTION getNumberValue RETURN number AS 
  BEGIN 
    RETURN number_value; 
  END getNumberValue; 
   
  MEMBER FUNCTION getStringValue RETURN varchar2 AS 
  BEGIN 
    RETURN string_value; 
  END getStringValue; 
   
  MEMBER FUNCTION getDateValue RETURN date AS 
  BEGIN 
    RETURN date_value; 
  END getDateValue; 
END; 
 
 
3、使用自定义类型为字段创建表
Sql代码 
create table IGRP_SYS_PROPERTIES ( 
nid             number primary key, 
propertyKey     varchar2(2000), 
propertyValue   PropertyValue, 
propertyType        varchar2(2000), 
description     varchar2(2000), 
createUserId        number, 
modifyUserId        number, 
createDate      date, 
modifyDate      date); 
 
CREATE SEQUENCE SQ_IGRP_PROPERTYID 
  START WITH 1 
  INCREMENT BY 1 
  MAXVALUE 1E27 
  MINVALUE 1 
  NOCYCLE 
  CACHE 20 
  NOORDER; 
 
-- Add comments to the columns 
comment on column IGRP_SYS_PROPERTIES.nid 
  is 'PK唯一值'; 
comment on column IGRP_SYS_PROPERTIES.propertyKey 
  is '属性键'; 
comment on column IGRP_SYS_PROPERTIES.propertyValue 
  is '属性值'; 
comment on column IGRP_SYS_PROPERTIES.propertyType 
  is '属性类型'; 
comment on column IGRP_SYS_PROPERTIES.description 
  is '描述'; 
comment on column IGRP_SYS_PROPERTIES.createUserId 
  is '创建用户ID';   
comment on column IGRP_SYS_PROPERTIES.modifyUserId 
  is '修改用户ID';     
comment on column IGRP_SYS_PROPERTIES.createDate 
  is '创建日期';       
comment on column IGRP_SYS_PROPERTIES.modifyDate 
  is '修改日期';         
 
 
4、添加数据
insert into IGRP_SYS_PROPERTIES VALUES 
(SQ_IGRP_PROPERTYID.nextval,  
'xxx', 
PropertyValue(10, null,null), 
'number', 
'xxx', 
101, 
101, 
sysdate, 
sysdate); 
commit; 
 
 
5、查询数据
Sql代码 
select isp.propertyValue.getNumberValue() as myValues 
  from  
    IGRP_SYS_PROPERTIES isp  
  where  
    isp.propertyKey = 'xxx'  
    and isp.propertyType = 'number';  
 
一些注意的地方:
1. 数据类型使用在数据表后,其BODY是不能改变的。 因此,要先DROP和类型有关的表,才能修改其BODY。
2. EXPORT和IMPORT可能会有问题。
3. SQL*Plus 的COPY命令不能使用在自定义的数据类型中。

java调用oracle函数返回自定义类型

03-27

包定义如下:rn-------------------------------------------------------rn---------------------定义包头--------------------------rn-------------------------------------------------------rncreate or replace package pck_test isrn type cursorref is ref cursor; -----定义游标rn type temp_record is record -----定义游标输出记录类型rn (rn itemId mini_t_item_dict.item_id%TYPE,rn describe mini_t_item_dict.describe%TYPErn );rn type statistic_record is record -----定义输出记录类型,保存一项结果rn (rn itemId mini_t_item_dict.item_id%TYPE,rn describe mini_t_item_dict.describe%TYPE,rn recordCount numberrn );rn type statistic_record_array is TABLE OF statistic_record;-----定义记录型数组rn function getStatistics (itemid mini_t_item_dict.item_id%TYPE) return statistic_record_array; --------定义函数rnend pck_test;rn-------------------------------------------------------rn---------------------定义包体--------------------------rn-------------------------------------------------------rncreate or replace package body pck_test isrnfunction getStatisticsrn (itemid mini_t_item_dict.item_id%TYPE)rn return statistic_record_arrayrn isrn vs_temp_record temp_record;rn vs_statistic_record statistic_record;rn vs_statistic_record_array statistic_record_array;rn vs_cursorref cursorref;rn --vs_recordCount number;rn beginrn open vs_cursorref forrn select t.item_id, describe from mini_t_item_dict t where t.parent_id = itemId;rn fetch vs_cursorref into vs_temp_record;rn while vs_cursorref%found looprn vs_statistic_record_array.extend;rn vs_statistic_record.itemId := vs_temp_record.itemId;rn vs_statistic_record.describe := vs_temp_record.describe;rn select count(*) into vs_statistic_record.recordCountrn from mini_t_adverse_form1 frn where f.item_id = vs_statistic_record.itemId;rn vs_statistic_record_array(vs_statistic_record_array.count) := vs_statistic_record;rn fetch vs_cursorref into vs_temp_record;rn end loop;rn return vs_statistic_record_array;rn close vs_cursorref;rn end;rnend pck_test;rnrn在java中调用:rnpublic List statisticsCount(int pid)rn try rn CallableStatement cstm = this.getConnection().prepareCall("?=call pck_test.getstatistics(?)");rn cstm.setInt(2, pid);rn cstm.registerOutParameter(1,OracleTypes.ARRAY,"[color=#FF0000]pck_test.statistic_record_array[/color]");//这出错了,异常在下面rn cstm.execute();rn List stalit = new ArrayList();rn stalit = (List) cstm.getObject(1);rn return stalit;rn catch (CannotGetJdbcConnectionException e) rn // TODO Auto-generated catch blockrn e.printStackTrace();rn catch (SQLException e) rn // TODO Auto-generated catch blockrn e.printStackTrace();rn rn return null;rn rnrn异常:java.sql.SQLException: 无效的名称模式: pck_test.statistic_record_arrayrn at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)rn at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)rn at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:462)rn at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:361)rn at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1755)rn at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:271)rn at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:195)rn at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:164)rn at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:149)rn at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:114)rn at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:70)rn at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:78)rn at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:626)rn at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:133)rn at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:178)rn at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:173)rn at minimax.tim.form.save.dao.impl.SaveFormDaoImpl.statisticsCount(SaveFormDaoImpl.java:383)rn at minimax.tim.test.UserTest.testForm1(UserTest.java:49)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)rn at java.lang.reflect.Method.invoke(Method.java:597)rn at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)rn at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)rn at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)rn at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)rn at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)rn at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)rn at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)rn at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)rn at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)rn at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)rn at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)rn at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)rn at org.junit.runners.ParentRunner.run(ParentRunner.java:236)rn at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)rn at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)rn求大侠指教,谢谢!!!

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试