一、基本查询
1、查询结构
（5）select * |列名|表达式 – 表示表的所有列名称;多个列或者多个表达式用逗号分隔
（1）from 表名|结果集 – 查看的对象为表或者结果集
（2）where 条件 – 多个条件用and/or/not 连接
（3）group by 列名 – 多个列用逗号分隔，分组
（4）having 条件 – 一般为聚合函数表达式,分组后过滤，前面要有GROUP BY,多个表达式 and/or/not 连接
（6）order by 列名 [ asc / desc ] – asc 升序(默认),desc 降序
2、条件查询
-
运算符
- > 、>= 、< 、<= 、 = 、<=> 、 != 、<>
-
逻辑运算符
- and、or、not
-
模糊运算符
-
like ：字符匹配操作可以使用通配符’%‘和’_‘， ‘%’ 代表零个或多个字符，’_’ 代表任意一个字符
-
between and 、 in：查询包含在指定区间内（包含边界）的 值
-
not between and
-
not in
-
is null
-
is not null
-
--查询名字中包含'A'd select * from emp where ename like '%A%' --按时间查询 select * from emp where hiredate between date'1980-01-01' and date'1982-01-01'
-
-
分页查询
-
--使用子查询在原表加上一列rownum来实现分页 SELECT * FROM (SELECT E.*, ROWNUM RN FROM EMP E) T WHERE T.RN <5
-
3、子查询
-
单行子查询：不向外部返回结果,或者只返回一行结果
- SELECT * FROM EMP WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME = ‘FORD’)
-
多行子查询：向外部返回零行、一行或者多行结果
- SELECT * FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO)
4、多表查询 (结果集增加列)
- 内连接
- 隐式内连接
- SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
- 显式内连接
- SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 隐式内连接
- 左外连接
- 隐式左外连接
- SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+);
- 显式左外连接
- SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 隐式左外连接
- 右外连接
- 隐式左外连接
- SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;
- 显式左外连接
- SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 隐式左外连接
- 全外连接
- SELECT * FROM EMP E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 交叉连接
- 隐式交叉连接
- SELECT * FROM EMP,DEPT
- 显式交叉连接
- SELECT * FROM EMP E CROSS JOIN DEPT D
4、集合运算 (结果集增加行)
查询所返回的列数以及列的类型必须匹配,列名可以不同，只有UNION ALL不会去重。其他三个都需要排序后去重,性能比较差
-
并集运算：将两个查询结果进行合并
- UNION : 去重，排序
- SELECT * FROM EMP WHERE DEPTNO = 10 UNION SELECT * FROM EMP WHERE SAL <2000;
- UNION ALL : 不去重，不排序
- SELECT * FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT * FROM EMP WHERE SAL <2000;
- UNION : 去重，排序
-
交集运算：两者共有部分 INTERSECT
- SELECT * FROM EMP WHERE SAL>1500 INTERSECT SELECT * FROM EMP WHERE DEPTNO =20;
-
差集运算：前者有后者无 MINSU
- SELECT * FROM EMP WHERE DEPTNO =20 MINUS SELECT * FROM EMP WHERE SAL>1500
二、数据定义（DDL）
数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除对象）、TRUNCATE （删除表数据）命令等。
1、数据类型
- CHAR(length) --存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
- VARCHAR2(length) --存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
- NUMBER(p，s) --既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分，p默认是38位），s是指小数位数。
- DATE --存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
- NULL
- 在查询条件中NULL值用IS NULL作条件,非NULL值用 IS NOT NULL做条件
- 空值跟任何值进行算术运算,得到的结果都为空值
- 空值跟任何值进行关系运算,得到的结果都为不成立
- 空值不参与任何聚合运算
- 排序的时候,空值永远是最大的
2、CREATE（创建）命令
-
-- 语法格式 create table 表名 （列名1 数据类型， 列名2 数据类型，...); --加注释 comment on table 表名 is '表注释名'； comment on table 表名.列名1 is '字段注释名'; -
--创建一张学生表 create table student_info --学生信息表 (id number(4) primary key --主键 ,sno number(4) --学号 ,sname varchar2(20) not null --姓名 ,sex varchar2(2) check(sex = '男'or sex = '女') --性别 ,graduated_year number(4) --毕业年龄 ,major varchar2(40) --专业 ,province varchar2(20) --省份 ,dnum number constraint pk_1 references emp(empno) --外键 );
3、ALTER 更改表结构
-
修改的列没有数据时,可以随便更改类型；修改的列有数据时,只能在同一种类型上增加长度
-- 修改列数据类型
alter table 表名 modify 列名 新的数据类型;
alter table student_info modify sno varchar2(20);
--修改表名
alter table 表名 rename to 新表名;
alter table sutdent_info to student_info_new;
-- 修改列名
alter table 表名 rename column 旧列名 to 新列名;
alter table student_info rename column sno to snumber;
-- 增加列
alter table 表名 add 列名 数据类型;
alter table student_info add age number(3);
-- 删除列
alter table 表名 drop column 列名;
alter table student_info drop column sex;
4、DROP 删除表对象
-
-- 语法格式 drop table 表名; drop table student_info;
5、truncate(对象是表)删除表数据
-
清空整张表数据, 不会将清除的数据写入日志,不能跟where
-
-- 语法格式 truncate table 表名; truncate table student_info;
6、表的约束
定义
- 表的约束是Oracle数据库中应用在表数据上的一系列强制性规则。当向已创建的表中插入数据或修改表中的数据时，必须满足表的完整性约束所规定的条件
- 例如，学生的性别必须是“男”或“女”，各个学生的学号不得相同等。
- 在设计表的结构是，应该充分考虑在表上需要施加的完整性约束。
- 表的完整性约束既可以在创建表时制定，也可以在表创建之后再指定。可以对一个或多个字段进行约束。
分类
- PRIMARY KEY：主键约束
- FOREIGN KEY：外键约束
- CHECK：检查约束
- UNIQUE：唯一约束
- NOT NULL：非空约束
创建命令
-
一张表只有一个主键，主键非空且唯一
-
外键只能依赖于另一张表的主键（例如EMP表中的DEPTNO依赖于DEPT表中的DEPTNO）
-
约束不能修改，只能删除重建
-
--添加主键约束 alter table 主表名 add constraint 约束名 primary key(列名1[,列名2...]) alter table student_info add constraint p_sno primary key(sno); --添加外键约束 alter table 主表名 add constraint 约束名 foreign key(列名1[,列名2...]) references 从表名(列名1[,列名2...]) --添加CHECK约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件) --添加唯一约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名) --添加非空约束 ALTER TABLE 表名 MODIFY 列名 NOT NULL
7、创建分区表
-
create table students_info( id number(4) primary key, name varchar2(30), dnum number, datatime date ) partition by range(datatime) (partition part1 values less than(to_date('1999-4-1','yyyy/mm/dd')), partition part2 values less than(to_date('1999-4-2','yyyy/mm/dd')), partition part3 values less than(to_date('2000-4-2','yyyy-mm-dd')) ) --添加分区 alter table students_info add partition part4 values less than(to_date('2000-4-3','yyyy/mm/dd')); --查询分区数据 select * from students_info partition(part4);
8、创建自增序列
-
因为是首次查询序列的当前值，内存中之前并没有缓存序列的任何值，所以需要先查询 一下序列的下一个值（此时，Oracle会自动缓存已查询的序列值），再查询序列的当前值。
-
create sequence SEQ_ID minvalue 1 ---最小者=值 maxvalue 99999999 ---最大值 start with 1 -- 从1开始计数 increment by 1 -- 每次加几个 nocache -- cache/nocache：nocache不缓存。cache缓存。开启缓存，效率高 ; select SEQ_ID.currval from dual;----查看序列当前值 select SEQ_ID.nextval from dual;--查看序列当前下个值，每次运行语句都会增加
9、索引
- 类型：唯一索引（主键、唯一）、组合索引、基于函数的索引、反向键索引、位图索引
- 索引失效
- 查询不规范： IN EXISTS NOT EXISTS NOT IN
- 全表扫描、算术运算
- 解决方法：分库分表分区加索引
- 优点：加快检索速度；减少分组合排序的时间；唯一性；
- 缺点：占用空间；占用时间；
三、数据操纵（DML）
数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT(查找）命令等
对数据表进行DML操作后,一定要主要对数据进行提交（commit）或者回滚（rollback）
1.INSERT INTO 插入数据到表中
-
--语法格式 INSERT INTO 表名(列名1,列名2,……) VALUES (值1,值2……) INSERT INTO student_2(ename,age,birthday) VALUES ('张三',20,date'2000-12-01'); INSERT INTO student_2(ename,age,birthday) VALUES ('李四',22,date'1998-12-01');
2.UPDATE 更新表数据
-
--语法格式 UPDATE 表名 SET 列名1=值, 列名2=值,…… WHERE 条件
3.DELETE 删除表数据
-
--语法格式 DELETE FROM 表名 WHERE 条件; 删除的数据可以从日志中找回
四、函数
1、字符函数
- CONCAT(x,y) --连接字符串x和y
- LENGTH(x) --返回x的长度
- LOWER(x) --x转换为小写
- UPPER(x) --x转换为大写
- TRIM(x) --去掉字符串两端的多余的空格
- REPLACE(x,old,new) --在x中查找old，并替换为new
- SUBSTR(x,start[,length]) --返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾
2、数字函数
- ABS(x) x绝对值 ABS(-3)=3
- MOD(x,y) x除以y的余数 MOD(8,3)=2
- POWER(x,y) x的y次幂 POWER(2,3)=8
- ROUND(x[,y]) x在第y位四舍五入 ROUND(3.456,2)=3.46
- TRUNC(x[,y]) x在第y位截断，直接截取，不四舍五入 TRUNC(3.456,2)=3.45
3、日期函数
-
add_months(d,n) --在某一个日期d上，加上指定的月数n，返。d表示日期，n表示要加的月数
select sysdate,add_months(sysdate,3) from dual;
-
last_day(d) --返回指定日期当月的最后一天
select sysdate,last_day(sysdate) from dual;
-
round(d,‘fmt’) --返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
-
trunc(d,‘fmt’)对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
TRUNC(date[,fmt])：将date截取到fmt指定的形式，如果fmt省略，则截取到最近的 -
select sysdate, --22/4/25 12:14:34 round(sysdate), --22/4/26 round(sysdate,'day'), --22/4/24 round(sysdate,'month'), --22/5/1 round(sysdate,'year') --22/1/1 from dual;
4、转换函数
- 转换为字符串：to_char
select to_char(sysdate,'yyyymmdd') from dual;
- 转换为日期：to_date
select to_date('20170731123245','yyyymmdd hh24:mi:ss') from dual;
- 转换为数字：to_number
select to_number('123456') from dual;
5、其他常用函数
-
nvl(x,value) --如果x为空，返回value，否则返回x。
select ename,comm,nvl(comm,0)+100 c from emp where sal<2000
-
nvl2(x,value1,value2) --如果x非空，返回value1，否则返回value2
select ename,nvl2(comm,comm+100,200) from emp;
-
decode(列|表达式，值1，value1，值2，value2,…,默认值) --当参数的值为判断值1，则返回value1……当参数的值匹配不到时，则返回默认值
SELECT E.ENAME, DECODE(E.JOB, 'CLERK','业务员', 'SALESMAN','销售员', 'MANAGER','经理', 'ANALYST','分析员', 'PRESIDENT','总裁') FROM EMP E
-
case when end --适用于多条件判断
-- 第一种语法 CASE 参数 WHEN 判断值1 THEN 返回值1 WHEN 判断值2 THEN 返回值2 …… ELSE 默认值 END --例 Select E.Ename, case e.job WHEN 'CLERK' THEN'业务员' WHEN 'SALESMAN' THEN'销售员' WHEN 'MANAGER' THEN'经理' WHEN 'ANALYST' THEN'分析员' WHEN 'PRESIDENT' THEN'总裁' END FROM EMP E --第二种语法 CASE WHEN 条件1 THEN 返回值1 WHEN 条件2 THEN 返回值2 …… ELSE 默认值 END --例 select e.*, case when sal>3000 then '工资很高' when sal>1000 then '工资一般' else '工资很低' end from emp e -
行列转换
-
--创建表 emp014 create table emp014(id number,q number,amt number) id q AMT 01 1 100 01 4 100 02 1 200 01 2 110 02 2 150 02 3 100 01 3 130 02 4 200 --创建表 emp015 create table emp015(y number,q1 number,q2 number,q3 number,q4 number) insert into emp015( Y,Q1,Q2, Q3, Q4)values(01,100,110,130,100); insert into emp015( Y,Q1,Q2, Q3, Q4)values(02 ,200,150,100,200); -
--emp014 转 emp015 with aa as (select id y, max(case when q =1 then amt end) q1, max(case when q =2 then amt end) q2, max(case when q =3 then amt end) q3, max(case when q =4 then amt end) q4 from emp014 group by id) select * from aa -
--emp015 转 emp014 with aa as (select id, max(case when q=1 then amt end) as q1, max(case when q=2 then amt end) as q2, max(case when q=3 then amt end) as q3, max(case when q=4 then amt end) as q4 from emp014 group by id ) select id , 1 as q1,q1 as amt from aa union all select id , 2 as q1,q2 as amt from aa union all select id , 3 as q1,q3 as amt from aa union all select id , 4 as q1,q4 as amt from aa
-
-
伪列 rowid
- 表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址
- 使用ROWID可以快速的定位表中的某一行
- ROWID值可以唯一的标识表中的一行
DELETE FROM EMP E WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP E GROUP BY EMPNO); ROLLBACK;– 去重，删除重复数据
-
伪列rownum
- 在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。
- 通过ROWNUM伪列可以限制查询结果集中返回的行数
- rownum 必须从1开始查询
select ename,sal,deptno,rn from (select e.*,rownum rn from emp e where rownum<8) where rn >2;--查询emp表第3-7列数据
6、分析函数
-
分析函数与聚合函数不同，聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值
-
函数()over(partition by 字段 order by 字段 )
-
--求每个部门工资高于部门平均工资的员工数量 select deptno,count(1) from (SELECT E.*, AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG FROM EMP E) A where sal>avg group by deptno -
MAX(),MIN(),SUM(),AVG(),COUNT() --加了ORDER BY 是累计求值
-
排序分析函数 100 90 90 80 70
-
row_number() --派号码排序， 1 2 3 4 5
-
rank() --跳跃排序派号码 1 2 2 4 5
-
dense_rank() --不跳跃排序派号码 1 2 2 3 4
-
-- 各部门工资排名前3的员工的所有信息 select * from (select e.* ,dense_rank()over(partition by deptno order by sal desc) rn from emp e) where rn<4
-
-
聚类分析函数
-
MAX(),MIN(),SUM(),AVG(),COUNT() --加了ORDER BY 是累计求值
-
select t.*,sum(sal)over(partition by deptno order by empno desc) sum_sal from emp t; select t.*,avg(sal)over(partition by deptno) avg_sal from emp t; select t.*,max(sal)over(partition by deptno) max_sal from emp t; select t.*,min(sal)over(partition by deptno) min_sal from emp t; select t.*,count(sal)over(partition by deptno) count_sal from emp t;
-
-
最大值、最小值分析函数
-
--语法格式 MAX()/MIN() KEEP (DENSE_RANK FIRST/LAST ORDER BY ) --奖金最大、最小 select min(empno) keep(dense_rank first order by sum(NVL2(comm,comm+500,200)) desc) first, min(empno) keep(dense_rank last order by sum(NVL2(comm,comm+500,200)) desc) last from emp group by empno; --工资最大、最小 select max(empno) keep(dense_rank first order by sum(sal) desc) first, max(empno) keep(dense_rank last order by sum(sal) desc) last from emp group by empno;
-
-
按层次查询
- 平均分配，如果不能平均分配，则较小层次分配额外的行，并且各个层次能放的行数最多相差1
select t.*, ntile(5) over(order by sal desc) ntile from Emp t
-
位移分析函数
-
--LAG(取值字段,N)OVER(ORDER BY 排序字段) --N 表示步长，表示取前 N 个的结果 --LEAD(取值字段,N)OVER(ORDER BY 排序字段) --N 表示步长，表示取后 N 个的结果 select t.*, lag(sal,1,0) over(order by sal) a , lead(sal,2,1) over(order by sal) b , lag(sal,1) over(partition by deptno order by sal) as lag, lead(sal,1) over(partition by deptno order by sal) as lead from emp t;
-
五、程序块
1、语法结构
-
DECLARE V_EMPNO number; -- 声明变量 v_emp varchar2(20):='hello world'; --声明变量并赋值 v_num varchar2(20):=&请输入; --用户输入变量。 V_ENAME EMP.ename%TYPE; -- 列名%type ,指定变量的数据类型。 V_NSAL EMP%ROWTYPE; BEGIN V_EMPNO :=&请输入员工编号 ; SELECT E.ename ,12*( E.sal + NVL(E.comm，0)) INTO V_ENAME ,V_NSAL.sal FROM EMP E WHERE E.empno = V_EMPNO; IF V_NSAL.DEPTNO = 10 --IF判断 THEN V_NSAL.SAL := V_SAL1 *1.3 ; ELSIF V_NSAL.DEPTNO = 20 THEN V_NSAL.SAL := V_SAL1 *1.2 ; ELSE V_NSAL.SAL := V_SAL1 ; END IF; dbms_output.put_line(V_ENAME|| '的年薪是： '||V_NSAL.sal); EXCEPTION ----异常处理，比如输入1010编号，就会输出下面的没有这个编号 WHEN no_data_found THEN ---是否有数据 dbms_output.PUT_LINE('没有这个编号'); ----打印语句 END ;
2、循环
-
-- 一写法 DECLARE X NUMBER(5) := 1 ; Y NUMBER(5) := 0 ; BEGIN LOOP Y := X + Y; X := X +1 ; IF X >10 THEN -- 当x>10 才会退出循环 EXIT; -- 退出循环 END IF; DBMS_OUTPUT.PUT_LINE(X||' '||Y); END LOOP; DBMS_OUTPUT.PUT_LINE('1~10的累加结果为：'||Y); END ; -- 二写法 DECLARE X NUMBER(5) := 0; Y NUMBER(5) := 0; BEGIN WHILE X < 10 LOOP ---判断并做循环 X := X + 1; Y := X + Y; -- DBMS_OUTPUT.PUT_LINE(X||' '|| Y); -- DBMS_OUTPUT.PUT_LINE('RESULT IS :' || Y); END LOOP; DBMS_OUTPUT.PUT_LINE('RESULT IS :' || Y); END; -- 三写法 DECLARE X NUMBER(3) := 0; Y NUMBER := 0; BEGIN FOR X IN 1.. 10 LOOP --- 判断现在1--10之间就做循环； Y := Y + X; END LOOP; DBMS_OUTPUT.PUT_LINE('RESULT IS :' || Y); END;
3、动态SQL
-
DDL(数据定义语言,Data Definition Language)不能直接在程序块中运行，需使用动态SQL
-
Oracle中提供了Execute immediate [ɪˈmiːdiət] 语句来执行动态SQL
-
--语法 DECLARE V_SQL VARCHAR2(2000) := ''; -- V_NUM NUMBER; BEGIN --EXECUTE IMMEDIATE用法1:立刻执行 V_SQL := 'create or replace view mytest as select* from emp'; EXECUTE IMMEDIATE V_SQL; 或者 EXECUTE IMMEDIATE 'create or replace view mytest as select* from emp'; END PRO_TEST;
六、游标
1、定义及作用
-
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。作用是用于临时存储从数据库中提取的数据块。
-
指定结果集中特定行的位置 >>基于当前的结果集位置检索一行或连续的几行>>在结果集
2、显式游标的写法
DECLARE
CURSOR C_EMP -- 定义一个游标 C_EMP
IS --游标指向 一个结果集 IS 后面的查询结果集
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E
WHERE E.JOB = 'MANAGER';
V_CEMP C_EMP%ROWTYPE; -- 定义一个游标的变量
BEGIN
OPEN C_EMP; -- 打开游标
LOOP
FETCH C_EMP ---读取游标的结果集
INTO V_CEMP; -- 将游标结果集中的数据 FETCH INTO 到游标的变量中，每次变量只接收一行数据
EXIT WHEN C_EMP%NOTFOUND; -- 当游标结果集中找不到数据时，退出循环
DBMS_OUTPUT.PUT_LINE(V_CEMP.ENAME || ' ' || V_CEMP.EMPNO || ' ' ||
V_CEMP.JOB || ' ' || V_CEMP.SAL);
END LOOP;
CLOSE C_EMP; -- 关闭游标
END;
3、隐式游标的写法
DECLARE
V_JOB VARCHAR2(10) :=&输入一个岗位;
CURSOR C_EMP -- 定义一个游标 C_EMP
IS --游标指向 一个结果集 IS 后面的查询结果集
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E
WHERE E.JOB = V_JOB;
V_CEMP C_EMP%ROWTYPE; -- 定义一个游标的变量
BEGIN
FOR V_CEMP IN C_EMP LOOP -- 隐式游标，不需要打开和关闭游标，直接通过for循环遍历游标的结果集
DBMS_OUTPUT.PUT_LINE(V_CEMP.ENAME || ' ' || V_CEMP.EMPNO || ' ' ||
V_CEMP.JOB || ' ' || V_CEMP.SAL);
END LOOP;
END;
七、存储过程和自定义函数
1、区别
- 功能不同，存储过程主要用于数据同步，而自定义函数主要用于做计算
- 调用方式不同，存储过程一般使用 BEGIN END 调用，而自定义函数是放在 SELECT 语句后面调用的
- 返回值不同， 存储过程不需要返回结果，而自定义函数必须返回一个计算结果
- 定义时不同，存储过程不需要指明返回值的数据类型，自定义函数必须指定返回值的数据类型
2、创建存储过程
-
--语法 CREATE OR REPLACE PROCEDURE SP_过程名(参数1 [IN|OUT|IN OUT] 数据类型，参数2 [IN|OUT|IN OUT] 数据类型……) IS /*|AS*/ BEGIN 执行体 END ;
3、创建自定义函数
-
CREATE OR REPLACE FUNCTION FUN_函数名(参数1 数据类型，参数2[IN|OUT|IN OUT] 数据类型……) RETURN 返回的数据类型,不写长度 IS /*|AS*/ BEGIN 执行体 RETURN 结果； --里面必须要有一个RETURN子句 END ;
4、参数类型
-
IN 类型为 输入参数,默认参数，可以不写。【不能重新赋值】
-
OUT 类型为 输出参数。【可以被重新赋值，但是不会接受输入的参数】
-
IN OUT 类型为输入输出参数，既可以接受传入的值，也可以作为输出参数输出结果。【可以被重新赋值】
-
在过程或者函数中定义了参数，就必须传值
-
IN 类型的参数，不能通过变量传值，必须直接传值[可以是数字 字符 日期]
-
带了 OUT 类型的参数，必须通过变量传参，无论是 OUT 或者 IN OUT
-
OUT 类型的参数，虽然不接收传入的参数，但是跟第一条罗一样，必须给它一个变量，哪怕变量的值是空的
-
IN OUT 类型的参数，也需要通过变量传参，同时 IN OUT 类型的参数，可以像变量一样输出或者被重新赋值
5、增量抽取
-
只需抽取新增的或修改的数据。此方法性能好，但容易遗漏。目标表中有，但是源表中没有的话，更新不了。以时间戳取增量，对源表删除的数据无能为力。
-
通过源表更新目标表的时候，通常是先判断 源表中的数据在目标表中是否存在（通过主键判断），如果存在，那么就用源表的数据，更新目标表的数据，如果不存在，那么就直接从源表中插入数据到目标表。
-
-- 使用游标进行增量数据同步。 CREATE OR REPLACE PROCEDURE SP_EMP_BACK2 IS CURSOR C_EMP IS SELECT * FROM EMP; V_EMP C_EMP%ROWTYPE; V_CT NUMBER(5); V_MARK NUMBER(5); BEGIN --初始化变量 V_MARK := SEQ_ID.NEXTVAL; FOR V_EMP IN C_EMP LOOP SELECT COUNT(1) INTO V_CT FROM EMP_BACK1 WHERE EMPNO = V_EMP.EMPNO; -- 比较字段，通过主键 empno 判断员工信息是否存在于目标表 -- 判断源表中的数据在目标表中存在，存在则用源表中的数据更新目标表 --- V_MARK := SEQ_ID.NEXTVAL; 放在循环里面就会递增 IF V_CT = 1 THEN UPDATE EMP_BACK1 M SET -- 这里更新的时候，不能更新 比较字段。 M.ENAME = V_EMP.ENAME, M.JOB = V_EMP.JOB, M.MGR = V_EMP.MGR, M.HIREDATE = V_EMP.HIREDATE, M.SAL = V_EMP.SAL, M.COMM = V_EMP.COMM, M.DEPTNO = V_EMP.DEPTNO, M.DATA_DATE = SYSDATE, M.MARK = V_MARK WHERE EMPNO = V_EMP.EMPNO; -- 判断源表中的数据，在目标表中不存在，那么就插入数据 ELSIF V_CT = 0 THEN INSERT INTO EMP_BACK1 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DATA_DATE, MARK) VALUES (V_EMP.EMPNO, V_EMP.ENAME, V_EMP.JOB, V_EMP.MGR, V_EMP.HIREDATE, V_EMP.SAL, V_EMP.COMM, V_EMP.DEPTNO, SYSDATE, V_MARK); END IF; END LOOP; COMMIT; END; -
--使用 merge into 更新目标表 EMP_BACK1 CREATE OR REPLACE PROCEDURE SP_EMP_BACK3 IS BEGIN MERGE INTO EMP_BACK1 M USING (SELECT * FROM EMP) E -- 使用 （） 内的查询结果 ON (M.EMPNO = E.EMPNO) -- 通过 on ()里面的条件比较 -- 判断源表中数据在目标表中存在，则更新 WHEN MATCHED THEN -- 当 on 后面的条件比较 匹配上数据，then 更新 UPDATE SET -- 比较字段 empno 不能更新 M.ENAME = E.ENAME, M.JOB = E.JOB, M.MGR = E.MGR, M.HIREDATE = E.HIREDATE, M.SAL = E.SAL, M.COMM = E.COMM, M.DEPTNO = E.DEPTNO, M.DATA_DATE = SYSDATE, M.MARK = V_MARK -- 这里不加分号 ，分号表示程序执行到这里结束 -- 判断源表中的数据在目标表不存在，则插入 WHEN NOT MATCHED THEN -- 当 on 后面的条件比较 匹配不到数据，then 插入 INSERT (M.EMPNO, M.ENAME, M.JOB, M.MGR, M.HIREDATE, M.SAL, M.COMM, M.DEPTNO, M.DATA_DATE, M.MARK) VALUES (E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO, SYSDATE, V_MARK); COMMIT; END; --- 调用存储过程 BEGIN SP_EMP_BACK3; END ;
6、全量抽取
-
将目标表的数据全部删除，再将源系统的数据全部插入目标表。
-
此方法保证了数据的质量，但是对于数据量大的表而言，性能太差。
-
CREATE OR REPLACE PROCEDURE SP_EMP_BACK2 IS -- V_MARK NUMBER(10); BEGIN -- 初始化变量 -- V_MARK := seq_test2.nextval; -- 使用动态SQL 清空目标表，让存储过程支持重跑 /*EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_BACK1 ' ; */-- ① 删除效率比较高，但是必须使用动态SQL执行 DELETE FROM EMP_BACK2 WHERE 1 = 1; -- ② 直接使用delete 语句删除，记得加条件。 --EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_BACK1' ; INSERT INTO EMP_BACK2(empno, ename, job, mgr, hiredate, sal, comm, deptno, DATA_DATE -- MARK ) SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, SYSDATE --V_MARK FROM EMP_BACK1 ; COMMIT; END ;
九、包
1、创建包头
-
CREATE OR REPLACE PACKAGE PK_TEST1 IS procedure sp_emp(p_sal1 in number,p_sal2 in out number); END PK_TEST1;
2、创建包体
-
CREATE OR REPLACE PACKAGE BODY PK_TEST1 IS procedure sp_emp(p_sal1 in number,p_sal2 in out number) is v_emp emp%rowtype; begin select ename,sal,hiredate into v_emp.ename,v_emp.sal,v_emp.hiredate from emp where sal>p_sal1 and sal<=p_sal2; dbms_output.put_line('员工姓名:'||v_emp.ename||' 工资:'||v_emp.sal|| ' 入职日期:'||to_char(v_emp.hiredate,'yyyy/mm/dd')); end; END PK_TEST1;
扫一扫
762
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
