--case when 的用法
case when length(t1.DEPARTMENT_NO) > 8 then substr(t1.DEPARTMENT_NO, 1, 6) || '...'
else t1.DEPARTMENT_NOend SUB_DEPARTMENT_NO
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--将一个表中数据插入到另一个表中
insert into table1 (login_id,org_rank,department_no,login_name)
select t1.login_id,t2.region_rank,t3.line,t1.user_name
from table t2,pure_user_b t3
where t1.data_area=t2.region_id
and t1.login_id=t3.login_id
--查询login_id重复的数据
select t.login_id from
( select t1.login_id,t2.region_rank,t3.line,t1.user_name
from pure_user t1,kpi_region t2,pure_user_b t3
where t1.data_area=t2.region_id
and t1.login_id=t3.login_id) t
group by login_id
having count(login_id)>1
--复制数据表、sys_connect_by_path函数的使用
create table kpi_region_mid as
select sys_connect_by_path(t.region_id, '/') as region_rank,
level as region_type_id,
t.region_id,
t.region_name,
t.parent_id,
t.mnemonic,
rownum as ord
from kpi.kpi_region t
start with t.region_id = 'root'
connect by prior t.region_id = t.parent_id
还有,这个函数使用之前必须先建立一个树,否则无用。
--查询锁表语句
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
在表上建一个触发器:
CREATE OR REPLACE TRIGGER set_tid BEFORE INSERT ON test2
FOR EACH ROW
DECLARE next_tid NUMBER;
BEGIN
SELECT seq_tid.nextval INTO next_tid FROM dual;
:new.tid:=next_tid;
END;
}
--表操作授权
grant (select、ALL) on kpicode.ods_cb_organ_grid_zb to kpi