oracle 基本使用

sql 专栏收录该内容
1 篇文章 0 订阅

oracle基本使用

安装配置相关

下载破解包,直接exe安装,一些常用命令(cmd)
sqlplus /nolog
SQL> conn / as sysdba;
SQL> alter user scott account unlock;(解锁)
SQL> GRANT RESOURCE ,CONNECT TO SCOTT;(给权限)
CONNECT角色:仅具有创建SESSION的权限
RESOURCE角色:仅具有创建CLUSTER,INDEXTYPE,OPERATOR,PROCEDEURE,SEQUENCE,TABLE,TRIGGER,TYPE的权限。
PL/SQL链接别的电脑上的oracle需配置tnsnames.ora文件

条件查询和运算符

SQL(Structured Query Language), 是一种结构化的查询语言简称。
主要是对数据库中数据进行分析、统计、处理、存储等操作。
SQL语句不区分大小写!但是在查某个值的时候,值要区分大小写!

/*
– 1.查询结构
SELECT *|列名|表达式 – * 表示表的所有列名称;多个列或者多个表达式用逗号分隔
FROM 表名|结果集 – 查看的对象为表或者结果集
WHERE 条件 – 多个条件用and/or/not 连接
GROUP BY 列名 – 多个列用逗号分隔
HAVING 条件 – 多个条件用and/or/not 连接
ORDER BY 列名 [ASC/DESC] – ASC 升序(默认),DESC 降序

*/

– 1.查询结构(不涉及分组计算)
SELECT *|列名|表达式 – * 表示表的所有列名称;多个列或者多个表达式用逗号分隔
FROM 表名|结果集 – 查看的对象为表或者结果集
WHERE 条件 – 多个条件用and/or/not 连接
ORDER BY 列名 [ASC/DESC] – ASC 升序(默认),DESC 降序

【注意】:SELECT 和 FROM 是必须要有的, 其它关键字看题目需求使用!

-- 查看所有列
SELECT * 
  FROM student_info;
-- 查看学号和姓名
SELECT sno,sname
  FROM student_info;
-- 查看学号为6的学生信息
SELECT *
  FROM student_info
 WHERE sno=6;
-- 查看学号为6且专业为统计学
SELECT *
  FROM student_info
 WHERE sno=6 AND major='统计学';
-- 查看在2014年之后毕业的学生信息,按照毕业时间从前往后排序
SELECT *
  FROM student_info
 WHERE graduated_year>2014
 ORDER BY graduated_year;
-- 查看在2014年之后毕业的学生信息,按照毕业时间从前往后排序,毕业时间相同的按照性别降序排
SELECT *
  FROM student_info
 WHERE graduated_year>2014
 ORDER BY graduated_year ASC, sex DESC;  -- 默认排序为asc升序
 


练习:查询在2014年后毕业的,专业不为统计学的学生学号、姓名和专业,并按照毕业年份升序排列,年份一样的按照性别降序排列。

– 2.表和字段的别名
【用途】多表查询时候简化表名长度,字段给别名便于理解查询含义
– 字段给别名可以加AS 也可以不加AS, 表给别名不能加AS
SELECT sno AS 学号,sname AS 姓名 FROM STUDENT_INFO e; – 学号为sno别名,姓名为sname别名,s 为表STUDENT_INFO的别名
– 当select中含有*, 以及其它字段或表达是, 一定要用表名.* 或者表别名.*
SELECT e., sysdate AS 当前时间 FROM STUDENT_INFO e; – sysdate为系统日期函数, 看做一个表达式, 可以直接在select 中用
SELECT STUDENT_INFO.
, sysdate AS 当前时间 FROM STUDENT_INFO;

– 3.虚拟表
SELECT 10/2 FROM DUAL;
SELECT 1 AS 数字1, ‘1’ AS 字符1 FROM DUAL;

– 4.操作符(熟悉emp 和 dept表)
算术运算,关系运算,和逻辑运算


SELECT sno,
2+3,
6-4,
3*6,
5/2
FROM student_info;

关系运算:> >= = < <= <> !=
SELECT *
FROM student_info
WHERE sno>=3 AND sno<=6;

逻辑运算: NOT>AND>OR
SELECT (CASE WHEN 1=1 OR 1=3 AND 1=2 THEN 1
ELSE 2
END) AS FLAG
FROM DUAL;

– 5.NULL操作(’’,NULL)
在查询条件中NULL值用IS NULL作条件,非NULL值用 IS NOT NULL做条件
空值的一些特性:
1、空值跟任何值进行算术运算,得到的结果都为空值
2、空值跟任何值进行关系运算,得到的结果都为不成立
3、空值不参与任何聚合运算
4、排序的时候,空值永远是最大的
– 查询奖金为空
SELECT * FROM EMP WHERE COMM IS NULL;
– 1
SELECT e.*, COMM+100, SAL + COMM FROM EMP e;
– 2
SELECT * FROM EMP WHERE COMM<>0;
– 3
SELECT COUNT(COMM) FROM EMP;
– 4
SELECT * FROM EMP ORDER BY COMM ASC;

– 6.IN 操作
查询出工作职责是’SALESMAN’或者’PRESIDENT’或者’ANALYST’的员工信息。
SELECT ename,job FROM emp WHERE job = ‘SALESMAN’ OR job = ‘PRESIDENT’ OR job = ‘ANALYST’;
SELECT ename,job FROM emp WHERE job IN (‘SALESMAN’,‘PRESIDENT’,‘ANALYST’);

– 7.BETWEEN…AND…(包含边界从小到大顺序)
查询列值包含在指定区间内的行,包含边界。
查询工资大于等于1500且小于等于2000的员工信息。
SELECT *
FROM emp
WHERE sal >=1500 AND sal <=2000;

SELECT *
  FROM emp
 WHERE sal BETWEEN 1500 AND 2000;
 
查询工资在1500到2000之间的员工信息
SELECT *
  FROM emp
 WHERE sal >1500 AND sal <2000;
 
-- 查询不到值
SELECT *
  FROM emp
 WHERE sal BETWEEN 2000 AND 1500;

– 8.LIKE模糊查询(对字符串)
字符匹配操作可以使用通配符’%‘和’_’:
%:代表零个或多个字符。
:代表任意一个字符。
SELECT * FROM student_info WHERE sname LIKE ‘张%’;
SELECT * FROM student_info WHERE sname LIKE '张晓
’;

练习:查询员工名称第二个字母为A, 最后一个字母为S的员工的姓名、工资。

合函数和分组查询

– 1.查询结构(包含分组计算)
SELECT – 5
FROM – 1
WHERE – 2
GROUP BY – 3 字段或表达式(多个字段或表达式用逗号隔开)
HAVING – 4 一般为聚合函数表达式, 前面要有GROUP BY,多个表达式用OR或者AND隔开
ORDER BY – 6

– 2.聚合函数
聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。
聚合函数与group by 使用(字段需要分组,聚合函数必须要跟group by 联用)
注意:SELECT 中除了聚合函数包裹的内容,其它字段必须包含于group by 后面的字段,SELECT 只是选取聚合后的字段和值

AVG	     平均值
SUM	     求和
MIN、MAX 最小值、最大值
COUNT	 统计个数

-- 计算员工表的总体平均工资、总工资、总人数、工龄最大和最小的入职日期
SELECT AVG(sal) AS 平均工资,
	   SUM(sal) AS 总工资,
	   COUNT(empno) AS 总人数,
	   MIN(hiredate) AS 最早入职日期,
	   MAX(hiredate) AS 最晚入职日期
  FROM emp;
  
  
-- 计算各个部门员工的平均工资、总工资、总人数、工龄最大和最小的入职日期
     SELECT deptno AS 部门员,
     AVG(sal) AS 平均工资,
     SUM(sal) AS 总工资,
     COUNT(DISTINCT empno) AS 总人数,
     MIN(hiredate) AS 工龄最大入职日期,
     MAX(hiredate) AS 工龄最小入职日期
     FROM emp
     GROUP BY deptno;



-- 聚合函数运算问题(count对列时,有空值的列不会计数)  
SELECT COUNT(*),    --15
	   COUNT(1),    --15
	   COUNT(1000), --15
	   COUNT(COMM)  --4
  FROM EMP;
  


-- 例题:
-- 统计班级男女人数
SELECT SEX,COUNT(sno) FROM STUDENT_INFO GROUP BY SEX;                              
-- 统计班级在2014年后毕业的男女人数
SELECT SEX,COUNT(sno) FROM STUDENT_INFO WHERE GRADUATED_Y>2014 GROUP BY SEX;       
-- 统计班级在2014年后毕业的男女人数,只显示人数大于2的情况
SELECT sex AS 性别,
	   COUNT(sno) AS 人数   -- select中非聚合的字段必须包含于group by 的字段中
  FROM student_info
 WHERE graduated_year>2014
 GROUP BY sex
HAVING COUNT(sno)>2        -- 不能用  人数>2
 ORDER BY sex DESC;        -- 可以用  性别 DESC
 
-- 以下错误
SELECT sex AS 性别,
       graduated_year AS 毕业年月,       -- group by 的字段中没有graduated_year, 并且graduated_year 没有被聚合函数运算
	   COUNT(sno) AS 人数                -- group by 的字段中没有sno, 但是sno 被聚合函数运算(语句可行)
  FROM student_info
 WHERE graduated_year>2014
 GROUP BY sex
HAVING COUNT(sno)>2        -- 不能用  人数>2
 ORDER BY sex DESC;        -- 可以用  性别 DESC
 
 
  
/*
-- 数字为执行顺序
    SELECT       -- 5
FROM         -- 1
WHERE        -- 2
GROUP BY     -- 3                                   
HAVING       -- 4                  
ORDER BY     -- 6
*/

练习:1.按部门和工作分组,求平均工资
2.查询EMP表中员工人数大于3的所在部门的部门编号及对应的人数
3.查询EMP表中各部门工资大于2000的员工人数,并且按人数从高到低排列
4.查询EMP表中排除10号部门,员工人数大于2的所在部门的部门编号,及对应的人数
5.工资在1500到2850之间,除了10号部门的各部门员工平均工资和总人数
6.查找部门员工最高工资在1500到2850之间,除了10号部门的各部门平均工资和人数

子查询和集合运算

– 1.子查询
子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。
1.单行子查询:不向外部返回结果,或者只返回一行结果。
2.多行子查询:向外部返回零行、一行或者多行结果。
– 单行查询和多行查询
SELECT * FROM EMP WHERE EMPNO=7369; – 单行
SELECT * FROM EMP WHERE DEPTNO=10; – 多行

-- 单行子查询
SELECT * FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE EMPNO=7369);     -- 单行结果"=",并且字段要对应
-- 多行子查询
SELECT * FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO <> 10;   -- 单行结果"IN",并且字段要对应

-- 查询EMP表中每个部门的最低工资的员工信息(尝试练习)
SELECT * FROM EMP WHERE (deptno,sal) IN (SELECT deptno, MIN(sal) AS min_sal FROM EMP GROUP BY deptno);  -- 可以多列对应
  

练习:查询出dept表中销售部(SALES)下面的员工姓名、工作、工资。(子查询)

– 2.集合运算(a={1,2,3} , b={2,3,4})
交集:INTERSECT – 两者共有部分
并集(去重): UNION
并集(不去重):UNION ALL
补集:MINUS – 前者有后者无
当使用集合操作的时候,要注意:
1.查询所返回的列数以及列的类型必须匹配,列名可以不同。
2.只有UNION ALL不会去重。其他三个都需要排序后去重,性能比较差

例题:
1.求员工表和部门表中的共有的部门编号
SELECT DEPTNO
  FROM EMP
INTERSECT
SELECT DEPTNO
  FROM DEPT;
  
2.求员工表或者部门表中所包含的部门编号(不去重)
SELECT DEPTNO
  FROM EMP
 UNION ALL 
SELECT DEPTNO
  FROM DEPT;

3.求员工表或者部门表中所包含的部门编号(去重)
SELECT DEPTNO
  FROM EMP
 UNION
SELECT DEPTNO
  FROM DEPT;

4.求部门表中不在员工表中的部门编号
SELECT DEPTNO
  FROM DEPT
 MINUS
SELECT DEPTNO
  FROM EMP;
-- 
SELECT deptno 
  FROM EMP
MINUS
SELECT deptno
  FROM DEPT;

– 3.去重(DISTINCT, GROUP BY, UNION, ROWID)

客户编号 身份证号

id(唯一) 有重复值

只保留身份证号最大的一条记录,其他记录删除

1 330823198901222111
1 330823198901222112
1 330823198901222113 1 330823198901222113
2 330823198901222113
2 330823198901222114
2 330823198901222115 2 330823198901222115

delete from 表 where 身份证号 not in (select max(身份证号) from 表 group by id )

1 330823198901222111
1 330823198901222112
1 330823198901222113 1 330823198901222113 AAATi0AAEAAAL3wAAA
1 330823198901222113 1 330823198901222113 AAATi0AAEAAAL3wAAB 1 330823198901222113 AAATi0AAEAAAL3wAAB
2 330823198901222113
2 330823198901222114
2 330823198901222115 2 330823198901222115 AAATi0AAEAAAL3wAAC
2 330823198901222115 2 330823198901222115 AAATi0AAEAAAL3wAAD 2 330823198901222115 AAATi0AAEAAAL3wAAD

delete from 表 where 身份证号 not in (select max(身份证号) from 表 group by id )

delete from 表 where rowid not in (select max(rowid) from 表 group by 身份证号)

1 330823198901222111
2 330823198901222112
3 330823198901222113 AAATi0AAEAAAL3wAAA
4 330823198901222113 AAATi0AAEAAAL3wAAB
5 330823198901222113 AAATi0AAEAAAL3wAAC
6 330823198901222114
7 330823198901222115 AAATi0AAEAAAL3wAAA
8 330823198901222115 AAATi0AAEAAAL3wAAB

delete from 表 where rowid not in (select MIN(rowid) from 表 group by 身份证号)

delete from 表 where 身份证号 not in (select max(身份证号) from 表 group by id )

delete from 表 where rowid not in (select max(rowid) from 表 group by 身份证号)

-- 查看部门编号(去重)
SELECT DISTINCT DEPTNO,job, FROM EMP;
SELECT deptno FROM emp GROUP BY deptno;
-- DISTINCT 只能出现在最前,不能一个字段去重一个字段不去重
-- 错误1
SELECT JOB,
	   DISTINCT DEPTNO
  FROM EMP;
-- 错误2
SELECT DISTINCT JOB,
	   DISTINCT DEPTNO
  FROM EMP;

数据发散:多表关联容易造成数据重复或者冗余,
1、列出必要的字段
2、distinct,group by 对子结果集去重后再关联
3、注意表之间的查询条件关联

-- 三种等价去重
-- 性能最好
SELECT deptno,job
  FROM emp
 GROUP BY deptno,job;
-- 性能其次
SELECT DISTINCT deptno,job
  FROM emp;
-- 性能很差
SELECT deptno,job
  FROM emp
UNION
SELECT deptno,job
  FROM emp;

多表查询

– 连接查询(重点)
包括内联接(inner join 1种)和外联接(outer join 3种)
(1)内连接(inner join):inner可省略
内连接写法1(标准写法)
SELECT
FROM 表名1
JOIN 表名2
ON 表名1.字段1=表名2.字段1 – 等值连接
AND 表名1.字段2>=表名2.字段2 – 不等连接
OR …;

	内连接写法2(非标准写法)
	SELECT
	  FROM 表名1,表名2
	 WHERE 表名1.字段1=表名2.字段1      -- 等值连接
	   AND 表名1.字段2>=表名2.字段2     -- 不等连接
		OR ...;
	   
	   
	   
	-- 查询员工信息及其部门信息
	SELECT *                     -- E.*,D.*  / DEPTNO -- 指向对象才行
	  FROM DEPT E 
	  JOIN EMP D                 -- 两张表调换顺序无关
		ON E.DEPTNO = D.DEPTNO;  -- 顺序无关 D.DEPTNO=E.DEPTNO
	-- oracle
	SELECT *
	  FROM EMP E, DEPT D
	 WHERE E.DEPTNO=D.DEPTNO;


	练习: 1.查询部门名称为'SALES'的员工编号、姓名、部门编号及其部门信息
		  2.查看7369员工的员工名称和部门名称
		
		
  
(2)左外连接(left outer join): outer可省略
	 left join 跟表的前后顺序有关
	 左外连接写法1(标准写法)
	 SELECT
	   FROM 表名1                     -- 主表    
	   LEFT JOIN 表名2                -- 从表
		 ON 表名1.字段1=表名2.字段1   -- 等值连接
	    AND 表名1.字段2>=表名2.字段2  -- 不等连接
		 OR ...;
		


	 左外连接写法2(非标准写法)
	 SELECT
	   FROM 表名1,表名2
	  WHERE 表名1.字段=表名2.字段(+);
	  
	  
	-- 查询部门名称为'SALES'部门的信息及其对应的员工信息
	SELECT *
	  FROM DEPT D             -- 主表信息全部展示(关联不上的也要展示)
	  LEFT JOIN EMP E         -- 从表只显示关联得上的信息
		ON D.DEPTNO=E.DEPTNO;
	 WHERE D.DNAME='SALES';   -- WHERE 是对关联后的结果集的过滤
	注意:	
	SELECT *
	  FROM DEPT D             -- 主表信息全部展示(关联不上的也要展示)
	  LEFT JOIN EMP E         -- 从表只显示关联得上的信息
		ON D.DEPTNO=E.DEPTNO
	   AND D.DNAME='SALES';   -- and 是限定关联的条件,不会过滤主表数据
	-- oracle	
	SELECT *
	  FROM DEPT D, EMP E
	 WHERE D.DEPTNO=E.DEPTNO(+)
	   AND D.DNAME='SALES'; 
	  
		
(3)右外连接(right outer join): outer可省略
(4)全外连接(full outer join): outer可省略
	-- FULL JOIN
	SELECT E.*,D.*
	  FROM EMP E
	  FULL JOIN DEPT D
		ON E.DEPTNO=D.DEPTNO;

	-- 错的
	SELECT E.*,D.*
	  FROM EMP E, DEPT D
	 WHERE E.DEPTNO(+)=D.DEPTNO(+); 
	 
练习:1.查询各部门名称及其对应的人数
	  2.查询各部门名称及对应的经理名称
	  3.查询EMP表中每个部门的最低工资的员工信息
	  4.查询员工名称及其直接上级的名称

表的连接方式有哪几种

数据类型;表的DDL和DML操作

DDL(数据定义语言):CREATE/ALTER/DROP /TRUNCATE + 对象类型 + 对象名称
DML(数据操作语言):INSERT INTO/UPDATE/DELETE + 表名

数据库建模:建表
电商行业:
业务建模–>领域建模—>逻辑建模–>物理建模

1.业务建模:熟悉业务相关模块,理清业务主线

商品基本信息(商品基本信息维护)/采购模块(采购下单,营运部收货)/销售模块(收银,库存)/财务模块(结算,收款,对账)

2.领域建模:理清每个模块的流程

商品基本信息维护:录入供应商信息,录入商品基本信息,

采购模块(采购下单,营运部收货):选供应商–>选商品信息—>确认商品定价—>确认商品订货数量–>采购下单–>发送订单给供应商
—>供应商送货到门店或仓库—>收货部确认收货–>生成收货验收单—>生成结算信息

3.逻辑建模:列出每个模块的实体内容
商品信息:商品编号、商品名称
供应商信息:供应商编号、供应商名称
订单信息:

4.物理建模:建表

商品信息表:
CREATE TABLE ITEM
(
ITEM_NO NUMBER(8), --商品编号
ITEM_NAME VARCHAR2(50), --商品名称
SUP_NO NUMBER(5) --供应商
SELL_PRICE NUMBER(12,2) --售价
ENTER_PRICE NUMBER(12,2) --进价
ITEM_SEC_CAT NUMBER(5) --商品子类
ITEM_QTY NUMBER(12,2) --商品数量
ITEM_WEIGHT NUMBER(12,2) —重量
CREATE_DATE DATE,
CREATE_USER VARCHAR2(10),
UPDATE_DATE DATE,
UPDATE_USER VARCHAR2(10)
)

CREATE TABLE SUPPLIER
(
SUP_NO NUMBER(5), --供应商编号
SUP_NAME VARCHAR2(50), --供应商名称
SUP_CONTACT VARCHAR2(10), --联系人
MOBILE_PHONE VARCHAR2(11), --移动电话
OFFICE_PHONE VARCHAR2(15), --办公室电话
FAX_PHONE VARCHAR2(15), – 传真
ADDRESS VARCHAR2(200),

CREATE_DATE DATE,
CREATE_USER VARCHAR2(10),
UPDATE_DATE DATE,
UPDATE_USER VARCHAR2(10)

)

CREATE TABLE ORDER_HEAD
()
CREATE TABLE ORDER_DETAIL
()
CREATE TABLE CATEGORY
()

/--------------DDL---------------------/

– 1.CREATE 创建表
(1)直接创建表
CREATE TABLE 表名
(列名1 数据类型 [,
列名2 数据类型]…
);
– 加注释
COMMENT ON TABLE 表名 IS ‘表注释名’ ;
COMMENT ON COLUMN 表名.列名1 IS ‘字段注释名’;

-- 创建表	 
CREATE TABLE t_stu_info
(sno   NUMBER(10),
 sname VARCHAR2(20),
 cdate DATE
 );
-- 加注释
COMMENT ON  TABLE t_stu_info  IS '学生信息表' ;                  
COMMENT ON COLUMN t_stu_info.sno IS '学号';
COMMENT ON COLUMN t_stu_info.sname IS '姓名';
COMMENT ON COLUMN t_stu_info.cdate IS '创建日期';

问答:列举出oracle,sql server,mysql的常用数据类型:
clob 大文本
blob 二进制,主要保存图片信息

-- 数据类型
1.数字型   NUMBER        -- 默认最大为38位长度整数
		   NUMBER(10)    -- 最大长度为10的整数
		   NUMBER(8,2)   -- 整数最大长度为6位,小数长度为2位
2.字符型   CHAR          -- 默认1位长度的字符
		   CHAR(10)      -- 最大长度为10的字符,输入值不满10位,后面补空格
		   VARCHAR2(10)  -- 最大长度为10的字符,输入值不满10位,后面不会填充
3.日期型   DATE          -- 后面不要加括号(可以到时分秒)
		   TIMESTAMP


       oracle            sql server             MYSQL

1.数字型 NUMBER DECIMAL DECIMAL
NUMBER(10) DECIMAL(10) DECIMAL(10)
NUMBER(8,2) DECIMAL(8,2) DECIMAL(8,2)

2.字符型 CHAR CHAR CHAR
CHAR(10) CHAR(10) CHAR(10)
VARCHAR2(10) VARCHAR(10) VARCHAR(10)

3.日期型 DATE DATETIME DATETIME
DATE
TIMESTAMP TIMESTAMP TIMESTAMP

(2)根据结果集创建表
CREATE TABLE 表名 AS SELECT 语句;        

-- 间接创建
CREATE TABLE t_emp_cp AS SELECT * FROM EMP;            -- 拷贝表结构和数据(不带索引约束等)
CREATE TABLE t_emp_cp1 AS SELECT * FROM EMP WHERE 1=2; -- 拷贝表结构(不带索引约束等)

– 3.ALTER 更改表结构
– 【修改列数据类型】 ALTER TABLE 表名 MODIFY 列名 新的数据类型;
/*修改的列没有数据时,可以随便更改类型;修改的列有数据时,
– 【修改表名】 ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE t_stu_info RENAME TO t_stu_info_cp;

– 4.DROP 删除表对象
– DROP TABLE 表名;
DROP TABLE t_stu_info_cp;

– 5.TRUNCATE(对象是表) 删除表数据
– TRUNCATE TABLE 表名; 不能跟where, 清空整张表数据, 不会将清除的数据写入日志
TRUNCATE TABLE t_stu_info;

/---------------DML--------------------/

– 1.INSERT INTO 插入数据到表中
/只能在同一种类型上增加长度/
ALTER TABLE t_stu_info MODIFY sno VARCHAR2(20);
– 【修改列名】 ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;
ALTER TABLE t_stu_info RENAME COLUMN sno TO snumber;
– 【增加列】 ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE t_stu_info ADD sex CHAR(2);
– 【删除列】 ALTER TABLE 表名 DROP COLUMN 列名;
ALTER TABLE t_stu_info DROP COLUMN sex;
CREATE TABLE T_COURSE_WXP (
CNO NUMBER(10),
CNAME VARCHAR2(20),
CDATE DATE
);
*/
– INSERT INTO 表名(列名1,列名2,……) VALUES (值1,值2……)
INSERT INTO T_COURSE(CNO,CNAME,CDATE) VALUES (1,‘语文’,SYSDATE);
INSERT INTO T_COURSE VALUES (2,‘数学’,DATE’2019-08-19’);
INSERT INTO T_COURSE(CNO,CNAME) VALUES (3,‘英语’);
INSERT INTO T_COURSE SELECT 4,‘科学’, TO_DATE(‘2020-04-08’,‘YYYY-MM-DD’) FROM DUAL;

– 2.UPDATE 更新表数据
– UPDATE 表名 SET 列名1=值, 列名2=值,…… WHERE 条件
UPDATE T_COURSE SET CNAME=‘政治’, CDATE=SYSDATE WHERE CNO=3;

UPDATE EMP E SET JOB = (SELECT JOB FROM EMP_2 E2 WHERE E.EMPNO = E2.EMPNO)

UPDATE T1 

SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);

MERGE INTO EMP E1
USING (SELECT EMPNO,JOB FROM EMP_2) E2
ON (E1.EMPNO = E2.EMPNO)
WHEN MATCHED THEN
UPDATE SET E1.JOB = E2.JOB

–更新记录
CREATE TABLE EMP_NEW as select * from emp
CREATE TABLE EMP_BAK as select * from emp

1、将EMP_NEW中多出来的记录更新到EMP_BAK
2、将EMP_NEW中与EMP_BAK相同的员工中更新过的记录信息同步到EMP_BAK

MERGE INTO EMP_BAK E1
USING (SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP_NEW) E2
ON (E1.EMPNO = E2.EMPNO)
WHEN MATCHED THEN
UPDATE SET E1.JOB = E2.JOB,
– E1.EMPNO = E2.EMPNO, --主键不用更新
E1.ENAME = E2.ENAME,
E1.MGR = E2.MGR,
E1.HIREDATE = E2.HIREDATE,
E1.SAL = E2.SAL,
E1.COMM = E2.COMM,
E1.DEPTNO = E2.DEPTNO
WHEN NOT MATCHED THEN INSERT
(E1.EMPNO,E1.ENAME,E1.JOB,E1.MGR,E1.HIREDATE,E1.SAL,E1.COMM,E1.DEPTNO)
VALUES (E2.EMPNO,E2.ENAME,E2.JOB,E2.MGR,E2.HIREDATE,E2.SAL,E2.COMM,E2.DEPTNO)

– 3.DELETE 删除表数据
– DELETE FROM 表名 WHERE 条件; 删除的数据可以从日志中找回
DELETE FROM T_COURSE WHERE CNO=4;

--MERGE的灵活应用:
--若数据量大时,MERGE删除比DELETE好。

–删除10部门的员工信息
–第一种写法
DELETE FROM EMP WHERE DEPTNO =10 ;
–MERGE INTO 写法
MERGE INTO EMP E
USING (SELECT * FROM EMP WHERE DEPTNO = 10) S
ON (S.EMPNO = E.EMPNO)
WHEN MATCHED THEN
UPDATE SET E.COMM = E.COMM DELETE WHERE 1 = 1;

注意:DROP/DELETE/TRUNCATE的区别(语言类型不同/删除数据是否可跟条件/执行效率/是否可从日志恢复)
DROP 是DDL语句,用于删除一个对象;
DELETE 是DML语句,主要是针对表数据删除, 可以通过条件删除数据, 删除的数据可以从日志中找回,效率低;
TRUNCATE 是DDL语句,主要是针对表数据删除, 不能跟where 条件, 不会将清除的数据写入日志,效率高;

注意事项:
1、删除数据,更新数据之前,先备份表;
2、删除、更新之前,先查询记录,确认无误。

概念:
数据字典:系统数据字典一般以USER_开头
SELECT * FROM USER_TABLES WHERE TABLE_NAME =UPPER(‘t_emp_dept’)

-- 工作中用的比较多
INSERT INTO 表名 SELECT * FROM 结果集; 

练习:先创建一张表, 将emp表的员工编号、员工姓名和其所在的部门编号、部门名称插入到表中
/*
CREATE TABLE t_emp_dept(
empno number(20),
ename varchar2(20),
deptno number(10),
dname varchar2(20)
);

INSERT INTO T_EMP_DEPT
(EMPNO, ENAME, DEPTNO, DNAME)
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

SELECT * FROM T_EMP_DEPT;
*/

–复制表:
1.复制表及表数据,不复制主键,索引,约束
CREATE TABLE EMP1 AS SELECT * FROM EMP;
SELECT * FROM EMP1;

2.删除表中数据
DELETE FROM EMP1; --整表删除,会生成删除日志
DELETE FROM EMP1 WHERE EMPNO =7369; --可删除多条记录或者指定记录,会生成删除日志

SELECT * FROM EMP1 WHERE EMPNO =7369;
或者
TRUNCATE TABLE EMP1; --没有产生日志,整表删除

3.重新插入数据到复制表
方法1:
DROP TABLE EMP1;
CREATE TABLE EMP1 AS SELECT * FROM EMP;
方法2:
INSERT INTO EMP1 SELECT * FROM EMP;
INSERT INTO EMP1(字段1,字段2,…) SELECT 字段1,字段2,… FROM EMP; --列名不一致,指定列名

单行函数

Oracle常用单行函数有:
数字函数:对数字进行计算,返回一个数字。
日期函数:对日期和时间进行处理。
字符函数:对字符串操作。

– 1.数字函数
ABS(x) x绝对值 SELECT ABS(-2) FROM DUAL; – 2
MOD(x,y) x除以y的余数 SELECT MOD(7,3) FROM DUAL; – 1
POWER(x,y) x的y次幂 SELECT POWER(2,3) FROM DUAL; – 8
ROUND(x[,y]) x在第y位四舍五入(默认四舍五入到整数) SELECT ROUND(2.989),ROUND(2.989,2),ROUND(5.989,-1) FROM DUAL; – 3, 2.99, 10
TRUNC(x[,y]) x在第y位截断(默认截断到整数) SELECT TRUNC(2.989),TRUNC(2.989,2),TRUNC(5.989,-1) FROM DUAL; – 2, 2.98, 0
CEIL(x) 向上取整 SELECT CEIL(2.13), CEIL(2.00),CEIL(-2.13) FROM DUAL; – 3, 2, -2
FLOOR(x) 向下取整 SELECT FLOOR(2.89),FLOOR(3.00),FLOOR(-2.89) FROM DUAL; – 2, 3,-3

-- TRUNC配合日期使用
TRUNC(d[,fmt])和ROUND(d[,fmt])对日期处理:
TRUNC(d[,fmt]):
fmt: 'DD'       截取到当月的当天 
fmt: 'DDD'      截取到当月的当天(默认格式)
fmt: 'D'        截取到当周的第一天 
fmt: 'MM'       截取到当月的第一天 
fmt: 'Q'        截取到当季度的第一天
fmt: 'Y'        截取到当年的第一天
SELECT SYSDATE,
	 TRUNC(SYSDATE),
	 TRUNC(SYSDATE,'DD'),
	 TRUNC(SYSDATE,'DDD'),
	 TRUNC(SYSDATE,'D'),    -- 周日为本周第一天
	 TRUNC(SYSDATE,'MM'),   -- 可以写成'MONTH'
	 TRUNC(SYSDATE,'Q'),    
	 TRUNC(SYSDATE,'Y')     -- 可以写成'YEAR'
FROM DUAL;

– 2.日期函数
ADD_MONTHS(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期。d表示日期,n表示要加的月数(n可以为负值)
SELECT ADD_MONTHS(SYSDATE, 1),
ADD_MONTHS(DATE’2019-08-30’, -6),
ADD_MONTHS(DATE’2020-08-30’, -6)
FROM DUAL;

LAST_DAY(d),返回指定日期当月的最后一天
SELECT LAST_DAY(SYSDATE),LAST_DAY(DATE'2019-08-30') FROM DUAL;

MONTHS_BETWEEN (date1, date2),用于计算date1和date2之间有几个月
SELECT MONTHS_BETWEEN(TO_DATE('2014-3-21', 'yyyy-mm-dd'), TO_DATE('2014-1-10', 'yyyy-mm-dd')) mon_diff FROM DUAL;


NEXT_DAY 取下一个日子
SELECT NEXT_DAY(SYSDATE,2) FROM DUAL ---取下一个2号的日期

注意:日期可以加减数字,表示加减多少天;日期减去日期表示相隔多少天;日期不能相加减
SELECT DATE'2019-08-30'+6,
       DATE'2019-08-30'-6,
	   DATE'2019-08-30'-DATE'2019-08-16'
  FROM DUAL;

练习:2019年7月22日的情况
1.当年的第一天和最后一天
SELECT TRUNC(DATE’2019-7-22’,‘Y’),ROUND(DATE’2019-7-22’,‘Y’)-1 FROM DUAL;
2.本月的第一天和最后一天
SELECT TRUNC(DATE’2019-7-22’,‘MM’),LAST_DAY(DATE’2019-7-22’)FROM DUAL;
3.本季的第一天和最后一天
SELECT TRUNC(DATE’2019-07-22’,‘Q’),ADD_MONTHS(TRUNC(DATE’2019-07-22’,‘Q’),3) -1 FROM DUAL;
4.当天为当年的第几天
SELECT DATE’2019-07-22’ - TRUNC(DATE’2019-07-22’,‘Y’) FROM DUAL;
5.本周的第一天和最后一天
SELECT TRUNC(DATE’2019-7-22’,‘D’) +1,ROUND(DATE’2019-7-22’,‘D’) +7 FROM DUAL;
6.当年一共有多少天
TRUNC(DATE’2019-07-22’,‘Y’) -1,
(ROUND(DATE’2019-07-22’,‘Y’) -1) - (TRUNC(DATE’2019-07-22’,‘Y’) -1)
FROM DUAL

– 3.字符函数
ASCII(x) 返回字符x的ASCII码。
CONCAT(x,y) 连接字符串x和y。(|| 也可以连接字符串)
LENGTH(x) 返回x的长度。
LENGTHB(x) 返回x的字节长度
LOWER(x) x转换为小写。
UPPER(x) x转换为大写。
REPLACE(x,old,new) 替换字符 – 在x中查找old,并替换为new。
TRANSLATE(x,from_string,to_string) 替换字符 – from_string 和 to_string 以字符为单位,对应字符一一替换。
-------------------
LTRIM(x[,trim_str]) 左边截去字符 – 从x左边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
RTRIM(x[,trim_str]) 右边截去字符 – 从x右边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
TRIM([trim_str FROM] x) 两边截去字符 – 从x两边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
-------------------
INSTR(x, str[,start] [,n]) 定位字符位置 – 在x中查找str,可以指定从start位开始,第n次出现,省去的参数默认为1。
SUBSTR(x,start[,length]) 截取某段字符 – 在x中从start位开始,截取length个字符,缺省length,默认到结尾。

-- 示例
SELECT ASCII('A') FROM DUAL;   -- 65
-- 
SELECT '0722' || 'BI' || '大数据' FROM DUAL;
SELECT CONCAT(CONCAT('0722','BI'),'大数据') FROM DUAL;
SELECT CONCAT(CONCAT('0722','BI'), (SELECT '大数据' FROM DUAL)) FROM DUAL;
-- 
SELECT LENGTH('A Ub 好'), LENGTHB('中国平安'), LENGTHB('abcd') FROM DUAL;
--
SELECT LOWER('Ab2'), UPPER('Ab2'), REPLACE('a2b3a','a','520') FROM DUAL;
-- 
SELECT REPLACE(REPLACE('a你好djl','a','3'),'b','4') FROM dual;
SELECT TRANSLATE('a你好djl','ad','34') FROM dual;
SELECT TRANSLATE('a你好djl','adj','34') FROM dual;

------------------
SELECT LTRIM(' a 1 '), RTRIM(' a 1 '), TRIM(' a 1 ')
  FROM DUAL;  -- 默认截去空格
SELECT * FROM student_info WHERE TRIM(sname)='蓝浪峰';

------------------
-- 从'0722BI大数据BI520'字符串中的第1位开始查看找'BI',第1次出现的位置
SELECT INSTR('0722BI大数据BI520', 'BI') FROM DUAL;      -- 5
-- 从'0722BI大数据BI520'字符串中的第6位开始查看找'BI',第1次出现的位置    
SELECT INSTR('0722BI大数据BI520', 'BI',6) FROM DUAL;    -- 10
-- 从'0722BI大数据BI520'字符串中的第5位开始查看找'BI',第2次出现的位置    
SELECT INSTR('0722BI大数据BI520', 'BI',5, 2) FROM DUAL; -- 10
-- 从'0722BI大数据BI520'字符串中的第6位开始查看找'BI',第2次出现的位置   
SELECT INSTR('0722BI大数据BI520', 'BI',6, 2) FROM DUAL; -- 0
	
--
SELECT SUBSTR('ra badf', 2) FROM DUAL;    -- 从第2个字符截取到最后一个字符
SELECT SUBSTR('ra badf', 2, 3) FROM DUAL; -- 从第2个字符截取3个字符

练习:从字符串中’1#qfq#3df#520#d234#dlaj#’ 查找第3个#号和第4个#号之间的字符串
SELECT SUBSTR(‘1#qfq#3df#520#d234#dlaj#’,10)

问题:
常用的字符串函数有哪些,举例说明:
1、表中列的值进行替换 replace函数
字段值’China’替换成’中国’
2、SUBSTR-
切割字符串
3、INSTR
查找字符串位置
4、trim -去列值两边的空格 ltrim()/rtrim()

三种数据类型转换函数

数据类型转换函数:
TO_CHAR/TO_NUMBER/TO_DATE

– 1.TO_CHAR(d|n[,fmt])
SELECT 12, TO_CHAR(12),TO_CHAR(DATE’2019-07-08’,‘YYYYMMDD’) FROM DUAL;
– fmt 格式
SELECT TO_CHAR(DATE’2019-08-30’,‘YYYY’), – ‘2019’
TO_CHAR(DATE’2019-08-30’,‘YYYYMM’), – ‘201908’
TO_CHAR(DATE’2019-08-30’,‘WW’), – ‘35’ 第35周
TO_CHAR(DATE’2019-08-30’,‘IW’), – ‘35’ 第35周(自然周)
TO_CHAR(DATE’2019-08-30’,‘Q’), – ‘3’ 第3季度
TO_CHAR(DATE’2019-08-30’,‘MM’), – ‘08’
TO_CHAR(DATE’2019-08-30’,‘DD’), – ‘30’
TO_CHAR(DATE’2019-08-30’,‘D’) – ‘6’ 当周的第几天(星期天为第一天)
FROM DUAL;

T表:
DT                          CNT        AMT
2019-03-09 12:13:14         2          10.5
2019-08-17 09:12:17         3          25.6
2019-09-21 06:15:19         6          65.3
2020-03-26 07:09:22         4          6.8

统计2019年每个季度的销售数量和销售额 
/* to
CREATE TABLE T(DT DATE,CNT NUMBER(20),AMT NUMBER(20,1));
INSERT INTO T VALUES(TO_DATE('2019-03-24 12:13:14','YYYY-MM-DD HH24:MI:SS'),2,10.5);
INSERT INTO T VALUES(TO_DATE('2019-08-09 09:12:17','YYYY-MM-DD HH24:MI:SS'),3,25.6);
INSERT INTO T VALUES(TO_DATE('2019-09-21 06:15:19','YYYY-MM-DD HH24:MI:SS'),6,65.3);
INSERT INTO T VALUES(TO_DATE('2020-03-26 07:09:22','YYYY-MM-DD HH24:MI:SS'),4,6.8);
		   
SELECT TO_CHAR(DT,'Q') AS 季度,
	 SUM(CNT) AS 销售数量,
	 SUM(AMT) AS 销售额
FROM T
WHERE TO_CHAR(DT,'YYYY')='2019'
GROUP BY TO_CHAR(DT,'Q');
*/ 
  
  
练习:计算员工表中每年入职的人数。
	 SELECT TO_CHAR(HIREDATE, 'YYYY') AS 入职年份, 
			COUNT(*) AS 入职人数
	   FROM EMP
	  GROUP BY TO_CHAR(HIREDATE, 'YYYY')
	  ORDER BY 入职年份;

– 数字格式显示(后面格式长度要大于前面的值,如果不加FM或在前面填充空格)
SELECT TO_CHAR(243677865674,‘FM999,999,999,999,990.00’) FROM DUAL;

– 2.TO_NUMBER(x[,fmt])
x 里面必须全部为数字,如果有空格必须在最前或最后
SELECT TO_NUMBER(‘1 2’), – 不行
TO_NUMBER(’ 12’), – 可以 12
TO_NUMBER(‘12 ‘), – 可以 12
TO_NUMBER(’ 12 ‘), – 可以 12
TO_NUMBER(‘12A’) – 不行
FROM DUAL;
– 日期不能直接转数字
SELECT TO_NUMBER(DATE’2019-08-22’) FROM DUAL; – 错误
SELECT TO_NUMBER(TO_DATE(‘20190822’,‘YYYYMMDD’)) FROM DUAL; – 错误
SELECT TO_NUMBER(TO_CHAR(DATE’2019-08-22’,‘YYYYMMDD’)) FROM DUAL; --日期类型—>转换成字符串类型—>TO_NUMBER转换成数字

– 3.TO_DATE(x [,fmt])
SELECT TO_DATE(‘2019-07-08’,‘YYYY-MM-DD’),
TO_DATE(20190708,‘YYYY-MM-DD’)
FROM DUAL;
注意:字符或数字中的年月日必须是日期范围内的值
SELECT TO_DATE(‘2019-13-08’,‘YYYY-MM-DD’), – 月份不对
TO_DATE(20190732,‘YYYY-MM-DD’) – 天数不对
FROM DUAL;
– 字符日期中有-或/转化为日期类型必须加-或/
SELECT TO_DATE(‘2019-07-08’,‘YYYYMMDD’), – 错误
TO_DATE(20190708,‘YYYY-MM-DD’) – 可以
FROM DUAL;

问题:

常用转换日期函数:

to_char()
to_date()
trunc()
round()

空值转换函数

空值转换函数
NVL (列,默认值) 如果列值为null,则使用默认值表示
NVL2(列,返回值1,返回值2)如果列值不为null,返回结果1;如果列值为null,返回结果2

问题:

对空值或null的记录如何过滤
1.在查询条件中NULL值用IS NULL作条件,非NULL值用 IS NOT NULL做条件
2.在插入更新数据或转换数据时,用NVL(),NVL2()函数进行转换

– 1.NVL
SELECT NVL(NULL,0), – 空转0
NVL(NULL,100), – 空转100
NVL(’’, 99), – 空字符转99
NVL(10, 100), – 非空不会转
NVL(‘AD’,77) – 非空不会转
FROM DUAL;

– 2.NVL2
SELECT NVL2(NULL, 0, 1), – 空转1
NVL2(’’, 99, 2), – 空字符转2
NVL2(10, 3, 100), – 非空转3
NVL2(‘AD’, ‘AD’, 7) – 非空转’AD’
FROM DUAL;

例题:查看员工信息和其年薪
SELECT EMP.*,
	   (NVL(SAL,0)+NVL(COMM,0))*12 AS 年薪
  FROM EMP;

3.NULL操作(’’,NULL)
在查询条件中NULL值用IS NULL作条件,非NULL值用 IS NOT NULL做条件
空值的一些特性:
1、空值跟任何值进行算术运算,得到的结果都为空值
2、空值跟任何值进行关系运算,得到的结果都为不成立
3、空值不参与任何聚合运算
4、排序的时候,空值永远是最大的
– 查询奖金为空
SELECT * FROM EMP WHERE COMM IS NULL;
– 1
SELECT e.*, COMM+100, SAL + COMM FROM EMP e;
– 2
SELECT * FROM EMP WHERE COMM<>0;
– 3
SELECT COUNT(COMM) FROM EMP;
– 4
SELECT * FROM EMP ORDER BY COMM ASC;

练习:对EMP表中的奖金为空的转换为100,不为空的转换为88

问题:

如何过滤空值或null数据,如何转换空值或null的数据:

1.在查询条件中NULL值用IS NULL作条件过滤,非NULL值用 IS NOT NULL做条件过滤
2.nvl(),nvl2()函数转换空值或null的数据

条件转换函数

条件转换函数(从前往后判断,满足前面条件,执行对应操作后退出)
DECODE(列|值,判断值1,返回值1,判断值2,返回值2,…,默认值) – 默认值可以给可以不给,不给的话,默认为空
CASE WHEN 条件1 THEN 返回值1 [WHEN 条件2 THEN 返回值2 …] ELSE 默认值 END – 条件都不成立,没给else就为空
区别:decode 只能对单个字段进行等值判断
case when 可以对多个字段进行任意条件判断

– 1.DECODE
– 对值
SELECT DECODE(12, – 原始值
1, 2,
12, 10), – 10
DECODE(12,
1, 2,
13, 10), – 空
DECODE(12,
1, 2,
13, 10,
7), – 给定默认值7
DECODE(12,
1, 2,
12, 10, – 找到后就返回值,然后退出,不会向下找
12, 88, – 给定默认值7
7)
FROM DUAL;

-- DECODE对列(对EMP表中的奖金为空的转换为100,不为空的转换为88)
SELECT comm,
	   DECODE(comm, 
			  NULL, 100, 
			  88)
  FROM emp;
  

练习:查看各部门名称(中文),以及部门对应人数。
ACCOUNTING   会计部
RESEARCH     研究部
SALES        销售部
OPERATIONS   操作部
















 SELECT DECODE(DNAME,
			   'ACCOUNTING','会计部',
			   'RESEARCH','市场调查部',
			   'SALES','销售部',
			   'OPERATIONS','操作部'
			   ) AS 部门名称,
		COUNT(E.EMPNO) AS 部门人数
   FROM DEPT D
   LEFT JOIN EMP E
	 ON E.DEPTNO = D.DEPTNO
  GROUP BY D.DNAME;

– 2.CASE WHEN(对EMP表中的奖金为空的转换为100,不为空的转换为88)
SELECT COMM,
(CASE WHEN COMM IS NULL THEN 100
ELSE 88
END) AS COMM_1
FROM EMP;
– 部门转换
SELECT DEPT.*,
(CASE
WHEN DNAME = ‘ACCOUNTING’ THEN ‘会计部’
WHEN DNAME = ‘RESEARCH’ THEN ‘研究部’
WHEN DNAME = ‘SALES’ THEN ‘销售部’
ELSE ‘操作部’
END) AS 部门
FROM DEPT;

例题:统计员工表中各部门的员工人数,10号部门不将经理计算在内
SELECT deptno,
	   COUNT(CASE WHEN deptno=10 AND job='MANAGER' THEN NULL
				ELSE empno
			END) AS cnt
  FROM emp
 GROUP BY deptno;
  
SELECT DEPTNO, COUNT(EMPNO)
 FROM EMP
WHERE DEPTNO <> 10                         -- 排除10号部门
   OR (DEPTNO = 10 AND JOB <> 'MANAGER')   -- 10号中排除经理
GROUP BY DEPTNO;


SELECT DEPTNO, COUNT(EMPNO)
 FROM EMP
WHERE NOT (DEPTNO = 10 AND JOB = 'MANAGER')
GROUP BY DEPTNO;


SELECT DEPTNO, COUNT(EMPNO)
  FROM EMP
 WHERE empno NOT IN (SELECT empno FROM emp WHERE DEPTNO = 10 AND JOB = 'MANAGER')
 GROUP BY DEPTNO;


	  
  
  
练习:1.对员工表的job类型进行转换,CLERK -> 职员, SALESMAN -> 销售员, PRESIDENT -> 总裁, MANAGER -> 经理, ANALYST ->分析师;
	  2.对员工表的薪资打标, 小于1500为'低薪',大于等于1500并且小于等于2500为'中薪', 其它为'高薪';
	  3.更新数据:job为'CLERK' 转为'职员',将其工资增加10%, 
					   'SALESMAN' 转为'销售员',将其工资增加5%, 
					   'MANAGER' 转为'经理',将其工资增加2000, 
					   'ANALYST' 转为'分析师',将其工资增加6%,
					   'PRESIDENT' 转为'老板',其工资不变;
	  4.计算每个部门发放的总工资,总裁不考虑在内

	  /*
	  UPDATE EMP
		 SET JOB = DECODE(JOB,
						  'CLERK',
						  '职员',
						  'SALESMAN',
						  '销售员',
						  'PRESIDENT',
						  '总裁',
						  'MANAGER',
						  '经理',
						  'ANALYST',
						  '分析师'),
			 SAL = (CASE
					 WHEN JOB = 'CLERK' THEN
					  SAL * 1.1
					 WHEN JOB = 'SALESMAN' THEN
					  SAL * 1.05
					 WHEN JOB = 'MANAGER' THEN
					  SAL + 2000
					 WHEN JOB = 'ANALYST' THEN
					  SAL * 1.06
					 WHEN JOB = 'PRESIDENT' THEN
					  SAL * 1
					 ELSE
					  SAL
				   END);

		
	  SELECT deptno,
			 SUM(CASE WHEN job='PRESIDENT' THEN NULL
					  ELSE NVL(SAL,0)+NVL(COMM,0)
				END) AS sum_amount
		FROM emp
	   GROUP BY deptno;	
		*/

/*
UPDATE EMP SET JOB=DECODE(JOB,
‘CLERK’,‘职员’,
‘SALESMAN’,‘销售员’,
‘PRESIDENT’,‘总裁’,
‘MANAGER’,‘经理’,
‘ANALYST’,‘分析师’,
JOB
),SAL =
DECODE(JOB,
‘CLERK’,SAL1.1,
‘SALESMAN’,SAL+SAL
0.05,
‘MANAGER’,SAL+2000,
‘ANALYST’,SAL+SAL*0.06,
SAL
);

    UPDATE EMP SET
   JOB = (
   CASE 
     WHEN JOB='CLERK' THEN '职员'
     WHEN JOB='SALESMAN' THEN '销售员'
     WHEN JOB='PRESIDENT' THEN '总裁'
     WHEN JOB='MANAGER' THEN '经理'
     WHEN JOB='ANALYST' THEN '分析师'
       ELSE JOB
    END
   ),SAL = 
  (
   CASE 
     WHEN JOB='CLERK' THEN SAL*1.1
     WHEN JOB='SALESMAN' THEN SAL*1.05
    
     WHEN JOB='MANAGER' THEN SAL+2000
     WHEN JOB='ANALYST' THEN  SAL*1.06
       ELSE SAL
    END
  
  );
  
  
   UPDATE EMP SET
   JOB = (

CASE JOB
WHEN ‘CLERK’ THEN ‘职员’
WHEN ‘SALESMAN’ THEN ‘销售员’
WHEN ‘PRESIDENT’ THEN ‘总裁’
WHEN ‘MANAGER’ THEN ‘经理’
WHEN ‘ANALYST’ THEN ‘分析师’
ELSE JOB
END
),SAL =
(
CASE JOB
WHEN ‘CLERK’ THEN SAL1.1
WHEN ‘SALESMAN’ THEN SAL
1.05

     WHEN 'MANAGER' THEN SAL+2000
     WHEN 'ANALYST' THEN SAL*1.06
       ELSE SAL
    END
  );

*/

问题:
1.DECODE函数的用法
2.CASE WHEN 的用法

EXISTS函数

EXISTS 函数
EXISTS(查询结果集):查询结果集有记录则成立,否则不成立
NOT EXISTS(查询结果集):与EXISTS相反
– 列出有员工的部门信息
SELECT DISTINCT D.DNAME
FROM DEPT D
JOIN EMP E
ON D.DEPTNO=E.DEPTNO;

SELECT D.*
  FROM DEPT D
 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);
	
-- EXISTS
SELECT D.DNAME
  FROM DEPT D
 WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO=D.DEPTNO);
-- 空值和无结果集的区别
SELECT D.DNAME
  FROM DEPT D
 WHERE EXISTS (SELECT NULL FROM DUAL);

一般子查询效率比较低, 可以用EXISTS函数等价改写!!

问题:EXISTS与IN 的区别:
两表关联时,子查询的表数据量少时用IN,SELECT 查询表的数据量少时可以用EXISTS.

伪列

伪列–附加列
ROWID 插入数据的时候生成,记录的是该行的物理地址(用作去重)
ROWNUM 查询数据的时候生成,返回的是序号(用作分页)

SELECT E.*, E.ROWID, ROWNUM  -- ROWNUM是变化的值,不能指定到某个表
  FROM EMP E;
SELECT E.ROWID,D.ROWID,ROWNUM, E.*,D.*
  FROM EMP E
  JOIN DEPT D
	ON E.DEPTNO=D.DEPTNO;  

– 1.ROWNUM 查询只能是小于或小于等于某个值(不能直接等于或者大于或者不是从1开始的某个区间段)
SELECT E.,ROWID, ROWNUM
FROM EMP E
WHERE ROWNUM<=10;
抽取排在第7到第10行的数据
– 1.补集
SELECT E.
, ROWNUM
FROM EMP E
WHERE ROWNUM <= 10
MINUS
SELECT E., ROWNUM
FROM EMP E
WHERE ROWNUM <= 6;
– 2.子查询
SELECT t.

FROM (
SELECT E.*, ROWNUM AS rn – 一定要有别名
FROM EMP E
WHERE ROWNUM <= 10) t
WHERE t.rn>=7;

– 2.ROWID
删除重复数据,相同数据只保留一条
DELETE FROM 表名 别名
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM 表名 别名 GROUP BY 列名);

-- 查看表中某些字段是否有重复数据
SELECT DEPTNO
  FROM EMP
 GROUP BY DEPTNO
 HAVING COUNT(DEPTNO)>1;
-- 从员工表找出所有部门编号(字段值进行分组找该字段每一组的一个值)
SELECT DISTINCT DEPTNO
  FROM EMP;
SELECT DEPTNO
  FROM EMP
 GROUP BY DEPTNO;
-- 每个部门的员工数据只保留一条(字段值进行分组保留每一组的一条完整信息)
DELETE FROM EMP
 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP GROUP BY DEPTNO);

问题:

1、如何去重:
删除表中的重复数据:
2、如何分页: ROWNUM
3. 补集函数:MINUS
交集函数:INTERSECT

4.sql server/oracle/mysql中的取行数

sql server:select top 5 * from stock

mysql: select * from student limit 5

oracle: select * from sutdent where rownum < = 50

5、mysql中给查询记录中增加序号列

set @id = 0;
select
@id := @id + 1,
sname,sex from student

select
@id := @id + 1,
sname,sex from student,(select @id:= 0) r

6、oracle/sql server/mysql的区别:

oracle大型商业数据库
sqlserver中型商业数据库
mysql小型免费数据库

临时表

临时表
创建ORACLE临时表,可以有两种类型的临时表:
会话级临时表(1.其它会话查询不到数据 2.本会话关掉后也查询不到数据)
事务级临时表(1.当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断
2.其它会话查询不到数据
3.本会话关掉后也查询不到数据)
1)会话级临时表
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
(COL1 TYPE1,COL2 TYPE2…) ON COMMIT PRESERVE ROWS;

2)事务级临时表
	CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
	(COL1 TYPE1,COL2 TYPE2...) ON COMMIT DELETE ROWS;

– 1.会话级临时表(每一个打开的窗口)
CREATE GLOBAL TEMPORARY TABLE tmp_STUDENT
(STU_ID NUMBER(5),
CLASS_ID NUMBER(5),
STU_NAME VARCHAR2(8),
STU_MEMO VARCHAR2(200)) ON COMMIT PRESERVE ROWS ;

INSERT INTO tmp_STUDENT VALUES(1,2,'A','随便');
INSERT INTO tmp_STUDENT VALUES(2,3,'B','OK');
COMMIT;
SELECT * FROM tmp_STUDENT;

-- 目标表
CREATE TABLE STUDENT_tmp_cp
(STU_ID NUMBER(5),
CLASS_ID  NUMBER(5),
STU_NAME VARCHAR2(8),
STU_MEMO VARCHAR2(200));

-- 目标表
INSERT INTO STUDENT_tmp_cp
SELECT * 
FROM tmp_STUDENT
WHERE STU_MEMO='OK';


SELECT * FROM tmp_STUDENT;
SELECT * FROM STUDENT_tmp_cp;

– 2.事务级临时表
– 支付给商家,商家收到钱以及支付方被扣款
CREATE GLOBAL TEMPORARY TABLE tmp_pay_customer
(id NUMBER(5),
pay_mon VARCHAR2(8)
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE tmp_get_merchant
(id NUMBER(5),
get_mon VARCHAR2(8)
) ON COMMIT DELETE ROWS;

INSERT INTO tmp_pay_customer VALUES(1,-100);
SELECT * FROM tmp_pay_customer;
COMMIT; – 确认支付
– 商家收款
INSERT INTO tmp_get_merchant VALUES(1,100);

问题:
1、临时表有哪几种:会话级临时表、事务级临时表
2、两种临时表的概念及区别:
会话级临时表(1.其它会话查询不到数据 2.本会话关掉后也查询不到数据)
事务级临时表(1.当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断
2.其它会话查询不到数据
3.本会话关掉后也查询不到数据)

3、sql server临时表:

#临时表名
create table #temp
(字段,数据类型…)

mysql临时表:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

分析函数

分析函数(重点)
它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值
– 分组聚合
SELECT DEPTNO,
COUNT(EMPNO)
FROM EMP
GROUP BY DEPTNO;
按照每个部门的工资从低到高排序,计算每个部门的累计工资?
按照每个部门的工资从低到高排序,给出各部门工资的序号?

语法格式:分析函数语法(<> 内的内容可以选择性省略)
FUNCTION_NAME(<参数>,…) OVER (<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC DESC> )

– 1.函数为聚合函数(AVG,SUM,COUNT)
1.FUNCTION_NAME(<参数>,…) OVER (PARTITION BY 表达式,… ) – 分组求值
2.FUNCTION_NAME(<参数>,…) OVER (ORDER BY 表达式 ) – 整体数据未做分组,先排序,在求累计求值
3.FUNCTION_NAME(<参数>,…) OVER (PARTITION BY 表达式,… ORDER BY 表达式 ) – 先分组,按组内排序,对组内求累计求值

SELECT E.*,SUM(SAL) OVER (PARTITION BY DEPTNO) 工资 FROM EMP E; --每个员工信息并分组计算每个部门的总工资

SELECT E.,SUM(SAL) OVER (ORDER BY DEPTNO) 工资 FROM EMP E; --整体数据未分组,先排序,后计算汇总累加
SELECT E.
,SUM(SAL) OVER (ORDER BY SAL) 工资 FROM EMP E; --整体数据未分组,先排序,后计算汇总累加

SELECT E.*,SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) 工资 FROM EMP E; – 先分组,按组内排序,对组内求累计求值

-- PARTITION BY   1.查看员工表中员工信息以及对应部门的总人数
/*
SELECT * FROM EMP;
SELECT DEPTNO, COUNT(1) FROM EMP GROUP BY DEPTNO;

SELECT E.*, T.CNT
  FROM EMP E
  JOIN (SELECT DEPTNO, COUNT(1) AS CNT FROM EMP GROUP BY DEPTNO) T
	ON E.DEPTNO = T.DEPTNO;
*/
SELECT E.*,
	   COUNT(EMPNO) OVER (PARTITION BY DEPTNO) AS CNT
  FROM EMP E;

-- ORDER BY       2.对员工表的工资从低到高排序,求累计应发放薪资
SELECT E.*,
	   SUM(SAL) OVER (ORDER BY SAL)
  FROM EMP E;
区别
SELECT E.*,
	   SUM(SAL) OVER (ORDER BY SAL,EMPNO)
  FROM EMP E;
  
-- PARTITION BY + ORDER BY   3.按照每个部门的工资从低到高排序,计算每个部门的累计工资
SELECT E.*,
	   SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL)
  FROM EMP E;

SELECT E.*,
	   SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,EMPNO)
  FROM EMP E;
  

练习:    
每日生产量      T_A
data_date       daily_out
2019-07-10      120
2019-07-11      100
2019-07-12      150
求每日累计产量
data_date       daily_out   sum_daily_out
2019-07-10      120         120
2019-07-11      100         220
2019-07-12      150         370
/*
CREATE TABLE T_A (
data_date date,
daily_out  number);
INSERT INTO T_A(data_date,daily_out) VALUES (DATE'2019-07-10', 120);
INSERT INTO T_A(data_date,daily_out) VALUES (DATE'2019-07-11', 100);
INSERT INTO T_A(data_date,daily_out) VALUES (DATE'2019-07-12', 150);
COMMIT;
*/

– 2.函数为排序函数(ROW_NUMBER(),RANK(),DENSE_RANK())情况下:
①ROW_NUMBER:
ROW_NUMBER函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②RANK:
RANK函数返回一个唯一的值,当碰到相同的数据时,此时所有相同数据的排名是一样的,
同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
③DENSE_RANK:
DENSE_RANK函数返回一个唯一的值,当碰到相同数据时,此时所有相同数据的排名都是一样的。
同时会在最后一条相同记录和下一条不同记录的排名之间不空出排名。

2.FUNCTION_NAME() OVER (ORDER BY 表达式 <ASC DESC> )                       -- 整体数据未做分组,按ORDER BY 的字段排名
3.FUNCTION_NAME() OVER (PARTITION BY 表达式,… ORDER BY 表达式 <ASC DESC> ) -- 先分组,按组内排序,对组内进行排名

-- ORDER BY   2.将emp表按工资从低到高排序,并给出排名(排名不并列)
SELECT E.*,
	   ROW_NUMBER() OVER (ORDER BY SAL)
  FROM EMP E;
	
-- PARTITION BY + ORDER BY   3.按照员工部门分组, 给出工资从低到高的排名(排名不并列)
SELECT E.*,
	   ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL)
  FROM EMP E;

练习:找出员工表每个部门的一条员工信息(分析函数)

– 3.函数为位移函数(LEAD(列,参数), LAG(列,参数)
SELECT E.*,
LAG(E.SAL,1) OVER(ORDER BY E.SAL) AS 工资下移一位,
LEAD(E.SAL,1) OVER(ORDER BY E.SAL) AS 工资上移一位
FROM EMP E;

练习:员工表按入职先后排序, 求出每两个员工的入职时间差

/*
select deptno 部门,count(*) 人数 from emp group by deptno order by deptno

部门 人数
10 3
20 5
30 6
50 1

转换输出:

部门 10 20 30 50
人数 3 5 6 1
*/


月份 北京 上海
1月 50 40
2月 60 30
3月 70 20

case when 转换成

区域 1月 2月 3月
北京
上海

问题:

1、分析函数的原理和用途
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,
并且每一组的每一行都可以返回一个统计值。

2、分析函数和分组聚合函数的区别:
分析函数和聚合函数的不同之处是什么?
◎普通的聚合函数用group by分组,
◎每个分组返回一个统计值,
◎而分析函数采用partition by分组,
◎并且每组每行都可以返回一个统计值。

行列转换

行列转换
/*
– 适用场景介绍
– 数仓数据存储
Y Q AMT
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300
– 报表展示
Y Q1 Q2 Q3 Q4
2015 100 110 130 100
2016 200 150 100 300
*/

SELECT DEPTNO,
	   COUNT(EMPNO)
  FROM EMP
 GROUP BY DEPTNO;

员工表人数统计:
10号部门  20号部门  30号部门  50号部门  
	   2         5         6         1
WITH T AS (
		  SELECT DEPTNO,
				 COUNT(EMPNO) AS CNT
			FROM EMP
		   GROUP BY DEPTNO)
SELECT -- T.*, 查看所有数据
	   MAX(CASE WHEN t.deptno=10 THEN t.cnt ELSE 0 END) "10号部门",
	   MAX(CASE WHEN t.deptno=20 THEN t.cnt ELSE 0 END) "20号部门",
	   MAX(CASE WHEN t.deptno=30 THEN t.cnt ELSE 0 END) "30号部门"
 FROM T;

– 1.行转列
有一张表S,记录了某公司每个季度的销售额,如下
Y Q AMT
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300

	Y      Q1   Q2   Q3   Q4
	2015   100  110  130  100
	2016   200  150  100  300
	/*
	-- 
	CREATE TABLE T_Y_Q_AMT (
	Y NUMBER,
	Q NUMBER,
	AMT NUMBER
	);
	INSERT INTO T_Y_Q_AMT VALUES(2015,   1 ,     100);
	INSERT INTO T_Y_Q_AMT VALUES(2015,   2 ,     110);
	INSERT INTO T_Y_Q_AMT VALUES(2015,   3 ,     130);
	INSERT INTO T_Y_Q_AMT VALUES(2015,   4 ,     100);
	INSERT INTO T_Y_Q_AMT VALUES(2016,   1 ,     200);
	INSERT INTO T_Y_Q_AMT VALUES(2016,   2 ,     150);
	INSERT INTO T_Y_Q_AMT VALUES(2016,   3 ,     100);
	INSERT INTO T_Y_Q_AMT VALUES(2016,   4 ,     300);
	-- 
	CREATE TABLE T_Y_Q_AMT_1 (
	Y NUMBER,
	Q1 NUMBER,
	Q2 NUMBER,
	Q3 NUMBER,
	Q4 NUMBER
	);
	INSERT INTO T_Y_Q_AMT_1 VALUES(2015,   100,  110,  130, 100);
	INSERT INTO T_Y_Q_AMT_1 VALUES(2016,   200,  150,  100,  300);
	COMMIT;
	*/ 	
	  
	-- CASE WHEN
	SELECT A.Y,
		   SUM(CASE WHEN A.Q=1 THEN A.AMT END) AS Q1,
		   SUM(CASE WHEN A.Q=2 THEN A.AMT END) AS Q2,
		   SUM(CASE WHEN A.Q=3 THEN A.AMT END) AS Q3,
		   SUM(CASE WHEN A.Q=4 THEN A.AMT END) AS Q4
	  FROM T_Y_Q_AMT A 
	 GROUP BY A.Y;

– 2.列转行
/*
– 将原表拆分4个季度的值,给上季度的标识,然后合并
Y Q1 Q_FLAG
2015 100 1
2016 200 1

	Y      Q2      Q_FLAG
	2015   110     2
	2016   150     2
	
	Y      Q3      Q_FLAG
	2015   130     3
	2016   100     3
	
	Y      Q4      Q_FLAG
	2015   100     4
	2016   300     4
	*/
	
	SELECT Y, 1 AS Q, Q1 AS AMT
	 FROM T_Y_Q_AMT_1 T1
	UNION
	SELECT Y, 2 AS Q, Q2 AS AMT
	 FROM T_Y_Q_AMT_1 T1
	UNION
	SELECT Y, 3 AS Q, Q3 AS AMT
	 FROM T_Y_Q_AMT_1 T1
	UNION
	SELECT Y, 4 AS Q, Q4 AS AMT
	 FROM T_Y_Q_AMT_1 T1;

– pivot和unpivot函数
/*
– PIVOT 行转列
SELECT *
FROM T_Y_Q_AMT PIVOT(SUM(AMT) FOR Q IN(1 AS Q1,
2 AS Q2,
3 AS Q3,
4 AS Q4));

	-- UNPIVOT 列转行
	SELECT *
	  FROM T_Y_Q_AMT_1 UNPIVOT(AMT FOR Q IN(Q1 AS 1,Q2 AS 2,Q3 AS 3,Q4 AS 4));
	*/  

总结:行转列: PIVOT 或者 MAX(CASE WHEN …END)
列转行:UNPIVOT 或者 UNION ALL

练习:按部门统计每种工作下面员工的工资总额(每种工作显示一列)

name 语文 数学 英语
李四 78 85 83
张三 80 86 75
16:03:50 SQL> select * from test;

NAME KM CJ


张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78

Create table test (name char(10),km char(10),cj Number)

insert into test values(‘张三’,‘语文’,80)
insert into test values(‘张三’,‘数学’,86)
insert into test values(‘张三’,‘英语’,75)
insert into test values(‘李四’,‘语文’,78)
insert into test values(‘李四’,‘数学’,85)
insert into test values(‘李四’,‘英语’,78)

commit;

表约束

表的约束
按照约束用途分类:
1.PRIMARY KEY:主键约束 (非空、唯一) – 身份证号
2.FOREIGN KEY:外键约束 (受外部表主键的约束)
3.CHECK: 检查约束 (只能为空或约束的值) – 性别
4.UNIQUE: 唯一约束 (不重复、可多行为空)
5.NOT NULL: 非空约束 (不能为空)

-- 建表时候给定约束(系统给定约束名称)
CREATE TABLE t_student_1 (
sno VARCHAR2(18) PRIMARY KEY,                    -- 主键
cno VARCHAR2(60) REFERENCES t_course_1(cno),     -- 外键
score NUMBER(4,1) NOT NULL,
sex VARCHAR2(200) CHECK(sex='男' OR sex='女'),
tel NUMBER(11) UNIQUE
);


(1)PRIMARY KEY (主键只有一个, 但是可以对多个字段组合建立一个主键)
	CREATE TABLE t_china_id (
	ID_NO VARCHAR2(18),
	ID_NAME VARCHAR2(60),
	BIRTH_DAY DATE,
	ADDRESS VARCHAR2(200),
	TEL NUMBER(11),
	CONSTRAINT Pk_ID_NO PRIMARY KEY(ID_NO)
	);
	INSERT INTO t_china_id(ID_NO) VALUES('1');
	INSERT INTO t_china_id(ID_NO) VALUES('1'); -- 重复值报错
	INSERT INTO t_china_id(ID_NO) VALUES('');  -- 空值报错
	-- 
	CREATE TABLE t_china_id_1 (
	ID_NO VARCHAR2(18),
	ID_NAME VARCHAR2(60),
	BIRTH_DAY DATE,
	ADDRESS VARCHAR2(200),
	TEL NUMBER(11),
	CONSTRAINT PR_ID_NAME PRIMARY KEY(ID_NO,ID_NAME)
	);
	INSERT INTO t_china_id_1(ID_NO) VALUES('1');  -- 主键字段ID_NAME为空报错
	INSERT INTO t_china_id_1(ID_NO,ID_NAME) VALUES('1','A');
	INSERT INTO t_china_id_1(ID_NO,ID_NAME) VALUES('1','B');

(2)FOREIGN KEY (外部表要先建立主键,外键约束的字段值只能包含在外部表主键值中)
– 父项表
CREATE TABLE DEPT_1
(DEPTNO NUMBER(2,0),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT PK_DEPTNO_1 PRIMARY KEY (DEPTNO)
);
– 子项表
CREATE TABLE EMP_1(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMPNO_1 PRIMARY KEY (EMPNO),
CONSTRAINT FK_DEPTNO_1 FOREIGN KEY (DEPTNO) REFERENCES DEPT_1(DEPTNO)
);

	INSERT INTO EMP_1(EMPNO,ENAME,DEPTNO) VALUES (1,'A',NULL);  -- 可以插入数据
	INSERT INTO EMP_1(EMPNO,ENAME,DEPTNO) VALUES (2,'B',10);    -- 报错,外部表主键没有10的值
	INSERT INTO DEPT_1(DEPTNO,DNAME,LOC) VALUES (10,'AA','BB');
	INSERT INTO EMP_1(EMPNO,ENAME,DEPTNO) VALUES (2,'B',10);    -- 可以插入数据
	DELETE FROM DEPT_1 WHERE DEPTNO=10;                         -- 报错,字表已经引用外部表10号部门的值

(3)CHECK
CREATE TABLE t_china_id_2 (
ID_NO VARCHAR2(18) PRIMARY KEY,
ID_NAME VARCHAR2(60),
SEX VARCHAR2(4),
TEL NUMBER(11),
CONSTRAINT CK_SEX CHECK(SEX=‘男’ OR SEX=‘女’)
);
INSERT INTO t_china_id_2(id_no,sex) VALUES(‘1’,’’); – 可以为空
INSERT INTO t_china_id_2(id_no,sex) VALUES(‘3’,‘男’);
INSERT INTO t_china_id_2(id_no,sex) VALUES(‘4’,‘女’);
INSERT INTO t_china_id_2(id_no,sex) VALUES(‘2’,‘未知’); – 不能为检查约束外的值

(3)UNIQUE
CREATE TABLE t_china_id_3 (
ID_NO VARCHAR2(18) PRIMARY KEY,
ID_NAME VARCHAR2(60),
SEX VARCHAR2(4),
TEL NUMBER(11),
CONSTRAINT UQ_TEL UNIQUE(TEL)
);
INSERT INTO t_china_id_3(id_no,tel) VALUES(‘1’,NULL); – 可以为空
INSERT INTO t_china_id_3(id_no,tel) VALUES(‘2’,NULL); – 可以为空
INSERT INTO t_china_id_3(id_no,tel) VALUES(‘3’,NULL); – 可以为空

(4)NOT NULL

(5)约束组合使用(主键不能和唯一约束组合)
CREATE TABLE t_china_id_4 (
ID_NO VARCHAR2(18) PRIMARY KEY CHECK(LENGTH(ID_NO)=18),
ID_NAME VARCHAR2(60)
);
INSERT INTO t_china_id_4(id_no,id_name) VALUES(‘1’,‘张三’); – 主键中的检查约束不满足18位, 报错
INSERT INTO t_china_id_4(id_no,id_name) VALUES(‘123456789876543210’,‘张三’);

	-- ALTER TABLE命令添加约束
	ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名1[,列名2...])
	ALTER TABLE 主表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名1[,列名2...]) REFERENCES 从表名(列名1[,列名2...])
	ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)
	ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名) 
	ALTER TABLE 表名 MODIFY 列名 NOT NULL
	ALTER TABLE 表名 DROP CONSTRAINT 约束名	
			
			
	-- 有数据不满足约束的时候,不能创建约束		
	CREATE TABLE T_TEST_1 (
	SNO VARCHAR2(10),
	SNAME VARCHAR2(20)
	);
	INSERT INTO T_TEST_1(SNO, SNAME) VALUES ('1','张三');
	INSERT INTO T_TEST_1(SNO, SNAME) VALUES ('1','李四');
	ALTER TABLE T_TEST_1 ADD CONSTRAINT pk_t_test_1_sno PRIMARY KEY (SNO);	 -- 报错

练习:
1.建立2张表,学生表和课程表
2.学生表的学号为主键,课程表的课程号为主键
3.学生表的课程号建立外键,对应的为课程表的课程号(课程表的课程号为学生表的课程号的外键)
要求:建好表后通过语句添加主外键

问题:
约束有哪几种

缓慢变化维:
https://www.cnblogs.com/xqzt/p/4472005.html
拉链表
https://blog.csdn.net/weixin_43679675/article/details/89453819

表空间和同义词

1.表空间
(1)系统表空间 SYSTEM
(2)临时表空间 temp
(3)用户表空间 USERS
(4)undo表空间 undotbs1
(5)样例表空间 example
(6)系统副本空间 SYSAUX sysaux表空间在oralce database 10g中引用,作为system表空间的辅助表空间。
以前一些使用独立表空间或系统表空间的数据库组件先做着sysaux表空间中创建。

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,
并且当PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序。像数据库中一些操作: CREATE INDEX、
ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、
HASH JOIN等都可能会用到临时表空间。当操作完成后,系统会自动清理临时表空间中的临时对象,
自动释放临时段。这里的释放只是标记为空闲、可以重用,其实实质占用的磁盘空间并没有真正释放。
这也是临时表空间有时会不断增大的原因。

2.同义词
  私有同义词:拥有CREATE SYNONYM权限的用户(包括非管理员用户)即可创建私有同义词,创建的私有同义词只能由当前用户使用。
  公有同义词:系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。
	创建同义词
	CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name 
	FOR [schema.]object_name

	-- sys 用户
	-- SELECT * FROM DBA_SYNONYMS;
	CREATE OR REPLACE PUBLIC SYNONYM emp_copy FOR scott.emp;

	-- scott用户
	SELECT * FROM  emp_copy;

序列和视图

1.序列
序列(Sequence)是用来生成连续的整数数据的对象。-- 一般当做主键(像订单明细中)
创建序列
CREATE SEQUENCE sequence_name
[START WITH num] – 从某一个整数开始,升序默认值是1,降序默认值是-1
[INCREMENT BY increment] – 增长数
[MAXVALUE num|NOMAXVALUE] – 最大值
[MINVALUE num|NOMINVALUE] – 最小值
[CYCLE|NOCYCLE] – 表示如果升序达到最大值后,从最小值重新开始;如果是降序序列,达到最小值后,从最大值重新开始
[CACHE num|NOCACHE] – 预先在内存中生成序列号/不预先在内存中生成序列号
– 最简单创建
CREATE SEQUENCE s_1;

序列使用
--访问下一个值(初始创建后要先定义)
SELECT s_1.NEXTVAL FROM DUAL; 
--访问当前值
SELECT s_1.CURRVAL FROM DUAL; 

-- 生成序列号
CREATE SEQUENCE s_2
START WITH 2
MINVALUE 1
MAXVALUE 10
INCREMENT BY 2
CYCLE 
CACHE 2

序列修改和删除
--序列修改
ALTER SEQUENCE s_1
MAXVALUE 10000
MINVALUE -300
--删除序列
DROP SEQUENCE s_1; 


举例:		
CREATE TABLE T_S_INFO(SNO NUMBER,SNAME VARCHAR(20));
ALTER TABLE T_S_INFO ADD CONSTRAINT pk_sno PRIMARY KEY(sno);
CREATE SEQUENCE S_INFO;
	
INSERT INTO T_S_INFO(SNO,SNAME) VALUES(S_INFO.NEXTVAL,'张三');

INSERT INTO T_S_INFO(SNO,SNAME) 
SELECT S_INFO.NEXTVAL,'李四'
  FROM dual;

SELECT * FROM T_S_INFO;

问题:
MYSQL中给行数取序号:

set @i =0

select @i :=@i+1,… from table

SQL SERVER:
create table temp2
(
id int identity(1000,2),
name varchar(2)
)

insert into temp2 values(‘AA’)

SELECT * FROM temp2

MYSQL:

drop table if exists trace_test;

CREATE TABLE trace_test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT 5 DEFAULT CHARSET=utf8 ;

insert into trace_test(name)values(‘name2’);
select * from trace_test;

2.视图 – 1.关键字段可以隐藏 2.根据基表实时更新 3.不占用空间 4.可以处理复杂的表关联
视图(View)实际上是一张或者多张表上的预定义查询,这些表称为基表。
从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。
创建视图
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY]

视图
--创建视图
CREATE OR REPLACE VIEW v_emp_dept
AS
SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE, E.DEPTNO, D.DNAME
  FROM EMP E
  JOIN DEPT D
  ON E.DEPTNO = D.DEPTNO
WITH READ ONLY

--更改基础表
DELETE FROM emp WHERE empno<>7369

--通过视图查询
SELECT * FROM v_emp_dept;

问题:

普通视图和物化视图的区别:

普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。
对整体查询性能的提高,并没有实质上的好处。

普通视图和物化视图根本就不是一个东西,说区别都是硬拼到一起的,首先明白基本概念,
普通视图是不存储任何数据的,他只有定义,在查询中是转换为对应的定义SQL去查询,
而物化视图是将数据转换为一个表,实际存储着数据,这样查询数据,就不用关联一大堆表,
如果表很大的话,会在临时表空间内做大量的操作。

https://blog.csdn.net/joshua_peng1985/article/details/6213593
物化视图:
(1)物理结构,占用空间
(2)可以建立索引
(3)可以按照需求根据基础表刷新数据

索引和表分区

1、索引 --1.索引可以提高查询的效率 2.占用空间 3.数据增删改时需要更新索引,因此索引对增删改时会有负面影响
前提:表数据量比较大的时候,查询比较慢
1.如果表中的某些字段经常被查询或者表之间的关联,以及作为查询的条件出现时,就应该考虑为该列创建索引。
2.有一条基本的准则是:当任何单个查询要检索的行少于或者等于整个表行数的10%时,索引就非常有用。
原理:一个索引可以由一个或多个列组成,对列设置索引其实就是对列的内容按一定的方式进行排序,
检索数据的时候,检索排过序的数据,检索到最后一个有效数据之后就跳出检索,这样就不必进行全表扫描了。

(1)索引种类
    -- 结构上分类:
	B-树索引
	最常用的索引结构,默认建立的索引就是这种结构
	
	
	-- 用途上分类:
	唯一索引
	1、何时创建:当某列任意两行的值都不相同
	2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时,唯一索引将被自动建立
	
	
	组合索引
	1、何时创建:当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建
	2、组合索引中列的顺序是任意的,也无需相邻。但是建议将最频繁访问的列放在列表的最前面
	

	基于函数的索引
	1、何时创建:在WHERE条件语句中包含函数或者表达式时
	2、函数包括:算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
	
	
	位图索引
	1、何时创建:列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
	Where 条件中包含了很多OR操作符。较少的update操作,因为要相应的跟新所有的bitmap


(2)创建索引
    -- 唯一索引
	CREATE UNIQUE INDEX u_inx_emp_bak_empno ON emp_bak(empno);
	-- 组合索引,和顺序有关
	CREATE INDEX inx_emp_bak_deptno_job ON emp_bak(deptno,job);
	-- 基于函数的索引
	CREATE INDEX inx_emp_bak_ename ON emp_bak(substr(ename,-1,1));
	-- 位图索引
	CREATE BITMAP INDEX index_job ON emp_bak(job);


(3)索引失效
	1.隐士转换
	SELECT * FROM EMP_BAK WHERE EMPNO = '7934';
	-- 改 SELECT * FROM EMP_BAK WHERE EMPNO = 7934;
	2.字段引用函数
	SELECT * FROM EMP_BAK WHERE TO_CHAR(EMPNO) = '7934';
	3.NULL 值判断
	SELECT * FROM EMP_BAK WHERE EMPNO IS NOT NULL;
	-- 改SELECT * FROM EMP_BAK WHERE EMPNO>0;
	4.索引列进行运算
	SELECT * FROM EMP_BAK WHERE EMPNO + 10 = 7944;
	-- 改SELECT * FROM EMP_BAK WHERE EMPNO = 7944 -10;
	SELECT * FROM EMP_BAK WHERE EMPNO <> 7944;
	-- 改    
	SELECT * FROM EMP_BAK WHERE EMPNO > 7944
    UNION ALL
    SELECT * FROM EMP_BAK WHERE EMPNO < 7944;
	5.like首字母未知(末尾字母知道)
	-- 对ename 建立了唯一索引
	SELECT * FROM EMP_BAK WHERE ename like '%S';   -- 失效
	SELECT * FROM EMP_BAK WHERE ename like 'A%S';  -- 走部分索引
	-- 改substr(ename,-1,1)建立索引
	SELECT * FROM EMP_BAK WHERE substr(ename,-1,1) ='S';
	6.组合索引(字段顺序有关)
	SELECT * FROM EMP_BAK WHERE deptno=10;                    -- 部分索引
	SELECT * FROM EMP_BAK WHERE deptno=10 AND job='SALESMAN'; -- 全部索引
	SELECT * FROM EMP_BAK WHERE job='SALESMAN';               -- 索引失效


    -----------索引---------
	SELECT COUNT(1) FROM t_test_2000w;             -- 2.59s
	SELECT COUNT(*) FROM t_test_2000w;             -- 2.58s
	SELECT COUNT(ID) FROM t_test_2000w;            -- 2.60s
	SELECT ID FROM t_test_2000w WHERE ID=1;        -- 2.60s
	SELECT ID FROM t_test_2000w WHERE ID=20000000; -- 2.58s
	
	
    -- 建立索引会对数据进行排序,并将索引数据进行存储(占用磁盘空间)
	CREATE UNIQUE INDEX idx_t_test_2000w_id ON t_test_2000w(ID); -- 11.67s
	SELECT COUNT(1) FROM t_test_2000w;             -- 2.65s
	SELECT COUNT(*) FROM t_test_2000w;             -- 2.58s
	SELECT COUNT(ID) FROM t_test_2000w;            -- 1.43s
	SELECT ID FROM t_test_2000w WHERE ID=1;        -- 0.02s
	SELECT ID FROM t_test_2000w WHERE ID=20000000; -- 0.02s
	
	--查询表占用空间,SYS管理员账号登陆:
	select sum(bytes)/1024/1024 as "size(M)" from dba_segments where owner='SCOTT'
    select sum(bytes)/1024/1024 as "size(M)" from dba_segments where owner='SCOTT' and SEGMENT_NAME ='T_TEST_2000W'
	
	--SCOTT个人账号登陆查询表空间:
	select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('T_TEST_2000W');
	
	--查询索引占用空间
	select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_name =upper('idx_t_test_2000w_id') and segment_type ='INDEX' group by segment_name;
	
	查询”帐户/库”数据占用存储空间大小
	select sum(bytes)/1024/1024 as “size(M)” from dba_segments where owner=’帐户名/库名’
    
	查询单表数据占用存储空间大小
	select sum(bytes)/(1024*1024) as “size(M)” from user_segments
	where segment_name=upper(‘表名’);

	在表空间中,查询占用存储空间最大的表
	SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = upper(‘表空间名称’) GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

	在”帐户/库”中,查询占用存储空间最大的表
	SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE owner=upper(‘帐户名/库名’) GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

	
-- 4个字段,2000万行数据,表数据大概占用1.2G,只对一个字段建立索引,索引大概占用350M
	-- 建立索引前查询id=1用时2.567s左右,建立索引后用时0.02s左右

2.表分区
前提:表数据量比较大的时候,查询比较慢
优点:1.提高查询效率 2.增强可用性 3.维护方便(只对某些有问题分区数据维护,不用对整张表维护)
缺点:2.分区表需要维护(维护创建的分区)
表分区的几种类型及操作方法
1.范围分区:RANGE
2.列表分区:LIST
3.散列(哈希)分区:HASH
4.组合分区

(1)范围分区:
按入职日期进行范围分区
CREATE TABLE MYEMP
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION part1 VALUES LESS THAN (TO_DATE(‘1981-1-1’,‘YYYY/MM/DD’)), --①
PARTITION part2 VALUES LESS THAN (TO_DATE(‘1982-1-1’,‘YYYY/MM/DD’)),
PARTITION part3 VALUES LESS THAN (TO_DATE(‘1983-1-1’,‘YYYY/MM/DD’)),
PARTITION part4 VALUES LESS THAN (TO_DATE(‘1988-1-1’,‘YYYY/MM/DD’)),
PARTITION part5 VALUES LESS THAN (MAXVALUE) --默认最大
);

	-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP'; 查看分区表情况

	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(1,'张三',DATE'1980-1-1',10);
		   
	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(2,'李四',DATE'1981-10-02',20);       
		   
	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(3,'王五',DATE'1982-11-03',30);         
		   
		   
	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(4,'李蕾',DATE'1983-07-08',40);  
		 
		 
	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(5,'李华',DATE'1987-09-09',40);       
		   
	INSERT INTO MYEMP(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(6,'赵四',DATE'1989-11-03',50);        


	-- 查看分区数据
	SELECT * FROM MYEMP PARTITION(PART5);

(2)列表分区:
该分区的特点是某列的值比较少并且不会经常变动,基于这样的特点我们可以采用列表分区。
按DEPTNO进行LIST分区
CREATE TABLE MYEMP2
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY LIST (DEPTNO)
(
PARTITION MYEMP_DEPTNO_10 VALUES (10) ,
PARTITION MYEMP_DEPTNO_20 VALUES (20) ,
PARTITION MYEMP_DEPTNO_30 VALUES (30) ,
PARTITION MYEMP_DEPTNO_40 VALUES (40)
);

	-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP2'; 查看分区表情况

	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(1,'张三',DATE'1980-1-1',10);
		   
	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(2,'李四',DATE'1981-10-02',20);       
		   
	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(3,'王五',DATE'1982-11-03',30);         
		   
		   
	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(4,'李蕾',DATE'1983-07-08',40);  
		 
		 
	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(5,'李华',DATE'1987-09-09',40);       
		   
	INSERT INTO MYEMP2(EMPNO,
					  ENAME,
					  HIREDATE,
					  DEPTNO)
		 VALUES(6,'赵四',DATE'1989-11-03',50);  -- 没给分区数据无法插入

(3)散列分区/HASH 分区:
通过计算hash值,将相同的hash值放到相同的分区
CREATE TABLE MYEMP3
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY HASH (ENAME)
(PARTITION part01,
PARTITION part02);

-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP3'; 查看分区表情况     
	
 INSERT INTO MYEMP3(EMPNO,
		  ENAME,
		  HIREDATE,
		  DEPTNO)
   VALUES(1,'张三',DATE'1980-1-1',10);
	 
INSERT INTO MYEMP3(EMPNO,
		  ENAME,
		  HIREDATE,
		  DEPTNO)
   VALUES(2,'李四',DATE'1981-10-02',20); 
   
INSERT INTO MYEMP3(EMPNO,
		  ENAME,
		  HIREDATE,
		  DEPTNO)
   VALUES(3,'张五',DATE'1982-11-03',30);  
   
 SELECT * FROM MYEMP3 PARTITION(part01);
 SELECT * FROM MYEMP3 PARTITION(part02);

(4)组合分区:
这种分区是基于两种分区的组合,分区之中的分区被称为子分区。
按入职日期进行范围分区,再按DEPTNO进行LIST子分区
CREATE TABLE MYEMP4
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7,2)
)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST(DEPTNO)
(
PARTITION P1 VALUES LESS THAN(TO_DATE(‘1981-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P1A VALUES (10),
SUBPARTITION P1B VALUES (20),
SUBPARTITION P1C VALUES (30),
SUBPARTITION P1D VALUES (40)
),
PARTITION P2 VALUES LESS THAN (TO_DATE(‘1982-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P2A VALUES (10),
SUBPARTITION P2B VALUES (20),
SUBPARTITION P2C VALUES (30),
SUBPARTITION P2D VALUES (40)
),
PARTITION P3 VALUES LESS THAN (TO_DATE(‘1983-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P3A VALUES (10) ,
SUBPARTITION P3B VALUES (20),
SUBPARTITION P3C VALUES (30),
SUBPARTITION P3D VALUES (40)
),
PARTITION P4 VALUES LESS THAN (TO_DATE(‘1988-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P4A VALUES (10),
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (40)
),
PARTITION P5 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION P5A VALUES (10),
SUBPARTITION P5B VALUES (20),
SUBPARTITION P5C VALUES (30),
SUBPARTITION P5D VALUES (40)
)
);
– SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=‘MYEMP4’; 查看分区表情况

(5)分区表相关操作
1)查看分区数据(列表分区为例)
SELECT * FROM MYEMP2 PARTITION(MYEMP_DEPTNO_40);
1)添加分区(原有的分区表没有给定默认分区的前提)
ALTER TABLE MYEMP2 ADD PARTITION MYEMP_DEPTNO_50 VALUES (50);
2)删除分区
ALTER TABLE MYEMP2 DROP PARTITION MYEMP_DEPTNO_50;
注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
3)重命名表分区
以下代码将P21更改为P2
ALTER TABLE MYEMP2 RENAME PARTITION MYEMP_DEPTNO_50 TO MYEMP_DEPTNO_60;

总结:
1.非分区表, 不能直接改为分区表(通过重新建立分区表,将旧表数据导入到分区表)
2.创建了分区表, 给了默认分区, 不能添加其它分区
3.建完的分区表, 数据插入只能是当前分区所能包含的数据

问题:
1、索引是什么,如何建索引,举例说明
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,
它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。

2、索引的影响:索引可以提高查询的效率 2.占用空间 3.数据增删改时需要更新索引,因此索引对增删改时会有负面影响

3.索引的分类有哪些,索引失效问题

4.表分区有哪几种

增量同步和多表插入

1.增量更新数据
语法格式:MERGE(不是所有数据库都通用)
MERGE INTO 目标表
USING (增量)
ON (匹配字段)
WHEN MATCHED THEN UPDATE SET --UPDATE和SET之间不需要加表名
WHEN NOT MATCHED THEN INSERT VALUES
–INSERT和VALUES之间不需要加INTO 表名

– 全量同步(历史数据量太大,耗时太长)
INSERT INTO 目标表(字段)
SELECT 字段 FROM 源表;
insert into emp_bak select * from emp;
– 增量(按天增量较多,只更新按天变化的数据)

  1. 删除目标表前一天在源表中更新的数据,插入源表前一天的数据
    emp—>emp_bak
    delete from emp_bak where empno in (select empno from emp where update_dt >=sysdate -1 )

    insert into emp_bak select * from emp where update_dt >=sysdate -1

  2. Merge INTO, 增量是源表前一天的数据,
    如果跟目标表关联得上的数据, 表示在目标表中存在的数据, 源表发生了变化的数据;
    如果跟目标表关联不上的数据, 表示在目标表中没有的数据, 源表新增的数据.

例题:增量同步数据
CREATE TABLE SRC_DEPT AS SELECT * FROM DEPT WHERE DEPTNO IN (10,20);
ALTER TABLE SRC_DEPT ADD created_dt DATE;
ALTER TABLE SRC_DEPT ADD updated_dt DATE;
-- 0504源系统新增数据
UPDATE SRC_DEPT SET created_dt=DATE'2020-05-04';
UPDATE SRC_DEPT SET updated_dt=DATE'2020-05-04';
UPDATE SRC_DEPT SET loc='达拉斯' WHERE deptno=20;
-- 0505凌晨12点后, 目标表同步0504号数据(第一次同步要全量同步数据)
CREATE TABLE SRC_DEPT_BAK AS SELECT * FROM SRC_DEPT WHERE 1=2;
ALTER TABLE SRC_DEPT_BAK ADD etl_dt DATE;
INSERT INTO SRC_DEPT_BAK(deptno,dname,loc,created_dt,updated_dt)  SELECT deptno,dname,loc,created_dt,updated_dt FROM SRC_DEPT;
UPDATE SRC_DEPT_BAK SET etl_dt=DATE'2020-05-05';
-- 0505白天源系统数据新增两条数据,同时更新一条0504的数据
UPDATE SRC_DEPT SET loc='DALLAS',updated_dt=DATE'2020-05-05' WHERE deptno=20;
INSERT INTO SRC_DEPT SELECT DEPT.*,DATE'2020-05-05',DATE'2020-05-05' FROM DEPT WHERE DEPTNO IN (30,40);
-- 0506凌晨12点后, 目标表同步0505号数据(增量同步数据)
MERGE INTO SRC_DEPT_BAK a
USING (SELECT * FROM SRC_DEPT WHERE updated_dt=DATE'2020-05-05') b
ON (a.deptno=b.deptno)
WHEN MATCHED THEN UPDATE SET
   a.dname=b.dname
  ,a.loc=b.loc
  ,a.created_dt=b.created_dt
  ,a.updated_dt=b.updated_dt
  ,a.etl_dt=DATE'2020-05-06'
WHEN NOT MATCHED THEN INSERT
  (a.deptno,a.dname,a.loc,a.created_dt,a.updated_dt,a.etl_dt)   

VALUES (b.deptno,b.dname,b.loc,b.created_dt,b.updated_dt,DATE’2020-05-06’);

SELECT * FROM SRC_DEPT;
SELECT * FROM SRC_DEPT_BAK;

/*
delete from src_dept;
1).人事部5月4日新增了10,20两个部门信息
insert into src_dept values (10,‘ACCOUNTING’,‘NEW YORK’,DATE’2020-05-04’,DATE’2020-05-04’);
insert into src_dept values (20,‘RESEARCH’,‘达拉斯’,DATE’2020-05-04’,DATE’2020-05-04’);

2.5月5日凌晨系统备份数据:
INSERT INTO SRC_DEPT_BAK SELECT DEPTNO,DNAME,LOC,CREATED_DT,UPDATED_DT,DATE’2020-05-05’ FROM SRC_DEPT;

3.5月5日工作时间,人事部新增了两个30,40部门,并修改了20部门的LOC(地址),将‘达拉斯’改成了英文。

UPDATE SRC_DEPT SET LOC =‘DALLAS’,UPDATED_DT =DATE’2020-05-05’ WHERE DEPTNO =20;
insert into src_dept values (30,‘SALES’,‘CHICAGO’,DATE’2020-05-05’,DATE’2020-05-05’);
insert into src_dept values (40,‘OPERATIONS’,‘BOSTON’,DATE’2020-05-05’,DATE’2020-05-05’);

4.5月6日凌晨系统备份数据

MERGE INTO Src_Dept_Bak A
USING (SELECT * FROM SRC_DEPT WHERE UPDATED_DT =DATE’2020-05-05’) B
ON (A.DEPTNO = B.DEPTNO)
WHEN MATCHED THEN UPDATE SET
A.DNAME = B.DNAME,
A.LOC = B.LOC,
A.CREATED_DT = B.CREATED_DT,
A.UPDATED_DT =B.UPDATED_DT
WHEN NOT MATCHED THEN INSERT
(A.DEPTNO,A.DNAME,A.LOC,A.CREATED_DT,A.UPDATED_DT,A.ETL_DT)
VALUES
(B.DEPTNO,B.DNAME,B.LOC,B.CREATED_DT,B.UPDATED_DT,DATE’2020-05-06’)
*/

/*
1、人事部2020年10月28日新增了两个部门到SRC_DEPT

INSERT INTO SRC_DEPT VALUES (10,‘ACCOUNTING’,‘NEW YORK’,DATE’2020-10-28’,DATE’202-10-28’);
INSERT INTO SRC_DEPT VALUES (20,‘RESEARCH’,‘达拉斯’,DATE’2020-10-28’,DATE’202-10-28’);

SELECT * FROM SRC_DEPT

2、在2020年10月29日2:00,运行Schedule job调度将SRC_DEPT中的数据同步到SRC_DEPT_BAK

INSERT INTO SRC_DEPT_BAK SELECT D.*,DATE’2020-10-29’ FROM SRC_DEPT D

3、在2020年10月29日上班时间,人事部又新增了两个部门,并对20部门做了数据更新

INSERT INTO SRC_DEPT VALUES (30,‘SALES’,‘CHICAGO’,DATE’2020-10-29’,DATE’2020-10-29’);
INSERT INTO SRC_DEPT VALUES (40,‘OPERATIONS’,‘BOSTON’,DATE’2020-10-29’,DATE’2020-10-29’);

UPDATE SRC_DEPT SET LOC=‘DALLAS’,UPDATED_DT =DATE’2020-10-29’ WHERE DEPTNO =20;

4、在2020年10月30日2:00,运行schedule job调度,将SRC_DEPT中的数据同步到SRC_DEPT_BAK中(增量同步)

MERGE INTO SRC_DEPT_BAK A
USING (SELECT DEPTNO,DNAME,LOC,CREATED_DT,UPDATED_DT FROM SRC_DEPT WHERE UPDATED_DT >= DATE’2020-10-29’) B
ON (A.DEPTNO = B.DEPTNO)
WHEN MATCHED THEN UPDATE SET
– A.DEPTNO = B.DEPTNO,
A.DNAME = B.DNAME,
A.LOC = B.LOC,
A.CREATED_DT = B.CREATED_DT,
A.UPDATED_DT = B.UPDATED_DT,
A.ETL_DT = DATE’2020-10-30’
WHEN NOT MATCHED THEN INSERT
(A.DEPTNO,A.DNAME,A.LOC,A.CREATED_DT,A.UPDATED_DT,A.ETL_DT)
VALUES
(B.DEPTNO,B.DNAME,B.LOC,B.CREATED_DT,B.UPDATED_DT,DATE’2020-10-30’)

*/

MERGE的灵活应用:
若数据量大时,MERGE删除比DELETE好。
MERGE INTO EMP E
USING (SELECT * FROM EMP WHERE DEPTNO = 10) S
ON (S.EMPNO = E.EMPNO)
WHEN MATCHED THEN
UPDATE SET E.COMM = E.COMM DELETE WHERE 1 = 1;   --SET随便改,后面接DELETE



-- 未考虑分区情况
1.从100万的数据中删除deptno=10的数据(10万)   -- delete from emp where deptno=10;
2.将6000万的数据全部删除                     -- truncate table emp;
3.从6000万的数据中删除deptno=10的数据(10万)  -- merge into
4.一共6000万数据,分布情况如下:
部门标号      数据量
10            5800万
20            100万
30            50万
40            50万
删除10号部门数据?

创建一张新表,跟源表结构一致,将20,30,40号部门数据导入到新表;
旧表改名,将新表改名。(有外键约束等权衡考虑)

emp 6000w-->200w
emp-->改名--->emp_bak--->重新新建个emp表-->然后将emp_bak中200w保留的数据导入到emp表中

创建一张新表,跟源表结构一致,将20,30,40号部门数据导入到新表;
1). create table emp_new as select * from emp where deptno in (20,30,40);
2). ALTER TABLE EMP_NEW ADD…根据旧表来增加新表的主键或约束
3).alter table emp rename …将旧表改名
4).alter table emp_new rename emp 将新表改名成旧表

2.INSERT ALL
– 多条插入
CREATE TABLE t2 (id NUMBER(2),name VARCHAR2(20));
INSERT ALL
INTO t2
VALUES (1, ‘张美丽’)
INTO t2
VALUES (2, ‘王小二’)
SELECT * FROM dual; – 语句结构必须要select …
COMMIT;
SELECT * FROM t2;

-- 多表插入(分表--大表按照类别插入数据到小表)
INSERT ALL
	WHEN product_id = 111 THEN
		INTO apple_orders
	WHEN product_id = 222 THEN
		INTO orange_orders
	ELSE
		INTO banana_orders
	SELECT product_id, product_name, p_month 
	  FROM t1;
COMMIT;
SELECT * FROM apple_orders;
SELECT * FROM orange_orders;
SELECT * FROM banana_orders;

3.建表注意
CREATE TABLE ORCL_emp_syn
(“EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
“ETL_DATE” DATE
);

CREATE TABLE "ORCL_emp_syn"
("EMPNO" NUMBER(4,0), 
  "ENAME" VARCHAR2(10), 
  "JOB" VARCHAR2(9)
);

SELECT * FROM ORCL_emp_syn;
SELECT * FROM "ORCL_emp_syn";

问题:
1、什么是全量同步,增量同步
全量抽取类似于数据迁移或数据复制,它将数据源中的表或视图的数据从数据库中抽取出来放到目标表中,全量抽取比较简单。
insert into 目标表 select 字段1,字段2,… from 源表
2、增量同步方式

PLSQL变量声明和赋值

1.PL/SQL介绍
Oracle PL/SQL语言(Procedural Language/SQL)是结合了结构化查询与Oracle自身过程控制为一体的强大语言,
PL/SQL不但支持更多的数据类型,拥有自身的变量声明、赋值语句,而且还有条件、循环等流程控制语句。过
程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言,可以创建过程和函数以及程序包。
自带工具:sqlplus/sql developer
第三方工具:plsql/navicat premium

2.PL/SQL基础
2.1语法结构:PL/SQL块的语法
[DECLARE – 声明部分
–declaration statements]① – 声明变量、常量、游标等(声明的内容可以不在执行部分使用)
BEGIN – 执行部分
–executable statements ② – 对于执行部分不能去定义变量等(使用的变量、常量、游标等必须在声明的内容中)
[EXCEPTION – 异常处理
–exception statements] ③
END; – 分号不能省略

注意: 每一个命令语句结束后面必须加分号


特殊符号:
:= 赋值     v_cnt := 6;  对变量赋值为6
.. 连续值   1..6         表示1到6的整数
** 求幂     3**2         3的2次方为9


2.2声明与赋值
声明变量或常量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量声明必须在声明部分。
变量名 数据类型[ :=初始值]
代码演示:声明变量或常量(使用 CONSTANT 关键字声明常量)

-- 1 没有变量/常量
BEGIN
  DBMS_OUTPUT.PUT_LINE('0601BI课程!');
END;
  
  
-- 2 变量申明
DECLARE
v_sql VARCHAR2(20);
BEGIN
v_sql :='20200601儿童节快乐!';
v_sql :='儿童节快乐!';
DBMS_OUTPUT.PUT_LINE(v_sql);
END;

-- 3 变量申明并赋值
DECLARE
v_sql VARCHAR2(20) := '20200601儿童节快乐!';
BEGIN
-- v_sql :='儿童节快乐!';  -- 变量可以被重新赋值
DBMS_OUTPUT.PUT_LINE(v_sql);
END;


-- 4 常量赋值
DECLARE
v_sql CONSTANT VARCHAR2(20) := '20200601儿童节快乐!';
BEGIN
-- v_sql :='儿童节快乐!';   -- 常量不能被重新赋值
DBMS_OUTPUT.PUT_LINE(v_sql);
END;


-- 5 异常部分
DECLARE
v_1 NUMBER(6);
v_2 NUMBER(6);
BEGIN
v_1 :=100;         -- 分子
v_2 :=&输入数字;   -- 分母(手工输入)
DBMS_OUTPUT.PUT_LINE(v_1/v_2);
EXCEPTION
WHEN ZERO_DIVIDE
  THEN DBMS_OUTPUT.PUT_LINE('分母为0!'); 
END;


练习:设置三个变量,  v_1、v_2、v_3, 分别将自己的姓名、性别和专业赋值给这三个变量, 并将三个变量拼接在一起打印出来!
      如打印结果:姓名:张三  性别:男  专业:统 计学!
      DBMS_OUTPUT.PUT_LINE( '姓名:' || ' 性别:' || v_1 || ' 专业:'|| ...);

DECLARE
V_Z1 VARCHAR2(20);
V_Z2 VARCHAR2(20);
V_Z3 VARCHAR2(20);
BEGIN
V_Z1:=’&姓名’;
V_Z2:=’&性别’;
V_Z3:=’&专业’;
DBMS_OUTPUT.PUT_LINE
(‘姓名:’||V_Z1||'性别: '||V_Z2||‘专业:’||V_Z3);
END;

PLSQL隐士游标和引用数据类型

1.隐式游标
对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。
但是查询的结果只能是一行记录,不能是零行或者多行记录。

例题1:打印出emp中员工编号为7369的姓名和工资。  
DECLARE
  V_ENAME VARCHAR2(20);
  V_SAL NUMBER(10,2);
BEGIN
  SELECT ENAME,SAL
	INTO V_ENAME,V_SAL          -- 隐士游标赋值(来源于表的查询结果,查询结果只能是单行)
	FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.PUT_LINE('员工名称:'||V_ENAME||' 员工工资:'||V_SAL);    -- 不能直接打印表,只能是单行字段拼接后的字符串形式打印
END;
注意:使用select…into语句对变量赋值,要求查询的结果必须是一行,不能是多行或者没有记录。

2.引用数据类型
%TYPE: 引用数据库中的某列的数据类型或某个变量的数据类型。
%ROWTYPE:引用数据库中的一行(所有字段)作为数据类型。

例题1:打印出emp中员工编号为7369的姓名和工资。
-- %TYPE 引用表单个字段类型
DECLARE
  v_name emp.ename%TYPE;      -- 引用表的单个字段类型
  v_sal  emp.sal%TYPE;
BEGIN
  SELECT ename, sal
	INTO v_name, v_sal
	FROM emp 
   WHERE empno=7369;
  dbms_output.put_line('姓名:' || v_name ||' 工资: ' ||v_sal);
END;


-- %ROWTYPE 引用表的所有字段类型
DECLARE
  v_emp emp%ROWTYPE;         -- 引用整个表的字段类型
BEGIN
  SELECT *
	INTO v_emp               -- 插入整行数据(所有字段数据)
	FROM emp 
   WHERE empno=7369;
  dbms_output.put_line('姓名:' || v_emp.ename ||' 工资: ' ||v_emp.sal);  -- 不能直接打印整个表,只能是单个值的拼接
END;



DECLARE
  v_emp emp%ROWTYPE;           -- 引用整个表的字段类型
BEGIN
  SELECT ename, sal
	INTO v_emp.ename,v_emp.sal -- 只插入两个字段数据
	FROM emp 
   WHERE empno=7369;
  dbms_output.put_line('姓名:' || v_emp.ename ||' 工资: ' ||v_emp.sal);  -- 不能直接打印整个表,只能是单个值的拼接
END;



练习:1.计算192除以3后的余数并打印出来
	  2.打印员工7499的员工姓名、工作职位和部门名称

游标有哪几种:
隐式游标
显式游标

PLSQL条件转换语句

  1. IF 语句

    – IF-THEN
    IF 条件 THEN
    –条件结构体
    END IF;
    DECLARE
    V_NUM1 NUMBER:= 10;
    V_NUM2 NUMBER:= 30;
    BEGIN
    IF V_NUM1>V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’>’ ||V_NUM2);
    END IF;
    END;

    – IF-THEN-ELSE
    IF 条件 THEN
    –条件成立结构体
    ELSE
    –条件不成立结构体
    END IF;
    DECLARE
    V_NUM1 NUMBER:= 10;
    V_NUM2 NUMBER:= 30;
    BEGIN
    IF V_NUM1>V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’>’ ||V_NUM2);
    ELSE
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’<=’ ||V_NUM2);
    END IF;
    END;

    – IF-THEN-ELSIF
    IF 条件1 THEN
    –条件1成立结构体
    ELSIF 条件2 THEN
    –条件2成立结构体
    ELSE
    –以上条件都不成立结构体
    END IF;
    DECLARE
    V_NUM1 NUMBER:= 10;
    V_NUM2 NUMBER:= 30;
    BEGIN
    IF V_NUM1>V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’>’ ||V_NUM2); – 每个 then 面的语句结束都要加分号
    ELSIF V_NUM1=V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’=’ ||V_NUM2);
    ELSE – 可以没有此分支,有此分支一定不要加条件判断
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’<’ ||V_NUM2);
    END IF; – if 语句结束用 end if
    END;

  2. CASE 语句

    CASE
    WHEN 表达式1 THEN 语句序列1;
    WHEN 表达式2 THEN 语句序列2;
    WHEN 表达式3 THEN 语句序列3;
    ……
    ELSE 语句序列N;
    END CASE;

    DECLARE
    V_NUM1 NUMBER:= 10;
    V_NUM2 NUMBER:= 30;
    BEGIN
    CASE
    WHEN V_NUM1>V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’>’ ||V_NUM2); – 每个 then 面的语句结束都要加分号
    WHEN V_NUM1=V_NUM2 THEN
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’=’ ||V_NUM2);
    ELSE – 可以没有此分支但是以上语句中必须有一个条件为真,如果有此分支一定不要加条件判断
    DBMS_OUTPUT.PUT_LINE(V_NUM1 ||’<’ ||V_NUM2);
    END CASE; – case 语句结束用 end case
    END;

练习:1.查找员工编号7521的工资,如果工资小于1500则打印其编号和名字,
如果工资大于等于1500且小于等于2000则打印其编号、名字和工资,
否则打印其编号、名字和奖金。

declare
V_EMP EMP%ROWTYPE;
V_EMPNO NUMBER;
begin
V_EMPNO :=&输入员工编号;
SELECT * INTO V_EMP FROM EMP WHERE EMPNO = v_empno;
IF V_EMP.SAL < 1500 THEN
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: '||V_EMP.ENAME);
ELSIF V_EMP.SAL >= 1500 AND V_EMP.SAL <= 2000 THEN
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: ‘||V_EMP.ENAME||’ 工资: '||V_EMP.SAL);
ELSE
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: ‘||V_EMP.ENAME||’ 奖金: '||V_EMP.COMM);
END IF;
end;

创建 :

CREATE OR REPLACE PROCEDURE SP_EMP_OUTPUT(P_EMPNO NUMBER)
IS
V_EMP EMP%ROWTYPE;

begin

SELECT * INTO V_EMP FROM EMP WHERE EMPNO = P_EMPNO;
IF V_EMP.SAL < 1500 THEN
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: '||V_EMP.ENAME);
ELSIF V_EMP.SAL >= 1500 AND V_EMP.SAL <= 2000 THEN
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: ‘||V_EMP.ENAME||’ 工资: '||V_EMP.SAL);
ELSE
DBMS_OUTPUT.PUT_LINE('编号: ‘||V_EMP.EMPNO||’ 名字: ‘||V_EMP.ENAME||’ 奖金: '||V_EMP.COMM);
END IF;
end;

    2.查找员工编号7521的工资,如果工资小于1500,若其奖金小于500则奖金再加上100,否则奖金加上50;
      如果工资大于等于1500,若其奖金小于200则奖金加上80,否则奖金减去50.(提示:通过update更新comm)
   /*
   DECLARE
     v_emp emp%ROWTYPE;
   BEGIN
     SELECT sal,empno,ename,comm
       INTO v_emp.sal,v_emp.empno,v_emp.ename,v_emp.comm
       FROM emp
      WHERE empno=7521;
     IF v_emp.sal<1500 THEN 
         dbms_output.put_line('编号:'|| v_emp.empno ||' 名字:'||v_emp.ename);
     ELSIF v_emp.sal<=2000 THEN 
         dbms_output.put_line('编号:'|| v_emp.empno ||' 名字:'||v_emp.ename ||' 工资:'||v_emp.sal);
     ELSE
         dbms_output.put_line('编号:'|| v_emp.empno ||' 名字:'||v_emp.ename ||' 奖金:'||v_emp.comm);
     END IF;
   END;
   */	

PLSQL三种循环

循环控制
PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有:
1.无条件循环LOOP-END LOOP语句
2.WHILE + LOOP-END LOOP循环语句
3.FOR + LOOP-END LOOP循环语句
在上面的三类循环中EXIT用来强制结束循环。

-- LOOP循环
LOOP
	--循环体
END LOOP;
语法格式:
1.循环体在LOOP和END LOOP之间,在每个LOOP循环体中,首先执行循环体中的语句序列,执行完后再重新开始执行。
2.在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。

例题1:打印1到100数字(换行)
-- 	LOOP循环
DECLARE 
  V_NUM NUMBER :=0;
BEGIN
  LOOP
	  V_NUM := V_NUM + 1;           -- 变量自增
	  DBMS_OUTPUT.PUT_LINE(V_NUM);   
	  EXIT WHEN V_NUM>=100;         -- 退出条件
  END LOOP;
END;


-- WHILE循环(先判断条件,条件成立再执行循环体)
WHILE 条件 LOOP
	--循环体
END LOOP;

DECLARE
  V_NUM NUMBER :=1;
BEGIN
  WHILE V_NUM<=100 LOOP           -- 条件成立则循环
	DBMS_OUTPUT.PUT_LINE(V_NUM);
	V_NUM := V_NUM + 1;           -- 变量自增
  END LOOP;
END;



-- FOR循环
FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP
--循环体
END LOOP;
FOR循环需要预先确定的循环次数,可通过给循环变量指定下限和上限来确定循环运行的次数,然后循环变量在每次循环中递增(或者递减)

BEGIN
  FOR V_NUM IN 1..100 LOOP        --  可以不声明变量,数字只能从小到大,在结果集中遍历
	DBMS_OUTPUT.PUT_LINE(V_NUM);
  END LOOP;
END;



练习:1.打印1到100之间的奇数
     2.打印10次当前时间(sysdate)
     3.打印 -1+2-3+4-5+...+100 计算结果(奇数前是负号,偶数前为+号)

    /*
	DECLARE
	  V_SUM NUMBER := 0;
	BEGIN
	  FOR V_NUM IN 1..100 LOOP
		  V_SUM := V_SUM+(-1)**V_NUM*V_NUM;
	  END LOOP;
	  DBMS_OUTPUT.PUT_LINE('-1+2-3+4-5+...+100的值为:'||V_SUM);
	END;
	
	*/

PLSQL显示游标

显示游标
游标的类型有两种:隐式游标和显示游标。
PL/SQL会为所有的SQL数据操作声明一个隐式的游标,包括只返回一条记录的查询操作。
显示游标四个步骤:
1.声明
2.打开游标
3.逐行获取数据
4.关闭游标

语法结构:声明游标
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
IS SELECT 语句;  --游标的声明

语法结构:执行游标
OPEN 游标名[(实际参数1[,实际参数2...])];  --打开游标
FETCH 游标名 INTO 变量名1[,变量名2...];
或
FETCH 游标名 INTO 记录变量;  --提取数据
CLOSE 游标名;  --关闭游标(千万别忘了!)

游标属性:%FOUND、%NOTFOUND
%FOUND:
用于判断游标是否从结果集中提取数据。如果提取到数据,则返回值为TRUE,否则返回值为FALSE。
%NOTFOUND:
该属性与%FOUND相反,如果提取到数据则返回值为FALSE;如果没有,则返回值为TRUN。


练习1:查询10号部门所有员工的姓名和工资并打印(PL/SQL)
-- 隐式游标
DECLARE
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
BEGIN
  SELECT ENAME,SAL
	INTO V_ENAME,V_SAL    -- 多值无法插入
	FROM EMP
   WHERE DEPTNO=10;
  DBMS_OUTPUT.PUT_LINE(V_ENAME||V_SAL);
END;


-- 显式游标
DECLARE
  CURSOR C_EMP IS                   -- 先声明游标(放在declare中)
  SELECT ENAME,SAL
	FROM EMP
   WHERE DEPTNO=10;
  V_EMP C_EMP%ROWTYPE;              -- 声明变量为游标行类型
BEGIN
  -- DBMS_OUTPUT.PUT_LINE(C_EMP);   -- 没法直接打印结果集(表的结果集)
  OPEN C_EMP;                       -- 打开游标
  LOOP                              -- 游标为结果集所以要有循环
	FETCH C_EMP INTO V_EMP;
	EXIT WHEN C_EMP%NOTFOUND;       -- 退出循环条件
	-- DBMS_OUTPUT.PUT_LINE(C_EMP); -- 没法直接打印结果集(单行的结果集)
	DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME||' -- '||V_EMP.SAL);   -- 只能从变量中取字段
	-- EXIT WHEN C_EMP%NOTFOUND;        -- 放在最后打印结果会多出最后一行
  END LOOP;
  CLOSE C_EMP;
END;


--FOR循环
DECLARE
  CURSOR C_EMP IS
  SELECT ENAME,SAL
	FROM EMP
   WHERE DEPTNO=10; 
BEGIN
  FOR V_EMP IN C_EMP LOOP
	DBMS_OUTPUT.PUT_LINE('姓名: ' || V_EMP.ENAME || '工资: ' || V_EMP.SAL);
  END LOOP;
END;




-- 游标参数
例题1:打印某个部门的员工姓名和工资
DECLARE
  CURSOR C_EMP(P_DEPTNO EMP.DEPTNO%TYPE) IS   -- 游标参数
  SELECT ENAME,SAL
	FROM EMP 
   WHERE DEPTNO=P_DEPTNO;
BEGIN
  FOR V_EMP IN C_EMP(&部门号) LOOP    -- 键盘输入变量值(键盘输入值时,字符型要加单引号)/打开游标的时候传入
  DBMS_OUTPUT.PUT_LINE('姓名: ' || V_EMP.ENAME || '工资: ' || V_EMP.SAL);
  END LOOP;
END;




练习:1.用游标显示所有部门编号与名称,以及其所拥有的员工人数。
	  2.编写一个程序块,利用%type属性,接收一个雇员号,从emp表中显示该雇员的整体薪水(即,薪水加佣金)。

PLSQL异常处理和%ROWCOUNT

1.异常处理
语法格式:异常处理
BEGIN
–可执行部分
EXCEPTION – 异常处理开始
WHEN 异常名1 THEN
–对应异常处理;
WHEN 异常名2 THEN
–对应异常处理;
……
WHEN OTHERS THEN
–其他异常处理;
END;

------- 
DECLARE
  V_ENMAE EMP.ENAME%TYPE; 
BEGIN
  SELECT ENAME
  INTO V_ENMAE
  FROM EMP
   WHERE DEPTNO=10;   -- 'dfad' 其它错误
   DBMS_OUTPUT.PUT_LINE('返回单行值');       
  EXCEPTION   -- 异常处理开始
	WHEN TOO_MANY_ROWS THEN
	DBMS_OUTPUT.PUT_LINE('SELECT INTO 返回多行值!');
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('SELECT INTO 没有返回值!');
	  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('其它错误!');
END;

2.程序块中的%ROWCOUNT
2.1 SQL%ROWCOUNT 用于记录修改的条数,必须放在一个增删改等修改类语句后面执行,select语句用于查询的话无法使用,
当你执行多条修改语句时,SQL%ROWCOUNT 之前执行的最后一条语句修改数为准。

	BEGIN
	  DELETE FROM emp;
	  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录删除数据的条数
	  INSERT INTO emp(empno) VALUES (7777);
	  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录插入数据的条数
	  UPDATE emp SET comm=1000 WHERE empno=7369;
	  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录更新数据的条数
	END;


2.2 游标%ROWCOUNT  用于记录游标遍历到第几行。

    例题:用游标属性%rowcount实现输出前10个员工的编号、名称和岗位。
	DECLARE
	  CURSOR C_EMP IS
		SELECT * FROM EMP;
	BEGIN
	  FOR T_EMP IN C_EMP LOOP
		DBMS_OUTPUT.PUT_LINE('员工编号:' || T_EMP.EMPNO || ' 员工名称:' ||
							 T_EMP.ENAME || ' 岗位:' || T_EMP.JOB);
		EXIT WHEN C_EMP%ROWCOUNT >= 10;
	END LOOP;
	END;

PLSQL中的DDL和DML以及动态SQL

1.PL/SQL 中的DML和DDL语言

-- DML
CREATE TABLE EMP_01 AS SELECT * FROM EMP;
BEGIN
   DELETE FROM EMP_01 WHERE DEPTNO=10;
   UPDATE EMP_01 SET SAL=8888 WHERE DEPTNO=20;
   INSERT INTO EMP_01(EMPNO,DEPTNO) VALUES(1111,40);
   COMMIT;
END;
SELECT * FROM EMP_01;

-- DDL
BEGIN
   -- CREATE TABLE EMP_02 AS SELECT * FROM EMP;   -- DDL不能直接在PL/SQL块中使用
   -- TRUNCATE TABLE EMP_01;  
   -- ALTER TABLE EMP_01 ADD CT DATE;
   DROP TABLE EMP_01;
END;

2.动态SQL
在PL/SQL程序开发中,可以使用DML语句和事务控制语句,但是还有很多语句(比如DDL语句)不能直接在PL/SQL中执行。
这些语句可以使用动态SQL来实现。

语法格式:动态SQL
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]

-- 例1
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE YYY AS SELECT * FROM EMP';    -- 字符串语句最后不要加分号;
END;
或者
DECLARE
  V_SQL VARCHAR2(100) :='CREATE TABLE YYY AS SELECT * FROM EMP'  
BEGIN
  EXECUTE IMMEDIATE V_SQL;
END;

-- 例2
CREATE TABLE T_TMP_1 (
SNO NUMBER,
SNAME VARCHAR(20)
);
-- INSERT INTO T_TMP_1 VALUES(1, 'A''LIN');
SELECT * FROM T_TMP_1;
-- 转义
DECLARE 
  V_SQL VARCHAR2(100) := 'INSERT INTO T_TMP_1 VALUES(1, ''A''''LIN'')';
BEGIN
  DBMS_OUTPUT.PUT_LINE(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
END;


-- 用参数传入
DECLARE
  V_EMPNO  EMP.EMPNO%TYPE := &输入员工编号;
  V_DEPTNO EMP.DEPTNO%TYPE := &输入部门编号;
  V_SAL    EMP.SAL%TYPE;
BEGIN
  -- SELECT sal INTO v_sal FROM emp WHERE empno=V_EMPNO AND deptno=V_DEPTNO;
  EXECUTE IMMEDIATE 'SELECT SAL FROM EMP WHERE EMPNO = :参数1 AND deptno= :参数2'    -- :参数1和:参数2为参数名,用后面using的值替换
	 INTO V_SAL                 -- 查询的结果写入变量
	USING V_EMPNO, V_DEPTNO;    -- 替换动态sql中的参数:参数1和:参数2
  DBMS_OUTPUT.PUT_LINE(V_SAL);
END;



-- 关于 DBMS_OUTPUT.PUT 和 DBMS_OUTPUT.PUT_LINE
BEGIN
  DBMS_OUTPUT.PUT('0710');    -- 不换行(后面必须有DBMS_OUTPUT.PUT_LINE,才能打印)
  DBMS_OUTPUT.PUT_LINE('BI');
  DBMS_OUTPUT.PUT_LINE('HAHAHA');
END;
-- 
BEGIN
  DBMS_OUTPUT.PUT('0710');
  DBMS_OUTPUT.PUT('BI'); 
  DBMS_OUTPUT.PUT_LINE('');
END;


-- 双重循环
BEGIN
  FOR X IN 1..3 LOOP
	FOR Y IN 4..6 LOOP
		DBMS_OUTPUT.PUT_LINE(X||'*'||Y||'='||X*Y); 
	END LOOP;
  END LOOP;
END;  
	  
	  

练习:打印当前用户下面所有的表名称及每张表其对应的总行数
SELECT table_name FROM user_tables; 
/*
DECLARE
  CURSOR C_TABLE_NAME IS
  SELECT TABLE_NAME FROM USER_TABLES;
  V_CNT NUMBER;
  V_SQL VARCHAR2(100);
  
BEGIN
  FOR V_TABLE_NAME IN C_TABLE_NAME LOOP
  -- DBMS_OUTPUT.PUT_LINE(V_TABLE_NAME.TABLE_NAME);
  V_SQL := 'SELECT COUNT(1) FROM ' || V_TABLE_NAME.TABLE_NAME;
  -- DBMS_OUTPUT.PUT_LINE(V_SQL);
  EXECUTE IMMEDIATE V_SQL INTO V_CNT;
  DBMS_OUTPUT.PUT_LINE(V_TABLE_NAME.TABLE_NAME|| '......................' || V_CNT);
  END LOOP;
END;
或者
SELECT t.TABLE_NAME,NUM_ROWS FROM user_tables t;
*/	

PLSQL存储过程

SQL --> pl/sql 块 --> 过程体(存储过程/函数/触发器) --> 包

创建存储过程
为什么需要创建存储过程?
(1)封装代码,执行复杂的逻辑
(2)先编译后执行,第一次编译成功后,之后调用,其效率要比单独运行sql语句要高
(3)存储过程是一个完整的事物,整体的代码要么全部成功要么全部失败
存储过程在工作中的实际应用?
(1)处理多张表关联的逻辑,并将最终的结果集数据写入到目标表
(2)同一种数据库,可以做跨库数据同步,增量(MERGE INTO …)或全量
语法格式:创建存储过程
CREATE [OR REPLACE] PROCEDURE 过程名(参数1 [IN|OUT|IN OUT] 数据类型,参数2 [IN|OUT|IN OUT] 数据类型……)
IS|AS
PL/SQL过程体;

三种参数:传入(in或省去),传出(out),传入或传出(in out)
注意: 1.数据类型不要带字符长度也不用括号 varchar2/number/date
	  2.如果存储过程不带参数,过程名后不用括号,IS|AS 后可以直接跟声明内容,不要declare
	  3.为in的参数,不能在过程中被赋值,只能作为调用传入值
	  4.省去参数类型则默认为IN传入
	  5.IN OUT 参数必需在声明时候赋初值,调用中用变量名
语法格式:调用存储过程
BEGIN
  过程名[(参数)];
END;

-- 1.不带参数		
例题1:打印emp表中所有雇员的姓名,工作和薪水。
CREATE OR REPLACE PROCEDURE SP_EMP1
IS
  CURSOR C_EMP_DEPTNO IS
  SELECT ENAME, JOB, SAL FROM EMP;  
BEGIN
  FOR V_EMP_DEPTNO IN C_EMP_DEPTNO LOOP
	  DBMS_OUTPUT.PUT_LINE('姓名:' || V_EMP_DEPTNO.ENAME ||
						   '工作:' || V_EMP_DEPTNO.JOB || 
						   '工资:' || V_EMP_DEPTNO.SAL);
  END LOOP;
END;

-- 调用
BEGIN
  SP_EMP1;  -- 也可写成SP_EMP1();
END;




-- 2.只带传入参数
例题2:接收一个部门号,显示该部门的所有雇员的姓名,工作和薪水。
CREATE OR REPLACE PROCEDURE SP_EMP2 (P_DEPTNO IN EMP.DEPTNO%TYPE DEFAULT 20)  -- 给默认值
IS
  -- 游标
  CURSOR C_EMP IS
  SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
	FOR V_EMP IN C_EMP LOOP
	  DBMS_OUTPUT.PUT_LINE(' 姓名:'|| V_EMP.ENAME ||' 工作:' || V_EMP.JOB || ' 薪水:' || V_EMP.SAL);
  END LOOP;
END;

-- 调用
BEGIN
  SP_EMP2(10);
  -- SP_EMP2(&部门编号);
END;
或者    
DECLARE
  V_DEPTNO EMP.DEPTNO%TYPE :=10;
BEGIN
  SP_EMP2 (V_DEPTNO);
END;
或者 
BEGIN
  SP_EMP2(P_DEPTNO => 10);  -- 参数定向调用(多个参数可以改变顺序)
END;



-- 3.带传入和传出参数
例题3:查询某个部门中的某个职位员工的姓名、工资、入职日期。(只考虑单行情况)
CREATE OR REPLACE PROCEDURE SP_EMP3(P_DEPTNO   IN EMP.DEPTNO%TYPE,    -- 传入,不可被赋值,看作常量
									P_JOB      IN EMP.JOB%TYPE,       -- 传入
									P_ENAME    OUT EMP.ENAME%TYPE,    -- 传出,可被赋值,看作变量
									P_SAL      OUT EMP.SAL%TYPE,      -- 传出
									P_HIREDATE OUT EMP.HIREDATE%TYPE) -- 传出
 IS
BEGIN
  SELECT ENAME, SAL, HIREDATE
	INTO P_ENAME, P_SAL, P_HIREDATE
	FROM EMP
   WHERE DEPTNO = P_DEPTNO
	 AND JOB = P_JOB;
END;

-- 调用
DECLARE
  v_ename    emp.ename%TYPE;
  v_sal      emp.sal%TYPE;
  v_hiredate emp.hiredate%TYPE;
BEGIN
  SP_EMP3(20,'MANAGER',v_ename,v_sal,v_hiredate);
  DBMS_OUTPUT.PUT_LINE(' 姓名:' || v_ename ||
					   ' 工资:' || v_sal || 
					   ' 入职日期:' || v_hiredate);
  -- 单行的值打印可以防止调用中,如果多行值,打印防止调用中只会显示最后一条被替换的值
END;


-- 4.参数中含IN OUT 类型
CREATE OR REPLACE PROCEDURE SP_EMP4(P_DEPTNO    IN  NUMBER,
									P_HIREDATE  OUT DATE,
									P_SAL       OUT NUMBER,
									P_JOB_ENAME IN OUT VARCHAR2)
									----------参数不能定义长度
									----------OUT可以被赋值的参数,理解为PLSQL块中的变量
IS                                   
BEGIN
  SELECT E.ENAME, E.SAL, E.HIREDATE
	INTO P_JOB_ENAME, P_SAL, P_HIREDATE  -- P_JOB_ENAME 当OUT参数 传出ENAME
	FROM EMP E
   WHERE E.DEPTNO = P_DEPTNO             -- P_DEPTNO IN 参数只能传入值,不能被赋值
	 AND E.JOB = P_JOB_ENAME;            -- P_JOB_ENAME 当IN参数  传入JOB
  EXCEPTION   -- 异常处理
	  WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('没找到任何结果');
END;


--调用存储过程
DECLARE
  V_HIREDATE DATE;                                -- OUT 参数
  V_SAL NUMBER;                                   -- OUT 参数
  V_JOB_ENAME VARCHAR2(100) :='MANAGER';          -- IN OUT 参数此处先声明并赋值
BEGIN
  SP_EMP4(10, V_HIREDATE, V_SAL, V_JOB_ENAME);    -- 存储过程参数要对应(个数和类型)
  DBMS_OUTPUT.PUT_LINE(V_JOB_ENAME || ' ' || V_SAL || ' ' || V_HIREDATE);
END;



-- 5.注意参数给默认值情况
CREATE OR REPLACE PROCEDURE SP_EMP5(T_DEPTNO EMP.DEPTNO%TYPE DEFAULT 10,
									 T_JOB    EMP.JOB%TYPE)

 IS
  T_EMPNO EMP.EMPNO%TYPE;
  T_SAL   EMP.SAL%TYPE;

BEGIN
  SELECT EMPNO, SAL
	INTO T_EMPNO, T_SAL
	FROM EMP
   WHERE DEPTNO = T_DEPTNO
	 AND JOB = T_JOB;
  DBMS_OUTPUT.PUT_LINE('员工编号: ' || T_EMPNO || ' 工资: ' || T_SAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('未找到数据');
  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('其他错误');
  
END;


调用1
BEGIN
  SP_EMP5(20,'MANAGER');
END;
调用2
BEGIN
  SP_EMP5(T_JOB=>'MANAGER');
END;
调用3
BEGIN
  SP_EMP5(T_JOB=>'MANAGER',T_DEPTNO=>30);
END;
  
  
  
  


练习:1.编写存储过程,传入部门号和工作类型,将员工编号和对应工资保存到一个表中。
	  2.编写存储过程,统计每个部门名称及其员工人数和平均工资(将统计结果插入到建立的目标表中,目标表在存储过程外建立)
	    目标表:4个字段 (dname,emp_num,avg_sal,etl_date)-- etl_date为计算数据的时间,用sysdate就行
		注意:数据量特别大的时候,通过游标插入数据时候,实现每插入1万条数据提交一次;
			  数据量不大的时候,可以直接一次性插入数据到目标表,最后提交。 
	    -- 全量数据(清空所有数据支持重跑)
		DELETE FROM 表;  
		-- 增量数据(清空当天数据支持重跑)
		DELETE FROM 表 WHERE TRUNC(etl_date)=TRUNC(SYSDATE);
	  
	  /*
	  
	  -- 建立目标表
	  CREATE TABLE t_emp_stat(dname VARCHAR2(20),emp_num NUMBER(1),
							  avg_sal NUMBER(10,2),etl_date DATE);
	  -- 查询语句
	  SELECT d.dname,
			 COUNT(DISTINCT e.empno) AS emp_num,
			 round(AVG(sal),2) AS avg_sal
		FROM dept d
		LEFT JOIN emp e
		  ON d.deptno=e.deptno
		GROUP BY d.dname;
		
	  -- 编写过程体(数据量不大,一次提交数据)
	  CREATE OR REPLACE PROCEDURE SP_EMP_STAT IS
	  BEGIN
		DELETE FROM T_EMP_STAT;  -- 数据量不大可以用delete清空数据支持重跑
		INSERT INTO T_EMP_STAT
		  (DNAME, EMP_NUM, AVG_SAL, ETL_DATE)
		  SELECT D.DNAME,
				 COUNT(DISTINCT E.EMPNO) AS EMP_NUM,
				 ROUND(AVG(SAL), 2) AS AVG_SAL,
				 SYSDATE AS ETL_DATE
			FROM DEPT D
			LEFT JOIN EMP E
			  ON D.DEPTNO = E.DEPTNO
		   GROUP BY D.DNAME;
		COMMIT;
	  END;
	  
	  -- 数据量大
	  CREATE OR REPLACE PROCEDURE SP_EMP_STAT1 IS
		CURSOR C_EMP_STAT IS
		  SELECT D.DNAME,
				 COUNT(DISTINCT E.EMPNO) AS EMP_NUM,
				 ROUND(AVG(SAL), 2) AS AVG_SAL,
				 SYSDATE AS ETL_DATE
			FROM DEPT D
			LEFT JOIN EMP E
			  ON D.DEPTNO = E.DEPTNO
		   GROUP BY D.DNAME;
	  BEGIN
		EXECUTE IMMEDIATE 'TRUNCATE TABLE T_EMP_STAT';-- 数据量大用truncate清空数据支持重跑
		FOR V_EMP_STAT IN C_EMP_STAT LOOP
		  INSERT INTO T_EMP_STAT
			(DNAME, EMP_NUM, AVG_SAL, ETL_DATE)
		  VALUES
			(V_EMP_STAT.DNAME,
			 V_EMP_STAT.EMP_NUM,
			 V_EMP_STAT.AVG_SAL,
			 V_EMP_STAT.ETL_DATE);
		  IF MOD(C_EMP_STAT%ROWCOUNT, 10000) = 0 THEN
			COMMIT;
		  END IF;
		END LOOP;
		COMMIT;
	  END;
	  
  */

问题:
存储过程的应用:
1、处理多张表关联的逻辑,并将最终的结果集数据写入到目标表(报表统计查询,多表关联结果集查询,更新某个业务逻辑)
2、同一种类型数据库,可以做跨库数据同步,增量(MERGE INTO …)或全量

PLSQL自定义函数

自定义函数
语法格式:创建函数
CREATE [OR REPLACE] FUNCTION 函数名(参数 参数类型[IN|OUT|IN OUT] 数据类型,……)
RETURN 返回的数据类型 – 不带数据类型长度
IS|AS
PL/SQL函数体; – 里面必须要有一个RETURN子句

-- 不带参数情况
CREATE OR REPLACE FUNCTION FUN_TEST
RETURN NUMBER    -- 函数的返回类型
IS
BEGIN
	RETURN 100;
END;

SELECT FUN_TEST FROM DUAL;
		

-- 传入两个参数,返回最大值
CREATE OR REPLACE FUNCTION FUN_MAX (P_NUM1 IN NUMBER, P_NUM2 IN NUMBER DEFAULT 99)
RETURN NUMBER    -- 函数的返回类型
IS
BEGIN
	IF P_NUM1>P_NUM2 THEN 
		RETURN P_NUM1;	
	ELSE 
		RETURN P_NUM2; 
	END IF;
END;

调用:
SELECT FUN_MAX(12,20) FROM DUAL;
SELECT EMP.*,FUN_MAX(COMM,SAL) FROM emp;
SELECT FUN_MAX(20) FROM DUAL;
如果P_NUM1给了默认值,调用可以为:
SELECT FUN_MAX(P_NUM2=>20) FROM DUAL;
或者
BEGIN
  dbms_output.put_line(FUN_MAX(12,20));
END;
或者
DECLARE
  v_num NUMBER;
BEGIN
  v_num := FUN_MAX(12,20);
  dbms_output.put_line(v_num);
END;



-- 函数返回类型为游标(对应报表接口)
-- 传入部门编号,返回整个部门的员工信息(函数)
CREATE OR REPLACE FUNCTION FUN_REF(P_DEPTNO EMP.DEPTNO%TYPE)
  RETURN SYS_REFCURSOR IS
  C_EMP SYS_REFCURSOR;
BEGIN
  OPEN C_EMP FOR
	SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO;
  RETURN C_EMP;
END FUN_REF;




练习:1.输入2个整数,返回最小到最大数之间的连乘的结果(两个整数在1到20之间);
	  2.输入3个整数,取中位数。
	  
	  /*
		CREATE OR REPLACE FUNCTION FUN_JZ(P_N1 IN NUMBER, P_N2 IN NUMBER)
		  RETURN VARCHAR2 -- 返回字符串
		 IS
		  V_M NUMBER := 1;
		BEGIN
		  IF P_N1 > 20 OR P_N2 > 20 OR P_N1 < 1 OR P_N2 < 1 THEN
			RETURN '超出值范围!'; -- 返回字符串
		  ELSIF P_N1 > P_N2 THEN
			FOR I IN P_N2 .. P_N1 LOOP
			  V_M := I * V_M;
			END LOOP;
			RETURN TO_CHAR(V_M); -- 数字隐式转换为字符串返回
		   ELSIF P_N1 = P_N2 THEN
			RETURN TO_CHAR(P_N1*P_N2);
		  ELSE
			FOR I IN P_N1 .. P_N2 LOOP
			  V_M := I * V_M;
			END LOOP;
			RETURN TO_CHAR(V_M); -- 数字隐式转换为字符串返回
		  END IF;
		END;

		
		CREATE OR REPLACE FUNCTION MID_014(P_1 NUMBER, P_2 NUMBER, P_3 NUMBER)
		  RETURN NUMBER IS
		  A NUMBER(20);
		BEGIN
		  IF P_1 >= P_2 AND P_1 <= P_3 OR P_1 >= P_3 AND P_1 <= P_2 THEN
			A := P_1;
			RETURN A;
		  ELSIF P_2 >= P_1 AND P_2 <= P_3 OR P_2 >= P_3 AND P_2 <= P_1 THEN
			A := P_2;
			RETURN A;
		  ELSE
			A := P_3;
			RETURN A;
		  END IF;
		END;

		SELECT fun_avg_l(4,15,7) FROM dual;
		或者
		CREATE OR REPLACE FUNCTION fun_mid(P_1 NUMBER, P_2 NUMBER, P_3 NUMBER)
		  RETURN NUMBER IS
		BEGIN
		  IF (P_1-P_2)*(P_1-P_3)<=0 THEN
			RETURN P_1;
		  ELSIF (P_2-P_3)*(P_2-P_1)<=0 THEN
			RETURN P_2;
		  ELSE 
			RETURN P_3;
		  END IF;
		END;

		SELECT fun_mid(7,7,3) FROM dual;
		
		或者
		CREATE OR REPLACE FUNCTION FUN_ZCQH(P_1 NUMBER,P_2 NUMBER,P_3 NUMBER)
		RETURN NUMBER
		IS
		BEGIN
		  IF (P_1 BETWEEN P_2 AND P_3) OR (P_1 BETWEEN P_3 AND P_2) THEN
			RETURN P_1;
		  ELSIF (P_2 BETWEEN P_1 AND P_3) OR (P_2 BETWEEN P_3 AND P_1) THEN
			RETURN P_2;
		  ELSIF (P_3 BETWEEN P_1 AND P_2) OR (P_3 BETWEEN P_2 AND P_1) THEN
			RETURN P_3;
		  END IF;
		END;
		*/

问题:
存储过程与自定义函数的区别:

1、返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
2、调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用.
函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作
(比如修改、插入数据库表或执行某些DDL语句等等)
3、自定义函数查询中不允许DML操作,插入或更新数据

PLSQL触发器

触发器简介
触发器的定义就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。
触发器可以分为:语句级触发器和行级触发器。
1、在一个表中定义的语句级的触发器,当这个表被删除时,程序就会自动执行触发器里面定义的操作过程。
这个就是删除表的操作就是触发器执行的条件了。
2、在一个表中定义了行级的触发器,那当这个表中一行数据发生变化的时候,比如删除了一行记录,那触发器也会被自动执行了。

	--触发器语法
	语法:
	CREATE [OR REPLACE] TRIGGER TRIGGER_NAME
	{BEFORE | AFTER} TRIGGER_EVENT
	ON TABLE_NAME
	[FOR EACH ROW]
	[WHEN TRIGGER_CONDITION]
	TRIGGER_BODY

	语法解释:
	TRIGGER_NAME:   触发器名称
	BEFORE | AFTER : 指定触发器是在触发事件发生之前触发或者发生之后触发
	TRIGGER_EVENT:  触发事件,在DML触发器中主要为INSERT、UPDATE、DELETE等
	TABLE_NAME:     表名,表示发生触发器作用的对象
	FOR EACH ROW:   指定创建的是行级触发器,若没有该子句则创建的是语句级触发器
	WHEN TRIGGER_CONDITION:添加的触发条件
	TRIGGER_BODY:   触发体,是标准的PL/SQL语句块
	
   示例:
	CREATE TABLE t_order_detail
	(
	   detail_id  NUMBER(20)    --主键
	  ,ord_no     NUMBER(20)    --订单号
	  ,ord_cnt    VARCHAR2(32)  --订单数量
	  ,ord_amt    NUMBER(10,2)  --订单金额
	  ,ord_cdate  date          --下单时间
	  ,ord_update date          --更新时间
	);


	CREATE TABLE t_order_detail_log     --创建日志表,用于记录对t_order_detail表的操作日志
	(
	   log_id     NUMBER           --日志id
	  ,log_action VARCHAR2(100)    --操作名称
	  ,log_date   DATE             --操作时间
	  ,log_message1   VARCHAR2(32) --日志信息1
	  ,log_message2   VARCHAR2(32) --日志信息2
	);

	行级触发器(after触发器)-- 主要是对敏感数据的操作进行日志记录,以便数据追踪
	创建触发器:将对 t_order_detail 表的操作记录到 t_order_detail_log 表中
	(OF 用于指定一个或多个字段,指定字段被更新时才会触发触发器)
	CREATE OR REPLACE TRIGGER tr_t_order_detail
	AFTER INSERT OR DELETE OR UPDATE OF detail_id,ord_amt     -- 字段
	ON t_order_detail                                         -- 表
	FOR EACH ROW  -- 行级触发
	BEGIN 
		IF INSERTING THEN
		   INSERT INTO t_order_detail_log VALUES(1,'insert',SYSDATE,:NEW.detail_id,:NEW.ord_amt);
		ELSIF DELETING THEN
		   INSERT INTO t_order_detail_log VALUES(2,'delete',SYSDATE,:OLD.detail_id,:OLD.ord_amt);
		ELSIF UPDATING THEN
		  INSERT INTO t_order_detail_log VALUES(3,'update_old',SYSDATE,:OLD.detail_id,:OLD.ord_amt);
		  INSERT INTO t_order_detail_log  VALUES(4,'update_new',SYSDATE,:NEW.detail_id,:NEW.ord_amt);
		 END IF;
	END;
	
	--插入一条数据
	INSERT INTO t_order_detail(detail_id,ord_no,ord_cnt,ord_amt,ord_cdate,ord_update) 
	VALUES (1,201801012111134567,3,21.68,
			TO_DATE('2018-01-01 21:11:13','YYYY-MM-DD HH24:MI:SS'),
			TO_DATE('2018-01-01 21:11:13','YYYY-MM-DD HH24:MI:SS')); 
	--删除一条数据		
	DELETE t_order_detail WHERE detail_id=7;   
	--修改订单的数量
	UPDATE t_order_detail SET ord_cnt=20 WHERE detail_id=1;   
	--修改订单的金额
	UPDATE t_order_detail SET ord_amt=100 WHERE detail_id=1;
	-- 如果t_order_detail数据回滚,t_order_detail_log的数据也会回滚
	SELECT * FROM t_order_detail;
	SELECT * FROM t_order_detail_log;
	
	
	语句级触发器(BEFORE触发器):用来控制对表的修改  -- 主要是删除和更新(重要数据保留)
	CREATE OR REPLACE TRIGGER tr_t_order_detail_table
	BEFORE UPDATE OR DELETE ON t_order_detail
	BEGIN
	   IF DELETING THEN
		 RAISE_APPLICATION_ERROR(-20001,'该表不允许删除数据');
	   ELSIF UPDATING THEN
		 RAISE_APPLICATION_ERROR(-20002,'该表不允许修改数据');
	   END IF;
	END;

	DELETE FROM t_order_detail;
	UPDATE t_order_detail SET ord_amt=200 WHERE detail_id=1;
	DROP TABLE t_order_detail;

	
	

	-- 表字段的自增序列(触发器+序列)
	CREATE SEQUENCE S_DETAIL_ID;
	-- 触发器
	CREATE OR REPLACE TRIGGER tr_t_order_detail_id
	  BEFORE INSERT ON t_order_detail -- 表
	  FOR EACH ROW -- 行级触发
	DECLARE
	NEXTID NUMBER;
	BEGIN
	IF :NEW.detail_id IS NULL THEN --sno是列名
		:NEW.detail_id := S_DETAIL_ID.NEXTVAL;
	END IF;
	END;
	
   SELECT * FROM t_order_detail;
   INSERT INTO t_order_detail(ord_no,ord_cnt,ord_amt,ord_cdate,ord_update) 
   VALUES (202001012111134567,5,66.76,sysdate,sysdate);
	
   INSERT INTO t_order_detail(ord_no,ord_cnt,ord_amt,ord_cdate,ord_update) 
   VALUES (20200823111134567,7,21.68,sysdate,sysdate);

			


练习: 触发器记录对emp表的员工编号字段的数据变更情况(新建一张和emp表一样的表)

PLSQL创建日志

创建日志
日志是用来追溯问题的,记录整个程序的运行情况,知道哪个环节报错了,
记录每一步花了多少时间,判断哪一步性能不好,从而对程序进行修改和优化。
人为创建的日志区别于Oracle系统自带的日志,后者调用的成本比较高。
通常报错的时候,会有很多条报错信息,第一条是真正报错的原因。
一般SP里都会有调用日志语句。

语法格式:创建日志
--创建日志表
CREATE TABLE LOG_RECORD(
 LOG_ID NUMBER,
 SP_NAME VARCHAR2(100),
 STEP NUMBER,
 FINISH_TIME DATE,
 REMARKS VARCHAR2(100));

--创建序列用于LOG_ID
CREATE SEQUENCE SEQ_LOG_ID;

--目标表T_SYN_EMP
CREATE TABLE T_SYN_EMP AS SELECT * FROM EMP WHERE 1=2;



(1)插入数据到日志表(INSERT INTO)
-- 全量同步emp的数据到T_SYN_EMP
CREATE OR REPLACE PROCEDURE SP_SYN_EMP
IS
BEGIN
  -- 1 
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_SYN_EMP';
  INSERT INTO LOG_RECORD VALUES(SEQ_LOG_ID.NEXTVAL, 'SP_SYN_EMP',1,SYSDATE,'清空T_SYN_EMP表数据!');		  
  -- 2
  INSERT INTO T_SYN_EMP SELECT * FROM EMP;
  INSERT INTO LOG_RECORD VALUES(SEQ_LOG_ID.NEXTVAL, 'SP_SYN_EMP',2,SYSDATE,'全量插入数据到T_SYN_EMP表!');
  -- 3
  INSERT INTO LOG_RECORD VALUES(SEQ_LOG_ID.NEXTVAL, 'SP_SYN_EMP',3,SYSDATE,'存储过程执行完成!');
  COMMIT;
END;

-- 调用
BEGIN
  SP_SYN_EMP;
END;

SELECT * FROM LOG_RECORD;
SELECT * FROM T_SYN_EMP;


(2)插入数据到日志表(调用日志存储过程)
-- 创建存储过程日志记录
CREATE OR REPLACE PROCEDURE SP_LOG(P_SP_NAME  VARCHAR2,
								   P_STEP     NUMBER,
								   P_REMARKS  VARCHAR2) 
IS
BEGIN
  INSERT INTO LOG_RECORD
	(LOG_ID, SP_NAME, STEP, FINISH_TIME, REMARKS)
  VALUES
	(SEQ_LOG_ID.NEXTVAL, P_SP_NAME, P_STEP, SYSDATE, P_REMARKS);
  COMMIT;
END;


-- 跑某个存储过程的时候调用日志存储过程

CREATE OR REPLACE PROCEDURE SP_SYN_EMP_1
  IS
	V_SP_NAME VARCHAR2(30):='SP_SYN_EMP_1';
  BEGIN
	-- 日志数据删除支持重跑
	DELETE FROM LOG_RECORD 
	WHERE TRUNC(finish_time)=TRUNC(SYSDATE)
	  AND sp_name='SP_SYN_EMP_1';
	-- 1 
	SP_LOG(V_SP_NAME, 1, '清空T_SYN_EMP表数据');   
	EXECUTE IMMEDIATE 'TRUNCATE TABLE T_SYN_EMP';
	-- 2
	SP_LOG(V_SP_NAME, 2, '全量插入数据到T_SYN_EMP表');
	INSERT INTO T_SYN_EMP SELECT * FROM EMP;
	-- 3
	SP_LOG(V_SP_NAME, 3, '存储过程执行完成!');
	COMMIT;
  END;



-- 调用
BEGIN
  SP_SYN_EMP;
END;


SELECT * FROM T_SYN_EMP;
SELECT * FROM LOG_RECORD;



-- 通过日志可以发现的问题
 /*--单个存储过程查看--*/
1.存储过程当天是否成功执行(查看存储过程SP_SYN_EMP_1)
SELECT *
  FROM LOG_RECORD
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE)
   AND sp_name='SP_SYN_EMP_1';
2.存储过程总耗时
SELECT (MAX(finish_time)-MIN(finish_time))*24*60 AS 总耗时
  FROM LOG_RECORD
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE)
   AND sp_name='SP_SYN_EMP_1';
3.存储过程耗时比较长的步骤
SELECT t.*,
       (LEAD(finish_time) OVER (ORDER BY step)-finish_time)*24*60 AS 每步耗时
  FROM LOG_RECORD t
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE)
   AND sp_name='SP_SYN_EMP_1';
   
/*--整体查看--*/
1.存储过程当天是否成功执行
SELECT DISTINCT sp_name
  FROM LOG_RECORD
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE);
2.存储过程耗时比较长的步骤(总时长超过20分钟)
SELECT sp_name
  FROM (
SELECT sp_name,
       (MAX(finish_time)-MIN(finish_time))*24*60 AS 总耗时
  FROM LOG_RECORD
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE)
 GROUP BY sp_name) t
WHERE 总耗时>20;
3.存储过程耗时比较长的步骤(超过6分钟步骤)
SELECT *
  FROM (
  SELECT t.*,
       (LEAD(finish_time) OVER (PARTITION BY sp_name ORDER BY step)-finish_time)*24*60 AS 每步耗时
  FROM LOG_RECORD t
 WHERE TRUNC(finish_time)=TRUNC(SYSDATE)) t
 WHERE 每步耗时>6;




	
练习:删除emp表各个部门的经理的相关信息,插入自己的相关信息到emp表,记录存储过程的操作步骤到日志表(存储过程实现)
	 (日志通过调用SP_LOG(P_SP_NAME  VARCHAR2, P_CYCLE_ID NUMBER, P_STEP NUMBER, P_REMARKS VARCHAR2)插入)
	  4个步骤记录到日志表:
	  1)开始
	  2)删除
	  3)插入
	  4)执行完毕

PLSQL创建包

创建包
包就是把相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起,
并赋予一定的管理功能的程序块。
一个程序包由两部分组成:包定义和包体。
其中包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素,
这些元素为包的共有元素。包主体则定义了包定义部分的具体实现。
注意:包声明的对象不一定要在包体中去使用,但是包体中的对象一定要在包声明中定义并保持一致。

语法格式:创建包头
CREATE [OR REPLACE] PACKAGE 包名
IS|AS
	变量、常量及数据类型定义;
	游标定义头部;
	函数、过程的定义和参数列表以及返回类型;
END [包名];

语法格式:创建包体
CREATE [OR REPLACE] PACKAGE BODY 包名
IS|AS
	PROCEDURE 过程名(参数)
	IS|AS
	BEGIN
	过程体;
	END [过程名];
	FUNCTION 函数名(参数) RETURN 类型
	IS|AS
	BEGIN
	函数体;
	END [函数名];
END;

	
例题1:创建一个包, 包中含有存储过程和函数
CREATE OR REPLACE PACKAGE PK_MYPACKAGE
IS
  A NUMBER;                                  -- 声明变量(声明的对象不一定要在包体中使用)
  PROCEDURE MY_SP(P_EMPNO IN NUMBER);        -- 声明存储过程
  FUNCTION MY_FUN(P_NUM1 IN NUMBER , P_NUM2 IN NUMBER) RETURN NUMBER; -- 声明函数
END;


CREATE OR REPLACE PACKAGE BODY PK_MYPACKAGE
IS
  -- 通过员工号找员工姓名和工资存储过程
  PROCEDURE MY_SP(P_EMPNO IN NUMBER)    -- 和包中的声明要对应(并且一定要先声明)
  IS
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
  BEGIN
	SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
	DBMS_OUTPUT.PUT_LINE('姓名:'|| V_ENAME ||'工资:'|| V_SAL);
	EXCEPTION 
	  WHEN NO_DATA_FOUND THEN
		  DBMS_OUTPUT.PUT_LINE('SELECT INTO语句中没有返回任何记录!'); 
	  WHEN TOO_MANY_ROWS THEN 
		  DBMS_OUTPUT.PUT_LINE('SELECT INTO语句中返回多于1条记录!');
  END;
  -- 比较大小函数
  FUNCTION MY_FUN(P_NUM1 IN NUMBER , P_NUM2 IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN 
	IF P_NUM1>P_NUM2 THEN 
	  RETURN P_NUM1;
	ELSE 
	  RETURN P_NUM2; 
	END IF;
  END;
END;


-- 调用存储过程
BEGIN
  PK_MYPACKAGE.MY_SP(&员工编号);
END;
-- 调用函数
SELECT PK_MYPACKAGE.MY_FUN(10,20) FROM DUAL;
	
	
-- 用户下面的表
SELECT * FROM USER_TABLES;
-- 用户下面的过程体
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE ='PACKAGE'  ;
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE ='FUNCTION' ;
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE ='PROCEDURE';
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE ='TRIGGER';

PLSQL存储过程定时

设置存储过程定时作业(DBMS_JOB)
该过程用于建立一个新的作业,当建立作业的时候,需要通过设置相应的参数来告诉Oracle要执行的内容,
要执行的时间,要执行任务的间隔。如下格式:
DBMS_JOB.SUBMIT(
JOB OUT BINARY_INTERGER, – 用于指定作业编号
WHAT IN VARCHAR2, – 用于指定作业要执行的操作
NEXT_DATE IN DATE DEFAULT SYSDATE, – 用于指定该操作的下一次运行的日期
INTERVAL IN VARCHAR2 DEFAULT ‘NULL’, – 用于指定该操作的时间间隔
NO_PARSE IN BOOLEAN DEFAULT FALSE, – 用于指定是否需要解析与作业相关的过程
INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE, – 用于指定哪个例程可以运行作业?
FORCE IN BOOLEAN DEFAULT FALSE – 用于指定是否强制运行与作业相关的例程
);
– 举例
CREATE TABLE TEST_JOB(
ID1 NUMBER,
NAME1 VARCHAR2(30),
CT_DT DATE
);
–如果序列存在就删除,然后创建一个序列
CREATE SEQUENCE TEST_JOB_ID_SEQ;
–创建一个序列,每一次向表中插入一条数据,并且表中的ID字段值使用序列指定
CREATE OR REPLACE PROCEDURE SP_INSERT_JOB IS
BEGIN
INSERT INTO TEST_JOB
VALUES
(TEST_JOB_ID_SEQ.NEXTVAL, ‘TEST’ || TEST_JOB_ID_SEQ.CURRVAL,SYSDATE);
COMMIT;
END;

	DECLARE
	  V_JOBNO NUMBER;
	BEGIN
	--提交,操作的时间间隔设置为5秒(default参数可以不用传入)
	  DBMS_JOB.SUBMIT(JOB=>V_JOBNO,
					  WHAT=>'SP_INSERT_JOB;', -- 'SP_INSERT_JOB;' 分号不能少 调用存储过程INSERT_JOB
					  NEXT_DATE=>SYSDATE,
					  INTERVAL=>'SYSDATE+1/(24*60*12)');  
	--打印序列号
	  DBMS_OUTPUT.PUT_LINE('JOBNO='||V_JOBNO);
	--运行
	  DBMS_JOB.RUN(V_JOBNO);
	END;
  
	-- 删除
	BEGIN
	DBMS_JOB.REMOVE(JOBNO);  -- JOBNO为具体数字
	END; 

一些网址

https://www.5axxw.com/wenku/mv/1070281t.html --物化视图刷新
https://www.cnblogs.com/lushanyanyu/p/10916821.html --oracle to_char函数
http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html --Oracle中start with…connect by子句的用法
https://blog.csdn.net/xue_yanan/article/details/78210654 -------Oracle删除约束和主键的语句
https://blog.csdn.net/jeryjeryjery/article/details/70047022 —merge into

例子

declare
TYPE EMP_REC_TYPE IS RECORD(
EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE,
HIREDATE EMP.HIREDATE%TYPE);

TYPE NESTED_EMP_TYPE IS TABLE OF EMP_REC_TYPE;
a NESTED_EMP_TYPE;
cursor bb is select * from emp;
begin

select EMPNO, ENAME, HIREDATE BULK COLLECT INTO a from emp;
dbms_output.put_line(a.last);
for i in a.FIRST…a.last loop
dbms_output.put_line(a(i).empno);
end loop;
for i in bb loop
dbms_output.put_line(i.empno);
dbms_output.put_line(bb%rowCOUNT);
end loop;
end;

DECLARE
– 定义记录类型
TYPE EMP_REC_TYPE IS RECORD(
EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE,
HIREDATE EMP.HIREDATE%TYPE);
– 定义基于记录的嵌套表
TYPE NESTED_EMP_TYPE IS TABLE OF EMP_REC_TYPE;
– 声明变量
EMP_TAB NESTED_EMP_TYPE;
BEGIN
– 使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中
SELECT EMPNO, ENAME, HIREDATE BULK COLLECT INTO EMP_TAB FROM EMP;

FOR I IN EMP_TAB.FIRST … EMP_TAB.LAST LOOP
DBMS_OUTPUT.PUT_LINE('当前记录: ’ || EMP_TAB(I)
.EMPNO || CHR(9) || EMP_TAB(I)
.ENAME || CHR(9) || EMP_TAB(I).HIREDATE);
END LOOP;
END;
select * from emp;

SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;
select country,sum(salary) from employee group by rollup(country)

------------------第5讲练习题-----------------
select * from emp;
select * from dept;

declare
/a dept.deptno%type;
b dept.dname%type;
c number;
/
cursor c_d is
select d.deptno,d.dname,count(e.empno) as rs from emp e right join dept d
on e.deptno=d.deptno group by d.deptno,d.dname;
begin
for i in c_d loop
dbms_output.put_line(i.deptno||i.dname||i.rs);
end loop;
end;

declare
/a dept.deptno%type;
b dept.dname%type;
c number;
/
cursor c_d is
select d.dname,e.ename as xm from emp e right join dept d
on e.deptno=d.deptno order by d.deptno;
begin
for i in c_d loop
dbms_output.put_line(i.dname||’ '||i.xm);
end loop;
end;
drop function r_2_c
create or replace procedure sp_r_2_c(tname varchar2,cname varchar2)
AUTHID CURRENT_USER
is

–cursor cr is select distinct b,count(b) over() as bb from a3;
–type cursor_t is ref cursor;
cr SYS_REFCURSOR;
vc varchar2(100);
va varchar2(100) :=’’;
vac varchar2(100) :=’’‘上海’’,’‘北京’’,’‘深圳’’’;
vsql varchar2(100);
vg varchar2(100);
vt number(10);
begin
open cr for
‘select distinct ‘||cname||’ as b,count(’||cname||’) over() as bb from ‘||tname;
loop
fetch cr into vg,vt;
exit when cr%NOTFOUND;
va:=va||q’[’]’||vg||q’[’,]’;
dbms_output.put_line(va);
end loop;
dbms_output.put_line(substr(va,1,length(va)-1));
close cr;
va:=substr(va,1,length(va)-1);
vsql := ‘with a4 as(
SELECT * FROM a3 PIVOT(SUM© FOR b IN (’||va||’)))
select * from a4’;
dbms_output.put_line(vsql);
execute immediate vsql;

END;

drop table a4;
select * from a4
select r_2_c(‘a3’,‘b’) from dual;
begin
sp_r_2_c(‘a3’,‘b’);
end;

with a4 as(
SELECT * FROM a3 PIVOT(SUM© FOR b IN (‘北京’,‘上海’,‘深圳’)))
select * from a4
/for vc in cr loop
if cr%rowcount=vc.bb then
va:=va||q’[’]’||vc.b||q’[’]’;
else
va:=va||q’[’]’||vc.b||q’[’,]’; end if;
dbms_output.put_line(va);
end loop;
/
/vsql := ‘create table a4 as
SELECT * FROM a3 PIVOT(SUM© FOR b IN (’||va||’))’;
dbms_output.put_line(vsql);
execute immediate vsql;
/
/* return 1;
end;*/

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值