oracle常用笔记（一）

1、操作表

ALTER TABLE TABNAME RENAME TO TABNAME2;--修改表名
ALTER TABLE TABNAME RENAME COLUMN COLNAME TO COLNAME2; --修改表列名
ALTER TABLE TABNAME MODIFY COLNAME NUMBER(20); --修改字段类型
ALTER TABLE TABNAME ADD COLNAME VARCHAR2(40); --添加表列
ALTER TABLE TABNAME DROP COLUMN COLNAME; --删除表列
ALTER TABLE TABNAME ADD (COLNAME1 VARCHAR2(10),COLNAME2 NUMBER); --增加多列
ALTER TABLE TABNAME MODIFY (COLNAME1 VARCHAR2(20),COLNAME2 VARCHAR2(20)); --修改多列
ALTER TABLE TABNAME DROP (COLNAME1,COLNAME2); --删除多列
COMMENT ON TABLE TABNAME IS '表名注释';--表名注释
COMMENT ON COLUMN TABNAME.COLNAME IS '列名注释';--列注释

2、跨库操作-DBLINK

--注意用户名和密码不要引号
--如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。
create public database link dblink1
 connect to dbusername  identified by dbpassword 
 using '(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl)
 )
 )';

--查询带上@dblink1（dblink名称）
select * from TABLE1@dblink1;

3、动态执行sql

拼接的sql中不要加分号，否则报错。

• 动态执行sql并传参

--V_SQLSTR,V_TABLENAME 都是变量
V_SQLSTR := 'INSERT INTO ' || V_TABLENAME || '('
            ||' SELECT DISTINCT KBP,KPI_NO,DCTIME,:1 WRITETIME,TO_NUMBER(VALUE) '
            ||' FROM BIZMAN_PM_TEMP )';

execute immediate V_SQLSTR
using 'abc';--把'abc'代替了 :1 所在的位置

•  动态执行sql并赋值

--vch_Sql，v_s 是变量
vch_Sql:='select sysdate from dual';
EXECUTE IMMEDIATE vch_Sql into v_s;

4、动态执行存储过程

• 在存储过程中动态执行

EXECUTE IMMEDIATE 'BEGIN KDETL.P_ETL_OTC_SRC_BANK (:1,:2,:3,:4,:5); END;'
USING in v_task_id,in v_etl_file,in v_execute_sno, out v_out_flag,out v_out_msg;

•  在命令窗口执行

declare
 out_flag char;
 out_msg varchar2(320);
begin
 p_etl_gd_otc(v_task_id => '',
 v_etl_file => '',
 v_execute_sno => '',
 v_out_flag => out_flag,
 v_out_msg => out_msg); 
end;
/

5、RECORD， VARRAY，TABLE类型

• RECORD

declare
   --自定义一个record模板
 Type user_type is record(
 Id INTEGER,
 USERNAME VARCHAR2(64),
 PASSWORD VARCHAR2(64),
 ACCOUNT NUMBER
 );

 --声明一个user_type类型的变量 userRocord，并放入数据（只能放一条记录，多条则报错）
 userRocord user_type;

begin
 select id, USERNAME, PASSWORD, ACCOUNT into userRocord
 from t_user
 where id = 1;

 --获取变量里的成员值
 dbms_output.put_line(userRocord.USERNAME);

end;
/

•  VARRAY

 数组是具有相同数据类型的一组成员的集合(可变数组-variable array)  

 TYPE VARRAY_NAME IS VARRAY(100) OF student_type[NOT NULL];
 --VARRAY_NAME数组最大容量为100，数组内成员是 student_type类型的，且不能为空。
 --使用：（下标从1开始）

DECLARE
 TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25);
 V_ORG_VARRAY ORG_VARRAY_TYPE;
BEGIN
 V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');
 DBMS_OUTPUT.PUT_LINE('输出1：' || V_ORG_VARRAY(1));
 V_ORG_VARRAY(5) := '5001';
 DBMS_OUTPUT.PUT_LINE('输出5：' || V_ORG_VARRAY(5));
END;
/

• TABLE

定义记录表（或索引表）数据类型，类似于C语言中的二维数组，使得可以在PL/SQL中模仿数据库中的表

--语法：
  TYPE typename IS TABLE OF ELEMENT_TYPE [NOT NULL]
  INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];
--例子：
  TYPE TY_STR_SPLIT IS TABLE OF VARCHAR2 (4000) INDEX BY BINARY_INTEGER;
--INDEX BY BINARY_INTEGER 的作用就是 TY_STR_SPLIT类型在加入元素时，下标是自增长的，不需要调用 EXTEND

--使用
DECLARE
 TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)
 INDEX BY BINARY_INTEGER;
 V_ORG_TABLE ORG_TABLE_TYPE;
BEGIN
 V_ORG_TABLE(1) := '1';
 V_ORG_TABLE(2) := '2';
 V_ORG_TABLE(3) := '3';
 V_ORG_TABLE(4) := '4';
 V_ORG_TABLE(5) := '5';
 DBMS_OUTPUT.PUT_LINE('输出2：' || V_ORG_TABLE(5));
END;
/

--定义方式
DECLARE TYPE TY_STR_SPLIT IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE TYPE "TY_STR_SPLIT" IS TABLE OF VARCHAR2 (4000);

6、空值排序

select * from pro_import_log a order by a.error_reason nulls first; --nulls last

7、oracle使用正则表达式

• regexp_like

--只能用于条件表达式，和 like 类似，例如
select str from tmp where id='like' and regexp_like(str, 'a\d+');

• regexp_substr

使用正则表达式从字符串中抽取子串。

--1. 语法: 
regexp_substr( expression, 
regular-expression 
[, start-offset [ , occurrence-number [, escape-expression ] ] ] )；

参数	含义
expression	被搜索的字符串
regular-expression	匹配的正则表达式
start-offset	指定开始搜索的位置，默认值为 1（expression的起点）
occurrence-number	指定一个整数来表示要定位第几个出现的匹配项，默认值为 1
escape-expression	regular-expression 所使用的转义字符。默认值为反斜线字符

--'aa,bb,cc,dd,ee' 字符串用正则 '[^,]+' 匹配得到5个字符串：'aa' 'bb' 'cc' 'dd' 'ee'
--获取第三个字符子串，则为 'cc'
select regexp_substr('aa,bb,cc,dd,ee','[^,]+', 1, 3) from dual;
--'aa,bb,cc,dd,ee' 字符串从第三个字符开始，用正则 '[^,]+' 匹配得到4个字符串：'bb' 'cc' 'dd' 'ee'
--获取第三个字符子串，则为 'dd'
select regexp_substr('aa,bb,cc,dd,ee','[^,]+', 3, 3) from dual;

• regexp_instr

和 substr 类似，用于拾取符合正则表达式描述的字符子串

--语法
REGEXP_INSTR (source_char, pattern 
[, position [, occurrence [, return_option [, match_parameter ] ] ] ] )

参数	含义
source_char	需要匹配的字符串
pattern	匹配的正则表达式
position	指定开始匹配的位置，默认值为 1（source_char的起点）
occurrence	指定一个整数来表示要定位第几个出现的匹配项，默认值为 1
return_option	如果指定0，那么Oracle将返回出现的第一个字符的位置。这是默认的。 如果指定1，则Oracle返回字符之后发生的位置。
match_parameter	'c'    区分大小写的匹配. 'i'    不区分大小写的匹配. 等等

--从 'aa,bb,cc,dd,ee' 的第3个字符开始向后匹配，匹配到的字符有 'bb' 'cc' 'dd' 'ee',找到第四个字符，即'ee'
--再找到'ee'后面的位置是即 15
select regexp_instr('aa,bb,cc,dd,ee','[^,]+', 3, 4, 1) from dual;

• regexp_replace

和 replace 类似，用于替换符合正则表达式的字符串

--结果为 31
select regexp_replace('31天', '\D', '') from dual;

 

8、游标

• 静态游标简单使用

declare 
  CURSOR cur_emp IS SELECT * FROM t_pro_cls;
  row_emp cur_emp%ROWTYPE;
begin
  open cur_emp;
       fetch cur_emp into row_emp;
       while cur_emp%FOUND LOOP
             dbms_output.put_line(row_emp.cls_name);
             fetch cur_emp into row_emp;
       END LOOP;
  close cur_emp;
end;
/

--带参数的游标，用的最多
--指定参数类型时如果是number或varchar2的话不需要定义长度，否则编译不会通过。
declare
  cursor cur_emp(v_cls_id number) is
    select * from t_pro_cls where cls_id = v_cls_id;
  row_emp cur_emp%ROWTYPE;
begin
  open cur_emp(0);
  fetch cur_emp into row_emp;
  while cur_emp%FOUND LOOP
    dbms_output.put_line(row_emp.cls_name);
    fetch cur_emp into row_emp;
  END LOOP;
  close cur_emp;
end;
/

• 动态游标 

关键字： ref cursor

动态游标分为： 强类型，弱类型

sys_refcursor是oracle9i以后系统定义的一个ref cursor,主要用在过程中返回结果集。 

-- rerurn cls_record_type 表明该游标是强类型的动态游标，否则则为弱类型
Declare
  --定义类型
  Type cls_record_type is record(cls_id number,
                                 cls_name varchar2(64));
  Type cur_cls_type is ref cursor return cls_record_type;
  --定义类型变量
  cur_cls cur_cls_type;
  cls_record cls_record_type;
begin
  --游标定义可以具体的select语句分开
  open cur_cls for select cls_id,cls_name from t_pro_cls;
    fetch cur_cls into cls_record;
    while cur_cls%FOUND LOOP 
      dbms_output.put_line(cls_record.cls_name);
      fetch cur_cls into cls_record;
    END LOOP;
   --不用去close游标
end;

• 游标定义 

在package中定义，可以再其他地方使用该类型

在函数或者过程的变量处定义，则只能在定义的过程和函数中使用。

9、物化视图

• 创建物化视图基本语法

--在源库建立被同步表的物化视图日志（fast增量刷新才需要）
create materialized view log on t_sb_yzpz tablespace m_view;

--在目标库创建物化视图
create materialized view mv_t_Pro_cls
build immediate  --立即加载物化视图的数据
refresh fast on demand --增量刷新，且需要刷新时才会触发刷新
with primary key --用源表的主键来增量刷新
enable query rewrite --查询重写   
start with sysdate 
--这个物化视图在每天10：25进行刷新 
next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss')
as
  select * from t_Pro_cls; --fast增量刷新不支持复杂的sql查询

• 数据加载 

build immediate	创建物化视图的同时，立即根据定义从基础表中获取数据
build deffered	延迟载入数据

• 数据更新 

类别	代码标识	注释
刷新模式	ON COMMIT	基础表数据更新提交后，会自动更新物化视图数据
	ON DEMAND	建立JOB去定时刷新物化视图，在JOB中调用 DBMS_MVIEW.REFRESH 存储过程去刷新物化视图。
刷新方法	FAST	增量式刷新，使用此方法必须有前提，就是建立物化视图日志表。
	COMPLETE	先将物化视图表内容删除，然后再刷新。
	FORCE	如果可以用fast 方式刷新则用fast，否则使用 COMPLETE 刷新。
模式方法结合	refresh force on commit
	refresh fast on commit
	refresh fast on DEMAND

• 调用存储过程刷新物化视图

注意：用FAST 刷新物化视图，前提要新建物化视图日志表。

如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式。

--刷新MV_T_PRO_CLS，MV_T_PRO_CLS2这两个物化视图
--刷新方式 （f、增量刷新，c、完全刷新，?、强制刷新）。
declare
v_mvname varchar2(50);
begin
v_mvname:='MV_T_PRO_CLS，MV_T_PRO_CLS2';
dbms_mview.refresh(v_mvname,'fc');
end;
/

• 查询重写 

enable query rewrite	支持查询重写
disable query rewrite	不支持查询重写，默认为不支持

查询重写是指当对物化视图的基表进行查询时，oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

10、分组求积

在SQL中每行数据相加十分简单,只需要使用SUM,但相乘得使用exp()和ln()的组合：exp(SUM(ln(col)))

有表数据如下： 

--以col2分组，求col1的乘积
select col2,exp(sum(ln(col1))) from test1 group by col2;
--结果如下
--dong	120
--test	6