好记性不如烂笔头。
三、oracle相关使用记载
3.1、oracle函数调用
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 、创建序列，以多少开始字增多少
create sequence mbr_s_mbr_config
increment by 1
start with 10000
nomaxvalue
nocycle cache 10;
3.3、oracle调用存储过程
begin
PRC_ULM_POINT_FORFEIT ('2016-12-01', '2016-12-31');
end;
3.4、oracel分页问题
查询某条数据：
select m.* from (select rownum rn,t.* from t_user t ) m where rn=6;
查询分业务数据
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
有关序列的这篇文字写的不错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'
