SQL> SELECT ename FROM emp ORDER BY ename;
ENAME
----------
CLARK
KING
MILLER 想输出为:
ENAME
----------
CLARK, KING, MILLER 写法:
select substr(max(sys_connect_by_path(ename,',')),2) ename
from (select a.*,row_number()over(order by ename) rn from emp )
start with rn=1
connect by rn-1=prior rn 我想把上面取得的对象放到一个VARCHAR2的变量里去可以么?DECLARE
A VARCHAR2(4000);
BEGIN
select substr(max(sys_connect_by_path(ename,',')),2) ename
from (select a.*,row_number()over(order by ename) rn from emp )
INTO A
start with rn=1
connect by rn-1=prior rn ;
END;
/
为什么老是说有错误呢?SQL Statement ignored
ENAME
----------
CLARK
KING
MILLER 想输出为:
ENAME
----------
CLARK, KING, MILLER 写法:
select substr(max(sys_connect_by_path(ename,',')),2) ename
from (select a.*,row_number()over(order by ename) rn from emp )
start with rn=1
connect by rn-1=prior rn 我想把上面取得的对象放到一个VARCHAR2的变量里去可以么?DECLARE
A VARCHAR2(4000);
BEGIN
select substr(max(sys_connect_by_path(ename,',')),2) ename
from (select a.*,row_number()over(order by ename) rn from emp )
INTO A
start with rn=1
connect by rn-1=prior rn ;
END;
/
为什么老是说有错误呢?SQL Statement ignored
A VARCHAR2(4000);
BEGIN
select substr(max(sys_connect_by_path(ename,',')),2) ename INTO A
from (select a.*,row_number()over(order by ename) rn from emp );
start with rn=1
connect by rn-1=prior rn ;
END; 你赋值给A。A的位置放错了。。
DECLARE
A VARCHAR2(4000);
BEGIN
select ename into A from (select substr(max(sys_connect_by_path(ename,',')),2) ename
from (select a.*,row_number()over(order by ename) rn from emp )
start with rn=1
connect by rn-1=prior rn)
END;
/
单独做个检索能检索到数据,但是放到变量里 就不行啊select ename into A from (select substr(max(sys_connect_by_path(ename,',')),2) ename
*
PL/SQL: ORA-00904: 列名無効。
2 from (select emp.*,row_number()over(order by ename) rn from emp )
3 start with rn=1
4 connect by rn-1=prior rn)
5 /ENAME
--------------------------------------------------------------------------------
CLARK,KING,MILLERSQL>
SQL> DECLARE
2 A VARCHAR2(4000);
3 BEGIN
4 select ename into A from (select substr(max(sys_connect_by_path(ename,',')),2) ename
5 from (select emp.*,row_number()over(order by ename) rn from emp )
6 start with rn=1
7 connect by rn-1=prior rn);
8 END;
9 /
select ename into A from (select substr(max(sys_connect_by_path(ename,',')),2) ename
*
在第4行发生错误。
ORA-06550: 行4、列45:
PL/SQL: ORA-00904: 列名無効。
ORA-06550: 行4、列1:
PL/SQL: SQL Statement ignoredSQL>
* 星号指在sys_connect_by_path的下面
这样就可以了
我晕了
数据库版本不对?
能不能把你的执行画面 考出来
我的数据库Release 9.0.1.1.1 - Production
2 A VARCHAR2(4000);
3 BEGIN
4 select ename into A from (select substr(max(sys_connect_by_path(ename,',')),2) ename
5 from (select emp.*,row_number()over(order by ename) rn from emp )
6 start with rn=1
7 connect by rn-1=prior rn);
8 DBMS_output.put_line(A);
9 END;
10 / 9i,执行下来没错啊。