oracle笔记3
-----在64位机子上安装plsql
1：下载win32位 的oracle客户端 instantclient-basic-nt-11.2.0.3.0
2：需要在win7 的 系统环境变量 加上 NLS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK <选这个吧> 或者 SIMPLIFIED CHINESE_CHINA.AL32UTF8
3：设置plsql的启动路径Tool-->preference
----python安装cx_oracle
http://tonyqiu.iteye.com/blog/1296583
在导入cx_oracle的时候出现InterfaceError: Unable to acquire Oracle environment handle 可参见如下将所有oci**.dll都拷贝到“C:\Python27\Lib\site-packages”下即可
http://blog.csdn.net/zhangweiwindow/article/details/6575224
----python链接cx_oracle
http://www.cnblogs.com/oubo/archive/2012/07/24/2607034.html
----ptvs安装好了之后 调用cx_oracle的时候没有智能提示cx_oracle的话，则需要将“C:\Python27\Lib\site-packages\cx_Oracle.pyd” 已引用的方式引用到vs的工程中即可。
---ptvs安装后 注释中文的时候 出现 python non-ascii xce 错误 ，则只需要在.py文件的头一行中加入“#coding=gbk”即可
-------爆出一下错误,解决方案如下：
<NAME>\xba\xa3\xd6\xe9\xc7\xf8\xb3\xf6\xbf\xda</NAME><CHN>\xba\xa3\xd6\xe9\xc7\xf8\xb3\xf6\xbf\xda</CHN>
Traceback (most recent call last):
File "E:\prj\Pub_LgVer\Pub_LgVer\Pub_LgVer.py", line 48, in <module>
f.write(' <%s>%s</%s>\r\n'%(l,r[i],l))
File "C:\Python27\lib\codecs.py", line 691, in write
return self.writer.write(data)
File "C:\Python27\lib\codecs.py", line 351, in write
data, consumed = self.encode(object, self.errors)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xba in position 18: ordinal not in range(128)
Press any key to continue . . .
参见：http://www.jb51.net/article/17560.htm；http://www.2cto.com/kf/201207/142453.html
decode是解码
encode是压码
一般windows系统默认都是gbk编码，所以再cmd下能够显示的是gbk
demo: print ‘啊啊啊啊啊啊’.decode('utf-8').encode('bk')
-----使用cxfreeze进行python打包
http://www.crifan.com/use_cx_freeze_to_package_python_to_single_executable_exe/ （cx_freeze下载地址）
-----安装好之后打包方法在cmd中输入cxfreeze c:\123.py 然后在注意下面的提示:
Missing modules: ? IronPythonConsole imported from pyreadline.console.ironpython_console ? System.Windows.Forms.Clipboard imported from pyreadline.clipboard.ironpython_c lipboard ? clr imported from pyreadline.clipboard.ironpython_clipboard ? console imported from pyreadline.console.ansi ? startup imported from pyreadline.keysyms.common ? win32evtlog imported from logging.handlers ? win32evtlogutil imported from logging.handlers copying C:\Python27\DLLs\_ctypes.pyd -> C:\Users\hongliang.lu\dist\_ctypes.pyd copying C:\Python27\DLLs\_hashlib.pyd -> C:\Users\hongliang.lu\dist\_hashlib.pyd copying C:\Python27\DLLs\_socket.pyd -> C:\Users\hongliang.lu\dist\_socket.pyd copying C:\Python27\DLLs\_ssl.pyd -> C:\Users\hongliang.lu\dist\_ssl.pyd copying C:\Python27\DLLs\bz2.pyd -> C:\Users\hongliang.lu\dist\bz2.pyd copying C:\Python27\lib\site-packages\cx_Oracle.pyd -> C:\Users\hongliang.lu\dis t\cx_Oracle.pyd copying C:\Python27\DLLs\unicodedata.pyd -> C:\Users\hongliang.lu\dist\unicodeda ta.pyd
然后再在提示的“C:\Users\hongliang.lu\dist”下面去执行即可，注意：如果使用到了cx_Oracle则需要将C:\instantclient_11_2_64下的所有dll拷贝到C:\Users\hongliang.lu\dist下才可。
------------------------------------------在oracle中使用对象类型的继承
http://blog.csdn.net/rcom10002/article/details/1769461
比如PSUPER为父类
create or replace type PSUPER as object
(
-- Author : HONGLIANG.LU
-- Created : 2013/8/3 16:11:41
-- Purpose : P_SUPER
-- Attributes
cfgname_ varchar2(10),
featcode_ varchar2(10),
--CONSTRUCTOR 构造函数默认是带两个参数的,因为Attributes而定。
--并且Oracle会为每个对象类型生成一个构造函数，其中形参与对象类型的属性相匹配。
--也就是说，参数和属性是一一对应的关系，并且顺序、名称和数据类型都完全相同。
member function compress_sql return varchar2,
-- Member functions and procedures
member function cycle(cfgname varchar2,data_flow_view varchar2,featcode varchar2:='POI') return number,
member function nofeat return varchar2
)NOT FINAL;
create or replace type body PSUPER is
member function compress_sql return varchar2 as
begin
return null;
end;
--先获取此次应该从要素主表获取多少数据
member function cycle(cfgname varchar2,data_flow_view varchar2,featcode varchar2:='POI') return number as
cfg_config PMB_CFG_CONFIG%rowtype;
mr_user varchar2(50);
cycle number(10):=0;
begin
select * into cfg_config from pmb_cfg_config where cfg_name=cfgname;
mr_user:=pmb_comm.parseconn(cfg_config.mdb_conn_info);
if cfg_config.data_source=0 then
execute immediate
'select count(*) from
(select feature_id,rownum rm from
(select distinct feature_id
from '||mr_user||'.'||data_flow_view||' a where
(a.process_result_code=0 or a.process_result_code is null))
) where rm<=:1' into cycle using cfg_config.cntcount;
else
execute immediate
'select count(1) from
(select feat_id,rownum rm from (select distinct feat_id from '||data_flow_view||'))
where rm<=:1' into cycle using cfg_config.cntcount;
end if;
return cycle;
end;
--
member function nofeat return varchar2 as
begin
return null;
end;
end;
-------------------PPOP为子类
create or replace type PPOI under PSUPER
(
-- Author : HONGLIANG.LU
-- Created : 2013/8/3 15:44:50
-- Purpose :
-- Attributes
--CONSTRUCTOR 构造函数不可写一个参数包含两个的，因为在他的父类中默认的就是两个参数的CONSTRUCTOR 。
constructor function PPOI(s varchar2) return self as result,
constructor function PPOI(s varchar2,t varchar2,u varchar2) return self as result,
OVERRIDING member function compress_sql return varchar2,
OVERRIDING member function nofeat return varchar2
)NOT FINAL;
create or replace type body PPOI is
constructor function PPOI(s varchar2) return self as result as
begin
return;
end;
constructor function PPOI(s varchar2,t varchar2,u varchar2) return self as result as
begin
return;
end;
-- 获取配置的字段修改 以便open cur for sql_str;使用
OVERRIDING member function compress_sql return varchar2 as
sql_str varchar2(4000);
cfg_config PMB_CFG_CONFIG%rowtype;
status_Exp number(1);
status_Exp2 varchar2(300);
tname varchar2(50);
pubid integer:=-1;
mr_user varchar2(50);
data_flow_id varchar2(100);
data_flow_view varchar2(100);
begin
select * into cfg_config from pmb_cfg_config where cfg_name=cfgname_;
select from_sql into tname from pmb_cfg_spec where feat_code=featcode_ and spec_ver=cfg_config.spec_ver;
select max(publish_id) into pubid from pmb_log_publish where cfg_name=cfgname_;
select pmb_comm.getfeattb_id(data_flow_id) into data_flow_id from pmb_log_publish where publish_id=pubid;
--等pmb编译
--select pmb.GetDataFlowViewName(pmb_comm.getfeattb_id(data_flow_id)) into data_flow_view from dual;
mr_user:=pmb_comm.parseconn(cfg_config.mdb_conn_info);
--为大版本的时候就不需要A,D,M信息
IF cfg_config.NEED_ADM=0 THEN--需要A,D,M信息
status_Exp:=1;
ELSE
status_Exp:=0;
END IF;
status_Exp2:='pmb.GetDataStatus(poi_id,'''||tname||''','||pubid||',
wm_concat(compile_id'||'||'||'''#''||update_type),wm_concat(update_field_all),'''||
cfg_config.update_field_all||''','''||featcode_||''')';
if cfg_config.data_source=0 then
sql_str:='select * from (select poi_id,max(compile_id) as compile_id,max(Priority) as Priority,
decode('||status_Exp||',0,null,'||status_Exp2||') as update_type
from '||tname||' a where exists (
select 1 from '||mr_user||'.'||data_flow_view||' b
where (b.process_result_code=0 or b.process_result_code is null)
and a.compile_id=b.compile_mark
and a.poi_id=b.feature_id) group by a.poi_id) order by Priority desc';
else
sql_str:='select * from (select poi_id,max(compile_id) as compile_id,max(Priority) as Priority,
decode('||status_Exp||',0,null,'||status_Exp2||') as update_type
from '||data_flow_view||' group by poi_id) order by Priority desc';
end if;
return sql_str;
end;
--
OVERRIDING member function nofeat return varchar2 as
sql_str varchar2(4000);
cfg_config PMB_CFG_CONFIG%rowtype;
tname varchar2(50);
pubid integer:=-1;
mr_user varchar2(50);
data_flow_id varchar2(100);
data_flow_view varchar2(100);
begin
select * into cfg_config from pmb_cfg_config where cfg_name=cfgname_;
select from_sql into tname from pmb_cfg_spec where feat_code=featcode_ and spec_ver=cfg_config.spec_ver;
select max(publish_id) into pubid from pmb_log_publish where cfg_name=cfgname_;
select pmb_comm.getfeattb_id(data_flow_id) into data_flow_id from pmb_log_publish where publish_id=pubid;
--等pmb编译
--select pmb.GetDataFlowViewName(pmb_comm.getfeattb_id(data_flow_id)) into data_flow_view from dual;
mr_user:=pmb_comm.parseconn(cfg_config.mdb_conn_info);
sql_str:='select distinct feature_id
from '||mr_user||'.'||data_flow_view||' a where
(a.process_result_code=0 or a.process_result_code is null)
and not exists (
select 1 from '||tname||' b
where a.compile_mark =b.compile_id
and a.feature_id=b.poi_id)';
return sql_str;
end;
end;
----------调用示例：
declare
feat_class psuper;
begin
feat_class:=ppoi(cfgname,featcode);
end;
如果每次再编译psuper的时候会报出“”的错误则可以先确定此类型没有和其他类型关联的情况下使用下面语句强行删除掉。
drop type PSUPER; drop type PSUPER force ;
----------------------------------------在对象类型中除了可以使用member作为成员方法以外，还可以使用static作为静态方法。
MEMBER方法接受一个内置的SELF参数，它代表了对象类型的实例。不论显式或隐式声明，它总是第一个传入MEMBER方法的参数。但是，STATIC方法就不能接受或引用SELF。如果在member方法中加入了self，就可以修改对象类型中的属性了，否则无法修改。例如“member procedure refresh(self in out nocopy psuper)”
http://blog.csdn.net/wanghai__/article/details/4735727
---------------------------判断字段是否为数字的方法
http://bbs.csdn.net/topics/70525667
方法1：select * from pmb_log_filter a WHERE nvl2(translate(a.feat_id,'\1234567890','\'),'C','N')='C';
方法2：或者使用regexp_like可能会好些，因为方法1 用的时候如果字段不是数字，那么和数字比较的时候就会报错 “无效的数字”错误。
---------------------------------oracle执行较长的sql的时候可以使用 下面的方法，将一个clob执行。
execute immediate
'create view '||v_name||' as '||dbms_lob.substr(sql_str,4000,1)||dbms_lob.substr(sql_str,4000,4001)||dbms_lob.substr(sql_str,4000,8001);
执行sql语句太长,是因为使用了dbms_lob.substr()将比较大的clob切分的时候oracle只获取了clob的前一部分而后一部分为获取。（oracle的一个注意点）
-----------------------------------------oracle字符编码转换
convert('哈哈哈哈', 'ZHS16GBK') 是把字符转换成中文
-------------------------------------自定义聚合函数
为什么要使用自定义聚合函数，因为wm_concat的限制是30KB大小，如果超过了就会报 “ORA-22813: 操作数值超出系统的限制” 错误。
聚合函数的写法如下：
------------类型体声明：
CREATE OR REPLACE TYPE pmb_concat_agg AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT pmb_concat_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT pmb_concat_agg,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN pmb_concat_agg,
RETURNVALUE OUT clob,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT pmb_concat_agg,
SCTX2 IN pmb_concat_agg)
RETURN NUMBER
);
---------------类型体实现
CREATE OR REPLACE TYPE BODY pmb_concat_agg IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT pmb_concat_agg)
RETURN NUMBER IS
BEGIN
SCTX := pmb_concat_agg(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT pmb_concat_agg,
P1 IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN pmb_concat_agg,
RETURNVALUE OUT clob,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT pmb_concat_agg,
SCTX2 IN pmb_concat_agg)
RETURN NUMBER IS
BEGIN
IF (SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
-------------------------构造使用类型体的函数
CREATE OR REPLACE FUNCTION pmb_concat(P1 VARCHAR2) RETURN clob
AGGREGATE USING pmb_concat_agg;
-------------------------使用类型体函数 select pmb_concat(update_field_all) as aaa from pdb_poi where rownum<10000;
------------------------oracle 的no_merge hints
-----------------一些hints ：
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#SQLRF50507
http://zhidao.baidu.com/link?url=P-sPOHKSruzfAU5KvOaqYI4uM9zarg1rArzQJnvFeT3dtWjrup1L_Vyw2rxfXVkwyre79IuD4hQjAyYW4sfHAa
使用情况大体是这样：
如果在查询中用到多个视图，而组成这些视图的SQL语句都是优化好了的，单独访问任何一个视图，性能都没有问题。
如果此时不加NO_MERGE，则ORACLE会自动将若干个视图拆散，重新构造执行计划。
而事实证时，重新构造的执行计划往往会比较糟糕，于是，这种情况下就可以利用NO_MERGE（按字面理解就是不把若干个视图的查询条件进行合并），避免ORACLE将视图的查询拆散。
Views can speed up and simplify application design. A simple view definition can mask data model complexity from the programmers whose priorities are to retrieve, display, collect, and store data.
However, while views provide clean programming interfaces, they can cause sub-optimal, resource-intensive queries. The worst type of view use is when a view references other views, and when they are joined in queries. In many cases, developers can satisfy the query directly from the table without using a view. Usually, because of their inherent properties, views make it difficult for the optimizer to generate the optimal execution plan.
----------demo：
insert into abc
(FEAT_ID,
PUBLISH_ID,
RECORD_TIME,
SEQ_NUM,
LOG_TYPE,
REASON_TYPE,
FEAT_CODE)
select FEAT_ID, 5608, sysdate,seq_tmp.nextval,
'WARN', 129, 'POI'
from pmb_cycle_data a
where
PUBLISH_ID = 5608
and not exists
(select /*+ no_merge(b)*/ 1 from PMB_V_2267890 b where a.feat_id = b.PY_FEAT)
and a.feat_code = 'POI'
---------------------------------------分区索引，全局索引global,本地索引local。
http://blog.csdn.net/nsj820/article/details/6165352 -----------示例
http://blog.csdn.net/wanglilin/article/details/7177338
http://keepwork.iteye.com/blog/1949489
http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html-----------分区操作，合并，分裂（split），添加等等。
本地索引不需要人为的进行维护，而global索引则需要：
create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('2013-09-02','YYYY-MM-DD')),
partition Q2 values less than (TO_DATE('2013-10-02','YYYY-MM-DD')),
partition Q3 values less than (TO_DATE('2013-11-02','YYYY-MM-DD')),
partition Q4 values less than (TO_DATE('2013-12-02','YYYY-MM-DD'))
)
;
create index orders_global_22_idx
on orders(order_no)
local
(partition q1,
partition q2,
partition q3,
partition q4
)
;
create index orders_global_2_idx
on orders(part_no)
global partition by range (part_no)
(partition IND1 values less than (555555),
partition IND2 values less than (MAXVALUE)
)
;
insert into orders(order_no,part_no,ord_date) values(1,'aaa',sysdate);
insert into orders(order_no,part_no,ord_date) values(2,'bbb',sysdate);
insert into orders(order_no,part_no,ord_date) values(3,'ccc',sysdate);
insert into orders(order_no,part_no,ord_date) values(4,'ddd',sysdate);
insert into orders(order_no,part_no,ord_date) values(5,'aeaa',sysdate);
commit;
-----改变orders的分区
alter table orders truncate partition q3
--查看改变分区后，所建立的两个索引的状态
--orders_global_22_idx
select index_name,table_name,partitioning_type,locality from user_part_indexes where index_name=UPPER('orders_global_22_idx');--查看索引是否是global还是local索引
select index_name,partition_name,status from user_ind_partitions where index_name=UPPER('orders_global_22_idx');--查看索引是否还有效
--orders_global_2_idx
select index_name,table_name,partitioning_type,locality from user_part_indexes where index_name=UPPER('orders_global_2_idx');--查看索引是否是global还是local索引
select index_name,partition_name,status from user_ind_partitions where index_name=UPPER('orders_global_2_idx');--查看索引是否还有效
---也可以下面这个进行分析，索引有效性
ANALYZE TABLE orders COMPUTE STATISTICS;
----当索引无效后，需要rebuild索引。,批量一次执行即可。
select 'alter index '||index_name||' rebuild online;' from user_ind_partitions where status='UNUSABLE'
--------------------------------------位图索引，当某列多次重复出现是，可对此表此列使用次索引，会加快查询速度。
http://zhidao.baidu.com/link?url=2IgTBf5rVGA5cphUj9WfQPv8ogHXBF0FzLls00mxhBQ4ILia6n57lBzwUU-8KHF-gLIKtOqAy2gkC7l6s_oy0_
------------------------------------------删除重复数据
delete from test_3 a where rowid<>(select min(rowid) from test_3 b where a.poi_id=b.poi_id)
---------------------------------减少对标的查询,可以使用
select * from test_1 a where a.id=(select max(b.id) from test_2) and a.id2=(select max(b.id2) from test_2);
update test_2 a set a.id=(select max(b.id) from test_2 b),a.id2=(select max(b.id2) from test_2 b) where a.id3=1234;
替换为：
select * from test_1 a where (a.id,a.id2)=(select max(b.id),max(b.id2) from test_2);
update test_2 a set (a.id,a.id2)=(select max(id),min(id2) from test_2) and a.id3=1234;
-------------------------------------如果查询中是多个字段合起来使用的检索，则最好建一个联合索引，并且这并不影响再给联合索引字段中的某一字段建索引。
select * from test_3 where poi_type>'141202' and PCS_POIID='116'--------如果没有见联合索引的话，可能只使用到了PCS_POIID/POI_TYPE一个索引而已。
-------------------------------物理读，逻辑读
http://blog.csdn.net/kkdelta/article/details/7183981
http://www.cnblogs.com/sopost/archive/2010/12/21/2190065.html
1：物理读 physical reads：
从硬盘度去数据到buffer cache(高速缓存)。
2：逻辑读：
临时读 db block gets(从高速缓存区读取数据 ) + 一致读 consistent gets(从高速缓存区/回滚段读取数据 )。
依赖于一个SCN(时间戳)，当执行select的时候有一个SCN号，DML操作的时候也有一个SCN号。如果DML先执行并提交了，则select的结果会是提交前的，否则会是提交后的。
select pcs_poiid from pdb_poi--结果都是 oooo --先执行下面 declare TYPE pmb_CURSOR IS REF CURSOR; cur pmb_CURSOR; xx varchar2(111); begin open cur for 'select pcs_poiid from pdb_poi'; dbms_lock.sleep(10); fetch cur into xx; dbms_output.put_line(xx); end; --3秒后再执行下面 update pdb_poi set pcs_poiid='xxxxx'; commit; --等7秒后查看输出结果为:oooo而不是xxxxx。如果过去掉“dbms_lock.sleep(10);”这句话的话，输出结果会是xxxxx
------------------------------聚簇表，聚簇索引
http://blog.csdn.net/dlodj/article/details/7017194
http://hi.baidu.com/bystander1983/item/7e390d3eacbee0b9124b1435
http://www.itpub.net/thread-1747053-1-1.html
----------------------------------------物理视图：
http://xsb.itpub.net/post/419/49515
http://blog.csdn.net/tianlesoftware/article/details/4713553
