select wm_concat(a) from tbname
解决方案 »
- linux安装oracle问题
- oracle中增加自增列的问题
- 请问同表内,不同记录间不同字段的比较问题,谢谢!
- 请问各位大哥,racle\ora81\network\ADMIN\sqlnet.ora这个文件,能删除吗?因为太不安全了~!在线等侍,非常急~~!
- 请问下面3句SQL 语句在pl/sql 中怎样写呢?
- @@@数据库链接不行!ORA-12154: TNS:无法处理服务名
- 在ORCALE如何定义一个新的类型??
- 紧急高分:ora-01033:oracle initialization or shutdown in progress
- 求大神解读代码
- oracle程序如何恢复
- 求一sql写法
- 安装11gR2 Grid 时出错。
create or replace function func_test(tb in varchar2) return varchar2
is
strsql varchar2(8000);
strsql2 varchar2(1000);
strsql3 varchar2(1000);
type cur_type is ref cursor;
cur_test cur_type ;
begin
strsql := null;
strsql2 := null;
strsql3 := 'select ename from '|| tb ;
open cur_test for strsql3 ;
loop
exit when cur_test % notfound ;
fetch cur_test into strsql2 ;
strsql := strsql ||','|| strsql2 ;
end loop;
dbms_output.put_line(strsql);
return strsql ;
end ;
(
select '1' as czbh,'1' as fsr from dual union all
select '5' as czbh,'3' as fsr from dual union all
select '3' as czbh,'3' as fsr from dual union all
select '6' as czbh,'4' as fsr from dual union all
select '7' as czbh,'2' as fsr from dual union all
select '2' as czbh,'2' as fsr from dual union all
select '0' as czbh,'1' as fsr from dual union all
select '15' as czbh,'3' as fsr from dual union all
select '16' as czbh,'4' as fsr from dual union all
select '17' as czbh,'2' as fsr from dual union all
select '12' as czbh,'2' as fsr from dual union all
select '10' as czbh,'1' as fsr from dual union all
select '2' as czbh,'2' as fsr from dual
)
select fsr,max(r)
from (select fsr, wm_concat(czbh) over (partition by fsr order by to_number(czbh)) r from a)
group by fsr刚刚写了一个关于WM_CONCAT函数的用法 希望对你有帮助
COL VARCHAR2(2000);
BEGIN
FOR I IN (
SELECT T.COL||',' AS COL FROM TB T) LOOP
COL := COL||I.COL;
END LOOP;
COL := SUBSTR(COL,1,LENGTH(COL)-1);
DBMS_OUTPUT.put_line(COL);
END;--写出自己曾用过的方式,顺便蹭分
学习一下
二种方法 第一种 写java 查出之后 for循环 拼接字符串
第二种 写个oracle中函数处理直接拿到 你这个拿到的数据最终派什么用处 如果java中要传还是建议第一种方法 如果只是看看 ctrl+c oracle中实现吧
java代码
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import com.xwtech.report.job.model.ReportReconciliation;public class test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
try
{
StringBuffer Result1 = new StringBuffer();
Connection conn = null;
String dbUrl="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String theUser="t_mall";
String thePW="xwt";
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(dbUrl,theUser,thePW);
String stat111 ="select t.A from 表名 t";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(stat111);
while(rs.next()){
String a =rs.getString("A");
Result1.append(a);
}
//Result1 就是你要拿的 }
}catch (IOException e)
{
e.printStackTrace();
}finally{
//w.flush();
//w.close();
conn.close
}
}}
oracle 中代码 一样 随手写不测试 你代码不能老copy别人的 得理解
plsql 打开个窗口复制下面代码 报错自己试着解决】dearcle
ia:=varchar2(2000);
begin
for i in select t.A from 表名 t
loop
ia:=ia||i.A
end loop;
dbms_output.putline(ia)
end;
--------------------------------------------------------------------------------
test,test1,test2,test3
学习一下
,good job