经典sql语句.docx
《经典sql语句.docx》由会员分享，可在线阅读，更多相关《经典sql语句.docx（25页珍藏版）》请在冰豆网上搜索。
经典sql语句
设为首页加入收藏网站报错网站导航立即注册首页资讯信息化管理开发项目酷站生活期刊网址帮助业界软件E商务人物营销品牌战略文化人力流程财务研发职场创业客户规划ERPPDMCRMSOABISCMMESCIO服务器局域网需求架构建模测试.NetC#JavaHtmlms-sqlOracleJavascript频道首页Asp.NetC#VB.NetASPJavaJScriptAjaxCSSHtmlJavaJSPPHPMSSQLOracleMySQLSQLiteDB2XMLSEOC++FlashSilverLight移动开发Digg列表你所在的位置:
网站－Web开发 经典SQL语句收集 感谢作者:
来源:
日期:
2009-9-2 影响数:
68 好评:
0 差评:
0分类Oracle|专题Oracle基础关键字:
1、经典的查询语句
2、经典的字定义函数
3、经典的与业务相关的存储过程
等等
抛砖引玉：
备注本人彩票迷
（有点缺陷）
createorreplacefunctionf_ssqrandom（priornumininteger,endnumininteger）
returnvarchar2
is
v_prior_randinteger:
=0;
v_end_randinteger;
v_stringstring（32000）:
='红色球';
begin
forvin1..5loop
forv_countin1..3loop
v_prior_rand:
=floor（dbms_random.value（1,priornum））;
v_end_rand:
=v_prior_rand;
<>
v_prior_rand:
=floor（dbms_random.value（1,priornum））;
ifv_prior_rand!
=v_end_randthen
v_string:
=v_string||','||v_prior_rand||','||v_end_rand;
else
gotorandom;
endif;
dbms_output.put_line（v_string）;
endloop;
v_string:
=v_string||'蓝色球,'||floor（dbms_random.value（1,endnum））||'红色球';
endloop;
returnv_string;
endf_ssqrandom;
查询新建用户
selectusername
fromdba_users
whereusernamenotin
（'TEXT','RMAN_USER','TEST','SCOTT','TSMSYS','MDDATA','DIP',
'DBSNMP','SYSMAN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS',
'CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','SI_INFORMTN_SCHEMA',
'OLAPSYS','MGMT_VIEW','SYS','SYSTEM','OUTLN'）;
查询那些用户，操纵了那些表造成了锁机
SELECTs.username,
decode（l.type,'TM','TABLELOCK',
'TX','ROWLOCK',
NULL）LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROMv$sessions,v$lockl,all_objectso
WHEREl.sid=s.sid
ANDl.id1=o.object_id（+）
ANDs.usernameisNOTNull
其中TM为表锁定TX为行锁定
看锁阻塞的方法是
SELECT（selectusernameFROMv$sessionWHEREsid=a.sid）blocker,
a.sid,
'isblocking',
（selectusernameFROMv$sessionWHEREsid=b.sid）blockee,
b.sid
FROMv$locka,v$lockb
WHEREa.block=1
ANDb.request>0
ANDa.id1=b.id1
ANDa.id2=b.id2--各进制之间转换
CREATEORREPLACETYPEtype_str_aggASOBJECT
（
totalVARCHAR2（4000）,
STATICFUNCTIONodciaggregateinitialize（sctxINOUTtype_str_agg）
RETURNNUMBER,
MEMBERFUNCTIONodciaggregateiterate
（
SELFINOUTtype_str_agg,
VALUEINVARCHAR2
）RETURNNUMBER,
MEMBERFUNCTIONodciaggregateterminate
（
SELFINtype_str_agg,
returnvalueOUTVARCHAR2,
flagsINNUMBER
）RETURNNUMBER,
MEMBERFUNCTIONodciaggregatemerge
（
SELFINOUTtype_str_agg,
ctx2INtype_str_agg
）RETURNNUMBER
）
/
CREATEORREPLACETYPEBODYtype_str_aggIS
STATICFUNCTIONodciaggregateinitialize（sctxINOUTtype_str_agg）
RETURNNUMBERIS
BEGIN
sctx:
=type_str_agg（NULL）;
RETURNodciconst.success;
END;
MEMBERFUNCTIONodciaggregateiterate
（
SELFINOUTtype_str_agg,
VALUEINVARCHAR2
）RETURNNUMBERIS
BEGIN
SELF.total:
=SELF.total||VALUE;
RETURNodciconst.success;
END;
MEMBERFUNCTIONodciaggregateterminate
（
SELFINtype_str_agg,
returnvalueOUTVARCHAR2,
flagsINNUMBER
）RETURNNUMBERIS
BEGIN
returnvalue:
=SELF.total;
RETURNodciconst.success;
END;
MEMBERFUNCTIONodciaggregatemerge
（
SELFINOUTtype_str_agg,
ctx2INtype_str_agg
）RETURNNUMBERIS
BEGIN
SELF.total:
=SELF.total||ctx2.total;
RETURNodciconst.success;
END;
END;
/
CREATEORREPLACEFUNCTIONf_stragg（p_inputVARCHAR2）RETURNVARCHAR2
PARALLEL_ENABLE
AGGREGATEUSINGtype_str_agg;
/
CREATEORREPLACEPACKAGEpkg_number_transIS
FUNCTIONf_bin_to_oct（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_bin_to_dec（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_bin_to_hex（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_oct_to_bin（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_oct_to_dec（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_oct_to_hex（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_hex_to_bin（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_hex_to_oct（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_hex_to_dec（p_strINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_dec_to_bin（p_intINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_dec_to_oct（p_intINVARCHAR2）RETURNVARCHAR2;
FUNCTIONf_dec_to_hex（p_intINVARCHAR2）RETURNVARCHAR2;
ENDpkg_number_trans;
/
CREATEORREPLACEPACKAGEBODYpkg_number_transIS
FUNCTIONf_bin_to_oct（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_oct
--对象描述:
二进制转换八进制
--输入参数:
p_str二进制字符串
--返回结果:
八进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_oct（'11110001010'）FROMdual;
--备注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
v_binVARCHAR2（4000）;
BEGIN
v_bin:
=substr（'00'||p_str,-3*ceil（length（p_str）/3））;
SELECTf_stragg（data1）INTOv_return
FROM（SELECT（CASEupper（substr（v_bin,（rownum-1）*3+1,3））
WHEN'000'THEN'0'
WHEN'001'THEN'1'
WHEN'010'THEN'2'
WHEN'011'THEN'3'
WHEN'100'THEN'4'
WHEN'101'THEN'5'
WHEN'110'THEN'6'
WHEN'111'THEN'7'
END）data1
FROMdual
CONNECTBYrownum<=length（v_bin）/3）;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_oct;
FUNCTIONf_bin_to_dec（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_dec
--对象描述:
二进制转换十进制
--输入参数:
p_str二进制字符串
--返回结果:
十进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_dec（'11110001010'）FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
BEGIN
SELECTSUM（data1）INTOv_return
FROM（SELECTsubstr（p_str,rownum,1）*power（2,length（p_str）-rownum）data1
FROMdual
CONNECTBYrownum<=length（p_str））;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_dec;
FUNCTIONf_bin_to_hex（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_hex
--对象描述:
二进制转换十六进制
--输入参数:
p_str二进制字符串
--返回结果:
十六进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_oct（'11110001010'）FROMdual;
--备注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
v_binVARCHAR2（4000）;
BEGIN
v_bin:
=substr（'000'||p_str,-4*ceil（length（p_str）/4））;
SELECTf_stragg（data1）INTOv_return
FROM（SELECT（CASEupper（substr（v_bin,（rownum-1）*4+1,4））
WHEN'0000'THEN'0'
WHEN'0001'THEN'1'
WHEN'0010'THEN'2'
WHEN'0011'THEN'3'
WHEN'0100'THEN'4'
WHEN'0101'THEN'5'
WHEN'0110'THEN'6'
WHEN'0111'THEN'7'
WHEN'1000'THEN'8'
WHEN'1001'THEN'9'
WHEN'1010'THEN'A'
WHEN'1011'THEN'B'
WHEN'1100'THEN'C'
WHEN'1101'THEN'D'
WHEN'1110'THEN'E'
WHEN'1111'THEN'F'
END）data1
FROMdual
CONNECTBYrownum<=length（v_bin）/4）;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_hex;
FUNCTIONf_oct_to_bin（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_bin
--对象描述:
八进制转换二进制
--输入参数:
p_str八进制字符串
--返回结果:
二进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_bin（'3612'）FROMdual;
--备注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
BEGIN
SELECTto_char（to_number（f_stragg（data1）））INTOv_return
FROM（SELECT（CASEupper（substr（p_str,rownum,1））
WHEN'0'THEN'000'
WHEN'1'THEN'001'
WHEN'2'THEN'010'
WHEN'3'THEN'011'
WHEN'4'THEN'100'
WHEN'5'THEN'101'
WHEN'6'THEN'110'
WHEN'7'THEN'111'
END）data1
FROMdual
CONNECTBYrownum<=length（p_str））;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_oct_to_bin;
FUNCTIONf_oct_to_dec（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_dec
--对象描述:
八进制转换十进制
--输入参数:
p_str八进制字符串
--返回结果:
十进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_dec（'3612'）FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
BEGIN
SELECTSUM（data1）INTOv_return
FROM（SELECTsubstr（p_str,rownum,1）*power（8,length（p_str）-rownum）data1
FROMdual
CONNECTBYrownum<=length（p_str））;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_oct_to_dec;
FUNCTIONf_oct_to_hex（p_strINVARCHAR2）RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_bin
--对象描述:
八进制转换十六进制
--输入参数:
p_str八进制字符串
--返回结果:
十六进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_hex（'3612'）FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2（4000）;
v_binVARCHAR2（4000）;
BEGIN
SELECTp
升级会员 