(Oracle)儲存過程、儲存函式和包的相關知識與例項

儲存過程(procedure)

有具體的例項來學習

例項1

create or replace procedure  dept_count   --建立儲存過程dept_count來統計部門個數

as

v_total number(10);

begin

 select count(*) into v_total from dept;

 dbms_output.put_line('總共有'||v_total||'個部門。');

end;

將以上程式碼在sql*plus中編譯，一旦編譯成功，就被儲存在資料庫中，可以被其他有許可權的使用者或程式來呼叫執行。

儲存過程的執行有兩種方法：

<1>

set serveroutput  on

execute dept_count;

<2>

set serveroutput  on

begin

 dept_count;

end;

例項2

create or replace procedure dept_list   --建立儲存過程dept_list

as

cursor dept_coursor is select  deptno,dname from dept;

v_deptno  dept.deptno%type;

v_dname   dept.dname%type;

begin

 open dept_cursor;

 loop

  fetch dept_cursor into v_deptno,v_dname;

  exit when dept_cursor%notfound;

  dbms_output.put_line(v_deptno||'----'||v_dname);

 end loop;

 close dept_cursor;

 dept_count;                      --在儲存過程dept_list中呼叫儲存過程dept_count

end;

例項3(帶in 型別的引數)

create or replace procedure  change_sal(p_empno in number default 7788,p_raise in number default 50) --建立儲存過程

as

v_ename emp.ename%type;

v_sal   emp.sal%type;

begin

 update emp set sal=sal+p_raise where empno=p_empno;

 select ename,sal into v_ename,v_sal from emp where empno=p_empno;

 dbms_output.put_line('僱員'||v_ename||'的工資被改為'||v_sal);

 commit;

end;

例項4(帶out 型別的引數)

建立並編譯儲存過程

create or replace procedure check_sal(p_empno in number,p_sal out number)

as

begin

 select sal into p_sal from emp where empno=p_empno;

end;

執行儲存過程

set serveroutput on

declare

v_sal emp.sal%type;

v_empno emp.empno%type;

begin

 check_sal(7788,v_sal);         --呼叫儲存過程check_sal

 v_empno:=7788;

 dbms_output.put_line('僱員'||v_empno||'的工資為'||v_sal);

end;

例項5(帶in out 型別的引數)

建立並編譯儲存過程

create or replace procedure  change_phone(p_phone in out varchar2)

as

begin

p_phone:='010-'||p_phone;

end;

執行儲存過程

set serveroutput on

declare

 v_phone varchar2(20);

begin

 v_phone:=88886666;

 change_phone(v_phone);        --呼叫儲存過程change_phone

 dbms_output.put_line('修改後的電話號碼為：'||v_phone);

end;

其他知識(以儲存過程dept_list為例)

重新編譯儲存過程的用法：alter  procedure dept_list compile;

檢視儲存過程的引數：describe dept_list

通過資料字典user_source查詢儲存過程的指令碼：select text from user_source where name='DEPT_LIST';

通過資料字典user_objects查詢儲存過程的有效性：select status from user_objects where object_name='DEPT_LIST';

通過資料字典user_dependencies查詢儲存過程的依賴性：select * from user_dependencies where name='DEPT_LIST';

發生編譯錯誤時用show errors; 指令可以顯示錯誤的詳細資訊。

刪除儲存過程：drop proceduredept_list;

儲存函式(function)

例項

建立並編譯函式

create or replace function get_dept_dname(P_deptno in number default 10) --儲存函式的引數型別只有in 型別

return varchar2                                                          --返回值是必須的                

as

v_dname dept.dname%type;

begin

 select dname into v_dname from dept where deptno=p_deptno;

 return(v_dname);

end;

呼叫函式

set serveroutput on

declare

 v_no dept.deptno%type;

 v_name dept.dname%type;

begin

 v_no:=20;

 v_name:=get_dept_dname(v_no);             --呼叫儲存函式

 dbms_output.put_line(v_no||'號部門的名稱是：'||v_name);

end;

其他知識

其他的知識和儲存過程是很類似的，自己根據儲存過程的知識做一下嘗試。

包(package)

包分為包頭和包體，包頭和包體可以分開編譯，也可以一起編譯；當分開編譯時，要先編譯包頭，後編譯包體；一起編譯時，包頭在前，包體在後，它們中間用”/”隔開。

例項

建立和編譯package

create or replace package emp_package       --建立包頭

is

procedure  emp_count;

function  get_sal(p_empno in number) return number;

end emp_package;

/                                         

create or replace package body  emp_package   --建立包體

is

/*儲存過程*/

procedure  emp_count

as

v_count number(10);

begin

 select count(*) into v_count from emp;

 dbms_output.put_line('僱員表中的總人數是：'||v_count);

end emp_count;

/*儲存函式*/

function  get_sal(p_empno in number)

return number

as

v_sal emp.sal%type;

begin

 select sal into v_sal from emp where empno=p_empno;

 return(v_sal);

end get_sal;

end emp_package;

執行package

set serveroutput on

declare

v_empno emp.empno%type;

v_sal   emp.sal%type;

begin

 v_empno:=7788;

 v_sal:=emp_package.get_sal(v_empno);     --呼叫儲存函式

 dbms_output.put_line('僱員'||v_empno||'的工資為'||v_sal);

 emp_package.emp_count;                    --呼叫儲存過程

end;