实验目的
1、掌握PL/SQL的数据结构和编程结构,掌握应用PL/SQL编写简单程序的方法 2、理解存储过程的概念,掌握编写存储过程的方法 3、理解函数的概念,掌握编写存储过程的方法
实训内容
一、PL/SQL编程基础
1、pl/sql的组成
声明块(DECLARE)、执行块(BEGIN和END包裹)、异常处理块(EXCEPTION)
2、编制代码实现1+2+...+100的计算
----自定义函数,如何将pl/sql中函数的改变后的参数带回原函数:用“in out”
create or replace procedure swap(startN in out int , endN in out int)
as
temp int;
begin
temp:=startN;
startN:=endN;
endN :=temp;
end;
DECLARE
-- 声明块
startNum int;
endNum int;
i int;
sumNum int;
BEGIN
-- 执行块
startNum :=1;
endNum :=100;
if startNum > endNum then
swap(startNum, endNum);
end if;
sumNum :=0;
i := startNum;
while i<=endNum loop
sumNum:=sumNum+i;
i:=i+1;
end loop;
dbms_output.put_line('结果是:'||sumNum);
EXCEPTION
-- 异常处理块
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
二、存储过程编程
1、存储过程的概念
procedure 存储过程,存储在数据库中的不带返回值的命名PL/sql程序块,(可以反复使用复杂操作,并提高执行效率)
2、存储过程的语法
create or replace procedure procedureName(parm parmType)
as
local varible defination
begin
procedure body;
end;
3、编写代码
3.1、求1+2+...+100的结果并显示的存储过程
create or replace procedure sp_calcSum
as
startNum int;
endNum int;
i int;
sumResult int;
begin
startNum := 1;
endNum := 100;
i:= startNum;
sumResult:= 0;
WHILE i <=endNum LOOP
sumResult:=i+sumResult;
i:=i+1;
END LOOP;
dbms_output.put_line('result is ' || sumResult);
end;
运行结果如下:
3.2、求1+2+...+n的带参数的存储过程
create or replace procedure sp_calcSum(Num int)
as
startNum int;
endNum int;
i int;
sumResult int;
begin
startNum := 1;
endNum := Num;
i:= startNum;
sumResult:= 0;
WHILE i <=endNum LOOP
sumResult:=i+sumResult;
i:=i+1;
END LOOP;
dbms_output.put_line('result is ' || sumResult);
end;
当Num=6时, 运行结果如下:
三、函数编程
1、函数的概念
function函数,存储在数据库中的带返回值的命名pl/sql程序块
2、函数的语法
create or replace function functionName(parm parmType) return resultType
as
local varible defination
begin
function body
return result;
end;
3、编写代码
3.1、求1+2+...+100的函数
CREATE OR REPLACE function f_calcSum RETURN int
as
startNum int;
endNum int;
i int;
sumResult int;
begin
startNum := 1;
endNum := 100;
i:= startNum;
sumResult:= 0;
WHILE i <=endNum LOOP
sumResult:=i+sumResult;
i:=i+1;
END LOOP;
RETURN sumResult;
END;
运行结果如下:
3.2、求1+2+...+n的函数
create or replace function f_calcSum(pEndNumber int) return int
as
startNum int;
endNum int;
i int;
sumResult int;
begin
startNum := 1;
endNum := pEndNumber;
i:= startNum;
sumResult:= 0;
WHILE i <=endNum LOOP
sumResult:=i+sumResult;
i:=i+1;
END LOOP;
RETURN sumResult;
END;
当pEndNumber=5时,运行结果如下:
四、存储过程与函数的应用
1、编写代码
1.0、前置条件
你已经配备了EMP员工表与DEPT部门表,表内内容不赘述,具体可看实训2。
1.1、问题一
将指定部门号的所有员工薪水增加指定值的存储过程,并调用此存储过程将30号部门的薪水增加1000
Part 1. 编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)
CREATE OR REPLACE PROCEDURE sp_AlterSalByDeptno(pSalDelta NUMBER, pDeptno NUMBER)
AS
BEGIN
UPDATE EMP
SET sal = sal + pSalDelta
WHERE deptno = pDeptno;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Successfully updated salaries for department ' || pDeptno);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error updating salaries: ' || SQLERRM);
ROLLBACK;
END;
30号部门当前的薪水sal为:
Part 2.调用存储过程将30号部门的薪水增加1000元
execute sp_AlterSalByDeptno(1000,30);
30号部门此时的薪水sal为:
Part 3.与使用update语句进行对比 (内容来自网络)
使用存储过程的优点:
- 可以将复杂的业务逻辑封装在存储过程中,提高了代码的模块化和可维护性。
- 可以通过存储过程的参数来动态地传递值,增强了存储过程的灵活性和通用性。
- 可以在存储过程中实现错误处理机制,提高了程序的健壮性和稳定性。
使用存储过程的缺点:
- 存储过程的编写和调试相对复杂,需要一定的数据库开发经验。
- 存储过程依赖于数据库平台,不够具有通用性,可能会限制系统的可移植性。
- 存储过程的执行效率可能不如直接执行 SQL 语句,因为存储过程的执行涉及到编译和解释的过程。
1.2、问题二
求指定部门号的所有员工平均薪水的函数,并调用此函数计算30号部门的平均薪水
Part 1.编写函数 f_GetAvgSalByDeptno(pDeptno)
CREATE OR REPLACE FUNCTION f_GetAvgSalByDeptno(pDeptno NUMBER) RETURN NUMBER
AS
v_avg_sal NUMBER;
BEGIN
-- 使用 AVG 函数计算指定部门号的所有员工平均薪水
SELECT AVG(sal) INTO v_avg_sal
FROM emp
WHERE deptno = pDeptno;
RETURN v_avg_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL; -- 如果找不到数据,则返回 NULL
END;
Part 2.调用函数求出30号部门的平均薪水并显示temp:=f_GetAvgSalByDeptno(30)
DECLARE
tempSal NUMBER;
BEGIN
-- 调用函数获取30号部门的平均薪水
tempSal:= f_GetAvgSalByDeptno(30);
-- 输出结果
DBMS_OUTPUT.PUT_LINE('Average salary for department 30: ' ||tempSal);
END;
运行结果如下:
Part 3.与使用select语句进行对比 (内容来自网络)
使用函数的优点:
- 可以将复杂的业务逻辑封装在函数中,提高了代码的模块化和可维护性。
- 可以通过函数的参数来动态地传递值,增强了函数的灵活性和通用性。
- 可以在函数中实现错误处理机制,提高了程序的健壮性和稳定性。
使用函数的缺点:
- 函数的编写和调试相对复杂,需要一定的数据库开发经验。
- 函数依赖于数据库平台,不够具有通用性,可能会限制系统的可移植性。
- 函数的执行效率可能不如直接执行 SQL 语句,因为函数的执行涉及到编译和解释的过程。
2、结论
需要频繁重复的数据库操作通常会编制专门的存储过程或函数
存储过程应用: 先创建存储过程(编写sql语句,将编写的代码编译后保存在数据库中,同时存储了编写的plsql语句和对应的编译后的机器操作指令),再使用存储过程(直接调用机器操作指令)
sql语句:update emp set sal=sal+1000 where deptno=30; (先检查sql是否正确,再转换成机器操作指令,最后执行机器操作)
对比:!!!看“---”后的字
while deptno in(10,20,30) loop
execute alterSalBydeptno(delta,target); ---1 每次只需直接执行
update emp set sal=sal+delta where deptno=target; ---2 每次执行都要检查、转换、执行
DeptnoMoveNext();
end loop