select nodecode
  from (select rownum rn, a.nodecode from lis.ldmenu a order by a.nodecode desc)  ---排序降序 默认升序
 where rn >= 1000 and rn<=1010;  ------选择中间的数据

select distinct a.parentnodecode from lis.ldmenu a;  --不重复

/********************几张表等值连接查询****************/
select a.funcflag a1,
       a.transrno a2, --transno a2,
       b.ProposalNo a3, --ProposalNo a3,  --取值
       a.cardno a4, -- PolNo a4,  保单号-->单证号
       to_char(a.trandate, 'yyyy-mm-dd') || ' ' || a.maketime a5,
       a.TranAmnt a6, --交易金额
       c.name a7, --aa.name a7,
       b.RiskCode a8, --RiskCode a8,
       a.temp2 a9,
			 a.transrno , b.transno,                  ---------注意几张表必须主键必须满足唯一性
       a.bankcode , b.bankcode,
       a.bankzonecode, b.bankbranch
-- a.brno , b.banknode
  from lis.LKBalanceDetail a, lis.lktransstatus b, lis.ldcom c
 where a.funcflag = 'Q9'
   and a.bankcode = '05'
      /* and a.transdate=date'2014-7-31'*/ --可输入日期条件
   --and a.temp2 = '银行有，建信无，对账失败！'
   and a.transrno = b.transno(+)                  ---------注意几张表必须主键必须满足唯一性
   and a.bankcode = b.bankcode(+)                 ---------否则只能使左连接或右连接查询
   and a.bankzonecode = b.bankbranch(+)
      -- and a.brno = b.banknode     --电销会更改banknode的值
   and b.managecom = c.comcode(+);
/********************联合查询****************/
select  '菜单名称:','菜单名称:' from dual
union
select a.nodename,a.runscript from lis.ldmenu a
/********************系统时间处理****************/
select to_char(sysdate,'YYYY-MM-DD'),to_char(sysdate,'HH24:MI:SS'),add_months(sysdate,12)+3/24+30/60/24 ,ROUND((add_months(sysdate,12)+3/24+30/60/24)-sysdate), systimestamp from dual --获取时间
select to_char(sysdate,'YYYY-MM-DD'),to_char(sysdate,'day'),to_char(sysdate,'DD'),to_char(sysdate,'MM')from dual --转换星期

/******************CASE 流程控制语句***************/
select a.nodecode,
       a.nodename,
       (case a.nodecode
         when '8272' then
          '单证扫描'
         when '8274' then
          '扫描'
         else
          'ddd'
       end) as 别名
  from lis.ldmenu a

/**************触发器  自增   当向t_person中添加数据时，会触发自动生成一个id**********/
create or replace trigger trigger_personIdAutuInc
  before insert on t_person
  for each row
declare
begin
  select seq_0_sn.nextval into:new.fid from dual;
end trigger_personIdAutuInc;
/**********开窗函数**********************************/
--函数名（列）OVER(选项)  选项：PARTITION BY
select a.parentnodecode, count(*) over(partition by nodename)
  from lis.ldmenu a
select * from lis.lcpolprint
delete
################################查询数据合并 分组 ################################
select lk.transno,wm_concat(t.riskcode) from gcs.lcgrppol t ,gcs.lktransstatus lk where t.grpcontno = lk.polno and lk.funcflag='ORP011' and lk.rbankvsmp <> '0'-- group by t.grpcontno;
-- wm_concat()函数会对多条数据进行合并，并用“，”分开（value，value，……）
--查询数据合并需要保证数据的唯一性，可以配合 group by 进行配合使用
select t.grpcontno,count(1) from  gcs.lcgrppol t group by t.grpcontno

################################ 正则表达式截取字符串 ################################
select regexp_substr('ASJ454DFDF44','[A-Z]+') from dual
select regexp_substr('ASJ454DFDF44','\d+\w+') from dual
select regexp_substr('ASJ454DFDF44','[0-9]+') from dual
--正则表达式截取字符串  需要对正则表达式比较了解
select lk.transno, wm_concat(regexp_substr(t.riskcode,'[A-Z]+')),lk.funcflag,lk.rbankvsmp

################################ 更新数据  添加数据  ################################
update lccont set CrossType = '3Y', DirectFlag = 'N' where contno = '1408061122';
insert into LJTempFee ( TempFeeNo,TempFeeType,RiskCode,PayIntv,OtherNo,OtherNoType,PayMoney,PayDate,EnterAccDate,ConfDate,ConfMakeDate,SaleChnl,ManageCom,PolicyCom,APPntName,AgentCode,ConfFlag,SerialNo,Operator,MakeTime,MakeDate,ModifyDate,ModifyTime,ContCom,PayEndYear,StandPrem ) values ( '3608500119472','0','OTADD11',0,'282000328385','12',-456.0,'2014-08-06','2014-08-06','2014-08-06','2014-08-06','3','8685050100','8685050100','WuXiao','851B120045','1','980850000382425','YBT0000001','18:25:27','2014-08-06','2014-08-06','18:25:27','8685050100',0,0.0 )

################################ 添加、修改、删除字段 ################################
alter table ldoccupation add liferisk varchar2 (10) null;
alter table ldoccupation modify column liferisk varchar2(255);
alter table ldoccupation drop column liferisk;
-- Add comments to the table
comment on table CSI_USER_INFO is '用户信息表';
-- Add comments to the columns
comment on column CSI_USER_INFO.USER_ID is '用户ID';

################################运行时可以输入参数  a  b ################################
 select 1 from dual  where 1=&b
 union
  select 2 from dual  where 1='&b'
	 union
  select 3 from dual  where 2=&a
/***********多行选择一行*****************************/
select * from ldcode where 1=1 and rownum=1

################################ orcale 空值处理 NULL '' #########################################
/***********如果结果为空时返回固定值，注意数据类型的区别（COALESCE）*****************************/
SELECT COALESCE('','1') FROM dual;
SELECT COALESCE(2,1) FROM dual;
SELECT nvl('2','1') FROM dual ;
SELECT nvl('',1) FROM dual;
SELECT * FROM DUAL WHERE  '' IS NULL OR '' IS NOT NULL;

##########  20150724  ########## orcale 不存在#########################################
NOT EXISTS (SELECT 1 FROM dual);

##########  20150724  ########## orcale 存储过程#########################################
SQL> set serveroutput on;  --显示存储过程输出
SQL> exec COMP_USERINFO_DEAL_UPDATE;  --执行

##########  20150724  ########## orcale数据导入导出 #########################################

exp clientm_test/clientm_test@usercenter_002023_PRO file=D:\dmp\clientm_test.dmp    log=D:\dmp\clientm_test.log  direct=y
imp clientm_test/clientm_test@csi1715w_USER_DEV   file=D:\dmp\clientm_test.dmp  log=D:\dmp\clientm_test_in.log fromuser=clientm_test touser=clientm_test buffer=64000 feedback=1000



mysqldump -uroot --password=root csi csi_index_info csi_index_info_en csi_index_report csi_index_report_en csi_report_list csi_report_list_en csi_index_ysp csi_sys_operate_log >D:\dmp\csi\20160503\csi.sql
mysql -u root -p csi <D:\dmp\csi\20160429\csi.sql
##########  20160223  ##########orcale  to_number函数 #########################################
SELECT to_number('0011100.9900','9999999.0000') FROM dual

##########  20160223  ##########orcale 获取表结构信息 #########################################
select t.TABLE_NAME,
       t.COLUMN_NAME,
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
       t1.COMMENTS
  from user_tab_cols t, user_col_comments t1
 where t.TABLE_NAME = t1.TABLE_NAME
   and t.COLUMN_NAME = t1.COLUMN_NAME

##########  20160323  ##########  orcale生成清库脚本 #########################################
SELECT 'DROP TABLE "'||TABLE_NAME||'";' FROM cat WHERE Table_type='TABLE' AND TABLE_NAME NOT LIKE 'BIN%'
----清库脚本生产
SELECT '--清表'
  FROM DUAL
UNION ALL
SELECT 'drop table ' || A.TABLE_NAME || '' || ';'
  FROM USER_TABLES A
UNION ALL
SELECT '--清序列'
  FROM DUAL --清序列
UNION ALL
SELECT 'drop sequence ' || A.SEQUENCE_NAME || '' || ';'
  FROM USER_SEQUENCES A --清存储过程
UNION ALL
SELECT '--清存储过程'
  FROM DUAL
UNION ALL
SELECT 'drop procedure  ' || A.OBJECT_NAME || '' || ';'
  FROM USER_PROCEDURES A
-- WHERE A.OBJECT_TYPE = 'PROCEDURE' --清包
UNION ALL
SELECT '--清包'
  FROM DUAL
UNION ALL
SELECT DISTINCT 'drop PACKAGE  ' || A.OBJECT_NAME || '' || ';'
  FROM USER_PROCEDURES A
-- WHERE A.OBJECT_TYPE = 'PACKAGE'
UNION ALL
SELECT '--清自定义函数'
  FROM DUAL
UNION ALL
SELECT 'drop FUNCTION ' || A.OBJECT_NAME || '' || ';'
  FROM USER_PROCEDURES A
 --WHERE A.OBJECT_TYPE = 'FUNCTION'
UNION ALL
SELECT '--清视图'
  FROM DUAL
UNION ALL
SELECT 'drop VIEW '||A.VIEW_NAME||';' FROM User_Views A

--清除带引号的表
SELECT 'drop table  "' || A.TABLE_NAME || '"' || ';'
  FROM USER_TABLES A


##########  20160330  ##########  orcale生成一段时间日期列表 #########################################
SELECT * FROM ( SELECT TO_CHAR(to_date(to_char(add_months(sysdate, -1), 'yyyy-MM') || '-01','yyyy-MM-dd') + ROWNUM - 1,
               'yyyy-MM-dd') AS daylist
  FROM DUAL  CONNECT BY ROWNUM < TRUNC(
           to_date(to_char(sysdate, 'yyyy-MM-dd'), 'yyyy-MM-dd') -
           to_date(to_char(add_months(sysdate, -1), 'yyyy-MM') || '-01','yyyy-MM-dd')
           ) + 1)
          where daylist not in ('20150602')

##########  20160330  ##########  orcale日期字符串截取 #########################################
 SELECT * FROM dual WHERE  substr(to_date('2015-07-01','yyyy-MM-dd'), 6, 10) = substr('2015-07-01',4, 7) ; --未处理日期
SELECT  substr(to_char(to_date('2015-07-01','yyyy-MM-dd'),'yyyyMMdd'), 5, 7) , substr('2015-07-01',6, 5) FROM dual
 substr(to_date(u.formal_date,'yyyyMMdd'), 6, 10) = substr(undealtday.daylist,4, 7)  --未处理日期
 SELECT  SUBSTR('2015-07-01',0,4) , substr('20150701',5, 7) FROM dual


##########  20160330  ##########  orcale 数据库配置项 #########################################
select count(*) from v$session   --当前连接数
show parameters processes        --
select username,count(username) from v$session where username is  not null group by username;   --查看用户连接数
select count(*) from v$session where status='ACTIVE'  --
alter system set processes = 500 scope=spfile;  --修改最大连接数
select value from v$parameter where name ='processes' --查看最大连接数

##########  20160330  ##########  orcale 集合关系运算 #########################################
  A MINUS B  = A-B
  A union B  = A+B(去同)
  A union all B  = A+B(不去同)
  取AB差集
  （A MINUS B ）union （B MINUS A)
  取AB交集
  A intersect B  =(AB取同)