Oracle笔记之——常用的函数及脚本

一、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_str
select REPLACE('jellythink', 'think', ' is good') from dual; -- jelly is good
--9）INSTR(char1,char2[,n[,m]])--获取子串char2在字符串char1中的位置。n为其实搜索位置，m为子串出现的次数；n为负，则从尾部开始搜索；n\m默认为1
select 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; -- 0
select 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/30
select ROUND(SYSDATE, 'YEAR') from dual; -- 2016/1/1
select ROUND(SYSDATE, 'MONTH') from dual; -- 2015/10/1
--7)TRUNC(day,[,fmt]) 日期截断函数。如果fmt指定年度，则结果为本年度的1月1日；如果为月，则将结果为本月1日，默认截断到日
select TRUNC(SYSDATE) from dual; -- 2015/9/29
select TRUNC(SYSDATE, 'YEAR') from dual; -- 2015/1/1
select 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; -- 2015
select EXTRACT(MONTH from SYSDATE) from dual; -- 9
select EXTRACT(DAY from SYSDATE) from dual; -- 29

（3）常用类型转换函数

--1)TO_CHAR 将一个数字或日期转换成字符串
select TO_CHAR(100) from dual; -- 100
select 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; -- 10
select 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; -- 100
select 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则返回0
select SIGN(20) from dual; -- 1
select SIGN(-30) from dual; -- -1
select SIGN(0) from dual; -- 0
--3)TRUNC(number, [ decimal_places ]) number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。 如果此参数缺省，默认保留0位小数
select TRUNC(20.2183, 2) from dual; -- 20.21
select 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，否则返回expr1
select NULLIF(20, 20) from dual; -- NULL
select NULLIF(20, 10) from dual; -- 20
--7)NVL(expr1,expr2) 如果expr1is null；则返回expr2，否则返回expr1
select NVL(20, 30) from dual; -- 20
select NVL(NULL, 30) from dual; -- 30
--8)NVL2(expr1,expr2,expr3) 如果expr1 is not null；则返回expr2；如果expr1=null；则返回expr3
select NVL2(NULL, 20, 30) from dual; -- 30
select 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 ALL
SELECT '张三' AS XM,'数学' AS KM,34 AS CJ FROM DUAL UNION ALL
SELECT '李四' AS XM,'语文' AS KM,87 AS CJ FROM DUAL UNION ALL
SELECT '李四' AS XM,'数学' AS KM,54 AS CJ FROM DUAL UNION ALL
SELECT '王五' AS XM,'语文' AS KM,65 AS CJ FROM DUAL UNION ALL
SELECT '王五' AS XM,'数学' AS KM,79 AS CJ FROM DUAL UNION ALL
SELECT '赵六' AS XM,'语文' AS KM,43 AS CJ FROM DUAL UNION ALL
SELECT '赵六' 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 ALL
SELECT 'China' country ,'Shanghai' city FROM DUAL UNION ALL
SELECT 'China' country ,'Beijing' city FROM DUAL UNION ALL
SELECT 'USA' country ,'New York' city FROM DUAL UNION ALL
SELECT 'USA' country ,'Bostom' city FROM DUAL UNION ALL
SELECT 'Japan' country ,'Tokyo' city FROM DUAL 
)
/*SELECT TO_CHAR(WM_CONCAT(city)) AS F1,
     LISTAGG(city,',') WITHIN GROUP (ORDER BY city) AS F2
FROM TT*/
SELECT country,TO_CHAR(WM_CONCAT(city)) AS F1,
     country,LISTAGG(city,',') WITHIN GROUP (ORDER BY city) AS F2
FROM TT
GROUP 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) T
CONNECT BY LEVEL <= LENGTH(F1) - LENGTH(REGEXP_REPLACE(F1, ',', '')) + 1;
--支持区分多个符号
SELECT REGEXP_SUBSTR(F1, '[^+,]+', 1, LEVEL)
  FROM (SELECT '1010000001+1010000003,1010000004' AS F1 FROM DUAL) T1
CONNECT 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 dual
union all
select '500001' as TREE_ID, '100002' as NODE_ID, '100001' as NODE_PARENTID, '一级A' as NODE_NAME from dual
union all
select '500001' as TREE_ID, '100003' as NODE_ID, '100001' as NODE_PARENTID, '一级B' as NODE_NAME from dual
union all      
select '500001' as TREE_ID, '100004' as NODE_ID, '100002' as NODE_PARENTID, '二级A' as NODE_NAME from dual
union all
select '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') T
CONNECT 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 s
where l.object_id=o.OBJECT_ID
and 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(<argument>,<argument>...)
  --OVER
  --(<Partition-Clause><Order-by-Clause><Windowing Clause>)
   --PARTITION子句
   --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
   select * from emp1
DECLARE
   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
  <<top>>
  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'