Oracle——PL/SQL高级编程


实验目的:

-- 1、掌握PL/SQL的数据结构和编程结构,掌握应用PL/SQL编写简单程序的方法
-- 2、理解存储过程的概念,掌握编写存储过程的方法
-- 3、理解函数的概念,掌握编写存储过程的方法

实验内容:

一、PL/SQL编程基础

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

declare/*定义变量*/
    predentNum number(4);
begin
    select empno into predentNum  from emp where ename = 'KING';
    dbms_output.put_line('boss的编号是:'||predentNum);
EXCEPTION
   when no_data_found then
        dbms_output.put_line('未找到指定的员工名');
end;

–value_error 预定义的异常
–porridge_too_hot 用户定义异常
–porridge_too_cold 用户定义异常

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——i中可以被m整除的数的和。

declare
    n int;
    i constant int:=100;
    m constant int :=3;
    result int;
begin
    n:=1;/*赋初值*/
    result:=0;
    while n<=i loop
    if mod(n,m)=0 then
        result:=result+n;
    end if;
        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、编写求1+2+…+100的结果并显示的存储过程

create or replace procedure GetSum
as
    n int;
    i constant int:=100;
    m constant int :=3;
    result int;
begin
    n:=1;/*赋初值*/
    result:=0;
    while n<=i loop
    if mod(n,m)=0 then
        result:=result+n;
    end if;
        n:=n+1;
    end loop;
    dbms_output.put_line('结果是'||result);
end;

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

create or replace procedure sp_calcSum(pEndNumber int)
as
begin
    ...
end;

in 类型参数:输入参数、只能接收值,不能在执行块过程中修改
out 类型参数:输出参数、不能接收值,只能在执行块中被修改后输出
in out 类型参数,输入输出参数,可以接收值,也可以在执行块中被修改后输出

三、函数编程

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;

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

create or replace function f_calcSum(pEndNumber int) return int
as
begin
    ....
    return result;
end;
execute getSum(100,3);
begin
    getSum(100,3);
end;


create or replace function func_getsum(m int ,n int)return long
as
    summ number(8):=0 ;
    i int default 1;

begin
    while i<=n loop
        if mod(i,m)=0 then
            summ:=summ+i;
        end if;
        i:=i+1;
    end loop;
    return summ;
end;
declare
    mysum number(8):=0;
begin
    mysum:= func_getsum(4,100)+func_getsum(3,100);
    dbms_output.putline('reuslt is'||mysum);
end;

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

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 emp set sal = sal+pSalDelta where deptno = pDeptno;
end;

select * from emp where deptno = 10;

execute sp_AlterSalByDeptno(-200,10);

select * from emp where deptno = 10;

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

– 编写函数 f_GetAvgSalByDeptno(pDeptno)
– 调用函数求出30号部门的平均薪水并显示tempSal:=f_GetAvgSalByDeptno(30)
– 与使用select语句进行对比

create or replace function f_getAvgSalBuDeptno(pDeptno number) return float
as
    result float;
begin

    select avg(sal) into result from emp where deptno = 10;
    return result;

end;
declare
    temp float:=0;
begin
    temp := f_getAvgSalBuDeptno(10);
    DBMS_OUTPUT.PUT_LINE(to_char(temp,99999.99));
end;

3、结论

a、需要频繁重复的数据库操作通常会编制专门的存储过程或函数

b、存储过程应用: 先创建存储过程(编写sql语句,将编写的代码编译后保存在数据库中,同时存储了编写的plsql语句和对应的编译后的机器操作指令),再使用存储过程(直接调用机器操作指令)
c、sql语句:update emp set sal=sal+1000 where deptno=30;先检查sql是否正确,再转换成机器操作指令,最后执行机器操作
d、对比:
while deptno in(10,20,30) loop
    execute alterSalBydeptno(delta,target);
    update emp set sal=sal+delta where deptno=target;
    DeptnoMoveNext();
 end loop
e、可以在此段代码执行前后分别获取当前时间,检查其两种方式所用时间的差别
create or replace package pkg_salManage
as
    procedure sp_AlterSalByDeptno(psalDelta float, pDeptno number);
    function f_GerAvgSalByDeptno(pDeptno number) return float;
end;
create or replace package body pkg_salManage
as
    procedure sp_AlterSalByDeptno(psalDelta float, pDeptno number)
    as
    begin
        update emp set sal = sal+pSalDelta where deptno = pDeptno;
        commit;
    end;

    function f_GerAvgSalByDeptno(pDeptno number) return float
    as
        result float;
    begin
        select avg(sal) into result from emp where deptno = 10;
        return result;
    end;
end;

声明:May丶乘剑的部落小阁|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - Oracle——PL/SQL高级编程


一个偶尔努力、偶尔懈怠的"搬砖"人