用SQL管理数据库

 

multiple-table Insert

 

 作用： 从一个表同时导入到多个表，可以提升效率。

 

view insert :

l         规则：view中

         1.  不包含 distinct

         2. 不包含操作符：union ,minus

         3. 不包含聚合函数：sum，count，avg

         4. 不包含group by ，order by ， connect by

         5. select 中不包含子查询

 

l         例子:INSERT FIRST WHEN policy_type = 'P' THEN INTO property_premium_fact

         (policy_id, policy_nbr, premium_amt)

       VALUES

             (property_premium_seq.nextval, policy_number, gross_premium)

                                   WHEN p.policy_type = 'C' THEN INTO casualty_premium_fact

         (policy_id, policy_nbr, premium_amt)

      VALUES

            (property_premium_seq.nextval, policy_number, gross_premium)

  SELECT policy_nbr, gross_premium, policy_type

    FROM policies

   WHERE policy_date >= TRUNC(SYSDATE, 'MM') - TO_YMINTERVAL('00-01');

 

 

Trigger

l         作用范围: DML,DDL,Database Event(BEFORE OR AFTER)

l         数据库事件:logon logoff, startup, shutdown, servererror, suspend

                   statistics <=>ANALYZE and DDL events

cmd：

           ALTER TRIGGER ENABLE statement

           ALTER TABLE ENABLE ALL TRIGGERS statement.

 

 

l         作用： 函数，存储，数据结构的容器

 

 

性能Performance

l         参数:

[1] PLSQL_WARNING

[1] PLSQL_DEBUG

[1] PLSQL_OPTIMIZE_MODE

[1] PLSQL_CODE_TYPE  1.native machine code  ------provides faster runtime performance at a cost of longer compilation times and slightly greater administrative

                                         2.default-interpreted byte

 

目录 Directory

 

l         有关对象:BFILE, external table, utl_file, Data Pump.

l         特性： 不属于schema对象，只属于数据库（同profile, role）

l         用法:    CREATE DIRECTORY dump_dir AS '/oracle/data_pump/dumps';

 

l         先决条件: 使用--GRANT read,write ON DIRECTORY dump_dir TO PUBLIC;