将Oracle查询转换为pl/sql中的用户定义类型

问题描述：

我在plsql过程中的关系表上有一个选择查询.

I have a select query on a relational table in a plsql procedure.

我想将此查询的结果转换为用户定义的类型对象，以通过odp.net返回.

I want to convert the results of this query into a user defined type object to return via odp.net.

我该怎么做?

答

(这是我今天发表的另一篇文章) 这是入门指南: http://www. oracle.com/technology/obe/hol08/dotnet/udt/udt_otn.htm

(this is from one of my other post today) this is a walkthrough on getting started: http://www.oracle.com/technology/obe/hol08/dotnet/udt/udt_otn.htm

这会更详细: http://download.oracle .com/docs/html/E10927_01/featUDTs.htm

但真正的肉&在Samples目录中安装ODP之后，土豆已经安装在计算机上:％ORA_HOME％\ product \ 11.1.0 \ client_1 \ odp.net \ samples \ 2.x \ UDT

but the real meat & potatoes are already installed on your computer after you install ODP in the Samples directory: %ORA_HOME%\product\11.1.0\client_1\odp.net\samples\2.x\UDT

但在pl/sql方面却如此:

but the pl/sql side of things:

首先创建单例udt以一次处理一行

First create the singleton udt to handle one row at a time

 CREATE TYPE TESTTYPE IS OBJECT(COLA VARCHAR2(50) , COLB NUMBER(10) );
 create or replace procedure GetTestType(lTestType OUT NOCOPY TESTTYPE)
 IS
  BEGIN
     SELECT TESTTYPE('ValA',123) 
       INTO LTESTTYPE
       FROM DUAL ;
  END GetTestType ;

按照上面的链接中的说明进行操作，以使.net端不同步

follow the directions in the above links to get the .net side insynch

现在开始收集:

CREATE TYPE TESTTYPETABLE IS TABLE OF TESTTYPE ;

CREATE OR REPLACE PROCEDURE GETTESTTYPETABLE(lTestTypeTable OUT NOCOPY TestTypeTable)
 IS
  BEGIN
     SELECT TESTTYPE(COLA,COLB)
               bulk collect INTO lTestTypeTable
         FROM (
             SELECT 'ValA' COLA ,123 COLB
               FROM DUAL
               UNION
             SELECT 'ValB' COLA ,234 COLB
               FROM DUAL
               UNION
             SELECT 'Valc' COLA ,456 COLB
               FROM DUAL
         ) ;

END GETTESTTYPETABLe;

然后在.net方面，这实际上是TESTTYPE()的值

then in the .net side of things this will effectively be a value of TESTTYPE()

现在可以节省一些时间，您可以在INSERT/UPDATE/DELETES上使用RETURNING子句这样

Now to save you some time you can use the RETURNING clause on INSERT/UPDATE/DELETES as such

create table testTable (colA varchar2(50) , colB number(10) );
CREATE SEQUENCE TESTSEQ START WITH 1 NOCACHE;

DECLARE
lTestTypeTable TestTypeTable ; 
BEGIN
    UPDATE TESTTABLE
      SET
      COLA = '1' ,
      COLB = 'a'
    WHERE COLA IS NULL
     RETURNING TESTTYPE(COLA,COLB)  --NOTE IF YOU HAVE ONE ROW YOU MAY DROP THE BULK COLLECT AND PUT IT INTO THE SINGLE ROW TYPE
     BULK COLLECT INTO
     lTestTypeTable
     ;
END ; 
/



DECLARE
lTestType TestType; 
BEGIN
    INSERT INTO TESTTABLE(COLA, COLB)
    VALUES ('BBB' , testSeq.NEXTVAL )  
     RETURNING TESTTYPE(COLA,COLB)
     INTO
     lTestType
     ;

     DBMS_OUTPUT.PUT_LINE('MY NEW SEQUENCE # IS SET TO ' || lTestType.COLB) ;
END ; 
/

不幸的是，您似乎无法做到

unfortunately it seems that you cannot do a

insert into TT ... SELECT * from .. RETURNING Type(x,y) BULK COLLECT INTO lVariable;

因此而不是死记硬背地从此站点进行复制，而是通过变通"来告知大批量收集是否可以在插入语句中工作 http://www.oracle-developer.net/display.php?id= 413

so instead of rote COPING FROM THIS SITE, IT TELLS OF A "work-around" TO GET THE BULK COLLECT TO WORK IN AN INSERT STATEMENT http://www.oracle-developer.net/display.php?id=413

将Oracle查询转换为pl/sql中的用户定义类型

相关推荐