orcale:orcale数据库来源: 发布时间:星期三, 2008年12月17日 浏览:173次 评论:0
PL/SQL单行
 和组详解 是 种有零个或多个参数并且有个返回值   在SQL中Oracle内建了系列 这些都可被称为SQL或PL/SQL语句主要分为两大类: 单行 组 本文将讨论如何利用单行 以及使用规则 SQL中 单行 SQL和PL/SQL中自带很多类型 有 、数字、日期、转换、和混合型等多种用于处理单行数据因此这些都可被统称为单行这些均可用于SELECT,WHERE、ORDER BY等子句中例如下面例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行 也可以在其他语句中使用如updateSET子句INSERTVALUES子句DELETWHERE子句,认证考试特别注意在SELECT语句中使用这些所以我们注意力也集中在SELECT语句中 NULL和单行 在如何理解NULL上开始是很困难 就算是个很有经验人依然对此感到困惑NULL值表示个未知数据或者个空值算术操作符任何个操作数为NULL值结果均为提个NULL值,这个规则也适合很多只有CONCAT,DECODE,DUMP,NVL,REPLACE在 了NULL参数时能够返回非NULL值在这些中NVL时最重要 他能直接处理NULL值NVL有两个参数:NVL(x1,x2),x1和x2都式表达式当x1为null时返回X2,否则返回x1 下面我们看看emp数据表它包含了薪水、奖金两项 需要计算总补偿column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单 将薪水和奖金加起来就可以了如果某行是null值那么结果就将是null比如下面例子:update emp  salary=(salary+bonus)*1.1 这个语句中 雇员工资和奖金都将更新为个新值但是如果没有奖金即 salary + null,那么就会得出 结论这个时候就要使用nvl来排除null值影响所以正确 语句是:update emp salary=(salary+nvl(bonus,0)*1.1单行 串 单行 串用于操作串数据他们大多数有个或多个参数其中绝大多数返回串 ASCII  c1是 串返回c1第个字母ASCII码他逆是CHRSELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122 CHR(＜i＞)[NCHAR_CS] i是 个数字返回十进制表示select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B CONCAT(,) c1,c2均为 串将c2连接到c1后面如果c1为null,将返回c2.如果c2为null,则返回c1如果c1、c2都为null则返回null他和操作符||返回结果相同select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda INITCAP c1为 串将每个单词第个字母大写其它字母小写返回单词由空格控制标点符号限制select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici INSTR(,[,＜i＞[,]]) c1,c2均为 串i,j为整数返回c2在c1中第j次出现位置搜索从c1第i个开始当没有发现需要时返回0,如果i为负数那么搜索将从右到左进行但是位置计算还是从左到右i和j缺省值为1.select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2 INSTRB(,[,i[,j]) 和INSTR() 样只是他返回是字节对于单字节INSTRB等于INSTR LENGTH c1为 串返回c1长度如果c1为null那么将返回null值select LENGTH('Ipso Facto') ergo from dualergo10 LENGTHb 和LENGTH 样返回字节 lower 返回c 小写经常出现在where子串中select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'COLORNAMEW  erwhite LPAD(,＜i＞[,]) c1,c2均为 串i为整数在c1左侧用c2串补足致长度i,可多次重复如果i小于c1长度那么只返回i那么长c1其他将被截去c2缺省值为单空格参见RPADselect LPAD(answer,7,'') padded,answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybe LTRIM(,) 把c1中最左边 去掉使其第个不在c2中如果没有c2那么c1就不会改变select LTRIM('Mississippi','Mis') from dualLTRppi RPAD(,＜i＞[,]) 在c1 右侧用c2串补足致长度i,可多次重复如果i小于c1长度那么只返回i那么长c1其他将被截去c2缺省值为单空格,其他和LPAD相似RTRIM(,) 把c1中最右边 去掉使其第后个不在c2中如果没有c2那么c1就不会改变 REPLACE(,[,]) c1,c2,c3都是 串用c3代替出现在c1中c2后返回select REPLACE('uptown','up','down') from dualREPLACEdowntown STBSTR(,＜i＞[,]) c1为 串i,j为整数从c1第i位开始返回长度为j子串如果j为空则直到串尾部select SUBSTR('Message',1,4) from dualSUBSMess SUBSTRB(,＜i＞[,]) 和SUBSTR大致相同 只是I,J是以字节计算 SOUNDEX 返回和c1发音相似 词select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dualDawes Daws DawsonD200 D200 D250 TRANSLATE(,,) 将c1中和c2相同 以c3代替select TRANSLATE('fumble','uf','ar') test from dualTEXTramble TRIM([  ] from c3) 将c3串中 第个最后个或者都删除select TRIM(' space padded ') trim from dual TRIMspace padded UPPER 返回c1 大写常出现where子串中select name from dual where UPPER(name) LIKE 'KI%'NAMEKING 单行数字 单行数字 操作数字数据执行数学和算术运算所有都有数字参数并返回数字值所有 3角操作数和值都是弧度而不是角度oracle没有提供内建弧度和角度转换 ABS 返回n 绝对值 ACOS 反余玄 返回-1到1的间数n表示弧度select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0 ASIN 反正玄 返回-1到1n表示弧度ATAN 反正切 返回n反正切值n表示弧度 CEIL 返回大于或等于n 最小整数 COS 返回n 余玄值n为弧度 COSH 返回n 双曲余玄值n 为数字select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847 EXP 返回e n次幂e=2.71828183. FLOOR 返回小于等于N 最大整数 LN 返回N 自然对数N必须大于0 LOG(,) 返回以n1为底n2 对数 MOD 返回n1除以n2 余数 POWER(,) 返回n1 n2次方 ROUND(,) 返回舍入小数点右边n2位 n1值n2缺省值为0这回将小数点最接近整数如果n2为负数就舍入到小数点左边相应位上n2必须是整数select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54 SIGN 如果n为负数 返回-1,如果n为正数返回1如果n=0返回0. SIN() 返回n 正玄值,n为弧度SINH 返回n 双曲正玄值,n为弧度 SQRT 返回n 平方根,n为弧度 TAN() 返回n 正切值,n为弧度 TANH 返回n 双曲正切值,n为弧度 TRUNC(,) 返回截尾到n2位小数 n1值n2缺省设置为0当n2为缺省设置时会将n1截尾为整数如果n2为负值就截尾在小数点左边相应位上 单行日期 单行日期 操作DATA数据类型绝大多数都有DATA数据类型参数绝大多数返回也是DATA数据类型值 ADD_MONTHS(,＜i＞) 返回日期d加上i个月后 结果i可以使任意整数如果i是个小数那么数据库将隐式他转换成整数将会截去小数点后面部分 LAST_DAY 返回包含日期d月份最后天 MONTHS_BETWEEN(,) 返回d1和d2的间月 数目,如果d1和d2日日期都相同或者都使该月最后天那么将返回个整数否则会返回结果将包含个分数 NEW_TIME(,,) d1是 个日期数据类型当时区tz1中日期和时间是d时返回时区tz2中日期和时间tz1和tz2时串NEXT_DAY(,) 返回日期d后由dow给出 条件第天dow使用当前会话中给出语言指定了周中某天返回时间分量和d时间分量相同select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004 ROUND([,]) 将日期d按照fmt指定 格式舍入fmt为串 SYADATE 没有参数返回当前日期和时间 TRUNC([,]) 返回由fmt指定 单位日期d.单行转换 单行转换 用于操作多数据类型在数据类型的间进行转换 CHARTORWID c 使 个串将c转换为RWID数据类型SELECT test_id from test_  where rowid=CHARTORWID('AAAA0SAACAAAALiAAA') CONVERT(,[,]) c尾 串d、s是两个集将串c由s集转换为d集s缺省设置为数据库集 HEXTORAW x为16进制 串将16进制x转换为RAW数据类型 RAWTOHEX x是RAW数据类型 串将RAW数据类转换为16进制数据类型 ROWIDTOCHAR 将ROWID数据类型转换为CHAR数据类型TO_CHAR([[,) x是 个data或number数据类型将x转换成fmt指定格式char数据类型如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回月份和日份所使用语言如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位分隔符以及货币符号NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="  " TO_DATE([,[,) c表示 串fmt表示种特殊格式串返回按照fmt格式显示c,nlsparm表示使用语言将串c转换成date数据类型 TO_MULTI_BYTE c表示 个串将c担子截转换成多字节 TO_NUMBER([,[,) c表示 串fmt表示个特殊格式串返回值按照fmt指定格式显示nlsparm表示语言将返回c代表数字 TO_SINGLE_BYTE 将 串c中得多字节转化成等价单字节该仅当数据库集同时包含单字节和多字节时才使用 其它单行 BFILENAME( ,) dir是 个directory类型对象file为文件名返回个空BFILE位置值指示符用于 化BFILE变量或者是BFILE列 DECODE(,,[,,, ) x是 个表达式m1是个匹配表达式x和m1比较如果m1等于x那么返回r1,否则,x和m2比较依次类推m3,m4,m5....直到有返回结果 DUMP(,[,[,[,]]]) x是 个表达式或fmt表示8进制、10进制、16进制、或则单返回包含了有关x内部表示信息VARCHAR2类型值如果指定了n1,n2那么从n1开始长度为n2字节将被返回EMPTY_BLOB 该 没有参数返回 个空BLOB位置指示符用于化个BLOB变量或BLOB列 EMPTY_CLOB 该 没有参数返回 个空CLOB位置指示符用于化个CLOB变量或CLOB列 GREATEST exp_list是 列表达式返回其中最大表达式每个表达式都被隐含转换第个表达式数据类型如果第个表达式是串数据类型中任何个那么返回结果是varchar2数据类型同时使用比较是非填充空格类型比较 LEAST exp_list是 列表达式返回其中最小表达式每个表达式都被隐含转换第个表达式数据类型如果第个表达式是串数据类型中任何个将返回结果是varchar2数据类型同时使用比较是非填充空格类型比较 UID 该 没有参数返回唯标示当前数据库用户整数 USER 返回当前用户 用户名 USERENV 基于opt返回包含当前会话信息 opt可选值为: ISDBA 会话中SYSDBA脚色响应 返回TRUE SESSIONID 返回审计会话标示符 ENTRYID 返回可用 审计项标示符 INSTANCE 在会话连接后 返回例子标示符该值只用于运行Parallel 服务器并且有 多个例子情况下使用 LANGUAGE 返回语言、地域、数据库设置 集 LANG 返回语言名称 ISO缩写 TERMINAL 为当前会话使用 终端或计算机返回操作系统标示符 VSIZE x是 个表达式返回x内部表示字节数SQL中 组 组 也叫集合返回基于多个行单结果行准确数量无法确定除非查询被执行并且所有结果都被包含在内和单行区别是在解析时所有行都是已知由于这种差别使组和单行有在要求和行为上有微小差异. 组(多行) 和单行 相比oracle提供了丰富基于组多行这些可以在select或selecthaving子句中使用当用于select子串时常常都和GROUP BY起使用 AVG([{DISYINCT|ALL}]) 返回数值 平均值缺省设置为ALL.SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413 COUNT({*|DISTINCT|ALL} ) 返回查询中行 数目缺省设置是ALL,*表示返回所有行 MAX([{DISTINCT|ALL}]) 返回选择列表项目 最大值如果x是串数据类型他返回个VARCHAR2数据类型如果X是个DATA数据类型返回个日期如果X是numeric数据类型返回个数字注意distinct和all不起作用应为最大值和这两种设置是相同 MIN([{DISTINCT|ALL}]) 返回选择列表项目 最小值 STDDEV([{DISTINCT|ALL}]) 返回选者 列表项目标准差所谓标准差是方差平方根 SUM([{DISTINCT|ALL}]) 返回选择列表项目 数值总和 VARIANCE([{DISTINCT|ALL}]) 返回选择列表项目 统计方差 用GROUP BY给数据分组 正如题目暗示 那样组就是操作那些已经分好组数据我们告诉数据库用GROUP BY怎样给数据分组或者分类当我们在SELECT语句SELECT子句中使用组时我们必须把为分组或非常数列放置在GROUP BY子句中如果没有用group by进行专门处理那么缺省分类是将整个结果设为类select stat,counter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT-- ---------AK 360AL 1212AR 1309AZ 768CA 3982 在这个例子中 我们用state字段分类；如果我们要将结果按照zip_codes排序,可以用ORDER BY语句ORDER BY子句可以使用列或组select stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*)-- --------NY 4312PA 4297TX 4123CA 3982 用HAVING子句限制分组数据 现在你已经知道了在查询 SELECT语句和ORDER BY子句中使用主组只能用于两个子串中组不能用于WHERE子串中例如下面查询是: SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000 GROUP BY sales_clerk 这个语句中数据库不知道SUM 是什么当我们需要指示数据库对行分组然后限制分组后行输出时正确思路方法是使用HAVING语句:SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' GROUP BY sales_clerkHAVING SUM(sale_amount)>10000; 嵌套 可以嵌套个输出可以是另个输入操作数有个可继承执行过程但优先权只是基于位置遵循由内到外由左到右原则嵌套技术般用于象DECODE这样能被用于逻辑判断语句IF....THEN...ELSE 嵌套 可以包括在组中嵌套单行或者组嵌套入单行或组中比如下面例子:SELECT deptno, GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr) cnt,COUNT(DISTINCT job) jobs,COUNT(DISTINCT mgr) mgrsFROM empGROUP BY deptno;DEPTNO CNT JOBS MGRS------ --- ---- ----10 4 4 220 4 3 430 3 3 2 Oracle SQL FAQ 荣耀 2002 整理这篇Oracle基本语法FAQ最初是为了回答 位客户朋友提问(客观情况所限我无法在线回答问题)这些解答并非唯答案只是答案的更完备答案请查阅Oracle正式文档 目录 Q1.怎样创建表？ Q2.怎样删除表？ Q3.怎样创建视图？ Q4.怎样删除视图？ Q5.怎样给表添加字段？ Q6.怎样删除表中某个字段？ Q7.怎样给某个字段添加约束？ Q8.怎样去除某个字段上 约束？ Q9.怎样给表加上主键？ Q10.怎样删除表 主键？ Q11.怎样给表添加 个外键？ Q12.怎样删除表 个外键？ Q13.怎样给字段加上CHECK？ Q14.怎样去掉字段上 CHECK？ Q15.怎样给字段设置默认值？ Q16.怎样移去字段 默认值？ Q17.怎样创建索引？ Q18.怎样删除索引？ Q19.怎样创建用户？ Q20.怎样删除用户？ Q21.怎样将对象权限(object privileges)授予用户？ Q22.怎样从用户收回对象权限？ Q23.怎样将角色权限(role privileges)授予用户？ Q24.怎样从用户收回角色权限？ Q25.怎样将系统权限(system privileges)授予用户？ Q26.怎样从用户收回系统权限？ Q27.怎样创建序列？ Q28.怎样删除序列？ Q29.怎样获取序列值？ Q30.怎样创建角色？ Q31.怎样删除角色？ Q32.怎样将对象权限(object privileges)授予角色？ Q33.怎样从角色收回对象权限？ Q34.怎样将角色权限(role privileges)授予角色？ Q35.怎样从角色收回角色权限？ Q36.怎样将系统权限(system privileges)授予角色？ Q37.怎样从角色收回系统权限？ Q38.不等于条件有哪几种写法？(茴香豆问题:)) Q39.like子句 使用方法？ Q40.请举出 个where子查询简单例子？ Q41.Oracle常用 串处理有哪些？ Q42.Oracle支持哪些数字处理 ？ Q43.怎样取得数据库服务器当前日期、时间？ Q44.怎样将 串转换为日期、时间格式？ Q45.常用日期 有哪些？ Q46.能给出 个DECODE使用方法例子吗？ Q47.能给出 个group by、having和order by使用方法例子吗？ Q48.Oracle有哪些常用数据字典？ Q49.怎样将日期、时间插入date型字段中？ Q50.能介绍 下connect by使用方法吗？0
相关文章读者评论发表评论 |
|
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
