PL/SQL高级编程

实验目的:

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

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

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

实训内容:

一、PL/SQL编程基础

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

DECLARE  -- 声明块     

BEGIN  -- 执行块

EXCEPTION  -- 异常处理块    

END;  

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) 
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_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

begin

...

end;

CREATE OR REPLACE PROCEDURE sp_calcSum(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_caleSum() 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_calcSum(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

可以在此段代码执行前后分别获取当前时间,检查其两种方式所用时间的差别。

  • 19
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值