Oracle系列：（29）存儲(chǔ)過程和存儲(chǔ)函數(shù)

1、存儲(chǔ)過程【procedure】

什么是存儲(chǔ)過程？

事先運(yùn)用oracle語法寫好的一段具有業(yè)務(wù)功能的程序片段，長期保存在oracle服務(wù)器中，供oracle客戶端（例如，sqlplus）和程序語言遠(yuǎn)程訪問，類似于Java中的函數(shù)。

為什么要用存儲(chǔ)過程？

    （1）PLSQL每次執(zhí)行都要整體運(yùn)行一遍，才有結(jié)果

    （2）PLSQL不能將其封裝起來，長期保存在oracle服務(wù)器中

    （3）PLSQL不能被其它應(yīng)用程序調(diào)用，例如：Java

存儲(chǔ)過程與PLSQL是什么關(guān)系？

存儲(chǔ)過程是PLSQL的一個(gè)方面的應(yīng)用，而PLSQL是存儲(chǔ)過程的基礎(chǔ)。

即存儲(chǔ)過程需要用到PLSQL。

--------------------------------------------------------存儲(chǔ)過程

語法：

create [or replace] procedure 過程名[(參數(shù)列表)]  
as
PLSQL程序體；

注意：存儲(chǔ)過程中有【begin…end;/】，無declare

創(chuàng)建無參存儲(chǔ)過程hello，無返回值，語法：create or replace procedure 過程名 as PLSQL程序

create or replace procedure hello
as
begin
       dbms_output.put_line('這是我的第一個(gè)存儲(chǔ)過程'); 
end;
/

刪除存儲(chǔ)過程hello，語法：drop procedure 過程名

drop procedure hello;

調(diào)用存儲(chǔ)過程方式一，exec 存儲(chǔ)過程名

exec hello;

調(diào)用存儲(chǔ)過程方式二，PLSQL程序

begin
  hello;  
end;
/

調(diào)用存儲(chǔ)過程方式三，Java程序

JDBC中講過一個(gè)對(duì)象：CallableStatement

創(chuàng)建有參存儲(chǔ)過程raiseSalary(編號(hào))，為7369號(hào)員工漲10%的工資，演示in的用法，默認(rèn)in，大小寫不敏感

-- 定義過程
create or replace procedure raiseSalary(pempno number)
as
begin
  update emp set sal=sal*1.2 where empno=pempno;
end;
/

-- 調(diào)用過程
exec raiseSalary(7369);

創(chuàng)建有參存儲(chǔ)過程findEmpNameAndSalAndJob(編號(hào))，查詢7788號(hào)員工的的姓名，職位，月薪，返回多個(gè)值，演示out的用法

-- 定義過程
create or replace procedure findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
  select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/
-- 調(diào)用過程
declare
   pename emp.ename%type;
   pjob emp.job%type;
   psal emp.sal%type;
begin
  findEmpNameAndSalAndJob(7369,pename,pjob,psal);
  dbms_output.put_line('7369號(hào)員工的姓名是' || pename ||'，職位是' || pjob || '，月薪是' || psal);
end;
/

什么情況下用exec調(diào)用，什么情況下用PLSQL調(diào)用存儲(chǔ)過程？

exec適合于調(diào)用存儲(chǔ)過程無返回值

plsql適合于調(diào)用存儲(chǔ)過程有返回值，不管多少個(gè)

用存儲(chǔ)過程，寫一個(gè)計(jì)算個(gè)人所得稅的功能

-- 定義存儲(chǔ)過程
create or replace procedure get_rax(sal in number,rax out number)
as
  -- sal表示收入
  -- bal 表示需要交稅的收收入
  bal number;
begin
  bal := sal - 3500;
  if bal <= 1500 then
    rax := bal * 0.03 - 0;
  elsif bal <= 4500 then
    rax := bal * 0.1 - 105;
  elsif bal <=9000 then
    rax := bal * 0.2 - 555;
  elsif bal <=35000 then
    rax := bal * 0.25 - 1005;
  elsif bal <= 55000 then
    rax := bal * 0.3 - 2755;
  elsif bal <=80000 then
    rax := bal * 0.35 - 5505;
  else 
    rax := bal * 0.45 - 13505;
  end if;
end;
/


-- 調(diào)用存儲(chǔ)過程
declare
   -- 需要交的稅
   rax number;
begin
   get_rax(&sal,rax);
   dbms_output.put_line('你需要交稅' || rax);     
end;
/

2、存儲(chǔ)函數(shù)

創(chuàng)建無參存儲(chǔ)函數(shù)getName，有返回值，語法：create or replace function 函數(shù)名 return 返回類型 as PLSQL程序段

create or replace function get_name return varchar2
as 
begin
       return 'hello 你好';  
end;
/

刪除存儲(chǔ)函數(shù)getName，語法：drop function 函數(shù)名

drop function get_name;

調(diào)用存儲(chǔ)函數(shù)方式一，PLSQL程序

declare
  name varchar2(20);
begin
  name := get_name(); 
  dbms_output.put_line(name); 
end;
/

調(diào)用存儲(chǔ)函數(shù)方式二，Java程序

創(chuàng)建有參存儲(chǔ)函數(shù)findEmpIncome(編號(hào))，查詢7369號(hào)員工的年收入，演示in的用法，默認(rèn)in

-- 定義存儲(chǔ)函數(shù)
create or replace function findEmpIncome(pempno in number) return number
as
       income number; 
begin
  select sal*12+NVL(comm,0) into income from emp where empno=pempno;
  return income;
end;
/
-- 調(diào)用存儲(chǔ)函數(shù)
declare
   income number;
begin
   income := findEmpIncome(&income);
   dbms_output.put_line('該員工的年收入為' || income);
end;
/

創(chuàng)建有參存儲(chǔ)函數(shù)findEmpNameAndJobAndSal(編號(hào))，查詢7788號(hào)員工的的姓名(return)，職位(out)，月薪(out)，返回多個(gè)值

-- 定義存儲(chǔ)函數(shù)
create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2, psal out number) return varchar2
as
       pename emp.ename%type;
begin
       select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
       return pename;       
end;
/

-- 調(diào)用存儲(chǔ)函數(shù)
declare
   pename emp.ename%type;
   pjob emp.job%type;
   psal emp.sal%type;
begin
   pename := findEmpNameAndJobAndSal(&empno,pjob,psal);
   dbms_output.put_line('7369號(hào)員工的姓名是'|| pename ||'，職位是'|| pjob || '，月薪是' || psal);
end;
/

3、存儲(chǔ)過程和存儲(chǔ)函數(shù)的適合場(chǎng)景

注意：適合不是強(qiáng)行要使用，只是優(yōu)先考慮

什么情況下【適合使用】存儲(chǔ)過程？什么情況下【適合使用】存儲(chǔ)函數(shù)？

    【適合使用】存儲(chǔ)過程：無返回值 或 有多個(gè)返回值時(shí)，適合用過程 

    【適合使用】存儲(chǔ)函數(shù)：有且只有一個(gè)返回值時(shí)，適合用函數(shù)

什么情況【適合使用】過程函數(shù)，什么情況【適合使用】SQL？

    【適合使用】過程函數(shù)：

    》需要長期保存在數(shù)據(jù)庫中

          》需要被多個(gè)用戶重復(fù)調(diào)用

          》業(yè)務(wù)邏輯相同，只是參數(shù)不一樣

    》批操作大量數(shù)據(jù)，例如：批量插入很多數(shù)據(jù)

    【適合使用】SQL：

    》凡是上述反面，都可使用SQL

    》對(duì)表，視圖，序列，索引，等這些還是要用SQL 

批量添加操作示例：

-- 定義過程
create or replace procedure batchInsert
as
       i number(4) := 1;
begin
       for i in 1..999
           loop
             insert into emp(empno,ename) values(i,'員工'||i);
           end loop; 
end;
/

-- 調(diào)用過程
exec batchInsert;