Oracle进阶四

Oracle 第四天

  1. 游标
  2. 存储过程
  3. 存储函数
  4. 分析函数
  5. 触发器

1. 游标

游标主要是用来查询结果集的

  • 定义游标
CURSOR cursor_name(游标名称) IS select * from emp；
  • 打开游标
open cursor_name
  • 提取游标数据
FETCH cursor_name INTO {variable_list|record_list}(指定的输出变量)
  • 关闭游标:释放资源
close cursor_name
  • 游标定义实例
declare v_empno number(10);
        v_empname varcahr2(20);
cursor c_emp IS select empno,empname from emp;
begin
    open c_emp;
        loop
            fetch c_emp into v_empno,v_empname;
            exit when c_emp%notfound;
                dbms_output.put_line(v_empno||'---'||v_empname);
        end loop;
    close c_emp;
end;
  • 删除部门名称相同的数据 保存部门编号最大的
declare v_dname varchar2(20);
        v_deptno varchar2(10);
    cursor c_dept is select dname,max(deptno) from dept group by dname having count(deptno) > 0;
begin
    open c_dept;
    loop
        fetch c_dept into v_dname,v_deptno;
        exit when c_dept%notfound
            delete from dept where dname = v_dname and deptno < v_deptno;
    end loop;
    close c_dept;
end;
  • 带参数的游标：输出指定部门下的员工姓名和工资
declare
    cursor vrows(dno number) is select * from emp where deptno = dno;
    vrow emp%rowtype;
begin
    open vrows(10);
    loop
        fetch vrows into vrow;
        exit when vrows%notfound;
            dbms_output.put_line(vrow.ename||'---'||vrow.sal);
    end loop;
    close vrows;
end;
  • 系统引用游标：
    • 声明游标：游标名 sys_refcursor
    • 打开游标：open 游标名 for 结果集
    • 从游标中取数据
    • 关闭游标
-- 输出员工表中所有员工姓名和工资
declare
    vrows sys_refcursor;
    vrow emp%rowtype;
begin
    open vrows for select * from emp;
    loop
        fetch vrows int vrow;
        exit when vrows%notfound;
        dbms_output.put_line(vrow.ename||'---'||vrow.sal);
    end loop;
    close vrows
end;
  • for循环遍历游标
-- 输出员工表中所有员工姓名和工资declare   vrows sys_refcursor;    vrow emp%rowtype;begin  open vrows for select * from emp;   for vrow in vrows loop      dbms_output.put_line(vrow.ename||'---'||vrow.sal);  end loop;   close vrows；end;
  • 案例：按照员工工作给所有员工涨工资，总裁涨1000，经理涨800，其他人涨400
declare cursor vrows is select * from emp;  vrow emp%rowtype;begin  open vrows;     loop            fetch vrows into vrow;          exit when vrows%notfound;           if vrow.job = 'president' then              update emp set sal = sal + 1000 where vrow.empno = empno;           elseif vrow.job = 'manager' then                update emp set sal = sal + 800 where vrow.empno = empno;            else                update emp set sal = sal + 400 where vrow.empno = empno;            end if;     end loop;   close vrows;    commit;end;
  • 系统异常
    • zero_divide：除零异常
    • value_error：类型转换异常
    • too_many_rows：查询出多行记录但复制给了一行记录
    • no_data_found：没有找到数据
declare vi number;  vrow emp%rowtype;begin      vi := 8/0;  vi: = 'aa'; select * into vrow from emp;    select * into vrow from emp where empno = 1234567exception  when zero_divide then       dbms_output.put_line('除零异常');   when value_error then       dbms_output.put_line('类型转换异常'); when too_many_rows then     dbms_output.put_line('查询出多行记录但复制给了一行记录');   when no_data_found then     dbms_output.put_line('没有找到数据'); when others then        dbms_output.put_line('其他异常' || sqlerrm);end;
  • 自定义异常
    • 异常名 exception;
    • 抛出自定义异常：raise 异常名
-- 查询指定编号的员工，如果没有找到，则抛出自定义的异常/*---------错误演示-------------------------declare    -- 1.声明一个变量 vrow emp%rowtype    -- 声明自定义异常  no_emp exception;begin  -- 2.查询员工信息，保存起来    select * into vrow from emp where empno = 4444; --抛出异常  -- 3.判断员工信息是否为空 if vrow.sal is null then        --  4.如果是抛出异常       raise no_emp;   end if;EXCEPTION    WHEN no_emp then        dbms_output.put_line('输出自定义异常');    when others then        dbms_output.put_line('其他异常' || sqlerrm);end;*/declare   cursor vrows is select * from emp where empno = 4444;   -- 1.声明一个变量 vrow emp%rowtype    -- 声明自定义异常  no_emp exception;begin  open vrows; fetch vrows into vrow;  if vrows%notfound then      raise no_emp;   end if; close vrows;EXCEPTION   WHEN no_emp then        dbms_output.put_line('输出自定义异常');    when others then        dbms_output.put_line('其他异常' || sqlerrm);end;

2. 存储过程

  • 存储过程
  • 是一个PL/SQL块
  • 以命名的数据库对象形式存储于数据库中
  • 可以传递参数给存储过程
  • 存储过程可以有返回值，也可以没有返回值
  • 存储过程的返回值必须通过参数带回
  • 存储过程语法结构
create(or replace) PROCEDURE AA(存储过程名称) [(<parameter list>)]IS/AS   <local variable declaration>begin   <executables statements>[EXCEPTION  <exception handlers>]end [存储过程名称];
  • 创建存储过程实例
CREATE OR replace PROCEDurE MYPROCASBEGIN   DELETE FROM dept where deptno in (80,81);   insert into dept(deptno,dname) values (80,'财务部');   insert into dept(deptno,dname) values (81,'市场部');   commit;END myproc;

注: 如果有insert delete update 语句，则一定要有commit；

  • 执行存储过程
// 1.命令方式execute myproc;// 2.PL/SQL 方式begin myproc;end;
  • 存储过程参数的三种模式
    • in：输入参数,默认参数模式
    • out：输出参数
    • in out :既做输入，也做输出
  • 创建带参存储过程 ---- in参数
CREATE OR REPLACE  PROCEDURE FIND_EMP (EMP_NO in NUMBER)AS/*********************************************************        程序功能：输入员工编号，打印员工姓名      业务逻辑：查询句        开发日期：2021/09/25     开发人员：le_u       源    表：emp      目 标 表：**********************************************************/   empname varchar2(30);begin  SELECT ENAME INTO EMPNAME FROM EMP WHERE EMPNO = EMP_NO;    DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);EXCEPTION    WHEN NO_DATA_FOUND THEN     DBMS_OUTPUT.PUT_LINE('雇员编号未找到');end FIND_EMP;---- 调用存储过程BEGIN   find_emp(7788);END;
  • 创建存储过程 -----out
CREATE OR REPLACE PROCEDURE FINDEMP (EMP_NO IN NUMBER,EMP_NAME OUT VARCAHR)IS/*******************************************************       程序功能：输入员工编号，输出员工姓名      业务逻辑：查询句        开发日期：2021/09/25     开发人员：le_u       源    表：emp      目 标 表：********************************************************/ e_name varchar2(30)BEGIN    SELECT ENAME INTO E_NAME FROM EMP WHERE EMPNO = EMP_NO; EMP_NAME := E_NAME;EXCEPTION    WHEN NO_DATA_FOUND THEN     DBMS_OUTPUT.PUT_LINE('雇员编号未找到');END FINDEMP;----调用存储过程declare ename varchar2(30);begin  findemp(7788,ename);    dbms_output.put_line(ename);end;
  • in out 既可以做输入也可以做输出 ：定义存储过程，交换两个参数的值
create procedure mp(n1 in out number,n2 in out number)as    temp number;begin   temp := n1; n2 := n1;   n2 := temp;end;-- 调用declare a number := 10; b number := 20;begin    mp(a,b) dbms_output.put_line(a);    dbms_output.put_line(b);end;
  • 有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
create or replace procedure process_student IStotal number := 0;i number := 0;begin select count(1) into total from student; while i <= total loop  update student set grade = grade + 1 where s_no = i;    i := i+1;   if i >= 10000 then   commit;    end if; end loop; dbms_output.put_line('finished!')end;
  • 案例：给指定员工涨薪，并打印涨薪前和涨薪后的工资
create or replace procedure proc_updatesal(vempno in number,vnum in number)is   vasl emp.sal%rowtype;begin  select sal into vsal from emp where empno = vempno; dbms_output.put_line('涨薪前：'||vsal); update emp set sal = vsal+vnum where empno = vempno;    dbms_output.put_line('涨薪后：'||(vsal+vnum));  commit;end;-- 调用call proc_updatesal(7788,10) --方式1--方式2declarebegin proc_updatesal(7788,10)end;

3. 存储函数

存储函数实际上是一段封装在oracle服务器中的一段plsql代码片段，已经编译好的

  • 语法
create [or replace] function 存储函数的名称（参数名 in|out 参数类型） return 参数类型is|asbeginend;

  • 存储过程和存储函数的区别

    • 函数存在的意义是给存储过程调用的（存储过程里面调用存储函数）
    • 函数可以在sql语句中直接调用
    • 存储过程能实现的存储函数也能实现

  • 案例：查询员工年薪

create or replace function func_getsal(vempno in number) return numberis    yersal number;begin select sal*12+nvl(comm) into yersal from emp where empno = vempno;  return yersal;end;declare   vsal number;begin   vsal := func_getsal(7788);  dbms_output.put_line('年薪：'|| vsal);end;-- 查询员工的姓名和年薪select ename,func_getsal(empno) from emp;

4. 分析函数

  • 定义

分析函数是Oracle专门用于解决复杂报表统计需求的函数

可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值

  • 实例：查询出员工的姓名、工资、所在部门的平均工资，要求显示出来的是工资大于部门平均工资的员工
// 连接查询方式select a.deptno, a.ename, a.sal  from emp a, (select deptno, avg(sal) avg_sal from emp group by deptno) b where a.deptno = b.deptno,   and a.sal > b.avg_sal// 函数方法select *  from (select deptno,               ename,               sal,               avg(sal) over(partition by deptno) avg_sal) where sal > avg_sal

over 是一个关键字，用于标识分析函数，否则查询分析器不能识别avg()聚合函数和avg()分析函数

partition by deptno 是可选的分区子句，如果不存在任何分区子句，则全部的结果可以看作一个单一的大区

  • 分析函数和聚合函数的区别

    • 普通的聚合函数要用group by进行分组，每个分组返回一个统计值
    • 分析函数使用partition by 分组，并且每组每行都可以返回一个统计值

  • 分析函数之等级函数：工资从大到小

select empno.ename,sal from emp order by desc；// 取员工表中，工资排名前三位的select * from(select empno ,ename,sal,   rank() over(order by sal desc) rank , -- 122456 dense_rank over(order by sal desc) dense_rank, -- 122345    row_number over(order by sal desc) num  --123456from emp )where dense_rank <= 3;
  • 分析函数-sum用法
select empno,       ename,      sal,        deptno,     sum(sal) over (partition by deptno) 部门总工资，      sum(sal) over() 所有人总工资,     sum(sal) over(partition by deptno order by sal) 部门工资累计求和from emp
  • 案例：需求统计各年份、月份，iphone手机，日期当日销售量，月累计而销售
// 1. 创建目标表，存储需求的最终结果create table year_month_iphone_sales ( year varchar2(10),  month varchar2(10), day varchar2(10),   brand varchar2(10), sales number,   leijisales number,)// 2.开发存储过程create procedure year_month_iphone_salesas/*******************************************************        程序功能：统计各年份、月份，iphone手机，日期当日销售量，月累计而销售       业务逻辑：从订单明细表、机型表、品牌表获取相关字段信息，并开发统计       开发日期：2021/09/25     开发人员：le_u       源    表：order_detal,phone_model,brand        目 标 表：year_month_iphone_sales********************************************************/begin -- 清空目标表    delete from year_month_iphone_sales;    commit;     -- 业务逻辑开发,插入目标表 insert into year_month_iphone_sales     (           year,           month ,         day ,           brand ,         sales ,         leijisales,     )   select year         month,          day,            brand_name,         sales,          sum(sales) over (partition by year,month order by day) leijisales   from    (select         to_char(order_date,'yyyy') year,        to_char(order_date,'mm') month,     to_char(order_date,'yyyy-mm-dd') day,       brand_name,     sum(amount) sales,  from order_detal a,         phone_model b,          brand c where a.mp_no = b.mp_no and b.brand_no = c.brand_no and c.brand_name = 'iphone' group by to_char(order_date,'yyyy') ,       to_char(order_date,'mm') ,      to_char(order_date,'yyyy-mm-dd') ,      brand_name);            commit;end;// 3.测试，执行存储过程select * from year_month_iphone_salesbegin year_month_iphone_sales;end;

5. 触发器

  • 语法
create [or replace] trigger 触发器的名称before | afterinsert | update | deleteon 表名[for each row]declarebeginend;
  • 语句级触发器案例：新员工入职之后，输出一句话
create or replace trigger tri_test1afterinserton empdeclarebegin    dbms_output.put_line('hello world');end;insert into emp(empno,ename) values (9527,'hy');
  • 语句级触发器案例：数据校验，星期六老板不在不能办理新员工入职
create or replace trigger tri_test2beforeinserton empdeclare    vday varchar2(10);begin -- 查询当前星期 trim 去空格  select trim(to_char(sysdate,'day')) into vday from dual;    if vday = 'saturday' then       dbms_output.put_line('星期六老板不在不能办理新员工入职');       -- 不能进行提交或回滚 抛系统异常      raise_application_error(-20001,'星期六老板不在不能办理新员工入职'); end if;end;insert into emp(empno,ename) values (9527,'hy');

  1. 语句级触发器：不管影响多少行都只会触发一次

  2. 行级触发器：影响多少行就触发多少次

                       :old    代表更新前的数据

                   :new    代表新的记录
  • 行级触发器案例：更新所有的工资
create or replace trigger tri_test3afterupdateon empfor each rowdeclarebegin    dbms_output.put_line('更新了数据');end;
  • 行级触发器案例：判断员工涨工资后的工资一定要大于涨工资前的工资
create or replace trigger tri_test4beforeupdateon empfor each rowdeclarebegin   if :old.sal > :new.sal then     raise_application_error(-20001,'更新前的工资不能小于之前的工资');  end if;end;
  • 模拟mysql中id自增长属性 auto_increment
-- 创建表create table person(  pid number primary key,    pname varchar2(20));-- 创建序列create sequence seq_person_pid;-- 创建触发器create or replace trigger tri_test4afterinserton personfor each rowdeclarebegin        dbms_output.put_line(:new.pname);   -- 使用序列给pid赋值   select seq_person_pid.nextval into :new.pid fromdual;end;
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,826评论 6赞 506
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,968评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,234评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,562评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,611评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,482评论 1赞 302
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,271评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,166评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,608评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,814评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,926评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,644评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,249评论 3赞 329
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,866评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,991评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,063评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,871评论 2赞 354

推荐阅读更多精彩内容