oracle11g帮助文档
这是所有的:http://www.oracle.com/technetwork/documentation/index.html#database
这是11g下R2的:http://www.oracle.com/pls/db112/homepage
---------http://www.eygle.com/archives/2005/02/ecinaoracleaeoi.html
/**************************oracle 对xml数据处理实例:**************************************************/
select xmlagg(xmlelement("REC", xmlattributes(rownum AS "SEQ_NUM"),
xmlforest(a.seq_num AS "DEFAULT", TRIM(a.image_id) AS "PIC_ID",
a.image_type AS "TYPE", a.quality AS "QUALITY", trim(a.description) AS "REMARKS"))
ORDER BY a.seq_num DESC).getstringval() from pdb_poi_image a where a.poi_id = 1650 AND TRIM(a.image_id) IS NOT NULL
/*************************下面是一个.sql文件可以用bat去执行他***************************************************/
---创建一个job
declare
job integer:=&1;
cus_name varchar2(200):='&2';
w varchar2(200):='pmb.inventory_monit('''||cus_name||''');';
Begin
sys.dbms_job.isubmit(job => job,
what => w,
next_date => sysdate,
interval => 'sysdate+15/1440');
Commit;
End;
/
--运行一个job
declare
job integer:=&1;
Begin
dbms_job.run(job);
Commit;
end;
/
---停止一个job
declare
job integer:=&1;
Begin
dbms_job.broken(job,true,sysdate);
Commit;
end;
/
---删除一个job
declare
job integer:=&1;
Begin
dbms_job.remove(job);
Commit;
end;
/
exit;
/****************************************************************************/
/*逆向遍历p_ty
select reverse(p_ty) into p_ty2 from dual;
open cur for 'select select * from (substr('||p_ty', rownum, 1) data1 from dual connect by rownum <= length('||p_ty||')) order by rownum desc';loop
*/
TRIM ([{{LEADING|TRAILING|BOTH} [trim_characters])|trim_character} FROM] trim_source)
select trim(BOTH '0' from result) into result from dual;--剔除两边的'0'
select trim(TRAILING '0' from result) into result from dual;--剔除右边的'0'
select trim(leading '0' from result) into result from dual;--剔除左边的'0'
/*********************oracle有一种hints技术,挺有趣的*******************/
这是向oracle一种提示,让其如何执行
比如
/* +parallel*/
/* +index*/
/****************************************************************************/
创建sequence
http://zhidao.baidu.com/question/55743158.html
时间戳的使用
http://hi.baidu.com/lhb319/blog/item/deadfe00f5d0788e0b7b82c3.html
时间戳加上0 ,的话就变成了date类型,然后可以跟date比较
oracle中define 和var 的使用,参考:http://www.iteye.com/problems/51049
----------------------------------------------下面是a.bat 红色字体的是参数
sqlplus autonavi2/autonavi2@sdedbdev @b.sql 1 2 wwe1
pause;
-----------------------------------------------------下面是b.sql
------------------test1
define aa=5;
var uu number;
--exec :uu :=1; --ok
exec :uu :=&aa;
select poi,name from poi where rownum<=:uu;
----------------test2
create or replace function aadd return integer as
begin
dbms_output.put_line('test aadd');
return 1;
end aadd;
/
----------------test3
begin
dbms_output.put_line('asdf');
end;
/
----------------test4
define tb_t =poi;
select poi ,name from &tb_t where rownum<2;
----------------test5
define p_1 ='&1';
define p_2 ='&2';
define p_3 ='&3';
variable outtb char ;
select name as name_x from poi where objectid='&p_1';
----------------test6
exec test3('&p_3');
----------------test7
update poi set name=98765 where objectid=&p_2;
commit;
oracle 的imp和exp
http://www.cnblogs.com/jason_lb/archive/2007/02/09/645586.html
oracle 时间戳的大小比较
http://www.itpub.net/thread-1266763-1-1.html
oracle connect by
http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html
http://blog.csdn.net/54powerman/article/details/649068
oracle trim用法
http://wiseboyloves.iteye.com/blog/1233520
定义游标:TYPE pmb_CURSOR IS REF CURSOR;
oracle 自定义数组
type Arr is table of varchar2(200) index by binary_integer;
http://www.cnblogs.com/lll3344/archive/2011/03/09/1978366.html
http://hi.baidu.com/question_how/item/e1d1d12ceefe34d30f37f935
oracle 拆分逗号分隔字符串 实现split
oracle 导入xml到数据库
http://download.csdn.net/download/nbtoms/4468759
http://www.blogjava.net/botson/archive/2009/12/09/187360.html
http://blog.csdn.net/wzy0623/article/details/2839310
oracle 操作json
http://blog.csdn.net/jiujiea6543/article/details/7192542
oracle built-in XML 加载xml 文件到oracle数据库中 ,不过xlm文件必须得先放在服务器上
http://sangei.iteye.com/blog/1139222
样例:
drop table xxrp_acct_detail;
/
create table xxrp_acct_detail (
GLOBAL_ID number,
PCS_POIID varchar2(4000),
NAME xmltype);
/
declare
acct_doc xmltype := xmltype( bfilename('TESTDIR','Shennongjia.xml') , nls_charset_id('ZHS16GBK') );--AL32UTF8
begin
insert into xxrp_acct_detail (GLOBAL_ID, PCS_POIID,NAME)
select *
from xmltable(
'/RichData/POI/POIItem'
passing acct_doc
columns GLOBAL_ID number path 'GLOBAL_ID',
PCS_POIID varchar2(4000) path 'PCS_POIID',
NAME xmltype path 'NAME'
);
commit;
end;
/
select * from xxrp_acct_detail
/*****************************************************************************************************/
对数据进行多行合并成一行:
方法一:使用cast.....MULTISET......
CREATE OR REPLACE TYPE T_PRB_STR_TAB IS TABLE OF VARCHAR2(4000);
/
select poi_id,
CAST(MULTISET(SELECT TRIM(A.name_chn)
FROM pdb_poi_name A
WHERE A.POI_ID = P.POI_ID
ORDER BY A.SEQ_NUM)
AS T_PRB_STR_TAB) AS name_chn
from pdb_poi p where poi_id in (
select poi_id from pdb_poi_name where seq_num>1);
/
-------将cast的返回值使用str_join 函数进行处理就可以了,其实cast的返回值是一个T_PRB_STR_TAB 类型即就是sys_refcursor表的游标
create or replace FUNCTION str_join(in_cur SYS_REFCURSOR, in_sep VARCHAR2 := ',',
trim_flag NUMBER := 1, del_null NUMBER := 1)
content VARCHAR2(32767);
one VARCHAR2(32767);
sep VARCHAR2(1000) := in_sep;
BEGIN
IF sep IS NULL THEN
sep := ',';
END IF;
LOOP
FETCH in_cur
INTO one;
EXIT WHEN in_cur%NOTFOUND;
IF trim_flag > 0 THEN
one := TRIM(one);
END IF;
IF one IS NOT NULL OR del_null = 0 THEN
IF content IS NULL THEN
content := one;
ELSE
content := content || sep || one;
END IF;
END IF;
END LOOP;
CLOSE in_cur;
RETURN content;
EXCEPTION
WHEN OTHERS THEN
RAISE;
RETURN NULL;
END;
方法二: wm_concate()......group by.....
但是这种只能将合并后的内容以逗号分隔开来,不能像方法一 那样可以以任意的符号分隔。
/**************************************************************************************************************/
http://www.cnblogs.com/single-jun/articles/1972612.html
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
Intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;
Minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。
----minus 等同于 not exists ; minus 不支持clob字段的比较 ; Minus返回的总是左边表中的数据
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC
from pmb_amap_mtr
minus
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC from pmb_amap2_mtr
-----intersect 等同于全等 ; intersect返回的总是左边表中的数据
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC
from pmb_amap_mtr
intersect
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC from pmb_amap2_mtr
----union|union all
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC
from pmb_amap_mtr
union|union all
select INPUTTIME,global_id ,PCS_POIID,GEO_PREC from pmb_amap2_mtr
-------/******************************************************************************************************/
用一个表去更新另外一个表
http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html
merge into pmb_amap_mtr a
using pmb_amap_mtr_t b
on (a.global_id = b.global_id)
when matched then
update
set a.geo_prec = b.geo_prec, a.pcs_poiid = b.pcs_poiid
where b.inputtime > sysdate - 1 delete
where a.geo_prec in (15, 16)
when not matched then
insert
values
(b.INPUTTIME,
b.PACKAGE_ID,
b.EXTRACTMANNERS,
b.PATCH_ID,
b.GLOBAL_ID,
b.PCS_POIID,
b.NAME,
b.ADDR,
b.POI_TYPE,
b.GEO_PREC,
b.GEOMETRY,
b.ADMIN,
b.TEL,
b.FREE_TEL,
b.MOBILE_TEL,
b.FAX,
b.STATE) where length
(b.adm_code) = 6 and
(substr(b.adm_code, 1, 2) in ('81', '82'))
/******************************************************************************************/
/**
* Write from a clob to a file with given character encoding.
* If csid is zero or not given then the file will be in the db charset.
* 只不过生成的文件是在服务器上
*/
procedure dbms_xslprocessor.clob2file(cl clob, flocation VARCHAR2, fname VARCHAR2,
csid IN NUMBER := 0);
---------将sql语句生成clob或者xmltype
-----如果想将 sql生成xml或者csv到本地文件的话,则可以使用spool或者先将sql得到的数据生成clob然后通过c++或者.net将clob写入本地文件(这样因为clob会以stream的方式一块块的传给c++或者.net)
create table mmkx (m xmltype);
/
declare
ctx number;
clb clob;
tmpxmltype xmltype;
begin
ctx:=dbms_xmlgen.newContext('select * from pdb_poi where rownum<10');
dbms_xmlgen.setRowSetTag(ctx,'root');
dbms_xmlgen.setRowTag(ctx,'poi');
dbms_xmlgen.getXMLType(ctx,tmpxmltype);
dbms_xmlgen.getXML(ctx,clb);
insert/*+append*/ into mmkx(m) select tmpxmltype from dual ;
DBMS_XMLGEN.closeContext(ctx);
commit;
end ;
/
---------------------
create or replace type mytype is table of varchar2(4000);
/
---------pipe row就相当于替代了return;
create or replace function jjxk(cur sys_refcursor) return mytype pipelined as
pcs_poiid varchar2(400);
begin
loop
fetch cur into pcs_poiid;
exit when cur%notfound;
pipe row(pcs_poiid);
end loop;
end jjxk;
/
-----如果是在package中的话
则可以在包的定义中定义。
dir VARCHAR2(4000),
fn VARCHAR2(100),
val CLOB,
cnt NUMBER);
TYPE tbl_path_val IS TABLE OF t_path_val;
/****************************************************************************************/
oracle 批量(bulk)绑定 进行 update/insert/delete
create or replace type t_name_chn is table of varchar2(1000);
/
declare
type t_gd_bulk2 is table of pls_integer index by binary_integer;---也可以在forall..values of ..中使用索引表 ,但是必须是pls_integer或者binary_integer类型的
global_id_bulk2 t_gd_bulk2;
type t_pdb_name is table of number(10,0);
name_bulk t_pdb_name;
type t_name_v is table of pdb_poi_name.name_chn%type;
name_bulk2 t_name_v;
type t_gd_bulk is table of pls_integer;---forall...values of...必须使用pls_integer或者binary_integer类型的嵌套表
global_id_bulk t_gd_bulk;
type t_pdb is table of pdb_poi%rowtype;
pdb_bulk t_pdb;
begin
-----方法1
name_bulk:=t_pdb_name(1,null,3,null,7);
select name_chn bulk collect into name_bulk2 from pdb_poi_name where rownum<31;
forall i in indices of name_bulk
update poi set name=name_bulk2(i) where name = name_bulk2(i);
-----方法2
global_id_bulk:=t_gd_bulk(3,7,30); ---不能有null 不能超多上面写的rownum<31
forall i in values of global_id_bulk
update poi set name=name_bulk2(i) where name = name_bulk2(i);
--------或者
global_id_bulk2(1):=1;
global_id_bulk2(1):=8;
forall i in values of global_id_bulk2
update poi set name=name_bulk2(i) where name = name_bulk2(i);
-----方法3
forall i in 1..2
--delete from poi where global_id=i;---不能直接使用i
delete from poi where global_id=global_id_bulk(i);
--一般来说想知道 删除了什么多少数据(name_bulk2)
--delete from poi where rownum<10 returning * bulk collect into xxx; --这样有误
delete from poi where rownum<10 returning name bulk collect into name_bulk2;
end;
-/****************************************************************************/
对于集合的操作
declare
type t_x is table of number(30);
t_1 t_x;
t_2 t_x;
t_result t_x;
begin
t_1:=t_x(1,2,3,4,5,6,7,8,9,10);
t_2:=t_x(3,5,4);
--t_result:=t_1 multiset union t_2;
--t_result:=t_1 multiset intersect t_2;
t_result:=t_1 multiset except t_2;
for i in 1..t_result.count loop
dbms_output.put_line(t_result(i));
end loop;
end;
---------------
很多时候在写plsql脚本的时候需要动态绑定变量,使用using来做,当然也可以使用dbms_sql或者forall
insert_sql_2 := insert_sql ||
' WHERE PCS_POIID >= :1 AND PCS_POIID <= :2';
EXECUTE IMMEDIATE insert_sql_2
USING idnum_tab(i).rid, idnum_tab(i + 1).rid;
---------------
很多时候在写plsql脚本的时候需要动态的执行一些语句并使用into,可如下做:
EXECUTE IMMEDIATE 'SELECT * FROM
(SELECT * FROM
(SELECT RID, ROWNUM RNUM
FROM (SELECT PCS_POIID RID FROM ' || view_name ||
' ORDER BY PCS_POIID))
WHERE MOD(RNUM,1000)=0 OR RNUM=1
UNION
SELECT MAX(PCS_POIID) RID, COUNT(1) RNUM FROM ' ||
view_name || ')
ORDER BY RNUM' BULK COLLECT
INTO idnum_tab;
----------嵌套表的使用
http://www.itpub.net/thread-640129-1-1.html
在获取数据的时候可已使用select * from table(select 嵌套字段 from 表) 将嵌套字段转换成为一般表。
------------select * from user_tab_cols ;user_tab_cols 系统表包含了视图,表 的所有列和类型,长度
--------嵌套表的插入和更新
http://www.itpub.net/thread-640129-1-1.html
-------------oracle 调用webservice
http://www.cnblogs.com/raymond19840709/archive/2009/03/26/1422327.html
----------------oracle 使用java自定义函数
http://blog.163.com/dba@126/blog/static/84685642007624113226375/
------------Oracle调用webservice说明
http://bxl766.blog.163.com/blog/static/6109950520091123103932167/
---------------oracle使用rowtype类型方式insert 和update表
http://www.itpub.net/thread-667141-1-1.html
------------------------------insert row
http://www.itpub.net/forum.php?mod=viewthread&tid=733889
--all_orders所有订单;new_records新增加订单(状态1);orders_archive无效订单(状态0)
create table all_orders(id number(18) primary key,mc varchar2(60),order_status number(2));
create table new_orders(id number(18) primary key,mc varchar2(60),order_status number(2));
create table orders_archive(id number(18) primary key,mc varchar2(60),order_status number(2));
insert into all_orders values(1,'mc1',0);
insert into all_orders values(2,'mc2',2);
insert into all_orders values(3,'mc3',2);
insert into all_orders values(4,'mc4',2);
insert into all_orders values(5,'mc5',1);
insert into all_orders values(6,'mc6',0);
insert into all_orders values(7,'mc7',1);
insert into all_orders values(8,'mc8',1);
insert into all_orders values(9,'mc9',1);
insert into all_orders values(10,'mc10',1);
insert into all_orders values(11,'mc11',0);
insert into all_orders values(12,'mc12',1);
insert into all_orders values(13,'mc13',1);
insert into all_orders values(14,'mc14',1);
insert into all_orders values(15,'mc15',1);
insert into all_orders values(16,'mc16',1);
insert into all_orders values(17,'mc17',0);
insert into all_orders values(18,'mc18',1);
insert into all_orders values(19,'mc19',1);
insert into all_orders values(20,'mc20',1);
commit;
SET SERVEROUTPUT ON
DECLARE
TYPE orders_type IS TABLE OF all_orders%ROWTYPE;
TYPE orders_index_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ;
orders_archive orders_type;
new_order orders_type;
all_order orders_type;
orders_archive_index orders_index_type;
order_status INTEGER;
new_orders_count INTEGER := 0;
old_orders_count INTEGER := 0;
BEGIN
SELECT * BULK COLLECT INTO all_order FROM all_orders;
new_order := all_order;
FOR i IN all_order.FIRST .. all_order.LAST LOOP
order_status := all_order(i).order_status ;
IF ( order_status = 2 ) THEN
new_order.DELETE(i);
ELSE
new_orders_count := new_orders_count+1;
END IF;
IF order_status = 0 THEN
orders_archive_index(old_orders_count) := i;
old_orders_count := old_orders_count+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Inserting '|| TO_CHAR(new_orders_count) || ' new ORDERS into NEW_ORDERS table');
DBMS_OUTPUT.PUT_LINE(' ');
FORALL indx IN INDICES OF new_order
INSERT INTO new_orders VALUES all_order(indx);
DBMS_OUTPUT.PUT_LINE('Inserting ' ||TO_CHAR(old_orders_count) ||' old ORDERS data into ORDERS_ARCHIVE table');
FORALL indx IN VALUES OF orders_archive_index
INSERT INTO orders_archive VALUES all_order(indx);
commit;
END;
----------------------------
function get_VANITY_ADMIN(feature_id number,compile_id number) return varchar2 as
Result varchar2(4000);
ADCODE_VANITY t_pmb_VARCHAR2_tab:=t_pmb_VARCHAR2_tab();
begin
select pmb_comm.splitVAR(a.ADCODE_VANITY,'|') into ADCODE_VANITY from PDB_POI_PLUS a where a.POI_ID=feature_id and
a.compile_id=compile_id ;
--------------------会报出“get_VANITY_ADMIN异常:feature_id:28261583compile_id:322sqlcode:-1422sqlerrm:ORA-01422: 实际返回的行数超出请求的行数”的错误:
以下是oracle 给出的解释:
OERR: ORA 1422 "exact fetch returns more than requested number of rows" (文档 ID 18827.1)转到底部
修改时间:2012-7-25类型:REFERENCE状态:PUBLISHED优先级:3
注释 (0)
Error: ORA 1422
Text: exact fetch returns more than requested number of rows
-------------------------------------------------------------------------------
Cause: The number specified in exact fetch is less than the rows returned.
Action: Rewrite the query or change number of rows requested.
*** Important: The notes below are for experienced users - See Note:22080.1
Explanation:
Note that by an 'exact fetch' we mean a SELECT ... INTO ...
i.e. using what is called in PL/SQL an 'implicit' cursor, where the
'number of rows requested' is always 1.
If such a select returns more than one row, and there is no
TOO_MANY_ROWS exception handler in the block, this error is
returned.
(Note that a pre-compiler program may also return a similar error -
for the same reason - except that the error it will return is
'ORA-02112: PCC: SELECT ... INTO returns too many rows' - or
'SQL-02112: SELECT ... INTO returns too many rows'.
The reason for the difference is that the 1422 error is returned from
the server side and 2112 is an error returned by SQLLIB, the Oracle
runtime library used by pre-compiled programs - which is at the client
side.---------不同于'ORA-02112: PCC: SELECT ... INTO returns too many rows'
However, a pre-compiler program will *never* return this error if it was
pre-compiled with SELECT_ERROR=NO.)
The 'action' specified above (taken from oraus.msg) is rather dubious
- the advice should really be to use an explicit cursor or investigate
why more than one row is being returned if this is unexpected.
The first thing this error indicates is that the programmer has not
written good exception-handling into the code.
However one of the less obvious reasons for getting this error is that
you are doing:
'select ... where <column> = <variable>'
and the column and the variable have the same name.
In this case pl/sql will resolve this ambiguity by assuming you mean--------歧义,假设查询为下
'select ... where <column> = <column>' - i.e. all rows.--------所有的行
-------------------------------------------在使用oracle的xmlagg的时候会有内存泄露 需要打oracle的补丁。
以下是oracle给出的解释:
关于上面涉及到的bug的介绍: 查询使用XMLAGG会导致内存泄漏。
Bug 8849734 - SQLX query using XMLAGG spins / PGA memory leak in kolaGetRfcHeap / Private memory corruption [ID 8849734.8]转到底部
修改时间:2013-6-28类型:PATCH状态:PUBLISHED优先级:3
注释 (0)
Bug 8849734 SQLX query using XMLAGG spins / PGA memory leak in kolaGetRfcHeap / Private memory corruption
This note gives a brief overview of bug 8849734.
The content was last updated on: 28-JUN-2013
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Xdb)
Range of versions believed to be affected Versions >= 10.2.0.4 but BELOW 12.1
Versions confirmed as being affected
11.2.0.1
11.1.0.7
10.2.0.5
10.2.0.4
Platforms affected Generic (all / most platforms affected)
It is believed to be a regression in default behaviour thus:
Regression introduced in 10.2.0.4
Regression introduced in 11.1.0.6
Fixed:
This issue is fixed in
12.1.0.1 (Base Release)
11.2.0.2 (Server Patch Set)
Symptoms:
Related To:
Error May Occur
Hang (Process Spins)
Leak (Memory Leak / Growth)
Memory Corruption
ORA-4030
ORA-600 [17147]
ORA-600 [15851]
Stack is likely to include qxuageag
XDB
XMLAGG
Description
A memory leak is introduced in 10.2.0.4 / 11.1.0.6 by the fix for
bug 5735091. Additionally memory corruption can still occur when
using XMLAGG() even if that fix is present.
A memory leak and/or memory corruption may be seen when using XMLAGG()
with the fix for bug 5735091 in place.
Rediscovery Notes:
For the memory leak:
The query will appear to hang / spin with increasing memory use and/or
raise an ORA-4030 with possibly different arguments every time.
Heapdumps of the process running the query will shows lots of
memory allocated against the "kolaGetRfcHeap" heap as
"perm" allocation.
With event 10235 level 65536 set heapdumps show this perm memory
to be mostly of type "kghsseg: kolasl"
For the memory corruption:
A dump may occur under qxuageag , or various ORA-600 errors
may be reported executing a SQL using XMLAGG typically with
corruption in the "session heap"
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References
Bug:8849734 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article
----------------------------------------------如果想在存储过程中使用
dba_objects==》查看对象
或者dba_ind_columns==》查看对象索引
或者dba_tab_cols ==》查看对象的列
的时候必须赋予权限
Grant create session to user_123;
------------------------------------------
-- 描述 : 关系或
function bitor(x integer,y integer) return integer as
res integer;
begin
res := (x + y) - BITAND(x, y);
return res;
end;
-----------------------------自治事务会引起死锁
create table eymit(id int);
insert into eymit select 1 from dual;
commit;
delete from eymit;
declare
pragma autonomous_transaction;
begin
delete from eymit;
commit;
end;
declare
pragma autonomous_transaction;
begin
delete from eymit;
commit;
end;