客服:点击这里给我发消息

手机:15193566610 马先生

奈特网络科技,武威网站建设公司

您的位置:当前位置:武威网站-甘肃奈特网 > 网站建设 > 建站知识 >

Oracle 新手必读,不要错过噢

来源:未知   发表时间:2015-11-10 10:23

    1. 删除表的注重事项  
    在删除一个表中的全部数据时,须使用TRUNCATE TABLE 表名;因为用DROP TABLE,DELETE * FROM 表名时,TABLESPACE表空间该表的占用空间并未释放,反复几次DROP,DELETE操作后, 该TABLESPACE上百兆的空间就被耗光了。  

    2.having 子句的用法  
      having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只答应涉及常量,聚组函数或group by 子句中的列.  

    3.外部联接\"+\"的用法  
      外部联接\"+\"按其在\"=\"的左边或右边分左联接和右联接.若不带\"+\"运算符的表中的一个行不直接匹配于带\"+\"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接\"+\",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢  

    用外联接提高表连接的查询速度  
    在作表连接(常用于视图)时,常使用以下方法来查询数据:  
    SELECT PAY_NO, PROJECT_NAME  
    FROM A  
    WHERE A.PAY_NO NOT IN (SELECT PAY_  
    NO FROM B WHERE VALUE >=120000);  
    ---- 但是若表A有10000条记录,表B有10000条记录,则要用掉30分钟才能查完,主要因为NOT IN要进行一条一条的比较,共需要10000*10000次比较后,才能得到结果。该用外联接后,可以缩短到1分左右的时间:  
    SELECT PAY_NO,PROJECT_NAME  
    FROM A,B  
    WHERE A.PAY_NO=B.PAY_NO(+)  
    AND B.PAY_NO IS NULL  
    AND B.VALUE >=12000;  


    4.set transaction 命令的用法  
    在执行大事务时,有时Oracle会报出如下的错误:  
    ORA-01555:snapshot too old (rollback segment too small)  
      这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如  

    set transaction use rollback segment roll_abc;  
    delete from table_name where ...  
    commit;  
      回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.  


    5.数据库重建应注重的问题  

      在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:首先输入结构,然后输入数据.命令举例如下 (uesrname:jfcl,passWord:hfjf,host stingra1,数据文件:eXPdata.dmp):  

    imp jfcl/hfjf@ora1 file=empdata.dmp rows=N  

    imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000  

    commit=Y ignore=Y  

      第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.  

    select a.empno from emp a where a.empno not in  

    (select empno from emp1 where job=’SALE’);  

      倘若利用外部联接,改写命令如下:  

    select a.empno from emp a ,emp1 b  

    where a.empno=b.empno(+)  

    and b.empno is null  

    and b.job=’SALE’;  

      可以发现,运行速度明显提高.  


    6.从已知表新建另一个表:  
    CREATE TABLE b  
    AS SELECT * (可以是表a中的几列)  
    FROM a  
    WHERE a.column = ...;  


    7.查找、删除重复记录:  

    法一: 用Group by语句 此查找很快的  
    select count(num), max(name) from student --查找表中num列重复的,列出重复的记录数,并列出他的name属性  
    group by num  
    having count(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次  

    delete from student(上面Select的)  
    这样的话就把所有重复的都删除了。
    -----慎重  

    法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法: 
    • ---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录  
      SELECT * FROM EM5_PipE_PREFAB  
      WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相当于First,Second  
      WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND  
      EM5_PIPE_PREFAB.DSNO=D.DSNO);  

      ---- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录  
      DELETE FROM EM5_PIPE_PREFAB  
      WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D  
      WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND  
      EM5_PIPE_PREFAB.DSNO=D.DSNO);  

      8.返回表中[N,M]条记录:  

      取得某列中第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;返回的是前9条记录。  
      不能用:>,>=,=,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嵌套:内层排序外层选。  

      rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!  
      rownum 是在 查询集合产生的过程中产生的伪列,并且假如where条件中存在 rownum 条件的话,则:  
      1: 假如 判定条件是常量,则:  
      只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的, 大于一个数也是没有结果的  
      即 当出现一个 rownum 不满足条件的时候则 查询结束   this is stop key!  
      2: 当判定值不是常量的时候  
      若条件是 = var , 则只有当 var 为1 的时候才满足条件,这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数据进行判定  
      选出一行后才能去选rownum=2的行……  


      9.快速编译所有视图  

      ---- 当在把数据库倒入到新的服务器上后(数据库重建),需要将视图重新编译一遍,因为该表空间视图到其它表空间的表的连接会出现问题,可以利用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;  


      -------------------- 
      让你的天空只有甜和美  

      遗忘———该怎么流泪  

      文章选项:   
        
      Lunatic 
      (stranger) 
      06/13/03 11:33 
         [精华] Re: Oracle 常用命令  [re: Lunatic]     
        


      10.读写文本型操作系统文件  
      ---- 在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;  


        11.在数据库触发器中使用列的新值与旧值  
        ---- 在数据库触发器中几乎总是要使用触发器基表的列值,假如某条语句需要某列修改前的值,使用:OLD就可以了,使用某列修改后的新值,用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。  


        12.数据库文件的移动方法  
        当想将数据库文件移动到另外一个目录下时,可以用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;  


        13.连接查询结果:  
        表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  



        14.在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;  


        15.查看对象的依靠关系(比如视图与表的引用)  

        查看视图:dba_dependencies 记录了相关的依靠关系  
        查东西不知道要查看哪个视图时,可以在DBA_Objects里看,  
        select object_name from dba_objects where object_name like \'%ROLE%\'(假如查看ROLE相关)  
        然后DESC一下就大体上知道了。
          


        16.要找到某月中所有周五的具体日期  
        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,即为查找当前  
        月份的前三个月中的每周五的日期。 
        •  
        •