oracle笔记一（sql语句方面）-4

 <1>.字符串字段中含有"'",如果用来组合sql语句，会造成语句不准确。
     比如：replace(f1,'''','')
 <2>.字符串字段中含有"\t \n",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
  比如：replace(f2,'\t','')
 <3>.清除换行和回车
  比如: replace(f2,chr(13)chr(10),'')
23、如何在字符串里加回车或者tab键
  在sqlplus中执行
   sql>select 'UserId=1233111'chr(10)'AccId=13431'chr(9)'AccId2=11111' from dual;

24、树形查询
create table zj(
bm     number(8),
bmmc   varchar2(20),
sjbm   number(8)
)

insert into zj values(1,'aaa',0)
insert into zj values(11,'aaa1',1)
insert into zj values(12,'aaa2',1)
insert into zj values(111,'aaa11',11)
insert into zj values(112,'aaa12',11)
insert into zj values(113,'aaa13',11)
insert into zj values(121,'aaa21',12)
insert into zj values(122,'aaa22',12)
insert into zj values(123,'aaa23',12)
--
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by prior  bm = sjbm
或者

select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by  sjbm = prior  bm

25、快照
    create snapshot SNAPSHOT_NAME
       [storage (storage parameter)]
       [tablespace  TABLESPACE_NAME]
       [refresh  [fast\complete\force]
       [start with  START_DATE next NEXT_DATE]
       as QUERY;
  
   create snapshot snapshot_to_study as select * from ;
   创建角色
     create role aa identified by aaa;
   授权  grant create snapshot,alter snapshot to aaa;
         grant  aaa to emp;
   create snapshot SNAPSHOT_TO_ refresh  complete start with sysdate next
       sysdate+5/(24*60*60) as  select * from ;
   删除  drop snapshot snap_to_html
   手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
       begin
          DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
       end;
   对所有快照进行刷新
       begin
          DBMS_SNAPSHOT.REFRESH_ALL;
       end;
   怎样执行远程的内嵌过程
       begin
         (参数);