ORACLE技术小整理

wdyzwvbfkx

1.查询并杀死锁
　　select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.sql_text
   from v$locked_object t1,v$session t2,v$sqltext t3
   where t1.session_id=t2.sid
   and t2.sql_address=t3.address
   order by t2.logon_time;
　　alter system kill session 'sid,serial#';(sid,serial#为查询字段值);
　　
2.分页
　　select * from (select rownum r,* from yourtable where rownum < N) a where a.r betweenN1 and N2;
　　
3.用其他表的数据更新
　　UPDATE (
SELECT T1.BALANCE, T2.MONTH_CUMULATE, T2.QUARTER_CUMULATE, T2.YEAR_CUMULATE
FROM PLSQL_MX_BBHQ_PROCESSED T1, PLSQL_MX_BBHQ_CUMULATE T2
WHERE T1.ACNO = T2.ACNO)
SET
MONTH_CUMULATE = MONTH_CUMULATE + BALANCE ,
QUARTER_CUMULATE = QUARTER_CUMULATE + BALANCE ,
YEAR_CUMULATE = YEAR_CUMULATE + BALANCE ;

　　UPDATE PLSQL_MX_JJ_CUMULATE T2
SET t2.MONTH_CUMULATE = t2.MONTH_CUMULATE + ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno = t2.acno ),
t2.quarter_cumulate = t2.quarter_cumulate + ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno = t2.acno ),
t2.year_cumulate = t2.year_cumulate + ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno = t2.acno )
WHERE EXISTS ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno = t2.acno);

　　


4.全半角转换
　　
FUNCTION FUNC_FULLTOHALF(INSTR VARCHAR2) RETURN VARCHAR2
IS
V_I INTEGER;
OUTSTR varchar2(20);
BEGIN
V_I := 1;
OUTSTR := '';
WHILE V_I <= LENGTH(INSTR) LOOP
IF ASCII(SUBSTR(INSTR, V_I, 1)) > 41856 AND
ASCII(SUBSTR(INSTR, V_I, 1)) < 41856 + 128 THEN
OUTSTR := OUTSTR || CHR(ASCII(SUBSTR(INSTR, V_I, 1)) - 41856);
ELSE
OUTSTR := OUTSTR || SUBSTR(INSTR, V_I, 1);
END IF;
V_I := V_I + 1;
END LOOP;

RETURN OUTSTR;
　　EXCEPTION
WHEN OTHERS THEN RETURN OUTSTR;


END FUNC_FULLTOHALF;
5.关于计划dbms_scheduler
　　a.create job
　　BEGIN
　　
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name=>'delete_goods_job',
job_type=>'STORED_PROCEDURE',
job_action=>'delete_goods',
repeat_interval=>'FREQ=SECONDLY;INTERVAL=6',
enabled=>true,
comments=>'deletegoodsdata'
);
END;
/
　　drop job
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name=>'delete_goods_job');
END;
/
　　disable job
BEGIN
DBMS_SCHEDULER.DISABLE(name=>'delete_goods_job');
END;
/
　　enable job
BEGIN
DBMS_SCHEDULER.ENABLE(name=>'delete_goods_job');
END;
/
　　select jobs
select*fromUSER_SCHEDULER_JOBS;
　　query logs(感谢itpub的teddyboy)
select*fromALL_SCHEDULER_JOB_RUN_DETAILS
whereowner='SCOTT'
orderbylog_datedesc
　　delete logs
deletefromALL_SCHEDULER_JOB_RUN_DETAILS
whereowner='SCOTT'
　　
6.查询约束
　　select a.constraint_name , --约束名称
　　 a.CONSTRAINT_TYPE ,--约束类型
　　 a.TABLE_NAME ,--表名
　　 b.COLUMN_NAME ,--列名
　　 a.SEARCH_CONDITION --约束内容
　　 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b
　　 where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
AND a.constraint_name = '要查询约束名称'