PL/SQL高级编程

实验目的：
 1、掌握PL/SQL的数据结构和编程结构，掌握应用PL/SQL编写简单程序的方法
 2、理解存储过程的概念，掌握编写存储过程的方法
 3、理解函数的概念，掌握编写存储过程的方法
实验内容：
一、PL/SQL编程基础
1、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) 
as
 local varible defination
begin
 procedure body;
end;

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

create or replace procedure sp_calcSum 
as
 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;
/

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

create or replace procedure sp_calcSum1(pEndNumber int) 
as
 n int;
 result int;
begin
 n:=0;
 result:=0;
 while n<=pEndNumber loop
 result:=result+n;
 n:=n+1;
 end loop;
 dbms_output.put_line('结果是'||result);
end;
/
exec sp_calcSum1(10)

三、函数编程
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
 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);
 return result;
end;
/

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

create or replace function f_calcSum(pEndNumber int) return int
as
 n int;
 result int;
begin
 n:=0;
 result:=0;
 while n<=pEndNumber loop
 result:=result+n;
 n:=n+1;
 end loop;
 dbms_output.put_line('结果是'||result);
 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 in number,pDeptno in number)
as 
begin 
 update emp set sal=sal+pSalDelta where deptno=pDeptno;
 end;
 /
 
 exec sp_AlterSalByDeptno(1000,30); //调用
 
 commit;

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

create or replace function f_AvgSalByDeptno(pDeptno number)return number
as 
sal_avg number(6,2);
begin 
 select avg(sal) into sal_avg from emp where deptno=pDeptno;
 return sal_avg;
 end;
 /

commit;
set serveroutput on; //函数result转存并输出
var tempSal number;
call f_AvgSalByDeptno(30) into:tempSal
print tempSal;

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

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