搜索
1、decode函数：SELECTcheckup_type,DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)FROMcheckup;DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.其具体的语法格式如下：DECODE(input_value,value,result[,value,result…][,default_result]);其中：input_value试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果value是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应result是一组成序偶的结果值default_result未能与任何一序偶匹配成功时，函数返回的默认值下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。2、nvl函数的用法：如果你某个字段为空，但是你想让这个字段显示0nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如：1，2，3……NULL指的是空值，或者非法值。NVL(expr1,expr2)-expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致NVL2(expr1,expr2,expr3)-expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型NULLIF(expr1,expr2)-相等返回NULL，不等返回expr13、oracle的查询必须是select...from...成对出现：查询单行的时候可以用dual代替，dual表在系统中只有一行；例如：获取系统时间；selectsysdatefromdual4、oracle查询结果多行用逗号拼接：SELECTWM_CONCAT(GOODSTYPENAME)FROMTB_SYS_PRODUCT_FORBIDGOODSTYPEfibLEFTJOINTB_SYS_GOODSTYPEtyONfib.goodstypeid=ty.goodstypeidWHEREfib.productid=t.productid5、oracle递归获取所有子或者父节点：pid为子级的父级id的字段名称从Root往树末梢递归：获取子级select*fromtempstartwithid=3/*父级的id*/connectbypriorid=pid/*pid子级的父级id的字段名称*/从末梢往树ROOT递归：获取父级select*fromtempstartwithid=3connectbyid=priorpid/*pid子级的父级id的字段名称*/如果需要显示层次结构selectsys_connect_by_path(id,'/'),pidfromtempstartwithid=1connectbypriorid=pidRg:SELECT*fromtb_base_netpointtSTARTWITHt.netpointid=74852CONNECTBYPRIORt.netpointid=t.parentnetpoint6、oracle使用正则表达式：替换掉大写字母：SELECTREGEXP_REPLACE(printNo,'^[A-Z]*','')ASISSUENOFROMTB_BASE_PRINTORDERLISTOracle10g支持正则表达式的四个新函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。特殊字符：'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。'$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'n'或'r'。'.'匹配除换行符n之外的任何单字符。'?'匹配前面的子表达式零次或一次。'*'匹配前面的子表达式零次或多次。'+'匹配前面的子表达式一次或多次。'()'标记一个子表达式的开始和结束位置。'[]'标记一个中括号表达式。'{m,n}'一个精确地出现次数范围，m=出现次数=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。'|'指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。num匹配num，其中num是一个正整数。对所获取的匹配的引用。正则表达式的一个很有用的特点是可以保存子表达式以后使用，被称为Backreferencing.允许复杂的替换能力如调整一个模式到新的位置或者指示被代替的字符或者单词的位置.被匹配的子表达式存储在临时缓冲区中，缓冲区从左到右编号,通过数字符号访问。下面的例子列出了把名字aabbcc变成cc,bb,aa.SelectREGEXP_REPLACE('aabbcc','(.*)(.*)(.*)','3,2,1')FROMdual；REGEXP_REPLACE('ELLENHILDISMITcc,bb,aa''转义符。字符簇：[[:alpha:]]任何字母。[[:digit:]]任何数字。[[:alnum:]]任何字母和数字。[[:space:]]任何白字符。[[:upper:]]任何大写字母。[[:lower:]]任何小写字母。[[:punct:]]任何标点符号。[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。各种操作符的运算优先级转义符(),(?:),(?=),[]圆括号和方括号*,+,?,{n},{n,},{n,m}限定符^,$,anymetacharacter位置和顺序|“或”操作7、oracle替换字母：替换所有字母：REGEXP_REPLACE(issueno,'^[A-Z]*','')8、oracle新建存储过程：createorreplaceprocedureTest_Get_User(IuseridinNUMBER,ttoutvarchar)isbeginselectusernameintottfromtb_sys_userwhereuserid=Iuserid;--dbms_output.put_line('tt='||tt);--打印结果endTest_Get_User;9、PLSQL添加新链接：1、客户端目录/network/admin，在admin目录中建立tnsnames.ora文件，内容大致如下：demo1=#oracle服务名(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.100)(PORT=1521))#oracle服务器地址与端口)(CONNECT_DATA=(SERVICE_NAME=demo1)))2、客户端配置：配置和移植工具——NetManager——服务命名；10、substr的用法：TSG.SCHEDULEORDERNO要截取的字符串，从3开始截取,字符串的下标从1开始;SUBSTR(TSG.SCHEDULEORDERNO,3)11、遍历所有数据：forlrin(SELECT*fromTB_FIN_CREDITORDERWHERE1=1ANDto_date(lr.startdate)=to_date(SYSDATE)ANDstate=8)loopendloop;跳出循环：exitwhenresult=500;12、C#调用执行sql插入数据返回序列号：stringstr_Sql=@begininsertintotesttab(name)values('test');selectTESTTABSEQ.Currvalinto:IDfromdual;end;;ORAC.OracleCommandcmd=newORAC.OracleCommand(str_Sql,this.oracleConnection1);ORAC.OracleParameterparm=newORAC.OracleParameter(ID,ORAC.OracleType.Number);parm.Direction=ParameterDirection.Output;cmd.Parameters.Add(parm);if(this.oracleConnection1.State==System.Data.ConnectionState.Closed){this.oracleConnection1.Open();}cmd.ExecuteNonQuery();this.textBox1.Text=cmd.Parameters[0].Value.ToString();13、查询表的所有分区：查询表的所有分区，表名必须大写select*fromuser_tab_partitionstwheret.table_name='ABC';14、查找字符串位置：INSTR（string,subString,position,ocurrence）查找字符串位置解释：string：源字符串subString：要查找的子字符串position：查找的开始位置ocurrence：源字符串中第几次出现的子字符串Forexample:INSTR('CORPORATEFLOOR','OR',3,2)中，源字符串为'CORPORATEFLOOR',目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为14'15、单据编号生成sql：单据编号加上前缀，加上每天日期，每天从1开始生成，SELECT'DD'||(NVL(SUBSTR(MAX(TSG.SCHEDULEORDERNO),3),TO_CHAR(SYSDATE,'yyyymmdd')||'00000')+1)FROMTB_SALE_SCHEDULEORDERTSGWHERETSG.SCHEDULEORDERNOLIKE'DD'||TO_CHAR(SYSDATE,'yyyymmdd')||'%'16、定义变量并输出：declarev_exitNUMBER;beginv_exit:=0;FORiIN0..100LOOPv_exit:=v_exit+i;ENDLOOP;dbms_output.put_line(v_exit);end;17、提取的列值为NULL：问题：提取的列值为NULL解决：有些列的值为NULL，用NVL（）转换；18、调试存储过程：存储过程右键--Test；按F9；19、运行存储过程：--运行存储过程,有返回值declarev_aNUMBER;v_bVARCHAR2(500);beginTEST_UPDATE_CUSTOMERCREDIT(v_a,v_b);dbms_output.put_line(v_a);dbms_output.put_line(v_b);end;20、整除：整除，不要四舍五入：TRUNC(5/3)----------121、新建作业：在DBMS_Jobs右键--新建：运行：作业右键--Run可以select*fromuser_jobs;查到job的id；然后execdbms_job.remove(21);commit;就删掉了select*fromuser_jobs;select*fromdba_users;oracle中创建作业/*创建作业任务*/DECLAREjobnoNUMBER;BEGIN--每15分钟执行一次DBMS_JOB.