数据泵&程序块&变量&循环&异常报错&存储过程&游标&视图&分区&拉链表_拆解

Joan月

已于 2024-02-19 16:05:57 修改

阅读量316

点赞数 1

文章标签：数据仓库数据库

于 2023-03-20 16:24:21 首次发布

本文链接：https://blog.csdn.net/migina_2003/article/details/129670301

版权

数仓

-- 数仓概念
大数据下的数据仓库对数据进行了分层管理，分为ODS、DWD，DWS，DM层
相同点：
每一层都有单独的职责;降低复杂度;减少重复开发，提高复用性
不同点:
程序中的分层是为了关注点分离;
数据的分层更像是一个萃取的过程，数据由低价值到高价值转变.

-- 数仓算法

数据库补充点

-- 创建用户设置密码
create user ods identified by 123456;	-- 相当于创建ods数据库
-- 增加连接权限，和资源权限
grant connect,resource to ods
-- 解锁scott用户
alter user scott account unlock ;
-- 修改密码
alter user scott identified by 123456;

-- 复制表结构 以及 表数据
create table emp1 as select * from emp
-- 复制表结构 并且 不复制表数据
create table emp1 as select * from emp 1=2

-- 删除用户		【注意：必须是管理员权限，比如system】
drop user ods1;
-- 报错提示必须指定cascade删除   解决方法如下👇
drop user ods cascade

1.数据泵

2.程序块

DML 数据操作语言

-- 用户数据库的基本操作   指：基本的“增删改查”操作
-- 且DML操作是可以手动控制事务的开启、提交和回滚的
   
-- 手工（依靠手工）
for update （pl/sql小锁 解锁可以编辑修改）
例：select * from emp for update
提交commit	回滚rollback
-- 自动（依靠代码）
不基于原表：增加insert	删除delete	修改update
基于原表： 并入merge

-- 复制stu表给stu01（内容以及结构）
create table stu01 as select * from stu
-- 多条插入（必须保证stu的表结构 顺序和stu01一样）
insert into stu01 select * from stu

DDL 数据定义语言

-- 用于定义和管理SQL数据库中的所有对象的语言,对数据库中的某些对象(database,table)进行管理。
-- 包括的关键字有：create、alter、drop、truncate、comment、grant、revoke
-- DDL操作是隐性提交的，不能rollback

-- alter使用
-- 增加字段
alter table emp add(tname varchar(20))
-- 删除字段 （慎用！）没有回滚！
alter table emp drop (deptno)
-- 修改	（改大字符集可以，改小需要注意表中数据，有数据不能修改类型）
alter table emp modify (tname varchar(40))

打印输出基础

-- 注释： 【-- 单行注释】  【/* */ 多行注释】

begin	-- 程序开始部分
	dbms_output.put_line();   -- 打印输出
    dbms_output.put();        -- 缓存不换行
    dbms_output.new_line();   -- 显示缓存
end;	-- 程序结束部分
【注意：缓存不换行指的是存在缓存里面，不会显示，必须使用new_line()来显示缓存】

DML程序块

begin
  -- 增加
  insert into stu01 (sname) values('张三1');
  -- 删除
  delete from stu01 where sid_1 in ('05','07','09');
  -- 修改
  update stu01 set sid_1 = 01; 
  commit;
end;

DDL程序块

-- 执行非ddl操作 需要在程序块中加上：execute immediate 

-- 清空表
begin
  execute immediate 'truncate table stu01';
end;

-- 创建a1表
begin
    execute immediate 'create table a1(id int)';
end;

-- 创建a2表，并且删除表
begin
    execute immediate 'create table a2(id int)';
    execute immediate 'delete from a2';
end;
【注意：当程序块中同时存在ddl和dml语句时，都需要加上execute immediate】

题目

-- 要求：用程序块写创建表不少于三个字段并插入两条数据，在删除其中的一条数据
begin
   -- 创建表
   execute immediate 'create table demo(d_id int,d_name varchar(20),d_desc VARCHAR(40))';
   dbms_output.put_line('创建成功');
   -- 插入两条数据
   execute immediate 'insert into demo values(1,''张三'',''今年20岁'')';
   execute immediate 'insert into demo values(2,''李四'',''今年20岁'')';
   dbms_output.put_line('成功插入两条数据'); 
   -- 删除其中的一条数据
   execute immediate 'delete from demo where id = 1';
   dbms_output.put_line('删除成功'); 
   commit;
end;

-- 报错时因为单引号''    参考以下👇
select ' '' '' ' from dual


-- 要求：用程序块写出替换两个类型不同的字段下的内容
begin
  --增加两个新字段
  execute immediate 'alter table aaa add(id_a varchar2(20),a_name number(4))';
  --复制字段并清空原字段
  execute immediate 'update aaa set a_name = id,id_a = aname,id = null , aname = null';
  --类型修改
  execute immediate 'alter table aaa modify (id varchar2(20),aname number(4))';
  --给原字段赋值
  execute immediate 'update aaa set id = id_a, aname = a_name';
  --删除字段
  execute immediate 'alter table aaa drop (id_a,a_name)';
end;
-- 另一种写法：
create table stu1(sid int,sname VARCHAR(20)); 
INSERT INTO stu1 values(1001,'张三');
begin
  execute immediate 'ALTER TABLE stu1 add(sid1 int,sname1 VARCHAR(20))';
  execute immediate 'UPDATE stu1 set sid1=sid,sname1=sname';
  execute immediate 'UPDATE stu1 set sid=null,sname=null';

  execute immediate 'ALTER table stu1 MODIFY(sid VARCHAR(20),sname int)';
  execute immediate 'update stu1 set sid=sname1,sname=sid1';
  execute immediate 'ALTER table stu1 drop(sid1,sname1)';
end;

3.变量

设置变量类型

-- 声明部分  -- 设置变量类型 
declare
a varchar2(20) := '张三';  	
b number(20,2) := 12344.1249; 
c date := sysdate;
begin
  dbms_output.put_line(a);
  dbms_output.put_line(b); 
  dbms_output.put_line(c); 
end;

type类型

-- type类型  根据表内的字段类型变化变量类型
declare
  a  stu.sname%type := '张三';	-- 取 stu 表中的sname字段类型 varchar(20)
  b  stu.sid_1%type := 5 ;  	  -- 取 stu 表中的sid_1字段类型 int
begin
  dbms_output.put_line(a); 
  dbms_output.put_line(b); 
end;

ROWTYPE类型

-- %ROWTYPE
-- 表示该类型为行数据类型，存储的时候为一行数据，一行有很多列，相当于表中的一行数据，也可以的游标中的一行数据。
-- 用到%ROWTYPE作用是：当查询整行的时候
declare 
  tt stu%rowtype ;
begin
  dbms_output.put_line(tt.ssex); 
end;

定义变量类型

-- 例：定义变量类型
declare
  a varchar2(100);
begin
  a := 'insert into stu values (''七七'',to_date(sysdate,''yyyy-mm-dd''),''女'',1)';
  dbms_output.put_line(a);
  -- execute immediate a;
  commit;
end;

取数据字段

-- 取字段数据
declare 
  a int;
  b varchar(20);
begin
  select tid,tname into a,b from table1 where tid = 1001;
  dbms_output.put_line('编号:'||a||' 姓名:'||b); 
end;

-- 取字段数据 并 赋值
declare 
  a int;
  b varchar(20);
  c varchar(20);
begin
  select tid,tname into a,b from table1 where tid = 1001;
  c := a||' '||b;
  dbms_output.put_line(c);
  c := a;
  dbms_output.put_line(c);
end;

变量值可以被改变

-- 变量值可以被改变
declare
  a varchar2(30);
begin
  a:= '今天天气真好';
  dbms_output.put_line(a);
  a:= '今天天气还可以';
  dbms_output.put_line(a); 
end;

-- 手动输入变量值	string类型
declare 
a varchar2(30):= '&姓名';
begin
  dbms_output.put_line(a); 
end;


-- 手动输入变量值	int类型
declare 
  a int:= &年龄;
begin
  dbms_output.put_line(a); 
end;

题目

-- 声明一个变量1 里面写员工姓名
-- 声明一个变量2 用来组成删除员工姓名的sql
-- execute 执行变量2
declare
	a varchar2(20); 
	b varchar2(50);
begin
  a := upper('&姓名');	-- 转大写
  b := 'delete from stu where upper(sname) = '||''''||a||'''';
  dbms_output.put_line(a); 
  execute immediate b;
  commit;
end;
-- 【注意：数据库中两个单引号表示一个引号；同理：所以需要写4个单引号才会显示1个引号】
-- 【 select ' '' ' from dual 】

-- 手动输入一个部门数值薪资增加150%，并打印输出10号部门，20号部门，30号部门的人数以及薪资
-- 声明 并 使用变量
declare
	deptno_no number      := &输入一个部门编码;
	sql_a     varchar2(50):= 'update emp set sal = sal*1.5 where deptno 	='||deptno_no;
	dp      number;
	dpcount number;
begin
  --dbms_output.put_line(sql_a); 
  execute immediate sql_a;
  commit;
  select count(1),sum(sal) into dp ,dpcount from emp where deptno = 10;
  dbms_output.put_line('10号部门 '||dp||' 10号部门薪资 '||dpcount); 
  select count(1),sum(sal) into dp ,dpcount from emp where deptno = 20;
  dbms_output.put_line('20号部门 '||dp||' 20号部门薪资 '||dpcount); 
  select count(1),sum(sal) into dp ,dpcount from emp where deptno = 30;
  dbms_output.put_line('30号部门 '||dp||' 30号部门薪资 '||dpcount); 
  end;

update emp set sal = sal*1.5 where deptno = deptno_no

---解锁scott用户
alter user scott account unlock ;
---修改密码
alter user scott identified by 123456;

4.IF判断

-- if语法：
if  条件   then  代码1
elsif 条件 then 代码2
else 代码3
end if;

题目

-- 输入一个编号，如果emp表内有这个人这打印姓名;
declare
   s_id number := &输入一个编号;
   s_count number;    -- 统计是否有学生过
begin
  select count(1) into s_count from table01 where tid = s_id;
  if s_count>0 then
    dbms_output.put_line(s_id||'员工存在');
  else
    dbms_output.put_line(s_id||'员工不存在');
  end if;
end;

-- 输入三个值返回最大值
declare
  a number := &输入第一个最大值;
  b number := &输入第二个最大值;
  c number := &输入第三个最大值;
begin
  if a>b and a>c then 
    dbms_output.put_line('最大值：'||a);
  elsif b>a and b>c then 
    dbms_output.put_line('最大值：'||b);
  elsif c>a and c>b then 
    dbms_output.put_line('最大值：'||c);
  end if;
end;

-- 输入一个数判断奇术还是偶数
declare
  a number := &输入一个数;
begin
  if mod(a,2)=0 then 
    dbms_output.put_line('偶数');
  else 
    dbms_output.put_line('奇数');
  end if;
end;

-- 输入一个年份判断平年还是闰年
declare
  a number := &年份;
begin
  if mod(a,4)=0 and mod(a,100)<>0 then
     dbms_output.put_line('闰年');
  elsif mod(a,400)=0 then
     dbms_output.put_line('闰年');
  else 
     dbms_output.put_line('平年');
  end if;
end;

-- 输入emp的一个empno值查询出这个人存不存在，如果不存在则插入数据，如果存在则打印存在并把对应的薪资更新成20000
declare
   e_id number := &输入编号;
   e_count number;  -- 返回行数
   e_sal number;    -- 薪资
begin
  select count(1) into e_count from emp where dempno = e_id;
  if e_count=0 then 
    dbms_output.put_line('不存在，开始插入数据');
    insert into emp values(e_id,'赵六',8000);
  else
    dbms_output.put_line('存在，开始修改薪资');
    update emp set sal=20000 where dempno = e_id;
  end if;
end;

-- 输入一个部门返回其部门的总人数，平均薪资，最高，最低，换行打印

-- 当需要恢复EMP表时，运行该存储过程，可以直接恢复成原本的样子
-- 数据多了 少了 改了 
-- 表结构 列多了，变了

-- 输入一个MIN，AVG,MAX,COUNT无论大小写都要把emp的sal值打印出来

5.循环

loop

-- loop循环语法：
loop 循环开始点
	循环体
exit when 退出循环点
end loop 循环结束点

-- 打印1-10
declare
    a number := 0;
begin
    loop 
      a := a+1;
      dbms_output.put_line(a);
     exit when a=10;
     end loop;
end;

-- 打印1到10相加的结果
declare
  a number:=0;
  b number:=0;
begin
  loop
    a:=a + 1;
    b:=a + b;
    dbms_output.put_line(b); 
  exit when a=10;
  end loop;
end;

while

-- while循环语法：
while 条件 loop
       循环体语句
end  loop;

-- 用wilhe 打印1到10
declare
   a number := 0;
begin
  while a<10 loop
    a := a+1;
    dbms_output.put_line(a);
  end loop;
end;

for

-- for循环语法：
for 循环变量 in 循环次数 loop
    循环体
end loop;

-- for打印1-10
begin
  for t in 1..10 loop
    dbms_output.put_line(t);
  end loop;
end;
----------------------------------------------------------------------
declare 
  a number:=0;
begin
  for t in 1..9 loop 
    dbms_output.put_line('-------'); 
    for i in 1..t loop
        a:= i+1;
          dbms_output.put_line(a); 
    end loop;
  end loop;
end;

--打印1 到 10 直接的奇术

--打印2的倍数打印5次（包括2）

--打印99乘法表如（1+1=1）还原99乘法表

--用*号打印正金子塔

--用* 号打印空心金字塔

6.异常报错

-- 异常语法
declare 
	
begin
	
exception  when 错误1 then 代码1
           when 错误2 then 代码2
           ...
           when others then    
end;


-- 异常弹窗
raise_application_error(-20999,v_ename||'该降薪了');
-- 万能写法
dbms_output.put_line(sqlerrm||sqlcode);
sqlerrm 	-- 错误信息
sqlcode		-- 错误编码

-- 返回行数 
sql%count

-- 异常统计
https://blog.csdn.net/nanyangnongye/article/details/124744388

-- 常见错误信息
ACCESS_INTO_NULL 未定义对象
CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 
COLLECTION_IS_NULL 集合元素未初始化
CURSER_ALREADY_OPEN 游标已经打开 
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值 
INVALID_CURSOR 在不合法的游标上进行操作 
INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的元素时 
TOO_MANY_ROWS 执行 select into 时，结果集超过一行 
ZERO_DIVIDE 除数为 0 
SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 
VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 
VARRAY 时，将下标指定为负数 
VALUE_ERROR 赋值时，变量长度不足以容纳实际数据 
LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 
NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 
PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 
ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 
SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法 
STORAGE_ERROR 运行 PL/SQL 时，超出内存空间 
SYS_INVALID_ID 无效的 ROWID 字符串 
TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时

预定义异常

-- 测试报错收集
declare
   e_name varchar2(20);
begin
  select ename into e_name from emp where sal = '&薪资';
  dbms_output.put_line(e_name);
  exception when NO_DATA_FOUND then
                 dbms_output.put_line('错误信息：未初始化的元素时');
            when TOO_MANY_ROWS then 
                 dbms_output.put_line('错误信息：结果集超过一行');
            when others then
                 dbms_output.put_line('错误信息：未知错误');
end;

非预定义异常

-- ORA-00001违反唯一约束条件
declare
   a exception;
   pragma exception_init(a,-00001);     -- 把错误信息值赋给a
begin
   insert into emp(dempno) values(1001);
   exception when a then
      dbms_output.put_line('违反唯一约束条件');
end;

自定义异常

-- raise返回异常
-- 薪资大于4000返回报错信息
declare
   a exception;
   e_ename varchar2(20);
   e_sal number;
begin
  select ename,sal into e_ename,e_sal from emp where dempno = &编号;
  -- 判断薪资是否大于4000
  if e_sal > 4000 then
    raise a; -- 返回到a参数异常
  else 
    dbms_output.put_line(e_sal);
  end if;
  -- 异常处理
  exception when a then
    dbms_output.put_line(e_ename||'该降薪了');
            when others then
    dbms_output.put_line(e_ename||e_sal);
end;

题目1

在这里插入图片描述

-- 先创建一个日志表，程序写入其中包括不管成功与否都有插入
-- 报错信息，报错id，执行时间，结束时间，程序执行是否成功，插入数据条数
create table logs(
       l_name varchar2(255),    -- 报错信息
       l_id number,             -- 报错id
       l_stime date,            -- 开始时间
       l_etime date,            -- 结束时间
       l_result varchar2(20),   -- 是否插入成功
       l_rows number            -- 插入条数
);

declare
  a exception;    -- 定义一个异常信息
  b number;       -- 员工薪资(用来if判断)      
  c date;         -- 开始时间
  d date;         -- 结束时间
  e number;       -- 插入条数（返回行数）
  i varchar2(50); -- 错误信息
  j number;       -- 错误编码
begin
  c := sysdate;   -- 开始时间
  select sal into b from emp where dempno = &员工编号;  -- 输入员工编号查薪资
  -- if判断
  if b > 3000 then
    d := sysdate; -- 结束时间
    insert into logs(l_stime,l_etime,l_result) values(c,d,'执行成功');
  else
    raise a;  
  end if;
  commit;  -- 提交【要么全部提交，要么全部回滚】
  -- 异常报错
  exception when a then
    rollback;    -- 回滚
    e := sql%rowcount;  -- 返回行数
    d := sysdate;
    insert into logs(l_name,l_id,l_stime,l_etime,l_result,l_rows) values('该涨薪了',-20022,c,d,'执行失败',e);
    commit;     -- 提交事务
  when others then
    rollback;   -- 回滚
    dbms_output.put_line(sqlerrm||sqlcode);
    d := sysdate;
    i := sqlerrm;
    j := sqlcode;
    e := sql%rowcount;
    insert into logs(l_name,l_id,l_stime,l_etime,l_result,l_rows) values(i,j,c,d,'执行失败',e);
    commit;     -- 提交事务
end;

题目2

-- 定制异常插入一条数据时如果薪资5000超出范围弹窗报错，删除20号部门的数据也要进行报错提示
declare
  a exception;
  b number := &输入员工部门;
  c varchar(20) := '&输入员工姓名';
  d number := &输入员工薪资;
  e number := &输入需要删除员工部门;
begin
  if d > 5000 then      -- 薪资5000超出弹窗报错
     raise_application_error(-20222,d||'超过5000');
  elsif e = 20 then    -- 删除20号部门报错提示
     raise_application_error(-20211,e||'部门不能删除');   
  else
     insert into emp values(b,c,d);    -- 正常插入数据
     delete from emp where dempno = e; -- 正常删除
  end if;
end;

题目3

-- 在创建一个异常信息表如果程序中有删除数据时如果有异常报错要把异常插入到信息表内
-- 程序执行时间，结束时间，异常信息，异常code
create table err(
       stime date,
       etime date,
       errm varchar2(255),
       errcode number
);

declare
    a exception;
    b number := &要删除的编号;
    c number;   -- 薪资（用来判断）
    d date;     -- 开始时间
    e date;     -- 结束时间
    i varchar2(255);  -- 异常信息
    j number;         -- 异常code
begin
    d := sysdate; -- 开始时间
    select sal into c from emp where dempno = b;
    -- 薪资判断
    if c > 5000 then
      raise a;
    else
      delete from emp where dempno = b;
      dbms_output.put_line('已成功删除该员工信息');
    end if;
    -- 异常处理
    exception when a then
      select sysdate into e from dual;
      -- i := '该员工薪资过高';
      -- j := -20033;
      i := sqlerrm;
      j := sqlcode;
      insert into err(stime,etime,errm,errcode) values(d,e,i,j);
      dbms_output.put_line('报错信息收集并存入日志表');
    when others then
      select sysdate into e from dual;
      i := sqlerrm;
      j := sqlcode;
      insert into err(stime,etime,errm,errcode) values(d,e,i,j);
end;

7.约束

-- 主表
primary key	-- 主键约束：MySQL中自增AUTO_INCREMENT
unique		-- 唯一约束：字段必须是唯一值
not null 	-- 非空约束
check		-- 检查约束： sex varchar(20) check (sex in('男','女')) 
-- 子表
foreign key 子表字段 references 主表名称(主表外键字段)
-- constraint 外键名称 foreign key 本表字段名 references 主表外键名称(外表字段) REFERENCES emp_k(k_id)

8.存储过程

-- 存储过程可以不使用参数，也可以带一个或者多个参数
create or replace procedure 存储名称(可以写传入变量) is/as 
   变量参数
begin
   程序入口
end;

-------------------------------------------------------

-- 创建存储过程 	【默认是in传入数据  类型不可以指定长度】
create or replace procedure emp_in(eid in number) is
   ecount number(20);	-- 声明变量
begin
   select count(1) into ecount from emp where dempno = eid;		-- 根据输入的员工id查询总个数
   dbms_output.put_line(ecount);
end;
-- 执行存储过程 【因为存储过程中带参数，所以调用存储过程也需要参数】
call emp_in(20)

-------------------------------------------------------

-- 创建存储过程	【out返回数据  类型不可以指定长度】
create or replace procedure emp_out(eid out number) is
   ecount number(20);
begin
   eid := 10;     -- out模式定义的参数只能在过程体内部赋值
   select count(1) into ecount from emp where dempno = eid;
   dbms_output.put_line(ecount);
end;
-- 执行存储过程   
declare
  e_result number(32);  -- 定义一个和返回值相同的参数类型（用于接收out赋值的参数）
begin
  emp_out(e_result);    -- 调用存储过程
end;

存储过程日志表

-- 存储过程日志表
create or replace procedure sp_log_tab(p_sp_name   varchar2,
                                       p_sp_status varchar2,
                                       p_s_date    date,
                                       p_f_date    date,
                                       p_sp_desc   varchar2,
                                       p_sp_sqlcode varchar2,
                                       p_sp_sqlerrm varchar2
                                       ) -- 字段相当于常量

is
	-- 定义变量
begin
  insert into log_tab(log_id,
                      sp_name,
                      sp_status,
                      s_date,
                      f_date,
                      sp_desc,
                      sp_sqlcode,
                      sp_sqlerrm)
               values(sys_guid(),  -- 32不重复随机id
                      p_sp_name,
                      p_sp_status,
                      p_s_date,
                      p_f_date,
                      p_sp_desc,
                      p_sp_sqlcode,
                      p_sp_sqlerrm);
  commit;
end;


create or replace procedure text_sz(dept_id in varchar2) 
is
  ccount number:=0;
begin
  insert into emp1 select * from emp where deptno = dept_id;
  ccount := sql%rowcount+ccount;
  insert into emp1 select * from emp where deptno = dept_id;
  ccount := sql%rowcount+ccount;
  dbms_output.put_line(ccount);
  sp_log_tab ();
  end; 
  
  
call text_sz(20)

题目1

-- 设置一个有in和out的存储过程
create or replace procedure p_text_emp(
   s_no in number,    
   s_name out varchar2,
   s_sal number
) as
  total number := 0;   -- 声明变量
begin
  s_name := 'aaa';
  select count(1)into total from emp where sal = s_sal;
  dbms_output.put_line('薪资相等有'||total||'人');
end;

-- 调用out返回值参数
declare
   v_name varchar2(20);    -- 声明变量（作为返回out的结果）
begin
   p_text_emp(10,v_name,1000);
   dbms_output.put_line(v_name);
end;

题目2

-- 创建日志表和日志存储过程，在编写一个存储过程用于执行插入或者删除，该存储过程如果成功执行，日志表中要存在成功信息，如果失败则存储失败信息并且遵循事务的基本原则，要么全部提交要么全部回滚

-- 创建日志表 
create table log_test(
       lid number primary key,
       errm varchar2(20),
       errcode varchar2(20),
       sdate date,
       edate date,
       lcount number
);
select * from log_test;


-- 创建存储过程
create or replace procedure p_log_text(
       p_lid number,
       p_errm varchar2,
       p_errcode varchar2,
       p_sdate date,
       p_edate date,
       p_lcount number
)as
begin
  insert into log_test(lid,errm,errcode,sdate,edate,lcount) values(p_lid,p_errm,p_errcode,p_sdate,p_edate,p_lcount);
  commit;
end;


select * from log_test;	-- 日志表 
select * from emp1;		-- emp1表

-- 开始写存储过程
create or replace procedure p_log_text1(pid in number) as
a exception;
l_count number := 0; 
l_sdate date;
l_edate date;
l_id number;
l_errm varchar2(50);
l_errcode varchar2(50);
begin
  l_sdate := sysdate;   -- 记录程序开始时间
  select count(1) into l_count from log_test;  -- 查询日志表有没有第一条数据
  -- 判断日志表lid的值
  if l_id=0 then
    l_id := 1;
  else
    select max(lid)+1 into l_id from log_test; --取最大值加1
  end if;
  -- 程序核心：执行插入
  insert into emp1 select * from emp where dempno = 10;
  l_count := sql%rowcount + l_count; -- 插入记录行数
  insert into emp1 select * from emp where dempno = pid;
  l_count := sql%rowcount + l_count;
  l_edate := sysdate;     -- 记录程序结束时间
  commit;
  p_log_text(l_id,'无','无',l_sdate,l_edate,l_count);    -- 成功时调用
  exception when a then
    rollback;    
    l_errm := sqlerrm;    -- 错误信息
    l_errcode := sqlcode; -- 错误编码
    l_edate := sysdate;   -- 记录程序结束时间
    p_log_text(l_id,l_errcode,l_errm,l_sdate,l_edate,l_count); -- 失败时调用
end;

-- 调用
call p_log_text1(10);

题目3

-- 定义一个存储过程输入参数是表名，查询这个表被多少个存储过程使用
create or replace procedure check_table(table_name in varchar) 
is
cursor ck_table is (select * from user_source);
begin
  for ck_name in ck_table loop
    if instr(upper(ck_name.text),upper(table_name)) > 0  then
    dbms_output.put_line(ck_name.name);
    end if;
  end loop;
end check_table;

begin
 check_table('emp');
 end;

9.游标

-- 声明游标
cursor 游标名(游标输入参数)
is select语句

-- 打开游标
open 游标名(参数)  -- 参数如果为空，则默认参数

-- 读取游标
fetch cur_name into {variablel}   -- variablel 一个变量列表或'记录'变量  
fetch ...into -- 指向下一行

-- 退出游标
exit when cur_a%notfound;   

-- 关闭游标
close 游标名

参考示例

-- 声明10号部门人的姓名并打印出来
declare
  -- 声明游标
  cursor cur_a is(select ename from emp where dempno = 10); 	-- sql语句加括号
  -- 声明变量
  v_name varchar2(30);   
begin
  -- 打开游标
  open cur_a;
  -- loop循环
  loop
    fetch cur_a into v_name;	-- 读取游标:循环抓取ename的值赋给v_name   
    exit when cur_a%notfound;   -- 退出游标
    dbms_output.put_line(v_name);
  end loop;
  -- 关闭游标
  close cur_a; 
end;


-- 声明10号部门人的姓名并打印出来 并且 删除名字bbb的记录
declare
  cursor cur_a is(select ename from emp where dempno = 10);   -- 声明游标
  v_name varchar2(30);  -- 声明变量
begin
  open cur_a;   -- 打开游标
  loop
    fetch cur_a into v_name;    -- 读取并赋值
    exit when cur_a%notfound;   -- 退出游标
    if v_name = 'bbb' then
       delete from emp where ename = v_name;
       commit;
    else 
       dbms_output.put_line(v_name);
    end if;
   end loop;
  close cur_a; -- 关闭游标
end;

-- 使用for循环遍历游标的好处：1.不用声明额外的变量，2.不用打开和关闭游标，3.写法简单。

-- 游标for循环 
declare
 -- 定义游标（选择对应的sql）
 cursor outfor is (select * from emp);
begin
  -- 定义一个循环去循环打印游标
  for v in outfor loop
    -- 打印对应的值
    dbms_output.put_line(v.dempno||' '||v.ename);
  end loop;
end;

隐式游标

-- 显示游标：需要声明变量 调用变量
-- 隐式游标：没有定义游标

begin
	-- 遍历隐式游标中的记录
	for emp_record in (select eid,ename,esal from emp where eid = 20);
	loop
		dbms_output.put_line(emp_record.eid);	-- 输出员工编号
		dbms_output.put_line(emp_record.ename);	-- 输出员工姓名
		dbms_output.put_line(emp_record.esal	-- 输出员工薪资
	end loop;
end;

题目1

-- 动输入一个部门编码并打印其对应的员工名称
declare
   cursor cur is (select * from emp where deptno = &部门编号);
begin
  dbms_output.put_line('编号'||' '||'姓名'||' '||'薪资');
  dbms_output.put_line('');
  for e in cur loop
    dbms_output.put_line(e.eid||' '||e.ename||' '||e.esal);
  end loop;
end;

select * from emp


-- 另一种写法：添加序号
declare
   cursor cur is (select * from emp where deptno = &部门编号);
   v_number number := 1;
begin
  dbms_output.put_line('序号'||' '||'编号'||' '||'姓名'||' '||'薪资');
  dbms_output.put_line('');
  for e in cur loop
    dbms_output.put_line(v_number||' '||e.eid||' '||e.ename||' '||e.esal);
    v_number := v_number + 1;
  end loop;
end;

题目2

-- 输出20号和30号部门的薪资和姓名，如果薪资小于3000的则进行姓名加工如：（王五  》 王*）
-- 如果薪资大于5000则输出报错
declare
   cursor cur is (select * from emp where deptno in (20,30));
begin
   dbms_output.put_line('编号'||' '||'姓名'||' '||'详细信息');
   dbms_output.put_line('');
   for e in cur loop
     if e.esal < 3000 then
       dbms_output.put_line(rpad(substr(e.ename,1,1),length(e.ename)+1,'*')||' '||e.esal||' '||'姓名加工');
     elsif e.esal > 5000 then
       dbms_output.put_line(e.ename||' '||e.esal||' '||'输出报错');
     else
       dbms_output.put_line(e.ename||' '||e.esal);
     end if;
   end loop;
end;

select rpad(substr(ename,1,1),length(ename)+1,'*') from emp;

题目3

-- 手动输入一个部门名称并打印姓名，工龄，薪资，如果工龄大于10年则薪资翻倍，工龄小于一年则删除员工信息
-- 其余正常打印
declare
   cursor cur is (select * from emp1 where deptno = &部门编号);
begin
   for e in cur loop
     if add_months(sysdate,-120) > e.eyear then
       update emp1 set esal = esal*2 where deptno = e.deptno;
     elsif add_months(sysdate,-120) > e.eyear then
       delete from emp1 where deptno = e.deptno;
     else
       dbms_output.put_line(e.ename);
     end if;
   end loop;
end;


create table emp1 as select * from emp
select * from emp1 for update
alter table emp1 add (eyear date)
select sysdate from dual

10.视图

create view v_emp as
       select * from emp where dempno = 10;
with check option;   -- 检查【可读写视图】
with read only;      -- 只读

-- 视图不占物理内存，但消耗内存
select * from v_emp；
insert into v_emp(eid,ename,dempno) values(111,'张三',30); -- 可以插入语句，前提创建视图的时候不要运行检查with check option或只读with read only语句 【因为部门是30 不满足v_emp里面的20条件】

-- 数据字典（查看视图）
select * from user_views;

物化视图

11.表空间

-- 表空间【存储位置】
-- 创建表空间
create tablespace data_zqy datafile 'e:\data_zqy.dbf' size 20M;

-- 在指定表空间下创建用户
create user 用户名 identified by 密码 default tablespace 表空间表;

-- 赋权语句 grant
-- 把connect,resource权限授予study用户
grant connect,resource to zqy;
-- 把dba权限授予给study
grant dba to study;

在这里插入图片描述

12.分区

范围分区

-- 范围分区
-- 就是根据数据库表中某一个字段的值的范围来划分分区
create table score(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade number
)
partition by range(grade)(
  partition t1 values less than(60),      -- 不及格 成绩小于60
  partition t2 values less than (85),     -- 及格   成绩在60-85之间
  partition t3 values less than(maxvalue) -- 最大值  成绩大于85
)

select * from score
-- 插入测试数据
insert into score values('511601','魁','229',92);
insert into score values('511602','凯','229',62);
insert into score values('511603','东','229',26);
insert into score values('511604','亮','228',77);
insert into score values('511605','敬','228',47);
insert into score(sno,sname,dormitory) values('511606','峰','228');
insert into score values('511607','明','240',90);
insert into score values('511608','楠','240',100);
insert into score values('511609','涛','240',67);
insert into score values('511610','博','240',75);
insert into score values('511611','铮','240',60);
insert into score values('511611','铮','222',60);


select * from score where grade < 30 or grade is null
-- 查询分区表
select * from score partition (t1);  
select * from score partition (t2);
select * from score partition (t3);
-- 说明：数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

散列分区

-- 散列分区    （基本不用）
-- 散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。【可以理解为：数据量越大分的约平均，数据量越小分的不平均】
create table score(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by hash(sno)(
  partition p1,
  partition p2,
  partition p3
);

-- 查询分区表
select * from score partition (p1);
select * from score partition (p2);
select * from score partition (p3);
-- 说明：散列分区即为哈希分区，Oracle采用哈希码技术分区，具体分区如何由Oracle说的算。

列表分区

-- 列表分区
-- 明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
create table score(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by list(dormitory)(
  partition d229 values('229'),
  partition d228 values('228'),
  partition d240 values('240'),
  partition max1 values(else),
)

-- 查询分区表
select * from score partition (d229);
select * from score partition (d228);
select * from score partition (d240);

复合分区

-- 复合分区
-- 首先讲范围-散列分区。先声明一下：列表分区不支持多列，但是范围分区和哈希分区支持多列。
create table score(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by range(grade)
subpartition by hash(sno,sname)(
  partition p1 values less than(75)(
               subpartition sp1,subpartition sp2
  ),
  partition p2 values less than(maxvalue)(
               subpartition sp3,subpartition sp4
  )
);

-- 以grade划分范围，然后以sno和sname划分散列分区，当数据量大的时候散列分区则趋于“平均”。

-- 插入数据
insert into score values('511601','魁','229',92);
insert into score values('511602','凯','229',62);
insert into score values('511603','东','229',26);
insert into score values('511604','亮','228',77);
insert into score values('511605','敬','228',47);
insert into score(sno,sname,dormitory) values('511606','峰','228');
insert into score values('511607','明','240',90);
insert into score values('511608','楠','240',100);
insert into score values('511609','涛','240',67);
insert into score values('511610','博','240',75);
insert into score values('511611','铮','240',60);
insert into score values('511612','狸','244',72);
insert into score values('511613','杰','244',88);
insert into score values('511614','萎','244',19);
insert into score values('511615','猥','244',65);
insert into score values('511616','丹','244',59);
insert into score values('511617','靳','244',95);



select * from score partition(p1);
select * from score partition(p2);
select * from score subpartition(sp1);
select * from score subpartition(sp2);
select * from score subpartition(sp3);
select * from score subpartition(sp4);
-- 当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡

复合分区列值分区

-- 复合分区 列值分区
create table list_range(
  ACCT_MONTH VARCHAR2(6),   -- 帐期 格式：年月 YYYYMM
  AREA_NO VARCHAR2(10),     -- 地域号码 
  DAY_ID VARCHAR2(2),       -- 本月中的第几天 格式 DD
  SUBSCRBID VARCHAR2(20),   -- 用户标识 
  SVCNUM VARCHAR2(30)       -- 手机号码
)
partition by range(ACCT_MONTH,AREA_NO) 
subpartition by list(DAY_ID)(
  partition p1 values less than('200705','012')(
    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p2 values less than('200709','014')(
    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p3 values less than('200801','016')(
    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')
  )
)

insert into list_range values('200705','011','04','------','13800000000');
-- 插入数据
insert into list_range values('200701','010','04','ghk001','13800000000');
insert into list_range values('200702','015','12','myx001','13633330000');
insert into list_range values('200703','015','24','hjd001','13300000000');
insert into list_range values('200704','010','04','ghk001','13800000000');
insert into list_range values('200705','010','04','ghk001','13800000000');
insert into list_range values('200705','011','18','sxl001','13222000000');
insert into list_range values('200706','011','21','sxl001','13222000000');
insert into list_range values('200706','012','11','tgg001','13800044400');
insert into list_range values('200707','010','04','ghk001','13800000000');
insert into list_range values('200708','012','24','tgg001','13800044400');
insert into list_range values('200709','014','29','zjj001','13100000000');
insert into list_range values('200710','014','29','zjj001','13100000000');
insert into list_range values('200711','014','29','zjj001','13100000000');
insert into list_range values('200711','013','30','wgc001','13444000000');
insert into list_range values('200712','013','30','wgc001','13444000000');
insert into list_range values('200712','010','30','ghk001','13800000000');
insert into list_range values('200801','015','22','myx001','13633330000');

-- 查看分区表
select * from list_range subpartition(xiaxun1);
select * from list_range subpartition(xiaxun2);
-- 范围分区 range(A,B)的分区法则，范围分区都是 values less than(A,B)的，通常情况下以A为准，
-- 如果小于A的不用考虑B，直接插进去，如果等于A那么考虑B，要是满足B的话也插进去。



-- (1)分裂分区，以第一个范围分区为例：
alter table graderecord split partition jige at(75) 
      into(partition keyi,partition lianghao);    
-- 把分区及格分裂为两个分区：可以和良好

-- (2)合并分区，以第一个范围分区为例：
alter table graderecord merge partitions keyi,lianghao 
into partition jige;
-- 把可以和良好两个分区合并为及格

-- (3)添加分区，由于在范围分区上添加分区要求添加的分区范围大于原有分区最大值，但原有分区最大值已经为maxvalue，故本处以第二个散列分区为例：
alter table graderecord add partition p4;
-- 给散列分区例子又增加了一个分区p4 

-- (4)删除分区，语法：
alter table table_name drop partition partition_name;

-- (5)截断分区，清空分区中的数据
alter table table_name truncate partition partition_name;

13.临时表

-- 如果创建会话临时表的会话没有结束，则无法删除此临时表，因为临时表，
-- 还在使用之中，但是结束会话（关闭创建会话级临时表的命令窗口）后就可以删除了：

-- 会话级临时表
Create Global Temporary Table t_time(
  id number(4),
  tname varchar(20)
)
On Commit Preserve Rows;

select * from t_time;
insert into t_time values(1001,'张三');
insert into t_time values(1002,'李四');


-- 事务级临时表
Create Global Temporary Table t_rows(
  id number(4),
  tname varchar(20)
)
On Commit Delete Rows;

select * from t_rows;
insert into t_rows values(1001,'王五');
insert into t_rows values(1002,'赵六');

14.拉链表

-- 快照表:全部打包插入，整张表直接拿进来 全量插入
-- 拉链表:数据有变化则做出改变，如果没变化则不改变，增加改变的条数

select * from tmg1  -- 临时表1
select * from tmg2  -- 临时表2

select * from tmg 	-- 拉链表

select * from src 	-- 数据源表
create table SRC(
  id   VARCHAR2(4),
  name VARCHAR2(20),
  bal  NUMBER(7)
)
-- 1.创建临时表1 用于存放源数据，添加时间戳
insert into tmg1
 select s.*,trunc(sysdate-1,'dd'),
to_date('29991230','yyyy/mm/dd') 
from src s;
commit;

-- 2.将tmg1数据跟tmg对比
  数据有变化的 或者新增的数据 放到tmg2里
insert into tmg2
select * from tmg1 t1 where
(id ,name,bal)not in (
select id ,name,bal from tmg 
where end_dt=to_date('29991230','yyyy/mm/dd')) ;
commit;

-- 3.关链
update tmg t set end_dt=trunc(sysdate-1,'dd')
where t.id  in (select t2.id from tmp2 t2)
and end_dt=to_date('29991230','yyyy/mm/dd')

-- 4.开链
insert into tmg 
select * from tmp2;
select t.*,rowid from tmg t

tion partition_name;

– (5)截断分区，清空分区中的数据
alter table table_name truncate partition partition_name;


## 13.临时表

```sql
-- 如果创建会话临时表的会话没有结束，则无法删除此临时表，因为临时表，
-- 还在使用之中，但是结束会话（关闭创建会话级临时表的命令窗口）后就可以删除了：

-- 会话级临时表
Create Global Temporary Table t_time(
  id number(4),
  tname varchar(20)
)
On Commit Preserve Rows;

select * from t_time;
insert into t_time values(1001,'张三');
insert into t_time values(1002,'李四');


-- 事务级临时表
Create Global Temporary Table t_rows(
  id number(4),
  tname varchar(20)
)
On Commit Delete Rows;

select * from t_rows;
insert into t_rows values(1001,'王五');
insert into t_rows values(1002,'赵六');

-- 快照表:全部打包插入，整张表直接拿进来 全量插入
-- 拉链表:数据有变化则做出改变，如果没变化则不改变，增加改变的条数

select * from tmg1  -- 临时表1
select * from tmg2  -- 临时表2

select * from tmg 	-- 拉链表

select * from src 	-- 数据源表
create table SRC(
  id   VARCHAR2(4),
  name VARCHAR2(20),
  bal  NUMBER(7)
)
-- 1.创建临时表1 用于存放源数据，添加时间戳
insert into tmg1
 select s.*,trunc(sysdate-1,'dd'),
to_date('29991230','yyyy/mm/dd') 
from src s;
commit;

-- 2.将tmg1数据跟tmg对比
  数据有变化的 或者新增的数据 放到tmg2里
insert into tmg2
select * from tmg1 t1 where
(id ,name,bal)not in (
select id ,name,bal from tmg 
where end_dt=to_date('29991230','yyyy/mm/dd')) ;
commit;

-- 3.关链
update tmg t set end_dt=trunc(sysdate-1,'dd')
where t.id  in (select t2.id from tmp2 t2)
and end_dt=to_date('29991230','yyyy/mm/dd')

-- 4.开链
insert into tmg 
select * from tmp2;
select t.*,rowid from tmg t