好記性不如爛筆頭。
三近刘、oracle相關(guān)使用記載
3.1、oracle函數(shù)調(diào)用
declare user_id number;
begin
for i in (SELECT distinct t1.user_id FROM t_user t1 left join t_user_category t2 on t1.user_category_id = t2.user_category_id where CATEGORY_TYP = '00001'
and t1.user_id not in (
select distinct(user_id) from t_user where user_category_id in (123,234))
loop
select SEQ_USER.NEXTVAL into user_id from dual;
insert into t_user(user_ID,
MBR_ID,
user_CD,
USER_CATEGORY_ID,
USER_CATEGORY_CD,
REGISTER_IP,
USER_CODE,
START_DATE,
STUS,
CREATE_TIM,
CREATE_BY)
values(mbrship_id,
i.mbr_id,
('123'+mbrship_id),
123,
'634634643',
'123.123.123.123',
'test',
sysdate,
'1',
sysdate,
'DBA');
end loop;
commit;
end;
Paste_Image.png
3.2 臀晃、創(chuàng)建序列觉渴，以多少開(kāi)始字增多少
create sequence mbr_s_mbr_config
increment by 1
start with 10000
nomaxvalue
nocycle cache 10;
3.3、oracle調(diào)用存儲(chǔ)過(guò)程
begin
PRC_ULM_POINT_FORFEIT ('2016-12-01', '2016-12-31');
end;
3.4徽惋、oracel分頁(yè)問(wèn)題
查詢某條數(shù)據(jù)：
select m.* from (select rownum rn,t.* from t_user t ) m where rn=6;
查詢分業(yè)務(wù)數(shù)據(jù)
select m.* from (select rownum rn,t.REGISTER_ID from t_user t) m where rn between 20000000 and 20000010
3.5案淋、查看查詢情況
Paste_Image.png
Paste_Image.png
Paste_Image.png
有關(guān)序列的這篇文字寫(xiě)的不錯(cuò)http://blog.csdn.net/leshami/article/details/7452310
3.6 查看表的空間大小
select owner,segment_name, bytes/ 1024 / 1024 / 1024 GB from dba_segments
where tablespace_name='USER_TAB' and segment_name = 'T_user'
