目录
4、编写求1+2+...+n的带参数的存储过程create or replace procedure sp_calcSum(pEndNumber int) asbegin ...end;
1、编写将指定部门号的所有员工薪水增加指定值的存储过程,并调用此存储过程将30号部门的薪水增加1000
2、编写求指定部门号的所有员工平均薪水的函数,并调用此函数计算30号部门的平均薪水
实验目的:
1、掌握PL/SQL的数据结构和编程结构,掌握应用PL/SQL编写简单程序的方法
2、理解存储过程的概念,掌握编写存储过程的方法
3、理解函数的概念,掌握编写存储过程的方法
实训内容:
一、PL/SQL编程基础
1、pl/sql的组成:声明块、执行块、异常处理块
DECLARE -- 声明块
BEGIN -- 执行块
EXCEPTION -- 异常处理块
END;
2、编制代码实现1+2+...+100的计算
DECLARE
n NUMBER:=0;
result NUMBER:=0;
BEGIN
WHILE N<=100 LOOP
result:=result+n;
n:=n+1;
end loop;
DBMS_OUTPUT.PUT_LINE('结果是:'||TO_CHAR(result));--输出结果
EXCEPTION
WHEN OTHERS THEN--捕获其他异常
DBMS_OUTPUT.PUT_LINE('发生异常:'||SQLERRM);
end;
异常处理函数
异常处理函数用于取得Oracle错误号和错误信息,其中函数SQLCODE用于取得错误号,SQLERRM用于取得错误信息
二、存储过程编程
1、存储过程的概念
procedure 存储过程,存储在数据库中的不带返回值的命名PL/sql程序块,(可以反复使用复杂操作,并提高执行效率)
2、存储过程的语法
create or replace procedure procedureName(parm parmType)
as
local varible defination
begin
procedure body;
end;
3、编写求u1+2+...+100的结果并显示的存储过程
create or replace procedure sp_calcSum as begin ... end;
CREATE OR REPLACE PROCEDURE sp_Sum
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
begin
...
end;
CREATE OR REPLACE PROCEDURE sp_Sum(pEndNumber INT)
AS
result INT;
BEGIN
result:=0;
FOR 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
as
local varible defination
begin
function body
return result;
end;
3、编写求1+2+...+100的函数
create or replace function f_calcSum return int
as
begin
...
return result;
end;
CREATE OR REPLACE FUNCTION f RETURN INT
AS
result INT;
BEGIN
FOR n IN 1..100 LOOP
result:=result+n;
end loop;
RETURN result;
END;
4、编写求1+2+...+n的函数
create or replace function f_calcSum(pEndNumber int) return int
as
begin
....
return result;
end;
CREATE OR REPLACE FUNCTION f(pEndNumber INT) RETURN INT
AS
result INT;
BEGIN
FOR n IN 1..pEndNumber LOOP
result:=result+n;
end loop;
RETURN result;
end;
四、存储过程与函数的应用
1、编写将指定部门号的所有员工薪水增加指定值的存储过程,并调用此存储过程将30号部门的薪水增加1000
编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)
调用存储过程将30号部门的薪水增加1000元 execute sp_AlterSalByDeptno(1000,30)
与使用update语句进行对比
--首先,创建一个员工表,包含工号和薪水
create table employees(
department_Id int,
salary int
);
--然后,创建存储过程
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
avg_sal number;
begin
select avg(salary) into avg_sal --将计算的平均薪水存入avg_sal
from EMPLOYEES where DEPARTMENT_ID=pDeptno;
return avg_sal;
exception
when NO_DATA_FOUND THEN
return NULL;
end;
--调用存储过程
declare
Sal number;
begin
Sal:=f_GetAvgSalByDeptno(30);
if Sal is not null then
DBMS_OUTPUT.PUT_LINE('部门30的平均薪水是:'||to_char(Sal));
else
DBMS_OUTPUT.PUT_LINE('部门30没有员工');
end if;
end;
3、结论
需要频繁重复的数据库操作通常会编制专门的存储过程或函数
存储过程应用:
先创建存储过程(编写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
可以在此段代码执行前后分别获取当前时间,检查其两种方式所用时间的差别