游标 cursor定义 游标 实际上是一个指针 指向 结果集的每一行数据初始的时候 指向第一行数据作用 处理多行数据 ------for select语法declarecursor 游标名 is select语句;-------1 声明游标beginopen 游标名;-----------------------2 打开游标fetch 游标名 into 变量1 ,变量2......;---3. 使用游标 提取游标****----1 提取数据 (2) 交给变量 3 指针下移close 游标名; ---------------------4 关闭游标end;select * from emp例题 打印输出 emp表中所有员工的姓名 岗位declarecursor c1 is select ename,job from emp ; -----1v_ename emp.ename%type;V_job emp.job%type;beginopen c1 ; -----------2fetch c1 into v_ename,V_job; ----3dbms_output.put_line(v_ename||V_job);close c1; -----------------4end;declarecursor c1 is select ename,job from emp ; -----1v_ename emp.ename%type;V_job emp.job%type;beginopen c1 ; -----------2fetch c1 into v_ename,V_job; ----3dbms_output.put_line(v_ename||V_job);close c1; -----------------4end;注意 游标 是需要配合 循环来使用的loop例题 打印输出 emp表中所有员工的姓名 岗位declarecursor c1 is select ename,job from emp ; ------1v_ename varchar2(20);V_job emp.job%type;beginopen c1 ; -------------------------------------2loopfetch c1 into V_ename,V_job ; -------------------3exit when c1%notfound;dbms_output.put_line(V_ename||v_job);end loop;close c1;end;游标的四个属性1. 游标名%found --------------游标有值的时候返回 true2. 游标名%notfound ------------游标没有值的时候 返回 true3. 游标名%isopen -------判断游标是否打开 如果是 则返回 true4. 游标名%rowcount -------统计游标处理的行数 ------返回的是数值练习例题 打印输出 emp表中所有员工的姓名 岗位--whiledeclarecursor c1 is select ename,job from emp ;V_ename emp.ename%type;V_job emp.job%type;beginopen c1 ;fetch c1 into V_ename,V_job ;while c1%found ----------游标有值的时候loopdbms_output.put_line(V_ename||V_job);fetch c1 into V_ename,V_job ;end loop;close c1;end;for 循环配合游标使用1.for 循环会 自动的打开和关闭游标2. for 循环会 自动的 fetch 游标例题 打印输出 emp表中所有员工的姓名 岗位--fordeclarecursor c1 is select ename,job from emp ;----1 声明游标beginfor i in c1 ---游标loopdbms_output.put_line(i.ename||i.job);end loop;end;等价写法declarebeginfor i in (select ename,job from emp)loopdbms_output.put_line(i.ename||i.job);end loop;end;------------------------------------练习打印输出 员工的姓名岗位薪资部门编号部门名称以及部门平均工资--必须使用游标 ---3种方法 loop while for--------------------------------------------------------------------有名块 有名字的 是可以永久保存到数据库中随时拿来调用 比如 to_date函数 function--特点 函数 有 且只有一个返回值自定义函数语法create [or replace] function 函数名[(形参1 形参类型,形参2 形参类型....)]return 返回值的类型------------------------------------------------以上的所有类型 不能写长度is|as--声明部分begin--执行部分 核心部分 实现函数的过程的部分return 最终的值 ; ----函数最终的返回结果end ;例题 创建没有参数的函数 返回上个月的最后一天create or replace function fu_97 --------------------名字 有意义return date ----返回值的类型isV_d date; -----变量beginselect add_months(last_day(sysdate),-1)into V_dfrom dual;return v_d;end;---有名块select fu_97 -----使用函数的时候 里面的参数的个数 顺序 属性要和创建时形参一直from dual例题 创建一个有参数的函数 要求 返回任意一个日期的上个月的最后一天create or replace function fu_97( v_d date ) ---形参return dateisV_a date; ----变量beginselect add_months(last_day( V_d ) ,-1) into V_a from dual;return v_a;end;select fu_97( to_date(2000/3/1,yyyy/mm/dd) ) from dual;练习 创建一个函数要求 传入一个员工编号 返回该员工的部门的平均工资create or replace function fu_97(v_empno number)return numberisv_deptno number;V_avg number;beginselect deptno into V_deptno from emp where empnoV_empno;select avg(sal) into V_avg from emp where deptnoV_deptno;return v_avg;end;select ename,fu_97(7566) from empselect fu_97(7566) from dual;CREATE or replace FUNCTION fu_avg_sal(V_d number)return NUMBERisV_a number;BEGINSELECT AVG(b.sal)into V_aFROM emp aINNER JOIN emp bon a.deptno b.deptnowhere a.empno v_d;return V_a;end;SELECT fu_avg_sal(7566) FROM dual;练习 创建一个函数 传入一个员工编号如果该员工的工资等级是 1 则返回 低等级 2-3 中等级 4-5 高等级create or replace function fu_97 (v_empno number)return varchar2isV_grade number;beginselect gradeinto V_gradefrom empleft join salgradeon sal between losal and hisalwhere empnoV_empno;if v_grade 1 thenreturn 低等级;elsif v_grade between 2 and 3 thenreturn 中等级;elsif v_grade in(4,5) thenreturn 高等级;end if;end;select fu_97(7566) from dual;----------------------------------------------------存储过程 --有名块 ---数据库对象之一是将 任务 语句 存储起来 随时拿来调用---函数 有且只有一个返回值 ----返回值---存储过程 把过程存储起来 ----没有返回值创建存储过程语法create [or replace ] procedure (形参1 形参类型,形参2 形参类型)------------------以上类型不能写长度isbeginend;例题 创建一个存储过程 传入一个员工编号 打印输出该员工的姓名create or replace procedure sp_97(v_empno number)isV_ename emp.ename%type;beginselect ename into V_ename from emp where empnoV_empno;dbms_output.put_line(V_ename);end;调用存储过程1. call 存储过程();2. 用 程序块 调用存储过程declarebegin存储过程(); ----调用存储过程end;call sp_97(7566); --调用的时候 参数的个数顺序属性和创建时一致declarebeginsp_97(7839);end;练习 创建一个存储过程 传入 员工编号姓名岗位薪资入职日期以及部门编号要求 将传入的参数 insert 插入到 emp表中create or replace procedure sp_97(V_empno number,V_ename varchar2,V_job varchar2,V_hiredate date,V_sal number,V_deptno emp.deptno%type)isbegininsert into emp (empno,ename,job,sal,deptno,hiredate)values (V_empno,V_ename,V_job,V_sal,V_deptno,V_hiredate);end;call sp_97(3344,马德华,猪八戒,sysdate,1,10)select * from emp练习 1 创建一个函数函数函数 要求 传入部门编号 返回该部门的平均工资create or replace function fu_97(V_deptno number)return numberisV_avg number;beginselect avg(sal) into V_avg from emp where deptnoV_deptno;return V_avg;end;select fu_97(10) from dual;2.创建一个存储过程 传入一个员工编号 如果 该员工的工资 高于自己部门平均工资则降薪200 低于 涨薪200 等于 不变要求 1. 必须利用第一题的函数 2. 打印输出涨薪 前后的薪资create or replace procedure sp_97(v_empno number)isV_sal number;V_d number;V_sal1 number;beginselect sal,deptno into V_sal,V_d from emp where empnoV_empno;if V_sal fu_97(v_d ) thenupdate emp set salsal-200 where empnoV_empno returning sal into V_sal1;elsif V_sal fu_97(v_d) thenupdate emp set salsal200 where empnoV_empno returning sal into V_sal1;elsenull; --什么都不做end if;dbms_output.put_line(v_sal || v_sal1);end;call sp_97(7566);----------------------------------------------------------存储过程的三种形参1 输入型形参 in ----默认2. 输出型形参 out3. 输入输出型形参 in out1 输入型形参 in ----默认create or replace procedure sp_97(v_empno [in] number)isV_sal number;V_d number;V_sal1 number;beginselect sal,deptno into V_sal,V_d from emp where empnoV_empno;if V_sal fu_97(v_d ) thenupdate emp set salsal-200 where empnoV_empno returning sal into V_sal1;elsif V_sal fu_97(v_d) thenupdate emp set salsal200 where empnoV_empno returning sal into V_sal1;elsenull; --什么都不做end if;dbms_output.put_line(v_sal || v_sal1);end;2. 输出型形参 out例题 创建一个存储过程 插入一个员工编号 传出一个员工姓名--例题 创建一个存储过程 传入一个员工编号 打印输出该员工的姓名create or replace procedure sp_97( V_empno in number ,V_ename out varchar2 )isbeginselect ename into V_ename from emp where empnov_empno;end;call sp_97( 7566,变量 ); ------不能用call 调用declarea varchar2(20);beginsp_97(7566 , a );---接收了 返回的姓名 adbms_output.put_line(a);---a 是变量end;--可以通过 out 型形参 返回值 -----存储过程也可以有返回值存储过程和函数区别函数有且只有一个返回值 存储过程可以通过 out 输出型形参 有多个返回值练习 传入员工编号 传入该员工的工资等级create or replace procedure sp_97(V_empno in number,v_grade out number)isbeginselect grade into V_gradefrom empleft join salgradeon sal between losal and hisalwhere empnoV_empno;end;declarea number;beginsp_97(7566,a);dbms_output.put_line(a);end;3 输入输出型形参 in out --了解例题传入员工编号 输出该员工的姓名create or replace procedure sp_97( v_a in out emp%rowtype )isbeginselect ename into v_a.ename from emp where empnov_a.empno;end;declarev_b emp%rowtype; ------V_a 个数 顺序 属性 完全一致beginv_b.empno:7566;sp_97( v_b );dbms_output.put_line(v_b.ename);end;------------------------------------------------存储过程结束# Oracle PL/SQL 练习题10道中等难度约束要求1. 允许存储过程、自定义函数、IF判断、CASE、FOR/WHILE循环2. 禁止触发器、异常处理块(EXCEPTION)3. 环境基于经典emp、dept表题目可直接在SCOTT用户下运行不需要自建业务表 说明函数必须有返回值存储过程无返回值可使用IN/OUT参数不许写EXCEPTION部分。## 题目1存储过程‑IF判断编写存储过程p_check_sal传入员工编号p_empno。查询该员工工资- 工资大于3000输出员工XXX工资偏高- 工资1500~3000输出员工XXX工资正常- 小于1500输出员工XXX工资偏低要求使用DBMS_OUTPUT打印结果。## 题目2函数‑IF编写函数f_get_job_level接收岗位p_job返回岗位等级数字- PRESIDENT → 1- MANAGER →2- ANALYST →3- 其余岗位返回4。## 题目3存储过程‑WHILE循环编写存储过程p_print_num传入数字p_n使用**WHILE循环**打印1~p_n之间所有偶数。## 题目4函数‑FOR循环编写函数f_sum_even接收入参p_max使用FOR循环计算1~p_max所有偶数之和返回总和。## 题目5存储过程‑IF 查询 OUT参数创建存储过程p_dept_stats入参部门编号p_deptno两个OUT参数o_emp_count(部门人数)、o_avg_sal(部门平均工资)。逻辑如果部门人数大于5则把平均工资上浮10%赋值给o_avg_sal否则保持原平均工资。## 题目6函数‑CASE判断编写函数f_sal_tax传入工资p_sal使用CASE表达式计算模拟个税并返回- sal1000扣税0- 1000sal2000扣5%- 2000sal3500扣10%- sal3500扣15%返回扣税金额。## 题目7存储过程‑FOR循环 IF嵌套存储过程p_sal_update_loop传入部门号p_deptno。遍历该部门全部员工FOR循环游标for- 如果岗位是MANAGER工资增加200- 如果岗位是CLERK工资增加100其他岗位工资不变。执行update更新表。 提示使用FOR rec IN (select empno,job,sal from emp where deptnop_deptno) LOOP禁止显式声明cursor。## 题目8函数‑循环判断编写函数f_count_high_sal入参部门编号p_deptno统计该部门工资大于2500的员工人数返回统计数量。使用FOR循环遍历不允许直接count聚合一步返回结果必须循环逐个判断计数。## 题目9存储过程‑多条件IFOUT输出字符串存储过程p_emp_info输入员工编号p_empno输出OUT字符串o_result。拼接信息姓名:xxx岗位:xxx附加规则- 入职早于1982年追加[老员工]- 工资2800追加[高薪]。## 题目10综合函数调用存储过程IF循环1. 复用第6题函数f_sal_tax2. 创建存储过程p_show_tax_list(p_deptno number)使用FOR循环遍历该部门所有员工调用f_sal_tax得到每个人扣税DBMS_OUTPUT打印姓名:xxx工资:xxx扣税:xxx。---