限定查询
比较运算符 >,<,>=,<=,=,<>,!=
逻辑运算符：AND | OR
特殊运算符：IS NULL | IS NOT NULL
区间范围判断： between
指定范围判断： IN | NOT IN
模糊匹配： LIKE
字符函数
1.CONCAT --拼接 等同于 ||
语法：CONCAT(字符串|字段，字符串|字段) --只有两个参数
2.INITCAP --首字母大写
语法：INITCAP(字段|字符串)
3.UPPER --转大写
语法：UPPER(字符串|字段)
4.LOWER --转小写
语法1：LOWER(字符串|字段)
语法2:TRIM(LEADING | TRAILING | BOTH 要删除的字符 FROM 字符串|字段)
LEADING--左边 TRAILING--右边 BOTH--左右两边
5.TRIM --删除左右两边空格
语法：TRIM(字符串|字段)
6.LTRIM --删除左边的空格
语法1：LTRIM(字符串|字段)
语法2：LTRIM(字段|字符串，要删除的字符) --删除左边的字符遇到非第二个参数的则停止
7.RTRIM --删除右边的空格
语法1：RTRIM(字段|字符串)
语法2：RTRIM(字段|字符串，要删除的字符)
8.INSTR --查找字符所在位数
语法：INSTR(字符串|字段，要查找的字符，从第几位开始查找，查找第几次出现的)
9.SUBSTR --截取
语法：SUBSTR(字段|字符串，从第几位开始截取，截取几位)
10.LENGTH --第一个是字符的返回字符的长度
语法：LENGTH(字符串|列) --返回字符的长度
11.LENGTHB --第二个是返回字节的长度
语法：LENGTHB(字符串|列) --返回字节的长度、
12.LPAD --左填充
语法：LPAD(字段|字符串，最终返回的字符串长度，填充的字符)
13.RPAD --右填充
语法：RPAD(字段|字符串，最终返回的字符串长度，要填充的字符)
14.REPLACE --整体替换删除 --第二个参数的值没有匹配值 则删除
语法：REPLACE(字符串|字段，要替换的内容（旧内容），要替换的新内容)
15.TRANSLATE --单个替换删除
语法：TRANSLATE(字符串|列，旧内容，新内容)
--TRANSLATE是单个替换逐一匹配替换
操作字符串'ABCACBABCBACB'把其中的A替换成S, B替换成T
SELECT TRANSLATE('ABCADCBABCDBACBD','ABCD','ST') FROM DUAL ;
结果：'STSTSTTST'
--如果说 第二个参数的值没有匹配值 则删除
SELECT TRANSLATE('ABCACBABCBACB','AB','STasdfghszxdrcvgbhmj') FROM DUAL ;
结果：'STSTSTTST'
--如果第三个参数没有匹配的值 则省略
SELECT TRANSLATE('ASDFSSGHJKLZXCVBNM!DDDDFFF','ASDF','12') FROM DUAL;
结果：'12 22GHJKLZXCVBNM!'
数值函数
1.ACOS --反余弦
语法：ACOS(N) -- 在-1到1之间
2.COS --余弦
语法：COS(N)
3.POWER --次幂
语法：POWER(N1,N2) --N1的N2次幂
4.SQRT --平方根
语法：SQRT(N)
5.ABS --绝对值
语法：ABS(数字|字段) --返回参数的绝对值
6.MOD --余数
语法：MOD(数字|字段，数字|字段) --返回的结果集就是第一个参数除以第二个参数的余数
7.FLOOR --向下取整
语法：FLOOR(数字|字段)
8.CEIL --向上取整
语法：CETL(数字|字段)
9.ROUND --四舍五入
语法：ROUND(数字|字段，保留的小数位数)
SELECT ROUND(10.123,2) FROM DUAL; --10.12
SELECT ROUND(10.567) FROM DUAL; --11
--如果说第二个参数不写 则默认保留整数
SELECT ROUND(56789.1234,-4) FROM DUAL; --60000
SELECT ROUND(56789.1234,-5) FROM DUAL; --100000
--如果说第二个参数是负数的话则从小数点左边开始四舍五入
10.TRUNC --不四舍五入直接截取
语法：TRUNC(数字|字段，保留小数的位数)
SELECT TRUNC(10.999,'2') FROM DUAL; --10.99
SELECT TRUNC(10.999) FROM DUAL; --10
-- 如果第二个参数不写 则默认保留整数 不四舍五入
SELECT TRUNC(7894521.1245,'-3') FROM DUAL; --7894000
SELECT TRUNC(7894521.1245,'-5') FROM DUAL; --7800000
SELECT TRUNC(7894521.1245,'-7') FROM DUAL; --0
--如果第二个参数是负数的话 则从小数点左边开始截取 不四舍五入
11.SIGN --如果这个参数的值>0则会返回1 如果这个参数的值小于0 则会返回-1 如果这个参数的值等于0 则会返回0
语法：SIGN(数字|字段) --一个参数
SELECT
SIGN(10), --1
SIGN(-10), --负1
SIGN(0) --0
FROM DUAL;
日期函数
1.SYSDATE --返回系统当前时间
SELECT SYSDATE FROM EMP; --2023/2/8 14:41:42
2.EXTRACT --提取
语法：EXTRACT(格式字符串 from 日期|字段)
年：YEAR
月：MONTH
日：DAY
3.MONTHS_BETWEEN --返回两个日期中间相差的月份
语法：MONTHS_BETWEEN(字段|日期，字段|日期)
4.ADD_MONTHS
语法：ADD_MONTHS(日期|字段，给日期增加或者减少月份)
5.LAST_DAY --返回日期当月的最后一天
语法：LAST_DAT(日期|字段)
6.NEXT_DAY -- 返回离第一个参数最近的星期几 ，不包括今天
语法：NEXT_DAY(日期|字段，数字)
7.TRUNC --不四舍五入 直接截取
trunc(日期，格式)截取指定日期的第一天
select trunc(sysdate,'w') from dual
yyyy:年初
mm：月初
dd：今天的0点0分0秒
hh24：本小时的0分0秒
mi：本分钟的0秒
ss：不能精确到秒
q：本季度的第一天
w：从本月1号开始以七天为一周这个日期所在周的第一天
ww：以本年的1月1日为一周的开始，这个日期所在周的第一天
iw：求日期所在周的周一
语法： TRUNC(日期|字段，格式字符串)
条件函数
1.CASE WHEN
语法：CASE WHEN 筛选条件[AND|OR 筛选条件2] THEN 返回值1 [WHEN 筛选条件3[AND|OR 筛选条件4]] ELSE END;
例： 查询部门编号，部门名称，10部门显示会计部，20部门显示研究部，30部门显示销售部，其他部门显示运营部
SELECT DEPTNO,DNAME,CASE WHEN DEPTNO = '10' THEN '会计部'
WHEN DEPTNO = '20' THEN '研究部'
WHEN DEPTNO = '30' THEN '销售部'
ELSE '运营部' END
FROM DEPT;
2.DECODE --ORACLE独有函数
语法：DECODE(字段|表达式，值1，返回值1，值2，返回值2，值n，返回值n，缺省值)
例：查询部门编号，部门名称，10部门显示会计部，20部门显示研究部，30部门显示销售部，其他部门显示运营部
SELECT DEPTNO,DNAME,DECODE(DEPTNO,10,'会计部','20','研究部','30','销售部','运营部') FROM DEPT;
3.NVL --处理空值的函数
语法：NVL(要处理的字段，默认值) --如果第一个参数为空值 则返回第二个参数
4.NVL2
语法： NVL2(字段，值，值)
-- 如果第一参数为空值返回的是第三个参数的值 如果 第一个参数不为空 则返回第二个参数的值
例：SELECT COMM, NVL2(COMM,'999','888') FROM EMP;
5.GREATEST
语法：GREATEST(值1，值2，值3，值4，值n) --返回最大值
6.LEAST
语法：LEAST(值1，值2，值3，值4，值n) --返回最小值
7.NULLIF
语法：NULLIF(A,B) --如果A=B 则返回空 否则则返回第一个参数的值
8.COALESCE
语法：COALESCE（N1,N2,N3,NN） --多个参数返回参数左边第一个不为空的值
数据类型
1. 字符类型
（1） CHAR (N) --定向类型 他有固定的长度 如果我们给他的长度过多 他会自动补充空格 最大2000字节
（2） VARCHAR(N) -- 最多存储4000字节 他不会用空格填充最大长度
（3） VARCHAR2(N) --最多可以存储4000字节 他不会用空格填充最大长度
（4） NVARCHAR2(N) --按照字符来进行存储 他的最大长度4000， 它不会用空格填充最大长度
（5） NCHAR(N) -- 按照字符来进行存储,它的最大长度2000 如果位数不足则自动填充空格
--区别 CHAR和VARCHAR2虽然char是定向类型 但是他要比varchar2效率高 但是varchar2比char更节省空间
1.varchar是标准sql里面的。 varchar2是oracle提供的独有的数据类型。
2.varchar对于汉字占两个字节，对于英文是一个字节，占的内存小，varchar2都是占两个字节。
3.varchar对空串不处理，varchar2将空串当做null来处理。
4.varchar存放固定长度的字符串，最大长度是2000，varchar2是存放可变长度的字符串，最大长度是4000.
5.如果是要跟换不同的数据库，例如mysql，那么就用varchar，如果就用oracle，那么用varchar2比较好一点。
2.数值类型
(1) NUMBER(总位数，小数位数) --它最多不能超过38和数字
NUMBER(10,2) --十位整数两位小数
--如果第二个参数省略则保留整数 四舍五入小数
(2) INT --整数 它就相当于number（38）
(3) DECIMAL(总位数，小数位) ---它最多不能超过38和数字
3.日期类型
(1)DATE --年月日时分秒
(2)TIMESTAMP --年月日时分秒 最大位数9位 默认值是六位 --年月日倒叙
SELECT CAST(SYSDATE AS TIMESTAMP ) FROM DUAL;
4.大文本类型
(1) LONG --存储可变字符串 最大可以存储2GB
(2) BLOB -- 它最大可以存储4GB 它的存储空间可以在加上数据库的大小
(3) CLOB -- 他最大可以存储4GB
转换函数
1.TO_CHAR --把日期格式或者数字格式转换为字符串格式
语法：TO_CHAR(数字|日期|字段，格式字符串)
--MM月份
--MON 返回月份
--MONTH
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; --02
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL; --2月
SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL; --2月
查询入职日期是十二月份的员工信息
SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE,'MM') = 12;
SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE,'MON') = '12月';
--D --是当前星期的第几天1是周日2是周一以此类推
查询当前日期是星期几
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; --5
--DD --当月第几天
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; --09
--DDD --当前日期本年的第几天
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; --040
--DAY --当前日期为星期几
SELECT TO_CHAR(SYSDATE,'day') FROM DUAL;
--HH --表示当前日期第几个小时
--HH12
SELECT TO_CHAR(SYSDATE,'HH') FROM DUAL; --02
SELECT TO_CHAR(SYSDATE,'HH12') FROM DUAL; --02
--HH24
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; --14
--MI 当前时间的分钟
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; --17
--SS 表示当前时间的秒数
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; --07
--W 表示当前日期本月的第几周
SELECT TO_CHAR(SYSDATE,'W') FROM DUAL; --2
-- WW 表示当前日期是本年的第几周
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL; --06
-- Q 表示当前日期是本年的第几季度
SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; --1
2.CAST --进行数据类型的转换
语法： CAST(字符串|日期|数字|字段 AS 数据类型)
例：把emp表中的员工编号转换为varchar2类型
SELECT CAST(EMPNO AS VARCHAR2(20)) FROM EMP
3.TO_NUMBER --把字符串转换成数字
语法：TO_NUMBER(字符串格式的数字|字段)
SELECT TO_NUMBER('100') + TO_NUMBER('200') FROM DUAL;
4. TO_DATE --将字符串转换成日期类型
语法：TO_DATE(日期|数字|字段，格式字符串)
聚合函数
1.COUNT --查询条数
语法：COUNT(*|字段|表达式
2.sum --求和
语法：SUM(字段|表达式)
3.AVG --求平均值
语法：AVG(字段|表达式)
4.MAX --最大值
语法：MAX(字段|表达式)
5.MIN --最小值
语法： MIN(字段|表达式)
6.GROUP BY --分组
语法：SELECT DISTINCT *|字段 AS 别名 FROM 表名 别名 WHERE GROUP BY
7. HAVING --二次筛选
语法：SELECT DISTINCT *|字段 AS 别名 FROM 表名 别名 WHERE GROUP BY HAVING
8. ORDER BY --排序
语法：SELECT DISTINCT *|字段 AS 别名 FROM 表名 别名 WHERE GROUP BY HAVING
ORDER BY --ASC 升序 DESC 降序
SQL的执行顺序
FROM -> WHERE ->GROUP BY ->HAVING ->SELECT ->ORDER BY
FROM --确定查询那张表
WHERE -- 一次条件筛选
GROUP BY --分组
HAVING --二次筛选
SELECT --显示结果集
ORDER BY -- 排序
分析函数
函数名称()OVER (PARTITION BY 字段|表达式 ORDER BY 字段|表达式 ASC|DESC 如果不写 默认升序)
1.ROW-NUMBER --对结果集进行排序，如果有相同数据不管重复值连续排序
2.DENSE-RANK --连续排序如果有相同数据则并列排序
3.RANK --跳跃排序
4.SUM --求和
5.AVG --平均值
6.MAX --最大值
7.MIN --最小值
8.COUNT --计数
9.FIRST_VALUE --取结果集的第一条数据
LAST_VALUE --取结果集最后一条数据
10.LAG --LAG 有三个参数 第一个参数是要取得字段名称，第二个参数是偏移量默认值1第三位省略则默认值为空
例：SELECT
ENAME,EMPNO,SAL,
LAG(SAL)OVER(ORDER BY SAL) A, --取上一行的值
LAG(SAL,1)OVER(ORDER BY SAL) B, --如果第二个参数是2则从第二行开始取数据
LAG(SAL,3,666)OVER(ORDER BY SAL) C, --如果取不到数据则返回默认值 也就是第三个参数
FROM EMP;
1. ORDER BY 不能省略
2.第二个参数要大于等于0 如果第二个参数不写则默认是1
3.如果第三个参数不省略 则第二个参数也不能省略
11.LEAD
--有三个参数 第一个参数是要取得字段名称，第二个参数是偏移量默认值1超出窗口给他的默认值 第三位省略则默认值为空
例：
SELECT
ENAME,EMPNO,SAL,
LEAD(SAL)OVER(ORDER BY SAL) A, --取上下面一行的值
LEAD(SAL,2)OVER(ORDER BY SAL) B, --如果第二个参数是2则从下面第二行开始取数据
LEAD(SAL,3,666)OVER(ORDER BY SAL) C, --如果取不到数据则返回默认值 也就是第三个参数
FROM EMP;
12.NTILE --划分
13.LISTAGG
例：SELECT DEPTNO,LISTAGG(EMPNO,'/') WITHIN GROUP(ORDER BY EMPNO)OVER(PARTITION BY DEPTNO) FROM EMP
14.RATIO_TO_REPORT --求百分比
15.WITH AS 语句
--WITH查询语句不是以select关键字开头 而是WITH开头
--在查询之前 他先构造了一个临时表 他会把子查询的结果集放到临时表中 可以反复使用
--单个临时表
语法： WITH 临时表名 AS（查询语句）
SELECT FROM 临时表名
--多个临时表
语法： WITH 临时表名 AS（查询语句），
临时表名2 AS（查询语句） ....
SELECT 字段 FROM 临时表名 inner join 临时表2
on 临时表字段 = 临时表2
伪列
1.ROWNUM
查询emp表只取前十条显示详细信息
SELECT ROWNUM,EMP.* FROM EMP
WHERE ROWNUM <= 10;
2.ROWID --自动生成的id编码
--ROWID的值是自动生成的 它的值没有重复值 他是唯一
3.UNION --去重排序
例：
把emp和empl两个表放在一起显示去重排序
SELECT * FROM EMP
UNION
SELECT * FROM EMPL;
4.UNION ALL --不去重不排序
5.INTERSECT --交集
例：
查看emp表中和empl一样的数据
SELECT * FROM EMP
INTERSECT
SELECT * FROM EMPL;
6.MINUS --差集
例：
查看emp表中和empl不一样的数据
SELECT * FROM EMP
MINUS
SELECT * FROM EMPL
递归函数
1.SELECT LEVEL -- 查询层级关系
列1,
列2,
列。。。。
FROM 表名
where 筛选条件
START WITH 字段 = 值 --开始节点
CONNECT BY PRIOR 字段1 = 字段2 | 字段1 = PRIOR 字段2
--连接条件 如果CONNECT BY PRIOR 字段1 = 字段2 |则是从父亲节点往儿子节点查询
--如果CONNECT BY 字段1 = PRIOR 字段2 则是从儿子节点往父亲节点查询
例：
查询emp表里7839这个员工所有的下属
SELECT LEVEL
,EMPNO
,ENAME
,MGR
,SAL
,JOB
FROM EMP
WHERE EMPNO != '7839'
START WITH EMPNO = '7839'
CONNECT BY PRIOR EMPNO = MGR
2.SIBLINGS --如果加上就按照层级来进行排序
3.CONNECT_BY_ISLEAF --判断是否有子集返回 0 1
4.SYS_CONNECT_BY_PATH --查找详细等级关系
5.CONNECT_BY_ROOT --返回当前节点最顶端
6.PRIOR --返回父级节点
PLSQL数据类型
--PLSQL不但支持Oracle的数据类型还具备自身的数据类型
基本的数据类型：
数值类型：
NUMBER -- 最高38位可有小数
INT --38位
INTEGER --38位
NUMBER_INTEGER --介于 -231 到 231之间的整数
PLS_INTEGER --介于 -231 到 231之间的整数 但是PLS_INTEGER比NUMBER_INTEGER运算速度更快
DECIMAL --和number一样
字符类型
VARCHAR2 --可变字符串 最常用的字符类型
CHAR --固定长度字符串
string --和varchar2相同
日期类型
DATE
布尔类型
返回 ： FALSE|TRUE|NULL
特殊数据类型
1.%TYPE --声名一个指定与字段相同的数据类型
2.%ROWTYPE --声名一个指定与原表所有列相同的数据类型
优点：
不必查看表中字段的数据类型
如果表中字段类型更改type跟着更改 他会跟着实际 类型做调整
流程控制
--流程控制语句必须在代码块中使用，代码块分两种，命名块|匿名块
--匿名块因为没有名字所以没有办法在数据库中保存
--1.条件分支语句，2.循环语句
1.条件分支语句
主要分为四种：
1. IF THEN
2. IF THEN ELSE
3. IF THEN ELSIF
4. CASE
语法：
CASE WHEN 条件表达式 THEN 执行内容
WHEN 条件表达式 THEN 执行内容
WHEN 条件表达式 THEN 执行内容
ELSE
END CASE ;
循环
1.LOOP 循环
语法；
LOOP --开始循环
--执行内容
EXIT WHEN 条件 --退出循环条件
--执行内容
END LOOP;
2.WHILE 循环
语法：
WHILE 条件
LOOP
执行内容
END LOOP;
例：
打印乘法口诀表
DECLARE
I NUMBER:=1;
A NUMBER:=1;
BEGIN
WHILE I <=99
LOOP
WHILE A<=I
LOOP
DBMS_OUTPUT.PUT(A||'*'||I||'='||A*I||' ');
A:=A+1;
END LOOP;
DBMS_OUTPUT.new_line();
I:=I+1;
A:=1;
END LOOP;
END;
3.FOR 循环
语法：
FOR 变量 IN[REVERSE]范围下限值....范围上线值
LOOP
--执行内容
END LOOP;
例：
使用FOR 循环求前十个自然数中的偶数之和
DECLARE
V_SUM NUMBER:=0;
V_CHAR VARCHAR2(20);
BEGIN
FOR I IN 1..10 LOOP
IF MOD(I,2) = 0 THEN
V_CHAR:= V_SUM||'+'||I||'=';
V_SUM:= V_SUM+I;
DBMS_OUTPUT.PUT_LINE(V_CHAR||V_SUM);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1-10的偶数总和是：'||V_SUM);
END;
索引
索引
-- 索引是数据的对象之一，可以通过索引快速定位数据
索引的优点
1.加快查询速度
2.加快表与表之间的连接
3.在使用分组函数是可以对排序数据进行检索，可以减少分组排序的时间
索引的缺点
1.占磁盘空间
2.对表中数据进行dml操作是降低dml操作的速度
select * from emp
1.B树索引
语法：
CREATE [UNIQUE/*唯一*/] INDEX 索引名称 ON 表名（表中字段1，2.....）TABLESPACE 表空间
例：
给EMP1表里的ename字段创建唯一索引
CREATE UNIQUE INDEX IN_ENAEM_EMP1 ON EMP1(ENAME)
给EMP1表里的job字段创建普通索引
CREATE INDEX IN_JOB_EMP1 ON EMP1(JOB);
select * from EMP1;
给EMP1表里的SAL,COMM字段创建普通索引
CREATE INDEX IN_SALCOMM_EMP1 ON EMP1(SAL,COMM)
2. BITMAP --位图索引
--如果说重复值较多范围较小则适合使用位图索引
例;
给deptno列增加位图索引
CREATE BITMAP INDEX IN_deptno_EMP1 ON EMP1(deptno)
查看索引
SELECT * FROM USER_INDEXES --索引地址
修改索引
ALTER INDEX 旧索引名称 RENAME TO 新索引名称;
禁用索引
ALTER INDEX 索引名称 UNUSABLE;
删除索引
DROP INDEX 索引名称;
1.索引和主键的区别
当创建一个主键他会自动生成索引 如果我们把主键删除 我们可以选择是否保留索引
如果我们删除唯一索引 它对应的主键不能删除 唯一索引不限制NULL值 可以插入NULL值 主键不能不能插入空值
2.什么情况下时候建索引
（1） 经常在where条件后面使用的列
（2） 作为主键的列
（3） 经常作为连接
（4）不经常进行dml操作的
3.为什么要建索引
（1） 加快查询速度
（2） 加速表连接
（3）在使用分组函数是可以对排序数据进行检索，可以减少分组排序的时间
索引的缺点
4.什么情况下sql中的索引不会用到
（1）如果列有null值 b树索引不会用到
（2） 有函数计算
（3）有隐形转换式
（4）全表扫描
（5）不等于<> 或者 or
5.索引的原理
如果在在列上建立了索引Oracle就会对全表进行搜索用列来匹配值的话可以直接定位到，索引是单块读写，
全部表扫描是多块读写如果要查询大批量数据 则全表扫描比较快
序列
1.序列
--他是Oracle中产生唯一数字的数据库对象可以为表中自动生成序列号
语法：
CREATE SEQUENCE 序列名称
[START WITH N] --开始的值 默认是1
[INCREMENT BY N] --递增的序列值是N如果是N是正数就递增如果是负数就递减
[MAXVALUE] --序列最大值 它的最大值是10的27次方10000000000000000000000000000
[MINVALUE] --序列的最小值
[CYCLE | NOCYCLE] --控制循环不循环 默认不循环
[CACHE N |NOCACHE] --空值缓存不缓存 默认不缓存
例：
创建序列 SEQ_T
CREATE SEQUENCE SEQ_d;
调用序列
NEXTVAL --获取序列下一个值
CURRVAL --当前值 调用它的时候必须在这之前使用一次
SELECT SEQ_d.NEXTVAL FROM DUAL; --持续递增
SELECT SEQ_d.CURRVAL FROM DUAL; --3 返回当前值
select SEQ_d.NEXTVAL,a.* from emp a
创建一个序列SEQ_1 序列值从10开始每次递增1 序列最大值是40 最小值是5
CREATE SEQUENCE SEQ_1
START WITH 10
INCREMENT BY 1
MAXVALUE 40
MINVALUE 5
SELECT SEQ_1.NEXTVAL FROM DUAL; --持续递增
SELECT SEQ_1.CURRVAL FROM DUAL; --持续递增
--如果递增的次数大于最大值则 报错 序列 SEQ_1.NEXTVAL exceeds MAXVALUE 无法实例化
CREATE SEQUENCE SEQ_2
START WITH 4
INCREMENT BY 1
MAXVALUE 40
MINVALUE 5
--ORA-04006: START WITH 不能小于 MINVALUE
创建序列SEQ_3序列从数字1开始每次增长1序列值最大50最小值1缓存 缓存设置为50
CREATE SEQUENCE SEQ_5
START WITH 1
INCREMENT BY 2
MAXVALUE 10
MINVALUE 1
CYCLE
noCACHE
SELECT SEQ_4.NEXTVAL FROM DUAL; --持续递增
SELECT SEQ_4.CURRVAL FROM DUAL; --3 返回当前值
修改序列：
ALTER SEQUENCE SEQ_3 NOCYCLE --需要修改参数
删除序列：
DROP SEQUENCE 序列名称
DROP SEQUENCE SEQ_3;
DROP SEQUENCE SEQ_1;
DROP SEQUENCE SEQ_4;
查看序列
SELECT * FROM USER_SEQUENCES --存储序列的表
异常
1、异常
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,
程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,
程序就会自动终止整个程序运行.
2.有三种类型的异常错误：
1、预定义异常 用于处理常见的Oracle错误（Oracle预定义的异常）
2、非预定义异常 用于处理预定义异常不能处理的Oracle错误
3、用户定义异常 用于处理Oracle错误无关的其他情况
3.在PL/SQL中有三种方式抛出异常
1.系统在运行时自动抛出
2.通过RAISE语句抛出，抛出的异常为已定义有名字的；
3.调用RAISE_APPLICATION_ERROR存储过程抛出异常，可直接抛出异常代码和异常消息。
异常处理部分一般放在?PL/SQL?程序体的后半部
注意：异常处理可以按任意次序排列,但?OTHERS?必须放在最后.
4.预定义异常
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
错误号 异常错误信息名称 说明
ORA-0001 DUP_VAL_ON_INDEX 违反了唯一性限制
ORA-0051 TIMEOUT_ON_RESOURCE 在等待资源时发生超时
ORA-0061 TRANSACTION_BACKED-OUT 由于发生死锁事务被撤消
ORA-1001 INVALID_CURSOR 试图使用一个无效的游标
ORA-1012 NOT_LOGGED-ON 没有连接到ORACLE
ORA-1017 LOGIN_DENIED 无效的用户名/口令
ORA-1403 NO_DATA_FOUND SELECT INTO没有找到数据
ORA-1422 TOO_MANY_ROWS SELECT INTO?返回多行
ORA-1476 ZERO_DIVIDE 试图被零除
ORA-1722 INVALID_NUMBER 转换一个数字失败
ORA-6500 STORAGE_ERROR 内存不够引发的内部错误
ORA-6501 PROGRAM_ERROR 内部错误
ORA-6502 VALUE_ERROR 转换或截断错误
ORA-6504 ROWTYPE_MISMATCH 宿主游标变量与?PL/SQL变量有不兼容行类型
ORA-6511 CURSOR_ALREADY_OPEN 试图打开一个已处于打开状态的游标
ORA-6530 ACCESS_INTO_NULL 试图为NULL?对象的属性赋值
ORA-6531 COLLECTION_IS_NULL 试图将EXISTS?以外的集合( COLLECTION)方法应用于一个NULL PL/SQL?表上或VARRAY上
ORA-6532 SUBSCRIPT_OUTSIDE_LIMIT 对嵌套或VARRAY索引得引用超出声明范围以外
ORA-6533 SUBSCRIPT_BEYOND_COUNT 对嵌套或VARRAY?索引得引用大于集合中元素的个数.
对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。
下面说一下几个预定义异常：
NO_DATA_FOUND --SELECT INTO没有找到数据
TOO_MANY_ROWS --select into 返回多行
zero_divide --试图被零除(除数为0)
VALUE_ERROR --数字或值错误 : 字符到数值的转换错误
OTHERS --表示其他所有的异常
在异常处理的代码块（）中可用到这两个关键字。
sqlcode：返回错误号；sqlerrm：返回错误信息
--语法：
DECLARE
BEGIN
/* 执行内容 */
EXCEPTION ---异常处理部分，
----上面的执行内容报错，才会跳到EXCEPTION这里执行这个
WHEN 异常错误信息名称 THEN执行内容
----如果报的错误是这个异常名执行 THEN 后面的内容
WHEN 异常名 THEN 执行内容;
END;
DECLARE
V_NAME VARCHAR(20);
BEGIN
SELECT ENAME INTO V_NAME FROM EMP WHERE 1=0;
EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- DBMS_OUTPUT.put_line('错误信息'||SQLERRM);
WHEN OTHERS THEN --用others一样 他表示其他所有异常
DBMS_OUTPUT.put_line('错误信息'||SQLERRM);
DBMS_OUTPUT.put_line('错误编号'||sqlcode);
END;
--需要注意的点我们异常处理信息可以任意次序排序 但是others要放到最后
5.非预定义异常
所有的报错信息都可以去定义成异常,oracle 存在错误代码，没有错误异常名称，
即其他标准的ORACLE错误。（也就是数据库定义好了异常信息和异常编码没有定义异常名称）
对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：
1、在PL/SQL?块的声明定义部分定义异常名称：
异常名称 EXCEPTION;
2、将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：
PRAGMA EXCEPTION_INIT(异常名称, 错误代码 ) ;
3、在PL/SQL?块的异常情况处理部分对异常情况做出相应的处理。
6.用户定义异常
用户定义的异常错误是通过显式使用 raise 语句来触发。
当引发一个异常错误时，控制就转向到 exception块异常错误部分，执行错误处理代码。 对于这类异常情况的处理，步骤如下：
1、在PL/SQL?块的声明定义部分定义异常情况：
异常名称EXCEPTION;
2、执行部分，RAISE异常名称
视图
--视图可以理解为数据库中一张虚拟的表 他是建立在已有表的基础上
--可以通过一张表 或者多张源表进行关联出来的虚拟逻辑表
--视图只有逻辑定义 不占用物理空间 每次使用就相当于重新执行了sql语句
--如果源表的数据有所变化 那么视图也会跟着变化
如果没有权限 则： GRANT CREATE VIEW TO 用户名；
语法：CREATE [OR REPLACE] [FORCE] VIEW 视图名称
AS
查询语句
例：
建立一个视图 展示员工编号，员工名称，部门编号，部门名称
CREATE VIEW V_EMP_DEPT
AS
SELECT A.EMPNO,A.ENAME,A.DEPTNO,B.DNAME
FROM EMP A INNER JOIN DEPT B
ON A.DEPTNO =B.DEPTNO
SELECT * FROM V_EMP_DEPT
1.TH READ ONLY--只读
建立一个只读视图 只能查询数据 不能更改数据 展示员工编号，员工名称，部门编号，部门名称
CREATE VIEW V_EMPDEPT1
AS
SELECT A.EMPNO,A.ENAME,A.DEPTNO,B.DNAME
FROM EMP1 A LEFT JOIN DEPT1 B
ON A.DEPTNO = B.DEPTNO
WITH READ ONLY
SELECT * FROM V_EMPDEPT1
--如果加上WITH READ ONLY 他就变成只读 我们只能查看数据 不能对数据进行更改
2.TH CHECKPOINT --给视图增加限制
CREATE VIEW V_EMP1_WHERE
AS
SELECT *
FROM EMP1
WHERE ENAME LIKE 'M%'
WITH CHECK OPTION ;
SELECT * FROM V_EMP1_WHERE
INSERT INTO V_EMP1_WHERE(EMPNO,ENAME,JOB)
SELECT '666','QWER','QWETW' FROM DUAL;
----ORA-01402: 视图 WITH CHECK OPTION where 子句违规
INSERT INTO V_EMP1_WHERE(EMPNO,ENAME,JOB)
SELECT '666','MWER','QWETW' FROM DUAL;
3.视图：
DROP VIEW V_EMP1_WHERE
SELECT * FROM V_EMP1_WHERE
4.用户下所有视图
SELECT * FROM USER_VIEWS
PLSQL程序块
/*PLSQL 过程化的SQL语言 他不像普通的sql语句 普通的sql语句没有流程控制 也没有变量
因此plsql可以实现较为复杂的逻辑他是Oracle对sql语句的扩展它允许在其内部嵌套普通的sql语句
这样就可以把普通sql的数据操控能力和plsql的过程能力结合到一起*/
--sql语言只是访问操作数据库并不具有流程控制
--plsql是一种高级数据库程序语言 该语言专门用于在各环节下的Oracle数据库中访问
1.PLSQL的优点
1.有利于客户服务器环境的运行
2.过程化
3.模块化
4.兼容性
5.重用性
6.安全性
7.性能更好 易用快速
2.PLSQL程序块结构:
DECLARE
/*声明部分
再此声名 变量|常量
游标
存储过程
函数*/
BEGIN
/*执行部分
过程
赋值
sql语句
异常*/
END;
2.DBMS_OUTPUT --打印
DBMS_OUTPUT.PUT_LINE(参数|函数) --用户在程序块例输入参数 用于输出信息并回车换行
3.注意：
1.plsql程序块只能执行一次，不在数据库保存
2.变量必须在声名处声名然后在使用，一个变量只能存储一个数据 可以使用单行函数和表达式
3.常量只能赋值一次 &接收输入值
例：
编写一个程序块，输入一个员工名称 打印输出员工的姓名职位工资
DECLARE
V_NAME VARCHAR2(10):='&员工姓名';
V_SAL NUMBER;
V_JOB VARCHAR2(20);
BEGIN
SELECT SAL,JOB INTO V_SAL,V_JOB FROM EMP
WHERE ENAME = V_NAME;
DBMS_OUTPUT.PUT_LINE(V_SAL||'/'||V_JOB||'/'||V_NAME);
END;
游标
--可以理解为实在数据库内存中的一个缓冲区，是一种能包括多条数据记录的结果集中 每次提取一条的机制
--决定对结果集进行处理时必须声名一个指向结果集的游标，
--对查询结果进行逐条处理，也就是可以逐条抓取查询结果数据
--游标有静态游标和动态游标在使用之前游标的定义已经完成 在打开时可以进行更改
游标的定义和操作 分成以下4个步骤：
1. 声明游标
在 declare部分去声明，
cursor 游标名[(参数1 数据类型,参数2 数据类型)] is sql语句;
参数为可选，如果定义了参数，必须 打开游标的时候 输入相应的参数值
sql语句可以是 表 视图等这样的查询。
2. 打开游标
在 执行部分 打开，--begin 后边
open 游标名[(参数1 数据类型,参数2 数据类型)];
打开游标的时候， 把select 查询的结果集 传送了 游标 工作缓冲区
3. 提取 游标数据 -- FETCH 提取
在执行部分，将游标缓冲区的 数据 提取到 变量中。 提取操作在打开游标之后进行
fetch 游标名 into 变量1，变量2...;
(2)fetch 游标名 into 变量%rowtype;
如果需要返回很多行数据记录，可以使用循环来实现
4. 关闭游标
-- 游标带参数与不带参数的区别
不带参数 ，结果集永远不变
带参数，在open 游标名(n) 时，n可以是不同的值，可以多次打开游标传
值，每次传不同的值，结果不相同。
close 游标名; --关闭游标
显示游标 打开后，一定要 显式去关闭。游标关闭后，占用的资源就会被释放
DECLARE
CURSOR 游标名 [参数1 数据类型，参数2数据类型，参数3数据类型。。。。] IS SQL语句
BEGIN
OPEN 游标名 [参数1 数据类型，参数2数据类型，参数3数据类型。。。。] --打开游标
CLOSE 游标名;
END;
例：
定义游标cur_EMP提取指定员工7788的姓名和工资
--没有参数
DECLARE
V_NAME VARCHAR2(20);
V_SAL NUMBER;
CURSOR cur_EMP IS SELECT ENAME,SAL FROM EMP WHERE EMPNO = '7788';
BEGIN
OPEN cur_EMP;
-- IF cur_EMP%ISOPEN THEN
FETCH cur_EMP INTO V_NAME,V_SAL;
DBMS_OUTPUT.PUT_LINE('姓名是：'||V_NAME||'工资是：'||V_SAL);
-- END IF;
CLOSE cur_EMP;
END;
--带参数的
DECLARE
V_NAME VARCHAR2(20);
V_SAL NUMBER;
CURSOR CUR_EMP(ENO NUMBER) IS SELECT ENAME,SAL FROM EMP WHERE EMPNO = ENO;
BEGIN
OPEN CUR_EMP(7788);
-- IF CUR_EMP%ISOPEN THEN
FETCH CUR_EMP INTO V_NAME,V_SAL;
DBMS_OUTPUT.PUT_LINE('姓名是：'||V_NAME||'工资是：'||V_SAL);
--END IF;
CLOSE CUR_EMP;
END;
---循环
--FOR 循环 不需要打开游标
例：DECLARE
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO = 10;
A C_EMP%ROWTYPE;
BEGIN
--open C_EMP;
-- CLOSE C_EMP;
FOR I IN C_EMP LOOP --1..3
DBMS_OUTPUT.PUT_LINE('第'||C_EMP%ROWCOUNT||'条的员工姓名是：'||I.ENAME||'员工工资是：'||I.SAL);
END LOOP;
-- DBMS_OUTPUT.PUT_LINE(C_EMP%ROWCOUNT); --推出循环关闭游标
open C_EMP;
DBMS_OUTPUT.PUT_LINE(C_EMP%ROWCOUNT);
CLOSE C_EMP;
END;
创建一个程序块， 使用游标 并用for循环来打印输出部门表的部门编号和部门的名称
DECLARE
CURSOR C_DEPT IS SELECT A.DEPTNO,A.DNAME FROM DEPT A;
A C_DEPT%ROWTYPE;
BEGIN
FOR I IN C_DEPT LOOP
DBMS_OUTPUT.PUT_LINE('部门编号是：'||I.DEPTNO||'员工工资是：'||I.DNAME);
END LOOP;
END;
--LOOP
例：DECLARE
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO = 10;
V_EMP C_EMP%ROWTYPE;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO V_EMP;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||C_EMP%ROWCOUNT||'条的员工姓名是：'||V_EMP.ENAME||'员工工资是：'||V_EMP.SAL);
END LOOP;
CLOSE C_EMP;
END;
--WHILE 循环
例：DECLARE
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO = 10;
V_EMP C_EMP%ROWTYPE;
BEGIN
OPEN C_EMP;
FETCH C_EMP INTO V_EMP;
WHILE C_EMP%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE('第'||C_EMP%ROWCOUNT||'条的员工姓名是：'||V_EMP.ENAME||'员工工资是：'||V_EMP.SAL);
DBMS_OUTPUT.PUT_LINE('第'||C_EMP%ROWCOUNT||'条的员工姓名是：'||V_EMP.ENAME||'员工工资是：'||V_EMP.SAL);
FETCH C_EMP INTO V_EMP;
DBMS_OUTPUT.PUT_LINE('第'||C_EMP%ROWCOUNT||'条的员工姓名是：'||V_EMP.ENAME||'员工工资是：'||V_EMP.SAL);
END LOOP;
CLOSE C_EMP;
END;
--游标指针指向只能往下没办法往上
隐式游标
--主要运用到dml操作 就是 INSERT UPDATE DELETE
--当系统使用一个隐式游标是可以通过游标的属性来了解操作状态和结果，隐式游标可以使用名字sql来访问
--但是要注意通过sql游标名总是只能访问前一个dml操作或单行select操作的游标属性
----游标分为四种属性
|%rowcount 数值 --返回记录数 可以理解为有几条数
|%found 布尔类型 --返回一条数据则为真否则为假
|%notfound 布尔类型 --属性相反没有找到数据则是true 通常运用到退出循环
|%isopen 布尔类型 -- 游标是否开启，true 打开 false 关闭
在之前程序中用到单行SELECT ..INTO 查询语句，一次只能从数据库中提取一行数据，
对于这种形式的查询 和DML操作，系统都会使用一个隐式游标。但是如果要是提取多行数据，
就要用到我们上面所说的显示游标。
触发器
1、触发器
触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，
存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。
触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用
然后，触发器的触发条件其实在你定义的时候就已经设定好了。
2、触发器的分类
（1）DML触发器
对数据表进行DML语句操作（如INSERT、UPDATE、DELETE）时所触发的触发器，可以分为：
语句级触发器或行级触发器：行级触发器会对数据库表中的受影响的每一行触发一次触发器代码，
语句级触发器则只触发一次，与语句所影响到的行数无关
（2）替代触发器（INSTEAD OF触发器）
对视图进行操作时定义的触发器，替代触发器只能定义在视图上
（3）系统事件触发器
DML触发器和替代触发器都是在DML事件上触发的 ，而系统触发器是在进行数据库系统事件时进行触发，主要包括DDL语句。
一个触发器只能针对监控一个对象，同一个对象上可以创建多个触发器。
触发器作用：数据的同步，数据的备份，数据的检查
触发器里面的限制：触发器里不能写事务语句(commit,rollback),触发里的代码和我们监控的对象
上的事务是一个触发器中数据类型不能使用long raw类型
3、DML触发器
对数据表进行DML语句操作（如INSERT、UPDATE、DELETE）时所触发的触发器，可以分为：
语句级触发器或行级触发器：行级触发器会对数据库表中的受影响的每一行触发一次触发器代码，
语句级触发器则只触发一次，与语句所影响到的行数无关
4.语法：
CREATE [OR REPLACE] TRIGGER TRIGGER_NAME
BEFORE | AFTER TRIGGER_EVENT
ON TABLE_NAME
[FOR EACH ROW] --省略不写，则为语句级触发器
[WHEN 条件] -- WHEN条件只允许在行级上指定
[DECLARE]
--声明部分
BEGIN
PL/SQL语句
END;
5.语法解释：
TRIGGER_NAME：触发器名称
BEFORE | AFTER : 指定触发器是在触发事件发生之前触发还是发生之后触发
TRIGGER_EVENT：触发事件，指明哪些数据库动作会触发此触发器，
在DML触发器中主要为INSERT（数据库插入会触发此触发器）、UPDATE（数据库修改会触发此触发器）、DELETE（数据库删除会触发此触发器）等
TABLE_NAME：表名，表示发生触发器作用的对象
FOR EACH ROW：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
WHEN ：添加的触发条件
6.DML触发器基本要点：
触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，
以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，
以便记录该操作或做某些事后处理。
触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。
既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，
为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。
1.INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2.UPDATING [（COLUMN_1,COLUMN_2,…,COLUMN_X）]：当触发事件是UPDATE 时，如果修改了COLUMN_X列，
则取值为TRUE，否则为FALSE。其中COLUMN_X是可选的。
3.DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
触发对象：指定触发器是创建在哪个表、视图上。
触发类型：是语句级还是行级触发器
触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件。
:NEW --记录变量，引用最新的列值;
:OLD --记录变量，引用以前的列值;
这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在.且UPDATE语句两个都有,而INSERT只有:NEW ,DELECT 只有:OLD;
7.行级触发器
行级触发器会对数据表中的受影响的库每一行触发一次触发器代码
CREATE [OR REPLACE] TRIGGER TRIGGER_NAME
BEFORE | AFTER TRIGGER_EVENT
ON TABLE_NAME
[FOR EACH ROW] --省略不写，则为语句级触发器
[WHEN 条件] WHEN条件只允许在行级上指定
[DECLARE]
--声明部分
BEGIN
PL/SQL语句
END;
例：建立一个触发器, 当用户对EMP1表执行DML(DELETE)语句时，将相关信息记录到日志表TIG_EMP_LOG
创建日志表 TIG_EMP_LOG
CREATE TABLE TIG_EMP_LOG(DML VARCHAR2(30)
,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)
,DATA_DATE DATE
);
SELECT * FROM TIG_EMP_LOG
创建触发器
CREATE OR REPLACE TRIGGER TIG_DEL_EMP1
BEFORE ---- 触发时间执行DML操作之前触发
DELETE ---- 触发事件 ，DELETE
ON EMP1 ---- 触发的对象
FOR EACH ROW --- 行级触发器
WHEN (OLD.DEPTNO =10) --添加的触发条件,只有删除部门10时触发
BEGIN
INSERT INTO TIG_EMP_LOG
(DML,EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,DATA_DATE)
VALUES
('D',
:OLD.EMPNO,
:OLD.ENAME,
:OLD.JOB,
:OLD.MGR,
:OLD.HIREDATE,
:OLD.SAL,
:OLD.COMM,
:OLD.DEPTNO,
SYSDATE
); --OLD 引用以前的列值
END ;
--测试
DELETE FROM EMP1 WHERE DEPTNO=10;
SELECT * FROM TIG_EMP_LOG;
自定义函数
1.函数
函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
它是一种存储在数据库中的命名程序块。
函数与存储过程相似，也是数据库中存储的已命名PL-SQL程序块。
函数的主要特征是它必须有一个返回值。通过return来指定函数的返回类型。
2.自定义函数语法：
CREATE OR REPLACE FUNCTION 函数名(参数1 模式 参数类型) RETURN 返回值类型
AS|IS
变量1 变量类型;
变量2 变量类型;
BEGIN
函数体(执行内容);
RETURN 返回值; --函数必须要有返回值
END 函数名;
3.函数名：如果数据库中已经存在了此名称，则可以指定 OR REPLACE关键字，这样新的函数将覆盖掉原来的函数。
参数：可选项。
模式：参数的模式有3种，
（1）IN 输入参数 可以省略,表示输入给函数的参数
（2）OUT输出参数 ： out 不能省略,示参数在函数中被赋值，可以传给函数调用 程序
（3）in out 参数:表示参数既可以传值也可以被赋值.
返回值类型：必选项，返回函数的类型。并且在函数体部分（BEGIN部分），必须使用 RETURN语句返回函数值，返回值的类型要与函数声明时的类型相同。
例1:
创建一个函数判断传入日期来获取上一旬的第一天
CREATE OR REPLACE FUNCTION F_DATE(B IN DATE) RETURN STRING
IS
A DATE;
C VARCHAR2(20);
BEGIN
A:=B;
SELECT CASE WHEN TO_CHAR(A,'DD') <= 10 THEN TO_CHAR(ADD_MONTHS(A,-1),'YYYYMM')||'21'
WHEN TO_CHAR(A,'DD') BETWEEN 11 AND 20 THEN TO_CHAR(A,'YYYYMM')||'01'
WHEN TO_CHAR(A,'DD') BETWEEN 21 AND 31 THEN TO_CHAR(A,'YYYYMM')||'11'
END
INTO C
FROM DUAL;
RETURN C;
END F_DATE;
调用F_DATE函数；
SELECT F_DATE(DATE'2022-12-05') FROM DUAL;
select TO_CHAR(ADD_MONTHS(DATE'2023-03-01',-1),'YYYYMM')||'21' from DUAL;
select TO_CHAR(DATE'2023-03-01','YYYYMM')from DUAL
程序块调用
DECLARE
V_NUM NUMBER:=F_DATE(SYSDATE);
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NUM);
END;
例2：
创建一个函数 传参输入部门编号返回该部门的人数 使用输出参数传出部门名称
CREATE OR REPLACE FUNCTION F_COUNT_ENAME(DNO IN NUMBER ,F_DNAME OUT VARCHAR2) RETURN NUMBER
IS
V_COUNT NUMBER;
V_DNAME VARCHAR(20);
BEGIN
SELECT COUNT(*) ,B.DNAME INTO V_COUNT,V_DNAME --值赋给变量
FROM EMP A
LEFT JOIN DEPT B ON A.DEPTNO = B.DEPTNO WHERE B.DEPTNO = DNO
GROUP BY B.DNAME;
F_DNAME:=V_DNAME; --变量赋给输出参数
RETURN V_COUNT; --返回 变量值
END F_COUNT_ENAME;
--out不能用sql调用他只能用程序块调用
DECLARE
DNAME VARCHAR2(20);
REN NUMBER;
BEGIN
REN:=F_COUNT_ENAME(10,DNAME);
DBMS_OUTPUT.PUT_LINE('10部门的人数是'||REN||'部门名称是：'||DNAME);
END;
-- IN OUT参数：表示参数既可以传值也可以赋值
例3：
创建一个函数 传参输入部门编号，返回该部门的人数，输出传出的部门名称
CREATE OR REPLACE FUNCTION F_CT_DNO(DNO IN OUT VARCHAR2) RETURN VARCHAR2
IS
V_COUNT NUMBER;
V_DNAME DEPT.DNAME%TYPE;
BEGIN
SELECT D.DNAME,COUNT(1) AS CT INTO V_DNAME,V_COUNT
FROM DEPT D LEFT JOIN EMP E
ON D.DEPTNO= E.DEPTNO
WHERE D.DEPTNO = DNO
GROUP BY D.DEPTNO,D.DNAME;
DNO:= V_DNAME;
RETURN V_COUNT;
END F_CT_DNO;
DECLARE
V_DNAME VARCHAR2(20);
V_DEPT VARCHAR2(40);
BEGIN
V_DEPT := 10;
V_DNAME:= F_CT_DNO(V_DEPT);--V_DEPT 赋值为10传入进去10部门，函数的返回值为部门的人数，为v_danme，传出来的值赋值给v_dept
DBMS_OUTPUT.PUT_LINE(V_DNAME||'-------'||V_DEPT);
END;
删除函数：
DROP FUNCTION 函数名称；
注意：
1.函数参数只有返回类型 没有大小
2.执行成功不是真的成功 要去编辑器中重新执行
存储过程
1、存储过程
它是一种存储在数据库中的命名程序块。它不可以被SQL语句直接执行或调用。
在oracle数据库中存储过程是procedure。
2.优点：
1.效率高：
2.降低网络流量：
3.复用性高：
4.可维护性高：
5.安全性高：
3.存储过程基本结构：
Oracle存储过程包含三部分：存储过程声明，执行过程部分，存储过程异常（可写可不写
CREATE OR REPLACE PROCEDURE 存过名称
--创建一个存储过程， OR REPLACE 如果此存过已经存在，则替换旧存过。如不存在，就创建一个新的存过
[( 参数 IN 数据类型,
参数 OUT 数据类型,
参数 IN OUT 数据类型 )]
IS |AS 可以IS 也可以 AS
-- 声明部分
BEGIN --执行部分
/* 执行内容*/
EXCEPTION --处理异常部分
END 存过名称;
1.OR REPLACE 如果数据库中已经存在了此名称，则可以指定 OR REPLACE关键字，这样新的存储过程将覆盖掉原来的存储过程。
2.参数： （可选）
（1）IN 输入参数，默认模式，可以省略,表示输入给函数的参数
（2）OUT 输出参数，只能在存过内部赋值，表示该参数可以将某个值传递回调用它的存储过程
（3）IN OUT输入输出，该参数可以向该存过中传值，也可以将某个值传出去
3.BEGIN：执行部分
4.EXCEPTION:异常处理部分
无参数例子
例：写一个存储过程，(原表EMP1，目标表 EMP2)打印显示新入职的员工姓名，入职日期
抽取每天新入职的EMP1表的数据插入到 目标表 EMP2
CREATE OR REPLACE PROCEDURE P_EMP12
IS
V_ENAME VARCHAR2(10);
V_DATE VARCHAR2(20);
BEGIN
--赋值
SELECT ENAME,TO_CHAR(HIREDATE,'YYYYMMDD') INTO V_ENAME,V_DATE
FROM EMP1
WHERE HIREDATE = TRUNC(SYSDATE);
DBMS_OUTPUT.PUT_LINE('新入职的员工姓名是：'||V_ENAME||'，入职日期是：'||V_DATE);
---将目标表当天数据清空
DELETE FROM EMP2 WHERE TO_DATE(HIREDATE,'YYYYMMDD') = TRUNC(SYSDATE);
--插入数据
INSERT INTO EMP2(empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp1
WHERE HIREDATE = TRUNC(SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_EMP12;
存储过程是事先经过编译并存储在数据库中的SQL语句的集合。现在是保存到数据库服务器里面了，
检查存过的里面代码（不管里面的代码有无错误都会创建成功），并不会执行里面的内容。
需要用户调用存储过程才会去执行里面的内容
存储过程是编译后的代码，被其调用执行效率非常高。
如果里面的代码有错误，编辑存储过程进行修改。
如不改调用的时候会报错。(ORA-06575: 程序包或函数 P_EMP 处于无效状态)
--调用 他不可以被sql语句直接调用
1.CALL P_EMP12();
SELECT EMP1.*,ROWID FROM EMP1 ;
SELECT * FROM EMP2;
2.
BEGIN
P_EMP12;
END;
有参数例子IN
例：
写一个存储过程，输入一个员工编号，如果雇员提成>400 则在原来的基础上加100 如果是0 或者null 则设置成200 并打印更新的提成
CREATE OR REPLACE PROCEDURE P_COMM_SAL(V_ENO IN NUMBER)
IS
V_COMM NUMBER;
V_RCOMM NUMBER;
BEGIN
SELECT NVL(COMM,0) INTO V_COMM FROM EMP1
WHERE EMPNO = V_ENO;
UPDATE EMP1 SET
COMM = CASE WHEN COMM > 400 THEN COMM + 100
WHEN COMM = 0 OR IS NULL THEN 200
END
WHERE EMPNO = V_ENO
RETURNING COMM INTO V_RCOMM ;
DBMS_OUTPUT.PUT_LINE('原提成是：'||V_COMM||',新提成是；'||V_RCOMM);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_COMM_SAL;
CALL P_COMM_SAL(7839)
SELECT * FROM EMP1
------------OUT
例：
创建一个存过 定义两个输出参数 输出部门的名称和地址
CREATE OR REPLACE PROCEDURE P_DEPT(V_DNO IN NUMBER,V_DNAME OUT VARCHAR2, V_LOC OUT VARCHAR2)
IS
BEGIN
--赋值
SELECT DNAME,LOC INTO V_DNAME,V_LOC FROM DEPT WHERE DEPTNO = V_DNO;
--异常
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_DEPT;
--调用 如果有输出变量只能用程序块调用
DECLARE
V_ENAME VARCHAR2(30);
V_LOC VARCHAR2(30);
BEGIN
P_DEPT(20,V_ENAME,V_LOC);
DBMS_OUTPUT.PUT_LINE('部门编号20的名称是'||V_ENAME||'地址是'||V_LOC);
END;
----IN OUT
例：
创建一个存过，定义一个 IN OUT 模式的参数 输入部门编号 输出部门名称
CREATE OR REPLACE PROCEDURE P_DEPT(DNO IN OUT VARCHAR2)
IS
V_NAME VARCHAR2(20);
BEGIN
SELECT DNAME INTO DNO
FROM DEPT
WHERE DEPTNO = DNO;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_DEPT;
--调用
declare
V_DNAME VARCHAR2(30);
BEGIN
V_DNAME := 10;
P_DEPT(V_DNAME);
DBMS_OUTPUT.PUT_LINE(V_DNAME);
END;
--删除存储过程
drop procedure 存储过程名称;
--数据字典
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE'; -- 查看当前用户存过
3286
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
