实训报告6：PL/SQL高级编程

实训 PL/SQL高级编程

实验目的：
1、掌握PL/SQL的数据结构和编程结构，掌握应用PL/SQL编写简单程序的方法
2、理解存储过程的概念，掌握编写存储过程的方法
3、理解函数的概念，掌握编写存储过程的方法
实训内容：

目录

实训 PL/SQL高级编程

一、PL/SQL编程基础

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

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

二、存储过程编程

4、编写求1+2+...+n的带参数的存储过程create or replace procedure sp_calcSum(pEndNumber int) asbegin...end;代码：create or replace procedure sp_calcSum as    n int:=1;    result int:=0;begin    while n<=100 loop        result:=result+n;        n:=n+1;    end loop;    dbms_output.put_line('结果是'||result);end;​编辑三、函数编程

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

2、函数的语法create or replace function functionName(parm parmType) return resultTypeaslocal varible definationbeginfunction bodyreturn result;end;3、编写求1+2+...+100的函数create or replace function f_calcSum() return intasbegin...return result;end;代码：create or replace procedure sp_calcSum(n int) as    i int:=1;    result int:=0;begin    while i<=n loop        result:=result+i;        i:=i+1;    end loop;    dbms_output.put_line('结果是'||result);end;​编辑

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

---

一、PL/SQL编程基础

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

（1）说明部分：说明部分是可选的。由关键字DECLARE引出，用于定义常量、变量、游标、异常、复杂数据类型。在编写程序时，程序块中引用的数据对象和程序单元应遵循先定义后使用的原则。
    （2）执行部分：执行部分是必需的。由关键字BEGIN开始，至END结束。PL/SQL程序块至少包含一条可执行语句，也可以嵌套其他PL/SQL程序块。
    （3）异常处理部分：异常处理部分是可选的。由关键字EXCEPTION开始。当执行部分发生错误时，将会引起异常。这时，正常的执行将被停止且转移到异常程序处理。异常处理完成后，将结束对应PL/SQL块的执行。

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

代码：

declare
	n int:=1;
	result int:=0;
begin
	while n<=100 loop
		result:=result+n;
		n:=n+1;
	end loop;
	dbms_output.put_line('结果是'||result);
end;

二、存储过程编程

1、存储过程的概念
存储过程的英文是 Stored Procedure。它的思想很简单，就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。

2、存储过程的语法
create or replace procedure procedureName(parm parmType) 
as
local varible defination
begin
procedure body;
end;

存储过程的定义：
CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    需要执行的语句
END

3、编写求u1+2+...+100的结果并显示的存储过程
create or replace procedure sp_calcSum 
as
begin
...
end;

代码：

create or replace procedure sp_calcSum 
as
    n int:=1;
    result int:=0;
begin
    while n<=100 loop
        result:=result+n;
        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;
代码：

create or replace procedure sp_calcSum 
as
    n int:=1;
    result int:=0;
begin
    while n<=100 loop
        result:=result+n;
        n:=n+1;
    end loop;
    dbms_output.put_line('结果是'||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 procedure sp_calcSum(n int) 
as
    i int:=1;
    result int:=0;
begin
    while i<=n loop
        result:=result+i;
        i:=i+1;
    end loop;
    dbms_output.put_line('结果是'||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(n int) return int
as
    i int:=1;
    result int:=0;
begin
    while i<=n loop
        result:=result+i;
        i:=i+1;
    end loop;
    return result;
end;

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

1、编写将指定部门号的所有员工薪水增加指定值的存储过程，并调用此存储过程将30号部门的薪水增加1000
编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)
调用存储过程将30号部门的薪水增加1000元 execute sp_AlterSalByDeptno(1000,30)
与使用update语句进行对比
代码:

create or replace procedure sp_AlterSalByDeptno(pSalDelta float,pDeptno number)
as
begin
    update emp set sal=sal+pSalDelta where deptno = pDeptno;
end;
 
 
exec sp_AlterSalByDeptno(1000,30);

2、编写求指定部门号的所有员工平均薪水的函数，并调用此函数计算30号部门的平均薪水
编写函数 f_GetAvgSalByDeptno(pDeptno)
调用函数求出30号部门的平均薪水并显示tempSal:=f_GetAvgSalByDeptno(30)
与使用select语句进行对比
代码：

create or replace function f_getAlterSalByDeptno(pDeptno number) return float
as
    result float:=0;
begin
    select avg(sal) into result from emp where deptno = pDeptno;
    return result;
end;
 
 
declare
    temp float:=0;
begin
    temp:=f_getAlterSalByDeptno(10);
    dbms_output.put_line('该部门的所有员工平均薪水是:'||to_char(temp,999999.99));
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

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

g感谢您的观看