Oracle（8）

Oracle基础八

一、PL/SQL编程基础

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

声明块	执行块	异常处理块
declare	begin......end	while n loop......end loop

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

二、存储过程编程

1、存储过程的概念

procedure 存储过程，存储在数据库中的不带返回值的命名PL/sql程序块,（可以反复使用复杂操作，并提高执行效率）

2、存储过程的语法

     create or replace procedure procedureName(parm parmType) 
     as
     local varible defination
     begin
     procedure body; 
     end;

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

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

三、函数编程

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的函数

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

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

1、编写将指定部门号的所有员工薪水增加指定值的存储过程，并调用此存储过程将30号部门的薪水增加1000
– 编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)
– 调用存储过程将30号部门的薪水增加1000元

 execute sp_AlterSalByDeptno(1000,30)

–与使用update语句进行对比

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

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
可以在此段代码执行前后分别获取当前时间，检查其两种方式所用时间的差别.

五、实例应用代码如下

1.编制代码实现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;
 
 
2. 编写求u1+2+...+100的结果并显示的存储过程
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;  


3.编写求1+2+...+n的带参数的存储过程
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;
 exec sp_calcSum(99);
 

4.编写求1+2+...+100的函数
create or replace function f_calcSum return int
 as
    i int:=1;
    result int:=0;
begin
    while i<101 loop
    result:=result+i;
    i:=i+1;
    end loop;
    return result;
end;
declare
begin
    dbms_output.put_line('结果是'||f_calcSum);
end;


5.编写求1+2+...+n的函数
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;

declare
    result int:=f_calcSum(10);
begin
    dbms_output.put_line('结果是'||result);
end;


6.编写将指定部门号的所有员工薪水增加指定值的存储过程，并调用此存储过程将30号部门的薪水增加1000：	
select sal,deptno from emp where deptno=30;
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);
select sal,deptno from emp where deptno=30;


7.编写求指定部门号的所有员工平均薪水的函数，并调用此函数计算30号部门的平均薪水：
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(30);  
    dbms_output.put_line('该部门的所有员工平均薪水是:'||to_char(temp,999999.99));  
end;