JAVA与ORACLE

最新推荐文章于 2024-07-22 14:15:06 发布

Tiwen

最新推荐文章于 2024-07-22 14:15:06 发布

阅读量1.5k

点赞数

分类专栏：数据库 JAVA 文章标签： java import string oracle insert jdbc

本文链接：https://blog.csdn.net/Tiwen818/article/details/7054969

版权

数据库同时被 2 个专栏收录

29 篇文章 0 订阅

订阅专栏

JAVA

26 篇文章 0 订阅

订阅专栏

JAVA与ORACLE

1、在JDBC用xsu将SELECT转化为xml(cursor)

2、在数据库中，访问xml数据服务

3、ORACLE JDBC扩展

4、java过程察看跟踪文件

5、用WMSYS.WM_CONCAT进行字符累计运算

一、用ORACLE 的XSU JAVA包将SELECT 查询结果转化为XML数据文件。

该包的名称为xsu.jar，在数据库安装后，可以在安装目录中找到它。

package xmls.com;

importjava.sql.*;

importoracle.xml.sql.query.OracleXMLQuery;

public class testXSU {

public static void main(String[] args) {

try {

Connection myConnection = getConnection("scott","tiger");

myConnection.setAutoCommit(false);

//普通select 测试

// String strSQL="SELECTd.DEPTNO,d.DNAME,d.LOC," +

// "EMPNO,ENAME,JOB,MGR,to_char(HIREDATE,'yyyymmdd') HIREDATE ,"+

// "SAL,nvl(COMM,0)comm from dept d, emp e where d.deptno=e.deptno and d.deptno = 10";

//通过

// 包含cursor 函数的select

String strSQL =

"selectd.deptno,d.dname,d.loc, " +

"cursor(selectempno,ename,job,mgr,hiredate from emp where d.deptno=deptno) emp from dept d where d.deptno >0";

//通过

//2输出方法

OracleXMLQuery qry = new OracleXMLQuery(myConnection, strSQL);

qry.keepObjectOpen(true);

qry.setRowsetTag("DEPTS"); //定义根节点的名称

qry.setRowTag("DEPT"); //定义行名称

//cursor的节点名称采用了select 中的别名

qry.setDateFormat("yyyy年mm月dd日") //定义date的输出格式;

qry.setMaxRows(2);

qry.setEncoding("UTF-8"); //定义字符

String str =qry.getXMLString();

System.out.println(str);

// XMLDocument domDoc = (XMLDocument)qry.getXMLDOM();

// StringWriter s = newStringWriter(10000);

// domDoc.print(newPrintWriter(s));

// System.out.println(" The string version --->\n"+s.toString());

qry.close();

myConnection.close();

} catch (SQLException e) {

System.out.println("错误代码 = " + e.getErrorCode());

System.out.println("错误信息 = " + e.getMessage());

System.out.println("sql状态 = " + e.getSQLState());

e.printStackTrace();

} //catch (IOException e) {

// System.out.println(e.toString());

// }

}

private static Connection getConnection(String username,

String password) throws SQLException {

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",

username, password);

return conn;

}

输出结果

<?xml version= '1.0' encoding = 'UTF-8'?>

<DEPTS>

<DNAME>ACCOUNTING</DNAME>

<EMP>

<EMP_ROW num="1">

<ENAME>CLARK</ENAME>

<JOB>MANAGER</JOB>

</EMP_ROW>

<EMP_ROW num="2">

<JOB>PRESIDENT</JOB>

</EMP_ROW>

<EMP_ROW num="3">

<ENAME>MILLER</ENAME>

<JOB>CLERK</JOB>

</EMP_ROW>

</EMP>

</DEPT>

<DNAME>RESEARCH</DNAME>

<LOC>DALLAS</LOC>

<EMP>

<EMP_ROW num="1">

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

</EMP_ROW>

<EMP_ROW num="2">

<ENAME>JONES</ENAME>

<JOB>MANAGER</JOB>

</EMP_ROW>

<EMP_ROW num="3">

<ENAME>SCOTT</ENAME>

<JOB>ANALYST</JOB>

</EMP_ROW>

<EMP_ROW num="4">

<ENAME>ADAMS</ENAME>

<JOB>CLERK</JOB>

</EMP_ROW>

<EMP_ROW num="5">

<JOB>ANALYST</JOB>

</EMP_ROW>

</EMP>

</DEPT>

</DEPTS>

二、在数据中访问xml数据服务

观察来自以下的数据服务

http://asktom.oracle.com/pls/ask/ask_tom.newest.rss

以下的代码，可以在oracle数据库中取得该数据

create table c_clob

(y clob,

x xmltype

)

declare

l_url httpuritype;

l_xml xmltype;

l_len integer;

clob_1 clob;

l_rss_version number;

begin

l_url:= httpuritype.createuri('http://asktom.oracle.com/pls/ask/ask_tom.newest.rss');

l_xml:= l_url.getxml();

clob_1:= l_xml.extract('/').getstringval();

l_len:= length(l_xml.extract('/').getstringval());

insert intoc_clob(y,x) values(clob_1, l_xml);

commit;

dbms_output.put_line(l_len);

--dbms_output.put_line(substr(l_xml.extract('/').getstringval(),1,250));

l_rss_version := l_xml.extract('/rss/@version').getNumberVal();

dbms_output.put_line(l_rss_version);

end;

遗留问题：

1、11g个中增加了安全机制，在普通用户下运行上述需要手工配置ACL ;

2、利用createuri下的其他函数可以取得网页数据，邮件数据。

在return的后面的语句不会被执行

create table t
(
a varchar2(10)
)

create or replace function foo(val1varchar2) return varchar2 as
begin
insert into t values ('hh');
dbms_lock.sleep(5);
return val1 || 'suffix';
insert into t values ('hh');
end;

declare

iinteger;

avarchar2(10);

begin

--Test statements here

a:=foo('hhhh');

dbms_output.put_line(a);

select count(*) into i from t;

dbms_output.put_line(i);

end;

文打印出的i是多少？

package yourcompany.yourapp.model.custom;

import java.sql.*;

import oracle.jdbc.*;

import oracle.jdbc.pool.OracleDataSource;

import java.io.*;

public class TestProc

{

public static void main(String args[])throws SQLException, IOException

{

// Prompt the user for connect information

System.out.println("Please enterinformation to test connection to the database");

String user="scott";

String password="tiger";

Stringdatabase="localhost:1521:gq";

System.out.flush();

System.out.println("Connecting...");

// Open an OracleDataSource and get aconnection

OracleDataSource ods = newOracleDataSource();

ods.setURL("jdbc:oracle:thin:@" +database);

ods.setUser(user);

ods.setPassword(password);

Connection conn = ods.getConnection();

conn.setAutoCommit(false);

System.out.println("connected.");

// Create a statement

CallableStatement cs = conn.prepareCall("begin ? := foo(?); end;");

cs.registerOutParameter(1,Types.CHAR);

cs.setString(2, "aa");

cs.executeUpdate();

String result = cs.getString(1);

cs.close();

Statement stmt = conn.createStatement();

ResultSet rset =stmt.executeQuery("select a from t");

while (rset.next())

System.out.println(rset.getString(1));

conn.rollback();

rset.close();

stmt.close();

conn.close();

System.out.println(result);

System.out.println("Your JDBCinstallation is correct.");

}

如果没有conn.rollback(); 数据是否被提交？

三、使用oracle的jabc的扩展

package mycompany.guanqi.j;

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

public class BasicExample3 {

public static void main(String[] args) {

try {

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection myConnection =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:GQ",

"scott","tiger");

myConnection.setAutoCommit(false);

oracle.sql.NUMBER customerId = new oracle.sql.NUMBER(6);

int customerIdInt = customerId.intValue();

System.out.println("customerIdInt = " + customerIdInt);

oracle.sql.CharacterSet myCharSet =

CharacterSet.make(CharacterSet.US7ASCII_CHARSET);

oracle.sql.CHAR firstName =

neworacle.sql.CHAR("Jason", myCharSet);

String firstNameString =firstName.stringValue();

System.out.println("firstNameString = " + firstNameString);

oracle.sql.CHAR lastName = new oracle.sql.CHAR("Price",myCharSet);

System.out.println("lastName = " + lastName);

oracle.sql.DATE dob = new oracle.sql.DATE("1969-02-22 13:54:12");

String dobString = dob.stringValue();

System.out.println("dobString = " + dobString);

OraclePreparedStatementmyPrepStatement =

(OraclePreparedStatement)myConnection.prepareStatement("INSERTINTO t VALUES (?)");

myPrepStatement.setCHAR(1, firstName);

myPrepStatement.execute();

myPrepStatement.close();

System.out.println("Added row to customers table");

Statement myStatement = myConnection.createStatement();

OracleResultSet customerResultSet =

(OracleResultSet)myStatement.executeQuery("SELECT ROWID r, a FROM t");

System.out.println("Retrieved row from customers table");

oracle.sql.ROWID rowid;

oracle.sql.CHAR pa = new oracle.sql.CHAR("", myCharSet);

OraclePreparedStatement pstmt =

(OraclePreparedStatement)myConnection.prepareStatement("UPDATEt SET a = ? WHERE rowid = CHARTOROWID(?)");

while (customerResultSet.next()) {

rowid =customerResultSet.getROWID("r");

pa =customerResultSet.getCHAR("a");

String s1=pa.stringValue()+"aaaa";

oracle.sql.CHAR newpa = new oracle.sql.CHAR(s1, myCharSet);

System.out.println("rowid= " + rowid.stringValue()+"

"+newpa.stringValue());

pstmt.setCHAR (1, newpa);

pstmt.setROWID (2, rowid);

pstmt.executeUpdate ();

}

pstmt.close();

customerResultSet.close();

myConnection.commit();

myConnection.close();

} catch (SQLException e) {

System.out.println("错误代码 = " + e.getErrorCode());

System.out.println("错误信息 = " + e.getMessage());

System.out.println("sql状态 = " + e.getSQLState());

e.printStackTrace();

}

采用oracle jdbc扩展，可以处理oracle的繁多的数据类型，如对象类型，对象类型等，从而提高程序效率。

如：

create or replace type p_in is table of number

create or replace procedure procin(p1 in p_in) is
i integer;
begin
i:=p1.count;
for k in 1..i loop
insert into t values(p1(k));
end loop;
end procin;

package mycompany.guanqi.j;

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.jdbc.pool.OracleDataSource;

public class Test_col {

public static void main(String[] args) {

try {

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:thin:@localhost:1521:gq");

ods.setUser("scott");

ods.setPassword("tiger");

Connectionconn = ods.getConnection();

conn.setAutoCommit(false);

System.out.println("connected.");

OracleCallableStatement procin =(OracleCallableStatement)conn.prepareCall ("begin procin (?); end;");

int[]values = { 1, 2, 3 };

ARRAY ary1 = newARRAY(ArrayDescriptor.createDescriptor("P_IN", conn), conn, values);

procin.setObject(1, ary1,OracleTypes.ARRAY);

procin.execute ();

System.out.println("执行成功");

procin.close();

conn.commit();

conn.close();

}

catch (SQLException e) {

System.out.println("错误代码 = " + e.getErrorCode());

System.out.println("错误信息 = " + e.getMessage());

System.out.println("sql状态 = " + e.getSQLState());

e.printStackTrace();

}

package mycompany.guanqi.j;

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.jdbc.pool.OracleDataSource;

public class TestRowid_ora {

public static void main(String[] args) {

try {

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:thin:@localhost:1521:gq");

ods.setUser("scott");

ods.setPassword("tiger");

Connection conn = ods.getConnection();

conn.setAutoCommit(false);

System.out.println("connected.");

oracle.sql.CharacterSet myCharSet = CharacterSet.make(CharacterSet.ZHS16GBK_CHARSET);

oracle.sql.CHAR mystr = new oracle.sql.CHAR("中华sss", myCharSet);

System.out.println(mystr.stringValue());

OraclePreparedStatementmyPrepStatement =

(OraclePreparedStatement)conn.prepareStatement("INSERTINTO t VALUES (?)");

myPrepStatement.setCHAR(1, mystr);

myPrepStatement.execute();

myPrepStatement.close();

System.out.println("增加一行数据");

conn.commit();

conn.close();

}

catch (SQLException e) {

System.out.println("错误代码 = " + e.getErrorCode());

System.out.println("错误信息 = " + e.getMessage());

System.out.println("sql状态 = " + e.getSQLState());

e.printStackTrace();

}

在JDBC中使用集合操作方法

建立集合类型

create type P_IN is table of number

建立一个使用集合的过程

create or replace procedure procin(p1 inp_in) is
i integer;
begin
i:=p1.count;
for k in 1..i loop
insert into t values(p1(k));
end loop;
end procin;

JDBC程序

package mycompany.guanqi.j;

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.jdbc.pool.OracleDataSource;

public class Test_col {

public static void main(String[] args) {

try {

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:thin:@localhost:1521:gq");

ods.setUser("scott");

ods.setPassword("tiger");

Connection conn = ods.getConnection();

conn.setAutoCommit(false);

System.out.println("connected.");

OracleCallableStatement procin =(OracleCallableStatement)conn.prepareCall ("begin procin (?); end;");

int[] values = { 1, 2, 3 };

ARRAY ary1 = newARRAY(ArrayDescriptor.createDescriptor("P_IN", conn), conn, values);

procin.setObject(1, ary1, OracleTypes.ARRAY);

procin.execute ();

System.out.println("执行成功");

procin.close();

conn.commit();

conn.close();

}

catch (SQLException e) {

System.out.println("错误代码 = " + e.getErrorCode());

System.out.println("错误信息 = " + e.getMessage());

System.out.println("sql状态 = " + e.getSQLState());

e.printStackTrace();

}

四、java过程察看跟踪文件

nvl问题

package mycompany.guanqi.j;

import java.sql.*;

import oracle.jdbc.pool.OracleDataSource;

import java.io.*;

import oracle.jdbc.OracleTypes;

class nvl_test {

public static void main(String[] args) throws SQLException, IOException{

String user = "scott";

String password = "tiger";

String database = "localhost:1521:gq";

System.out.println("Connecting...");

// Open an OracleDataSource and get a connection

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:thin:@" + database);

ods.setUser(user);

ods.setPassword(password);

Connection conn = ods.getConnection();

System.out.println("connected.");

PreparedStatement pstmt = null;

try{

pstmt = conn.prepareStatement ("select count(distinct d.dname) fromdept d where d.deptno=nvl(?,d.deptno)");

pstmt.setInt (1, 11); //假定一个不存在的deptno

//pstmt.setInt (1, 10); //假定一个存在的deptno

//pstmt.setNull(1,OracleTypes.NUMERIC); //假定没有输入参数

ResultSet rset = pstmt.executeQuery();

while (rset.next())

System.out.println(rset.getString(1));

rset.close();

}

finally{

if(pstmt!=null)

// Close the statement

pstmt.close();

}

conn.close();

System.out.println("test is finished");

}

看看后台发生了什么

比较以下过程

declare
-- Local variables here
i integer;
i_count integer;
begin
select count(*) into i_count from dept dwhere d.deptno= nvl(i,d.deptno);
dbms_output.put_line(i_count);
end;

看看后台发生了什么？对于DML语句，我们可以有以下方法观察：

1、 mv_log

2、编写触发器

3、 logminer

4、跟踪文件

5、 V$sql_text表

但是对于select语句，由于select不产生log（产生log的情况很少，如块清除），只能用一下方法来看

1、V$sql_text表

2、跟踪文件

取得跟踪文件名的方法

1、为了简化java程序建立一个视图,取得当前跟踪文件名

2、 create view v_tkprof
as
select p1.value||'\'||p2.value||'_ora_'||p.spid||'.trc' filename
from
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
where p1.name = 'user_dump_dest'
and p2.name = 'db_name'
and p.addr = s.paddr
and s.audsid = userenv ('sessionid')

如果当前没有权限浏览上述视图中的系统表，首先用dba帐号进行授权。

tkprof d:\app\gx\diag\rdbms\gq\gq\trace\gq_ora_5880.trcd:\app\gx\diag\rdbms\gq\gq\trace\gq_ora_5880.txt

java程序

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.xml.sql.query.OracleXMLQuery;

public class jdbc_tkprof {

public static voidmain(String[] args) {

try {

Connection myConnection =getConnection("scott","tiger");

myConnection.setAutoCommit(false);

Statement stmt =myConnection.createStatement();

String cmd;

cmd = "altersession set sql_trace = true";

stmt.execute(cmd);

cmd = "alter session set timed_statistics = true";

stmt.execute(cmd);

cmd = "alter sessionset events '10046 trace name context forever,level 12'";

stmt.execute(cmd);

cmd = "altersession setmax_dump_file_size=unlimited";

stmt.execute(cmd);

// cmd = "altersession set events '10046 trace namecontext off'";

// stmt.execute(cmd);

StringstrSQL="select filename from v_tkprof" ;

ResultSet rs =stmt.executeQuery(strSQL);

while (rs.next())

System.out.println(rs.getString(1));

rs.close();

stmt.close();

PreparedStatement stmt1 =null;

try{

stmt1 =myConnection.prepareStatement ("select count(distinct d.dname) from dept dwhere d.deptno=nvl(?,d.deptno)");

//stmt1.setInt (1,11); //假定一个不存在的deptno

//stmt1.setInt (1,10); //假定一个存在的deptno

stmt1.setNull(1,OracleTypes.NUMERIC); //假定没有输入参数

ResultSet rset =stmt1.executeQuery();

while(rset.next())

System.out.println(rset.getString(1));

rset.close();

}

finally{

if(stmt1!=null)

stmt1.close();

}

myConnection.close();

} catch (SQLException e) {

System.out.println("错误代码 = " +e.getErrorCode());

System.out.println("错误信息 = " +e.getMessage());

System.out.println("sql状态 = " +e.getSQLState());

e.printStackTrace();

} //catch (IOException e) {

// System.out.println(e.toString());

// }

}

private static ConnectiongetConnection(String username, String password)

throws SQLException

{

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:GQ",username,password);

return conn;

}

我们尝试运行上述程序，发现每一次跟踪文件名称不一样

观察对程序中sql的运行情况

SQL ID : 8c1a6t15jh7n0

select count(distinct d.dname)

from

deptd where d.deptno=nvl(:1,d.deptno)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.01 0 0 0 0

Fetch 1 0.00 0.01 0 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.00 0.03 0 3 0 1

Misses inlibrary cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 81

Rows RowSource Operation

------- ---------------------------------------------------

1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us)

5 CONCATENATION (cr=3 pr=0 pw=0 time=14 us)

5 FILTER (cr=3 pr=0 pw=0 time=10 us)

5 TABLE ACCESS BY INDEX ROWID DEPT (cr=3pr=0 pw=0 time=6 us cost=3 size=55 card=5)

5 INDEX FULL SCAN PK_DEPT (cr=1 pr=0 pw=0time=2 us cost=1 size=0 card=5)(object id 69538)

0 FILTER (cr=0 pr=0 pw=0 time=0 us)

0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0pr=0 pw=0 time=0 us cost=1 size=11 card=1)

0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0time=0 us cost=0 size=0 card=1)(object id 69538)

SQL ID :8c1a6t15jh7n0

8c1a6t15jh7n0（上一次的sqlid）

select count(distinct d.dname)

from

dept d whered.deptno=nvl(:1,d.deptno)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.00 0.00 0 3 0 1

------- ------ ------------------ ---------- ---------- ---------- ----------

total 3 0.00 0.00 0 3 0 1

Misses inlibrary cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 81

Rows RowSource Operation

------- ---------------------------------------------------

1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us)

5 CONCATENATION (cr=3 pr=0 pw=0 time=14 us)

5 FILTER (cr=3 pr=0 pw=0 time=10 us)

5 TABLE ACCESS BY INDEX ROWID DEPT (cr=3pr=0 pw=0 time=6 us cost=3 size=55 card=5)

5 INDEX FULL SCAN PK_DEPT (cr=1 pr=0 pw=0time=2 us cost=1 size=0 card=5)(object id 69538)

0 FILTER (cr=0 pr=0 pw=0 time=0 us)

0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0pw=0 time=0 us cost=1 size=11 card=1)

0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0time=0 us cost=0 size=0 card=1)(object id 69538)

Elapsed times include waiting on following events:

Event waitedon Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net messageto client 2 0.00 0.00

SQL*Net messagefrom client 2 0.02 0.04

********************************************************************************

与在数据库中对下列sql的执行计划很相似

select count(distinct d.dname)
from dept d
where d.deptno=nvl(:1,d.deptno)

我们传递了一个null的参数，发现这个执行计划有什么问题？

不应该存在索引扫描，而应该是直接的全表扫描。

如何解决这个问题呢？

五、用WMSYS.WM_CONCAT进行字符累计运算

这个特性只有在10g以后的版本中提供

select version from v$instance;

VERSION

-----------------

10.2.0.1.0

create table test_char_agg

(

idnumber,

val varchar2(20) );

Table created

insert into test_char_agg (ID, VAL) values (10, 'abc');

insert into test_char_agg (ID, VAL) values (10, 'def');

insert into test_char_agg (ID, VAL) values (20, 'ghi');

insert into test_char_agg (ID, VAL) values (20, 'jkl');

insert into test_char_agg (ID, VAL) values (20, 'mno');

select id,val from test_char_agg;

ID VAL

------------------------------

10 abc

10 def

20 ghi

20 jkl

20 mno

SELECT ID, WMSYS.WM_CONCAT(VAL) ASENAMES FROM test_char_agg GROUP BY ID;

ID ENAMES

------------------------------------------------------------------------------------------

10 abc,abc,def,def

20 ghi,jkl,mno,mno

SELECT ID,WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES

FROM test_char_agg

GROUP BY ID

ORDER BY ID;

ID ENAMES

------------------------------------------------------------------------------------------

10 abc,def

20 ghi,jkl,mno

SELECT ID, VAL,WMSYS.WM_CONCAT(VAL) OVER(PARTITION BY ID) AS ENAMES

FROMtest_char_agg

ORDER BY ID;

ID VAL ENAMES

---------- ----------------------------------------------------------------------------------------------------

10 abc abc,abc,def,def

10 def abc,abc,def,def

20 ghi ghi,jkl,mno,mno

20 jkl ghi,jkl,mno,mno

20 mno ghi,jkl,mno,mno

SELECT ID, VAL,WMSYS.WM_CONCAT(VAL) OVER(ORDER BY ID, VAL) AS ENAMES

FROMtest_char_agg

ORDER BY ID;

ID VAL ENAMES

---------- ----------------------------------------------------------------------------------------------------

10 abc abc,abc

10 def abc,abc,def,def

20 ghi abc,abc,def,def,ghi

20 jkl abc,abc,def,def,ghi,jkl

20 mno abc,abc,def,def,ghi,jkl,mno,mno

原创文章，如果转载，请标注作者：田文 CSDN地址：http://blog.csdn.net/tiwen818

Tiwen

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JAVA与ORACLE

JAVA与ORACLE 1、在JDBC用xsu将SELECT转化为xml(cursor)2、在数据库中，访问xml数据服务3、ORACLE JDBC扩展4、java过程察看跟踪文件5、用WMSYS.WM_CONCAT进行字符累计运算一、用ORACLE 的XSU JAVA包将SELECT 查询结果转化为XML数据文件。该包的名称为x
复制链接

扫一扫