一、oracle 常用的函数及关键字

1、集合操作

1）minus 差集

2）intersect 交集

3）UNION 并集，会去重

4）UNION ALL 并集，不去重

2、事物

1）COMMIT (事务提交)

2）ROLLBACK（事务回退）

3）SAVEPOINT（设置保存点命令）

3、函数

（1）常用字符串函数：

--1）LOWER(char)--将字符串表达式char中的所有大写字母转换为小写字母select LOWER('HTTP://WWW.JELLYTHINK.COM') from dual; -- http://www.jellythink.com--2）UPPER(char)--将字符串表达式char中的所有小写字母转换为大写字母select UPPER('http://www.jellythink.com') from dual; -- HTTP://WWW.JELLYTHINK.COM--3）INITCAP(char)--首字母转换成大写select INITCAP('jelly think') from dual; -- Jelly Think--4）SUBSTR(char, start, length) --返回字符串表达式char中从第start开始的length个字符select SUBSTR('http://www.jellythink.com', 12, 10) from dual; -- jellythink(注：下标从1开始)--5）LENGTH(char)--返回字符串表达式char的长度select LENGTH('JellyThink') from dual; -- 10--6）ASCII(char)--取char的ASCII值select ASCII('A') from dual; -- 65--7）CHR(number)--取number的ASCII值select CHR(65) from dual; -- A--8）REPLACE(char,search_str[,replacement_str])--将字符串char中的子串search_str替换成replacement_str；如果search_str=null，返回char；如果replacement_str=null，则会去掉char中的search_strselect REPLACE('jellythink', 'think', ' is good') from dual; -- jelly is good--9）INSTR(char1,char2[,n[,m]])--获取子串char2在字符串char1中的位置。n为其实搜索位置，m为子串出现的次数；n为负，则从尾部开始搜索；n\m默认为1select INSTR('JellyThink', 'Jelly', 1) from dual; -- 1--10）LPAD(char1,n,char2)--在字符串char1的左端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符select LPAD('JellyThink', 12, '*') from dual; -- **JellyThink--11）RPAD(char1,n,char2)--在字符串char1的右端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符select RPAD('JellyThink', 12, '*') from dual; -- JellyThink**--12）LTRIM(char1[,set])--去掉字符串char1左端包含的set中的任意字符select LTRIM('**JellyThink', '*') from dual; -- JellyThink--13）RTRIM(char1[,set])--去掉字符串char1右端包含的set中的任意字符select RTRIM('JellyThink**', '*') from dual; -- JellyThink--14）TRIM(char | char From string)--从字符串的头尾或者两端截断特定字符select TRIM('  JellyThink  ') from dual; -- JellyThink(注：默认去掉空格)--15）CONCAT(str1,str2)--连接字符串，同||的作用一样select CONCAT('Jelly', 'Think') from dual; -- JellyThink--16）translate(string,from_str,to_str);Select translate(‘abcd’,’bd’,’24’) from dual --等同于 Select replace(replace(‘abcd’,’b’,’2’),’d’,’4’) from dual--取字符串中 非数字部分select translate('456asd啊哦额123','#1234567890','#') from dual;--取字符串中 数字部分select translate('456asd啊哦额123','1234567890' || '456asd啊哦额123' ,'1234567890') from dual;

（2）常用的日期函数

--1)SYSDATE,SYSTIMESTAMP 返回系统当前日期,时间 ，时间戳select TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mi:ss') from dual; -- 2015-09-29 15:14:44--2)NEXT_DAY(day,char) 返回指定日期day后的第一个工作日char所对应的日期select NEXT_DAY(SYSDATE, '星期一') from dual; -- 2015/10/5 15:16:46--3)LAST_DAY(day) 返回day日期所指定月份中最后一天所对应的日期select LAST_DAY(SYSDATE) from dual; -- 2015/9/30 15:17:23--4)ADD_MONTHS(day,n) 返回day日期在n个月后(n为正数)或前(n为负数)的日期select ADD_MONTHS(SYSDATE, 2) from dual; -- 2015/11/29 15:18:39--5)MONTHS_BETWEEN(day1,day2) 返回day1日期和day2日期之间相差得月份select MONTHS_BETWEEN(SYSDATE, SYSDATE) from dual; -- 0select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) from dual; -- -4--6)ROUND(day[,fmt]) 返回日期的四舍五入结果。如果fmt指定年度，则7月1日为分界线；如果fmt指定月，则16日为分界线；如果指定天，则中午12:00为分界线，默认舍入到日select ROUND(SYSDATE) from dual; -- 2015/9/30select ROUND(SYSDATE, 'YEAR') from dual; -- 2016/1/1select ROUND(SYSDATE, 'MONTH') from dual; -- 2015/10/1--7)TRUNC(day,[,fmt]) 日期截断函数。如果fmt指定年度，则结果为本年度的1月1日；如果为月，则将结果为本月1日，默认截断到日select TRUNC(SYSDATE) from dual; -- 2015/9/29select TRUNC(SYSDATE, 'YEAR') from dual; -- 2015/1/1select TRUNC(SYSDATE, 'MONTH') from dual; -- 2015/9/1--8)CURRENT_DATE 返回当前会话时区所对应日期时间select CURRENT_DATE from dual; -- 2015/9/29 15:22:44--9)EXTRACT--从日期中获取所需要的特定数据select EXTRACT(YEAR from SYSDATE) from dual; -- 2015select EXTRACT(MONTH from SYSDATE) from dual; -- 9select EXTRACT(DAY from SYSDATE) from dual; -- 29

（3）常用类型转换函数

--1)TO_CHAR 将一个数字或日期转换成字符串select TO_CHAR(100) from dual; -- 100select TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') from dual; -- 2015-09-29 15:36:24--2)TO_NUMBER 将字符型数据转换成数字型数据select TO_NUMBER('10') from dual; -- 10select TO_NUMBER('JellyThink') from dual; -- 无效数字--3)TO_DATE 将字符型数据转换为日期型数据select TO_DATE('2015-9-29', 'YYYY-MM-DD') from dual; -- 2015/9/29--4)CAST 将一种built-in类型转换成另一种built-in类型select CAST('100' as NUMBER) from dual; -- 100select CAST(2 as char) from dual; -- 2

（4）聚合函数

--COUNT、AVG等聚合函数不统计null的情况WITH TT AS (  SELECT 1 AS ID , 15 AS VALUE1 FROM DUAL UNION ALL  SELECT 2 AS ID , 12 AS VALUE1 FROM DUAL UNION ALL  SELECT 3 AS ID , 11 AS VALUE1 FROM DUAL UNION ALL  SELECT 4 AS ID , 16 AS VALUE1 FROM DUAL UNION ALL  SELECT 5 AS ID , 8 AS VALUE1 FROM DUAL UNION ALL  SELECT 6 AS ID , 20 AS VALUE1 FROM DUAL UNION ALL  SELECT NULL AS ID , 11 AS VALUE1 FROM DUAL )SELECT SUM(ID), COUNT(*), COUNT(ID), AVG(ID), AVG(NVL(ID, 0)) FROM TT

（5）其他常用的函数

--1)decode(expression , search , result [, search , result]... [, default]) IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合select DECODE(20, 10, 5, 200, 10, 20, 30) from dual; -- 30--2)SIGN(number) 如果number大于0，SIGN则返回1；如果number小于0，SIGN则返回-1；如果number等于0，SIGN则返回0select SIGN(20) from dual; -- 1select SIGN(-30) from dual; -- -1select SIGN(0) from dual; -- 0--3)TRUNC(number, [ decimal_places ]) number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。 如果此参数缺省，默认保留0位小数select TRUNC(20.2183, 2) from dual; -- 20.21select TRUNC(20.1, 4) from dual; -- 20.1--4)GREATEST(expr1[,expr2]...) 返回表达式中值最大的一个select GREATEST(20, 100, 30, 20, 40, 400) from dual; -- 400--5)LEAST(expr1[,expr2]...) 返回表达式中值最小的一个select LEAST(20, 100, 30, 20, 40, 400) from dual; -- 20--6)NULLIF(expr1,expr2) 如果expr1=expr2；则返回null，否则返回expr1select NULLIF(20, 20) from dual; -- NULLselect NULLIF(20, 10) from dual; -- 20--7)NVL(expr1,expr2) 如果expr1is null；则返回expr2，否则返回expr1select NVL(20, 30) from dual; -- 20select NVL(NULL, 30) from dual; -- 30--8)NVL2(expr1,expr2,expr3) 如果expr1 is not null；则返回expr2；如果expr1=null；则返回expr3select NVL2(NULL, 20, 30) from dual; -- 30select NVL2('JellyThink', 20, 30) from dual; -- 20--9)COALESCE(value1, value2, value3, ...) 返回value列表第一个非空的值。 value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。select COALESCE(null, 1, 2,3) from  dual --1

（6）分析、开窗函数

      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

      1）over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数   

      over（partition by deptno）按照部门分区

      2）over（order by salary range between 5 preceding and 5 following）

      每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

      3）over（order by salary rows between 2 preceding and 4 following）

     每行对应的数据窗口是之前2行，之后4行 

      4）over（order by salary rows between unbounded preceding and unbounded following）

     每行对应的数据窗口是从第一行到最后一行，等效：

     over（order by salary range between unbounded preceding and unbounded following）

     等效：

     over(partition by null)

WITH TT AS (SELECT '张三' AS XM,'语文' AS KM,78 AS CJ FROM DUAL UNION ALLSELECT '张三' AS XM,'数学' AS KM,34 AS CJ FROM DUAL UNION ALLSELECT '李四' AS XM,'语文' AS KM,87 AS CJ FROM DUAL UNION ALLSELECT '李四' AS XM,'数学' AS KM,54 AS CJ FROM DUAL UNION ALLSELECT '王五' AS XM,'语文' AS KM,65 AS CJ FROM DUAL UNION ALLSELECT '王五' AS XM,'数学' AS KM,79 AS CJ FROM DUAL UNION ALLSELECT '赵六' AS XM,'语文' AS KM,43 AS CJ FROM DUAL UNION ALLSELECT '赵六' AS XM,'数学' AS KM,65 AS CJ FROM DUAL)SELECT T1.*,     ROW_NUMBER() OVER(ORDER BY CJ DESC) AS 成绩排序,     ROW_NUMBER() OVER(PARTITION BY KM ORDER BY CJ DESC) AS 按科目分组排序,     DENSE_RANK() OVER(ORDER BY CJ DESC) AS 成绩排序可以并列1,     RANK() OVER(ORDER BY CJ DESC) AS 成绩排序可以并列2,     LAG(CJ,1,0) OVER(ORDER BY CJ) AS 下一个的成绩,     LEAD(CJ,1,0) OVER(ORDER BY CJ) AS 上一个的成绩,     SUM(CJ) OVER(PARTITION BY KM) AS 该科目的总成绩,     SUM(CJ) OVER(ORDER BY CJ) AS 成绩从低到高累加,     SUM(CJ) OVER(ORDER BY CJ ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS 开窗1,     SUM(CJ) OVER(ORDER BY CJ RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS 开窗2,     FIRST_VALUE(CJ) OVER(PARTITION BY KM ORDER BY CJ) AS 当前科目最低分,     LAST_VALUE(CJ) OVER(PARTITION BY KM ORDER BY CJ) AS 当前科目最高分  FROM TT T1

（7）行转列

--WMSYS.WM_CONCAT: 依赖WMSYS 用户，不同oracle环境时可能用不了（Oracle12c），返回类型为CLOB，可用substr截取长度后to_char转化为字符类型。--LISTAGG 11g2才提供的函数，不支持distinct，拼接长度不能大于4000，函数返回为varchar2类型，最大长度为4000。WITH TT AS(SELECT 'China' country ,'Guangzhou' city FROM DUAL UNION ALLSELECT 'China' country ,'Shanghai' city FROM DUAL UNION ALLSELECT 'China' country ,'Beijing' city FROM DUAL UNION ALLSELECT 'USA' country ,'New York' city FROM DUAL UNION ALLSELECT 'USA' country ,'Bostom' city FROM DUAL UNION ALLSELECT 'Japan' country ,'Tokyo' city FROM DUAL )/*SELECT TO_CHAR(WM_CONCAT(city)) AS F1,     LISTAGG(city,',') WITHIN GROUP (ORDER BY city) AS F2FROM TT*/SELECT country,TO_CHAR(WM_CONCAT(city)) AS F1,     country,LISTAGG(city,',') WITHIN GROUP (ORDER BY city) AS F2FROM TTGROUP BY country/*SELECT country,MAX(F1) AS F1 FROM (SELECT country,TO_CHAR(WM_CONCAT(city) OVER(PARTITION BY country ORDER BY city)) AS F1 FROM TT) T GROUP BY country*/

（8）列转行

SELECT REGEXP_SUBSTR(F1, '[^,]+', 1, LEVEL) AS STR  FROM (SELECT '1010000001,1010000003,1010000004' AS F1 FROM DUAL) TCONNECT BY LEVEL <= LENGTH(F1) - LENGTH(REGEXP_REPLACE(F1, ',', '')) + 1;--支持区分多个符号SELECT REGEXP_SUBSTR(F1, '[^+,]+', 1, LEVEL)  FROM (SELECT '1010000001+1010000003,1010000004' AS F1 FROM DUAL) T1CONNECT BY LEVEL <= LENGTH(F1) - LENGTH(TRANSLATE(F1, '$+,', '$')) + 1

（9）层级询语句CONNECT BY

with tt as(select '500001' as TREE_ID, '100001' as NODE_ID, '-1' as NODE_PARENTID, '根' as NODE_NAME from dualunion allselect '500001' as TREE_ID, '100002' as NODE_ID, '100001' as NODE_PARENTID, '一级A' as NODE_NAME from dualunion allselect '500001' as TREE_ID, '100003' as NODE_ID, '100001' as NODE_PARENTID, '一级B' as NODE_NAME from dualunion all      select '500001' as TREE_ID, '100004' as NODE_ID, '100002' as NODE_PARENTID, '二级A' as NODE_NAME from dualunion allselect '500001' as TREE_ID, '100005' as NODE_ID, '100002' as NODE_PARENTID, '二级B' as NODE_NAME from dual)SELECT TREE_ID,     NODE_ID,     NODE_PARENTID,     NODE_NAME,     LPAD('-', 2 * (LEVEL - 1), '-') || NODE_NAME AS "树节点",     SYS_CONNECT_BY_PATH(NODE_NAME,'/') AS "路径",     CONNECT_BY_ROOT(NODE_NAME) AS "根节点",     CONNECT_BY_ISLEAF AS "是否叶子节点",     LEVEL AS "层级",     ROWNUM AS "行号"  FROM (SELECT TREE_ID, NODE_ID, NODE_PARENTID, NODE_NAME      FROM tt     WHERE TREE_ID = '500001') TCONNECT BY PRIOR NODE_ID = NODE_PARENTID START WITH NODE_PARENTID = '-1'--生成数据SELECT LEVEL F1 FROM DUAL CONNECT BY LEVEL < = 5或SELECT ROWNUM F1 FROM DUAL CONNECT BY ROWNUM < = 5

4. Oracle分页

--Oracle中使用rownum来进行分页, 这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的 select * from   ( select rownum r,a from tabName where rownum <= 20 ) where r > 10

5、查询表空间大小

SELECT A.TABLESPACE_NAME "表空间名",       TOTAL "表空间大小",       FREE "表空间剩余大小",       (TOTAL - FREE) "表空间使用大小",       ROUND(TOTAL / 1024 / 1024 / 1024, 4) "表空间大小(G)",       ROUND(FREE / 1024 / 1024 / 1024, 4) "表空间剩余大小(G)",       ROUND((TOTAL - FREE) / 1024 / 1024 / 1024, 4) "表空间使用大小(G)",       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率(%)"    FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE        FROM DBA_FREE_SPACE       GROUP BY TABLESPACE_NAME) A,       (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL        FROM DBA_DATA_FILES       GROUP BY TABLESPACE_NAME) B   WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME   ORDER BY 1;

6、查询表大小

SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) "表大小(G)"  FROM DBA_SEGMENTS WHERE OWNER = 'SCOTT'   AND SEGMENT_TYPE LIKE 'TABLE%'   AND SEGMENT_NAME IN ('EMP') GROUP BY SEGMENT_NAME ORDER BY 2 DESC;

7、查询索引大小

SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) "表大小(G)"  FROM DBA_SEGMENTS WHERE OWNER = 'SCOTT'   AND SEGMENT_TYPE LIKE 'INDEX%'--AND SEGMENT_NAME IN ('EMP') GROUP BY SEGMENT_NAME ORDER BY 2 DESC;

8、锁表

SELECT /*+ USE_HASH(O,S,T,Q)*/ T.OBJECT_NAME, O.SESSION_ID, S.SQL_ID, Q.SQL_TEXT, S.*  FROM GV$LOCKED_OBJECT O, DBA_OBJECTS T, GV$SESSION S, GV$SQL Q WHERE T.OBJECT_ID = O.OBJECT_ID   AND O.SESSION_ID = S.SID   AND S.SQL_ID = Q.SQL_ID;--AND T.OBJECT_NAME = 'TABLE'或者select object_name,machine,s.sid,s.serial#from gv$locked_object l,dba_objects o,gv$session swhere l.object_id=o.OBJECT_IDand l.session_id=s.sid;--杀掉会话ALTER SYSTEM KILL SESSION '12222,42828'

9、取某区间执行的所有SQL

SELECT C.USERNAME, A.PROGRAM, B.SQL_TEXT, B.COMMAND_TYPE, A.SAMPLE_TIME  FROM DBA_HIST_ACTIVE_SESS_HISTORY A  JOIN DBA_HIST_SQLTEXT B    ON A.SQL_ID = B.SQL_ID  JOIN DBA_USERS C    ON A.USER_ID = C.USER_ID WHERE A.SAMPLE_TIME BETWEEN       TO_DATE('2018-01-16 14:00', 'YYYY-MM-DD HH24:MI') AND       TO_DATE('2018-01-16 19:00', 'YYYY-MM-DD HH24:MI')      --AND B.COMMAND_TYPE IN (7, 85)          --delete以及truncate table语句   AND USERNAME = 'SCOTT'   AND LENGTH(B.SQL_TEXT) <= 4000 ORDER BY A.SAMPLE_TIME;

10、查询某时间段内，执行频率最高的SQL以及次数，注（不包含字符超过4000的SQL）

WITH TT AS (SELECT C.USERNAME,         A.PROGRAM,         TO_CHAR(B.SQL_TEXT) AS SQL_TEXT,         B.COMMAND_TYPE,         A.SAMPLE_TIME    FROM DBA_HIST_ACTIVE_SESS_HISTORY A    JOIN DBA_HIST_SQLTEXT B      ON A.SQL_ID = B.SQL_ID    JOIN DBA_USERS C      ON A.USER_ID = C.USER_ID   WHERE A.SAMPLE_TIME BETWEEN         TO_DATE('2018-01-16 14:00', 'YYYY-MM-DD HH24:MI') AND         TO_DATE('2018-01-16 19:00', 'YYYY-MM-DD HH24:MI')     AND USERNAME = 'NEWRISK'     AND LENGTH(B.SQL_TEXT) <= 4000)SELECT SQL_TEXT, COUNT(1) AS FNUM  FROM TT GROUP BY SQL_TEXT ORDER BY 2 DESC;

11、查看回滚段大小

SELECT SEGMENT_NAME,       TABLESPACE_NAME,       R.STATUS,       (INITIAL_EXTENT / 1024) INITIALEXTENT,       (NEXT_EXTENT / 1024) NEXTEXTENT,       MAX_EXTENTS,       V.CUREXT CUREXTENT  FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V WHERE R.SEGMENT_ID = V.USN(+) ORDER BY SEGMENT_NAME;

12、查找表的相关属性

--1）查找表的所有索引（包括索引名，类型，构成列）：select t.*, i.index_type  from user_ind_columns t, user_indexes i where t.index_name = i.index_name   and t.table_name = i.table_name   and t.table_name = 表名--2）查找表的主键（包括名称，构成列）：select cu.*  from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name   and au.constraint_type = 'P'   and au.table_name =  表名--3）查找表的唯一性约束（包括名称，构成列）：select column_name  from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name   and au.constraint_type = 'U'   and au.table_name =  表名--4）查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：select *  from user_constraints c where c.constraint_type = 'R'   and c.table_name =  表名--外键约束的列名：select * from user_cons_columns cl where cl.constraint_name =  外键名称--引用表的键的列名：select *  from user_cons_columns cl where cl.constraint_name =  外键引用表的键名--5）查询表的所有列及其属性：select t.*, c.COMMENTS  from user_tab_columns t, user_col_comments c where t.table_name = c.table_name   and t.column_name = c.column_name   and t.table_name =  表名

13、查看正在执行的SQL的进度

SELECT b.sid,       b.serial#,       b.username 登录Oracle用户名,       spid 操作系统ID,       paddr,       sql_text 正在执行的SQL,       b.machine 计算机名,       d.target,       d.opname,       round(d.sofar * 100 / d.totalwork, 0) || '%' as progress, --进度条       time_remaining second, --剩余时间：秒       trunc(d.time_remaining / 60, 2) minute, --剩余时间：分钟       c.LAST_ACTIVE_TIME  FROM v$process a, v$session b, v$sqlarea c, v$session_longops d WHERE a.addr = b.paddr   AND b.sql_hash_value = c.hash_value   and d.time_remaining <> 0   and d.sql_address = c.address   and d.sql_hash_value = c.hash_value   and a.username = 'SCOTT'

14、Oracle数据库的连接数及会话

select * from v$session a where a.USERNAME='SCOTT';--查看最大连接数select value from v$parameter where name = 'processes';--查看并发连接数select username,count(*) from v$session where status='ACTIVE' group by username;--查看正在使用的连接数select username,status,count(username) from v$session a /*where username is not null*/ group by username,status;select username,       serial#,       sid,       status,       a.SCHEMANAME,       a.OSUSER,       a.MACHINE,       a.TERMINAL,       a.MODULE  from v$session a where username ='SCOTT';--修改数据库允许的最大连接数alter system set processes = 300 scope = spfile;----select username,serial#, sid from v$session;  ---查询用户会话--alter system kill session 'serial#, sid ';---删除相关用户会话

15、游标

      1）游标有 显式游标，隐式游标，强类型游标，弱类型游标

      2）游标的状态

      显式游标属性：

      %FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

      %NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

      %ROWCOUNT：当前时刻已经从游标中获取的记录数量。

      %ISOPEN：是否打开。

      隐式游标属性:

      SQL%FOUND

      SQL%NOTFOUND

--1. 声明游标；CURSOR cursor_name IS select_statement--For 循环游标--（1）定义游标--（2）定义游标变量--（3）使用for循环来使用这个游标declare  --类型定义  cursor c_job is    select empno, ename, job, sal from emp where job = 'MANAGER';  --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型  c_row c_job%rowtype;begin  for c_row in c_job loop    dbms_output.put_line(c_row.empno || '-' || c_row.ename || '-' ||                         c_row.job || '-' || c_row.sal);  end loop;end;--Fetch游标--使用的时候必须要明确的打开和关闭declare  --类型定义  cursor c_job is    select empno, ename, job, sal from emp where job = 'MANAGER';  --定义一个游标变量  c_row c_job%rowtype;begin  open c_job;  loop    --提取一行数据到c_row    fetch c_job      into c_row;    --判读是否提取到值，没取到值就退出    --取到值c_job%notfound 是false     --取不到值c_job%notfound 是true    exit when c_job%notfound;    dbms_output.put_line(c_row.empno || '-' || c_row.ename || '-' ||                         c_row.job || '-' || c_row.sal);  end loop;  --关闭游标  close c_job;end;--2：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。 begin   update emp set ENAME = 'ALEARK' WHERE EMPNO = 7469;   if sql%isopen then     dbms_output.put_line('Openging');   else     dbms_output.put_line('closing');   end if;   if sql%found then     dbms_output.put_line('游标指向了有效行'); --判断游标是否指向有效行   else     dbms_output.put_line('Sorry');   end if;   if sql%notfound then     dbms_output.put_line('Also Sorry');   else     dbms_output.put_line('Haha');   end if;   dbms_output.put_line(sql%rowcount); exception   when no_data_found then     dbms_output.put_line('Sorry No data');   when too_many_rows then     dbms_output.put_line('Too Many rows'); end;--3,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  declare  CURSOR c_dept(p_deptNo number) is    select * from emp where emp.depno = p_deptNo;  r_emp emp%rowtype;begin  for r_emp in c_dept(20) loop    dbms_output.put_line('员工号：' || r_emp.EMPNO || '员工名：' || r_emp.ENAME ||                         '工资：' || r_emp.SAL);  end loop;end;--4：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来   create table emp1 as select * from emp;declare    cursor    csr_Update    is    select * from  emp1 for update OF SAL;    empInfo csr_Update%rowtype;    saleInfo  emp1.SAL%TYPE;begin  FOR empInfo IN csr_Update LOOP    if empInfo.SAL<1500 THEN     saleInfo:=empInfo.SAL*1.2;    elsif empInfo.SAL<2000 THEN     saleInfo:=empInfo.SAL*1.5;    elsif empInfo.SAL<3000 THEN     saleInfo:=empInfo.SAL*2;    end if;    UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;   END LOOP;END;--5:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。--AVG([distinct|all] expr) over (analytic_clause)---作用：--按照analytic_clause中的规则求分组平均值。  --分析函数语法:  --FUNCTION_NAME(
     
      ,
      
       ...)  --OVER  --(
       
        
         
          )   --PARTITION子句   --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组   select * from emp1DECLARE   CURSOR    crs_testAvg   IS   select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG   FROM EMP1 for update of SAL;   r_testAvg crs_testAvg%rowtype;   salInfo emp1.sal%type;   begin   for r_testAvg in crs_testAvg loop     if r_testAvg.SAL>r_testAvg.DEP_AVG then      salInfo:=r_testAvg.SAL-50;     end if;     update emp1 set SAL=salInfo where current of crs_testAvg;   end loop;end;
         
        
       
      
     

16、存储过程或函数返回集合

        1）游标形式返回集合

CREATE OR REPLACE FUNCTION A_Test(jobName in varchar2)     RETURN SYS_REFCURSOR    is         type_cur SYS_REFCURSOR;    BEGIN      OPEN type_cur FOR          select * from emp a where a.job=jobName;          dbms_output.put_line(jobName);            RETURN  type_cur;    END;--调用函数传入emp表的job字段值select A_Test('CLERK') from dual;select * from emp a where a.job='CLERK'

       2）返回table类型的结果集

--定义一个行类型CREATE OR REPLACE TYPE SPLIT_ARR  AS OBJECT(nowStr varchar2(18));--以此行类型定义一个表类型CREATE OR REPLACE TYPE SPLIT_TAB AS TABLE of SPLIT_ARR;--定义函数CREATE OR REPLACE FUNCTION B_Test(str       in varchar2, --待分割字符串                  splitchar in varchar2 --分割标志                  ) RETURN split_tab is  restStr  varchar2(2000) default B_Test.str; --剩余的字符串  thisStr  varchar2(18); --取得的当前字符串  indexStr int; --临时存放分隔符在字符串中的位置  v split_tab := split_tab(); --返回结果begin  dbms_output.put_line(restStr);  while length(restStr) != 0 LOOP  <
     
      >  indexStr := instr(restStr, splitchar); --从子串中取分隔符的第一个位置      if indexStr = 0 and length(restStr) != 0 then    --在剩余的串中找不到分隔符   -- begin    v.extend;    v(v.count) := split_arr(Reststr);    return v;    --end;  end if;      if indexStr = 1 then    ---第一个字符便为分隔符,此时去掉分隔符    --begin    dbms_output.put_line(restStr);    restStr := substr(restStr, 2);    dbms_output.put_line(restStr);    goto top;    --end;  end if;      if length(restStr) = 0 or restStr is null then    return v;  end if;      v.extend;  thisStr := substr(restStr, 1, indexStr - 1); --取得当前的字符串  restStr := substr(restStr, indexStr + 1); ---取剩余的字符串      v(v.count) := split_arr(thisStr);  END LOOP;  return v;end;--调用函数select B_test(',aaaa,bbbbb,cccc,dddd,eeee',',') from dual
     

       3)管道形式输出

--类似于2创建行类型CREATE OR REPLACE TYPE empRow AS OBJECT(  empno    number(4),  ename    varchar2(10),  job      varchar2(9),  mgr      number(4),  hiredate date,  sal      number(7, 2),  comm     number(7, 2),  deptno   number(2)); --创建表类型CREATE OR REPLACE TYPE emp_tab AS TABLE of empRow;--创建函数create or replace function C_test(jobName in varchar2) return emp_tab  pipelined is  v_empRow empRow; --定义v为行对象类型begin  for thisrow in (select * from emp a where a.job = jobName) loop  v_empRow := empRow(thisrow.empno,             thisrow.ename,             thisrow.job,             thisrow.mgr,             thisrow.hiredate,             thisrow.sal,             thisrow.comm,             thisrow.deptno);  pipe row(v_empRow);  end loop;  return;end;--调用输出emp表中job为CLERK的员工select c_Test('CLERK') from dual;select * from emp a where a.job='CLERK'