怎样把多行查询结果集,在一个字段里显示.
解决方案 »
- 求助:存储过程存在一个逻辑判断错误,请帮忙看下!
- 这个效果有什么办法 更新
- Oracle设置物化视图的自动刷新
- ORA-03113...又见ORA-03113..
- 关于表的设计和数据报表的问题。数据生成的报表如下。一起讨论一下该怎样创建表结构。交流经验!!散分
- oracle 执行select 如何将方案名省略?
- (原创)Oracle8.1.7如何在系统启时自动启动---个人总节
- *****奇怪问题:一段sql语句在oracle10g中执行报错,而在92中执行没有问题
- ora-12154:TNS: could not resolve service name 客户端连接数据库时出现这个问题
- 一启动OracleServiceORCL服务就报1053
- 如何调试存储过程
- 从excel表中读日期并插入数据库
create or replace procedure p_test
as
v_rowline table%rowtype
cursor cur is select field1,field2,... from table
begin
open cur
loop
fetch cur into v_rowline
when exit cur%notfound;
execute immediate 'create table t1(field1,field_type)';
execute immediate 'insert into t1 values(v_rowline.field1)';
execute immediate 'insert into t1 values(v_rowline.field2)'
execute immediate 'insert into t1 values(v_rowline.field3)'
...
end loop;
execute immediate 'select field1 from t1';
close cur;
end没测试不知道能不能行.
------------------------------
AA
T_TEST
T
TEST
MIKE_TEST0115SQL> select max(sys_connect_by_path(table_name,',')) from
2 (
3 select table_name, rn,
4 lead(rn) over(order by rn) rn1
5 from
6 ( select table_name,rownum rn from t ))
7 start with rn=1
8 connect by prior rn1=rn
9 /MAX(SYS_CONNECT_BY_PATH(TABLE_
--------------------------------------------------------------------------------
,AA,T_TEST,T,TEST,MIKE_TEST0115
CREATE TABLE TEL
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(8) NULL,
TEL VARCHAR(8) NULL,
)
GO
INSERT INTO TEL
SELECT 'W','1' UNION
SELECT 'W','2'UNION
SELECT 'W','3'UNION
SELECT 'X','4'UNION
SELECT 'X','5'UNION
SELECT 'X','6'UNION
SELECT 'Y','7'UNION
SELECT 'Y','8'UNION
SELECT '衣服','春装'UNION
SELECT '衣服','冬装'UNION
SELECT 'COLOR','RED' UNION
SELECT 'COLOR','YELLOW' UNION
SELECT 'COLOR','BLACK' UNION
SELECT 'COLOR','GREEN' UNION
SELECT 'COLOR','BLACK' UNION
SELECT 'COLOR','CYAN'
GO
CREATE TABLE TELNAME
(
NAME VARCHAR(8) PRIMARY KEY,
TEL VARCHAR(8000) NULL,
)
GO
INSERT INTO TELNAME
(NAME)
SELECT NAME FROM TEL GROUP BY NAME
GO
CREATE FUNCTION V_TEL
(
@TNAME VARCHAR(8)
)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @MESSAGE VARCHAR(8000)
DECLARE CURTEL CURSOR
READ_ONLY
FOR SELECT TEL FROM TEL WHERE [NAME]=@TNAME
SET @MESSAGE=''
DECLARE @NAME VARCHAR(40)
OPEN CURTEL
FETCH NEXT FROM CURTEL INTO @NAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @MESSAGE=@MESSAGE + @NAME+','
END
FETCH NEXT FROM CURTEL INTO @NAME
END
CLOSE CURTEL
DEALLOCATE CURTEL
RETURN (LEFT(@MESSAGE,LEN(@MESSAGE)-1))
END
GOUPDATE TELNAME
SET TEL=(SELECT DBO.V_TEL([NAME]) )
GO
SELECT * FROM TEL
SELECT * FROM TELNAMEDROP TABLE TEL
DROP TABLE TELNAME
DROP FUNCTION V_TEL
1 COLOR BLACK
2 COLOR CYAN
3 COLOR GREEN
4 COLOR RED
5 COLOR YELLOW
6 W 1
7 W 2
8 W 3
9 X 4
10 X 5
11 X 6
12 Y 7
13 Y 8
14 衣服 春装
15 衣服 冬装
运行结果:
COLOR BLACK,CYAN,GREEN,RED,YELLOW
W 1,2,3
X 4,5,6
Y 7,8
衣服 春装,冬装
不知道是不是这个样子的.
Create Table henry_test (a varchar2(10),b int);
Insert Into henry_test values ('aa',1);
Insert Into henry_test values ('bb',1);
Insert Into henry_test values ('cc',1);
Insert Into henry_test values ('dd',2);
Insert Into henry_test values ('ee',2);
Insert Into henry_test values ('ff',3);
Insert Into henry_test values ('gg',3);
Insert Into henry_test values ('hh',3);
Commit;
/*
SQL> select * from henry_test;A B
---------- ---------------------------------------
aa 1
bb 1
cc 1
dd 2
ee 2
ff 3
gg 3
hh 38 rows selected
*/create or replace function f_henry_ConcatRowsByColumn(
Column2Value in Varchar2, --分组该列的值
ColumnName1 in Varchar2, --要连接的列名
ColumnName2 in Varchar2, --用来做分组依据的列名
TableName in Varchar2 --表名
)
return varchar2 is
v_Result varchar2(32767);
type cur_type is ref cursor;
myCur cur_type;
v_Column1Value varchar2(4000);
begin
Open myCur for 'Select '||ColumnName1||' From '||TableName||' Where '||ColumnName2||' = '||Column2Value;
Loop
Fetch myCur Into v_Column1Value;
Exit When myCur%notfound;
v_Result:=v_Result||v_Column1Value||',';
End Loop;
Close myCur;
return(v_Result);
end /*f_henry_ConcatRowsByColumn*/;
/
/*
SQL> select B,f_henry_ConcatRowsByColumn(B,'A','B','henry_test') from henry_test group by B; B F_HENRY_CONCATROWSBYCOLUMN(B,'
--------------------------------------- --------------------------------------------------
1 aa,bb,cc,
2 dd,ee,
3 ff,gg,hh,
*/
Drop Table henry_test;/*
上面的操作仅限于一张表内。
*/