oralce 笔记

来源：互联网发布：mac excel 下拉表头编辑：程序博客网时间：2024/05/24 03:31

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

http://www.anbob.com/?p=221

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)

RETURN VARCHAR2 AS
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中的话

则可以在包的定义中定义。

TYPE t_path_val IS RECORD(
dir VARCHAR2(4000),
fn VARCHAR2(100),
val CLOB,
cnt NUMBER);
TYPE tbl_path_val IS TABLE OF t_path_val;

然后再body中使用

/****************************************************************************************/

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或者forallinsert_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 RNUMFROM (SELECT PCS_POIID RID FROM ' || view_name ||                        ' ORDER BY PCS_POIID))WHERE MOD(RNUM,1000)=0 OR RNUM=1UNIONSELECT 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 ONDECLARE 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;