11．快速编译所有视图
---- 当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。 SQL >SPOOL ON.SQL
SQL >SELECT ‘ALTER VIEW ‘||TNAME||’
COMPILE;’ FROM TAB;
SQL >SPOOL OFF 然后执行ON.SQL即可。 SQL >@ON.SQL 当然，授权和创建同义词也可以快速进行，如： SQL >SELECT ‘GRANT SELECT ON ’
||TNAME||’ TO USERNAME;’ FROM TAB;
SQL >SELECT ‘CREATE SYNONYM
‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB; 12．读写文本型操作系统文件---- 在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下： DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN(
‘C:\’,’TEST.TXT’,’A’);
UTL_FILE.PUT_LINE(FILE_HANDLE,’
HELLO,IT’S A TEST TXT FILE’);
UTL_FILE.FCLOSE(FILE_HANDLE);
END; 13．在数据库触发器中使用列的新值与旧值 ---- 在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用:OLD就可以了，使用某列修改后的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。 14．数据库文件的移动方法 当想将数据库文件移动到另外一个目录下时，可以用ALTER DATABASE命令来移动(比ALTER TABLESPACE适用性强)： 1. 使用SERVER MANAGER关闭实例. 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 / DEPTNO DNAME EMPS ------- ----------- ------------------------------------------ 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 16．在Oracle中建一个编号会自动增加的字段,以利于查询 1、建立序列： CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2; 2、建立触发器： CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual; --use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END; 17．查看对象的依赖关系(比如视图与表的引用) 查看视图：dba_dependencies 记录了相关的依赖关系 查东西不知道要查看哪个视图时，可以在DBA_Objects里看， select object_name from dba_objects where object_name like '%ROLE%'(假如查看ROLE相关) 然后DESC一下就大体上知道了。18．要找到某月中所有周五的具体日期 select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五' -------- 03-05-02 03-05-09 03-05-16 03-05-23 03-05-30 如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期 转载:http://blog.csdn.net/nightdays/archive/2006/12/11/1438591.aspx
---- 当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。 SQL >SPOOL ON.SQL
SQL >SELECT ‘ALTER VIEW ‘||TNAME||’
COMPILE;’ FROM TAB;
SQL >SPOOL OFF 然后执行ON.SQL即可。 SQL >@ON.SQL 当然，授权和创建同义词也可以快速进行，如： SQL >SELECT ‘GRANT SELECT ON ’
||TNAME||’ TO USERNAME;’ FROM TAB;
SQL >SELECT ‘CREATE SYNONYM
‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB; 12．读写文本型操作系统文件---- 在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下： DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN(
‘C:\’,’TEST.TXT’,’A’);
UTL_FILE.PUT_LINE(FILE_HANDLE,’
HELLO,IT’S A TEST TXT FILE’);
UTL_FILE.FCLOSE(FILE_HANDLE);
END; 13．在数据库触发器中使用列的新值与旧值 ---- 在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用:OLD就可以了，使用某列修改后的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。 14．数据库文件的移动方法 当想将数据库文件移动到另外一个目录下时，可以用ALTER DATABASE命令来移动(比ALTER TABLESPACE适用性强)： 1. 使用SERVER MANAGER关闭实例. 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 / DEPTNO DNAME EMPS ------- ----------- ------------------------------------------ 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 16．在Oracle中建一个编号会自动增加的字段,以利于查询 1、建立序列： CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2; 2、建立触发器： CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual; --use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END; 17．查看对象的依赖关系(比如视图与表的引用) 查看视图：dba_dependencies 记录了相关的依赖关系 查东西不知道要查看哪个视图时，可以在DBA_Objects里看， select object_name from dba_objects where object_name like '%ROLE%'(假如查看ROLE相关) 然后DESC一下就大体上知道了。18．要找到某月中所有周五的具体日期 select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五' -------- 03-05-02 03-05-09 03-05-16 03-05-23 03-05-30 如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期 转载:http://blog.csdn.net/nightdays/archive/2006/12/11/1438591.aspx
我得仔细看看，现在刚有有个项目要迁移到Oracle，
取得某列中第N大的行select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N； 假如要返回前5条记录： select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)假如要返回第5-9条记录：select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name选出结果后用name排序显示结果。(先选再排序)注意：只能用以上符号(<、<=、!=)。select * from tablename where rownum != 10;返回的是前９条记录。不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件 不成立，查不到记录.另外，这个方法更快：select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10这样取出第11-20条记录!(先选再排序再选) ”
这个好像有问题吧。
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
这个好像不能跟“order by”吧？？难道是我的版本太低了？？？“另外，这个方法更快：select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10”这个选出来的好像不是“order by name”之后的 11-20 行吧，应该是select出rownum<=20行之后再order by name。