SQL语句不是死的,它是可以根据你的需要不断变化的,要想用
得好,最重要的是要“熟”:比如:获取系统信息:
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual
/
得好,最重要的是要“熟”:比如:获取系统信息:
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual
/
解决方案 »
- 导入dmp文件错误
- 求HP UNIX操作系统备份到磁盘的命令
- 【求助】这样的需求,SQL要怎么写呢?
- 北京近期举办oracle9iocp培训,有兴趣的朋友参加,一起提高技术
- ===== 求一简单的sql语句 =====
- 怎么样取出最大ID?(急!!!!!)
- 请问如何计算时间和秒数的运算
- 臨時計算出來的字段能不能放在group by子句中?
- 如何能免费下载到Norton Antivirus 2002 for win2000 Advanced Server,必定结贴!
- instr 优化方法forall?
- 请问oracle9i中service名和SID的区别
- Oracle & Linux 错误 , 如何处理 ?
在sqlplus中以dba用户执行:
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,(select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
------------------------------------------------------
OTN 中文技术论坛
OCP 认证 Oracle学习资料
欢迎各路新手和高手光临!
http://211.99.196.144:8090/forum1/frontshow/index.jsp
因此如果想检测一个字符串中是否含有字母,可以如下操作:
SELECT COUNT(*) FROM Yourtab WHERE UPPER(col)<>LOWER(col);同理,因为汉字在 TO_MULTI_BYTE 和 TO_SINGLE_BYTE 之后均保持不变,
从而可以使用如下语句检测一个字符串中是否存在非汉字字符:
SELECT COUNT(*) FROM Yourtab WHERE TO_MULTI_BYTE(col)<>TO_SINGLE_BYTE(col);见笑,见笑;俺这只是雕虫小技罢了,但是可能对于一些网友会有用处,
呵呵,大家集思广益嘛。。关注这
--DoGetDDL.SQL
SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF
SPOOL MyDDL.sql
select DBMS_LOB.substr(DBMS_METADATA.get_ddl('TABLE',u.TABLE_NAME)) FROM USER_TABLES U;
SPOOL OFF
SET HEADING ON
SET ECHO ON
SET FEEDBACK ON
(在没想到用1=2以前,我总是这么做:
create table tableA as select * from tableB;
truncate table tableA;
多写一句,呵呵)如果你还有其它问题,或想获得更多Oracle技术帮助,欢迎到
OTN 中文论坛网址:http://211.99.196.144:8090/ 提问
select ...
union all
select ...
create table WLTM
(
FABID VARCHAR2(1),
SYSID TIMESTAMP(6) not null,
MLOTID VARCHAR2(15),
SOPRODID VARCHAR2(25),
PRODID VARCHAR2(20),
PRODVER VARCHAR2(5),
WOTYPE VARCHAR2(2),
PENTITYQTY NUMBER(10) default 0 not null,
PENTITYUNIT VARCHAR2(1),
ENTITYID VARCHAR2(15) not null,
WONBR VARCHAR2(15),
ENTITYUNIT VARCHAR2(1),
ENTITYCARRY VARCHAR2(1),
ENTITYPOS NUMBER(10) default 0 not null,
ENTITYTQTY NUMBER(10) default 0 not null,
ENTITYGQTY NUMBER(10) default 0 not null,
ENTITYBQTY NUMBER(10) default 0 not null,
ENTITYRQTY NUMBER(10) default 0 not null,
ENTITYSQTY NUMBER(10) default 0 not null,
LOTID VARCHAR2(15) not null,
LOTTYPE VARCHAR2(2),
SERIALNO VARCHAR2(20),
GROUPID VARCHAR2(15),
QANBR VARCHAR2(12),
CARTID VARCHAR2(20),
BOXID VARCHAR2(25),
STARTDATE TIMESTAMP(6),
DUEDATE TIMESTAMP(6),
TRANSID TIMESTAMP(6) not null,
TRANSDATE TIMESTAMP(6) not null,
TRACKINDATE TIMESTAMP(6),
TRACKOUTDATE TIMESTAMP(6),
USERID VARCHAR2(10),
TRACKINUSERID VARCHAR2(10),
SHIFTID VARCHAR2(2),
RTGCD VARCHAR2(8),
RRTGCD VARCHAR2(8),
PEQLINE VARCHAR2(4),
OPRSEQNO NUMBER(10) default 0 not null,
MAXOPRSEQ NUMBER(10) default 0 not null,
PWORKCTR VARCHAR2(4),
OWNCTR VARCHAR2(4),
WORKCTR VARCHAR2(4),
EQLINE VARCHAR2(4),
NEXTEQLINE VARCHAR2(4),
RACK VARCHAR2(2),
CAUCTR VARCHAR2(4),
RWKCTR VARCHAR2(4),
RWKCNT NUMBER(10) default 0 not null,
PENTITYSTS VARCHAR2(1),
ENTITYSTS VARCHAR2(1) default 'S' not null,
ENTITYWIPSTS VARCHAR2(1) default '1' not null,
DEFECTCD VARCHAR2(4),
HOLDCD VARCHAR2(4),
HOLDCTR VARCHAR2(4),
BANKID VARCHAR2(4),
STORAGEID VARCHAR2(5),
LOTOWNER VARCHAR2(10),
LOTPTY VARCHAR2(2),
TRACKMAXNBR NUMBER(10) default 0 not null,
TRACKNBR NUMBER(10) default 0 not null,
LOTGRADE VARCHAR2(3) default 'A' not null,
LOTLEVEL VARCHAR2(2),
CARTGRADE VARCHAR2(3),
FHOLDID TIMESTAMP(6),
HOLDID TIMESTAMP(6),
TRACKMODE VARCHAR2(1) default 'G' not null,
PACKFLG VARCHAR2(1) default '4' not null,
HIDEFLG VARCHAR2(1) default '0' not null,
SPLITFLG VARCHAR2(1) default '0' not null,
MGRADEFLG VARCHAR2(1) default '0' not null,
UNITTRACEFLG VARCHAR2(1) default 'A' not null,
OPT1 VARCHAR2(25),
OPT2 VARCHAR2(20),
OPT3 VARCHAR2(20),
LOTCMT VARCHAR2(50),
MOVEFLG VARCHAR2(1) default '0' not null
)
tablespace DMMSP8K_H2
pctfree 50
initrans 20
maxtrans 255
storage
(
initial 20M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table WLTM
add constraint WLTM_PK primary key (SYSID)
using index
tablespace IMMSP8K_H2
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
-- Create/Recreate check constraints
alter table WLTM
add constraint CHKENTITYBQTY
check (ENTITYBQTY>=0);
alter table WLTM
add constraint CHKENTITYGQTY
check (ENTITYGQTY>=0);
alter table WLTM
add constraint CHKENTITYRQTY
check (ENTITYRQTY>=0);
alter table WLTM
add constraint CHKENTITYSQTY
check (ENTITYSQTY>=0);
alter table WLTM
add constraint CHKENTITYTQTY
check (ENTITYTQTY>=0);
-- Create/Recreate indexes
create index I1WLTMLOT on WLTM (FABID,LOTID,WORKCTR,HIDEFLG)
tablespace IMMSP8K_H2
pctfree 20
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
create index I2WLTMENTITY on WLTM (FABID,ENTITYID,HIDEFLG)
tablespace IMMSP8K_H2
pctfree 20
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
create index I3WLTMLOT on WLTM (FABID,LOTID,HIDEFLG)
tablespace IMMSP8K_H2
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
create index I4WLTMQUERY on WLTM (FABID,WORKCTR,ENTITYSTS,HIDEFLG)
tablespace IMMSP8K_H2
pctfree 40
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
create index I5WLTMCARTON on WLTM (FABID,CARTID,HIDEFLG)
tablespace IMMSP8K_H2
pctfree 20
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
create index WLTM_STRESS on WLTM (FABID,WONBR,WORKCTR,HIDEFLG,ENTITYSTS)
tablespace IMMSP8K_H2
pctfree 20
initrans 2
maxtrans 255
storage
(
initial 5M
minextents 1
maxextents unlimited
);
结分在"溶轩结分"贴
我最多只能一次给100分,sorry。
但是应该以实用为主,这是我现在项目刚刚用到的 1.A表的A1,A2都是B表的外键,A表与B表是一对二的关系,查询出来的结果变成一条记录后如下显示
select A.* ,B1.NAME,B2.NAME FROM A,B B1,B B2 WHERE A.A1=B1.ID AND A.A2=B2.ID 2. select id ,case when id=1 then "一般" when id=2 "优惠" else "其它" from a
select ...case...when...then...when...then...else...end...from...
因此如果想检测一个字符串中是否含有字母,可以如下操作:
SELECT COUNT(*) FROM Yourtab WHERE UPPER(col)<>LOWER(col);
---------------------------------------------------------
这条语句在表记录很多时 会死的很难看.
因此如果想检测一个字符串中是否含有字母,可以如下操作:
SELECT COUNT(*) FROM Yourtab WHERE UPPER(col)<>LOWER(col);
---------------------------------------------------------
这条语句在表记录很多时 会死的很难看.
---------------------------------------------------------
?俺只是个初学者,对于这个问题只能这样解决了,而且在 500 万数据
中测试过,并没有觉得太慢。
不过,您如果有高见的话,最好贴出来,不要只是批评而没有指正!!
哦。是这样。 你提供的语句一般情况不会使用上索引,速度当然很慢。
如果你建了基于函数的索引,那就是另一回事了。 我就随便提供一个批量插入测试数据的例子吧,使用FORALL
成批插入,速度极快.SQL>create table test(v number) nologging;SQL> declare
type numTab is table of number(4) index by binary_integer;
data numTab;
empty numTab;
begin
for j in 1 .. 5000 loop
data(data.count+1) := j;
if ( mod(data.count,1000) = 0 )
then
forall i in 1 .. data.count
insert into test(v) values
( data(i) );
data := empty;
end if;
end loop;
if ( data.count is not null )
then
forall i in 1 .. data.count
insert into test(v) values
( data(i) );
end if;
commit;
end;
/
什么意思?
----按子找父---
select lpad(' ',level-1,' ')||name name from testtree a start with id=10 connect by id=prior parentid;---按父找子--
select lpad(' ',level-1,' ')||name name from testtree a start with id=1 connect by prior id=parentid;
luyuen(滋味) ( ) 信誉:100 2003-12-10 08:52:00 得分:0
order by 的数值型灵活使用
select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny; 控制试图的访问时间:
6.create view ...
as
select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)妙用decode实现排序
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); select * from tabname
order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
找出某个时期内工作日数:
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
in ( '1', '7' )我觉得查询重复记录的语句就很经典
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
由它引申的就有很多有用的语句,如昨天回答别人的排序的难题
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id树型结构表的查询:
select ID,PARENT_ID from parent_child
connect by prior id = parent_id
start with id = 1;1.decode这个函数一定需要会,我觉得sql的灵活很多地方都是通过这个function来体现的,相当于if,很好用。
2.group by,这个东东想想简单,其实好多统计功能是离不开这个操作的。oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注意的是,当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。
3.很表竖置的经典写法,也要记住:sum(decode( )) group by ...
注意:需要在一个subquery中确定一个横置判点。
4.树形结构表的遍历写法:select ...from ....
start with ... connect by prior (父子关系表达式)select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);816以上的 一些分析函数如 rank() over() and row_number() over()
当然关于 group by rollup/cube使用的人恐怕特别少如何实现最大匹配的操作?
例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项
select * from (
select col_FT from table_FT
where instr('12345',col_FT)=1
order by length(col_FT) desc)
where rownum =1给你一个意想不到的东西
SQL> select to_char(to_date(12,'yyyy'),'year') from dual;
TO_CHAR(TO_DATE(12,'YYYY'),'YEAR')
------------------------------------------
twelveselect to_char(sysdate,'day') from dual
还有 d、iw、mm等等格式对于translate函数有一个功能
比如:找出某个字符串中完全是数字
select * from xxx where translate(column1,'1234567890','') = column1;
select trunc(sysdate) from dual;
select trunc(sysdate,'mm') from dual;
大家构造几个例子看看就能明白
select a,b,sum(c) from xxx group by rollup(a,b);select a,b,sum(c) from xxx group by cube(a,b);怎么查找字符串里面包含有%的记录:
当然,常规方法就是利用 escape了
可如果不知道escape也行,比如
select * from xxx where replace(a,'%','') = a;利用decode解决动态sql的不确定条件查询的问题:
假设前台传入的都是变量
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
这样比 like :var||'%' 效率高另:对于
select ...
from a,b
where a.a = b.a(+) and b.a is null;
我想对于不明白的朋友,我要交代一下用处才好:比如,你需要查找在a表中有而b表中没有的记录
也许你会选择 not in:
select * from a aa where aa.a1 not in (select a1 from bb);
这是效率最低的
或者:
select a1 from aa
minus
select a1 from bb;所有这些写法,都不如下面下率高:
select a.* from aa a,bb b
where a.a1 = b.a1(+) and b.a1 is null;给一个很普通的适用的最高效的外连接例子(不是什么新鲜玩意):
select ...
from a,b
where a.a = b.a(+) and b.a is null;我要按年龄段(小于20,20-30,---)统计人数,我可以用
select
sum(decode(sign(age - 20),-1,1,0)),
sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))),
sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))),
sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))),
sum(decode(sign(age - 50),-1,0,1))
from xxx;
这样只做一遍表扫描
这是分了20以下和50以上的
类似的问题,自己扩展了添加行号:
select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by itemselect * from table1 a
where id in (select top 3 from table1 where 物品=a.物品 order by price desc)每一种物品有很多价格,每一种物品选择排在前三的纪录1。job的使用:
DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间:dbms_job.next_date(job,next_date);
修改间隔时间:dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2。把一个表放在内存里
alter table tablename cache.
3。创建临时表
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束
4。加一个自动增加的id号
第一种方法:
第一步:创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
第二种方法:
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
/
5。限制用户登录:创建一个概要文件
create profile CLERK_PROFILE limit
session_per_user 1 #用户可拥有的会话次数
idle_time 10 #进程处于空闲状态的时间(10分钟)
然后就可以将该概要文件授予一个用户
alter user A profile CLERK_PROFILE;
6。使触发器为无效alter trigger yourtriggername disable如果是对于某一个表的所有的触发器:
alter table yourtablename disable all triggers
BigBoyInBigWorld(BigBoyInBigWorld) ( ) 信誉:100 2003-12-10 10:23:00 得分:0
我的,献丑
create or replace procedure pSecurityDaysCaculate is
RecDate date;
Days Number;
type RSSecurity is Record(
BMBM VARCHAR2(50),
NAME VARCHAR2(50),
WZD NUMBER(10),
WTD NUMBER(10),
WD NUMBER(10),
WXX NUMBER(10),
WYB NUMBER(10),
WSW NUMBER(10),
WZS NUMBER(10),
WQS NUMBER(10),
WJX NUMBER(10),
WHZ NUMBER(10),
TARGET NUMBER(10),
TOTARGET NUMBER(10)
);
type RSDateRecord is record(
RDate DATE);
RSD RSDateRecord;
Cursor RSDC is Select * from tDateRecord;
RSS RSSecurity;
Cursor RSSC is select * from table_glaq_aqt;
begin
open rsdc;
fetch rsdc into rsd;
if rsdc%NOTFOUND then
RecDate:=to_date(to_char(sysdate,'yyyy-mm-dd')||' 18:00:00','yyyy-mm-dd hh24:mi:ss');
Insert into tDateRecord Values(RecDate-1);
else
select RDate into RecDate from tDateRecord;
end if;
Days:=trunc(sysDate-RecDate,0);
if days>1 then
Update tDateRecord Set RDate=to_date(to_char(sysdate-1,'yyyy-mm-dd')||'18:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
close rsdc;
open rssc ;
fetch rssc into rss;
if rssc%FOUND then
loop
if rss.totarget=0 then
update table_glaq_aqt set wzd=rss.wzd+days,wtd=rss.wtd+days,wd=rss.wd+days
,wxx=rss.wxx+days,wyb=rss.wyb+days,wsw=rss.wsw+days,wzs=rss.wzs+days
,wqs=rss.wqs+days,wjx=rss.wjx+days,whz=rss.whz+days where bmbm=rss.bmbm;
else
update table_glaq_aqt set wzd=rss.wzd+days,wtd=rss.wtd+days,wd=rss.wd+days
,wxx=rss.wxx+days,wyb=rss.wyb+days,wsw=rss.wsw+days,wzs=rss.wzs+days
,wqs=rss.wqs+days,wjx=rss.wjx+days,whz=rss.whz+days,TOTARGET=rss.TOTARGET-days
where bmbm=rss.bmbm;
end if;
fetch rssc into rss;
exit when rssc%NOTFOUND;
end loop;
end if;
close rssc;
if to_char(sysdate,'hh24')>='18' then
Update tDateRecord Set RDate=to_date(to_char(sysdate,'yyyy-mm-dd')||'18:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
exception
when NO_DATA_FOUND then
raise_application_error(-20100,'no data find!');
when OTHERS then
raise_application_error(-20101,'some custom error message!');
end pSecurityDaysCaculate;
/
为什么效率高呢? 因为使用的Bulk Insert 策略。 那个 data:=empty是将PL/SQL表(Collection)元素值清空. 这个代码很容易改造成一个带输入参数的存储过程,例如:
加两个输入参数(all_count in number, commit_count in numer)
然后在代码中稍做修改就能通用。 但是Bulk Insert 只能用于
静态SQL, 我试过将forall 循环内的静态SQL改成动态SQL,然后用
execute immediate ...执行,是不成功的。
Bulk操作的局限性很大呀。比如它的目标对象不能是包含两列以上的Table类型。这样用起来很不方便。
我给的例子,稍微改一下就可以支持多列。只是它不支持动态SQL,
这是最大的局限性。 SQL> set timing on SQL> create table test2(f1 number,f2 number) nologging;
表已创建. SQL> declare
type numTab is table of number(3) index by binary_integer;
type numTab2 is table of number(6) index by binary_integer;
data numTab;
empty numTab;
data2 numTab2;
empty2 numTab2;
begin
for j in 1 .. 100 loop
data(data.count+1) := j;
data2(data2.count+1) := j*j;
if ( mod(data.count,100) = 0 )
then
forall i in 1 .. data.count
insert into test(v,v2) values
( data(i),data2(i) );
data := empty;
data2:= empty2;
end if;
end loop;
if ( data.count is not null )
then
forall i in 1 .. data.count
insert into test(v,v2) values
( data(i),data2(i) );
end if;
commit;
end;
/ PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.78
应该是: create table test(v number,v2 number) nologging;
select * from cat;
select * from tab;
select table_name from user_tables;
视图:
select text from user_views where view_name=upper('&view_name');
索引:
select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
触发器:
select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照:
select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同义词:
select * from syn;
序列:
select * from seq;
数据库链路:
select * from user_db_links;
约束限制:
select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;
本用户读取其他用户对象的权限:
select * from user_tab_privs;
本用户所拥有的系统权限:
select * from user_sys_privs;
用户:
select * from all_users order by user_id;
表空间剩余自由空间情况:
select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name;
数据字典:
select table_name from dict order by table_name;
锁及资源信息:
select * from v$lock;不包括DDL锁
数据库字符集:
select name,value$ from props$ where name='NLS_CHARACTERSET';
inin.ora参数:
select name,value from v$parameter order by name;
SQL共享池:
select sql_text from v$sqlarea;
数据库:
select * from v$database
控制文件:
select * from V$controlfile;
重做日志文件信息:
select * from V$logfile;
来自控制文件中的日志文件信息:
select * from V$log;
来自控制文件中的数据文件信息:
select * from V$datafile;
NLS参数当前值:
select * from V$nls_parameters;
ORACLE版本信息:
select * from v$version;
描述后台进程:
select * from v$bgprocess;
查看版本信息:
select * from product_component_version; 查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
查看控制文件
select name from v$controlfile; 查看日志文件
select member from v$logfile; 查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle'; 查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;