企业官网建设流程全解析

游标 cursor

定义 ：游标 实际上是一个指针 指向 结果集的每一行数据，初始的时候 指向第一行数据

作用 处理多行数据 ------for select

语法

declare
cursor 游标名 is select语句;-------1 声明游标

begin
open 游标名;-----------------------2 打开游标

fetch 游标名 into 变量1 ,变量2......;---3. 使用游标 提取游标****

----（1） 提取数据 (2) 交给变量 （3） 指针下移

close 游标名; ---------------------4 关闭游标
end;

select * from emp

例题 打印输出 emp表中所有员工的姓名 岗位

declare
cursor c1 is select ename,job from emp ; -----1
v_ename emp.ename%type;
V_job emp.job%type;
begin
open c1 ; -----------2

fetch c1 into v_ename,V_job; ----3
dbms_output.put_line(v_ename||V_job);

close c1; -----------------4

end;

declare
cursor c1 is select ename,job from emp ; -----1
v_ename emp.ename%type;
V_job emp.job%type;
begin
open c1 ; -----------2

fetch c1 into v_ename,V_job; ----3
dbms_output.put_line(v_ename||V_job);

close c1; -----------------4

end;

注意 游标 是需要配合 循环来使用的

loop

例题 打印输出 emp表中所有员工的姓名 岗位

declare
cursor c1 is select ename,job from emp ; ------1
v_ename varchar2(20);
V_job emp.job%type;
begin
open c1 ; -------------------------------------2

loop

fetch c1 into V_ename,V_job ; -------------------3

exit when c1%notfound;

dbms_output.put_line(V_ename||v_job);




end loop;

close c1;
end;

游标的四个属性

1. 游标名%found --------------游标有值的时候返回 true

2. 游标名%notfound ------------游标没有值的时候 返回 true

3. 游标名%isopen -------判断游标是否打开 如果是 则返回 true

4. 游标名%rowcount -------统计游标处理的行数 ------返回的是数值

练习
例题 打印输出 emp表中所有员工的姓名 岗位
--while

declare
cursor c1 is select ename,job from emp ;
V_ename emp.ename%type;
V_job emp.job%type;

begin
open c1 ;
fetch c1 into V_ename,V_job ;

while c1%found ----------游标有值的时候
loop
dbms_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表中所有员工的姓名 岗位
--for

declare
cursor c1 is select ename,job from emp ;----1 声明游标

begin
for i in c1 ---游标
loop
dbms_output.put_line(i.ename||i.job);


end loop;


end;

等价写法

declare

begin
for i in (select ename,job from emp)
loop
dbms_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 ----返回值的类型

is
V_d date; -----变量

begin

select add_months(last_day(sysdate),-1)
into V_d
from dual;

return v_d;

end;

---有名块

select fu_97 -----使用函数的时候 里面的参数的个数 顺序 属性要和创建时形参一直

from dual

例题 ：创建一个有参数的函数 要求 返回任意一个日期的上个月的最后一天

create or replace function fu_97( v_d date ) ---形参

return date

is
V_a date; ----变量


begin
select 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 number

is
v_deptno number;
V_avg number;
begin
select deptno into V_deptno from emp where empno=V_empno;

select avg(sal) into V_avg from emp where deptno=V_deptno;

return v_avg;

end;

select ename,fu_97(7566) from emp

select fu_97(7566) from dual;

CREATE or replace FUNCTION fu_avg_sal(V_d number)

return NUMBER

is
V_a number;
BEGIN
SELECT AVG(b.sal)
into V_a
FROM emp a
INNER JOIN emp b
on a.deptno = b.deptno
where 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 varchar2

is
V_grade number;

begin
select grade
into V_grade
from emp
left join salgrade
on sal between losal and hisal
where empno=V_empno;

if v_grade =1 then
return '低等级';
elsif v_grade between 2 and 3 then
return '中等级';
elsif v_grade in(4,5) then
return '高等级';
end if;

end;

select fu_97(7566) from dual;

----------------------------------------------------
存储过程 --有名块 ---数据库对象之一
是将 任务 语句 存储起来 ，随时拿来调用

---函数 ：有且只有一个返回值 ----返回值

---存储过程： 把过程存储起来 ----没有返回值

创建存储过程语法

create [or replace ] procedure (形参1 形参类型,形参2 形参类型)

------------------以上类型不能写长度

is

begin

end;

例题 创建一个存储过程 传入一个员工编号 打印输出该员工的姓名

create or replace procedure sp_97(v_empno number)

is
V_ename emp.ename%type;

begin
select ename into V_ename from emp where empno=V_empno;
dbms_output.put_line(V_ename);

end;

调用存储过程

1. call 存储过程();

2. 用 程序块 调用存储过程

declare

begin
存储过程(); ----调用存储过程

end;

call sp_97(7566); --调用的时候 参数的个数顺序属性和创建时一致

declare

begin
sp_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)

is

begin

insert 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 number
is
V_avg number;
begin
select avg(sal) into V_avg from emp where deptno=V_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)

is
V_sal number;
V_d number;
V_sal1 number;
begin
select sal,deptno into V_sal,V_d from emp where empno=V_empno;

if V_sal > fu_97(v_d ) then
update emp set sal=sal-200 where empno=V_empno returning sal into V_sal1;
elsif V_sal < fu_97(v_d) then
update emp set sal=sal+200 where empno=V_empno returning sal into V_sal1;
else
null; --什么都不做
end if;

dbms_output.put_line(v_sal || v_sal1);

end;

call sp_97(7566);

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

存储过程的三种形参

1 输入型形参 in ----默认

2. 输出型形参 out

3. 输入输出型形参 in out

1 输入型形参 in ----默认

create or replace procedure sp_97(v_empno [in] number)

is
V_sal number;
V_d number;
V_sal1 number;
begin
select sal,deptno into V_sal,V_d from emp where empno=V_empno;

if V_sal > fu_97(v_d ) then
update emp set sal=sal-200 where empno=V_empno returning sal into V_sal1;
elsif V_sal < fu_97(v_d) then
update emp set sal=sal+200 where empno=V_empno returning sal into V_sal1;
else
null; --什么都不做
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 )

is

begin
select ename into V_ename from emp where empno=v_empno;

end;

call sp_97( 7566,变量 ); ------不能用call 调用

declare
a varchar2(20);
begin
sp_97(7566 , a );---接收了 返回的姓名 a

dbms_output.put_line(a);---a 是变量



end;

--可以通过 out 型形参 返回值 -----存储过程也可以有返回值

存储过程和函数区别

函数有且只有一个返回值 ，存储过程可以通过 out 输出型形参 有多个返回值

练习 传入员工编号 传入该员工的工资等级

create or replace procedure sp_97(V_empno in number,v_grade out number)

is

begin
select grade into V_grade
from emp
left join salgrade
on sal between losal and hisal
where empno=V_empno;

end;

declare
a number;
begin
sp_97(7566,a);

dbms_output.put_line(a);

end;

3 输入输出型形参 in out --了解

例题传入员工编号 输出该员工的姓名

create or replace procedure sp_97( v_a in out emp%rowtype )

is

begin
select ename into v_a.ename from emp where empno=v_a.empno;

end;

declare
v_b emp%rowtype; ------V_a 个数 顺序 属性 完全一致

begin
v_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表达式计算模拟个税并返回：
- sal<=1000：扣税0
- 1000<sal<=2000：扣5%
- 2000<sal<=3500：扣10%
- sal>3500：扣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 deptno=p_deptno) LOOP`，禁止显式声明cursor。

## 题目8（函数‑循环+判断）
编写函数`f_count_high_sal`，入参部门编号`p_deptno`，统计该部门工资大于2500的员工人数，返回统计数量。使用FOR循环遍历，不允许直接count聚合一步返回结果（必须循环逐个判断计数）。

## 题目9（存储过程‑多条件IF，OUT输出字符串）
存储过程`p_emp_info`，输入员工编号`p_empno`，输出OUT字符串`o_result`。
拼接信息：`姓名:xxx，岗位:xxx`；
附加规则：
- 入职早于1982年，追加`[老员工]`
- 工资>2800，追加`[高薪]`。

## 题目10（综合：函数调用存储过程，IF+循环）
1. 复用第6题函数`f_sal_tax`；
2. 创建存储过程`p_show_tax_list(p_deptno number)`；
使用FOR循环遍历该部门所有员工，调用`f_sal_tax`得到每个人扣税，DBMS_OUTPUT打印：`姓名:xxx，工资:xxx，扣税:xxx`。

---