2-3-函数-使用单行函数省名师优质课赛课获奖课件市赛课一等奖课件.ppt

Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,3-,*,Copyright Oracle Corporation,2001.All rights reserved.,本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。谢谢您,单行函数,第1页,目标,完成本课后，您应该能够执行以下操作：,描述在,SQL,中可用函数变量类型,在,SELECT,语句中使用字符，数字和日期函数,描述转换函数使用,第2页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第3页,SQL,函数,Function,输入,参数,1,参数,2,参数,n,函数（,Function,）,执行作用：,执行数据计算,修改单个数据项,操纵输出进行行分组,格式化显示日期和数字,转换列数据类型,输出,结果值,第4页,SQL,函数两种类型,函数,单行函数,多行函数,单行函数只对单条统计有效，多行函数对多条统计有效。,第5页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第6页,单行函数,单行函数：,操纵数据项,接收多个参数（常数、变量、列名、表示式），并返回一个值,作用于每一个返回行,每行返回一个结果,能够修改数据类型,能够嵌套,接收多个参数，参数能够是一个列或者一个表示式,function_name,(,arg1,arg2,.,),第7页,单行函数,转换,字符,数字,日期,通用,单行函数,第8页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第9页,字符函数,字符函数,LOWER,：转换字符值为小写,UPPER,：转换字符值为大写,INITCAP,：转换每个单词首字母值为大写，全部其它值为小写,CONCAT,：连接第一个字符值到第二个字符值,SUBSTR,：从字符返值中回指定字符,LENGTH,：返回表示式中字符数,INSTR,：返回一个命名串数字位置,LPAD|RPAD,：填充字符值左、右调整到,n,字符位置总宽度,TRIM,：够从一个字符串修整头或尾字符,REPLACE,：从字符串查找一个文本表示式,大小写处理函数,字符处理函数,第10页,Function,Result,大小写处理函数,这些函数转换字符串大小写,LOWER(,SQL Course,),UPPER(,SQL Course,),INITCAP(,SQL Course,),sql course,SQL COURSE,Sql Course,第11页,使用大小写处理函数,显示雇员,Higgins,雇员号、姓名和部门号：,SELECT employee_id,last_name,department_id,FROM employees,WHERE last_name=higgins;,no rows selected,SELECT employee_id,last_name,department_id,FROM employees,WHERE LOWER(last_name)=higgins;,第12页,CONCAT(Hello,World),SUBSTR(HelloWorld,1,5),LENGTH(HelloWorld),INSTR(HelloWorld,W),LPAD(salary,10,*,),RPAD(salary,10,*),TRIM(H FROM HelloWorld),HelloWorld,Hello,10,6,*24000,24000*,elloWorld,Function,Result,字符处理函数,字符串处理函数：,第13页,SELECT employee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,a)Contains a?,FROM employees,WHERE SUBSTR(job_id,4)=REP;,使用字符处理函数,1,2,3,1,2,3,第14页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第15页,数字函数,ROUND:,四舍五入指定小数值,ROUND(45.926,2)45.93,TRUNC:,截断指定小数值,TRUNC(45.926,2)45.92,MOD:,返回除法余数,MOD(1600,300)100,第16页,SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1),FROM DUAL;,使用,ROUND,函数,DUAL,是一个虚拟表，你能够用它来查看函数和计算结果,1,2,3,3,1,2,第17页,SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2),FROM DUAL;,使用,TRUNC,函数,3,1,2,1,2,3,第18页,SELECT last_name,salary,MOD(salary,5000),FROM employees,WHERE job_id=SA_REP;,使用,MOD,函数,计算全部是销售代表雇员工资被,5000,除后余数,第19页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第20页,日期使用,Oracle,数据库用内部数字格式存放日期：世纪，年，月，日，小时，分钟和秒,默认日期显示格式是,DD-MON-RR.,仅指定年最终两位数字，允许你存放,21,世纪日期在,20,世纪中,用一样方式，允许你存放,20,世纪日期在,21,世纪中,SELECT last_name,hire_date,FROM employees,WHERE last_name like,G%,;,第21页,日期使用,SYSDATE,函数返回：,Date,Time,第22页,用日期计算,从日期加或者减一个数，结果是一个日期值,两个日期相减，得到两个日期之间天数,用小时数除以,24,，能够加小时到日期上,第23页,用日期做算术运算,SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS,FROM employees,WHERE department_id=90;,第24页,日期函数,Number of monthsbetween two dates,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,Add calendar months to date,Next day of the date specified,Last day of the month,Round date,Truncate date,第25页,MONTHS_BETWEEN(01-SEP-95,11-JAN-94),使用日期函数,ADD_MONTHS(11-JAN-94,6),NEXT_DAY(01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-95,第26页,ROUND(SYSDATE,MONTH)01-AUG-95,ROUND(SYSDATE,YEAR)01-JAN-96,TRUNC(SYSDATE,MONTH)01-JUL-95,TRUNC(SYSDATE,YEAR)01-JAN-95,使用日期函数,假定,SYSDATE=25-JUL-95,:,SELECT employee_id,hire_date,ROUND(hire_date,MONTH),TRUNC(hire_date,MONTH),FROM employees,WHERE hire_date LIKE%97;,第27页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第28页,转换函数,隐式数据类型转换,显式数据类型转换,数据类型转换,第29页,隐式数据类型转换,对于直接赋值，,Oracle,服务器能够自动地进行下面转换：,VARCHAR2 or CHAR,To,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,第30页,隐式数据类型转换,对于表示式赋值，,Oracle,服务器能自动地进行下面转换：,VARCHAR2 or CHAR,To,VARCHAR2 or CHAR,NUMBER,DATE,第31页,显式数据类型转换,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,第32页,显式数据类型转换,第33页,对日期使用,TO_CHAR,函数,格式模板,必须加单引号，而且区分大小写,能够包含任一有效日期格式元素,有一个,fm,元素用来删除填补空，或者前导零,用一个逗号与日期值分开,TO_CHAR(,date,format_model,),SELECT employee_id,TO_CHAR(hire_date,MM/YY)Month_Hired,FROM employees,WHERE last_name=Higgins;,第34页,YYYY,日期格式模板元素,YEAR,MM,MONTH,DY,DAY,Full year in numbers,Year spelled out,Two-digit value for month,Three-letter abbreviation of the day of the week,Full name of the day of the week,Full name of the month,MON,Three-letter abbreviation of the month,DD,第35页,例子：,select TO_CHAR(SYSDATE,HH24:MI:SS AM)from dual,select TO_CHAR(TO_DATE(21-1,月,-95),YYYY-MM-DD HH24:MI:SS AM),from dual,select TO_CHAR(SYSDATE,DD of MONTH)from dual,select TO_CHAR(TO_DATE(25-JUL-95),DD of MONTH)from dual,select TO_CHAR(TO_DATE(25-7,月,-95),DD of MONTH)from dual,select to_char(sysdate,ddspth)from dual,select TRUNC(TO_DATE(25-JUL-95),ddspth)from dual,日期格式模板元素,第36页,日期格式模板元素,日期时间部分，时间元素格式,.,加字符串，将它们括在双引号中,.,数字前缀拼出数字,HH24:MI:SS AM,15:45:32 PM,DD of MONTH,12 of OCTOBER,ddspth,fourteenth,例：,select to_char(sysdate,ddspth)from dual,第37页,日期格式模板元素,时间格式化,其它格式,指定后缀来影响数字显示,第38页,TO_CHAR,函数用于日期转换,SELECT last_name,TO_CHAR(hire_date,fmDD Month YYYY),AS HIREDATE,FROM employees;,第39页,对数字使用,TO_CHAR,函数,下面是一些你能够和,TO_CHAR,一起使用格式化元素，用于显示字符形式数字值：,TO_CHAR(,number,format_model,),9,0,$,L,.,Represents a number,Forces a zero to be displayed,Places a floating dollar sign,Uses the floating local currency symbol,Prints a decimal point,Prints a thousand indicator,第40页,SELECT TO_CHAR(salary,$99,999.00)SALARY,FROM employees,WHERE last_name=Ernst;,对数字使用,TO_CHAR,函数,第41页,使用,TO_NUMBER,和,TO_DATE,函数,转换字符串到数字，用,TO_NUMBER,函数格式化：,转换字符串到日期，用,TO_DATE,函数格式化,:,这些函数有一个,fx,修饰符，该修饰符指示对字符参数和一个,TO_DATE,函数模板数据格式准确匹配,TO_NUMBER(,char,format_model,),TO_DATE(,char,format_model,),第42页,RR,日期格式,If two digits of the current year are:,0,49,049,50,99,50,99,The return date is in the current century,The return date is in the century after the current one,The return date is in the century before the current one,The return date is in the current century,第43页,RR,日期格式例子,为了找出,1990,年以前受雇雇员，使用,RR,格式，不论该命令运行在,1999,年还是现在，都会得到一样结果：,SELECT last_name,TO_CHAR(hire_date,DD-Mon-YYYY),FROM employees,WHERE hire_date TO_DATE(01-Jan-90,DD-Mon-RR);,第44页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第45页,嵌套函数,单行函数能够被嵌套任意层次,嵌套函数计算是从最里层到最外层,F3,(F2,(F1(col,arg1),arg2),arg3),Step 1=Result 1,Step 2=Result 2,Step 3=Result 3,第46页,SELECT last_name,NVL(TO_CHAR(manager_id),No Manager),FROM employees,WHERE manager_id IS NULL;,嵌套函数,第47页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第48页,通用函数,这些函数可用于任意数据类型，而且适合用于空值,NVL(expr1,expr2),NVL2(expr1,expr2,expr3),NULLIF(expr1,expr2),COALESCE(expr1,expr2,.,expr,n,),第49页,NVL,函数,转换一个空值到一个实际值,可用数据类型能够是日期、字符和数字,数据类型必须匹配：,NVL(commission_pct,0),NVL(hire_date,01-JAN-97),NVL(job_id,No Job Yet),第50页,SELECT last_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0)AN_SAL,FROM employees;,使用,NVL,函数,1,2,1,2,第51页,SELECT last_name,salary,commission_pct,NVL2(commission_pct,SAL+COMM,SAL)income,FROM employees WHERE department_id IN(50,80);,使用,NVL2,函数,1,2,1,2,第52页,SELECT first_name,LENGTH(first_name)expr1,last_name,LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)result,FROM employees;,使用,NULLIF,函数,1,2,3,1,2,3,第53页,使用,COALESCE,函数,COALESCE,函数超出,NVL,函数优点是,COALESCE,函数能够接收多个交替值,假如第一个表示式非空，它返回该表示式；不然，它做一个保留表示式结合,第54页,SELECT last_name,COALESCE(commission_pct,salary,10)comm,FROM employees,ORDER BY commission_pct;,使用,COALESCE,函数,第55页,大纲,SQL,函数种类,单行函数,字符函数,数字函数,日期函数,转换函数,嵌套函数,通用函数,条件表示式,第56页,条件表示式,在,SQL,语句中提供,IF-THEN-ELSE,逻辑使用,两种使用方法：,CASE,表示式,DECODE,函数,第57页,CASE,表示式,使得IF-THEN-ELSE 条件判断轻易实现,CASE,expr,WHEN,comparison_expr1,THEN,return_expr1,WHEN,comparison_expr2,THEN,return_expr2,WHEN,comparison_exprn,THEN,return_exprn,ELSE,else_expr,END,第58页,SELECT last_name,job_id,salary,CASE job_id WHEN IT_PROG THEN 1.10*salary,WHEN ST_CLERK THEN 1.15*salary,WHEN SA_REP THEN 1.20*salary,ELSE salary END REVISED_SALARY,FROM employees;,使用,CASE,表示式,使得,IF-THEN-ELSE,条件判断轻易实现：,第59页,DECODE,函数,使得,CASE,或者,IF-THEN-ELSE,条件判断轻易实现：,DECODE(,col|expression,search1,result1,search2,result2,.,default,),第60页,使用,DECODE,函数,SELECT last_name,job_id,salary,DECODE(job_id,IT_PROG,1.10*salary,ST_CLERK,1.15*salary,SA_REP,1.20*salary,salary),REVISED_SALARY,FROM employees;,第61页,使用,DECODE,函数,SELECT last_name,salary,DECODE(TRUNC(salary/,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATE,FROM employees,WHERE department_id=80;,对每一个在部门,80,雇员显示可用税率,第62页,小结,在本课中,您应该已经学会怎样：,用函数执行对数据计算,用函数修饰不一样数据项,用函数操纵行组输出,用函数改变数据格式,用函数转换列数据类型,使用,NVL,函数,使用,IF-THEN-ELSE,逻辑,第63页,