Oracle系列：（29）預存程序和儲存函數

標籤：oracle

1、預存程序【procedure】

什麼是預存程序？

事先運用oracle文法寫好的一段具有業務功能的程式片段，長期儲存在oracle伺服器中，供oracle用戶端（例如，sqlplus）和程式語言遠端存取，類似於Java中的函數。

為什麼要用預存程序？

    （1）PLSQL每次執行都要整體運行一遍，才有結果

    （2）PLSQL不能將其封裝起來，長期儲存在oracle伺服器中

    （3）PLSQL不能被其它應用程式調用，例如：Java

預存程序與PLSQL是什麼關係？

預存程序是PLSQL的一個方面的應用，而PLSQL是預存程序的基礎。

即預存程序需要用到PLSQL。

--------------------------------------------------------預存程序

文法：

create [or replace] procedure 過程名[(參數列表)]  asPLSQL程式體；

注意：預存程序中有【begin…end;/】，無declare

建立無參預存程序hello，無傳回值，文法：create or replace procedure 過程名 as PLSQL程式

create or replace procedure helloasbegin       dbms_output.put_line(‘這是我的第一個預存程序‘); end;/

刪除預存程序hello，文法：drop procedure 過程名

drop procedure hello;

調用預存程序方式一，exec 預存程序名

exec hello;

調用預存程序方式二，PLSQL程式

begin  hello;  end;/

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/87/22/wKioL1fVT4DQUYZJAABzyHYtUy4172.jpg" title="001.jpg" alt="wKioL1fVT4DQUYZJAABzyHYtUy4172.jpg" />

調用預存程序方式三，Java程式

JDBC中講過一個對象：CallableStatement

建立有參預存程序raiseSalary(編號)，為7369號員工漲10%的工資，示範in的用法，預設in，大小寫不敏感

-- 定義過程create or replace procedure raiseSalary(pempno number)asbegin  update emp set sal=sal*1.2 where empno=pempno;end;/-- 調用過程exec raiseSalary(7369);

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/87/24/wKiom1fVUOXQv5CLAAB9nNBe5-A878.jpg" title="002.jpg" alt="wKiom1fVUOXQv5CLAAB9nNBe5-A878.jpg" />

建立有參預存程序findEmpNameAndSalAndJob(編號)，查詢7788號員工的的姓名，職位，月薪，返回多個值，示範out的用法

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

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/87/24/wKiom1fVWY7Aidi_AADctoOCqio886.jpg" title="003.jpg" alt="wKiom1fVWY7Aidi_AADctoOCqio886.jpg" />

什麼情況下用exec調用，什麼情況下用PLSQL調用預存程序？

exec適合於調用預存程序無傳回值

plsql適合於調用預存程序有傳回值，不管多少個

用預存程序，寫一個計算個人所得稅的功能

-- 定義預存程序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;/-- 調用預存程序declare   -- 需要交的稅   rax number;begin   get_rax(&sal,rax);   dbms_output.put_line(‘你需要交稅‘ || rax);     end;/

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M00/87/22/wKioL1fVX6mBSFdJAAC0QtxHYjs076.jpg" title="004.jpg" alt="wKioL1fVX6mBSFdJAAC0QtxHYjs076.jpg" />

2、儲存函數

建立無參儲存函數getName，有傳回值，文法：create or replace function 函數名 return 傳回型別 as PLSQL程式段

create or replace function get_name return varchar2as begin       return ‘hello 你好‘;  end;/

刪除儲存函數getName，文法：drop function 函數名

drop function get_name;

調用儲存函數方式一，PLSQL程式

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

調用儲存函數方式二，Java程式

建立有參儲存函數findEmpIncome(編號)，查詢7369號員工的年度營收，示範in的用法，預設in

-- 定義儲存函數create or replace function findEmpIncome(pempno in number) return numberas       income number; begin  select sal*12+NVL(comm,0) into income from emp where empno=pempno;  return income;end;/-- 調用儲存函數declare   income number;begin   income := findEmpIncome(&income);   dbms_output.put_line(‘該員工的年度營收為‘ || income);end;/

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/87/25/wKiom1fVY__jL_zbAACZPDIPKxs205.jpg" title="005.jpg" alt="wKiom1fVY__jL_zbAACZPDIPKxs205.jpg" />

建立有參儲存函數findEmpNameAndJobAndSal(編號)，查詢7788號員工的的姓名(return)，職位(out)，月薪(out)，返回多個值

-- 定義儲存函數create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2, psal out number) return varchar2as       pename emp.ename%type;begin       select ename,job,sal into pename,pjob,psal from emp where empno=pempno;       return pename;       end;/-- 調用儲存函數declare   pename emp.ename%type;   pjob emp.job%type;   psal emp.sal%type;begin   pename := findEmpNameAndJobAndSal(&empno,pjob,psal);   dbms_output.put_line(‘7369號員工的姓名是‘|| pename ||‘，職位是‘|| pjob || ‘，月薪是‘ || psal);end;/

3、預存程序和儲存函數的適合情境

注意：適合不是強行要使用，只是優先考慮

什麼情況下【適合使用】預存程序？什麼情況下【適合使用】儲存函數？

    【適合使用】預存程序：無傳回值 或 有多個傳回值時，適合用過程 

    【適合使用】儲存函數：有且只有一個傳回值時，適合用函數

   

什麼情況【適合使用】過程函數，什麼情況【適合使用】SQL？

    【適合使用】過程函數：

    》需要長期儲存在資料庫中

          》需要被多個使用者重複調用

          》商務邏輯相同，只是參數不一樣

    》批操作大量資料，例如：批量插入很多資料

    【適合使用】SQL：

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

    》對錶，視圖，序列，索引，等這些還是要用SQL 

大量新增操作樣本：

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

Oracle系列：（29）預存程序和儲存函數