1、语法
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
2、说明
1）lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）
2）lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值
3）lag ，lead 分别是向前，向后
3、准备试验环境

CREATE TABLE t(t_NAME VARCHAR2(10),t_order NUMBER);
INSERT INTO t VALUES('aaaa',1);
INSERT INTO t VALUES('bbbb',2);
INSERT INTO t VALUES('cccc',3);
INSERT INTO t VALUES('dddd',6);
INSERT INTO t VALUES('eeee',4);
INSERT INTO t VALUES('ffff',5);
SELECT * FROM t;
T_NAME        T_ORDER
---------- ----------
aaaa                1
bbbb                2
cccc                3
dddd                6
eeee                4
ffff                5
6 rows selected

4、测试

SELECT T_ORDER,
LAG(T_NAME, 1,'lag_fei') OVER(ORDER BY T_ORDER) "LAG",
T_NAME,
LEAD(T_NAME, 1,'lead_fei') OVER(ORDER BY T_ORDER)"LEAD"
FROM T
ORDER BY T_ORDER;
T_ORDER LAG        T_NAME     LEAD
---------- ---------- ---------- ----------
1 lag_fei    aaaa       bbbb
2 aaaa       bbbb       cccc
3 bbbb       cccc       eeee
4 cccc       eeee       ffff
5 eeee       ffff       dddd
6 ffff       dddd       lead_fei
6 rows selected

1、创建表

desc test_rollup;
Name        Type         Nullable Default Comments
----------- ------------ -------- ------- --------
TYPE_NAME   VARCHAR2(10) Y
TYPE_VALUE  NUMBER       Y
TYPE_NAME2  VARCHAR2(10) Y
TYPE_VALUE2 NUMBER       Y  

2、插入数据

select * from test_rollup;
TYPE_NAME  TYPE_VALUE TYPE_NAME2 TYPE_VALUE2
---------- ---------- ---------- -----------
a                 123 t1                 120
a                 423 t2                 200
a                 523 t1                 555
b                 223 x1                 504
b                 283 x2                 484
c                 103 y1                 333
c                 843 y2                 984
c                 899 y2                 151
c                 100 y2                 150
d                 204 s1                 606
10 rows selected

3、使用grouping_id查询结果

 select type_name,
        type_name2,
        decode(grouping_id(type_name), 0, type_name, '总计')g_type,
        decode(grouping_id(type_name2), 0, type_name2, decode(grouping_id(type_name),0,'小计','总计'))g2_type,
        grouping_id(type_name, type_name2)gg_type,
        sum(type_value),
        sum(type_value2)
   from test_rollup
  group by rollup(type_name, type_name2);
TYPE_NAME  TYPE_NAME2 G_TYPE     G2_TYPE       GG_TYPE SUM(TYPE_VALUE) SUM(TYPE_VALUE2)
---------- ---------- ---------- ---------- ---------- --------------- ----------------
a          t1         a          t1                  0             646              675
a          t2         a          t2                  0             423              200
a                     a          小计                1            1069              875
b          x1         b          x1                  0             223              504
b          x2         b          x2                  0             283              484
b                     b          小计                1             506              988
c          y1         c          y1                  0             103              333
c          y2         c          y2                  0            1842             1285
c                     c          小计                1            1945             1618
d          s1         d          s1                  0             204              606
d                     d          小计                1             204              606
                      总计       总计                3            3724             4087
12 rows selected

对于全角和半角互换，oracle 提供了两个函数to_multi_byte和to_single_byte函数

 select to_multi_byte('1234') from dual;
TO_MULTI_BYTE('1234')
---------------------
１２３４
 select to_single_byte('１２３４') from dual;
TO_SINGLE_BYTE('１２３４')
--------------------------
1234

--create directory
create directory ULTLOBDIR as 'd:'
--create table
create table blobtest(col1 BLOB);
create table clobtest(col1 cLOB);
--insert BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert into blobtest values (empty_blob())
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--update BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update blobtest set col1=empty_blob() where rownum=1
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--insert CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert into clobtest values (empty_clob())
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--update CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update clobtest set col1=empty_clob() where rownum=1
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--查询是否成功
select dbms_lob.getlength(col1) from blobtest;
select dbms_lob.getlength(col1) from clobtest;

1、procedure

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
--举例
create or replace
procedure dos_fx
is
cursor c1 is
select get from dos_gj;
begin
 for c2 in c1 loop
 insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),
 REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);
 end loop;
 commit;
end;

2、function

CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]
--举例
CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);
return rawtohex(decrypted_raw);
END;

3、package

--header
CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--body
CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--举例
--header
create or replace
package pk_t1
as
procedure get_num(getnum in number,aname varchar2);
end pk_t1;
--body
create or replace
package body pk_t1
as
procedure get_num(getnum in number,aname varchar2)
is
begin
 insert into shell_1 values(getnum,aname);
end;
end pk_t1;

4、trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
--举例
create or replace trigger add_shell
before update
on shell_1 for each row
declare
begin
 if :OLD.name!=:new.name then
  :new.name:='ggggg';
 end if;
end;

一、简单创建外部表
1、简单模拟外部文件
10,20,30
40,50,60
70,80,90
2、创建外部表用户名和授权

create user test identified by “123” default tablespace test quota unlimited on test;
grant RESOURCE,create session,create any directory to test;

3、创建目录

conn test/"123"
create directory ext as '/sdb/orcl/file';

4、创建外部表

 create table exttable(
　　id number,
     name varchar2(10),
     i number
)
organization external
　(
     type oracle_loader
　　default directory ext
　　access parameters
　　  (
         records delimited by newline
　　    fields terminated by ','
　　  )
     location('ext.dat')
　);

二、使用外部表管理alert文件

create directory alert as 'create directory bdump as '/oracle/u01/app/oracle/admin/db2/bdump';
create table alert_log
(
　text varchar2(400)
)
organization external
　(
     type oracle_loader
　　default directory alert
　　access parameters
　　(
        records delimited by newline
　　)
     location('alert_orcl.log')
　);
--查找到ora错误记录
select * from alert_log where TEXT like 'ORA-%';

从上次在亚旭培训的时候，我和dba讨论一次我开发系统中为了一张表不是非常的大，采用了动态sql创建多个部门的表，然后存取相应的数据，从而解决了一张表过大的问题。当时dba和我说了分区表，我第一感觉，如果当时我知道数据库还有这种表，那我当时开发起来应该轻松的多，后来就一直有个想法，去了解分区表，因为最近自己一直都比较忙，被琐事所困，今天晚上终于抽出了点时间，了解了相关的知识，并做了400多w条数据的一个分区表的测试。
一.范围分区
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。

--例一取值范围：
CREATE TABLE CUSTOMER
(
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
    FIRST_NAME  VARCHAR2(30) NOT NULL,
    LAST_NAME   VARCHAR2(30) NOT NULL,
    PHONE        VARCHAR2(15) NOT NULL,
    EMAIL        VARCHAR2(80),
    STATUS       CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02,
    PARTITION  CUS_PART3 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  CUS_TS02
)
--例二按时间划分（随着时间的增长，还需要添加分区表）：
CREATE TABLE ORDER_ACTIVITIES
(
    ORDER_ID      NUMBER(7) NOT NULL,
    ORDER_DATE    DATE,
    TOTAL_AMOUNT NUMBER,
    CUSTOTMER_ID NUMBER(7),
    PAID           CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
  PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)

二.列表分区
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。

--这个是我在自己电脑上测试的实例，一个sql竞赛的资料库中的数据
create table p_t2(a1 number,a2 varchar2(10),a3 varchar2(30),a4 varchar2(10),a5 number)
    partition by list(a2)
    (partition p_tcp  values('tcp') tablespace p1,
    partition p_udp  values('udp') tablespace p2,
    partition p_icmp  values('icmp') tablespace p3)

三.散列分区
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

CREATE TABLE HASH_TABLE
(
  COL NUMBER(8),
  INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
  PARTITION PART01 TABLESPACE HASH_TS01,
  PARTITION PART02 TABLESPACE HASH_TS02,
  PARTITION PART03 TABLESPACE HASH_TS03
)
--简写：
CREATE TABLE emp
(
    empno NUMBER (4),
    ename VARCHAR2 (30),
    sal   NUMBER
)
PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

四.组合范围散列分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
          (
              SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
              SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
          ),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
          (
              SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
              SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
          )
)

五.复合范围散列分区
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test
 (
 transaction_id number primary key,
 item_id number(8) not null,
 item_description varchar2(300),
 transaction_date date
 )
 partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
 (
     partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
     partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
     partition part_03 values less than(maxvalue)
 );