Oracle的PL/SQL高级编程

一颗冰椰子

于 2024-06-17 11:43:47 发布

阅读量615

点赞数 5

文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/weixin_68999643/article/details/139738052

版权

实验目的：

1、掌握PL/SQL的数据结构和编程结构，掌握应用PL/SQL编写简单程序的方法

2、理解存储过程的概念，掌握编写存储过程的方法

3、理解函数的概念，掌握编写存储过程的方法

实训内容：

一、PL/SQL编程基础

1、pl/sql的组成：声明块、执行块、异常处理块

DECLARE -- 声明块

在PL/SQL中，DECLARE关键字用于定义变量、常量和其他数据结构，以便后续的PL/SQL块可以使用这些声明的项。它类似于其他编程语言中的变量声明部分。

BEGIN -- 执行块

BEGIN关键字标志着PL/SQL匿名块或存储过程的正文的开始。在BEGIN和END之间的部分是实际的可执行代码，包括控制流语句、SQL查询、过程调用等。

EXCEPTION -- 异常处理块

EXCEPTION关键字用于指定PL/SQL块中异常发生时的处理逻辑。它定义了在异常发生时应该执行的语句或处理程序。异常处理块可以捕获和处理数据库操作中的错误或自定义异常。

2、编制代码实现1+2+...+100的计算

declare

n int;

result int;

begin

n:=0;

result:=0;

while n<=100 loop

result:=result+n;

n:=n+1;

end loop;

dbms_output.put_line('结果是'||result);

end;

二、存储过程编程

1、存储过程的概念

procedure 存储过程，存储在数据库中的不带返回值的命名PL/sql程序块,（可以反复使用复杂操作，并提高执行效率）

2、存储过程的语法

create or replace procedure procedureName(parm parmType)

local varible defination

begin

procedure body;

end;

3、编写求u1+2+...+100的结果并显示的存储过程

create or replace procedure sp_calcSum as
result int;
begin
result := 0;
for n in 1..100 loop
result := result + n;
end loop;
dbms_output.put_line('1+2+...+100的结果是' || TO_CHAR(result));
end;

4、编写求1+2+...+n的带参数的存储过程

create or replace procedure sp_calcSum(pEndNumber int) as
result int;
begin
result := 0;
foe n in 1..pEndNumber loop
result := result + n;
end loop;
dbms_output.put_line('1+2+...+' || TO_CHAR(pEndNumber) || '的结果是' || TO_CHAR(result));
end;

三、函数编程

1、函数的概念

function函数,存储在数据库中的带返回值的命名pl/sql程序块

2、函数的语法

create or replace function functionName(parm parmType) return resultType

local varible defination

begin

function body

return result;

end;

3、编写求1+2+...+100的函数

create or replace function f_calcSum return number
as
result number :=0;
begin
for i in 1..100 loop
result :=result+i;
end loop;
return result;
end;
select f_calcSum() from dual;

4、编写求1+2+...+n的函数

create or replace function f_calcSum(pEndNumber int) return int
as
result int :=0;
begin
for i in 1..pEndNumber loop
result :=result+i;
end loop;
return result;
end;
select f_calcSum(100) from dual;

四、存储过程与函数的应用

1、编写将指定部门号的所有员工薪水增加指定值的存储过程，并调用此存储过程将30号部门的薪水增加1000

编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)

调用存储过程将30号部门的薪水增加1000元 execute sp_AlterSalByDeptno(1000,30)

与使用update语句进行对比

--创建存储过程
CREATE OR REPLACE PROCEDURE sp_AlterSalByDeptno(pSalDelta NUMBER, pDeptno NUMBER) AS
BEGIN
UPDATE employees SET salary = salary + pSalDelta WHERE department_id = pDeptno;
COMMIT; -- 提交事务
DBMS_OUTPUT.PUT_LINE('部门' || TO_CHAR(pDeptno) || '的薪水已经增加' || TO_CHAR(pSalDelta) || '元');
END;

--调用存储过程
EXECUTE sp_AlterSalByDeptno(1000, 30);

2、编写求指定部门号的所有员工平均薪水的函数，并调用此函数计算30号部门的平均薪水

编写函数 f_GetAvgSalByDeptno(pDeptno)

调用函数求出30号部门的平均薪水并显示tempSal:=f_GetAvgSalByDeptno(30)

与使用select语句进行对比

--创建存储过程
CREATE OR REPLACE FUNCTION f_GetAvgSalByDeptno(pDeptno NUMBER) RETURN NUMBER AS
v_avg_sal NUMBER;
BEGIN
SELECT AVG(salary) INTO v_avg_sal FROM employees WHERE department_id = pDeptno;
RETURN v_avg_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

--调用存储过程
DECLARE
tempSal NUMBER;
BEGIN
tempSal := f_GetAvgSalByDeptno(30);
IF tempSal IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('部门30的平均薪水是：' || TO_CHAR(tempSal));
ELSE
DBMS_OUTPUT.PUT_LINE('部门30没有员工');
END IF;
END;