oracle用sql语句,Oracle常用SQL语句

递归查询部门

select * from table start with id= ? connect by prior id = parent_id;//查询id的子部门(包 括id自身)

select * from table start with id= ? connect by prior parent_id= id;//查询id的父部门(包括id自身)

copy表

create table tableName as select * from tableName;//copy表

设置百分点

concat(to_char(count(distinct l.person_id) /

count(l.person_id) * 100,

‘990.99‘),

‘%‘) baifen

for循环执行SQL语句

declare

begin

for i in 1..100

loop

dbms_output.put_line(‘sql语句‘||i);

end loop;

end;

oracle查询已锁住的表

SELECT

OBJECT_NAME,

SESSION_ID SID,

MACHINE,

VS.MODULE,

‘ALTER SYSTEM KILL SESSION ‘‘‘ || SESSION_ID || ‘, ‘ || SERIAL#|| ‘‘‘; ‘ KILL_SESSION,

VS.STATUS,

VS.ACTION,

SERIAL#,

ORACLE_USERNAME,

OS_USER_NAME

FROM

V$LOCKED_OBJECT VO,

V$SESSION VS,

ALL_OBJECTS AO

WHERE

VO.SESSION_ID = VS.SID

AND AO.OBJECT_ID = VO.OBJECT_ID

AND NVL(VS.ACTION, ‘ ‘) <> ‘Service Management ‘

ORDER BY

OBJECT_NAME,

MACHINE,

VS.MODULE;

其中KILL_SESSION为对应解锁的SQL，需要具有权限才能执行

Oracle常用SQL语句

标签：sel   mod   create   start   bms   ||   for   where   serial

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/yxb-blog/p/12654244.html