实训6：PL/SQL高级编程

实验目的

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语句进行对比 (内容来自网络)

使用存储过程的优点：

1. 可以将复杂的业务逻辑封装在存储过程中，提高了代码的模块化和可维护性。
2. 可以通过存储过程的参数来动态地传递值，增强了存储过程的灵活性和通用性。
3. 可以在存储过程中实现错误处理机制，提高了程序的健壮性和稳定性。

使用存储过程的缺点：

1. 存储过程的编写和调试相对复杂，需要一定的数据库开发经验。
2. 存储过程依赖于数据库平台，不够具有通用性，可能会限制系统的可移植性。
3. 存储过程的执行效率可能不如直接执行 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语句进行对比 (内容来自网络)

使用函数的优点：

1. 可以将复杂的业务逻辑封装在函数中，提高了代码的模块化和可维护性。
2. 可以通过函数的参数来动态地传递值，增强了函数的灵活性和通用性。
3. 可以在函数中实现错误处理机制，提高了程序的健壮性和稳定性。

使用函数的缺点：

1. 函数的编写和调试相对复杂，需要一定的数据库开发经验。
2. 函数依赖于数据库平台，不够具有通用性，可能会限制系统的可移植性。
3. 函数的执行效率可能不如直接执行 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