Oracle SQL 函数


SQL 单行函数 1、 单行函数: 函数类别: 单行:返回单个结果:substr,length 多行:返回多个结果,any,all 单行的分类: 字符类,日期类,数字类,转换类,通用类 1.字符类 转换大小写: lower: 转换为小写 Select ENAME,LOWER(ENAME) From EMP upper:转换为大写 Select upper('abcd') From DUAL initcap :首字母大写 Select INITCAP(ENAME) From EMP 2.字符处理类 concat:连接两个不同的列,CONCAt只能接受两个参数 Select CONCAT(EMPNO,ENAME) From EMP Select CONCAT('A','B') From dual substr:截取子串 下标从1开始 Select SUBSTR(ENAME,2) From EMP--从第2个位置截到末尾 Select SUBSTR(ENAME,2,3) From EMP--从第2个位置截取3个 length:提取长度 Select ENAME,length(ename) From emp INSTR:相当于String类中的indexOf,求索引 Select ENAME,instr(ename,'A') From emp LPAD和RPAD的用法 表示补充的含义 Select LPAD(SAL,10,'*') From EMP Select RPAD(SAL,10,'*') From EMP2去2 将sal显示为10位,不足的位置补上字符* trim两边滤空 Select Trim(' A B C ') From Dual 使用LTrime和RTrim过滤一边的空格 Select LTrim(' A B C ') From DUAL REPLACE:替换 Select ename ,Replace(ename,'A','B') From EMP 3.数字转换类: ROUND:四舍五入 TRUNC:截取 MOD:取余 Select ROUND(16.336666663,3) From DUAL Select TRUNC(16.33666666,3) From DUAL Select Mod(8,3) From DUAL 4.日期类函数 sysdate:年月日时分秒 日期+-1,都代表一天的时间,比如: Select TRUNC(Sysdate-365) From DUAL Select Sysdate-1/24/60 From DUAL Oracle SQL 函数 2011年2月26日 22:03 分区 Oracle_SQL 的第 1 页 Select Sysdate-1/24/60 From DUAL 5.日期运算函数 MONTHS_BETWEEN:表示两个日期的月份之差 Select EMPNO,HIREDATE,MONTHS_BETWEEN(Sysdate,HIREDATE)/12 From EMP ADD_MONTHS:表示给指定的日期加一个月数 Select HIREDATE,ADD_MONTHS(HIREDATE,3) From EMP NEXT_DAY:表示以当前时间为基准,下一个"目标日"的日期 Select NEXT_DAY(Sysdate,'星期二') From DUAL LAST_DAY:计算当前日期的最后一天 Select HIREDATE,LAST_DAY(HIREDATE)-HIREDATE From EMP ROUND:对日期进行四舍五入 Select ROUND(Sysdate,'YEAR') From DUAL TRUNC:表示对日期进行截取 Select TRUNC(Sysdate) From DUAL 5.TO_CHAR函数将日期转换为字符类型 Select TO_CHAR(Sysdate,'YYYY-MM-DD DAY HH24:MI:SS AM') From DUAL 特殊的日期转换描述 Select TO_CHAR(Sysdate,'"今天是"YYYY-MM-DD DAY HH24:MI:SS AM') From DUAL 6.TO_CHAR:可以对数字类型进行类型转换,转换的同时,你可以指定转换后的格式,比如: 9 0 , L 本地货币 $ . G D 实例: Select TO_CHAR(SAL,'$999,999.00') From EMP 7.TO_NUMBER:作用是用来将一个指定的字符串类型的数字,转换为数字格式 注意:格式必须要匹配. Select TO_NUMBER('$1,600.01','$999,999.99') From DUAL 8.TO_DATE:将字符串类型的日期,转换为日期类型 Select TO_DATE('2008-01==01','YYYY-MM==DD') From DUAL 特殊的日期格式: Select TO_DATE('2008-01-01 星期二 10:36:31 上午','YYYY-MM-DD DAY HH:MI:SS PM') From DUAL 9,函数嵌套: Select LENGTH(LOWER(ENAME)) From EMP 10.关于通用函数: NVL:当修饰的字段的内容为null时,运算结果为指定的值 Select COMM,NVL(COMM,200) From EMP NVL2:如果第一个表达式的值不为null,显示表达式2的值,否则(为null)显示表达式3的值 Select SAL,COMM,NVL2(COMM,SAL+COMM,SAL) From EMP NULLIF:如果前后两个表达式的内容相等的,那就返回null,否则,返回第一个表达式的值 Select ENAME,JOB,NULLIF(LENGTH(ENAME),5) From EMP COALESCE函数:用来匹配多个字段的值,如果表达式1的值为null,显示表达式2的值,如果表达式2也为空,显示表达式3的值,依次类推 Select MGR,COMM, Coalesce(MGR,COMM,EMPNO,88) From EMP 11.条件表达式: 两中方式: 1.case:表达式语句 2.decode:函数,decode() A.case语句 条件判断 分区 Oracle_SQL 的第 2 页 条件判断 case expre when condition then value else value End Select job, Case JOB When 'CLERK' Then '店员' When 'SALESMAN' Then '销售' Else '临时工' End From emp B.decode函数 Select job, decode(JOB,'CLERK','店员', 'SALESMAN' ,'销售', '临时工') From emp 02:33:14 SQL> select ename,JOB,sal, 02:33:36 2 case job when 'CLERK' THEN sal*1.1 02:33:40 3 WHEN 'SALESMAN' THEN sal*2 02:33:47 4 when 'ANALYST' THEN SAL*1.5 02:33:57 5 else sal 02:34:01 6 end salary 02:34:12 7 from emp; ENAME JOB SAL SALARY ---------- --------- ---------- ---------- SMITH CLERK 800 880 ALLEN SALESMAN 1600 3200 WARD SALESMAN 1250 2500 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 2500 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 4500 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 3000 ADAMS CLERK 1100 1210 JAMES CLERK 950 1045 FORD ANALYST 3000 4500 MILLER CLERK 1300 1430 14 rows selected. 02:34:15 SQL> 02:35:55 SQL> select ename,JOB,sal, 02:36:15 2 decode(job,'CLERK',sal*1.1, 02:36:19 3 'SALESMAN',SAL*2, 02:36:31 4 'ANALYST',SAL*1.5, 02:36:42 5 SAL) salary 02:36:48 6 FROM EMP; ENAME JOB SAL SALARY ---------- --------- ---------- ---------- SMITH CLERK 800 880 ALLEN SALESMAN 1600 3200 WARD SALESMAN 1250 2500 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 2500 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 4500 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 3000 ADAMS CLERK 1100 1210 JAMES CLERK 950 1045 FORD ANALYST 3000 4500 MILLER CLERK 1300 1430 14 rows selected. CASE 语句 SELECT ENAME,SAL, 分区 Oracle_SQL 的第 3 页 SELECT ENAME,SAL, CASE WHEN SAL BETWEEN 1000 AND 2000 THEN 'LOW' WHEN SAL BETWEEN 2000 AND 3000 THEN 'MID' WHEN SAL BETWEEN 300 AND 5000 THEN 'HIGH' END AS SAL_INFO FROM EMP 01:11:29 SQL> / ENAME SAL SAL_ ---------- ---------- ---- SMITH 800 HIGH ALLEN 1600 LOW WARD 1250 LOW JONES 2975 MID MARTIN 1250 LOW BLAKE 2850 MID CLARK 2450 MID SCOTT 3000 MID KING 5000 HIGH TURNER 1500 LOW ADAMS 1100 LOW JAMES 950 HIGH FORD 3000 MID MILLER 1300 LOW 14 rows selected. 01:18:07 SQL> SELECT ENAME,SAL, 01:19:03 2 CASE WHEN SAL BETWEEN 1000 AND 2000 THEN 'LOW' WHEN SAL BETWEEN 2000 AND 3000 THEN 'MID' WHEN SAL BETWEEN 3000 AND 5000 THEN 'HIGH' ELSE 01:19:03 7 'NORMAL' END AS SAL_INFO 01:19:03 10 FROM EMP; ENAME SAL SAL_IN ---------- ---------- ------ SMITH 800 NORMAL ALLEN 1600 LOW WARD 1250 LOW JONES 2975 MID MARTIN 1250 LOW BLAKE 2850 MID CLARK 2450 MID SCOTT 3000 MID KING 5000 HIGH TURNER 1500 LOW ADAMS 1100 LOW JAMES 950 NORMAL FORD 3000 MID MILLER 1300 LOW 14 rows selected. DECODE 语句 01:12:23 SQL> SELECT ENAME,SAL, 01:12:32 2 DECODE (SAL,'800' ,'LOW', 01:13:09 3 '3000','MID', 01:13:22 4 '5000','HIGH') 01:13:47 5 AS SAL_INFO 01:13:56 6 FROM EMP; ENAME SAL SAL_ ---------- ---------- ---- SMITH 800 LOW ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 MID KING 5000 HIGH TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MID MILLER 1300 分区 Oracle_SQL 的第 4 页 14 rows selected. 01:13:59 SQL> SELECT ENAME,SAL, 01:15:22 2 DECODE (SAL,'800' ,'LOW', 01:15:26 3 '3000','MID', 01:15:29 4 '5000','HIGH', 01:15:36 5 'NORMAL') 01:15:53 6 AS SAL_INFO 01:15:58 7 FROM EMP; ENAME SAL SAL_IN ---------- ---------- ------ SMITH 800 LOW ALLEN 1600 NORMAL WARD 1250 NORMAL JONES 2975 NORMAL MARTIN 1250 NORMAL BLAKE 2850 NORMAL CLARK 2450 NORMAL SCOTT 3000 MID KING 5000 HIGH TURNER 1500 NORMAL ADAMS 1100 NORMAL JAMES 950 NORMAL FORD 3000 MID MILLER 1300 NORMAL SQL_分组函数 2、 1.分组函数: 概念:对一组数据返回一个值 函数,()里面往往是一个字段 select max(sal) from emp 作用:统计数据 取平均值:AVG Select AVG(SAL) From EMP 取最大值:max select max(sal) from emp 取最小值:min select min(sal) from emp 取记录数count SELECT COUNT(*) FROM EMP 也可以 select count(empno) from emp 注意: 要么count(*) 要么count(没有null值的列---主键) 求和:sum Select SUM(SAL*12) From EMP 2.分组函数和distinct关键词的搭配 Select COUNT(Distinct SAL) From EMP 3.组函数与空值 select count(comm) from emp 空值不参与运算,直接被过滤掉 4.分组数据 group by子句 SELECT COUNT(*) FROM EMP GROUP BY DEPTNO 5.使用group by规则 A.group by后面的字段不必显示在select列表中 B.反之则不行 也就是说:select后面的字段必须在group by子句中出现 例外的是: 在组函数(count,max,min等)中出现的字段除外 6.关于group by条件分组的问题 分区 Oracle_SQL 的第 5 页 A.group by可以和where来搭配 where只能在group by的前面 Select JOB From EMP Where SAL>2000 Group By JOB group by后面不能有where B.where子句中不能包括组函数 条件的表达只能使用having来表示 Select DEPTNO,Max(SAL),Min(SAL) From EMP Group By DEPTNO Having Max(SAL)>2000 3、 Like 、instr、substr 查找所有以姓名以S打头的用户信息: 23:47:54 SQL> select instr(ename,'S') ,ename from scott.emp 23:48:18 2 where instr(ename,'S')=1; INSTR(ENAME,'S') ENAME ---------------- ---------- 1 SMITH 1 SCOTT 23:50:45 SQL> select substr(ename,1,1) ,ename from scott.emp 23:50:46 2 where substr(ename,1,1)='S'; S ENAME ----------- S SMITH S SCOTT 23:19:15 SQL> select ename,sal from emp 00:04:41 2 where ename like 'S%'; ENAME SAL ---------- ---------- SCOTT 3000 SMITH 3000 4、 转换函数 To_char 、to_date 、to_number 3:41:22 SQL> select to_char(hiredate ,'yyyy-mm-dd') from scott.emp 23:41:35 2 where substr(to_char(hiredate ,'yyyy-mm-dd'),9,2)=17; SU -- 17 17 00:07:34 SQL> select to_char(hiredate ,'yyyy-mm-dd') from scott.emp 00:07:53 2 where substr(to_char(hiredate ,'yyyy-mm-dd'),9,2)=17; TO_CHAR(HI ---------- 1980-12-17 1981-11-17 23:58:27 SQL> select sysdate,to_char(sysdate,'yyyy-mm-dd day') from scott.emp; SYSDATE TO_CHAR(SYSDATE,'YYY ------------------- -------------------- 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 分区 Oracle_SQL 的第 6 页 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 2011-02-25 23:58:28 2011-02-25 friday 14 rows selected. 23:58:28 SQL> select sysdate,to_char(sysdate,'yyyy-mm-dd dy') from scott.emp; SYSDATE TO_CHAR(SYSDAT ------------------- -------------- 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 2011-02-25 23:58:41 2011-02-25 fri 14 rows selected. 23:58:41 SQL> 23:59:10 SQL> select sysdate,to_char(sysdate,'fmyyyy-mm-dd dy') from scott.emp; SYSDATE TO_CHAR(SYSDAT ------------------- -------------- 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 2011-02-25 23:59:11 2011-2-25 fri 14 rows selected. 00:01:34 SQL> select sysdate,to_char(sysdate,'fmyyyy-mm-dd dy') from scott.emp 00:02:00 2 where to_number(substr(to_char(sysdate,'yyyy-mm-dd'),1,4))=2011; SYSDATE TO_CHAR(SYSDAT ------------------- -------------- 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 分区 Oracle_SQL 的第 7 页 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 2011-02-26 00:02:06 2011-2-26 sat 14 rows selected. 00:03:37 SQL> select hiredate ,to_char(hiredate,'yyyy-mm-dd day') from scott.emp; HIREDATE TO_CHAR(HIREDATE,'YY ------------------- -------------------- 1980-12-17 00:00:00 1980-12-17 wednesday 1981-02-20 00:00:00 1981-02-20 friday 1981-02-22 00:00:00 1981-02-22 sunday 1981-04-02 00:00:00 1981-04-02 thursday 1981-09-28 00:00:00 1981-09-28 monday 1981-05-01 00:00:00 1981-05-01 friday 1981-06-09 00:00:00 1981-06-09 tuesday 1987-04-19 00:00:00 1987-04-19 sunday 1981-11-17 00:00:00 1981-11-17 tuesday 1981-09-08 00:00:00 1981-09-08 tuesday 1987-05-23 00:00:00 1987-05-23 saturday 1981-12-03 00:00:00 1981-12-03 thursday 1981-12-03 00:00:00 1981-12-03 thursday 1982-01-23 00:00:00 1982-01-23 saturday 14 rows selected. 隐式转换 00:11:32 SQL> select hiredate from emp 00:11:35 2 where hiredate >'1980-12-20'; HIREDATE ------------------- 1981-02-20 00:00:00 1981-02-22 00:00:00 1981-04-02 00:00:00 1981-09-28 00:00:00 1981-05-01 00:00:00 1981-06-09 00:00:00 1987-04-19 00:00:00 1981-11-17 00:00:00 1981-09-08 00:00:00 1987-05-23 00:00:00 1981-12-03 00:00:00 1981-12-03 00:00:00 1982-01-23 00:00:00 00:25:44 SQL> select hiredate from emp 00:25:47 2 where to_char(hiredate,'yyyy-mm-dd') >'1980-12-20'; HIREDATE ------------------- 1981-02-20 00:00:00 1981-02-22 00:00:00 1981-04-02 00:00:00 1981-09-28 00:00:00 1981-05-01 00:00:00 1981-06-09 00:00:00 1987-04-19 00:00:00 1981-11-17 00:00:00 1981-09-08 00:00:00 1987-05-23 00:00:00 1981-12-03 00:00:00 1981-12-03 00:00:00 1982-01-23 00:00:00 分区 Oracle_SQL 的第 8 页 1982-01-23 00:00:00 13 rows selected. 00:29:09 SQL> select hiredate from emp 00:29:27 2 where (sysdate-hiredate)/365 >10; HIREDATE ------------------- 1980-12-17 00:00:00 1981-02-20 00:00:00 1981-02-22 00:00:00 1981-04-02 00:00:00 1981-09-28 00:00:00 1981-05-01 00:00:00 1981-06-09 00:00:00 1987-04-19 00:00:00 1981-11-17 00:00:00 1981-09-08 00:00:00 1987-05-23 00:00:00 1981-12-03 00:00:00 1981-12-03 00:00:00 1982-01-23 00:00:00 14 rows selected. 00:29:42 SQL> select hiredate from emp 00:29:49 2 where (sysdate-hiredate)/365 >30; HIREDATE ------------------- 1980-12-17 00:00:00 1981-02-20 00:00:00 1981-02-22 00:00:00 00:29:53 SQL> 00:32:33 SQL> select ename,hiredate from emp 00:33:00 2 where hiredate >to_date('1985-01-01','yyyy-mm-dd'); ENAME HIREDATE ---------- ------------------- SCOTT 1987-04-19 00:00:00 ADAMS 1987-05-23 00:00:00 00:34:49 SQL> select ename,hiredate from emp 00:34:50 2 where hiredate>to_char('1985-01-01' ,'yyyy-mm-dd'); where hiredate>to_char('1985-01-01' ,'yyyy-mm-dd') * ERROR at line 2: ORA-01722: invalid number 00:35:24 SQL> 00:37:08 SQL> select ename,hiredate from emp 00:37:24 2 where to_char(sysdate,'yyyy-mm-dd')>to_char(hiredate ,'yyyy-mm-dd'); ENAME HIREDATE ---------- ------------------- SMITH 1980-12-17 00:00:00 ALLEN 1981-02-20 00:00:00 WARD 1981-02-22 00:00:00 JONES 1981-04-02 00:00:00 MARTIN 1981-09-28 00:00:00 BLAKE 1981-05-01 00:00:00 CLARK 1981-06-09 00:00:00 SCOTT 1987-04-19 00:00:00 KING 1981-11-17 00:00:00 TURNER 1981-09-08 00:00:00 ADAMS 1987-05-23 00:00:00 分区 Oracle_SQL 的第 9 页 ADAMS 1987-05-23 00:00:00 JAMES 1981-12-03 00:00:00 FORD 1981-12-03 00:00:00 MILLER 1982-01-23 00:00:00 14 rows selected. 00:37:41 SQL> select ename,hiredate from emp 00:41:38 SQL> select ename,hiredate from emp 00:41:40 2 where to_char(sysdate-hiredate) >100; ENAME HIREDATE ---------- ------------------- SMITH 1980-12-17 00:00:00 ALLEN 1981-02-20 00:00:00 WARD 1981-02-22 00:00:00 JONES 1981-04-02 00:00:00 MARTIN 1981-09-28 00:00:00 BLAKE 1981-05-01 00:00:00 CLARK 1981-06-09 00:00:00 SCOTT 1987-04-19 00:00:00 KING 1981-11-17 00:00:00 TURNER 1981-09-08 00:00:00 ADAMS 1987-05-23 00:00:00 JAMES 1981-12-03 00:00:00 FORD 1981-12-03 00:00:00 MILLER 1982-01-23 00:00:00 14 rows selected. 00:42:14 SQL> select ename,hiredate from emp 00:42:16 2 where to_number(sysdate-hiredate) >100; ENAME HIREDATE ---------- ------------------- SMITH 1980-12-17 00:00:00 ALLEN 1981-02-20 00:00:00 WARD 1981-02-22 00:00:00 JONES 1981-04-02 00:00:00 MARTIN 1981-09-28 00:00:00 BLAKE 1981-05-01 00:00:00 CLARK 1981-06-09 00:00:00 SCOTT 1987-04-19 00:00:00 KING 1981-11-17 00:00:00 TURNER 1981-09-08 00:00:00 ADAMS 1987-05-23 00:00:00 JAMES 1981-12-03 00:00:00 FORD 1981-12-03 00:00:00 MILLER 1982-01-23 00:00:00 14 rows selected. 00:42:22 SQL> 00:43:09 SQL> select ename,hiredate from emp 00:43:10 2 where sysdate-hiredate >100; ENAME HIREDATE ---------- ------------------- SMITH 1980-12-17 00:00:00 ALLEN 1981-02-20 00:00:00 WARD 1981-02-22 00:00:00 JONES 1981-04-02 00:00:00 MARTIN 1981-09-28 00:00:00 BLAKE 1981-05-01 00:00:00 CLARK 1981-06-09 00:00:00 SCOTT 1987-04-19 00:00:00 KING 1981-11-17 00:00:00 分区 Oracle_SQL 的第 10 页 KING 1981-11-17 00:00:00 TURNER 1981-09-08 00:00:00 ADAMS 1987-05-23 00:00:00 JAMES 1981-12-03 00:00:00 FORD 1981-12-03 00:00:00 MILLER 1982-01-23 00:00:00 14 rows selected. 4、 NVL 和 NVL2 23:54:33 SQL> select comm,nvl(to_char(comm),'No Comm') comm_nu from scott.emp; COMM COMM_NU ---------- ---------------------------------------- No Comm 300 300 500 500 No Comm 1400 1400 No Comm No Comm No Comm No Comm 0 0 No Comm No Comm No Comm No Comm 14 rows selected. 23:55:32 SQL> select comm,nvl2(to_char(comm),'No Comm','comm') comm_nu from scott.emp; COMM COMM_NU ---------- ------- comm 300 No Comm 500 No Comm comm 1400 No Comm comm comm comm comm 0 No Comm comm comm comm comm 14 rows selected. 23:56:30 SQL> select comm,nvl2(comm,comm,0) from scott.emp; COMM NVL2(COMM,COMM,0) ---------- ----------------- 0 300 300 500 500 0 1400 1400 0 0 0 0 0 0 0 0 0 分区 Oracle_SQL 的第 11 页 0 0 14 rows selected. 5、 日期函数 Months_between 03:50:46 SQL> select ename, trunc(months_between(sysdate,hiredate)/12) "Work Year",hiredate from emp; ENAME Work Year HIREDATE ---------- ---------- ------------------- SMITH 30 1980-12-17 00:00:00 ALLEN 30 1981-02-20 00:00:00 WARD 30 1981-02-22 00:00:00 JONES 29 1981-04-02 00:00:00 MARTIN 29 1981-09-28 00:00:00 BLAKE 29 1981-05-01 00:00:00 CLARK 29 1981-06-09 00:00:00 SCOTT 23 1987-04-19 00:00:00 KING 29 1981-11-17 00:00:00 TURNER 29 1981-09-08 00:00:00 ADAMS 23 1987-05-23 00:00:00 JAMES 29 1981-12-03 00:00:00 FORD 29 1981-12-03 00:00:00 MILLER 29 1982-01-23 00:00:00 14 rows selected. Add_months 03:50:57 SQL> select add_months(sysdate,12) from dual; ADD_MONTHS(SYSDATE, ------------------- 2012-02-26 03:53:08 Last_day 03:53:08 SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDATE) ------------------- 2011-02-28 03:54:05 Next_day 03:54:05 SQL> select to_char(sysdate,'yyyy-mm-dd day') from dual; TO_CHAR(SYSDATE,'YYY -------------------- 2011-02-26 saturday 03:55:18 SQL> select next_day(sysdate,'saturday') from dual; NEXT_DAY(SYSDATE,'S ------------------- 2011-03-05 03:55:49 03:55:49 SQL> select next_day(sysdate,'monday') from dual; NEXT_DAY(SYSDATE,'M ------------------- 2011-02-28 03:56:04 03:56:04 SQL> select to_char(sysdate,'yyyy-mm-dd dy') from dual; TO_CHAR(SYSDAT -------------- 2011-02-26 sat 分区 Oracle_SQL 的第 12 页 2011-02-26 sat 6、 SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat('010-','88888888')||'转23' 电话 from dual; 电话 ---------------- 010-88888888转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap('smith') upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) ------ ------------ ---------------- ------------ --------- -------------------- 分区 Oracle_SQL 的第 13 页 ------ ------------ ---------------- ------------ --------- -------------------- htrrt 3 北京市海锭区 6 9999.99 7 7.LOWER 返回字符串,并将所有的字符小写 SQL> select lower('AaBbCcDd')AaBbCcDd from dual; AABBCCDD -------- aabbccdd 8.UPPER 返回字符串,并将所有的字符大写 SQL> select upper('AaBbCcDd') upper from dual; UPPER -------- AABBCCDD 9.RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 SQL> select lpad(rpad('gao',10,'*'),17,'*') from dual; LPAD(RPAD('GAO',1 ----------------- *******gao******* 不够字符则用*来填满 10.LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; LTRIM(RTRIM(' ------------- gao qian jing 11.SUBSTR(string,start,count) 取子字符串,从start开始,取count个 SQL> select substr('13088888888',3,8) from dual; SUBSTR(' -------- 08888888 12.REPLACE('string','s1','s2') string 希望被替换的字符或变量 分区 Oracle_SQL 的第 14 页 s1 被替换的字符串 s2 要替换的字符串 SQL> select replace('he love you','he','i') from dual; REPLACE('H ---------- i love you 13.SOUNDEX 返回一个与给定的字符串读音相同的字符串 SQL> create table table1(xm varchar(8)); SQL> insert into table1 values('weather'); SQL> insert into table1 values('wether'); SQL> insert into table1 values('gao'); SQL> select xm from table1 where soundex(xm)=soundex('weather'); XM -------- weather wether 14.TRIM('s' from 'string') LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默认为空格符 15.ABS 返回指定值的绝对值 SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100) --------- --------- 100 100 16.ACOS 给出反余弦的值 SQL> select acos(-1) from dual; ACOS(-1) --------- 3.1415927 17.ASIN 给出反正弦的值 SQL> select asin(0.5) from dual; ASIN(0.5) --------- .52359878 分区 Oracle_SQL 的第 15 页 18.ATAN 返回一个数字的反正切值 SQL> select atan(1) from dual; ATAN(1) --------- .78539816 19.CEIL 返回大于或等于给出数字的最小整数 SQL> select ceil(3.1415927) from dual; CEIL(3.1415927) --------------- 4 20.COS 返回一个给定数字的余弦 SQL> select cos(-3.1415927) from dual; COS(-3.1415927) --------------- -1 21.COSH 返回一个数字反余弦值 SQL> select cosh(20) from dual; COSH(20) --------- 242582598 22.EXP 返回一个数字e的n次方根 SQL> select exp(2),exp(1) from dual; EXP(2) EXP(1) --------- --------- 7.3890561 2.7182818 23.FLOOR 对给定的数字取整数 SQL> select floor(2345.67) from dual; FLOOR(2345.67) -------------- 2345 分区 Oracle_SQL 的第 16 页 24.LN 返回一个数字的对数值 SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818) --------- --------- ------------- 0 .69314718 .99999999 25.LOG(n1,n2) 返回一个以n1为底n2的对数 SQL> select log(2,1),log(2,4) from dual; LOG(2,1) LOG(2,4) --------- --------- 0 2 26.MOD(n1,n2) 返回一个n1除以n2的余数 SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) --------- --------- --------- 1 0 2 27.POWER 返回n1的n2次方根 SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3) ----------- ---------- 1024 27 28.ROUND和TRUNC 按照指定的精度进行舍入 SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) ----------- ------------ ----------- ------------ 56 -55 55 -55 29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0 SQL> select sign(123),sign(-100),sign(0) from dual; SIGN(123) SIGN(-100) SIGN(0) --------- ---------- --------- 1 -1 0 30.SIN 分区 Oracle_SQL 的第 17 页 30.SIN 返回一个数字的正弦值 SQL> select sin(1.57079) from dual; SIN(1.57079) ------------ 1 31.SIGH 返回双曲正弦的值 SQL> select sin(20),sinh(20) from dual; SIN(20) SINH(20) --------- --------- .91294525 242582598 32.SQRT 返回数字n的根 SQL> select sqrt(64),sqrt(10) from dual; SQRT(64) SQRT(10) --------- --------- 8 3.1622777 33.TAN 返回数字的正切值 SQL> select tan(20),tan(10) from dual; TAN(20) TAN(10) --------- --------- 2.2371609 .64836083 34.TANH 返回数字n的双曲正切值 SQL> select tanh(20),tan(20) from dual; TANH(20) TAN(20) --------- --------- 1 2.2371609 35.TRUNC 按照指定的精度截取一个数 SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2) --------- ------------------ 100 124.16 36.ADD_MONTHS 分区 Oracle_SQL 的第 18 页 增加或减去月份 SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; TO_CHA ------ 200002 SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; TO_CHA ------ 199910 37.LAST_DAY 返回日期的最后一天 SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; LAST_DAY(S ---------- 31-5月 -04 38.MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份 SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual; MON_BETWEEN ----------- 9 SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual; MON_BETW --------- -60 39.NEW_TIME(date,'this','that') 给出在this时区=other时区的日期和时间 SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; BJ_TIME LOS_ANGLES ------------------- ------------------- 2004.05.09 11:05:32 2004.05.09 18:05:32 40.NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期 SQL> select next_day('18-5月-2001','星期五') next_day from dual; 分区 Oracle_SQL 的第 19 页 SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY ---------- 25-5月 -01 41.SYSDATE 用来得到系统的当前日期 SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual; TO_CHAR(SYSDATE,' ----------------- 09-05-2004 星期日 trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒 SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; HH HHMM ------------------- ------------------- 2004.05.09 11:00:00 2004.05.09 11:17:00 42.CHARTOROWID 将字符数据类型转换为ROWID类型 SQL> select rowid,rowidtochar(rowid),ename from scott.emp; ROWID ROWIDTOCHAR(ROWID) ENAME ------------------ ------------------ ---------- AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 43.CONVERT(c,dset,sset) 将源字符串 sset从一个语言字符集转换到另一个目的dset字符集 SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual; conver ------ strutz 44.HEXTORAW 将一个十六进制构成的字符串转换为二进制 45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型 分区 Oracle_SQL 的第 20 页 47.TO_CHAR(date,'format') SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2004/05/09 21:14:41 48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期 49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符 SQL> select to_multi_byte('高') from dual; TO -- 高 50.TO_NUMBER 将给出的字符转换为数字 SQL> select to_number('1999') year from dual; YEAR --------- 1999 51.BFILENAME(dir,file) 指定一个外部二进制文件 SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif')); 52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc SQL> select sid,serial#,username,decode(command, 2 0,'none', 3 2,'insert', 4 3, 5 'select', 6 6,'update', 7 7,'delete', 8 8,'drop', 9 'other') cmd from v$session where type!='background'; SID SERIAL# USERNAME CMD --------- --------- ------------------------------ ------ 1 1 none 2 1 none 分区 Oracle_SQL 的第 21 页 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none 53.DUMP(s,fmt,start,length) DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值 SQL> col global_name for a30 SQL> col dump_string for a50 SQL> set lin 200 SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING ------------------------------ -------------------------------------------------- ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 54.EMPTY_BLOB()和EMPTY_CLOB() 这两个函数都是用来对大数据类型字段进行初始化操作的函数 55.GREATEST 返回一组表达式中的最大值,即比较字符的编码大小. SQL> select greatest('AA','AB','AC') from dual; GR -- AC SQL> select greatest('啊','安','天') from dual; GR -- 天 56.LEAST 返回一组表达式中的最小值 SQL> select least('啊','安','天') from dual; LE -- 啊 57.UID 返回标识当前用户的唯一整数 SQL> show user 分区 Oracle_SQL 的第 22 页 USER 为"GAO" SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID ------------------------------ --------- GAO 25 58.USER 返回当前用户的名字 SQL> select user from dual; USER ------------------------------ GAO 59.USEREVN 返回当前用户环境的信息,opt可以是: ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看当前用户是否是DBA如果是则返回true SQL> select userenv('isdba') from dual; USEREN ------ FALSE SQL> select userenv('isdba') from dual; USEREN ------ TRUE SESSION 返回会话标志 SQL> select userenv('sessionid') from dual; USERENV('SESSIONID') -------------------- 152 ENTRYID 返回会话人口标志 SQL> select userenv('entryid') from dual; USERENV('ENTRYID') ------------------ 0 INSTANCE 返回当前INSTANCE的标志 SQL> select userenv('instance') from dual; USERENV('INSTANCE') ------------------- 1 分区 Oracle_SQL 的第 23 页 LANGUAGE 返回当前环境变量 SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG 返回当前环境的语言的缩写 SQL> select userenv('lang') from dual; USERENV('LANG') ---------------------------------------------------- ZHS TERMINAL 返回用户的终端或机器的标志 SQL> select userenv('terminal') from dual; USERENV('TERMINA ---------------- GAO VSIZE(X) 返回X的大小(字节)数 SQL> select vsize(user),user from dual; VSIZE(USER) USER ----------- ------------------------------ 6 SYSTEM 60.AVG(DISTINCT|ALL) all表示对所有的值求平均值,distinct只对不同的值求平均值 SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 语句已处理。 SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('zhu',5555.55); SQLWKS> commit; SQL> select avg(distinct sal) from gao.table3; AVG(DISTINCTSAL) ---------------- 3333.33 SQL> select avg(all sal) from gao.table3; AVG(ALLSAL) ----------- 2592.59 61.MAX(DISTINCT|ALL) 分区 Oracle_SQL 的第 24 页 61.MAX(DISTINCT|ALL) 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次 SQL> select max(distinct sal) from scott.emp; MAX(DISTINCTSAL) ---------------- 5000 62.MIN(DISTINCT|ALL) 求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次 SQL> select min(all sal) from gao.table3; MIN(ALLSAL) ----------- 1111.11 63.STDDEV(distinct|all) 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL) ----------- 1182.5032 SQL> select stddev(distinct sal) from scott.emp; STDDEV(DISTINCTSAL) ------------------- 1229.951 64.VARIANCE(DISTINCT|ALL) 求协方差 SQL> select variance(sal) from scott.emp; VARIANCE(SAL) ------------- 1398313.9 65.GROUP BY 主要用来对一组数进行统计 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 10 3 8750 20 5 10875 30 6 9400 66.HAVING 对分组统计再加限制条件 分区 Oracle_SQL 的第 25 页 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 67.ORDER BY 用于对查询到的结果进行排序输出 SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO ENAME SAL --------- ---------- --------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 SMITH 800 30 BLAKE 2850 30 ALLEN 1600 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250 30 JAMES 950 68.insert into 的时候要插入的变量里面含有很多字符,如:',",\ 等等应该怎么写? '号:insert into tabName (FieldName) values('aaa''bbb'); 也可以用chr函数,用ASCII码的形式插入,如: insert into tabName (FieldName) values('aaa||chr(39)||bbb'); -- chr(39)代表字符' 其它的特殊符号,都可以参照上面chr函数的形式插入,如 \ chr(92) " chr(34) 等 69.去除oracle数据库中字符串字段内容含有的\r\n replace(remark,chr(10),'') 7、 CHR() 和 ASICC 函数 CHR()函数将ASCII码转换为字符:字符 –》 ASCII码; ascii()函数将字符转换为ASCII码:ASCII码 –》 字符; 在oracle中chr()函数和ascii()是一对反函数。 chr()函数示例: SQL> select chr(65) from dual; 分区 Oracle_SQL 的第 26 页 CHR(65) ------- A accii()函数示例: SQL> select ascii('A') from dual; ASCII('A') ---------- 65 取出当前字段中最大的字母,并计算出下一个字母: SELECT CHR(ASCII(max(SUBSTR(UPPERCD,1,1)))+1) FROM exps_code_lm chr()函数可以输出的特殊字符: 暂无相关日志• 8、 单记录函数 SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual; 高乾竞电话 ---------------- 010-88888888转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap('smith') upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr('oracle traning','ra',1,2) instring from dual; 分区 Oracle_SQL 的第 27 页 SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) ------ ------------ ---------------- ------------ --------- -------------------- 高乾竞 3 北京市海锭区 6 9999.99 7 7.LOWER 返回字符串,并将所有的字符小写 SQL> select lower('AaBbCcDd')AaBbCcDd from dual; AABBCCDD -------- aabbccdd 8.UPPER 返回字符串,并将所有的字符大写 SQL> select upper('AaBbCcDd') upper from dual; UPPER -------- AABBCCDD 9.RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual; LPAD(RPAD('GAO',1 ----------------- *******gao******* 不够字符则用*来填满 10.LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; LTRIM(RTRIM(' ------------- gao qian jing 11.SUBSTR(string,start,count) 取子字符串,从start开始,取count个 SQL> select substr('13088888888',3,8) from dual; SUBSTR(' -------- 08888888 12.REPLACE('string','s1','s2') string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串 SQL> select replace('he love you','he','i') from dual; REPLACE('H ---------- i love you 13.SOUNDEX 返回一个与给定的字符串读音相同的字符串 SQL> create table table1(xm varchar(8)); SQL> insert into table1 values('weather'); SQL> insert into table1 values('wether'); SQL> insert into table1 values('gao'); SQL> select xm from table1 where soundex(xm)=soundex('weather'); XM -------- weather wether 14.TRIM('s' from 'string') 分区 Oracle_SQL 的第 28 页 14.TRIM('s' from 'string') LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默认为空格符 15.ABS 返回指定值的绝对值 SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100) --------- --------- 100 100 16.ACOS 给出反余弦的值 SQL> select acos(-1) from dual; ACOS(-1) --------- 3.1415927 17.ASIN 给出反正弦的值 SQL> select asin(0.5) from dual; ASIN(0.5) --------- .52359878 18.ATAN 返回一个数字的反正切值 SQL> select atan(1) from dual; ATAN(1) --------- .78539816 19.CEIL 返回大于或等于给出数字的最小整数 SQL> select ceil(3.1415927) from dual; CEIL(3.1415927) --------------- 4 20.COS 返回一个给定数字的余弦 SQL> select cos(-3.1415927) from dual; COS(-3.1415927) --------------- -1 21.COSH 返回一个数字反余弦值 SQL> select cosh(20) from dual; COSH(20) --------- 242582598 22.EXP 返回一个数字e的n次方根 SQL> select exp(2),exp(1) from dual; EXP(2) EXP(1) --------- --------- 7.3890561 2.7182818 23.FLOOR 对给定的数字取整数 SQL> select floor(2345.67) from dual; FLOOR(2345.67) -------------- 2345 24.LN 返回一个数字的对数值 SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818) 分区 Oracle_SQL 的第 29 页 SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818) --------- --------- ------------- 0 .69314718 .99999999 25.LOG(n1,n2) 返回一个以n1为底n2的对数 SQL> select log(2,1),log(2,4) from dual; LOG(2,1) LOG(2,4) --------- --------- 0 2 26.MOD(n1,n2) 返回一个n1除以n2的余数 SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) --------- --------- --------- 1 0 2 27.POWER 返回n1的n2次方根 SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3) ----------- ---------- 1024 27 28.ROUND和TRUNC 按照指定的精度进行舍入 SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) ----------- ------------ ----------- ------------ 56 -55 55 -55 29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0 SQL> select sign(123),sign(-100),sign(0) from dual; SIGN(123) SIGN(-100) SIGN(0) --------- ---------- --------- 1 -1 0 30.SIN 返回一个数字的正弦值 SQL> select sin(1.57079) from dual; SIN(1.57079) ------------ 1 31.SIGH 返回双曲正弦的值 SQL> select sin(20),sinh(20) from dual; SIN(20) SINH(20) --------- --------- .91294525 242582598 32.SQRT 返回数字n的根 SQL> select sqrt(64),sqrt(10) from dual; SQRT(64) SQRT(10) --------- --------- 8 3.1622777 33.TAN 返回数字的正切值 SQL> select tan(20),tan(10) from dual; TAN(20) TAN(10) --------- --------- 2.2371609 .64836083 34.TANH 返回数字n的双曲正切值 SQL> select tanh(20),tan(20) from dual; 分区 Oracle_SQL 的第 30 页 SQL> select tanh(20),tan(20) from dual; TANH(20) TAN(20) --------- --------- 1 2.2371609 35.TRUNC 按照指定的精度截取一个数 SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2) --------- ------------------ 100 124.16 36.ADD_MONTHS 增加或减去月份 SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; TO_CHA ------ 200002 SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; TO_CHA ------ 199910 37.LAST_DAY 返回日期的最后一天 SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; LAST_DAY(S ---------- 31-5月 -04 38.MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份 SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual; MON_BETWEEN ----------- 9 SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual; MON_BETW --------- -60 39.NEW_TIME(date,'this','that') 给出在this时区=other时区的日期和时间 SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; BJ_TIME LOS_ANGLES ------------------- ------------------- 2004.05.09 11:05:32 2004.05.09 18:05:32 40.NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期 SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY ---------- 25-5月 -01 41.SYSDATE 用来得到系统的当前日期 SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual; TO_CHAR(SYSDATE,' ----------------- 09-05-2004 星期日 trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒 SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; HH HHMM ------------------- ------------------- 2004.05.09 11:00:00 2004.05.09 11:17:00 分区 Oracle_SQL 的第 31 页 2004.05.09 11:00:00 2004.05.09 11:17:00 42.CHARTOROWID 将字符数据类型转换为ROWID类型 SQL> select rowid,rowidtochar(rowid),ename from scott.emp; ROWID ROWIDTOCHAR(ROWID) ENAME ------------------ ------------------ ---------- AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 43.CONVERT(c,dset,sset) 将源字符串 sset从一个语言字符集转换到另一个目的dset字符集 SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual; conver ------ strutz 44.HEXTORAW 将一个十六进制构成的字符串转换为二进制 45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型 47.TO_CHAR(date,'format') SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2004/05/09 21:14:41 48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期 49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符 SQL> select to_multi_byte('高') from dual; TO -- 高 50.TO_NUMBER 将给出的字符转换为数字 SQL> select to_number('1999') year from dual; YEAR --------- 1999 51.BFILENAME(dir,file) 指定一个外部二进制文件 SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif')); 52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc SQL> select sid,serial#,username,decode(command, 2 0,'none', 3 2,'insert', 4 3, 5 'select', 6 6,'update', 7 7,'delete', 8 8,'drop', 9 'other') cmd from v$session where type!='background'; SID SERIAL# USERNAME CMD --------- --------- ------------------------------ ------ 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 分区 Oracle_SQL 的第 32 页 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none 53.DUMP(s,fmt,start,length) DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值 SQL> col global_name for a30 SQL> col dump_string for a50 SQL> set lin 200 SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING ------------------------------ -------------------------------------------------- ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 54.EMPTY_BLOB()和EMPTY_CLOB() 这两个函数都是用来对大数据类型字段进行初始化操作的函数 55.GREATEST 返回一组表达式中的最大值,即比较字符的编码大小. SQL> select greatest('AA','AB','AC') from dual; GR -- AC SQL> select greatest('啊','安','天') from dual; GR -- 天 56.LEAST 返回一组表达式中的最小值 SQL> select least('啊','安','天') from dual; LE -- 啊 57.UID 返回标识当前用户的唯一整数 SQL> show user USER 为"GAO" SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID ------------------------------ --------- GAO 25 58.USER 返回当前用户的名字 SQL> select user from dual; USER ------------------------------ GAO 59.USEREVN 返回当前用户环境的信息,opt可以是: ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看当前用户是否是DBA如果是则返回true SQL> select userenv('isdba') from dual; USEREN ------ FALSE SQL> select userenv('isdba') from dual; USEREN ------ TRUE SESSION 返回会话标志 SQL> select userenv('sessionid') from dual; USERENV('SESSIONID') -------------------- 152 分区 Oracle_SQL 的第 33 页 152 ENTRYID 返回会话人口标志 SQL> select userenv('entryid') from dual; USERENV('ENTRYID') ------------------ 0 INSTANCE 返回当前INSTANCE的标志 SQL> select userenv('instance') from dual; USERENV('INSTANCE') ------------------- 1 LANGUAGE 返回当前环境变量 SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG 返回当前环境的语言的缩写 SQL> select userenv('lang') from dual; USERENV('LANG') ---------------------------------------------------- ZHS TERMINAL 返回用户的终端或机器的标志 SQL> select userenv('terminal') from dual; USERENV('TERMINA ---------------- GAO VSIZE(X) 返回X的大小(字节)数 SQL> select vsize(user),user from dual; VSIZE(USER) USER ----------- ------------------------------ 6 SYSTEM 60.AVG(DISTINCT|ALL) all表示对所有的值求平均值,distinct只对不同的值求平均值 SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 语句已处理。 SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('zhu',5555.55); SQLWKS> commit; SQL> select avg(distinct sal) from gao.table3; AVG(DISTINCTSAL) ---------------- 3333.33 SQL> select avg(all sal) from gao.table3; AVG(ALLSAL) ----------- 2592.59 61.MAX(DISTINCT|ALL) 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次 SQL> select max(distinct sal) from scott.emp; MAX(DISTINCTSAL) ---------------- 5000 62.MIN(DISTINCT|ALL) 求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次 SQL> select min(all sal) from gao.table3; MIN(ALLSAL) ----------- 1111.11 63.STDDEV(distinct|all) 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 分区 Oracle_SQL 的第 34 页 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL) ----------- 1182.5032 SQL> select stddev(distinct sal) from scott.emp; STDDEV(DISTINCTSAL) ------------------- 1229.951 64.VARIANCE(DISTINCT|ALL) 求协方差 SQL> select variance(sal) from scott.emp; VARIANCE(SAL) ------------- 1398313.9 65.GROUP BY 主要用来对一组数进行统计 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 10 3 8750 20 5 10875 30 6 9400 66.HAVING 对分组统计再加限制条件 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 67.ORDER BY 用于对查询到的结果进行排序输出 SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO ENAME SAL --------- ---------- --------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 SMITH 800 30 BLAKE 2850 30 ALLEN 1600 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250 30 JAMES 950 9、 日期函数 Oracle日期时间函数 1。Sysdate 当前日期和时间 SQL> Select sysdate from dual; SYSDATE ---------- 21-6月 -05 2。Last_day 本月最后一天 SQL> Select last_day(sysdate) from dual; 分区 Oracle_SQL 的第 35 页 SQL> Select last_day(sysdate) from dual; LAST_DAY(S ---------- 30-6月 -05 3。Add_months(d,n) 当前日期d后推n个月 用于从一个日期值增加或减少一些月份 date_value:=add_months(date_value,number_of_months) SQL> Select add_months(sysdate,2) from dual; ADD_MONTHS ---------- 21-8月 -05 4。Months_between(f,s) 日期f和s间相差月数 SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual; MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD')) ---------------------------------------------------------- -4.6966741 5。NEXT_DAY(d, day_of_week) 返回由"day_of_week"命名的,在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。 SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual; NEXT_DAY(T ---------- 26-6月 -05 6。current_date()返回当前会话时区中的当前日期 date_value:=current_date SQL> column sessiontimezone for a15 SQL> select sessiontimezone,current_date from dual; SESSIONTIMEZONE CURRENT_DA --------------- ---------- +08:00 13-11月-03 SQL> alter session set time_zone='-11:00' 2 / 会话已更改。 SQL> select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- ------------------------------------ -11:00 12-11月-03 04.59.13.668000 下午 -11:00 7。current_timestamp()以timestamp with time zone数据类型返回当前会话时区中的当前日期 SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 21-6月 -05 10.13.08.220589 上午 +08:00 8。dbtimezone()返回时区 SQL> select dbtimezone from dual; DBTIME ------ -08:00 9。extract()找出日期或间隔值的字段值 date_value:=extract(date_field from [datetime_value|interval_value]) SQL> select extract(month from sysdate) "This Month" from dual; This Month ---------- 6 分区 Oracle_SQL 的第 36 页 6 SQL> select extract(year from add_months(sysdate,36)) " Years" from dual; Years ---------- 2008 10。localtimestamp()返回会话中的日期和时间 SQL> select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 21-6月 -05 10.18.15.855652 上午 常用日期数据格式(该段为摘抄) Y或YY或YYY 年的最后一位,两位或三位 Select to_char(sysdate,‟YYY‟) from dual; 002表示2002年 SYEAR或YEAR SYEAR使公元前的年份前加一负号 Select to_char(sysdate,‟SYEAR‟) from dual;-1112表示公元前111 2年 Q 季度,1~3月为第一季度 Select to_char(sysdate,‟Q‟) from dual; 2表示第二季度① MM 月份数 Select to_char(sysdate,‟MM‟) from dual; 12表示12月 RM 月份的罗马表示 Select to_char(sysdate,‟RM‟) from dual; IV表示4月 Month 用9个字符长度表示的月份名 Select to_char(sysdate,‟Month‟) from dual; May后跟6个空格表示5月 WW 当年第几周 Select to_char(sysdate,‟WW‟) from dual; 24表示2002年6月13日为第24周 W 本月第几周 Select to_char(sysdate,‟W‟) from dual; 2002年10月1日为第1周 DDD 当年第几, 1月1日为001,2月1日为032 Select to_char(sysdate,‟DDD‟) from dual; 363 2002年1 2月2 9日为第363天 DD 当月第几天 Select to_char(sysdate,‟DD‟) from dual; 04 10月4日为第4天 D 周内第几天 Select to_char(sysdate,‟D‟) from dual; 5 2002年3月14日为星期一 DY 周内第几天缩写 Select to_char(sysdate,‟DY‟) from dual; SUN 2002年3月24日为星期天 HH或HH12 12进制小时数 Select to_char(sysdate,‟HH‟) from dual; 02 午夜2点过8分为02 HH24 24小时制 Select to_char(sysdate,‟HH24‟) from dual; 14 下午2点08分为14 MI 分钟数(0~59) Select to_char(sysdate,‟MI‟) from dual; 17下午4点17分 SS 秒数(0~59) Select to_char(sysdate,‟SS‟) from dual; 22 11点3分22秒 提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式,将它用于分钟也能工作,但结果是错误的。 现在给出一些实践后的用法: 1。上月末天: SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual; LASTDAY ---------- 2005-05-31 2。上月今天 SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual; PRETODAY ---------- 2005-05-21 3.上月首天 SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual; FIRSTDAY ---------- 2005-05-01 4.按照每周进行统计 SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww'); TO -- 25 5。按照每月进行统计 SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm'); 分区 Oracle_SQL 的第 37 页 SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm'); TO -- 06 6。按照每季度进行统计 SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q'); T - 2 7。按照每年进行统计 SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy'); TO_C ---- 2005 8.要找到某月中所有周五的具体日期 select to_char(t.d,'YY-MM-DD') from ( select trunc(sysdate, 'MM')+rownum-1 as d from dba_objects where rownum < 32) t where to_char(t.d, 'MM') = to_char(sysdate, 'MM')--找出当前月份的周五的日期 and trim(to_char(t.d, 'Day')) = '星期五' -------- 03-05-02 03-05-09 03-05-16 03-05-23 03-05-30 如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90,即为查找当前月份的前三个月中的每周五的日期。 9.oracle中时间运算 内容如下: 1、oracle支持对日期进行运算 2、日期运算时是以天为单位进行的 3、当需要以分秒等更小的单位算值时,按时间进制进行转换即可 4、进行时间进制转换时注意加括号,否则会出问题 SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss'; 会话已更改。 SQL> set serverout on SQL> declare 2 DateValue date; 3 begin 4 select sysdate into DateValue from dual; 5 dbms_output.put_line('源时间:'||to_char(DateValue)); 6 dbms_output.put_line('源时间减1天:'||to_char(DateValue-1)); 7 dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24)); 8 dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60))); 9 dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60))); 10 end; 11 / 源时间:2003-12-29 11:53:41 源时间减1天:2003-12-28 11:53:41 源时间减1天1小时:2003-12-28 10:53:41 源时间减1天1小时1分:2003-12-28 10:52:41 源时间减1天1小时1分1秒:2003-12-28 10:52:40 PL/SQL 过程已成功完成。 分区 Oracle_SQL 的第 38 页 在Oracle中实现时间相加处理 -- 名称:Add_Times -- 功能:返回d1与NewTime相加以后的结果,实现时间的相加 -- 说明:对于NewTime中的日期不予考虑 -- 日期:2004-12-07 -- 版本:1.0 -- 作者:Kevin create or replace function Add_Times(d1 in date,NewTime in date) return date is hh number; mm number; ss number; hours number; dResult date; begin -- 下面依次取出时、分、秒 select to_number(to_char(NewTime,'HH24')) into hh from dual; select to_number(to_char(NewTime,'MI')) into mm from dual; select to_number(to_char(NewTime,'SS')) into ss from dual; -- 换算出NewTime中小时总和,在一天的百分几 hours := (hh + (mm / 60) + (ss / 3600))/ 24; -- 得出时间相加后的结果 select d1 + hours into dResult from dual; return(dResult); end Add_Times; -- 测试用例 -- select Add_Times(sysdate,to_date('2004-12-06 03:23:00','YYYY-MM-DD HH24:MI:SS')) from dual 在Oracle9i中计算时间差 计算时间差是Oracle DATA数据类型的一个常见问题。Oracle支持日期计算,你可以创建诸如“日期1-日期2”这样的表达式来计算这两个日期 之间的时间差。 一旦你发现了时间差异,你可以使用简单的技巧来以天、小时、分钟或者秒为单位来计算时间差。为了得到数据差,你必须选择合适的时间度量单 位,这样就可以进行数据格式隐藏。 使用完善复杂的转换函数来转换日期是一个诱惑,但是你会发现这不是最好的解决方法。 round(to_number(end-date-start_date))- 消逝的时间(以天为单位) round(to_number(end-date-start_date)*24)- 消逝的时间(以小时为单位) round(to_number(end-date-start_date)*1440)- 消逝的时间(以分钟为单位) 显示时间差的默认模式是什么?为了找到这个问题的答案,让我们进行一个简单的SQL *Plus查询。 SQL> select sysdate-(sysdate-3) from dual; SYSDATE-(SYSDATE-3) ------------------- 3 这里,我们看到了Oracle使用天来作为消逝时间的单位,所以我们可以很容易的使用转换函数来把它转换成小时或者分钟。然而,当分钟数不是一 个整数时,我们就会遇到放置小数点的问题。 Select (sysdate-(sysdate-3.111))*1440 from dual; (SYSDATE-(SYSDATE-3.111))*1440 ------------------------------ 4479.83333 分区 Oracle_SQL 的第 39 页 当然,我们可以用ROUND函数(即取整函数)来解决这个问题,但是要记住我们必须首先把DATE数据类型转换成NUMBER数据类型。 Select round(to_number(sysdate-(sysdate-3.111))*1440) from dual; ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440) ---------------------------------------------- 4480 我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。在这个例子里,我们有一个离线(logoff)系统级触发机制来 计算已经开始的会话时间并把它放入一个Oracle STATSPACK USER_LOG扩展表格之中。 Update perfstat.stats$user_log set elapsed_minutes = round(to_number(logoff_time-logon_time)*1440) where user = user_id and elapsed_minutes is NULL; 查出任一年月所含的工作日 CREATE OR REPLACE FUNCTION Get_WorkingDays( ny IN VARCHAR2 ) RETURN INTEGER IS /*------------------------------------------------------------------------------------------ 函数名称:Get_WorkingDays 中文名称:求某一年月中共有多少工作日 作者姓名: XINGPING 编写时间: 2004-05-22 输入参数:NY:所求包含工作日数的年月,格式为yyyymm,如200405 返 回 值:整型值,包含的工作日数目。 算法描述: 1).列举出参数给出的年月中的每一天。这里使用了一个表(ljrq是我的库中的一张表。这个表可以是有权访问的、记录条数至少为31的任意一张 表或视图)来构造出某年月的每一天。 2).用这些日期和一个已知星期几的日期相减(2001-12-30是星期天),所得的差再对7求模。如果所求年月在2001-12-30以前,那么所得的差既 是负数,求模后所得值范围为大于-6,小于0,如-1表示星期六,故先将求模的结果加7,再求7的模. 3).过滤掉结果集中值为0和6的元素,然后求count,所得即为工作日数目。 ------------------------------------------------------------------------------------------------- */ Result INTEGER; BEGIN SELECT COUNT(*) INTO Result FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq FROM (SELECT substr(100+ROWNUM,2,2) dd FROM ljrq z WHERE Rownum<=31 ) t WHERE to_date(ny||t.dd,'yyyymmdd') BETWEEN to_date(ny,'yyyymm') AND last_day(to_date(ny,'yyyymm')) )q ) a WHERE a.weekday NOT IN(0,6); RETURN Result; END Get_WorkingDays; ______________________________________ 还有一个版本 CREATE OR REPLACE FUNCTION Get_WorkingDays( ny IN VARCHAR2 ) RETURN INTEGER IS /*----------------------------------------------------------------------------------------- 函数名称:Get_WorkingDays 中文名称:求某一年月中共有多少工作日 作者姓名: XINGPING 分区 Oracle_SQL 的第 40 页 作者姓名: XINGPING 编写时间: 2004-05-23 输入参数:NY:所求包含工作日数的年月,格式为yyyymm,如200405 返 回 值:整型值,包含的工作日数目。 算法描述:使用Last_day函数计算出参数所给年月共包含多少天,根据这个值来构造一个循环。在这个循环中先求这个月的每一天与一个已知是星 期天的日期(2001-12-30是星期天)的差,所得的差再对7求模。如果所求日期在2001-12-30以前,那么所得的差既是负数,求模后所得值范围为 大于-6,小于0,如-1表示星期六,故先将求模的结果加7,再求7的模. 如过所得值不等于0和6(即不是星期六和星期天),则算一个工作日。 ---------------------------------------------------------------------------------------- */ Result INTEGER := 0; myts INTEGER; --所给年月的天数 scts INTEGER; --某天距2001-12-30所差的天数 rq DATE; djt INTEGER := 1; -- BEGIN myts := to_char(last_day(to_date(ny,'yyyymm')),'dd'); LOOP rq := TO_date(ny||substr(100+djt,2),'yyyymmdd'); scts := rq - to_date('2001-12-30','yyyy-mm-dd'); IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN Result := Result + 1; END IF; djt := djt + 1; EXIT WHEN djt>myts; END LOOP; RETURN Result; END Get_WorkingDays; 以上两个版本的比较 第一个版本一条SQL语句就可以得出结果,不需要编程就可以达到目的。但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。 第二个版本需要编程,但不需要表或者视图。 这两个版本都还存在需要完善的地方,即没有考虑节日,如五一、十一、元旦、春节这些节假期都没有去除。这些节假日应该维护成一张表,然后 通过查表来去除这些节假日。 字符函数 Oracle字符函数 说明:字符函数输入值为字符类型,返回值为字符类型或数字类型,可以在sql语句中直接使用,也可以在pl/sql块中使用。 1、ASCII(n)函数 描述: 返回字符串的ascii码(当输入为字符串时返回第一个字符的ascii码) Examples:select ascii(‘A’) “test”,ascii(‘我们’) “test1” from dual; 2、CHR(n)函数 描述: 返回对应的ascii码的字符(n必须为数字类型) Examples:select ascii(54992) “test” from dual; 3、CONCAT(n,m)函数 描述: 连接n和m,n和m可以是字符,也可以是字符串。作用和”||”一样。 Examples:select concat(‘中国’,’人民’) “test” from dual; 4、INITCAP(n)函数 描述: 将字符串n中每个单词首字母大写,其余小写(区分单词的规则是按空格或非字母字符;可以输入中文字符,但没有任何作用) Examples:select initcap(‘中 国 人 民’) “test”,initcap(‘my word’) “test1”,initcap(‘my中国word’) “test2” from dual; 5、INSTR(chr1,chr2,[n,[m]])函数 描述: 获取字符串chr2在字符串chr1中出现的位置。n和m可选,省略是默认为1;n代表开始查找的起始位置,当n为负数从尾部开始搜索;m代表 字串出现的次数。 Examples:select instr('pplkoopijk','k',-1,1) “test” from dual; 备注:当n为负数从尾部搜索,但返回值仍然是按正向排列得出的位置。 6、LENGTH(n)函数 描述: 返回字符或字符串长度。(当n为null时,返回nll;返回的长度包括后面的空格) Examples:select length('ppl ') “test”,length(null) “test1” from dual; 分区 Oracle_SQL 的第 41 页 7、LOWER(n)函数 描述: 将n转换为小写。 Examples:select lower('KKKD') “test” from dual; 8、LPAD(chr1,n,[chr2])函数 描述: 在chr1左边填充字符chr2,使得字符总长度为n。chr2可选,默认为空格;当chr1字符串长度大于n时,则从左边截取chr1的n个字符显 示。 Examples:select lpad('kkk',5) “test”,lpad(‘kkkkk’,4) “test1”,lpad(‘kkk’,6,’lll’) “test2” from dual; 9、LTRIM(chr,[n])函数 描述: 去掉字符串chr左边包含的n字符串中的任何字符,直到出现一个不包含在n中的字符为止。 Examples:select ltrim('abcde',’a’) “test”,ltrim(‘abcde’,’b’) “test1”,ltrim(‘abcdefg’,’cba’) “test2” from dual; 10、NLS_INITCAP(chr,[‟nls_param‟])函数 描述: 将chr首字母大写。Nls_param可选,指定排序的方式。(有SCHINESE_RADICAL_M(部首、笔画), SCHINESE_STROKE_M(笔画、部首),SCHINESE_PINYIN_M(拼音)) Examples:select nls_initcap('ab cde') “test”,nls_initcap(‘a b c d e’,’nls_sort= SCHINESE_PINYIN_M’) “test1” from dual; 11、NLS_LOWER(chr,[„nls_param‟])函数 描述: 将字符串转换为小写。Nls_param可选,指定排序的方式。(有SCHINESE_RADICAL_M(部首、笔画), SCHINESE_STROKE_M(笔画、部首),SCHINESE_PINYIN_M(拼音)) Examples:select nls_lower('ABC') “test”,nls_lower(‘ABC’,’nls_sort= SCHINESE_PINYIN_M’) “test1” from dual; 12、NLSSORT(col,[‟nls_param‟])函数 描述: 根据nls_param指定的方式对col字段进行排序。 Examples:SELECT part_number FROM cux_om_part_all ORDER BY nlssort(part_number,'nls_sort=SCHINESE_RADICAL_M') 13、NLS_UPPER(chr,[„nls_param‟])函数 描述: 将chr转换为大写。Nls_param可选,用于指定排序规则 Examples:SELECT nls_upper('ddddd','nls_sort=xdanish') FROM dual 14、REGEXP_REPLACE(source_string,pattern,replace_string,position,occurtence,match_parameter)函数(10g新函数) 描述:字符串替换函数。相当于增强的replace函数。Source_string指定源字符表达式;pattern指定规则表达式;replace_string指定用于 替换的字符串;position指定起始搜索位置;occurtence指定替换出现的第n个字符串;match_parameter指定默认匹配操作的文本串。 其中replace_string,position,occurtence,match_parameter参数都是可选的。 15、REGEXP_SUBSTR(source_string, pattern[,position [, occurrence[, match_parameter]]])函数(10g新函数) 描述:返回匹配模式的子字符串。相当于增强的substr函数。Source_string指定源字符表达式;pattern指定规则表达式;position指定起 始搜索位置;occurtence指定替换出现的第n个字符串;match_parameter指定默认匹配操作的文本串。 Examples:select regexp_substr(„http://www.oracle.com/products‟,‟http://([[:alnum:]]+\.?) {3,4} / ?‟) “regexp_substr” from dual 其中position,occurtence,match_parameter参数都是可选的 16、REGEXP_LIKE(source_string, pattern[, match_parameter])函数(10g新函数) 描述:返回满足匹配模式的字符串。相当于增强的like函数。Source_string指定源字符表达式;pattern指定规则表达式; match_parameter指定默认匹配操作的文本串。 Examples: 其中position,occurtence,match_parameter参数都是可选的 17、REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[, return_option[, match_parameter]]]])函数(10g 新函数) 描述: 该函数查找 pattern ,并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1,除 非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0,它返回该模式的起始位置;值为 1 则返回符合匹配条件的下一个字符的 起始位置 Examples: 分区 Oracle_SQL 的第 42 页 18、REPLACE(chr,search_string,[,replacement_string])函数 描述:将chr中满足search_string条件的替换为replacement_string指定的字符串,当search_string为null时,返回chr;当 replacement_string为null时,返回chr中截取掉search_string部分的字符串。 Examples:SELECT REPLACE('abcdeef','e','oo') "test",REPLACE('abcdeef','ee','oo') "test1",REPLACE('abcdeef',NULL,'oo') "test2",REPLACE('abcdeef','ee',NULL) "test3" FROM dual 描述:在chr1右边填充chr2,使返回字符串长度为n..当chr1长度大于n时,返回左端n个字符。参考LPAD()函数。 19、RPAD(chr1,n,chr2)函数 20、RTRIM(chr,[set])函数 描述:去掉chr右边包含的set中的任何字符,直到出现一个不是set中的字符结束。参考LTRIM()函数。 描述:返回字符串的语音表示,可以用来比较字符串的发音是否相同。 Examples:select soundex(‘ship’) “test”,soundex(‘sleep’) “test1” from dual; 21、SOUNDEX(chr)函数 描述:取chr的子串。M代表开始位置,n是要取的长度。当m为0时从首字符开始,当m为负时从字符串尾部开始截取。 Examples:select substr(‘abcdef’,0,3) “test”,substr(‘abcdef’,1,3) “test1”,substr(‘abcdef’,-3,3) “test2”,substr(‘abcdef’,-1,3) “test3” from dual 注意:m取0或1时,开始位置是一样的,都是从第一位开始,m为负的时候,仍然是按从左到右的顺序取,所以如果m为-1,n的长度再大,也 只能取到最后一个字符,因为chr右边已经没有字符了。 22、SUBSTR(chr,m[,n])函数 23、TRANSLATE(chr,from_str,to_str)函数 描述:另一种替换函数的用法。 Examples:SELECT translate('abcdeabc','abc','fgh') "test",translate('abcdeabc','abc','hf') "test1",translate('abcdeabc','ab','hfgh') "test2",translate('abcdeabc','abc',' ') "test3" FROM dual 注意:匹配的规则是from_str和to_str每个字符按顺序相对应,如果from_str字符少于to_str中的字符,则只替换能对应的字符,to_str后面不能 和from_str对应的字符则不管,如果from_str字符多于to_str字符,则from_str中找不到对应字符按照null来处理。 24、TRIM(chr)函数 TRIM函数将字符串的前缀(或尾随)字符删除。 其具体的语法格式如下: TRIM([LEADING|TRAILING|BOTH][trimchar FROM] string) 其中: LEADING 指明仅仅将字符串的前缀字符删除 TRAILING 指明仅仅将字符串的尾随字符删除 BOTH 指明既删除前缀字符,也删除尾随字符。这也是默认方式 string 任意一待处理字符串 trimchar 可选项。指明试图删除什么字符,默认被删除的字符是空格 下面是该函数的使用情况: TRIM(’ Ashley ‟)=„Ashley‟ TRIM(LEADING ‟*‟ FROM‟***Ashley***‟)=„Ashley***‟ 25、UPPER(chr)函数 UPPER函数间返回字符串的大写形式。 其具体的语法格式如下: UPPER(string) 其中: string 任意VARCHAR2或CHAR型字符串 下面是该函数的使用情况: UPPER(’THIS IS a Test‟)=„THIS IS A TEST’ Oracle over函数 Oracle over函数 sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序,对于相同分组字段的结果集进行排序, 其中PARTITION BY 为分组字段,ORDER BY 指定排序字段 over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。 分区 Oracle_SQL 的第 43 页 over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。 其参数:over(partition by columnname1 order by columnname2) 含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。 例如:employees表中,有两个部门的记录:department_id =10和20 select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在 部门20中进行薪水排名。如果是partition by org_id,则是在整个公司内进行排名。 WITH OBJ AS( SELECT name, type 目的:以oracle自带的scott模式为测试环境,主要通过试验体会分析函数的用法。 类似 sum(...) over ... 的使用 1.原表信息: SQL> break on deptno skip 1 -- 为效果更明显,把不同部门的数据隔段显示。 SQL> select deptno,ename,sal 2 from emp 3 order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 KING 5000 MILLER 1300 20 SMITH 800 ADAMS 1100 FORD 3000 SCOTT 3000 JONES 2975 30 ALLEN 1600 BLAKE 2850 MARTIN 1250 JAMES 950 TURNER 1500 WARD 1250 已选择14行。 2.先来一个简单的,注意over(...)条件的不同, 使用 sum(sal) over (order by ename)... 查询员工的薪水‚连续‛求和, 注意over (order by ename)如果没有order by 子句,求和就不是‚连续‛的, 放在一起,体会一下不同之处: SQL> break on '' -- 取消数据分段显示 SQL> select deptno,ename,sal, 2 sum(sal) over (order by ename) 连续求和, 3 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal) 4 100*round(sal/sum(sal) over (),4) "份额(%)" 5 from emp 6 / DEPTNO ENAME SAL 连续求和 总和 份额(%) ---------- ---------- ---------- ---------- ---------- ---------- 20 ADAMS 1100 1100 29025 3.79 分区 Oracle_SQL 的第 44 页 20 ADAMS 1100 1100 29025 3.79 30 ALLEN 1600 2700 29025 5.51 30 BLAKE 2850 5550 29025 9.82 10 CLARK 2450 8000 29025 8.44 20 FORD 3000 11000 29025 10.34 30 JAMES 950 11950 29025 3.27 20 JONES 2975 14925 29025 10.25 10 KING 5000 19925 29025 17.23 30 MARTIN 1250 21175 29025 4.31 10 MILLER 1300 22475 29025 4.48 20 SCOTT 3000 25475 29025 10.34 20 SMITH 800 26275 29025 2.76 30 TURNER 1500 27775 29025 5.17 30 WARD 1250 29025 29025 4.31 已选择14行。 3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同, sum(sal) over (partition by deptno order by ename) 按部门‚连续‛求总和 sum(sal) over (partition by deptno) 按部门求总和 sum(sal) over (order by deptno,ename) 不按部门‚连续‛求总和 sum(sal) over () 不按部门,求所有员工总和,效果等同于sum(sal)。 SQL> break on deptno skip 1 -- 为效果更明显,把不同部门的数据隔段显示。 SQL> select deptno,ename,sal, 2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和 3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和,同一部门总和不变 4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)", 5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和 6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal),所有员工的薪水总和 7 100*round(sal/sum(sal) over (),4) "总份额(%)" 8 from emp 9 / DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和 总份额(%) ------ ------ ----- ------------ ---------- ----------- ---------- ------ ---------- 10 CLARK 2450 2450 8750 28 2450 29025 8.44 KING 5000 7450 8750 57.14 7450 29025 17.23 MILLER 1300 8750 8750 14.86 8750 29025 4.48 20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79 FORD 3000 4100 10875 27.59 12850 29025 10.34 JONES 2975 7075 10875 27.36 15825 29025 10.25 SCOTT 3000 10075 10875 27.59 18825 29025 10.34 SMITH 800 10875 10875 7.36 19625 29025 2.76 30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51 BLAKE 2850 4450 9400 30.32 24075 29025 9.82 JAMES 950 5400 9400 10.11 25025 29025 3.27 MARTIN 1250 6650 9400 13.3 26275 29025 4.31 TURNER 1500 8150 9400 15.96 27775 29025 5.17 WARD 1250 9400 9400 13.3 29025 29025 4.31 分区 Oracle_SQL 的第 45 页 已选择14行。 4.来一个综合的例子,求和规则有按部门分区的,有不分区的例子 SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum, 2 sum(sal) over (order by deptno,sal) sum 3 from emp; DEPTNO ENAME SAL DEPT_SUM SUM ---------- ---------- ---------- ---------- ---------- 10 MILLER 1300 1300 1300 CLARK 2450 3750 3750 KING 5000 8750 8750 20 SMITH 800 800 9550 ADAMS 1100 1900 10650 JONES 2975 4875 13625 SCOTT 3000 10875 19625 FORD 3000 10875 19625 30 JAMES 950 950 20575 WARD 1250 3450 23075 MARTIN 1250 3450 23075 TURNER 1500 4950 24575 ALLEN 1600 6550 26175 BLAKE 2850 9400 29025 已选择14行。 5.来一个逆序的,即部门从大到小排列,部门里各员工的薪水从高到低排列,累计和的规则不变。 SQL> select deptno,ename,sal, 2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum, 3 sum(sal) over (order by deptno desc,sal desc) sum 4 from emp; DEPTNO ENAME SAL DEPT_SUM SUM ---------- ---------- ---------- ---------- ---------- 30 BLAKE 2850 2850 2850 ALLEN 1600 4450 4450 TURNER 1500 5950 5950 WARD 1250 8450 8450 MARTIN 1250 8450 8450 JAMES 950 9400 9400 20 SCOTT 3000 6000 15400 FORD 3000 6000 15400 JONES 2975 8975 18375 ADAMS 1100 10075 19475 MITH 800 10875 20275 10 KING 5000 5000 25275 CLARK 2450 7450 27725 分区 Oracle_SQL 的第 46 页 CLARK 2450 7450 27725 MILLER 1300 8750 29025 已选择14行。 6.体会:在"... from emp;"后面不要加order by 子句,使用的分析函数的(partition by deptno order by sal) 里已经有排序的语句了,如果再在句尾添加排序子句,一致倒罢了,不一致,结果就令人费劲了。如: SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum, 2 sum(sal) over (order by deptno,sal) sum 3 from emp 4 order by deptno desc; DEPTNO ENAME SAL DEPT_SUM SUM ---------- ---------- ---------- ---------- ---------- 30 JAMES 950 950 20575 WARD 1250 3450 23075 MARTIN 1250 3450 23075 TURNER 1500 4950 24575 ALLEN 1600 6550 26175 BLAKE 2850 9400 29025 20 SMITH 800 800 9550 ADAMS 1100 1900 10650 JONES 2975 4875 13625 SCOTT 3000 10875 19625 FORD 3000 10875 19625 10 MILLER 1300 1300 1300 CLARK 2450 3750 3750 KING 5000 8750 8750 已选择14行 ================================================================== 利用over实现的分页功能: --假设code1,code2为用来分页的KEY,每页显示5第数据 select code1,code2,code3, ceil(count(*) over(partition by code1,code2 order by rownum)/5), count(*) over(partition by code1,code2) from ma_kbn order by code1,code2 ================================================================== 源文档 分区 Oracle_SQL 的第 47 页 删除表重复记录 select fphm from fp_sgfpxx group by fphm having count(fphm) > 1) and rowid not in (select min(rowid) from fp_sgfpxx group by fphm having count(fphm )>1) 1. 删除重复的发票号码2. 查询同一表内多字段同时重复记录的SQL语句 3. 来自:7th string 4. 比如现在有一人员表 (表名:peosons) 5. 若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来 select p1.* from persons p1,persons p2 where p1.id<>p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address 6. 可以实现上述效果. 7. 几个删除重复记录的SQL语句 8. 1.用rowid方法 9. 2.用group by方法 10. 3.用distinct方法 11. 1。用rowid方法 12. 据据Oracle带的rowid属性,进行判断,是否存在重复,语句如下: 13. 查数据: 14. select * from table1 a where rowid !=(select max(rowid) 15. from table1 b where a.name1=b.name1 and a.name2=b.name2......) 16. 删数据: 17. delete from table1 a where rowid !=(select max(rowid) 18. from table1 b where a.name1=b.name1 and a.name2=b.name2......) 19. 2.group by方法 20. 查数据: 21. select count(num), max(name) from student --列出重复的记录数,并列出他的name属性 22. group by num 23. having count(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次 24. 删数据: 25. delete from student 26. group by num 27. having count(num) >128. 这样的话就把所有重复的都删除了。 29. 3.用distinct方法 -对于小的表比较有用 30. create table table_new as select distinct * from table1 minux 31. truncate table table1; 32. insert into table1 select * from table_new; 33. 查询及删除重复记录的方法大全 34. 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 35. select * from people 36. where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 37. 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 38. delete from people 39. where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 40. and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 41. 3、查找表中多余的重复记录(多个字段) 42. select * from vitae a 43. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 44. 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 45. delete from vitae a 46. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 47. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 48. 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 49. select * from vitae a 50. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 51. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 52. (二) 53. 比方说 54. 在A表中存在一个字段‚name‛, 55. 而且不同记录之间的‚name‛值有可能会相同, 删除表重复记录 2012年3月26日 18:30 分区 Oracle_SQL 的第 48 页 而且不同记录之间的‚name‛值有可能会相同, 56. 现在就是需要查询出在该表中的各记录之间,‚name‛值存在重复的项; 57. Select Name,Count(*) From A Group By Name Having Count(*) > 158. 如果还查性别也相同大则如下: 59. Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 160. (三) 61. 方法一 62. declare @max integer,@id integer 63. declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 164. open cur_rows 65. fetch cur_rows into @id,@max66. while @@fetch_status=067. begin 68. select @max = @max -169. set rowcount @max70. delete from 表名 where 主字段 = @id71. fetch cur_rows into @id,@max72. end 73. close cur_rows 74. set rowcount 075. 方法二 76. "重复记录"有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name 字段重复,而其他字段不一定重复或都重复可以忽略。 77. 1、对于第一种重复,比较容易解决,使用 78. select distinct * from tableName 79. 就可以得到无重复记录的结果集。 80. 如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除 81. select distinct * into #Tmp from tableName 82. drop table tableName 83. select * into tableName from #Tmp 84. drop table #Tmp 85. 发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 86. 2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下 87. 假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集 88. select identity(int,1,1) as autoID, * into #Tmp from tableName 89. select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID 90. select * from #Tmp where autoID in(select autoID from #tmp2) 91. 最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列) 92. (四) 93. 查询重复 94. select * from tablename where id in ( 95. select id from tablename 96. group by id 97. having count(id) > 198. ) 99. =========================== 100. 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢! 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3、查找表中多余的重复记录(多个字段) select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 101. 分区 Oracle_SQL 的第 49 页 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 比方说在A表中存在一个字段‚name‛,而且不同记录之间的‚name‛值有可能会相同, 现在就是需要查询出在该表中的各记录之间,‚name‛值存在重复的项; Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 2.group by方法 查数据: select count(num), max(name) from student --列出重复的记录数,并列出他的name属性 group by num having count(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次 删数据: delete from student group by num having count(num) >1 这样的话就把所有重复的都删除了。 3.用distinct方法 -对于小的表比较有用 create table table_new as select distinct * from table1 minux truncate table table1; insert into table1 select * from table_new; delete select a.* from FLRK1 a inner join FLRK1 b on a.记录号=b.记录号 and (a.[ID]=b.[ID] and a.入库日期=b.入库日期 and a.操作时间=b.操作时间) delete from FLRK1 where 记录号 in (select min(记录号) from FLRK1 group by 记录号 having count(记录号)>1) A表结构: ID RQ SJ C -------------------------------------------- 1 2005-07-14 14:20:50 A1 2 2005-02-15 05:12:23 A1 3 2005-07-14 14:20:50 A1 4 2005-06-16 16:16:16 A2 5 2005-06-16 16:16:16 A2 6 2005-05-18 05:10:35 A3 7 2005-02-15 05:12:23 A1 -------------------------------------------- 求SQL语句一条,把表A中 RQ,SJ,C 三个字段有相同的重复记录删除. 得到的结果: ID RQ SJ C -------------------------------------------- 1 2005-07-14 14:20:50 A1 分区 Oracle_SQL 的第 50 页 1 2005-07-14 14:20:50 A1 2 2005-02-15 05:12:23 A1 4 2005-06-16 16:16:16 A2 6 2005-05-18 05:10:35 A3 -------------------------------------------- Delete from A Where ID Not In (Select Min(ID) from A Group By RQ,SJ,C ) Delete a from tb a inner join tb as b on a.fid 1 open cur_rows fetch cur_rows into @id,@max while fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 方法二 有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而 其他字段不一定重复或都重复可以忽略。 1、对于第一种重复,比较容易解决,使用 select distinct * from tableName 就可以得到无重复记录的结果集。 如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp 发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下 假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集 select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列) 源文档 分区 Oracle_SQL 的第 51 页 分区 Oracle_SQL 的第 52 页 Oracle 分析函数的使用 Oracle 分析函数使用介绍 分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查 询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高. 下面我将针对分析函数做一些具体的说明. 今天我主要给大家介绍一下以下几个函数的使用方法 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数 基础数据 Code: [Copy to clipboard] 06:34:23 SQL> select * from t; BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- -------------- 200405 5761 G 7393344.04 200405 5761 J 5667089.85 200405 5762 G 6315075.96 200405 5762 J 6328716.15 200405 5763 G 8861742.59 200405 5763 J 7788036.32 200405 5764 G 6028670.45 200405 5764 J 6459121.49 200405 5765 G 13156065.77 200405 5765 J 11901671.70 200406 5761 G 7614587.96 200406 5761 J 5704343.05 200406 5762 G 6556992.60 200406 5762 J 6238068.05 200406 5763 G 9130055.46 200406 5763 J 7990460.25 200406 5764 G 6387706.01 200406 5764 J 6907481.66 200406 5765 G 13562968.81 200406 5765 J 12495492.50 200407 5761 G 7987050.65 200407 5761 J 5723215.28 200407 5762 G 6833096.68 200407 5762 J 6391201.44 200407 5763 G 9410815.91 200407 5763 J 8076677.41 200407 5764 G 6456433.23 200407 5764 J 6987660.53 200407 5765 G 14000101.20 200407 5765 J 12301780.20 200408 5761 G 8085170.84 200408 5761 J 6050611.37 Oracle 统计函数 2011年5月7日 13:26 分区 Oracle_SQL 的第 53 页 200408 5761 J 6050611.37 200408 5762 G 6854584.22 200408 5762 J 6521884.50 200408 5763 G 9468707.65 200408 5763 J 8460049.43 200408 5764 G 6587559.23 BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- -------------- 200408 5764 J 7342135.86 200408 5765 G 14450586.63 200408 5765 J 12680052.38 40 rows selected. Elapsed: 00:00:00.00 1. 使用rollup函数的介绍 Quote: 下面是直接使用普通sql语句求出各地区的汇总数据的例子 06:41:36 SQL> set autot on 06:43:36 SQL> select area_code,sum(local_fare) local_fare 06:43:50 2 from t 06:43:51 3 group by area_code 06:43:57 4 union all 06:44:00 5 select '合计' area_code,sum(local_fare) local_fare 06:44:06 6 from t 06:44:08 7 / AREA_CODE LOCAL_FARE ---------- -------------- 5761 54225413.04 5762 52039619.60 5763 69186545.02 5764 53156768.46 5765 104548719.19 合计 333157065.31 6 rows selected. Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes= 24884) 1 0 UNION-ALL 2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871) 3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248 71) 4 1 SORT (AGGREGATE) 5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170 17) 分区 Oracle_SQL 的第 54 页 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 561 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6 rows processed 下面是使用分析函数rollup得出的汇总数据的例子 06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare 06:45:26 2 from t 06:45:30 3 group by rollup(nvl(area_code,'合计')) 06:45:50 4 / AREA_CODE LOCAL_FARE ---------- -------------- 5761 54225413.04 5762 52039619.60 5763 69186545.02 5764 53156768.46 5765 104548719.19 333157065.31 6 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes= 24871) 1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871) 2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871 ) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 557 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 分区 Oracle_SQL 的第 55 页 0 sorts (disk) 6 rows processed 从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果 基表很大的话,结果就可想而知了. 1. 使用cube函数的介绍 Quote: 为了介绍cube函数我们再来看看另外一个使用rollup的例子 06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:53:37 2 from t 06:53:38 3 group by rollup(area_code,bill_month) 06:53:49 4 / AREA_CODE BILL_MONTH LOCAL_FARE ---------- --------------- -------------- 5761 200405 13060433.89 5761 200406 13318931.01 5761 200407 13710265.93 5761 200408 14135782.21 5761 54225413.04 5762 200405 12643792.11 5762 200406 12795060.65 5762 200407 13224298.12 5762 200408 13376468.72 5762 52039619.60 5763 200405 16649778.91 5763 200406 17120515.71 5763 200407 17487493.32 5763 200408 17928757.08 5763 69186545.02 5764 200405 12487791.94 5764 200406 13295187.67 5764 200407 13444093.76 5764 200408 13929695.09 5764 53156768.46 5765 200405 25057737.47 5765 200406 26058461.31 5765 200407 26301881.40 5765 200408 27130639.01 5765 104548719.19 333157065.31 26 rows selected. Elapsed: 00:00:00.00 系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而 设计的. 下面,让我们看看使用cube函数的结果 06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:58:30 2 from t 06:58:32 3 group by cube(area_code,bill_month) 06:58:42 4 order by area_code,bill_month nulls last 分区 Oracle_SQL 的第 56 页 06:58:42 4 order by area_code,bill_month nulls last 06:58:57 5 / AREA_CODE BILL_MONTH LOCAL_FARE ---------- --------------- -------------- 5761 200405 13060.43 5761 200406 13318.93 5761 200407 13710.27 5761 200408 14135.78 5761 54225.41 5762 200405 12643.79 5762 200406 12795.06 5762 200407 13224.30 5762 200408 13376.47 5762 52039.62 5763 200405 16649.78 5763 200406 17120.52 5763 200407 17487.49 5763 200408 17928.76 5763 69186.54 5764 200405 12487.79 5764 200406 13295.19 5764 200407 13444.09 5764 200408 13929.69 5764 53156.77 5765 200405 25057.74 5765 200406 26058.46 5765 200407 26301.88 5765 200408 27130.64 5765 104548.72 200405 79899.53 200406 82588.15 200407 84168.03 200408 86501.34 333157.05 30 rows selected. Elapsed: 00:00:00.01 可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果 源文档 Grouping函数: 可以接受一列,返回0或1。如果列值为空,则返回1,非空则返回0。它只能在rollup和或cube函数中使用,因为在统计中显示 ‚全部‛的那一项统计值时,那一项的标签通常是空的,这时grouping就非常有用,还可以在grouping的基础上进行decode, case等进行 美化。 SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*) 2 from t group by rollup(index_type, status) order by 1, 2; G_IND G_ST INDEX_TYPE STATUS COUNT(*) ---------- ---------- --------------------------- -------- ---------- 0 0 LOB N/A 1 0 0 LOB VALID 572 0 0 FUNCTION-BASED NORMAL VALID 17 0 0 FUNCTION-BASED DOMAIN VALID 1 0 0 IOT - TOP VALID 115 0 0 CLUSTER VALID 10 0 0 NORMAL VALID 4557 0 0 NORMAL N/A 56 分区 Oracle_SQL 的第 57 页 0 0 NORMAL N/A 56 0 0 DOMAIN VALID 1 0 0 BITMAP VALID 8 0 0 BITMAP N/A 7 0 1 IOT - TOP 115 0 1 FUNCTION-BASED DOMAIN 1 0 1 DOMAIN 1 0 1 CLUSTER 10 0 1 BITMAP 15 0 1 FUNCTION-BASED NORMAL 17 0 1 NORMAL 4613 0 1 LOB 573 1 1 5345 20 rows selected Grouping_id函数: 比grouping还强点,可以接收多个列,这几个列都不为空时,返回0,只要有一个为空,则返回1,如果都为空,则返回3。 下面这条查询把grouping_id牛刀杀鸡,当做grouping用了,可以看到结果跟用grouping是完全一样。 SQL> select grouping_id(index_type) g_ind, grouping_id(status) g_st, index_type, status, count(*) 2 from t group by rollup(index_type, status) order by 1, 2; G_IND G_ST INDEX_TYPE STATUS COUNT(*) ---------- ---------- --------------------------- -------- ---------- 0 0 LOB N/A 1 0 0 LOB VALID 572 0 0 FUNCTION-BASED NORMAL VALID 17 0 0 FUNCTION-BASED DOMAIN VALID 1 0 0 IOT - TOP VALID 115 0 0 CLUSTER VALID 10 0 0 NORMAL VALID 4557 0 0 NORMAL N/A 56 0 0 DOMAIN VALID 1 0 0 BITMAP VALID 8 0 0 BITMAP N/A 7 0 1 IOT - TOP 115 0 1 FUNCTION-BASED DOMAIN 1 0 1 DOMAIN 1 0 1 CLUSTER 10 0 1 BITMAP 15 0 1 FUNCTION-BASED NORMAL 17 0 1 NORMAL 4613 0 1 LOB 573 1 1 5345 20 rows selected 下面这条查询才体现了grouping_id的作用。 SQL> select grouping_id(index_type, status) g_st, index_type, status, count(*) 2 from t group by rollup(index_type, status) order by 1, 2; G_ST INDEX_TYPE STATUS COUNT(*) ---------- --------------------------- -------- ---------- 0 BITMAP VALID 8 0 BITMAP N/A 7 0 CLUSTER VALID 10 0 DOMAIN VALID 1 0 FUNCTION-BASED DOMAIN VALID 1 0 FUNCTION-BASED NORMAL VALID 17 分区 Oracle_SQL 的第 58 页 0 FUNCTION-BASED NORMAL VALID 17 0 IOT - TOP VALID 115 0 LOB N/A 1 0 LOB VALID 572 0 NORMAL N/A 56 0 NORMAL VALID 4557 1 BITMAP 15 1 CLUSTER 10 1 DOMAIN 1 1 FUNCTION-BASED DOMAIN 1 1 FUNCTION-BASED NORMAL 17 1 IOT - TOP 115 1 LOB 573 1 NORMAL 4613 3 5345 20 rows selected 根据grouping_id接收不同的列的组合,可以美化出很神奇的结果。 分区 Oracle_SQL 的第 59 页 昨天在itpub看到这个帖子, 问题觉得有意思,, 就仔细想了想. 也给出了一种解决办法..:-) 问题求助,请高手指点.. 我有一个表结构, fphm,kshm 2014,00000001 2014,00000002 2014,00000003 2014,00000004 2014,00000005 2014,00000007 2014,00000008 2014,00000009 2013,00000120 2013,00000121 2013,00000122 2013,00000124 2013,00000125 (第二个字段内可能是连续的数据,可能存在断点。) 怎样能查询出来这样的结果,查询出连续的记录来。 就像下面的这样? 2014,00000001,00000005 2014,00000009,00000007 2013,00000120,00000122 2013,00000124,00000125 方法一: 引用自hmxxyy. SQL> select * from gap; ID SEQ ---------- ---------- 1 1 1 4 1 5 1 8 2 1 2 2 2 9 select res1.id, res2.seq str, res1.seq end from ( select rownum rn, c.* from ( select * from gap a where not exists ( select null from gap b where b.id = a.id and a.seq = b.seq - 1 ) order by id, seq ) c ) res1, ( select rownum rn, d.* from ( select * from gap a where not exists ( SQL 查询连续的号码段 2011年11月21日 16:29 分区 Oracle_SQL 的第 60 页 where not exists ( select null from gap b where b.id = a.id and a.seq = b.seq + 1 ) order by id, seq ) d ) res2 where res1.id = res2.id and res1.rn = res2.rn / ID STR END --------- ---------- ---------- 1 1 1 1 4 5 1 8 8 2 1 2 2 9 9 方法二: 使用lag/lead分析函数进行处理.. 楼上的方法确实挺好用就是觉得表扫描/表连接比较多, 可能数据量大了. 速度会比较慢, 当然我的这种方法 由于使用分析函数使用的比较频繁.所以排序量可能比上一种要多.. SQL> select fphm,lpad(kshm,8,'0') kshm 2 from t 3 / FPHM KSHM ---------- ---------------- 2014 00000001 2014 00000002 2014 00000003 2014 00000004 2014 00000005 2014 00000007 2014 00000008 2014 00000009 2013 00000120 2013 00000121 2013 00000122 FPHM KSHM ---------- ---------------- 2013 00000124 2013 00000125 13 rows selected. SQL> set echo on SQL> @bbb.sql SQL> select fphm,lpad(kshm,8,'0') start_kshm,lpad(prev_prev_kshm,8,'0') end_kshm 2 from ( 3 select fphm,kshm,next_kshm,prev_kshm, 4 lag(kshm,1,null) over (partition by fphm order by kshm )next_next_kshm, 5 lead(kshm,1,null) over (partition by fphm order by kshm ) prev_prev_kshm 6 from ( 7 select * 8 from ( 9 select fphm,kshm, 10 lead(kshm,1,null) over (partition by fphm order by kshm) next_kshm, 11 lag(kshm,1,null) over (partition by fphm order by kshm) prev_kshm 12 from t 13 ) 14 where ( next_kshm - kshm <> 1 or kshm - prev_kshm <> 1 ) 15 or ( next_kshm is null or prev_kshm is null ) 16 ) 17 ) 18 where next_kshm - kshm = 1 分区 Oracle_SQL 的第 61 页 18 where next_kshm - kshm = 1 19 / FPHM START_KSHM END_KSHM ---------- ---------------- ---------------- 2013 00000120 00000122 2013 00000124 00000125 2014 00000001 00000005 2014 00000007 00000009 SQL> spool off 方法三: 今天早上wildflower给了我这个答案, 顿时觉得耳目一新啊..就贴出来与大家一起共享了^_^. SQL> spool aaa.log SQL> set echo on SQL> select * from t; no rows selected SQL> select * from t; FPHM KSHM ---------- ---------- 2014 1 2014 2 2014 3 2014 4 2014 5 2014 7 2014 8 2014 9 2013 120 2013 121 2013 122 FPHM KSHM ---------- ---------- 2013 124 2013 125 13 rows selected. SQL> @bbb.sql SQL> select b.fphm,min(b.kshm),max(b.kshm) 2 from ( 3 select a.*,to_number(a.kshm-rownum) cc 4 from ( 5 select * from t order by fphm,kshm 6 ) a 7 ) b 8 group by b.fphm,b.cc 9 / FPHM MIN(B.KSHM) MAX(B.KSHM) ---------- ----------- ----------- 2013 120 122 2013 124 125 2014 1 5 2014 7 9 SQL> 转自:http://blog.itpub.net/post/5042/27936 -本文出自天天软件测试社区(http://www.365testing.com/bbs/),原文地址:http://www.365testing.com/bbs/thread-35817-1-1.html 分区 Oracle_SQL 的第 62 页 用spool+unix shell生成文本文件 比较熟悉使用ORACLE的人一般都会用spool命令来生成OS下的文本文件. 例如我们把scott.dept表生成文本文件的语句写成dept.sql,内容如下: set pages 50000; set lines 200; set trims on; set heading off; spool /oracle_backup/log/test/dept.lst; select deptno||','||dname||','||loc from scott.dept; spool off; 但是生成的文本文件头和尾里含有不需要的空行和提示信息(虚线里面是生成文本文件的详细内容): ---------------------------------------- 10,ACCOUNTING,NEW YORK 20,RESEARCH,DALLAS 30,SALES,CHICAGO 40,OPERATIONS,BOSTON 50,MARKETING,FOTTH WORTH 60,HEAD,Amercia 6 rows selected. ----------------------------------------- 我请教了kane,他写出来的del_head_tail_line.sh可以把文件头尾的行去掉,并生成新文件: #!/bin/sh #第一个参数是要处理的文件名 #第二个参数是文件头要去掉的行数 #第三个参数是文件尾要去掉的行数 #第四个参数是要输出的文件名 #created by kane@geeyo.com lines=`wc -l $1 | awk '{print $1}'` taillines=`expr $lines - $2` tail -$taillines $1 > $1.tmp headlines=`expr $taillines - $3` head -$headlines $1.tmp > $4 rm $1.tmp 我们执行下面命令,就可以把不符合要求的行去掉,生成新文件1.txt $./del_head_tail_line.sh dept.lst 1 3 1.txt 这样就解决了文本文件格式的问题. 具体应用举例: 因为工作需要,我经常要把一个小时或者一个星期变动过的数据生成文本文件上传到各个地区. 借用上面的方法, 我只需要写几个sql语句+ unix shell程序,配合OS定时就可以自动来完成这些工作: 第一步: 写好用来生成文本文件的SQL语句(假设是/oracle_backup/bin/test.sql) 提示1: 如果有几个不同的查询条件,可以把两个查询结果用union all合并起来生成一个结果集 提示2: Tab键的ascii码是char(9) 第二步: 写好UNIX OS调用test.sql的脚本gen_test.sh su - oracle -c "sqlplus username/password"<2.txt cat 2.txt>english_companyname_$CDAY.txt cat 1.txt>>english_companyname_$CDAY.txt cat 2.txt>>english_companyname_$CDAY.txt 第四步: 生成FTP传文件的脚本ftp_test.sh 提示1: /oracle_backup/log/test/log/目录下记录了上传文件前后的时间差,是上传日志 提示2: 下面例子里FTP服务器目的地址为默认的/目录,如果要传到子目录,可以在/后添加子目录名 CDAY=`date +%Y%m%d%H`;export CDAY cd /oracle_backup/log/test/ chmod 777 *.txt date>/oracle_backup/log/test/log/ftp_test_$CDAY.log ftp -i -n 192.168.0.253 <>/oracle_backup/log/test/log/ftp_test_$CDAY.log 第五步: 设定unix root用户的定时操作 Sun Solaris 文件 /var/spool/cron/crontabs/root Linux 文件 /var/spool/cron/root 例如每小时执行一次: 3 0-23 * * * /oracle_backup/bin/gen_test.sh 5 0-23 * * * /oracle_backup/bin/gen_test_file.sh 8 0-23 * * * /oracle_backup/bin/ftp_test.sh 重新启动unix OS的定时服务,使新添加的任务生效 Sun Solaris #/etc/rc2.d/S75cron stop #/etc/rc2.d/S75cron start Linux #/etc/rc.d/init.d/crond restart 注意事项: unix shell脚本都应该有755的可执行权限. 超过50000行的文件,会在每50000行的分隔处多出一个空行; 如果这样的格式不合要求,可以再写一个unix shell重写文件,遇到空行时去掉空行. 结束语: 这里看似简单的方法却能实现很多复杂的功能, 希望对大家有所启发. 源文档 spool的简单使用 spool是oracle sqlplus提供的一个控制命令。可以利用spool和select语句的组合动态的生成一些sql脚本或者一些数据。 sqlplus中可以执行的语句分类大概有两种,一种是最常见的dml,dcl,ddl。这些语句在执行以后会存放在一个sqlplus的buffer中,而且每次只会存放最近执行的一 条语句,以方便再次执行。另外的一种就是称为‚命令语句‛了,诸如set linesize之类的,这些语句往往是处理终端显示格式的一些命令,调用后语句不会存放入 sqlplus buffer中。 spool语句就是命令语句的其中之一。在调用spool语句后,其后的select语句所产生的所有终端显示,包括select语句本身和一些行数信息都会写入spool语句定义的 文件中。在语句执行完后,一定要调用spool off命令,不然输出的信息只是在数据缓存中,不会写入文件。 下面举两个利用spool可以完成的功能: 分区 Oracle_SQL 的第 64 页 下面举两个利用spool可以完成的功能: 1 利用spool动态生成删除某用户模式下面所有表的sql脚本: a. 创建gen_drop_table.sql文件,包含如下语句: set echo off set feedback off set newpage none set linesize 500 set verify off set pagesize 0 set term off set trims on set linesize 600 set heading off set timing off set numwidth 38 SPOOL c:\drop_table.sql SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables; SPOOL OFF b. 以SCOTT用户登录数据库 SQLPLUS > @ …..\gen_dorp_table.sql c. 在c盘根目录下会生成文件drop_table.sql文件,包含删除所有表的语句,如下所示: DROP TABLE DEPT; DROP TABLE EMP; DROP TABLE PARENT; DROP TABLE STAT_VENDER_TEMP; DROP TABLE TABLE_FORUM; d. 在scott用户下运行dorp_table.sql文件,删除scott用户下所有的表。 SQLPLUS > @ c:\dorp_table.sql 为了防止spool打出的文件中包含sql语句和行数这些不便的信息,可以通过set来关闭这些信息在终端上的显示,然后再执行spool。 2 利用spool将select语句选出的纪录数据存放入指定的文件(unix下,写成shell脚本): sqlplus 登陆名/密码@数据库名< SPOOL 脚本封装 oracle的spool工具可以将数据导出为文本格式,有很强的功能和实用性,如果能灵活应用,能带来很多好处和便捷。 下面是我写的两个示例脚本,使用着可以直接拷贝使用,如有更好的改进,请补充。 在unix/linux下,vi sqlexport.sh,下面是 sqlexport.sh的文件内容: #!/bin/sh if [ $# -ne 3 ]; then echo "usage:$0 user/passwd@dbstr sqlfile exportfile"; exit 1; fi 分区 Oracle_SQL 的第 65 页 fi dbstr=$1; sqlfile=$2; outfile=$3; echo $dbstr; sqlplus $dbstr< 0 loop i_index := instr(field_value1,';'); if i_index = 0 and str_len1 > 0 then i_index := str_len1; end if; field_value2 := substr(field_value1, 1, i_index-1); field_value2 := trim(field_value2); str_len2 := length(field_value2); field_value1 := substr(field_value1, i_index+1, str_len1-i_index); str_len1 := length(field_value1); if str_len2 > 0 then j_index := instr(field_value2,'-'); --dbms_output.put_line('field_value2=' || field_value2 || ',j_index=' || to_char(j_index) || ',str_len2=' || to_char(str_len2)) ; if j_index = 0 then start_value := to_number(field_value2); end_value := to_number(field_value2); else start_value := to_number( substr(field_value2,1,j_index-1) ); end_value := to_number( substr(field_value2,j_index+1,str_len2-j_index) ); end if; --循环,将分解后的数据插入临时表tmp loop --dbms_output.put_line(to_char(start_value)); --sql_text := 'insert into tmp(misdn,cnn_num) values(''' || to_char(rec.bill_id) || ''',''' || to_char(start_value) || ''')'; --dbms_output.put_line(sql_text); --execute immediate sql_text; insert into crc_num_rela(bill_id,crc_num,city_id,bill_month,rec_status,create_date) values(rec.bill_id,start_value,rec.city_id,bill_month,1,sysdate); start_value := start_value + 1; EXIT WHEN start_value > end_value; end loop; end if; end loop; end loop; commit; end; / exit; EOF unlink prepare.sql 分区 Oracle_SQL 的第 67 页 unlink prepare.sql cat<>prepare.sql set term off set heading off set pages 0 set feedback off set trimout on set trimspool on spool $out_file select bill_id||',' || crc_num ||',' || to_char(city_id) ||','|| to_char(bill_month) from crc_num_rela where rec_status=1; spool off quit; EOF #用spool生成文件 sqlplus bboss/bboss@cfcs1a @prepare.sql #如果正确地生成了文件,修改名字为要求的文件名 if [ -f "$out_file" ]; then mv $out_file $dest_file fi unlink prepare.sql #生成ftp上传脚本 unlink ftp.script cat<>ftp.script open 10.70.9.29 user chenli chenli put $dest_file EOF #运行ftp上传文件脚本 cat ftp.script | ftp -n unlink ftp.script 源文档 分区 Oracle_SQL 的第 68 页 Oracle Userenv Oracle中USERENV和SYS_CONTEXT总结时间:2009-11-03 19:12:25来源:网络 作者:未知 点击:713次 Oracle中USERENV和SYS_CONTEXT用来返回当前session的信息,其中,userenv是为了保持向下兼容的遗留函数,推荐使用 sys_context函数调用userenv命名空间来获取相关信息。 Oracle中USERENV和SYS_CONTEXT用来返回当前session的信息,其中,userenv是为了保持向下兼容的遗留函数,推荐使用 sys_context函数调用userenv命名空间来获取相关信息。 1、 USERENV(OPTION) 返回当前的会话信息. OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE. OPTION='LANGUAGE'返回数据库的字符集. OPTION='SESSIONID'为当前会话标识符. OPTION='ENTRYID'返回可审计的会话标识符. OPTION='LANG'返回会话语言名称的ISO简记. OPTION='INSTANCE'返回当前的实例. OPTION='terminal'返回当前计算机名 SELECT USERENV('LANGUAGE') FROM DUAL; 2、sys_context select SYS_CONTEXT('USERENV','TERMINAL') terminal, SYS_CONTEXT('USERENV','LANGUAGE') language, SYS_CONTEXT('USERENV','SESSIONID') sessionid, SYS_CONTEXT('USERENV','INSTANCE') instance, SYS_CONTEXT('USERENV','ENTRYID') entryid, SYS_CONTEXT('USERENV','ISDBA') isdba, SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, SYS_CONTEXT('USERENV','CURRENT_USER') current_user, SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, SYS_CONTEXT('USERENV','SESSION_USER') session_user, SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, SYS_CONTEXT('USERENV','DB_NAME') db_name, SYS_CONTEXT('USERENV','HOST') host, SYS_CONTEXT('USERENV','OS_USER') os_user, SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data from dual 本篇文章来源于:开发学院 http://edu.codepub.com 原文链接:http://edu.codepub.com/2009/1103/17303.php Oracle Userenv 2011年4月14日 10:26 分区 Oracle_SQL 的第 69 页 Oracle DDL,DML,DCL,TCL 基础概念 DDL CREATE - to create objects in the database • ALTER - alters the structure of the database • DROP - delete objects from the database • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed • COMMENT - add comments to the data dictionary • RENAME - rename an object • Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: DML SELECT - retrieve data from the a database • INSERT - insert data into a table • UPDATE - updates existing data within a table • DELETE - deletes all records from a table, the space for the records remain • MERGE - UPSERT operation (insert or update) • CALL - call a PL/SQL or Java subprogram • EXPLAIN PLAN - explain access path to data • LOCK TABLE - control concurrency • Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: DCL GRANT - gives user's access privileges to database • REVOKE - withdraw access privileges given with the GRANT command • Data Control Language (DCL) statements. Some examples: TCL COMMIT - save work done • SAVEPOINT - identify a point in a transaction to which you can later roll back • ROLLBACK - restore database to original since the last COMMIT • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use • Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. 源文档 Oracle DDL/DML/DCL 2011年4月8日 16:19 分区 Oracle_SQL 的第 70 页 Oracle 正则表达式 Oracle Database 10g 中的正规表达式特性是一个用于处理文本数据的强大工具 Oracle Database 10g 的一个新特性大大提高了您搜索和处理字符数据的能力。这个特性就是正规表达式,是一种用来描述 文本模式的表示方法。很久以来它已在许多编程语言和大量 UNIX 实用工具中出现过了。 Oracle 的正规表达式的实施是以各种 SQL 函数和一个 WHERE 子句操作符的形式出现的。如果您不熟悉正规表达式,那么这篇文 章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能。已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语 言的环境中应用这种功能。 什么是正规表达式? 正规表达式由一个或多个字符型文字和/或元字符组成。在最简单的格式下,正规表达式仅由字符文字组成,如正规表达式 cat。它被 读作字母 c,接着是字母 a 和 t,这种模式匹配 cat、location 和 catalog 之类的字符串。元字符提供算法来确定 Oracle 如何处理组 成一个正规表达式的字符。当您了解了各种元字符的含义时,您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。 验证数据、识别重复关键字的出现、检测不必要的空格,或分析字符串只是正规表达式的许多应用中的一部分。您可以用它们来验证 电话号码、邮政编码、电子邮件地址、社会安全号码、IP 地址、文件名和路径名等的格式。此外,您可以查找如 HTML 标记、数 字、日期之类的模式,或任意文本数据中符合任意模式的任何事物,并用其它的模式来替换它们。 用 Oracle Database 10g 使用正规表达式 您可以使用最新引进的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函数来发挥正规表达式的作用。您将体会到这个新的功能如何对 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函数进行了补充。 实际上,它们类似于已有的操作符,但现在增加了强大的模式匹配功能。被搜索的数据可以是简单的字符串或是存储在数据库字符列 中的大量文本。正规表达式让您能够以一种您以前从未想过的方式来搜索、替换和验证数据,并提供高度的灵活性。 正规表达式的基本例子 在使用这个新功能之前,您需要了解一些元字符的含义。句号 (.) 匹配一个正规表达式中的任意字符(除了换行符)。例如,正规表达 式 a.b 匹配的字符串中首先包含字母 a,接着是其它任意单个字符(除了换行符),再接着是字母 b。字符串 axb、xaybx 和 abba 都与之匹配,因为在字符串中隐藏了这种模式。如果您想要精确地匹配以 a 开头和以 b 结尾的一条三个字母的字符串,则您必须对正 规表达式进行定位。脱字符号 (^) 元字符指示一行的开始,而美元符号 ($) 指示一行的结尾(参见表1:附表见第4页)。因此, 正规 表达式 ^a.b$ 匹配字符串 aab、abb 或 axb。将这种方式与 LIKE 操作符提供的类似的模式匹配 a_b 相比较,其中 (_) 是单字符通 配符。 默认情况下,一个正规表达式中的一个单独的字符或字符列表只匹配一次。为了指示在一个正规表达式中多次出现的一个字符,您可 以使用一个量词,它也被称为重复操作符。.如果您想要得到从字母 a 开始并以字母 b 结束的匹配模式,则您的正规表达式看起来像 这样:^a.*b$。* 元字符重复前面的元字符 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等价的模式是 a%b,其中用百分号 (%) 来指示任意字符出现零次、一次或多次。 表 2 给出了重复操作符的完整列表。注意它包含了特殊的重复选项,它们实现了比现有的 LIKE 通配符更大的灵活性。如果您用圆括 号括住一个表达式,这将有效地创建一个可以重复一定次数的子表达式。例如,正规表达式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。 Oracle 的正规表达式实施支持 POSIX (可移植操作系统接口)字符类,参见表 3 中列出的内容。这意味着您要查找的字符类型可以 非常特别。假设您要编写一条仅查找非字母字符的 LIKE 条件 — 作为结果的 WHERE 子句可能不经意就会变得非常复杂。 POSIX 字符类必须包含在一个由方括号 ([]) 指示的字符列表中。例如,正规表达式 [[:lower:]] 匹配一个小写字母字符,而 [[:lower:]] {5} 匹配五个连续的小写字母字符。 除 POSIX 字符类之外,您可以将单独的字符放在一个字符列表中。例如,正规表达式 ^ab[cd]ef$ 匹配字符串 abcef 和 abdef。必须 选择 c 或 d。 除脱字符 (^) 和连字符 (-) 之外,字符列表中的大多数元字符被认为是文字。正规表达式看起来很复杂,这是因为一些元字符具有随上 下文环境而定的多重含义。^ 就是这样一种元字符。如果您用它作为一个字符列表的第一个字符,它代表一个字符列表的非。因此, [^[:digit:]] 查找包含了任意非数字字符的模式,而 ^[[:digit:]] 查找以数字开始的匹配模式。连字符 (-) 指示一个范围,正规表达式 [a- m] 匹配字母 a 到字母 m 之间的任意字母。但如果它是一个字符行中的第一个字符(如在 [-afg] 中),则它就代表连字符。 之前的一个例子介绍了使用圆括号来创建一个子表达式;它们允许您通过输入更替元字符来输入可更替的选项,这些元字符由竖线 (|) 分开。 Oracle 正则表达式 2011年4月1日 16:17 分区 Oracle_SQL 的第 71 页 分开。 例如,正规表达式 t(a|e|i)n 允许字母 t 和 n 之间的三种可能的字符更替。匹配模式包括如 tan、ten、tin 和 Pakistan 之类的字, 但不包括 teen、mountain 或 tune。作为另一种选择,正规表达式 t(a|e|i)n 也可以表示为一个字符列表 t[aei]n。表 4 汇总了这些 元字符。虽然存在更多的元字符,但这个简明的概述足够用来理解这篇文章使用的正规表达式。 REGEXP_LIKE 操作符 REGEXP_LIKE 操作符向您介绍在 Oracle 数据库中使用时的正规表达式功能。表 5 列出了 REGEXP_LIKE 的语法。 下面的 SQL 查询的 WHERE 子句显示了 REGEXP_LIKE 操作符,它在 ZIP 列中搜索满足正规表达式 [^[:digit:]] 的模式。它将检索 ZIPCODE 表中的那些 ZIP 列值包含了任意非数字字符的行。 SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]') ZIP ----- ab123 123xy 007ab abcxy 这个正规表达式的例子仅由元字符组成,更具体来讲是被冒号和方括号分隔的 POSIX 字符类 digit。第二组方括号(如 [^[:digit:]] 中 所示)包括了一个字符类列表。如前文所述,需要这样做是因为您只可以将 POSIX 字符类用于构建一个字符列表。 REGEXP_INSTR 函数 这个函数返回一个模式的起始位臵,因此它的功能非常类似于 INSTR 函数。新的 REGEXP_INSTR 函数的语法在表 6 中给出。这两 个函数之间的主要区别是,REGEXP_INSTR 让您指定一种模式,而不是一个特定的搜索字符串;因而它提供了更多的功能。接下来 的示例使用 REGEXP_INSTR 来返回字符串 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 中的五位邮政编码模式的起 始位臵。如果正规表达式被写为 [[:digit:]]{5},则您将得到门牌号的起始位臵而不是邮政编码的,因为 10045 是第一次出现五个连续 数字。因此,您必须将表达式定位到该行的末尾,正如 $ 元字符所示,该函数将显示邮政编码的起始位臵,而不管门牌号的数字个 数。 SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual RX_INSTR ---------- 45 编写更复杂的模式 让我们在前一个例子的邮政编码模式上展开,以便包含一个可选的四位数字模式。您的模式现在可能看起来像这样:[[:digit:]]{5}(- [[:digit:]]{4})?$。如果您的源字符串以 5 位邮政编码或 5 位 + 4 位邮政编码的格式结束,则您将能够显示该模式的起始位臵。 SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual STARTS_AT ---------- 44 在这个示例中,括弧里的子表达式 (-[[:digit:]]{4}) 将按 ? 重复操作符的指示重复零次或一次。此外,企图用传统的 SQL 函数来实现相 同的结果甚至对 SQL 专家也是一个挑战。为了更好地说明这个正规表达式示例的不同组成部分,表 7 包含了一个对单个文字和元字 符的描述。 REGEXP_SUBSTR 函数 类似于 SUBSTR 函数的 REGEXP_SUBSTR 函数用来提取一个字符串的一部分。表 8 显示了这个新函数的语法。在下面的示例 中,匹配模式 [^,]* 的字符串将被返回。该正规表达式搜索其后紧跟着空格的一个逗号;然后按 [^,]* 的指示搜索零个或更多个不是逗 号的字符,最后查找另一个逗号。这种模式看起来有点像一个用逗号分隔的值字符串。 SELECT REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,') FROM dual REGEXP_SUBSTR('FIR ------------------ , second field , 分区 Oracle_SQL 的第 72 页 REGEXP_REPLACE 函数 让我们首先看一下传统的 REPLACE SQL 函数,它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格, 您希望用单个空格来替换它们。利用 REPLACE 函数,您需要准确地列出您要替换多少个空格。然而,多余空格的数目在正文的各处 可能不是相同的。下面的示例在 Joe 和 Smith 之间有三个空格。REPLACE 函数的参数指定要用一个空格来替换两个空格。在这种 情况下,结果在原来的字符串的 Joe 和 Smith 之间留下了一个额外的空格。 SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual REPLACE --------- Joe Smith REGEXP_REPLACE 函数把替换功能向前推进了一步,其语法在表 9 中列出。以下查询用单个空格替换了任意两个或更多的空 格。( ) 子表达式包含了单个空格,它可以按 {2,} 的指示重复两次或更多次。 SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual RX_REPLACE ---------- Joe Smith 后向引用 正则表达式的一个有用的特性是能够存储子表达式供以后重用;这也被称为后向引用(在表 10 中对其进行了概述)。它允许复杂的 替换功能,如在新的位臵上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行 编号,并利用 \digit 符号进行访问,其中 digit 是 1 到 9 之间的一个数字,它匹配第 digit 个子表达式,子表达式用一组圆括号来显 示。 接下来的例子显示了通过按编号引用各个子表达式将姓名 Ellen Hildi Smith 转变为 Smith, Ellen Hildi。 SELECT REGEXP_REPLACE( 'Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual REGEXP_REPLACE('EL ------------------ Smith, Ellen Hildi 该 SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符 (.),并紧跟着 * 元字符,表示 任何字符(除换行符之外)都必须匹配零次或更多次。空格将各个子表达式分开,空格也必须匹配。圆括号创建获取值的子表达式, 并且可以用 \digit 来引用。第一个子表达式被赋值为 \1 ,第二个 \2,以此类推。这些后向引用被用在这个函数的最后一个参数 (\3, \1 \2) 中,这个函数有效地返回了替换子字符串,并按期望的格式来排列它们(包括逗号和空格)。表 11 详细说明了该正则表 达式的各个组成部分。 后向引用对替换、格式化和代替值非常有用,并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用 REGEP_SUBSTR 函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词 is 的子字符串。 SELECT REGEXP_SUBSTR( 'The final test is is the implementation', '([[:alnum:]]+)([[:space:]]+)\1') AS substr FROM dual SUBSTR ------ is is 匹配参数选项 您可能已经注意到了正则表达式操作符和函数包含一个可选的匹配参数。这个参数控制是否区分大小写、换行符的匹配和保留多行输 入。 正则表达式的实际应用 您不仅可以在队列中使用正则表达式,还可以在使用 SQL 操作符或函数的任何地方(比如说在 PL/SQL 语言中)使用正则表达式。 您可以编写利用正则表达式功能的触发器,以验证、生成或提取值。 接下来的例子演示了您如何能够在一次列检查约束条件中应用 REGEXP_LIKE 操作符来进行数据验证。它在插入或更新时检验正确 的社会保险号码格式。如 123-45-6789 和 123456789 之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必 分区 Oracle_SQL 的第 73 页 的社会保险号码格式。如 123-45-6789 和 123456789 之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必 须以三个数字开始,紧跟着一个连字符,再加两个数字和一个连字符,最后又是四个数字。另一种表达式只允许 9 个连续的数字。竖 线符号 (|) 将各个选项分开。 ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 由 ^ 和 $ 指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格,除非您希望格式如 此并相应地进行匹配。表 12 说明了该正则表达式示例的各个组成部分。 将正则表达式与现有的功能进行比较 正则表达式有几个优点优于常见的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函数的。这些传统的 SQL 函数不便于进行模式 匹配。只有 LIKE 操作符通过使用 % 和 _ 字符匹配,但 LIKE 不支持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字 符类等等。此外,新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览,以 及您如何能够在您的应用程序中使用它们。 实实在在地丰富您的工具包 因为正则表达式有助于解决复杂的问题,所以它们是非常强大的。正则表达式的一些功能难于用传统的 SQL 函数来仿效。当您了解 了这种稍显神秘的语言的基础构建程序块时,正则表达式将成为您的工具包的不可缺少的一部分(不仅在 SQL 环境下也在其它的编 程语言环境下)。为了使您的各个模式正确,虽然尝试和错误有时是必须的,但正则表达式的简洁和强大是不容臵疑的。 Alice Rischert (ar280@yahoo.com) 是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的主席。她编写了 Oracle SQL 交互手册 第 2 版(Prentice Hall,2002)和即将推出的 Oracle SQL 示例 (Prentice Hall,2003)。Rischert 拥 有超过 15 年的经验在财富 100 强公司内担任数据库设计师、DBA 和项目主管,并且她自从 Oracle version 5 起就一直使用 Oracle 产品。 附表见下页: 表 1:定位元字符 元字符 说明 ^ 使表达式定位至一行的开头 $ 使表达式定位至一行的末尾 表 2:量词或重复操作符 量词 说明 * 匹配 0 次或更多次 ? 匹配 0 次或 1 次 + 匹配 1 次或更多次 {m} 正好匹配 m 次 {m,} 至少匹配 m 次 {m, n} 至少匹配 m 次但不超过 n 次 表 3:预定义的 POSIX 字符类 字符类 说明 [:alpha:] 字母字符 [:lower:] 小写字母字符 [:upper:] 大写字母字符 [:digit:] 数字 [:alnum:] 字母数字字符 [:space:] 空白字符(禁止打印),如回车符、换行符、竖直制表符和换页符 [:punct:] 标点字符 [:cntrl:] 控制字符(禁止打印) [:print:] 可打印字符 表 4:表达式的替换匹配和分组 元字符 说明 | 替换 分隔替换选项,通常与分组操作符 () 一起使用 ( ) 分组 将子表达式分组为一个替换单元、量词单元或后向引用单元(参见‚后向引用‛部分) 分区 Oracle_SQL 的第 74 页 [char] 字符列表 表示一个字符列表;一个字符列表中的大多数元字符(除字符类、^ 和 - 元字符之外)被理解为文字 表 5:REGEXP_LIKE 操作符 语法 说明 REGEXP_LIKE(so urce_string, pattern [, match_parameter] ) source_string 支持字符数据类型(CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB, 但不包括 LONG)。pattern 参数是正则表达式的另一个名称。match_parameter 允许可选的参数(如处理 换行符、保留多行格式化以及提供对区分大小写的控制)。 表 6:REGEXP_INSTR 函数 语法 说明 REGEXP_INSTR(s ource_string, pattern [, start_position [, occurrence [, return_option [, match_parameter] ]]]) 该函数查找 pattern ,并返回该模式的第一个位臵。您可以随意指定您想要开始搜索 的 start_position。occurrence 参数默认为 1,除非您指定您要查找接下来出现的一个模 式。return_option 的默认值为 0,它返回该模式的起始位臵;值为 1 则返回符合匹配条件的下一个字符的起 始位臵。 表 7: 5 位数字加 4 位邮政编码表达式的说明 语法 说明 必须匹配的空白 [:digit:] POSIX 数字类 ] 字符列表的结尾 {5} 字符列表正好重复出现 5 次 ( 子表达式的开头 - 一个文字连字符,因为它不是一个字符列表内的范围元字符 [ 字符列表的开头 [:digit:] POSIX [:digit:]类 [ 字符列表的开头 ] 字符列表的结尾 {4} 字符列表正好重复出现 4 次 ) 结束圆括号,结束子表达式 ?? 量词匹配分组的子表达式 0 或 1 次,从而使得 4 位代码可选 $ 定位元字符,指示行尾 表 8:REGEXP_SUBSTR 函数 语法 说明 REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) REGEXP_SUBSTR 函数返回匹配模式的子字符串。 表 9: REGEXP_REPLACE 函数 语法 说明 REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) 该函数用一个指定的 replace_string 来替换匹配的模式,从而允许复杂的‚搜索并替 换‛操作。 表 10:后向引用元字符 元字符 说明 \digit 反斜线 紧跟着一个 1 到 9 之间的数字,反斜线匹配之前的用括号括起来的第 digit 个子表达式。 (注意:反斜线在正则表达式中有另一种意义,取决于上下文,它还可能表示 Escape 字符。 表 11:模式交换正则表达式的说明 分区 Oracle_SQL 的第 75 页 表 11:模式交换正则表达式的说明 正则表达式项目 说明 ( 第一个子表达式的开头 . 匹配除换行符之外的任意单字符 * 重复操作符,匹配之前的 . 元字符 0 到 n 次 ) 第一个子表达式的结尾;匹配结果在 \1 中获取(在这个例子中,结果为 Ellen。) 必须存在的空白 ( 第二个子表达式的开头 . 匹配除换行符之外的任意单个字符 * 重复操作符,匹配之前的 . 元字符 0 到 n 次 ) 第二个子表达式的结尾;匹配结果在 \2 中获取(在这个例子中,结果为 Hildi。) 空白 ( 第三个子表达式的开头 . 匹配除换行符之外的任意单字符 * 重复操作符,匹配之前的 . 元字符 0 到 n 次 ) 第三个子表达式的结尾;匹配结果在 \3 中获取(在这个例子中,结果为 Smith。) 表 12:社会保险号码正则表达式的说明 正则表达式项目 说明 ^ 行首字符(正则表达式在匹配之前不能有任何前导字符。) ( 开始子表达式并列出用 | 元字符分开的可替换选项 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {3} 字符列表正好重复出现 3 次 - 连字符 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {2} 字符列表正好重复出现 2 次 - 另一个连字符 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {4} 字符列表正好重复出现 4 次 | 替换元字符;结束第一个选项并开始下一个替换表达式 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {9} 字符列表正好重复出现 9 次 ) 结束圆括号,结束用于替换的子表达式组 $ 定位元字符,指示行尾;没有额外的字符能够符合模式 源文档 分区 Oracle_SQL 的第 76 页 分区 Oracle_SQL 的第 77 页 Oracle 表连接运行原理 1、 Oracle优化器会自动选择以下三种方式的一种运行表连接,但在数据环境上配合强化选择合适的方式或强制使用某种方式是SQL优化的需要: NESTED LOOP 对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个 表里面查找,没有索引一般就不会是 nested loops。 一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nested loop。如果驱动表返回记录太多,就不 适合nested loops了。如果连接字段没有索引,则适合走hash join,因为不需要索引。 可用ordered提示来改变优化器默认的驱动表,可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。 HASH JOIN hash join是优化器做大数据集连接时的常用方式。优化器扫描小表(或数据源),利用连接键(也就是根据连接字段计算hash 值)在内存中 建立hash表,然后扫描大表,每读到一条记录就来探测hash表一次,找出与hash表匹配的行。 当小表可以全部放入内存中,其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存,这时优化器会将它分割成若干不同的分 区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做 hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。 至于两个表都进行分区,其好处是可以使用parallel query,就是多个进程同时对不同的分区进行join,然后再合并。但是复杂。 使用hash join时,HASH_AREA_SIZE初始化参数必须足够的大,如果是9i,Oracle建议使用SQL工作区自动管理,设臵 WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可。 以下条件下hash join可能有优势: 两个巨大的表之间的连接。 在一个巨大的表和一个小表之间的连接。 可用ordered提示来改变优化默认的驱动表,可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。 SORT MERGE JOIN sort merge join的操作通常分三步:对连接的每个表做table access full;对table access full的结果进行排序;进行merge join对排序结果 进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下,9i开始已经很少出现了,因为其排序成本高,大多为hash join替代了。 通常情况下hash join的效果都比sort merge join要好,然而如果行源已经被排过序,在执行sort merge join时不需要再排序了,这时sort merge join的性能会优于hash join。 在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,sort merge join会比nested loops性能更佳。 可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。 2、 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处 理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接 它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表 中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法) select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN Oracle 表连接运行原理 2011年3月1日 15:53 分区 Oracle_SQL 的第 78 页 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 2.WHERE子句中的连接顺序. ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录 的条件必须写在WHERE子句的末尾. 例如: (低效,执行时间156.3秒) SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间10.6秒) SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’; 源文档 分区 Oracle_SQL 的第 79 页 简单实用SQL脚本 行列互转 create table test(id int,name varchar(20),quarter int,profile int) ; insert into test values(1,'a',1,1000); insert into test values(1,'a',2,2000); insert into test values(1,'a',3,4000); insert into test values(1,'a',4,5000); insert into test values(2,'b',1,3000); insert into test values(2,'b',2,3500); insert into test values(2,'b',3,4200); insert into test values(2,'b',4,5500); select * from test; --行转列 select id,name, [1] as "一季度", [2] as "二季度", [3] as "三季度", [4] as "四季度", [5] as "5" from test pivot ( sum(profile) for quarter in ([1],[2],[3],[4],[5]) ) as pvt create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int) insert into test2 values(1,'a',1000,2000,4000,5000) insert into test2 values(2,'b',3000,3500,4200,5500) select * from test2 --列转行 select id,name,quarter,profile from test2 unpivot ( profile for quarter in ([Q1],[Q2],[Q3],[Q4]) ) as unpvt 复制代码 --例子1: update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'**') where ID = 1 --例子2: update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'********') where ID = 1 --例子3: update tbPersonalInfo set Email = replace(Email,'chinamobile','******') where ID = 1 复制代码 sql替换字符串 substring replace SQL查询一个表内相同纪录 having SQL简单实用脚本 2012年3月27日 17:47 分区 Oracle_SQL 的第 80 页 如果一个ID可以区分的话,可以这么写 select * from 表 where ID in ( select ID from 表 group by ID having sum(1)>1) 复制代码 如果几个ID才能区分的话,可以这么写 select * from 表 where ID1+ID2+ID3 in (select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1) 复制代码 其他回答:数据表是zy_bho,想找出ZYH字段名相同的记录 --方法1: SELECT *FROM zy_bho a WHERE EXISTS (SELECT 1 FROM zy_bho WHERE [PK] <> a.[PK] AND ZYH = a.ZYH) --方法2: select a.* from zy_bho a join zy_bho b on (a.[pk]<>b.[pk] and a.zyh=b.zyh) --方法3: select * from zy_bbo where zyh in (select zyh from zy_bbo group by zyh having count(zyh)>1) --其中pk是主键或是 unique的字段。 复制代码 SQL查询一个表内相同纪录 having Select DeptName=O.OUName, '9G'=Sum(Case When PersonalGrade=9 Then 1 Else 0 End), '8G'=Sum(Case When PersonalGrade=8 Then 1 Else 0 End), '7G4'=Sum(Case When PersonalGrade=7 AND JobGrade =4 Then 1 Else 0 End), '7G3'=Sum(Case When PersonalGrade=7 AND JobGrade =3 Then 1 Else 0 End), '6G'=Sum(Case When PersonalGrade=6 Then 1 Else 0 End), '5G3'=Sum(Case When PersonalGrade=5 AND JobGrade =3 Then 1 Else 0 End), '5G2'=Sum(Case When PersonalGrade=5 AND JobGrade =2 Then 1 Else 0 End), '4G'=Sum(Case When PersonalGrade=4 Then 1 Else 0 End), '3G2'=Sum(Case When PersonalGrade=3 AND JobGrade =2 Then 1 Else 0 End), '3G1'=Sum(Case When PersonalGrade=3 AND JobGrade =1 Then 1 Else 0 End), '2G'=Sum(Case When PersonalGrade=2 Then 1 Else 0 End), '1G'=Sum(Case When PersonalGrade=1 Then 1 Else 0 End), --' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End) 复制代码 把多行SQL数据变成一条多列数据,即新增列 表复制 insert into PhoneChange_Num ([IMSI],Num) SELECT [IMSI] ,count([IMEI]) as num FROM [Test].[dbo].[PhoneChange] group by [IMSI] order by num desc 复制代码 语法1:Insert INTO table(field1,field2,...) values(value1,value2,...) 语法2:Insert into Table2(field1,field2,...) select value1,value2,... from Table1(要求目标表Table2必须存在,由于目标表Table2已经存 在,所以我们除了插入源表Table1的字段外,还可以插入常量。) 语法3:SELECT vale1, value2 into Table2 from Table1(要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中 指定字段数据复制到Table2中。) 语法4:使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】,那么在大数据表的复制就会有问题?因为复制到一定 程度就不再动了,内存爆了?它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道 了。 利用带关联子查询Update语句更新数据 --方法1: Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null --方法2: update A set newqiantity=B.qiantity from A,B 分区 Oracle_SQL 的第 81 页 from A,B where A.bnum=B.bnum --方法3: update (select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum=B.bnum) AS C set C.newqiantity = C.qiantity where C.bnum =XX 复制代码 连接远程服务器 --方法1: select * from openrowset('SQLOLEDB','server=192.168.0.67;uid=sa;pwd=password','SELECT * FROM BCM2.dbo.tbAppl') --方法2: select * from openrowset('SQLOLEDB','192.168.0.67';'sa';'password','SELECT * FROM BCM2.dbo.tbAppl') 复制代码 Date 和 Time 样式 不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3) - 0 或 100 (1,2) 默 认 mon dd yyyy hh:miAM(或 PM) 1 101 美 国 mm/dd/yyyy 2 102 ANSI yy.mm.dd 3 103 英 国/法国 dd/mm/yyyy 4 104 德 国 dd.mm.yy 5 105 意 大利 dd-mm-yy 6 106(1) - dd mon yy 7 107(1) - mon dd, yy 8 108 - hh:mi:ss - 9 或 109 (1,2) 默 认设臵 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM) 10 110 美 国 mm-dd-yy 11 111 日 本 yy/mm/dd 12 112 ISO yymmdd yyyymmdd - 13 或 113 (1,2) 欧 洲默认设臵 + 毫秒 dd mon yyyy hh:mi:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h) - 20 或 120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24h) - 21 或 121 (2) ODBC 规范(带毫秒) yyyy-mm-dd hh:mi:ss.mmm(24h) - 126 (4) ISO8601 yyyy- mm-ddThh:mi:ss.mmm(无空格) - 127(6, 7) 带时区 Z 的 ISO8601。 yyyy-mm-ddThh:mi:ss.mmmZ (无 空格) - 130 (1,2) 回历 (5) dd mon yyyy hh:mi:ss:mmmAM - 131 (2) 回历 (5) dd/mm/yy hh:mi:ss:mmmAM --语句及查询结果: SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06 SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16 SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06 SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06 SELECT CONVERT(varchar(100), GETDATE(), 5): 16-05-06 SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06 SELECT CONVERT(varchar(100), GETDATE(), 7): 05 16, 06 SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46 SELECT CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06 SELECT CONVERT(varchar(100), GETDATE(), 11): 06/05/16 SELECT CONVERT(varchar(100), GETDATE(), 12): 060516 分区 Oracle_SQL 的第 82 页 SELECT CONVERT(varchar(100), GETDATE(), 12): 060516 SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937 SELECT CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967 SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47 SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157 SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16 SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47 SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250 SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM SELECT CONVERT(varchar(100), GETDATE(), 101): 05/16/2006 SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16 SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006 SELECT CONVERT(varchar(100), GETDATE(), 104): 16.05.2006 SELECT CONVERT(varchar(100), GETDATE(), 105): 16-05-2006 SELECT CONVERT(varchar(100), GETDATE(), 106): 16 05 2006 SELECT CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006 SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49 SELECT CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM SELECT CONVERT(varchar(100), GETDATE(), 110): 05-16-2006 SELECT CONVERT(varchar(100), GETDATE(), 111): 2006/05/16 SELECT CONVERT(varchar(100), GETDATE(), 112): 20060516 SELECT CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513 SELECT CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547 SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49 SELECT CONVERT(varchar(100), GETDATE(), 121): 2006 复制代码 对上面进行动态生成字符串: declare @sql1 nvarchar(200),@sql2 nvarchar(200) declare @count nvarchar(100); set @sql1 = 'SELECT CONVERT(varchar(100), GETDATE(), 0)' set @sql2 = 'SELECT @count = CONVERT(varchar(100), GETDATE(), 0)' exec sp_executesql @sql2,N'@count nvarchar(50) out',@count out print @sql1 +':'+ @count 复制代码 --SQL Server 仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。 DECLARE @myval decimal (5, 2) SET @myval = 193.57 SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5)) -- Or, using CONVERT SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval)) --输出193.57000 --输出193.57000 复制代码 --bigint数据类型的字段截取(其它类型也一样) select substring(CONVERT(varchar(15),字段名),11,9) from 表名 select substring(cast(字段名 as varchar(50),6,9)) from 表名 复制代码 SQL中的相除 --SQL中的相除 SELECT CASE WHEN ISNULL(A+B,0)<>0 THEN LTRIM(CONVERT(DEC(18,2),A*100.0/(A+B)))+'%' ELSE '' END AS '百分数' FROM TB 复制代码 --百分比的不同格式 select LTRIM(CONVERT(DEC(18,2),42*100.0/96))+'%' AS '百分数string' ,--DEC=decimal CONVERT(decimal(10,2),42*100.0/96) AS '百分数dec', --100 与 100.0是不一样的 CONVERT(decimal(10,2),42*100/96) AS '没有保留到小数点' --from Tb 分区 Oracle_SQL 的第 83 页 --from Tb order by 百分数dec desc --43.75% 43.75 43.00 --方法二: Select (Convert(varchar(50),Round(42*100.0/96,3))+'%') as 百分比 --from A --43.750000% 复制代码 四舍五入 /*ROUND ( numeric_expression , length [ ,function ] ) function 必须为 tinyint、smallint 或 int。 如果省略 function 或其值为 0(默认值),则将舍入 numeric_expression。 如果指定了0以外的值,则将截断 numeric_expression。*/ SELECT ROUND(150.45648, 2);--保留小数点后两位,需要四舍五入 SELECT ROUND(150.45648, 2, 0);--保留小数点后两位,0为默认值,表示进行四舍五入 SELECT ROUND(150.45648, 2, 1);--保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果 SELECT ROUND(150.45648, 2, 2);--保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果 --150.46000 --150.45000 --150.45000 --150.45000 复制代码 对字段出现NULL值的处理 --判断某些字段是否为空 --case select case when '字段名' is null then '\N' else convert(varchar(20),'字段名') end as 'NewName' select case when null is null then '\N' else convert(varchar(20),null) end as 'NewName' --SQL Server 2005:coalesce select coalesce('字符串类型字段','\N') as 'NewName' select coalesce(convert(varchar(20),'非字符串类型字段'),'\N') as 'NewName' select coalesce(convert(varchar(20),null),'\N') as 'NewName' --coalesce,返回其参数中的第一个非空表达式 select Coalesce(null,null,1,2,null)union select Coalesce(null,11,12,13,null)union select Coalesce(111,112,113,114,null) 复制代码 count的几种情况 --第一种 select count(*) from tablename --第二种 select count(ID) from tablename --第三种,1换成其它值也是可以的 select count(1) from tablename /* --第四种,这个不存在性能问题 idint 表ID(如果 indid = 0 或255)。否则为索引所属表的ID Indid smallint 索引ID: 0=表 1=聚簇索引 >1=非聚簇索引 255=具有text或image数据的表条目。 rows int 基于indid=0 和 indid=1地数据级行数,该值对于indid>1重复。如果indid=255,rows设臵为0。 当表没有聚簇索引时,Indid = 0 否则为 1。 */ select rows,indid from sysindexes where id = object_id('tablename') and indid in (0,1) 复制代码 分区 Oracle_SQL 的第 84 页 Union all --把两个相同结构的表union后插入到一个新表中 select * into table_now from table_1 union all select * from table_2 --Truncate table table_now 复制代码 查看数据库缓存的SQL --适用MSSQL2000、MSSQL2005 use master declare @dbid int Select @dbid = dbid from sysdatabases where name = 'Test'--修改成数据库的名称 select dbid,UseCounts ,RefCounts,CacheObjtype,ObjType, DB_Name(dbid) as DatabaseName,SQL from syscacheobjects where dbid=@dbid order by dbid,useCounts desc,objtype 复制代码 删除计划缓存 --删除整个数据库的计划缓存 DBCC FREEPROCCACHE --删除某个数据库的计划缓存 USE master DECLARE @dbid INT SELECT @dbid=dbid FROM sysdatabases WHERE NAME = '表名' DBCC FLUSHPROCINDB (@dbid) 复制代码 导出时加入特殊字符 情况一:全部字段都需要加字符,在这里设臵【文本限定符】就可以了。 情况二: --某些特殊的字段需要加单引号(或者其它符号) SELECT [ID] ,''''+convert(varchar(25),[ts],121)+'''' as [ts] ,''''+convert(varchar(25),[otherParty],121)+'''' as [otherParty] ,''''+convert(varchar(25),[StartTime],121)+'''' as [StartTime] ,[CcCause] ,[RrCause] FROM [表] 复制代码 效果:74983006,'2010-03-09 23:59:10.000' newid()的妙用 --生成测试数据 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test]( [name] [varchar](20) NULL, 分区 Oracle_SQL 的第 85 页 [name] [varchar](20) NULL, [quarter] [int] NULL, [profile] [int] NULL, [dates] [smallint] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[test] ON INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'a', 1, 1000, 421, 1) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'Aa', 2, 2000, 421, 2) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'AA', 3, 4000, 421, 3) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'a', 4, 5000, 421, 4) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 1, 3000, 421, 5) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 2, 3500, 421, 6) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 3, 4200, 421, 7) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 4, 5500, 421, 8) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'a', 1, 1000, 421, 9) SET IDENTITY_INSERT [dbo].[test] OFF 复制代码 --从数据库中随机取出N条记录的方法:newid() select top 5 * from test select top 5 * from test order by newid() 复制代码 (图:效果图) 查询时区分大小写 --生成测试数据 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test]( [name] [varchar](20) NULL, [quarter] [int] NULL, [profile] [int] NULL, [dates] [smallint] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF 分区 Oracle_SQL 的第 86 页 SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[test] ON INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'a', 1, 1000, 421, 1) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'Aa', 2, 2000, 421, 2) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'AA', 3, 4000, 421, 3) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'A', 4, 5000, 421, 4) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 1, 3000, 421, 5) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 2, 3500, 421, 6) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 3, 4200, 421, 7) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'b', 4, 5500, 421, 8) INSERT [dbo].[test] ([name], [quarter], [profile], [dates], [ID]) VALUES (N'A', 1, 1000, 421, 9) SET IDENTITY_INSERT [dbo].[test] OFF 复制代码 --sql server 查询区分大小写 collate Chinese_PRC_CS_AS_WS select * from test where [name]='A' collate Chinese_PRC_CS_AI select * from test where ascii(name) = ascii('A') 复制代码 (图:效果图) SQL换行 /* SQL的换行 制表符 CHAR(9) 换行符 CHAR(10) 回车 CHAR(13) */ PRINT 'Test'+CHAR(13)+'Name' PRINT 'Test'+CHAR(10)+'Age' PRINT 'Test'+CHAR(9)+'Tel' --以文本格式显示结果 SELECT 'AAA'+ CHAR(10)+'BBB'--AAA BBB SELECT 'AAA'+ CHAR(13)+'BBB'--AAA BBB SELECT 'AAA' + CHAR(10) + CHAR(13) + 'BBB'--AAA BBB 复制代码 TRUNCATE TABLE [Table Name] 下面是对Truncate语句在MSSQLServer2000中用法和原理的说明: Truncate是SQL中的一个删除数据表内容的语句,用法是: Truncate table 表名 速度快,而且效率高,因为: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。 DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数 据,并且只在事务日志中记录页的释放。 TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重臵为该列的种子。如果想保留标 识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。 TRUNCATE TABLE 不能用于参与了索引视图的表。 其它 --查看内存状态 dbcc memorystatus --查看哪个引起的阻塞,blk 分区 Oracle_SQL 的第 87 页 --查看哪个引起的阻塞,blk EXEC sp_who active --查看锁住了那个资源id,objid EXEC sp_lock --当前查询分析器SPID --查询分析器的状态栏中可以看到 --比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控 复制代码 --获取脚本的执行时间 declare @timediff datetime select @timediff=getdate() select * from tablename print '1耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate())) 复制代码 参考文献 数据库表行转列,列转行终极方案 行列互转(动态脚本) SELECT INTO 和 INSERT INTO SELECT 两种表复制语句 非常有用的sql脚本 SQL中OpenDataSource与OpenRowSet的应用 SQL中CONVERT转化函数的用法 CAST 和 CONVERT (Transact-SQL) 精度、小数位数和长度 (Transact-SQL) 监 控 SQL Server (2005/2008) 的运行状况--来自微软TetchNet ROUND (Transact-SQL) -------------------华丽分割线------------------- 作者:听风吹雨 出处:http://gaizai.cnblogs.com/ 版权:本文版权归作者和博客园共有 转载:欢迎转载,不过记得留下买路钱 邮箱:gaizai@126.com 格言:不喜欢是因为你不会 && 因为会所以喜欢 -------------------华丽分割线------------------- 源文档 分区 Oracle_SQL 的第 88 页 1、 别名 通过“”可以设定别名的大小写,默认显示为大写。 00:19:49 SQL> select sal,sal*12 total from emp; SAL TOTAL ---------- ---------- 3000 36000 1600 19200 1250 15000 2975 35700 1250 15000 2850 34200 2450 29400 3000 36000 5000 60000 1500 18000 1100 13200 950 11400 3000 36000 1300 15600 14 rows selected. 00:19:50 SQL> select sal,sal*12 "total" from emp; SAL total ---------- ---------- 3000 36000 1600 19200 1250 15000 2975 35700 1250 15000 2850 34200 2450 29400 3000 36000 5000 60000 1500 18000 1100 13200 950 11400 3000 36000 1300 15600 14 rows selected. Oracle SQL 别名 2011年2月26日 15:53 分区 Oracle_SQL 的第 89 页 分区 Oracle_SQL 的第 90 页 Oracle数据类型 在ORACLE8中定义了:标量(Scalar)、复合(COMPOSITE)、引用(REFERENCE)和LOB 四种数据类型。 (一)标量(Scalar) 标量类型与数据库的列所使用的类型相同,此外它还有一些扩展。 它又分为七个组:数字、字符、行、日期、行标识、布尔和可信。 ①数字 有三种基本类型:NUMBER、PLS_INTEGER和BINARY_INTENER。 NUMBER可以描述整数或实数,而PLS_INTEGER和BINARY_INTENER只能描述整数。 NUMBER,是以十进制格式进行存储的,它便于存储,但是在计算上,系统会自动的将它 转换成为二进制进行运算的。它的定义方式是NUMBER(P,S),P是精度,最大38位,S 是刻度范围,可在-84...127 间取值。例如:NUMBER(5,2)可以用来存储表 示-999.99...999.99 间的数值。 P、S可以在定义是省略,例如:NUMBER(5)、NUMBER等; BINARY_INTENER用来描述不存储在数据库中,但是需要用来计算的带符号的整数值。 它以2的补码二进制形式表述。循环计数器经常使用这种类型。 PLS_INTEGER和BINARY_INTENER唯一区别是在计算当中发生溢出时, BINARY_INTENER型的变量会被自动指派给一个NUMBER型而不会出错, PLS_INTEGER型的变量将会发生错误。 ②字符 包括CHAR、VARCHAR2(VARCHAR)、LONG、NCHAR和NVARCHAR2几种 类型。 CHAR 描述定长的字符串,如果实际值不够定义的长度,系统将以空格填充。它的声明 方式如下 CHAR(L) L为字符串长度,缺省为1,作为变量最大32767个字符,作为数据存储在 ORACLE8中最大为2000。 VARCHAR2(VARCHAR) 描述变长字符串。它的声明方式如下VARCHAR2(L),L为字 符串长度,没有缺省值,作为变量最大32767个字节,作为数据存储在ORACLE8中最大 为4000。在多字节语言环境中,实际存储的字符个数可能小于L值,例如:当语言环境为 中文(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)时,一个VARCHAR2(200)的数据列 可以保存200个英文字符或者100个汉字字符。 LONG 在数据库存储中可以用来保存高达2G的数据,作为变量,可以表示一个最大长度 为32760字节的可变字符串。 NCHAR、NVARCHAR2 国家字符集,与环境变量NLS指定的语言集密切相关,使用方 法和CHAR、VARCHAR2相同。 ③行 包括RAW和LONG RAW两种类型。用来存储二进制数据,不会在字符集间转换。 Oracle 数据类型 2011年4月23日 18:11 分区 Oracle_SQL 的第 91 页 包括RAW和LONG RAW两种类型。用来存储二进制数据,不会在字符集间转换。 RAW 类似于CHAR,声明方式RAW(L),L为长度,以字节为单位,作为数据库列最大 2000,作为变量最大32767字节。 LONG RAW 类似于LONG,作为数据库列最大存储2G字节的数据,作为变量最大32760 字节。 ④日期 只有一种类型--DATE,用来存储时间信息,站用7个字节(从世纪到秒),绝对没有‚千年 虫‛问题。 ⑤行标识 只有一种类型--ROWID,用来存储‚行标识符‛,可以利用ROWIDTOCHAR函数来将行 标识转换成为字符。 ⑥布尔 只有一种类型--BOOLEAN,仅仅可以表示TRUE、FALSE或者NULL。 ⑦可信 只有一种类型--MLSLABEL,可以在TRUSTED ORACLE中用来保存可变长度的二进制 标签。 在标准ORACLE中,只能存储NULL值。 (二)复合(COMPOSITE) 标量类型是经过预定义的,利用这些类型可以衍生出一些复合类型。主要有记录、表。 ①记录,可以看作是一组标量的组合结构,它的声明方式如下: TYPE record_type_name IS RECORD ( filed1 type1 [NOT NULL] [:=expr1] ....... filedn typen [NOT NULL] [:=exprn] ) 其中,record_type_name是记录类型的名字。(是不是看着象CREATE TABLE?......)引 用时必须定义相关的变量, 记录只是TYPE,不是VARIABLE。 ②表,不是物理存储数据的表,在这里是一种变量类型,也称为PL/SQL表,它类似于C语 言中的数组,在处理方式上也相似。 它的声明方式如下: TYPE table_type_name IS TABLE OF scalar_type INDEX BY BINARY_INTENER; 其中,table_type_name是类型的名字,scalar_type是一种标量类型的类型声明。引用时 也必须定义相关的变量。 表和数组不同,表有两列,KEY和VALUE,KEY就是定义时声明的BINARY_INTENER, VALUE就是定义时声明的scalar_type。 除了记录和表之外,还有对象类型、集合(嵌套表和VARRAYS)等类型,这些将专门讲解。 (三)引用(REFERENCE) 在PL/SQL8.0 之前,只有一种类型--REF CURSOR,也就是游标。它的定义较为简单, 分区 Oracle_SQL 的第 92 页 在PL/SQL8.0 之前,只有一种类型--REF CURSOR,也就是游标。它的定义较为简单, CURSOR cursor_name IS select .....from.....; 在PL/SQL8.0 之后,引入了REF类型,它指向一个对象。 (四)LOB型 LOB变量主要是用来存储大量数据的数据库字段,最大可以存储4G字节的内容。主要有: ①CLOB 和ORACLE7中的LONG类型相似,存储单字节字符数据。(别用来存中文 喔。。。) ②NCLOB 用来存储定宽多字节字符数据。(例如:存《啸傲江湖》就挺好) ③BLOB 和LONG RAW相似,用来存储无结构的二进制数据。 ④BFILE 用来允许ORACLE对数据库外存储的大型二进制文本进行只读形式的访问。 8i 的数据类型 数据类 型 参数 描述 numbe r(m,n) m=1 to 38 n=-84 to 127 可变长的数值列,允许0、正值及负值,m是所有有效数字的位 数,n是小数点以后的位数。如:number(5,2),则这个字段的 最大值是99,999,如果数值超出了位数限制就会被截取多余的位 数。如:number(5,2),但在一行数据中的这个字段输入 575.316,则真正保存到字段中的数值是575.32。如: number(3,0),输入575.316,真正保存的数据是575。 char(n) n=1 to 2000字节 定长字符串,n字节长,如果不指定长度,缺省为1个字节长(一 个汉字为2字节) long 无 可变长字符列,最大长度限制是2GB,用于不需要作字符串搜索 的长串数据,如果要进行字符搜索就要用varchar2类型。long是 一种较老的数据类型,将来会逐渐被BLOB、CLOB、NCLOB等 大的对象数据类型所取代。 varcha r2(n) n=1 to 4000字节 可变长的字符串,具体定义时指明最大长度n,这种数据类型可以 放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接 受的字符集标准)中的所有符号。如果数据长度没有达到最大值 n,Oracle 8i会根据数据大小自动调节字段长度,如果你的数据 前后有空格,Oracle 8i会自动将其删去。VARCHAR2是最常用 的数据类型。可做索引的最大长度3209。 date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日 分区 Oracle_SQL 的第 93 页 date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日 期,Oracle 8i其实在内部是按7个字节来保存日期数据,在定义 中还包括小时、分、秒。缺省格式为DD-MON-YY,如07-11-00 表示2000年11月7日。 raw(n) n=1 to 2000 可变长二进制数据,在具体定义字段的时候必须指明最大长度n, Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文 件,如Miceosoft Word文档。raw是一种较老的数据类型,将来 会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 long raw 无 可变长二进制数据,最大长度是2GB。Oracle 8i用这种格式来保 存较大的图形文件或带格式的文本文件,如Miceosoft Word文 档,以及音频、视频等非文本文件。在同一张表中不能同时有 long类型和long raw类型,long raw也是一种较老的数据类型, 将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取 代。 blob clob nclob 无 三种大型对象(LOB),用来保存较大的图形文件或带格式的文本 文件,如Miceosoft Word文档,以及音频、视频等非文本文件, 最大长度是4GB。 LOB有几种类型,取决于你使用的字节的类型,Oracle 8i实实在 在地将这些数据存储在数据库内部保存。可以执行读取、存储、 写入等特殊操作。 bfile 无 在数据库外部保存的大型二进制对象文件,最大长度是4GB。这 种外部的LOB类型,通过数据库记录变化情况,但是数据的具体 保存是在数据库外部进行的。Oracle 8i可以读取、查询BFILE, 但是不能写入。大小由操作系统决定。 9i 的数据类型 名称 含义 Char 用于描述定长的字符型数据,长度< = 2000 字节 varchar2 用于描述变长的字符型数据,长度< = 4000 字节 nchar 用来存储Unicode字符集的定长字符型数据,长度< = 1000 字节 分区 Oracle_SQL 的第 94 页 nchar 用来存储Unicode字符集的定长字符型数据,长度< = 1000 字节 nvarchar2 用来存储Unicode字符集的变长字符型数据,长度< = 1000 字节 number 用来存储整型或者浮点型数值 Date 用来存储日期数据 Long 用来存储最大长度为2GB的变长字符数据 Raw 用来存储非结构化数据的变长字符数据,长度< = 2000 字节 Long raw 用来存储非结构化数据的变长字符数据,长度< = 2GB rowid 用来存储表中列的物理地址的二进制数据,占用固定的10个字节 Blob 用来存储多达4GB的非结构化的二进制数据 Clob 用来存储多达4GB的字符数据 nclob 用来存储多达4GB的Unicode字符数据 Bfile 用来把非结构化的二进制数据存储在数据库以外的操作系统文件中 urowid 用来存储表示任何类型列地址的二进制数据 float 用来存储浮点数 源文档 分区 Oracle_SQL 的第 95 页 1、 转义字符 00:19:57 SQL> 00:16:43 SQL> select * from t1 00:16:48 2 where name like 'C%'; DEPTNO NAME LOC ---------- -------------------- -------------------- 10 Cisco BeiJing 20 C%ommd ShangHai 30 Commd ShangHai 00:17:22 SQL> select * from t1 00:17:32 2 where name like 'C\%o%'; no rows selected 00:18:19 SQL> select * from t1 00:18:22 2 where name like 'C\%o%' escape '\'; DEPTNO NAME LOC ---------- -------------------- -------------------- 20 C%ommd ShangHai 在escape后面可以设定多种字符,如‘$' 00:06:25 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 50 C%omm BeiJing 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 00:11:42 SQL> select * from dept 00:12:24 2 where dname like '%C$%%' escape '$'; DEPTNO DNAME LOC ---------- -------------- ------------- 50 C%omm BeiJing Oracle SQL 转义字符 2011年2月26日 15:55 分区 Oracle_SQL 的第 96 页 Variance 和 Stddev 函数 PLSQL 两个内臵的统计函数VARIANCE和STDDEV PLSQL中提供了两个内臵的统计函数VARIANCE和STDDEV,分别对应于方差和标准差,今天验证了一下,他们的算法如下: VARIANCE:方差,数列中各项和平均值的差平方后求和,然后除以数列个数减一,得到的即为方差。 STDDEV:标准差,就是上面算出来的方差的开平方根 功能描述:计算当前行关于组的标准偏离。(Standard Deviation) SAMPLE:下例返回部门30按雇佣日期排序的薪水值的累积标准偏离 SELECT last_name, hire_date,salary, STDDEV(salary) OVER (ORDER BY hire_date) "StdDev" FROM employees WHERE department_id = 30; LAST_NAME HIRE_DATE SALARY StdDev ------------------------- ---------- ---------- ---------- Raphaely 07-12月-94 11000 0 Khoo 18-5月 -95 3100 5586.14357 Tobias 24-7月 -97 2800 4650.0896 Baida 24-12月-97 2900 4035.26125 Himuro 15-11月-98 2600 3649.2465 Colmenares 10-8月 -99 2500 3362.58829 STDDEV函数 STDDEV(x)用于计算x的标准差。标准差是一个统计函数,其定义是方差的平方根(方差的概念很快就会介绍)。 下面这个查询使用STDDEV()函数计算products表中price列值的标准差: SELECT STDDEV(price)FROM products; STDDEV(PRICE) ------------- 11.0896303 VARIANCE函数 VARIANCE(x)用于计算x的方差。方差是一个统计函数,其定义为一组样本数据的偏离程度,等于标准差的平方。 下面这个例子使用VARIANCE函数计算products表中price列值的方差: SELECT VARIANCE(price)FROM products; VARIANCE(PRICE) --------------- 122.979899 SQL Variance/Stddev 函数 2011年2月26日 23:30 分区 Oracle_SQL 的第 97 页 1、 多表连接: 查询的一种,数据来源不再是单一的一个表,我们可以重多个表中提取相关联的数据,比如,从emp和dept表中找出人员所在的部门名称,----多 个表来联合查询 1.查询的时候,可以from多个表 SELECT * FROM EMP,DEPT 这样会产生一个笛卡尔集 表示:两个表任何组合的结果 2.需求:取关联数据 我的部门20号,我想要20号部门的名称====连接条件 select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno 3.语法格式: select 字段 from 表1,表2 where 表1.列=表2.列 select empno,ename,d.deptno,dname from emp e,dept d where e.deptno=d.deptno 当然,你可以为表定义一个别名,但是不能使用as(字段重命名可以使用) 不同的连接条件可以使用and或or select empno,ename,d.deptno,dname,e.sal from emp e,dept d where e.deptno=d.deptno And sal>1600 4.使用不等值连接 连接条件:一个表中的记录,在另一个表中能够找到匹配的记录即可 Select e.*,g.grade From emp e,salgrade g Where e.sal Between g.losal And g.hisal 5.外连接:把不满足条件的数据显示出来 Select * From EMP E,DEPT D Where E.DEPTNO=D.DEPTNO(+) 外连接可以在左表,也可以在右表 6.自连接 从同一个表中联合查询 Select E.ENAME,P.ENAME From EMP E,EMP P Where E.MGR=P.EMPNO Sql1999: 7.交叉连接: 其结果是笛卡尔集,是没加任何条件所产生出来的数据集合 8.自然连接:默认情况下,以两个表中有相同名称的列来进行连接查询 Select * From EMP Natural Join DEPT 其效果相当于 select * from emp,dept where emp.deptno=dept.deptno 9.使用using子句来查询 select * from emp join dept using(deptno) 10.使用on子句 使用on来指定连接具体条件 Select * From EMP Join DEPT On emp.deptno=dept.deptno And EMP.DEPTNO=20 多个表连接查询 Select E.ENAME,E.SAL,D.DNAME,S.GRADE FROM EMP E Join DEPT D On E.DEPTNO=D.DEPTNO Join SALGRADE S On E.SAL Between S.LOSAL And S.HISAL 11.外连接: 与内连接的区别: 内连接========查询条件相等的记录(能够匹配的) 外连接========条件相等的+没匹配的 Select * From EMP E Left Outer Join DEPT D On E.DEPTNO=D.DEPTNO Select * From EMP E Right Outer Join DEPT D On E.DEPTNO=D.DEPTNO Oracle SQL 表连接 2011年2月26日 16:17 分区 Oracle_SQL 的第 98 页 Select * From EMP E Right Outer Join DEPT D On E.DEPTNO=D.DEPTNO Select * From EMP E Full Outer Join DEPT D On E.DEPTNO=D.DEPTNO 等值连接 02:27:11 SQL> select e.ename,d.loc 02:27:18 2 from emp e join dept d 02:27:21 3 using(deptno); ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS 13 rows selected. 02:27:24 SQL> select e.ename ,d.loc 02:28:41 2 from emp e ,dept d 02:28:48 3 where e.deptno=d.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS 13 rows selected. 03:05:17 SQL> select e.ename ,d.loc 03:05:21 2 from emp e join dept d 03:05:38 3 on e.deptno=d.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS 13 rows selected. 2、 自连接 01:43:31 SQL> select a.empno,a.ename,b.mgr ,b.ename 01:44:19 2 from emp a ,emp b 01:44:27 3 where a.mgr=b.empno; 分区 Oracle_SQL 的第 99 页 01:44:27 3 where a.mgr=b.empno; EMPNO ENAME MGR ENAME ---------- ---------- ---------- ---------- 7902 FORD 7839 JONES 7788 SCOTT 7839 JONES 7900 JAMES 7839 BLAKE 7844 TURNER 7839 BLAKE 7654 MARTIN 7839 BLAKE 7521 WARD 7839 BLAKE 7499 ALLEN 7839 BLAKE 7934 MILLER 7839 CLARK 7876 ADAMS 7566 SCOTT 7782 CLARK KING 7698 BLAKE KING 7566 JONES KING 7369 SMITH 7566 FORD 13 rows selected. 01:44:42 SQL> select ename,mgr from emp 01:45:16 2 where empno=7902; ENAME MGR ---------- ---------- FORD 7566 01:45:35 SQL> select a.empno,a.ename,a.mgr ,b.ename 01:45:47 2 from emp a ,emp b 01:45:52 3 where a.mgr=b.empno; EMPNO ENAME MGR ENAME ---------- ---------- ---------- ---------- 7902 FORD 7566 JONES 7788 SCOTT 7566 JONES 7900 JAMES 7698 BLAKE 7844 TURNER 7698 BLAKE 7654 MARTIN 7698 BLAKE 7521 WARD 7698 BLAKE 7499 ALLEN 7698 BLAKE 7934 MILLER 7782 CLARK 7876 ADAMS 7788 SCOTT 7782 CLARK 7839 KING 7698 BLAKE 7839 KING 7566 JONES 7839 KING 7369 SMITH 7902 FORD 13 rows selected. 01:45:56 SQL> select ename,mgr from emp 01:46:07 2 where empno=7788; ENAME MGR ---------- ---------- SCOTT 7566 01:46:16 SQL> select ename,mgr from emp 01:46:23 2 where empno=7876; ENAME MGR ---------- ---------- ADAMS 7788 01:46:33 SQL> 03:41:12 SQL> select a.empno,a.ename ,a.mgr,b.ename 03:41:18 2 from emp a join emp b 03:41:20 3 on a.mgr=b.empno; EMPNO ENAME MGR ENAME ---------- ---------- ---------- ---------- 7902 FORD 7566 JONES 7788 SCOTT 7566 JONES 7900 JAMES 7698 BLAKE 7844 TURNER 7698 BLAKE 7654 MARTIN 7698 BLAKE 7521 WARD 7698 BLAKE 7499 ALLEN 7698 BLAKE 7934 MILLER 7782 CLARK 7876 ADAMS 7788 SCOTT 分区 Oracle_SQL 的第 100 页 7876 ADAMS 7788 SCOTT 7782 CLARK 7839 KING 7698 BLAKE 7839 KING 7566 JONES 7839 KING 7369 SMITH 7902 FORD 13 rows selected. 03:41:58 SQL> select empno ,mgr from emp where empno=7566; EMPNO MGR ---------- ---------- 7566 7839 03:42:25 SQL> select ename ,mgr from emp where empno=7839; ENAME MGR ---------- ---------- KING 3、 外连接 01:51:51 SQL> select e.empno ,e.ename,e.sal ,d.deptno,d.loc 01:51:54 2 from emp e ,dept d 01:52:03 3 where e.deptno=d.deptno; EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- ------------- 7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 7654 MARTIN 1250 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7782 CLARK 2450 10 NEW YORK 7788 SCOTT 3000 20 DALLAS 7839 KING 5000 10 NEW YORK 7844 TURNER 1500 30 CHICAGO 7876 ADAMS 1100 20 DALLAS 7900 JAMES 950 30 CHICAGO 7902 FORD 3000 20 DALLAS 7934 MILLER 1300 10 NEW YORK 14 rows selected. 01:52:38 SQL> select e.empno ,e.ename,e.sal ,d.deptno,d.loc 01:52:39 2 from emp e ,dept d 01:52:41 3 where e.deptno(+)=d.deptno; EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- ------------- 7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 7654 MARTIN 1250 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7782 CLARK 2450 10 NEW YORK 7788 SCOTT 3000 20 DALLAS 7839 KING 5000 10 NEW YORK 7844 TURNER 1500 30 CHICAGO 7876 ADAMS 1100 20 DALLAS 7900 JAMES 950 30 CHICAGO 7902 FORD 3000 20 DALLAS 7934 MILLER 1300 10 NEW YORK 50 Beijing 40 BOSTON 16 rows selected. 01:55:23 SQL> select e.empno ,e.ename,e.sal ,d.deptno,d.loc 01:55:30 2 from emp e ,dept d 01:55:37 3 where e.deptno=d.deptno(+); EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- ------------- 7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 分区 Oracle_SQL 的第 101 页 7566 JONES 2975 20 DALLAS 7654 MARTIN 1250 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7782 CLARK 2450 10 NEW YORK 7788 SCOTT 3000 20 DALLAS 7839 KING 5000 10 NEW YORK 7844 TURNER 1500 30 CHICAGO 7876 ADAMS 1100 20 DALLAS 7900 JAMES 950 30 CHICAGO 7902 FORD 3000 20 DALLAS 7934 MILLER 1300 14 rows selected. 03:05:47 SQL> select e.ename ,d.loc 03:06:09 2 from emp e left join dept d 03:06:20 3 on e.deptno=d.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS MILLER 14 rows selected. 03:06:22 SQL> select e.ename ,d.loc 03:06:28 2 from emp e right join dept d 03:06:39 3 on e.deptno=d.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS Beijing BOSTON ShangHai 16 rows selected. 4、 Oracle 多表连接和子查询 一、连接: 1、等值连接 迪卡尔集连接 select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门 from emp a, dept b 等值连接 select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门 from emp a, dept b where a.deptno = b.deptno; 2、非等值连接,在emp表和salgrade表中查找员工的姓名,工资,等级,工资上线,工资下线 select ename as 姓名, sal as 工资, grade as 工资等级,losal as 工资上线,hisal as 工资下线 from emp, salgrade where sal between losal and hisal; 3、外连 分区 Oracle_SQL 的第 102 页 3、外连 外连接则是内连接的扩展,它不仅会满足连接条件的记录,而且还会返回不满足连接条件的记录,语法如下: 右外连 select e.ename, d.dname, e.deptno from emp e,dept d where e.deptno = d.deptno(+); 左外连与右外连相反 以hr登陆查询表employees 和departments 表 select first_name as 姓名, department_name as 部门名称, d.department_id as 部门编号 from employees e, departments d where e.department_id= d.department_id(+) ; 4、自连:在同一个表中查询每个员工及上司的工号和姓名 select a.empno as 员工编号, a.ename as 员工姓名, a.mgr as 上司的员工编号, b.ename as 上司姓名 from emp a, emp b where a.mgr = b.empno; 5、SQL99 交叉连接 cross join------相当于迪卡尔集 select e.ename,d.dname from emp e cross join dept d; 自然连接 natural join------相当等值连接 select e.ename, d.dname from emp e natural join dept d; using子句-----使用同名列查询 select e.ename, d.dname from emp e join dept d using (deptno); on子句 ------当列名不同时用on子句 用on查询两张表 select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; 用on查询多张表 select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno join 第三个表 on 列1 = 列2; 内连接(Inner join)-------内连接只返回满足连接条件的数据 select employee_id, last_name, salary, department_id, department_name from employees inner join departments using(department_id); 左外连 select employee_id, last_name, salary, department_id, department_name from employees left join departments using(department_id); 右外连 select employee_id, last_name, salary, department_id, department_name from employees right join departments using(department_id); 满外连 select employee_id, last_name, salary, department_id, department_name from employees full outer join departments using(department_id); 二、子查询: 1、子查询语法: SELECT select_list FROM table WHERE expr operator (select select_list FROM table); 2、子查询分为: 『标准子查询』:子查询只执行一次 『关联子查询』:主查询执行一次,子查询就执行一次,子查询依赖于主查询的参数。 select * from jobs a where job_id>1 and Exists(select * from jobs where job_id=a.job_id-1) 3、使用子查询的方针: (1)子查询要用括弧“()”括起来; (2)子查询要放在比较运算符的右边。 (3)Order by子句在子查询中是没有必要的,除非需要Top-N的分析。 分区 Oracle_SQL 的第 103 页 (3)Order by子句在子查询中是没有必要的,除非需要Top-N的分析。 (4)单行子查询配单个值的操作符,多行子查询配多个值的操作符。 (5)查询是基于未知的值时应考虑使用子查询。 注: 如果子查询没有返回结果,主查询也不会返回任何结果 如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符 如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符,只能用IN,ANY,ALL 多行子查询的比较运算符: IN,ANY ,ALL 其中ANY和ALL可以用“对聚合函数(Max,Min)统计结果的比较”来代替。 select stor_id,min(qty) from sales group by stor_id having min(qty) in (select min(qty) from sales group by stor_id); 这是一个画蛇添足的查询示例。 4、子查询示例: 查找出工资比scott高的人 select ename, sal from emp where sal> (select sal from emp where ename='SCOTT'); 查找那些人和scott相同职位的人 select ename, job from emp where job= (select job from emp where ename='SCOTT') and ename <> 'SCOTT'; any的用法 < any意味着小于最大、> any大于最小 select empno, ename, sal, job from emp where sal all:大于所有,即大于最大 select empno, ename, sal, job from emp where salb.avgsal; Oracle SQL 子查询 2011年2月27日 13:27 分区 Oracle_SQL 的第 105 页 and a.sal >b.avgsal; 22:19:38 SQL> / EMPNO ENAME SAL AVGSAL ---------- ---------- ---------- ---------- 7499 ALLEN 1900 1742.85714 7566 JONES 3175 2575 7698 BLAKE 3150 1742.85714 7788 SCOTT 4200 2575 7839 KING 5100 3825 7844 TURNER 1800 1742.85714 7902 FORD 3200 2575 2、SQL多列子查询 成对和非成对的比较 06:30:22 SQL> select * from item; QTY PRODID ORDID ------------------------------ 100 100861 605 10 102130 605 500 100870 605 100 100863 605 5 100890 605 50 101860 605 10 100888 616 10 100889 617 20 100861 604 500 100870 606 100 100863 607 100 102130 603 12 rows selected. 1)成对子查询 select ordid,prodid,qty from item 06:30:10 3 where (prodid,qty) in 06:30:10 4 (select prodid,qty from item where ordid=605) 06:30:10 6 and ordid<>605; ORDID PRODID QTY ---------- ---------- ---------- 607 100863 100 606 100870 500 2)非成对子查询 select ordid,prodid,qty from item where prodid in (select prodid from item where ordid=605) and qty in (select qty from item where ordid=605) 06:30:18 6 and ordid<>605 06:30:19 7 ; ORDID PRODID QTY ---------- ---------- ---------- 603 102130 100 607 100863 100 606 100870 500 分区 Oracle_SQL 的第 106 页 3、 子查询: 当一个查询是另一个查询的条件时,称之为子查询。 子查询可以使用几个简单命令构造功能强大的复合命令。 子查询最常用于SELECT-SQL命令的WHERE子句中。 子查询是一个 SELECT 语句,它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套 在另一子查询中。 语法 可用三种语法来创建子查询: comparison [ANY | ALL | SOME] (sqlstatement) expression [NOT] IN (sqlstatement) [NOT] EXISTS (sqlstatement) 子查询可分为以下几个部分: 组成部分 comparison 一个表达式及一个比较运算符,将表达式与子查询的结果作比较。 expression 用以搜寻子查询结果集的表达式。 sqlstatement SELECT 语句,遵从与其他 SELECT 语句相同的格式及规则。它必须括在括号之中。 说明 可以拿子查询代替表达式 用于SELECT 语句字段表或 WHERE 或 HAVING 子句。在子查询之中,在 WHERE 或 HAVING 子句的表达式中,用 于计算的特定值是由 SELECT 语句提供的。 使用 ANY 或 SOME 谓词,它们是同义字,来检索主查询中的记录,这些记录要满足在子查询中检索的任何记录的比较条件。下列示例将返回全部单价 比任何以 25% 或更高的折扣卖出的产品高的产品: SELECT * FROM Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM OrderDetails WHERE Discount >= .25); 使用 ALL 谓词只检索主查询中的这些记录,它们满足在子查询中检索的所有记录的比较条件。如果将前一个示例中的 ANY 改为 ALL,查询只会返 回单价比全部以 25% 或更高的折扣卖出的产品高的产品。这是更多的限制。 用 IN 谓词,只能在主查询检索那些记录,在子查询中的某些记录也包含和它们相同的值。下列示例返回有 25% 或更高的折扣的所有产品: SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25); 相反,可用 NOT IN 在主查询中检索那样的记录,在子查询中没有包含与它们的值相同的记录。 在 true/false 比较中使用 EXISTS 谓词(与可选的 NOT 保留字一道)来决定子查询是否会返回任何记录。 示例 还可用子查询中的表名别名来查询子查询外的 FROM 子句的列表。下列示例返回工资等于或高于所有职位相同员工的平均工资的员工姓名。这张员 工表的别名为 "T1": SELECT LastName, FirstName, Title, Salary FROM Employees AS T1 WHERE Salary >= (SELECT Avg(Salary) FROM Employees WHERE T1.Title = Employees.Title) Order by Title; 上例中AS保留词可选。 某些子查询在交叉表查询中是允许的,特别是谓词(那些在 WHERE 子句中的)。将子查询作为输出(那些列在 SELECT 中的)在交叉表查询中 是不允许的。 使用子查询的原则 1.一个子查询必须放在圆括号中。 2.将子查询放在比较条件的右边以增加可读性。 子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句, 并且如果指定了它就必须放在主 SELECT 语句的最后。 ORDER BY 子句可以使用,并且在进行 Top-N 分析时是必须的。 3.在子查询中可以使用两种比较条件:单行运算符和多行运算符。 分区 Oracle_SQL 的第 107 页 3.在子查询中可以使用两种比较条件:单行运算符和多行运算符。 子查询的类型 单行子查询:从内 SELECT 语句只返回一行的查询 多行子查询:从内 SELECT 语句返回多行的查询 单行子查询 单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。幻灯片中列出了单行操作符。 例 显示那些 job ID 与雇员 141 相同的雇员。 SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141); SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); 显示 job ID 与雇员 141 相同,并且薪水 高于雇员 143 的那些雇员。 注:外和内查询可以从不同的表中取得数据。 SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); 求所有人谁的工资最小。 SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); 求每个部门的最小工资,但是要高于50号部门的工资。 SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees 分区 Oracle_SQL 的第 108 页 FROM employees GROUP BY department_id); 问题出现在:单行子查询返回了多个查询值; 应改为: SELECT employee_id, last_name FROM employees WHERE salary in (SELECT MIN(salary) FROM employees GROUP BY department_id); SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); 如果子查询返回的是零值,不会对主程序造成影响; 如果子查询返回的是空值,那么会影响主程序的返回值; SELECT employee_id, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees) SELECT employee_id, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL) 放在select下的子查询的返回值必须是一个具体值, from后面也可以加子查询; having后面也可以加子查询; order by后面也可以; 多列子查询适应于:成对比较;非成对比较。 SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)) AND employee_id NOT IN (178,174); 输出:176 149 80 只有要查询的东西和你子查询返回的东西一一对应上了,你的查询才能成功。 如果有一个 对应不上那么你的查询不会成功。 非成对的子查询: SELECT employee_id, manager_id, department_id 分区 Oracle_SQL 的第 109 页 SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); 输出:144 124 50 143 124 50 142 124 50 176 149 80 上面两个程序就是成对子查询和非成对子查询两者之间的区别。 如果我想去显示员工信息,要求:员工的工资高于本部门的平均工资。 SELECT a.last_name, a.salary, a.department_id FROM employees a WHERE a.salary > (SELECT AVG(salary) FROM employees b WHERE b.department_id = a.department_id); in line view(内联视图) SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; 源文档 4、 关联子查询 方法一: 分析:每一类产品的平均价格如下: select product_type_id,avg(price) from products group by product_type_id; PRODUCT_TYPE_ID AVG(PRICE) ------------------------- 1 24.975 2 26.22 3 13.24 4 13.99 13.49 如果把上面的结果保存为一个表,如 products_avg, create table products_avg 分区 Oracle_SQL 的第 110 页 products_avg as select product_type_id,avg(price) avg_price from products group by product_type_id 求价格高于同类产品平均价格的产品,就可以通过prodducts与products_avg进行关联查询: select product_id,p.product_type_id,p.name,p.price from products p join products_avg a on p.product_type_id=a.product_type_id where p.price>a.avg_price; PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ------------------------------------------------------- ---------- 2 1 Chemistry 30 5 2 Z Files 49.99 7 3 Space Force 9 13.49 10 4 Pop 3 15.99 11 4 Creative Yell 14.99 上面的结果正对,即查询到每种产品,看其价格都大于同类产品的平均价格. 方法二: 还可以使用关联子查询实现上面要求. 关联子查询会引用外部查询中的一列或多列.在执行时,外部查询的每一行都被一次一行地传递给子查询.子查询 依次读取外部查询传递来的每一值,并将其用到子 查询上,直到外部查询所有的行都处理完为止.然后返回查询结果. 本例中,外部查询从products表中检索出所有的行,并将其传递给内部查询.内部查询依次读取外部查询传递来的每一行数据,并对内部查询中 product_type_id等于外部查询的中product_type_id值的每种产品计算平均价格. 如:查询第一行product_type_id为1时,把这行数据传递到内部查询,内部查询根据这个编号到产品表中找到同类型的所有行,然后求如平均价 格,外部 查询再根据这个结果来判断查询条件(外部查询是在执行者where语句时用到子查询,子查询执行完后还返回到where子句中). select product_id,product_type_id,name,price from products outer where price>( select avg(price) from products inner where inner.product_type_id=outer.product_type_id) PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ---------- 2 1 Chemistry 30 5 2 Z Files 49.99 7 3 Space Force 9 13.49 10 4 Pop 3 15.99 11 4 Creative Yell 14.99 例2 : 显示曾经有过两次工作以上的员工. SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); 源文档 分区 Oracle_SQL 的第 111 页 PLSQL_基础 PL/SQL块结构和组成元素 PL/SQL程序由三个块组成 即 声明部分 执行部分 异常处理部分 PL/SQL块的结构如下 DECLARE /* 声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 */ BEGIN /* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */ EXCEPTION /* 执行异常部分: 错误处理 */ END; 其中 执行部分 即 begin 和 end 块是必须的。 PL/SQL中声明变量 在语句块的声明部分对变量声明,声明一个变量的语法是: variable_name [ constant ] type [ not null ] [:=value] 其中: variable_name 为变量名 type 为类型 value 为变量的初值 例: DECLARE V_Description VARCHAR2(50); V_Number NUMBER := 45; V_Counter BINARY_INTEGER := 0; V_PI CONSTANT NUMBER NOT NULL :=3.14; 注意:如果变量在声明时使用了NOT NULL选项则必须为变量指定初值 如果变量在声明时使用了CONSTANT选项则必须为变量指定初值,并且该初值不能被改变。 PL/SQL中的复合类型 记录类型 :记录类型是把逻辑相关的数据作为一个单元存储起来 %TYPE:定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,这时可以使用%TYPE DECLARE --用%TYPE 类型定义变量 v_ename emp.ename%TYPE; -- 用 %TYPE 类型定义与表相配的字段 TYPE t_Record IS RECORD( t_no emp.empno%TYPE, t_name emp.ename%TYPE, t_sal emp.sal%TYPE ); -- 声明接收数据的变量 v_emp t_Record %ROWTYPE:返回一个记录类型, 其数据类型和数据库表的数据结构相一致 Sql代码 DECLARE 1. v_empno emp.empno%TYPE := &no; 2. rec emp%ROWTYPE; 3. BEGIN 4. SELECT * INTO rec FROM emp WHERE empno=v_empno; 5. DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate); 6. END; 7. PL/SQL_基础 2011年2月26日 22:26 分区 Oracle_SQL 的第 112 页 PLSQL_函数 ORACLE 提供可以把PL/SQL 程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子 程序,他们是被命名的PL/SQL块,均存储在数据库中,并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区 别是函数总向调用者返回数据,而过程则不返回数据。 创建函数的语法如下: Sql代码 CREATE [OR REPLACE] FUNCTION function_name 1. [(argment [{ IN | OUT | IN OUT }] Type , 2. argment [{ IN | OUT | IN OUT }] Type ] 3. RETURN return_type 4. { IS | AS } 5. <类型.变量的说明> 6. BEGIN 7. FUNCTION_body 8. EXCEPTION 9. 其它语句 10. END; 11. 注意:这里的type只能是类型,不能有长度或大小的定义,比如varchar2而非varchar2(20) 创建函数举例: Java代码 create or replace function get_salary( 1. v_deptno number, 2. emp_count out number 3. ) 4. return number 5. is 6. v_sum number; 7. begin 8. select sum(deptno),count(*) into v_sum,emp_count 9. from emp where empno = v_deptno; 10. return v_sum; 11. exception 12. when no_data_found then 13. dbms_output.put_line('部门号为' || v_deptno || '部门没有员工!'); 14. when others then 15. dbms_output.put_line(sqlcode || sqlerrm); 16. end; 17. 调用函数方法 函数声明时所定义的参数称为形式参数,应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时,可以使用以下三种方法 向函数传递参数: 第一种参数传递格式称为位臵表示法,格式为: argument_value1[,argument_value2 …] 第二种参数传递格式称为名称表示法,格式为 : argument => parameter [,…] 其中:argument 为形式参数,它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。在这种格式中,形式参数与实际 参数成对出现,相互间关系唯一确定,所以参数的顺序可以任意排列。 第三种参数传递格式称为混合表示法 : 即在调用一个函数时,同时使用位臵表示法和名称表示法为函数传递参数。采用这种参数传递方法时,使用位臵表示法所传递的参数必须放 在名称表示法所传递的参数前面。也就是说,无论函数具有多少个参数,只要其中有一个参数使用名称表示法(如EMPNUM=>7369),其后 所有的参数都必须使用名称表示法。 例子: Sql代码 declare 1. v_sum number; PL/SQL_函数 2011年2月26日 22:18 分区 Oracle_SQL 的第 113 页 v_sum number; 2. v_num number; 3. begin 4. --位臵表示法 5. v_sum := get_salary(30,v_num); 6. dbms_output.put_line('deptno 30 sum salary is ' || v_sum || ',emp count is ' || v_num); 7. --名称表示法 8. v_sum := get_salary(emp_count=> v_num,v_deptno => 20); 9. dbms_output.put_line('deptno 20 sum salary is ' || v_sum || ',emp count is ' || v_num); 10. --混合表示法 11. v_sum := get_salary(10,emp_count => v_num); 12. dbms_output.put_line('deptno 10 sum salary is ' || v_sum || ',emp count is ' || v_num); 13. end; 14. 参数默认值 在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。 Sql代码 create or replace function default_param( 1. name varchar2, 2. age number default 20, 3. sex varchar2 default 'male' 4. ) 5. return varchar2 6. is 7. v_str varchar2(50); 8. begin 9. v_str := name || ', ' || age || ', ' || sex; 10. return v_str; 11. end; 12. 具有默认值的函数创建后,在函数调用时,如果没有为具有默认值的参数提供实际参数值,函数将使用该参数的默认值。但当调用者为默认 参数提供实际参数时,函数将使用实际参数值。在创建函数时,只能为输入参数设臵默认值,而不能为输入/输出参数设臵默认值。 调用的例子: Sql代码 declare 1. v_str varchar2(50); 2. begin 3. v_str := default_param('scott',10,'male'); 4. dbms_output.put_line(v_str); 5. v_str := default_param('smith'); 6. dbms_output.put_line(v_str); 7. v_str := default_param('lucy', sex => 'female'); 8. dbms_output.put_line(v_str); 9. end; 10. 或者是这样: Java代码 select default_param('smith') from dual; 1. 分区 Oracle_SQL 的第 114 页 PLSQL_存储过程 1、 在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也 可以向存储过程传回参数。 创建存储过程语法: Sql代码 CREATE [OR REPLACE] PROCEDURE Procedure_name 1. [(argment [{ IN | OUT | IN OUT }] Type, 2. argment [{ IN | OUT | IN OUT }] Type ] 3. { IS | AS } 4. <类型.变量的说明> 5. BEGIN 6. <执行部分> 7. EXCEPTION 8. <可选的异常错误处理程序> 9. END; 10. 例子: create or replace procedure delEmp( 1. v_empno emp.empno%TYPE 2. ) 3. is 4. no_result exception; 5. begin 6. delete from emp where empno = v_empno; 7. if sql%notfound then 8. raise no_result; 9. end if; 10. dbms_output.put_line('empno ' || v_empno || 'has been deleted!'); 11. exception 12. when no_result then 13. dbms_output.put_line('empno ' || v_empno || ' not found!'); 14. when others then 15. dbms_output.put_line(sqlcode || sqlerrm); 16. end; 17. 调用存储过程: 方法一:写一匿名块 begin 1. delemp(7369); 2. end; 3. 方法二:在Toad Sql Editor中执行如下代码: PL/SQL_存储过程 2011年2月26日 22:20 分区 Oracle_SQL 的第 115 页 方法二:在Toad Sql Editor中执行如下代码: Java代码
exec delEmp(1234);
1. 2. 方法三:在Sql Plus中调用过程: 首先设臵让服务器显示信息: Sql代码 set serveroutput on; 1. 然后调用存储过程: exec delEmp(1234); 1. 可以使用DROP语句删除过程:DROP PROCEDURE proceduer_name; 可以使用DROP语句删除函数:DROP FUNCTION function_name; 练习题目:构造过程getEmp(),输入参数:职员编号,在控制台上输出职员的姓名,薪水,职 位,部门编号,注:加入异常处理。 Sql代码 create or replace procedure getEmp( 1. v_empno emp.empno%TYPE, 2. v_ename out emp.ename%TYPE, 3. v_sal out emp.sal%TYPE, 4. v_job out emp.job%TYPE, 5. v_deptno out emp.deptno%TYPE 6. ) 7. is 8. begin 9. select ename, sal, job, deptno into v_ename, v_sal, v_job, v_deptno from emp where empno = v_empno; 10. exception 11. when no_data_found then 12. dbms_output.put_line('你需要的数据不存在!'); 13. when others then 14. dbms_output.put_line(sqlcode || sqlerrm); 15. end; 16. 调用存储过程: Sql代码 declare 1. v_ename emp.ename%TYPE; 2. v_sal emp.sal%TYPE; 3. v_job emp.job%TYPE; 4. v_deptno emp.deptno%TYPE; 5. begin 6. getEmp(7499,v_ename,v_sal,v_job,v_deptno); 7. dbms_output.put_line('人员姓名:'||v_ename||',工资:'|| v_sal || ',职 分区 Oracle_SQL 的第 116 页 dbms_output.put_line('人员姓名:'||v_ename||',工资:'|| v_sal || ',职 位:'|| v_job || ',部门编号:'||v_deptno); 8. end; 9. 2、--基于表emp和dept --构造Procedure change_salary --参数:ename in varchar2 salary in number v_job out varchar2 v_dname out varchar2 --先查找指定员工,如果查出多条记录,提示并异常退出;如果没有该名员工,提示并异常退 出。 --如果非上述情况,先判断该名员工的职位,如果职位不是'MANAGER',且要修改的薪水大于 8000,拒绝修改并提示:‚普通员工不能赚这么多薪水‛。 --否则修改该名员工的薪水, --输出参数:该职员的职位,和所在部门的名字,并打印输出。 --构造上述过程,并写出在SQL Editor中的调试代码(给出调用方式) 存储过程源代码如下: Sql代码 create or replace procedure change_salary( 1. v_ename in emp.ename%TYPE, 2. v_salary in emp.sal%TYPE, 3. v_job out emp.job%TYPE, 4. v_dname out dept.dname%TYPE 5. ) 6. is 7. v_empno emp.empno%TYPE; 8. too_many_salary exception; 9. begin 10. select e.empno, e.job, d.dname into v_empno, v_job, v_dname 11. from emp e, dept d 12. where e.deptno = d.deptno 13. and upper(ename) = upper(v_ename); 14. 15. if v_job <> 'MANAGER' and v_salary > 8000 then 16. raise too_many_salary; 17. end if; 18. 19. update emp set sal = v_salary where empno = v_empno; 20. if sql%found then 21. dbms_output.put_line('员工姓名为' || v_ename || '的员工的工资已经修改 为' || v_salary); 22. end if; 分区 Oracle_SQL 的第 117 页 end if; 23. exception 24. when no_data_found then 25. dbms_output.put_line('您输入的人员姓名' || '对应信息不存在'); 26. when too_many_rows then 27. dbms_output.put_line('人员姓名为'||v_ename ||'不止一个,无法更 新!'); 28. when too_many_salary then 29. dbms_output.put_line('你输入的工资过高,无法录入'); 30. when others then 31. dbms_output.put_line(sqlcode || sqlerrm); 32. 33. end; 34. 测试代码如下: Sql代码 declare 1. v_out_job emp.job%type; 2. v_out_dname dept.dname%type; 3. begin 4. change_salary('jones', 8500, v_out_job, v_out_dname); 5. end; 6. 3、 存储过程与函数 1、存储过程 存储过程的参数 形式参数和实际参数,例如,有如下一个存储过程,该过程接收一个作家代码和一个工资值, 将该作家的工资改为接收到的工资值。 Java代码 create or replace procedure updateauths( 1. p_authscode auths.author_code%type, 2. p_authssalary auths.salary%type) 3. as 4. begin 5. update auths set salary=p_authssalary where author_code=p_authscode; 6. commit; 7. end updateauths; 8. 下面的PL/SQl块调用updateauths存储过程,将代码为A00011的作家的工资改为350元。 Java代码 declare 1. v_authorcode auths.author_code%type:='A00011'; 2. v_salary auths.salary%type:=350; 分区 Oracle_SQL 的第 118 页 v_salary auths.salary%type:=350; 3. begin 4. updateauths(v_authorcode,v_salary); 5. end; 6. v_authorcode、v_salary作为参数传递到存储过程updateauths中,这些参数是实际参数,简 称实参。 p_authscode、p_authssalary就是形式参数,简称形参。 参数定义中,IN、OUT和IN OUT代表参数的三种不同模式: IN:当调用存储过程时,该模式的形参接收对应实参的值,并且该是只读的,即不能被修改。 默认为IN。 OUT:该形参被认为只能写,既只能为其赋值。在存储过程中不能读它的值。返回时,将该形 参值传给相应的实参。 IN OUT:都允许。 Java代码 create or replace procedure updateauthssalary( 1. p_author_code in out auths.author_code%type, 2. p_salary in number, 3. p_name out auths.name%type) is 4. v_salary_temp number; --定义存储过程中的局部变量 5. begin 6. select salary into v_salary_temp from auths where author_code=p_author_code; 7. if v_salary_temp<300 then 8. update auths set salary=p_salary where author_code=p_author_code; 9. end if; 10. select name into p_name from auths where author code=p_author_code; 11. end updateauthssalary; 12. (1)参数的数据类型 在定义一个存储过程参数时,不能指定CHAR类型和VARCHAR2类型形参的长度,也不能指定 NUMBER形参的精度和标度。这些约束由实参来传递。 例如,下面的存储过程定义不合法,将产生一个编译错误: Java代码 create or replace procedure proc_auths( 1. --参数定义了类型长度,将产生编译错误。 2. p_code in out varchar2(6), 3. p_salary out number(8,2)) as 4. begin 5. select salary into p_salary from auths where author_code=p_code; 6. end proc_auths; 7. 修改上面存储过程的定义为: Java代码 create or replace procedure proc_auths( 1. --参数定义了类型长度,将产生编译错误。 分区 Oracle_SQL 的第 119 页 --参数定义了类型长度,将产生编译错误。 2. p_code in out varchar2, 3. p_salary out number) as 4. begin 5. select salary into p_salary from auths where author_code=p_code; 6. end proc_auths; 7. p_code的长度约束和p_salary的精度,标度约束由实参传递。 Java代码 delcare 1. v_code varchar2(6); 2. v_salary number(8,2); 3. begin 4. v_code:='A00001'; 5. proc_auths(v_code,v_salary); 6. end; 7. 注意,如果使用%type为参数定义类型,那么该参数具有定义在形参上而不是通过实参传递的 数据长度。 Java代码 create or replace procedure query_salary( 1. p_code in out auths.author_code%type, 2. p_salary out auths.salary%type) as 3. --那么由于author_code的长度为6,因此p_code的长度也为6。 (2)参数的传值方式 位臵表示法、名称表示法 如有这样的存储过程 Java代码 create or replace procedure insert_auths( 1. p_code auths.author_code%type, 2. p_name auths.name%type, 3. p_sex auths.sex%type, 4. p_birthdate auths.birthdate%type) as 5. 下面进行两种方法的调用: Java代码 declare 1. v_code varchar2(6); 2. v_name varchar2(12); 3. v_sex number(1); 4. v_birthdate date; 5. begin 6. v_code:='A00021'; 7. v_name:='张'; 8. v_sex:=1; 分区 Oracle_SQL 的第 120 页 v_sex:=1; 9. v_birthdate:='5-seq-70'; 10. --实参的位臵顺序与形参的位臵顺序相对应。---位臵表示法 11. insert_auths(v_code,v_name,v_sex,v_birthdate); 12. --实参名与形参名对应,这样就可以重新排列参数的先后顺序。---命名表示法 13. end; 14. 注意,位臵表示法和命名表示法在一些调用中也可以混合使用。但是,如果出现第一个用命名 表示法的参数时,后面的参数也必须使用命名表示法传值。 (3)参数的缺省值 如可以这样: p_entry_date_time auths.entry_date_time%type:sysdate, p_sex auths.sex%type default 1 2、创建函数 函数与存储过程非常类似,都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数 也可以不在于数据库中),并且在块中调用。 与存储过程不同,存储过程只能作为一个PL/SQL语句调用,而函数作为表达式的一部分调用。 并且它们的定义、可执行、异常处理部分是不同的。 例如,如作家表中男作家或女作家的工资在200元以上的人数大于百分之七十,则下面的函数返 回TRUE,否则返回FALSE: Java代码 create or replace function salarystat( 1. p_sex auths.sex%type) 2. return boolean is 3. v_currentsexauthors number; 4. v_maxauthors number; 5. v_returnvalue boolean; 6. v_percent constant number:=70; 7. begin 8. --获得满足条件的作家的最大数。 9. select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200; 10. select count(author_code) into v_currentsexauthors from auths where sex=p_se x; 11. if(v_maxauthors/v_currentsexauthors*100)>v_percent then 12. v_returnvalue:=true; 13. else 14. v_returnvalue:=false; 15. end if; 16. return v_returnvalue; 17. end salarystat; 18. 下面进行调用: Java代码 declare 1. cursor c_auths is 分区 Oracle_SQL 的第 121 页 cursor c_auths is 2. select distinct sex from auths; 3. begin 4. for v_authsrecord in c_auths loop 5. if salarystat(v_authsrecord.sex) then 6. update auths set salary=salary-50 where sex=v_authsrecord.sex; 7. end if; 8. end loop; 9. end; 10. return也可以用在存储过程中。在这种情况下,它没有参数。当执行了不带参数的return语句 后,立刻将控制返回到调用环境,并将OUT和IN OUT模式的形参的当前值传给实参,然后继续 执行调用存储过程后的语句。 在使用函数与存储过程时,一般情况下,如果只有一个返回值,则使用函数;如果有多个返回 值则使用存储过程。尽管函数的参数可以是OUT模式,但是一般不这样使用。 3、删除过程与函数 drop procedure procedurename; drop function functionname; 4、库存子程序和局部子程序 前面的子程序都是存储在数据库中的子程序,即库存子程序。这些子程序是由ORACLE命令创 建的,并可在其它的PL/SQL块中调用。它们在创建时要进行编译,并将编译后的代码存储在数 据库中。当子程序被调用时,编译后的代码从数据库中读出并执行。 一个子程序也可以在块的定义部分创建,这样的子程序被叫作局部子程序。 下面定义了一个局部函数formatname: Java代码 declare 1. cursor c_allauthors is 2. select name,sex from auths; 3. v_formattedname varchar2(60); 4. function formatname(p_name in varchar2,p_sex in number) 5. return varchar2 is 6. v_sex varchar2(16); 7. begin 8. if p_sex=1 then 9. v_sex:='男'; 10. else 11. v_sex:='女'; 12. end if; 13. return p_name||'('||v_sex||')'; 14. end formatname; 15. begin 16. for v_authsrecord in c_allauthors loop 17. v_formattedname:= 分区 Oracle_SQL 的第 122 页 v_formattedname:= 18. formatname(v_authsrecord.name,v_authsrecord.sex); 19. dbms_output.put_line(v_formattedname); 20. end loop; 21. end; 22. 如上例,在无名块的定义部分创建了formatname函数。这个函数只在创建它的块中可用,它的 作用域从创建它开始到结束。 局部子程序只能在定义部分的最后被创建,如果将formatname函数移到上面,将会出现编译错 误。子程序必须先定义再引用。 源文档 4、 建立过程: CREATE OR REPLACE PROCEDURE selemp(v_empno IN emp.empno%TYPE, v_sal out emp.sal%type) AS no_result EXCEPTION; BEGIN select sal into v_sal from emp where empno=v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_OUTPUT.PUT_LINE('emplooye number'||v_empno||'was selected!'); DBMS_OUTPUT.PUT_LINE('emplooye number'||v_empno||' salary is '||v_sal); EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE('no row found!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('other errors!'); END selemp ; 执行过程 23:34:21 SQL> var v_sal number 23:34:21 SQL> exec selemp(7789,v_sal); 分区 Oracle_SQL 的第 123 页 PL/SQL 学习 1、 PL/SQL循序渐进全面学习教程--Oracle 1 PL/SQL循序渐进全面学习教程--Oracle 课程 一 PL/SQL 基本查询与排序 本课重点: 1、写SELECT语句进行数据库查询 2、进行数学运算 3、处理空值 4、使用别名ALIASES 5、连接列 6、在SQL PLUS中编辑缓冲,修改SQL SCRIPTS 7、ORDER BY进行排序输出。 8、使用WHERE 字段。 一、写SQL 命令: 不区分大小写。 SQL 语句用数字分行,在SQL PLUS中被称为缓冲区。 最后以;或 / 结束语句。 也可以用RUN来执行语句 二、例1:SQL> SELECT dept_id, last_name, manager_id 2 FROM s_emp; 2:SQL> SELECT last_name, salary * 12, commission_pct 2 FROM s_emp; 对于数值或日期型的字段,可以进行相应的四则运算,优先级与标准的高级语言相同。 SQL> SELECT last_name, salary, 12 * (salary + 100) 2 FROM s_emp; 三、列的别名ALIASES: PL/SQL_学习 2011年2月26日 23:35 分区 Oracle_SQL 的第 124 页 三、列的别名ALIASES: 计算的时候特别有用; 紧跟着列名,或在列名与别名之间加“AS”; 如果别名中含有SPACE,特殊字符,或大小写,要用双引号引起。 例(因字体原因,读者请记住:引号为英文双引号Double Quotation): SQL> SELECT last_name, salary, 2 12 * (salary + 100) ”Annual Salary” 3 FROM s_emp; 四、连接符号:|| 连接不同的列或连接字符串 使结果成为一个有意义的短语: SQL> SELECT first_name || ’ ’ || last_name 2 || ’, ’|| title ”Employees” 3 FROM s_emp; 五、管理NULL值: SQL> SELECT last_name, title, 2 salary * NVL(commission_pct,0)/100 COMM 3 FROM s_emp; 此函数使NULL转化为有意义的一个值,相当于替换NULL。 六、SQL PLUS的基本内容,请参考 七、ORDER BY 操作: 与其他SQL92标准数据库相似,排序如: SELECT expr FROM table [ORDER BY {column,expr} [ASC|DESC]]; 从Oracle7 release 7.0.16开始,ORDER BY 可以用别名。 另:通过位置判断排序: SQL> SELECT last_name, salary*12 2 FROM s_emp 3 ORDER BY 2; 分区 Oracle_SQL 的第 125 页 这样就避免了再写一次很长的表达式。 另:多列排序: SQL> SELECT last name, dept_id, salary 2 FROM s_emp 3 ORDER BY dept_id, salary DESC; 八、限制选取行: SELECT expr FROM table [WHERE condition(s)] [ORDER BY expr]; 例1: SQL> SELECT first_name, last_name, start_date 2 FROM s_emp 3 WHERE start_date BETWEEN ’09-may-91’ 4 AND ’17-jun-91’; 例2: SQL> SELECT last_name 2 FROM s_emp 3 WHERE last_name LIKE ’_a%’; //显示所有第二个字母为 a的last_name 例3: 如果有列为NULL SQL> SELECT id, name, credit_rating 2 FROM s_customer 3 WHERE sales_rep_id IS NULL; 优先级: Order Evaluated Operator 1 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN) 2 AND 3 OR 总结:我们今天主要学习了如何进行查询SELECT操作,具体的组合查询与子查询将在以后的课堂中学 分区 Oracle_SQL 的第 126 页 总结:我们今天主要学习了如何进行查询SELECT操作,具体的组合查询与子查询将在以后的课堂中学 习,同时希望大家可以工作、学习中多多摸索,实践! 课程 二 PL/SQL PL/SQL 查询行函数 本课重点: 1、掌握各种在PL/SQL中可用的ROW函数 2、使用这些函数的基本概念 3、SELECT语句中使用函数 4、使用转换函数 注意:以下实例中标点均为英文半角 一、FUNCTION的作用: 进行数据计算,修改独立的数据,处理一组记录的输出,不同日期显示格式,进行数据类型转换 函数分为:单独函数(ROW)和分组函数 注意:可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出现。 语法:function_name (column|expression, [arg1, arg2,...]) 二、字符型函数 1、LOWER 转小写 2、UPPER 3、INITCAP 首字母大写 4、CONCAT 连接字符,相当于 || 5、SUBSTR SUBSTR(column|expression,m[,n]) 6、LENGTH 返回字符串的长度 7、NVL 转换空值 其中,1、2经常用来排杂,也就是排除插入值的大小写混用的干扰,如: SQL> SELECT first_name, last_name 2 FROM s_emp 3 WHERE UPPER(last_name) = ’PATEL’; FIRST_NAME LAST_NAME -------------------- -------------------- Vikram Patel Radha Patel 分区 Oracle_SQL 的第 127 页 三、数学运算函数 1、ROUND 四舍五入:ROUND(45.923,2) = 45.92 ROUND(45.923,0) = 46 ROUND(45.923,-1) = 50 2、TRUNC 截取函数 TRUNC(45.923,2)= 45.92 TRUNC(45.923)= 45 TRUNC(45.923,-1)= 40 3、MOD 余除 MOD(1600,300) 实例: SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM SYS.DUAL; 四、ORACLE 日期格式和日期型函数: 1、默认格式为DD-MON-YY. 2、SYSDATE是一个求系统时间的函数 3、DUAL〔'dju:el] 是一个伪表,有人称之为空表,但不确切。 SQL> SELECT SYSDATE 2 FROM SYS.DUAL; 4、日期中应用的算术运算符 例:SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS 2 FROM s_emp 3 WHERE dept_id = 43; DATE+ NUMBER = DATE DATE-DATE= NUMBER OF DAYS 分区 Oracle_SQL 的第 128 页 DATE-DATE= NUMBER OF DAYS DATE + (NUMBER/24) = 加1小时 5、函数: MONTHS_BETWEEN(date1, date2) 月份间隔,可正,可负,也可是小数 ADD_MONTHS(date,n) 加上N个月,这是一个整数,但可以为负 NEXT_DAY(date,‘char’) 如:NEXT_DAY (restock_date,’FRIDAY’),从此日起下个周五。 ROUND(date[,‘fmt’]) TRUNC(date[,‘fmt’]) 解释下面的例子: SQL> SELECT id, start_date, 2 MONTHS_BETWEEN (SYSDATE,start_date) TENURE, 3 ADD_MONTHS(start_date,6) REVIEW 4 FROM s_emp 5 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48; 我们看到: MONTHS_BETWEEN (SYSDATE,start_date)<48,说明至今工作未满一年的员工。 LAST_DAY (restock_date) 返回本月的最后一天 SQL> select round(sysdate,'MONTH') from dual ROUND(SYSD ---------- 01-11月-01 round(sysdate,'YEAR') = 01-1月 -02 ROUND 之后的值比基值大的最小符合值,大家可以用更改系统时间的方法测试,以15天为分界线,也是非常 形象的四舍五入,而TRUNC恰好相反,是对现有的日期的截取。 五、转换函数: 1、TO_CHAR 使一个数字或日期转换为CHAR 2、TO_NUMBER 分区 Oracle_SQL 的第 129 页 把字符转换为NUMBER 3、TO_DATE 字符转换为日期 这几个函数较为简单,但要多多实践,多看复杂的实例。 SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED 2 FROM s_ord 3 WHERE sales_rep_id = 11; 转换时,要注意正确的缺省格式: SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL;//正确 SELECT TO_DATE('031092') CORRECT FROM DUAL;//不正确 SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL 输出 3月10日 SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL 输出 10月3日 4、实例: select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL; TODAYS -------------------------------- SIXTEENTH of 11月 2001 下午 大小写没有什么影响,引号中间的是不参与运算。 实例 : SELECT ROUND(SALARY*1.25) FROM ONE_TABLE; 意义:涨25%工资后,去除小数位。在现实操作中,很有意义。 5、混合实例: SQL> SELECT last_name, TO_CHAR(start_date, 2 ’fmDD ”of” Month YYYY’) HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE ’%91’; LAST_NAME HIREDATE 分区 Oracle_SQL 的第 130 页 LAST_NAME HIREDATE ------------ -------------------- Nagayama 17 of June 1991 Urguhart 18 of January 1991 Havel 27 of February 1991 这里要注意:fmDD 和 fmDDSPTH之间的区别。 SQL> SELECT id, total, date_ordered 2 FROM s_ord 3 WHERE date_ordered = 4 TO_DATE(’September 7, 1992’,’Month dd, YYYY’); 六、独立的函数嵌套 SQL> SELECT CONCAT(UPPER(last_name), 2 SUBSTR(title,3)) ”Vice Presidents” 3 FROM s_emp 4 WHERE title LIKE ’VP%’; * 嵌套可以进行到任意深度,从内向外计算。 例: SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS 2 (date_ordered,6),’FRIDAY’), 3 ’fmDay, Month ddth, YYYY’) 4 ”New 6 Month Review” 5 FROM s_ord 6 ORDER BY date_ordered; SQL> SELECT last_name, 2 NVL(TO_CHAR(manager_id),’No Manager’) 3 FROM s_emp 4 WHERE manager_id IS NULL; 对于例子,大家重要的理解,并多做测试,并注意英文版和中文版在日期上的区别。 分区 Oracle_SQL 的第 131 页 有些教材上的例子,不要盲目的相信其结果,实践后才有发言权,希望大家能够在学习的过程中不要忽略了 用,多想一想为什么实例要如此设计,在何种情况下应用此实例来解决问题。这样,我们才真正掌握了知识。 课程 四 组函数 本课重点: 1、了解可用的组函数 2、说明每个组函数的使用方法 3、使用GROUP BY 4、通过HAVING来限制返回组 注意:以下实例中标点均为英文半角 一、概念: 组函数是指按每组返回结果的函数。 组函数可以出现在SELECT和HAVING 字段中。 GROUP BY把SELECT 的结果集分成几个小组。 HAVING 来限制返回组,对RESULT SET而言。 二、组函数:(#号的函数不做重点) 1、AVG 2、COUNT 3、MAX 4、MIN 5、STDDEV # 6、SUM 7、VARIANCE # 语法: SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 实例1:一个混合实例,说明所有问题: SQL> SELECT AVG(salary), MAX(salary), MIN(salary), 2 SUM(salary) 3 FROM s_emp 4 WHERE UPPER(title) LIKE ’SALES%’; AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) ----------- ----------- ----------- ----------- 1476 1525 1400 7380 说明:很多函数,我们在讲函数的已经向大家介绍过,但在此为何叫分组函数呢,主要是因为它们可以与 GROUP BY来形成对不同组的计算,相当于在很多值中进行挑选。 * MIN MAX函数可以接任何数据类型。 如果是MIN(last_name), MAX(last_name),返回的是什么呢? 千万记住,不是指LAST_NAME的长度,而是指在FIRST字母的前后顺序,第一个相同,然后比较第二个, 如:xdopt > cssingkdkdk > adopt > acccc 实例2: SQL> SELECT COUNT(commission_pct) 2 FROM s_emp 3 WHERE dept_id = 31; 返回所有非空行个数 三、GROUP BY的应用: 先看一个简单实例: SQL> SELECT credit_rating, COUNT(*) ”# Cust” 2 FROM s_customer 3 GROUP BY credit_rating; 注意这里别名的应用,复习一下从前的课程,加了引号后,就可以用特殊字符,但也仅有三个:#$_,什 么对象的名字都如此。当然空格也是可以的。 复杂实例: 分区 Oracle_SQL 的第 132 页 复杂实例: SQL> SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE ’VP%’ 4 GROUP BY title 5 ORDER BY SUM(salary); 这里要注意一下几个CLAUSE的先后次序。 WHERE在这里主要是做参与分组的记录的限制。 **另外,如果要选取出来一个不加组函数的列,如上面的TITLE,就要把这个列GROUP BY !否则要出错的! 信息为:ERROR at line 1: ORA-00937: not a single-group group function 理论很简单,如果不GROUP BY TITLE,显示哪一个呢?这个在试题中经常出现。 结论:不加分组函数修饰的列必定要出现在GROUP BY 里。 错误实例: SQL> SELECT dept_id, AVG(salary) 2 FROM s_emp 3 WHERE AVG(salary) > 2000 4 GROUP BY dept_id; WHERE AVG(salary) > 2000 * ERROR at line 3: ORA-00934: group function is not allowed here 应在GROUP BY 后面加上HAVING AVG(salary) > 2000; 因为是用来限制组的返回。 多级分组实例: SQL> SELECT dept_id, title, COUNT(*) 2 FROM s_emp 3 GROUP BY dept_id, title; 就是先按照DEPT_ID分组,当DEPT_ID相同的时候,再按TITLE分组,而COUNT(*)以合成的组计数。 顺序对结果有决定性的影响。 总结:本课我们主要学习了分组函数的使用及如何进行分组查询,我们可以想像一下,SQL SERVER中有 COMPUTE BY,来进行分组总数的计算,但在ORACLE中是没有的。大家可以建立一个有多个列,多个重复值的 表,然后进行各种分组的演示,用得多了,自然明了。 课程 五 子查询 本课重点: 1、在条件未知的情况下采用嵌套子查询 2、用子查询做数据处理 3、子查询排序 注意:以下实例中标点均为英文半角 一、概述: 子查询是一种SELECT句式中的高级特性,就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查 询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。 子查询可以用在WHERE子句,HAING子句,SELECT或DELETE语句中的FROM 子句。 注意:1、子查询必须在一对圆括号里。 2、比较符号:>, =, 或者 IN. 3、子查询必须出现在操作符的右边 4、子查询不能出现在ORDER BY里 (试题中有时出现找哪行出错) 二、子查询的执行过程: NESTED QUERY-----> MAIN QUERY SQL> SELECT dept_id SQL> SELECT last_name, title 2 FROM s_emp 2 FROM s_emp 3 WHERE UPPER(last_name)=’B每个查询只运行一次。当然,子查询要首先被执行IRI’; 3 WHERE dept_id 分区 Oracle_SQL 的第 133 页 3 WHERE UPPER(last_name)=’B每个查询只运行一次。当然,子查询要首先被执行IRI’; 3 WHERE dept_id = 这里 ,,大家设想一下,如果子查询中有一个以上的人的LASTNAME为BIRI,会如何?-----会出错,因为不 能用=来连接。 ORA-1427: single-row subquery returns more than one row 以上的查询也被称之为 单行子查询。 DELECT子查询实例: delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=( select pro_name from new_product where pro_addr in ('bj','sh')) 三、子查询中的GROUP 函数的应用 实例 1: SQL> SELECT last_name, title, salary 2 FROM s_emp 3 WHERE salary < 4 (SELECT AVG(salary) 5 FROM s_emp); 实例2: 选择出工资最高的员工的家庭住址: select emp_addr from employees where salary = (select max(salary) from employees); 这是一个简单实用的例子,可以衍生出很多情况,在实际应用经常出现,请大家多多思考。 实例3: SQL> SELECT dept_id, AVG(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING AVG(salary) > 5 (SELECT AVG(salary) 6 FROM s_emp 7 WHERE dept_id = 32); 子查询被多次执行,因为它出现在HAVING 子句中。 SQL> SELECT title, AVG(salary) 2 FROM s_emp 3 GROUP BY title 4 HAVING AVG(salary) = 5 (SELECT MIN(AVG(salary)) 6 FROM s_emp 7 GROUP BY title); 对子查询,我们了解这么多在理论上已经覆盖了所有的知识点,对于UPDATE 和DELETE的子查询,不作为重 点,但也要练习掌握。今天到这,谢谢大家。 课程 六 运行时应用变量 本课重点: 1、创建一个SELECT语句,提示USER在运行时先对变量赋值。 2、自动定义一系列变量,在SELECT运行时进行提取。 3、在SQL PLUS中用ACCEPT定义变量 注意:以下实例中标点均为英文半角 一、概述: 分区 Oracle_SQL 的第 134 页 一、概述: 变量可以在运行时应用,变量可以出现在WHERE 字段,文本串,列名,表名等。 1、我们这里的运行时,指的是在SQL PLUS中运行。 2、ACCEPT :读取用户输入的值并赋值给变量 3、DEFINE:创建并赋值给一个变量 4、在做REPORT时经常使用,比如对某个部门的销售信息进行统计,部门名称可以以变量代替。 SQL PLUS不支持对输入数据的有效性检查,因此提示要简单且不模棱两可。 二、应用实例: 1、SQL> SELECT id, last_name, salary 2 FROM s_emp 3 WHERE dept_id = &department_number; 2、可以在赋值前后进行比较: SET VERIFY ON ..... 1* select * from emp where lastname='&last_name' 输入 last_name 的值: adopt 原值 1: select * from emp where lastname='&last_name' 新值 1: select * from emp where lastname='adopt' ----如果在原语句中没有单引号,那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加 上单引号。 SET VERIFY OFF 之后,原值和新值这两句消失。这在ORACLE8I中是默认为ON。 3、子句为变量:WHERE &condition; 要注意引号 三、DEFINE和ACCEPT的应用: 1、SET ECHO OFF //使内容不 显示在用户界面 ACCEPT p_dname PROMPT ’Provide the department name: ’ SELECT d.name, r.id, r.name ”REGION NAME” FROM s_dept d, s_region r WHERE d.region_id = r.id AND UPPER(d.name) LIKE UPPER(’%&p_dname%’) / SET ECHO ON 分区 Oracle_SQL 的第 135 页 SET ECHO ON 存为文件:l7prompt.SQL SQL> START l7prompt Provide the department name: sales 2、SQL> DEFINE dname = sales SQL> DEFINE dname DEFINE dname = ”sales” (CHAR) SQL> SELECT name 2 FROM s_dept 3 WHERE lower(name) = ’&dname’; 可以正常执行了。 SQL> DEFINE dname 主要是显示当前的变量是否赋值,值是什么。当然,我们可以用UNDEFINEGO 来使变量恢 复初始,不然它会一直保持下去。 3、如果变量在SQL SCRIPT文件中确定 :可以SQL> START l7param President 来赋值。 总结:本课主要针对较古老的SQLPLUS方法,在REPORT和结果集生成方面使用变量,达到方便操作,动态修改 的目的。 课程 七 其他数据库对象 SEQUENCE 创建实例: SQL> CREATE SEQUENCE s_dept_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6 NOCYCLE; Sequence created. 1、NEXTVAL和CURRVAL的用法 分区 Oracle_SQL 的第 136 页 1、NEXTVAL和CURRVAL的用法 只有在INSERT 中,才可以作为子查询出现。 以下几个方面不可用子查询: SELECT 子句OF A VIEW 有DISTINCT的出现的SELECT。 有GROUP BY,HAVING,ORDER BY的SELECT 子句。 SELECT 或DELETE,UPDATE 中的子查询。 DEFAULT选项中不能用。 2、编辑SEQUENCE 只有OWNER或有ALTER权限的用户才能修改SEQUENCE 未来的NUMBER受修改的影响。 不能修改START WITH,如果变,则要RE-CREATE。 修改会受到某些有效性检验的限制,如MAXVALUE 3、删除: DROP SEQUENCE sequence; ORACLE对象之INDEX 一、INDEX概述: 是ORACLE的一种数据对象,用POINTER来加速查询行。通过快速路径存取方法定位数据并减少I/O。 INDEX独立于表。INDEX由ORACLE SERVER来使用和保持。 二、索引如何建立? 1、自动:通过PRIMARY KEY和UNIQUE KEY约束来建立。 2、用户手工建立非唯一性索引。 三、创建方法: 语法:CREATE INDEX index ON table (column[, column]...); 何时建立INDEX: 此列经常被放到WHERE字段或JOIN来作条件查询。 此列含有大量的数据。 此列含有大量的空值。 两个或几个列经常同时放到WHERE字段进行组合查询 分区 Oracle_SQL 的第 137 页 两个或几个列经常同时放到WHERE字段进行组合查询 表很大而且只有少于2-4% 的ROW可能被查询的时候。 以下情况不要建立索引: 表很小; 表被更新频繁。 四、查看已经存在的索引: 1、USER_INDEXES可以查询索引名和类型。 2、USER_IND_COLUMNS包含索引名、表名、列名。 实例: SQL> SELECT ic.index_name, ic.column_name, 2 ic.column_position col_pos, ix.uniqueness 3 FROM user_indexes ix, user_ind_columns ic 4 WHERE ic.index_name = ix.index_name 5 AND ic.table_name = ’S_EMP’; 五、删除索引: DROP INDEX index; SYNONYMS 同义词 语法: CREATE [PUBLIC] SYNONYM synonym for object; 注意:此对象不能包含在一个包里; 一个私有的同义词不能与同一USER的其他对象重名。 DROP SYNONYM D_SUM; 课程 八 用户访问控制 本课重点: 1、创建用户 2、创建角色来进行安全设置 3、使用GRANT或REVOKE 来控制权限 注意:以下实例中标点均为英文半角 一、概述: ORACLE通过用户名和密码进行权限控制。 数据库安全:系统安全和数据安全 系统权限:使用户可以访问数据库 对象权限:操纵数据库中的对象 SCHEMA:各种对象的集合 分区 Oracle_SQL 的第 138 页 SCHEMA:各种对象的集合 二、系统权限: 1、超过80个权限可用。 2、DBA有最高的系统权限: CREATE NEW USER REMOVE USERS REMOVE ANY TABLE BACKUP ANY TABLE 三、创建用户 1、CREATE USER user IDENTIFIED BY password; 2、系统权限:CREATE SESSION Connect to the database. CREATE TABLE Create tables in the user’s schema. CREATE SEQUENCE Create a sequence in the user’s schema. CREATE VIEW Create a view in the user’s schema. CREATE PROCEDURE Create a stored procedure, function, or package in the user’s schema. 3、授权用户系统权限: GRANT privilege [, privilege...] TO user [, user...]; GRANT CREATE TABLE TO SCOTT; 四、角色的使用 1、概念:角色是一组权限的命名,可以授予给用户。这样就如同给了某个用户一个权限包。 2、创建、授予给角色: CREATE ROLE MANAGER; GRANT CREATE TABLE,CREATE VIEW TO MANAGER; GRANT MANAGER TO CLARK 五、修改密码: ALTER USER user IDENTIFIED BY password; 六、对象权限: 1、语句: GRANT {object_priv(, object_priv...)|ALL}[(columns)] ON object TO {user[, user...]|role|PUBLIC} [WITH GRANT OPTION]; 2、实例: 最简单: SQL> GRANT select 2 ON s_emp 3 TO sue, rich; 稍复杂: SQL> GRANT update (name, region_id) 2 ON s_dept 3 TO scott, manager; SQL> GRANT select, insert 2 ON s_dept 3 TO scott 4 WITH GRANT OPTION; 课程 九 声明变量 本课重点: 1、了解基本的PLSQL块和区域 2、描述变量在PLSQL中的重要性 3、区别PLSQL与非PLSQL变量 4、声明变量 5、执行PLSQL块 注意:以下实例中标点均为英文半角 一、概述: 分区 Oracle_SQL 的第 139 页 一、概述: 1、PLSQL 块结构: DECLARE --- 可选 变量声明定义 BEGIN ---- 必选 SQL 和PLSQL 语句 EXCEPTION ---- 可选 错误处理 END;---- 必选 二、实例: declare vjob varchar(9); v_count number:=0; vtotal date:=sysdate +7; c_tax constant number(3,2):=8.25; v_valid boolean not null:=true; begin select sysdate into vtotal from dual; end; / 上例中,如果没有这个SELECT语句,会如何? 出错,说明必须有STATEMENTS 如果: select sysdate from dual into vtotal ; 同样,也不行。而且变量与赋值的类型要匹配。 三、%TYPE的属性 声明一个变量使之与数据库某个列的定义相同或与另一个已经定义过的变量相同 所以%TYPE要作为列名的后缀:如: v_last_name s_emp.last_name%TYPE; v_first_name s_emp.first_name%TYPE; --这样做的好处是我们不必去知晓此列的类型与定义 或:v_balance NUMBER(7,2); v_minimum_balance v_balance%TYPE := 10; 四、声明一个布尔类型的变量 1 只有TRUE、FALSE、NULL可以赋值给BOOLEAN变量 2 此变量可以接逻辑运算符NOT、AND、OR。 3、变量只能产生TRUE、FALSE、NULL。 实例: VSAL1:=50000; VSQL2:=60000; VCOMMSAL BOOLEAN:=(VSAL1 variable n number SQL> print n :n=v_sal /12; :n这个加了:前缀的变量不是PLSQL变量,而是HOST。 七、以下几个PLSQL声明变量,哪个不合法? A 、DECLARE V_ID NUMBER(4); B、DECLARE V_X,V_Y,V_Z VARCHAR2(9); C、DECLARE V_BIRTH DATE NOT NULL; D、DECLARE V_IN_STOCK BOOLEAN:=1; E、DECLARE TYPE NAME_TAB IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; DEPT_NAME NAME_TAB; 分区 Oracle_SQL 的第 140 页 DEPT_NAME NAME_TAB; 上面的习题我会在下章给出答案,这也正是声明变量的规则和难点。 课程 十 写执行语句 本课重点: 1、了解PLSQL执行区间的重要性 2、写执行语句 3、描述嵌套块的规则 4、执行且测试PLSQL块 5、使用代码惯例 注意:以下实例中标点均为英文半角 一、PLSQL 块的语法规则: 1、语句可以跨跃几行。 2、词汇单元可以包括:分隔符、标识符、文字、和注释内容。 3、分隔符: +-*/=<>||.... 4、标识符: 最多30个字符,不能有保留字除非用双引号引起。 字母开头,不与列同名。 5、文字串:如 V_ENAME:='FANCY';要用单引号括起来。 数值型可以用简单记数和科学记数法。 6、注释内容:单行时用-- 多行用/* */ 与C很相似 二、SQL函数在PL/SQL的使用: 1、可用的: 单行数值型、字符型和转换型,日期型。 2、不可用的: 最大、最小、DECODE、分组函数。 实例: BEGIN SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP; END; V_comment:=user||':'||sysdate; -- 会编译出错 V_comment:=user||':'||to_char(sysdate); --正确 如果有可能,PLSQL都会进行数据一致性的转换,但ORACLE推荐你应该进行显示的转换,因为这样会提高性 能。 三、嵌套块和变量作用区域 1、执行语句允许嵌套时嵌套。 2、嵌套块可以看作正常的语句块。 3、错误处理模块可以包括一个嵌套块 4、exponential指数 逻辑、算数、连接、小括号 5、看正面实例: declare vjob varchar(9); v_count number:=0; vtotal date:=sysdate +7; c_tax constant number(3,2):=8.25; v_valid boolean not null:=true; ttt vtotal%type; begin --select sysdate into vtotal from dual;--体会有无此句与结果的影响 dbms_output.put_line (vtotal); end; / 注意:在执行块之前,要在SQL PLUS中执行:SET SERVEROUTPUT ON 三、以实例来说明函数的参数声明作用域 declare 分区 Oracle_SQL 的第 141 页 declare v_weight number(3):=600; v_message varchar2(255):='product10000'; begin declare --sub-block v_weight number(3):=1; v_message varchar2(255):='pro300'; begin v_weight:=v_weight +1; end; v_weight:=v_weight +1; v_message:=v_message || 'my name'; end; / 子块中的V_WEIGHT值为 2 我们可以在子块中加入:dbms_output.put_line('subblock value is '||v_weight); 在主体中加入:dbms_output.put_line('main value is '||v_weight); 我们发现MAINBLOCK中V_WEIGHT为 601 改动: 1、在主块的声明中加 v_date date default sysdate; 在子块中加入:dbms_output.put_line('subblock date value is '||v_date); 执行结果:subblock date value is 22-11月-01 ****说明:主块中的变量,如果子块中没有同名变量声明,则继承主块中的声明和初始化值; 2、在子块中加入:v_sub char(9); dbms_output.put_line('subblock char value is '||v_sub); 此时正常输出。 在主块中加入:dbms_output.put_line('main char value is '||v_sub); 输出:ORA-06550: 第 21 行, 第 45 列: PLS-00201: 必须说明标识符 'V_SUB' 说明: 子块中声明的变量主块中并不知晓,因此出错。 了解了此实例,一切情况的变量的值的走向就都明了了。 课程 十一 与ORACLE SERVER交互 本课重点: 1、在PLSQL中成功的写SELECT语句 2、动态声明PLSQL变量类型与SIZE 3、在PLSQL中写DML语句 4、在PLSQL中控制事务 5、确定DML操作的结果 注意:以下实例中标点均为英文半角 一、PLSQL中的SQL语句: SELECT、DML、COMMIT、ROLLBACK、SAVEPOINT、CURSOR 特殊强调:PLSQL不支持DCL,不要问为什么。(DBMS_SQL package allows you to issue DDL and DCL statements.) 二、SELECT SELECT select_list INTO variable_name | record_name FROM table WHERE condition; 例: SQL> r 1 declare 2 v_deptno number(2); 分区 Oracle_SQL 的第 142 页 2 v_deptno number(2); 3 v_loc varchar2(15); 4 begin 5 select deptno,loc 6 into v_deptno,v_loc 7 from dept 8 where dname='SALES'; 9 DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); 10* end; 30 and CHICAGO 选取字段与变量个数和类型要一致。声明的变量一定要在SIZE上大于返回的赋值,否则提示缓冲区溢出。 如果SELECT语句没有返回值:ORA-01403: 未找到数据 ORA-06512: 在line 5 如果有多个值返回:ORA-01422: 实际返回的行数超出请求的行数 这些我们到了错误处理时会逐一讲解。 例: 上面的例子可以改为: declare v_deptno dept.deptno%type; v_loc dept.loc%type; begin select deptno,loc into v_deptno,v_loc from dept where dname='SALES'; DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); end; / 这样,可以在未知其他字段大小和类型的时候定义变量,提高兼容性。 三、DML 操作: 1、实例: declare v_empno emp.empno%type; begin select max(empno) into v_empno from emp; v_empno:=v_empno+1; insert into emp(empno,ename,job,deptno) values(v_empno,'asdfasdf','ddddd',10); end; / 这样也可以实现如SEQUENCE一样的编号唯一递增。 2、更新和删除: 这个较为简单: DECLARE V_DEPTNO EMP.DEPtno%type :=10; begin delete from emp where deptno=v_deptno; end; / 大家多多实践即可掌握。 PLSQL首先检查一个标识符是否是一个数据库的列名,如果不是,再假定它是一个PLSQL的标识符。所以如果 一个PLSQL的变量名为ID,列中也有个ID,如: SELECT date_ordered, date_shipped INTO date_ordered, date_shipped FROM s_ord WHERE id = id; 就会返回TOO MANY ROWS,这是要尽量避免的。 分区 Oracle_SQL 的第 143 页 就会返回TOO MANY ROWS,这是要尽量避免的。 四、SQL CURSOR 游标是一个独立SQL工作区,有两种性质的游标: 隐式游标: 当PARSE 和EXECUTE 时使用隐式游标。 显式游标: 是由程序员显式声明的。 游标的属性: SQL%ROWCOUNT:一个整数值,最近SQL语句影响的行数。 SQL%FOUND BOOLEAN属性,如果为TRUE,说明最近的SQL STATEMENT有返回值。 SQL%NOTFOUND 与SQL%FOUND相反 SQL%ISOPEN 在隐式游标中经常是FALSE,因为执行后立即自动关闭了。 SQL> variable row_de number SQL> r 1 declare 2 v_deptno number:=10; 3 begin 4 delete from emp where 5 deptno=v_deptno; 6 :row_de:=sql%rowcount; 7* end; PL/SQL 过程已成功完成。 SQL> print row_de --这是一个SQL PLUS变量 ROW_DE ---------- 4 这时其实并没有真正的删除,而是需要 COMMIT或ROLLBACK,来完成事务。 课程 十二 编写控制结构语句 本课重点: 1、结构控制的的用途和类型 2、IF 结构 3、构造和标识不同的循环 4、使用逻辑表 5、控制流和嵌套 注意:以下实例中标点均为英文半角 一、控制执行流 可以是分支和循环:IF THEN END IF IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; 例子:IF V_ENAME='OSBORNE' THEN V_MGR:=22; END IF; 这里我们可以注意,PLSQL和C语言或JAVA在条件上的不同,=代表关系运算,而:=代表赋值。 看一个函数: create FUNCTION calc_val (v_start IN NUMBER) RETURN NUMBER IS BEGIN IF v_start > 100 THEN RETURN (2 * v_start); ELSIF v_start >= 50 THEN 分区 Oracle_SQL 的第 144 页 ELSIF v_start >= 50 THEN RETURN (.5 * v_start); ELSE RETURN (.1 * v_start); END IF; END calc_val; 现在,虽然我们尚未讲解CREATE 函数或过程,但可以看到IF 条件在其中的作用。 二、注意LOGIC TABLE中的逻辑对应关系 1、NOT、AND、OR 2、任何表达式中含有空值结果都为 NULL 3、连接字符串中含有空值会把NULL作为 EMPTY STRING declare v_deptno dept.deptno%type; v_loc dept.loc%type; V_FLAG BOOLEAN ; V_REC BOOLEAN :=FALSE; --此值改为TRUE、NULL、FALSE进行不同的比较 V_AVA BOOLEAN:=NULL; begin V_FLAG:=V_REC AND V_AVA; IF V_FLAG=TRUE THEN DBMS_OUTPUT.PUT_LINE ('TRUE'); ELSIF V_FLAG=FALSE THEN DBMS_OUTPUT.PUT_LINE ('FALSE'); ELSE DBMS_OUTPUT.PUT_LINE ('NULL'); END IF; end; / 值得注意的是:NULL AND FALSE ---> FALSE 这是在实践中总结出来的。 三、基本循环基础: 1、LOOP statement1; statement2; . . . EXIT [WHEN condition]; END LOOP; v_ord_id s_item.ord_id%TYPE := 101; v_counter NUMBER (2) := 1; BEGIN . . . LOOP INSERT INTO s_item (ord_id, item_id) VALUES (v_ord_id, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; 2、FOR循环: FOR index IN [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . . END LOOP; 实例:DECLARE V_LOWER NUMBER:=1; V_UPPER NUMBER:=23; BEGIN DBMS_OUTPUT.PUT_LINE(''); FOR I IN V_LOWER..V_UPPER LOOP 分区 Oracle_SQL 的第 145 页 FOR I IN V_LOWER..V_UPPER LOOP DBMS_OUTPUT.PUT_LINE(I); END LOOP; END; / 3、WHILE 循环: WHILE condition LOOP statement1; statement2; . . . END LOOP; 4、循环是可以多层嵌套的。可以用<
还剩259页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 6 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

lofe

贡献于2015-09-03

下载需要 6 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf