insert into a
select (select e from c where d=b.col1) col1,
(select e from c where d=b.col2) col2,
(select e from c where d=b.col2) col3,
(select e from c where d=b.col4) col4
from b
select (select e from c where d=b.col1) col1,
(select e from c where d=b.col2) col2,
(select e from c where d=b.col2) col3,
(select e from c where d=b.col4) col4
from b
解决方案 »
- 对象库(object libraries)不是属于某个form的吗?它和对象组(object groups)有什么区别
- oracle中的正则表达式的奇怪问题regexp_replace
- 过程出错
- oracle 7.3 监听服务起不来
- oracle安装问题,请大家帮忙
- 再問:得到相同NO對應Q的最後一次變更的DATE
- Oarcle Agent服务是干什么用的?不启动行不行?
- 定时器为什么不好使?
- 网线断掉后超时问题
- 包体中,两个相同结构的游标如何进行合并,及再查询之类的?
- 帮忙看一下,这个句子为什么出不来结果?谢谢。语法好像没错,只是一执行就处于等待状态
- 请问有没有像T_SQL中select * into aaa from bbb这样直接产生新表的句子。
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
from (select sID , sNAME, cID, lead(cID) over(partition by sID order by cID desc) fID from (
select sID , sNAME, rownum cID from (select t1.sID, t2.sName from t1, t2 where instr(t1.sID, t2.SID) > 0 order by t1.sID) order by sID , sNAME))
start with fID is null
connect by prior cID = fID
group by sID ;