oracle相关使用记载

好记性不如烂笔头。

三、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'