SVRMGR > connect internal;

SVRMGR > shutdown;

SVRMGR >exit;

2. 使用操作系统命令来移动数据库文件位置(假设这里操作系统为SOLARIS 2.6). 在UNIX中用 mv命令可以把文件移动到新的位置，

#mv /ora13/orarun/document.dbf /ora12/orarun

3. 装载数据库并用alter database命令来改变数据库中的文件名.

SVRMGR > connect internal;

SVRMGR > startup mount RUN73;

SVRMGR > alter database rename file

> ‘/ ora13/orarun/document.dbf’

> ‘/ ora12/orarun/document.dbf’;

4. 启动实例.

SVRMGR > alter database open;

15．连接查询结果：

表a 列 a1 a2

记录 1 a

1 b

2 x

2 y

2 z

用select能选成以下结果:

1 ab

2 xyz

下面有两个例子：

1.使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制

create or replace type strings_table is table of varchar2(20);

/

create or replace function merge (pv in strings_table) return varchar2

is

ls varchar2(4000);

begin

for i in 1..pv.count loop

ls := ls || pv(i);

end loop;

return ls;

end;

/

create table t (id number,name varchar2(10));

insert into t values(1,''''Joan'''');

insert into t values(1,''''Jack'''');

insert into t values(1,''''Tom'''');

insert into t values(2,''''Rose'''');

insert into t values(2,''''Jenny'''');

column names format a80;

select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names

from (select distinct id from t) t0;

drop type strings_table;

drop function merge;

drop table t;

2.纯粹用sql：

表dept, emp

要得到如下结果

deptno, dname, employees

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

10, accounting, clark;king;miller

20, research, smith;adams;ford;scott;jones

30, sales, allen;blake;martin;james;turners

每个dept的employee串起来作为一条记录返回

This example uses a max of 6, and would need more cut n pasting to do more than that：

SQL> select deptno, dname, emps

2 from (

3 select d.deptno, d.dname, rtrim(e.ename ||'''', ''''||

4 lead(e.ename,1) over (partition by d.deptno

5 order by e.ename) ||'''', ''''||

6 lead(e.ename,2) over (partition by d.deptno

7 order by e.ename) ||'''', ''''||

8 lead(e.ename,3) over (partition by d.deptno

9 order by e.ename) ||'''', ''''||

10 lead(e.ename,4) over (partition by d.deptno

11 order by e.ename) ||'''', ''''||

12 lead(e.ename,5) over (partition by d.deptno

13 order by e.ename),'''', '''') emps,

14 row_number () over (partition by d.deptno

15 order by e.ename) x

16 from emp e, dept d

17 where d.deptno = e.deptno

18 )

19 where x = 1

20 /

上一页  [1] [2] [3] [4]  下一页