单行函数 #Objectives After compleing this lesson,you should be able to do the following: -Describe variours types of function available in SQL -Use character,number,and date functions in SELECT statements -Describe the use of conversion functions #SQL Functions -Two types of SQL Functions ·Single-row functions (one to one) ·Multiple-row functions (multiple to one) -函数与过程的区别 函数是用来计算一个值或某些值,过程是主要用来完成某个动作. -Single-Row Functions ·Manipulate data items 操作数据项 ·Accept arguments and return one value 接受参数返回一个值 ·Act on each row returned 命令在每一行返回 ·Return one result per row 每行返回一个结果 ·May modify the data type 可以修改的数据类型 ·Can be nested 可以被嵌套 ·Accept arguments which can be a column or an expression 接受参数可以是一个栏位或一个表达式 -Single-Row Functions Types ·Character 字符型 -Case-Manipulation functions 字符大小写处理的函数 ㈠LOWER 全部小写 ㈡UPPER 全部大写 ㈢INITCAP 每个单词开始的第一个位置大写 function result lower('SQL Course') sql course upper('SQL Course') SQL COURSE initcap('SQL Course') Sql Course -Character-Manipulation functions 字符处理的函数 ㈠CONCAT 连接输入的两个参数 ㈡SUBSTR 基于第一个参数的值,索引从第二个参数开始 经过第三个参数长度的字符(索引基数为1) ㈢LENGTH 返回参数的长度 ㈣INSTR 从参数一中索引,参数二中所对应的值,返回其索引 没有返回0 ㈤LPAD|RPAD 参数一如果不够参数二所指定的大小那么 就左(右)填充参数三所指定的值添满为止 ㈥TRIM 从参数二中截取字符参数一返回,一次只能截取一个 字符且只能够从参数两端的第一个开始截取 ㈦REPLACE 把参数一中的参数二替换成参数三如果不含参数三 则把参数一中的参数二全部去掉 function result CONCAT('Hello','world') Helloworld SUBSTR('Helloworld',6,5) world LENGTH('Helloworld') 10 INSTR('Helloworld','w') 6 LPAD(SAL,10,'!') !!!!!29000 RLPAD(SAL,10,'!') 29000!!!!! TRIM('h'from'helloworld') elloworld REPLACE('helloworld','l','@') he@@owor@d ·Number 数字型 -ROUND:Rounds value to specified decimal ROUND(45.926,2)------>45.93 -TRUNC:Truncates value to specified decimal TRUNC(45.926,2)------>45.92 -MOD:Returns remainder of division MOD(1400,300) ------>200 注意:如果ROUND、TRUNC的第二个参数为负是意思就是从 小数点往左取如ROUND(45.926,-1)------>50 ·Date 日期型 -SYSDATE: 当前时间 sysdate---->12-2月 -07 -MONTHS_BETWEEN: 两个时间点间的相差的月 MONTHS_BETWEEN(add_months(sysdate,6),sysdate)----->6 -ADD_MONTHS: 在指定的时间点加入几个月 ADD_MONTHS(sysdate,6)-------->12-8月 -07 -NEXT_DAY: 返回指定时间点的下一个时间点 NEXT_DAY(sysdate,'星期一')------>19-2月 -07 -LAST_DAY: 返回指定时间点的月的最后一天 LAST_DAY(sysdate)------>28-2月 -07 -ROUND: 四舍五入 ROUND(last_day(sysdate))----->01-3月 -07 ROUND(sysdate,'month')---->01-2月 -07 ROUND(add_months(sysdate,5),'year')---->01-1月 -08 当ROUND(sysdate,'year')中月大于6时返回01-1月 -08 -TRUNC: 截取 TRUNC(last_day(sysdate))----->28-2月 -07 TRUNC(sysdate,'month')----->01-2月 -07 TRUNC(add_months(sysdate,5),'year')---->01-1月 -07 ·Conversion 转换型 -Implicit date type conversion 隐式数据类型的转换 From To VERCHAR2 or CHAR NUMBER VERCHAR2 or CHAR DATE NUMBER VERCHAR2 DATE VERCHAR2 -Explicit date type conversion TO_NUMBER TO_DATE NUMBER←=======→CHARACTER←=======→DATE TO_CHAR TO_CHAR TO_CHAR: ...转换成字符 TO_CHAR(sysdate,'YYYY-month-dd')----->2007-2月 -12 TO_CHAR(sysdate,'YYYY-month-day')----->2007-2月 -星期一 TO_CHAR(293.936,'$999.99')------>$293.94 TO_CHAR(293.936,'L999.99')------>RMB293.94 TO_NUMBER: ...转换成数字 TO_NUMBER('1200')----->1200 TO_DATE: ...转换成日期 TO_DATE('07-2月 -12')-----07-2月 -12 ·General 通用型 -NVL(expr1,expr2):如果expr1,为null则expr2替换expr1 NVL(ename,'Not Name') -->如果字符型栏位ename为null则显示字符Not Name NVL(sal,0) -->如果数字型栏位sal为null则显示数字0 NVL(hiredate,'03-12月-81') -->如果日期型栏位hiredate为null则显示日期03-12月-81 注意:在NVL(hiredate...中如果您输入的日期格式不于hiredate 栏位日期格式相匹配会报ORA-01861错例:SQL> select ename,nvl(ename,'Not Name') from emp 2 where empno>7899;
ENAME NVL(ENAME,---------- ---------- Not NameFORD FORDMILLER MILLERSQL> -NVL2(expr1,expr2,expr3):如果expr1,为null则expr3替换expr1 不为null则expr2替换expr1例:SQL> select ename,nvl2(ename,'Have Name','Not Name') from emp 2 where empno>7899;
ENAME NVL2(ENAM---------- --------- Not NameFORD Have NameMILLER Have Name
SQL> -NULLIF(expr1,expr2):如果expr1,expr2相等则显 示为null,否则显示expr1例:SQL> select ename,job,nullif(length(ename),length(job)) from emp 2 where empno<7600;
ENAME JOB NULLIF(LENGTH(ENAME),LENGTH(JOB))---------- --------- ---------------------------------SMITH CLERKALLEN SALESMAN 5WARD SALESMAN 4JONES MANAGER 5
SQL> -COALESCE(expr1,expr2......,exprn):从1开始直到n显示最早 一个不为null的值 注意:在COALESCE()函数里表达式的类型必需相同可以用to_char 等转换如果类型不同则报ORA-00932错例:SQL>select ename,sal,comm,coalesce(ename,to_char(comm),to_char(sal)) coalesce from emp 2 where empno>7888;
ENAME SAL COMM COALESCE---------- ---------- ---------- ---------------------------------------- 950 950FORD 3000 FORDMILLER 1300 MILLER
SQL>
-Conditional Expression 条件表达式 ·Provide the use of IF-THEN-ELSE logic within a SQL statement ·Use two methods: -CACE exptession 例:SQL> select empno,ename,job,sal, 2 case job when 'CLERK' then 1.1*sal 3 when 'SALESMAN' then 1.3*sal 4 when 'MANAGER' then 1.4*sal 5 else sal end "++SAL" 6 from emp;
EMPNO ENAME JOB SAL ++SAL---------- ---------- --------- ---------- ---------- 7369 SMITH CLERK 800 880 7499 ALLEN SALESMAN 1600 2080 7521 WARD SALESMAN 1250 1625 7566 JONES MANAGER 2975 4165 7654 MARTIN SALESMAN 1250 1625 7698 BLAKE MANAGER 2850 3990 7782 CLARK MANAGER 2450 3430 7788 SCOTT ANALYST 3000 3000 7839 KING PRESIDENT 5000 5000 7844 TURNER SALESMAN 1500 1950 7876 ADAMS CLERK 1100 1210
EMPNO ENAME JOB SAL ++SAL---------- ---------- --------- ---------- ---------- 7900 CLERK 950 1045 7902 FORD ANALYST 3000 3000 7934 MILLER CLERK 1300 1430
已选择14行。 -DECODE function例:SQL> run 1 select empno,ename,sal,job, 2 decode(job,'CLERK',1.1*sal, 3 'SALESMAN' ,1.15*sal, 4 'MANAGER' ,1.3*sal, 5 sal) 6 "SAL++" 7* from emp
EMPNO ENAME SAL JOB SAL++---------- ---------- ---------- --------- ---------- 7369 SMITH 800 CLERK 880 7499 ALLEN 1600 SALESMAN 1840 7521 WARD 1250 SALESMAN 1437.5 7566 JONES 2975 MANAGER 3867.5 7654 MARTIN 1250 SALESMAN 1437.5 7698 BLAKE 2850 MANAGER 3705 7782 CLARK 2450 MANAGER 3185 7788 SCOTT 3000 ANALYST 3000 7839 KING 5000 PRESIDENT 5000 7844 TURNER 1500 SALESMAN 1725 7876 ADAMS 1100 CLERK 1210
EMPNO ENAME SAL JOB SAL++---------- ---------- ---------- --------- ---------- 7900 950 CLERK 1045 7902 FORD 3000 ANALYST 3000 7934 MILLER 1300 CLERK 1430
已选择14行。
summaryIn this lesson ,you should have learned how to: ·Perform calculations on data using funtions ·Modify individual data items using funtions ·Manipulate output for groups of rows using functions ·Alter date formats for display using functions ·Convert column data types using functions ·Use NVL functions ·Use IF-THEN-ELSE logic
