1分析函数
select f_dwbh from (
select f_dwbh,row_number() over ( order by f_dwbh) rn from cf_erp_lrzx
) where rn between 10 and 150;
ORDER BY objid RANGE BETWEEN 5 PRECEDING AND 8 FOLLOWING)
ORDER BY objid ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
first_value() over()：第一个值
last_value() over()：最后一个值
lag(arg1,arg2,arg3) 和 lead(arg1,arg2,arg3) 可以在一次查询中取出同一字段的前n行的数据和后n行的值
lag() over()
lag()函数向下偏移。
lead() over()
lead()函数是向上偏移。
group by rollup(a, b, c)
group by cube(a, b, c)
max/min/sum/avg(col)/count(*) over (partition by idcol order by col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING/RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING/RANGE BETWEEN 150 PRECEDING AND 150 FOLLOWING) 排序（idcol,col）排col
STDDEV计算组的标准偏离 STDDEV_SAMP 计算累积样本标准偏离【一个参数】
VAR_POP 非空集合的总体变量VAR_SAMP 非空集合的样本变量【一个参数】
VARIANCE 如果表达式中行数为1，则返回0如果表达式中行数大于1，则返回VAR_SAMP【一个参数】
COVAR_POP 总体协方差 COVAR_SAMP 样本协方差 CORR 相关系数
StdDev 标准偏差 STDDEV_POP 总体标准偏离 STDDEV_SAMP 样本标准偏离 【一个参数】
VAR_POP 非空集合的总体变量 VAR_SAMP 非空集合的样本变量
CUME_DIST 一行在组中的相对位置【无参】 PERCENT_RANK RATIO_TO_REPORT（value）over() 相对于总数的百分比
PERCENTILE_DISC/ PERCENTILE_CONT 与输入的分布百分比值相对应的数据值
FIRST_VALUE 组中数据窗口的第一个值LAST_VALUE最后一个值
FIRST/LAST ：从DENSE_RANK返回的集合中取出排在最前(后)面的一个值的行
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY yearmonth) OVER (PARTITION BY employeeno) first_salary, --基比分析salary/first_salary
LAG(salary,1,0) OVER (PARTITION BY employeeno ORDER BY yearmonth) AS prev_sal, --环比分析，与上个月份进行比较
LAG(salary,12,0) OVER (PARTITION BY employeeno ORDER BY yearmonth) AS prev_12_sal --同比分析，与上个年度相同月份进行比较
NTILE分组生成散列
Oracle的 BLOB一定要用EMPTY_BLOB()初始化
2更新Blob字段方式
oracle.sql.BLOB blob = ors.getBLOB(1); // **得到BLOB字段
int j = blob.putBytes(1, a); // **将字节数组写入BLOB字段
System.out.println("j:" + j);
test.conn.commit();
3.获取Blob
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("f_file");
InputStream in = blob.getBinaryStream(); // 建立输出流
int len = (int) blob.length();
byte[] buffer = new byte[len]; // 建立缓冲区
in.read(buffer);
4.存储文件
byte[] buffer
in.read(buffer);
in.close();
BASE64Encoder encode = new BASE64Encoder();
String base64 = encode.encode(buffer);
3.取最大值函数
decode(max(objid),null,0,max(objid)) 同A=B?C:D
coalesce（a,b,c）a=null =b,b=null,=c,c=null =null
decode(t4.name,'出差'，t8.destination )
NULLIF (expr1, expr2)：相等返回NULL，不等返回expr1
regexp_like(t.opportunitystatus,'^07$') 用于where字句
regexp_like,regexp_instr,regexp_replace,regexp_substr。
REGEXP_SUBSTR('a,b,c,',', [^,]*,')查找逗号分隔第一个
REGEXP_REPLACE('Joe Smith','( ){2,}', ' ')替换空格
regexp_like(mc,'^[:digit:]') --以':digit'中的任何一个字符开头的字符串
regexp_like(mc,'[^[:digit:]]') --任何含有非数字的字符列表
regexp_like(mc,'^[[:digit:]]') --数字开头
regexp_like(mc,'^[^[:digit:]]') --包含任何非数字开头的
regexp_like(mc,'[[:digit:]]') --任何含有数字的字符列表
将blob字段转化成字符串dbms_lob.substr(o.clobdata)
[:upper:], [:lower:], [:digit:]，[:cntrl:],[:alnum:]数字字符, [:alpha:]大小写, [:blank:]空白键,[:graph:]除空白,[:punct:]标点,[:space:]
--关于正则表达式的后向引用，暂时还是没法理解
select 0,REGEXP_REPLACE('Ellen Hildi Smith','(.) (.) (.*)', '\11\22\33') from dual
union
select 1,REGEXP_REPLACE('Ellen Hildi Smith','(.)(.)(.*)', '\11\22\33') from dual --2，3
union
select 2,REGEXP_REPLACE('Ellen Hildi Smith','(.)(.) (.*)', '\11\22\33') from dual --2
union
select 3,REGEXP_REPLACE('Ellen Hildi Smith','(.) (.)(.*)', '\11\22\33') from dual --3
union
select 4,REGEXP_REPLACE('EllenHildi Smith','(.) (.) (.*)', '\11\22\33') from dual
union
select 5,REGEXP_REPLACE('EllenHildi Smith','(.)(.)(.*)', '\11\22\33') from dual --2，3
union
select 6,REGEXP_REPLACE('EllenHildi Smith','(.)(.) (.*)', '\11\22\33') from dual --2
union
select 7,REGEXP_REPLACE('EllenHildi Smith','(.) (.)(.*)', '\11\22\33') from dual --3
union
select 8,REGEXP_REPLACE('Ellen HildiSmith','(.) (.) (.*)', '\11\22\33') from dual
union
select 9,REGEXP_REPLACE('Ellen HildiSmith','(.)(.)(.*)', '\11\22\33') from dual --2，3
union
select 10,REGEXP_REPLACE('Ellen HildiSmith','(.)(.) (.*)', '\11\22\33') from dual --2
union
select 11,REGEXP_REPLACE('Ellen HildiSmith','(.) (.)(.*)', '\11\22\33') from dual --3
随机生成数字或字符串
-- 'u', 'U' -返回全是大写的字符串
-- 'l', 'L' -返回全是小写的字符串
-- 'a', 'A' -返回大小写结合的字符串
-- 'x', 'X' -返回全是大写和数字的字符串
-- 'p', 'P' -返回键盘上出现字符的随机组合
dbms_random.value(1,101)
DBMS_RANDOM.string('~',5),
DBMS_RANDOM.string('L',5),
$可以将正则表达式位置定位到末尾
[[:lower:]]匹配一个小写字母字符 [cd]c或d
[^[:digit:]]查找包含了任意非数字字符 ^[[:digit:]]查找以数字开始 圆括号 更替元字符 t(a|e|i)n
分割函数(两种方案)
SELECT REGEXP_SUBSTR (indextemplateids, '[^,]+', 1,rownum) n from (select * from plm_investindexlog where objid = :objid) connect by rownum<=LENGTH (indextemplateids) - LENGTH (regexp_replace(indextemplateids, ',', ''))+1;
CREATE OR REPLACE TYPE TYPE_SPLIT AS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION SPLIT(P_STRING VARCHAR2, P_SEP VARCHAR2 := ',')
RETURN TYPE_SPLIT
PIPELINED IS
IDX PLS_INTEGER;
V_STRING VARCHAR2(4000) := P_STRING;
BEGIN
LOOP
IDX := INSTR(V_STRING, P_SEP);
IF IDX > 0 THEN
PIPE ROW(SUBSTR(V_STRING, 1, IDX - 1));
V_STRING := SUBSTR(V_STRING, IDX + LENGTH(P_SEP));
ELSE
PIPE ROW(V_STRING);
EXIT;
END IF;
END LOOP;
END;
select * from table(SPLIT('1,2356,2362,2365',','))
select 'delete '||column_value||';' from (
select * from table(SPLIT(
'CM_USERUICONFIG,CRM_ACCOUNT,CRM_APPOINTMENT,CRM_AUDITRECORD,
CRM_CITY,CRM_COLLECT,CRM_COMMUNICATION,CRM_COOPERATION, CRM_EXPAND, CRM_EXPANDASSESS,CRM_EXPANDATTACHMENT,CRM_EXPANDLOSTRECORD,CRM_EXPAND_AUDIT,CRM_NOTIFY, CRM_OPPORTUNITY,
CRM_PARTICIPANT,CRM_PROVINCE,CRM_SUBSCRIBE,CRM_USERNOTIFY, CRM_USER_ATTCHMENT,DEMOOBJECT,DEMOOBJECT2,DICTIONARY,INDUSTRY,METADB_ENTITY,METADB_ENTITYCATALOG,METADB_IDGENERATOR,
METADB_PROPERTY,ORG_AUTHORIZE,ORG_LOGONLOG,ORG_PSWHIS,ORG_RELATION, ORG_RELATIONSCHEME, SIRM_ATTACHMENT, SIRM_FUNCTIONGROUP,SIRM_FUNCTIONINFO,SIRM_GROUPSETTING,SIRM_MENUFUNCTIONRELA,
SIRM_SETTING,SIRM_SUPPORTCENTER,SPRT_BUSINLOGGER,SPRT_ENTITYRELATION,SPRT_MESSAGE,SPRT_RIGHTDEF,SPRT_SYSTEM, UM_USERKEEPSIGNEDIN,UM_USERSCHEMERELA
'
)))
扫描数据库值
create or replace procedure MY_Pro_SearchKeyWord is
v_sql VARCHAR2(4000);
v_tb_column VARCHAR2(4000);
v_cnt NUMBER(18,0);
cursor cur is SELECT 'SELECT '''||'"'||t1.table_name||'"."'||t1.Column_Name||'"'||''''||' as col_name, NVL(COUNT(t."'||t1.Column_Name||'"),0) as cnt FROM "'||
t1.table_name||'" t WHERE t."'||t1.column_name||'" like ''%关键字%''' AS str
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE'
AND t4.Temporary='Y'
AND t4.Object_Name=t1.Table_Name )
AND (t1.Data_Type='CHAR' or t1.Data_Type='VARCHAR2' or t1.Data_Type='VARCHAR')
ORDER BY t1.Table_Name, t1.Column_ID;
BEGIN
FOR i IN cur LOOP
v_sql := i.str; -- 获取将要执行的SQL语句；
EXECUTE IMMEDIATE v_sql INTO v_tb_COLUMN, v_cnt;
IF v_cnt > 0 THEN
dbms_output.put_line('表：'||substr(v_tb_column,1,instr(v_tb_column,'.',1,1)-1)||' 列：'||substr(v_tb_column,instr(v_tb_column,'.',1,1)+1)||
'有 '||to_char(v_cnt)|| '条记录含有字串"关键字" ');
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
dbms_output.put_line(v_sql);
dbms_output.put_line(v_tb_column);
END;
end MY_Pro_SearchKeyWord;
select * from user_source where TEXT like '%银行%' order by line;
行转列
SELECT COURSE_MARK
FROM (SELECT '罗飞' STU_NAME,
'2001-2002' TERM,
'90' 微积分,
'88' 线性代数,
'85' 数据结构,
'70' 操作系统
FROM DUAL) UNPIVOT(COURSE_MARK FOR TYPE IN （微积分,线性代数,数据结构,操作系统))
SELECT '' || l TYPE, COUNT(*) AS NUM FROM CRM_COOPERATION,(SELECT s - 1 l FROM (SELECT LEVEL s FROM DUAL CONNECT BY LEVEL <= 12)) WHERE INSTR(',' || TYPE || ',', ',' || l || ',') > 0 GROUP BY '' || l
转化成字符串
select cast('adbasdfa' as varchar2(4)) from dual
拼串形成的结果集大于4000,提示超过系统限制
select XMLAGG(XMLELEMENT(E, objid || ',')).EXTRACT('//text()').getclobval() from aml_custinfo
建表语句
select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(dbms_metadata.get_ddl('TABLE','B_STRITRADE_NOTICE'))) from dual;
获取大于2000长度的blob
Create Or Replace Function Blob_To_Varchar (Blob_In In Blob) Return Varchar2
Is
V_Varchar Varchar2(4000);
V_Start Pls_Integer := 1;
V_Buffer Pls_Integer := 4000;
Begin
If Dbms_Lob.Getlength(Blob_In) Is Null Then
Return '';
End If;
For I In 1..Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer) Loop
--当转换出来的字符串乱码时，可尝试用注释掉的函数
--V_Varchar := Utl_Raw.Cast_To_Varchar2(Utl_Raw.Convert(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));
V_Varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start));
V_Start := V_Start + V_Buffer;
End Loop;
Return V_Varchar;
End Blob_To_Varchar;
查询所有表建表语句
declare cursor cjob is select a.TABLE_NAME,b.comments from user_tables a
left join user_tab_comments b on a.TABLE_NAME = b.table_name where a.TABLE_NAME = 'SYS_MENU';
crow cjob%rowtype;
crtsql varchar2(5000):='';
tnote Varchar2(50):='';
begin
for crow in cjob loop
dbms_output.put_line('------------------表名称：'||crow.table_name||'表备注：'||crow.comments||'---------------------');
dbms_output.put_line('declare');
dbms_output.put_line('vCount1 integer;');
dbms_output.put_line('begin');
dbms_output.put_line('select count(*) into vCount1 from user_all_tables where table_name=upper('''||crow.table_name||''');');
dbms_output.put_line('if(vCount1 <= 0 ) then');
dbms_output.put_line('EXECUTE immediate ');
--select substr(,0,instr(,-1,1) ) from dual
/*供应商标准授权*/
dbms_output.put_line('''');
select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(dbms_metadata.get_ddl('TABLE',crow.table_name))) into crtsql from dual;
select substr(crtsql,0,instr(crtsql,')',-1,1) ) into crtsql from dual;
select comments into tnote from user_tab_comments where table_name = crow.table_name;
select replace(replace(crtsql,'"TIEJIAN_02_DEV".',''),'"','') into crtsql from dual;
dbms_output.put_line(crtsql);
dbms_output.put_line(''';');
dbms_output.put_line('EXECUTE immediate ''comment on table '||crow.table_name||' is '''''||tnote||''''''';');
----------添加字段备注--------
declare cursor cjob1 is select column_name,comments from user_col_comments where TABLE_NAME = crow.table_name;
crow1 cjob1%rowtype;
begin
for crow1 in cjob1 loop
--EXECUTE immediate 'comment on table SYS_MENU1 is ''系统菜单表''';
dbms_output.put_line('EXECUTE immediate ''comment on column '||crow.table_name||'.'||crow1.column_name||' is '''''||crow1.comments||''''''';');
end loop;
end;
----------添加字段备注--------
dbms_output.put_line('end if;');
dbms_output.put_line('end;');
dbms_output.put_line('/');
end loop;
end;
恢复drop掉的表
select * from user_recyclebin where original_name like 'B_SUPPLIER_PREWARN%' order by droptime desc;
flashback table B_SUPPLIER_PREWARN to before drop;
