用SQL管理数据库--基本操作

multiple-table Insert

 

Ø                 作用：

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

 

Ø                 view insert :

规则：view中

1.  不包含 distinct

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

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

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

5. select 中不包含子查询

 

Ø                 例子

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

Ø                 作用范围

DML,DDL,Database Event(BEFORE OR AFTER)

Ø                 数据库事件

logon logoff, startup, shutdown, servererror, suspend

statistics <=>ANALYZE and DDL events

Ø                 CMD

ALTER TRIGGER ENABLE statement

ALTER TABLE ENABLE ALL TRIGGERS statement.

 

 

Ø                 作用

函数，存储，数据结构的容器

 

 

性能Performance

Ø                 参数

[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

 

Ø                 有关对象

BFILE, external table, utl_file, Data Pump.

Ø                 特性

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

Ø                 用法

CREATE DIRECTORY dump_dir AS '/oracle/data_pump/dumps';

Ø                 先决条件

使用--GRANT read,write ON DIRECTORY dump_dir TO PUBLIC;

创建-- CREATE ANY DIRECTORY （缺省只有sys，system用户具备）,慎用，影响系统文件安全