PL/SQL高级编程

一、PL/SQL编程基础

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

declare
    声明块
begin
    执行快/异常处理块
end;

声明块 （变量声明，常量声明，游标声明）

执行块   PL/SQL程序的主要部分，可以编写SQL查询、DML（数据操作语言）语句(insert,update,delete)、控制结构（如条件语句、循环语句）、过程调用

异常处理块   用于处理运行时错误，异常处理块以关键字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.loop是一个关键字，表示一个循环的开始，并且反复执行loop到end loop里面的代码直到不满足while循环里面的条件
                   2. || 是PL/SQL中的字符串连接操作符
                   3. := 表示赋值
 

二、存储过程编程

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 spcalcSum 
as
 decaler sum int
 sum:=0;
begin
  for i in 1..100 loop
    sum:=sum+i;
  end loop
  dbms_output.put_line('结果'||sum）
end;

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

CREATE OR REPLACE PROCEDURE spcalcSum(pEndNumber IN NUMBER)   
AS  
  v_sum NUMBER := 0; -- 声明一个变量来保存和  
BEGIN  
  -- 使用循环来计算1到pEndNumber的和  
  FOR i IN 1..pEndNumber LOOP  
    v_sum := v_sum + i;  
  END LOOP;  
   -- 显示结果  
  DBMS_OUTPUT.PUT_LINE('1到' || TO_CHAR(pEndNumber) || '的和是: ' || TO_CHAR(v_sum));  END;  

三、函数编程

1、函数的概念

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

--函数的示例

CREATE or replace FUNCTION Cal(salary number) return number
AS  
    taxRate number(5,2) := 0.15;
    tax number(10,2);
BEGIN  
     --declare用于声明变量，输入变量taxRate，类型DECIMAL(5,2)
    tax := salary * taxRate;  --输入变量tax，类型DECIMAL(10,2)
    RETURN salary + tax;  --返回值是@salary + @tax
END;  

-- 调用函数 
SELECT CAL(1000) FROM DUAL;

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 number
as
  result number :=0;
begin
    for i in 1..100 loop
        result :=result+i;
    end loop;
    return result;
end;
select f_calcSum() from dual;

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

create or replace function f_calcSum(pEndNumber int) return int
as
  result int :=0;
begin
    for i in 1..pEndNumber loop
        result :=result+i;
    end loop;
    return result;
end;
select f_calcSum(100) from dual;

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

1、编写将指定部门号的所有员工薪水增加指定值的存储过程，并调用此存储过程将30号部门的薪水增加1000
        

        编写存储过程 sp_AlterSalByDeptno(pSalDelta,pDeptno)
        调用存储过程将30号部门的薪水增加1000元    execute sp_AlterSalByDeptno(1000,30)
           

--编写存储过程    
    create or replace procedure  sp_AlterSalByDeptno(pSalDelta number,pDeptno number)    
    as
    begin
        update emp
        set sal=sal+pSalDelta
        where deptno=pDeptno;
    end;
--调用过程
   BEGIN  
         sp_AlterSalByDeptno(1000, 30);  
    END;  

select * from emp;

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

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

2.1

create or replace function f_GetAvgSalByDeptno (pDeptno number)
return number
as
    avgsum number:=0;
begin
    select avg(sal)
    into avgsum
    from emp
    where deptno=pDeptno;
    return avgsum;
end;

2.2

select
f_GetAvgSalByDeptno(30)
from dual;

3、结论

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

存储过程应用:

先创建存储过程（编写sql语句，将编写的代码编译后保存在数据库中，同时存储了编写的plsql语句和对应的编译后的机器操作指令）

再使用存储过程（直接调用机器操作指令）
sql语句：update emp set sal=sal+1000 where deptno=30;先检查sql是否正确，再转换成机器操作指令，最后执行机器操作

--对比： 此代码的作用就是把部门号为10，20，30所有的员工的工资给加一下    

第一种 : for deptno in(10,20,30) 
       loop
        execute alterSalBydeptno(delta,target); ---1  执行存储过程alterSalBydeptno，每次只需直接执行
        update emp

        set sal=sal+delta
        where deptno=target;           ---2  每次执行都要检查、转换、执行
        DeptnoMoveNext();               --deptnomovenext表示部分号下一个
      end loop

   第二种： create or replace procedure  AlterSalByDeptno(pSalDelta number,pDeptno number)    
    as
    begin
        update emp
        set sal=sal+pSalDelta
        where deptno=pDeptno;
    end;