oracle-過程函數編程

標籤：

過程函數編程

實踐目的

　　(1) 掌握預存程序、儲存函數進階資料庫物件的基本作用。

　　(2) 掌握預存程序、儲存函數的建立、修改、查看、刪除操作。

實踐要求

　　(1) 記錄執行命令和操作過程中遇到的問題及解決方案，注意從原理上解釋原因。

　　(2) 記錄利用企業管理器管理預存程序、儲存函數、觸發器的方法。

　　(3) 記錄利用SQL*Plus和PLSQL DEVELOPER管理預存程序、儲存函數的命令。

實踐內容

　　下列任務中涉及的資料表是SCOTT使用者給出的表。

　　1．建立預存程序

(1) 擷取某部門僱員的姓名和工資。　　

(2) 擷取某部門的工資總和及部門名稱。

　　(3)輸出某個部門員工的姓名及工資檔次，5000以上高，3000-5000中等，2000-3000較低，2000以下低。

　　2．建立儲存函數

(1) 擷取某僱員的工資：　　

(2) 擷取某部門的工資總和。

(3) 輸出某個員工的姓名及工資檔次，5000以上高，3000-5000中等，2000-3000較低，2000以下低。

（4）計算指定部門的工資總和，並統計其中的職工數量。

實驗步驟：

下列任務中涉及的資料表是SCOTT使用者給出的表。

　　1．建立預存程序

(1) 擷取某部門僱員的姓名和工資。　

SQL> conn scott/tiger

已串連。

SQL> set serveroutput on;

SQL> create or replace procedure emp_table(e_deptno emp.deptno%type)

  2  as

  3       cursor e_cursor is

  4       select ename,sal from emp where deptno=e_deptno;

  5  begin

  6       for e_record in e_cursor loop

  7          dbms_output.put_line(e_record.ename||‘ ‘||e_record.sal);

  8       end loop;

  9  end;

 10  /

 

過程已建立。

 

SQL> exec emp_table(20);

SMITH 800

JONES 2975

SCOTT 3000

ADAMS 1100

FORD 3000

 

SQL> exec emp_table(10);

CLARK 2450

KING 5000

MILLER 1300

 

PL/SQL 過程已成功完成。

PL/SQL 過程已成功完成。

 

SQL> exec emp_table(30);

ALLEN 1600

WARD 1250

MARTIN 1250

BLAKE 2850

TURNER 1500

JAMES 950

 

PL/SQL 過程已成功完成。

 

SQL>

 

 

 

(2) 擷取某部門的工資總和及部門名稱。

 

 

SQL> create or replace procedure emp_1(e_deptno emp.deptno%type)

  2  as

  3       cursor emp_cur is

  4     select deptno,sum(sal) as e_sal from emp where deptno=e_deptno group by    deptno;

  5  begin

  6       for emp_record1 in emp_cur loop

  7       dbms_output.put_line(emp_record1.deptno||‘ ‘||emp_record1.e_sal);

  8       end loop;

  9  end;

 10  /

 

過程已建立。

 

SQL> exec emp_1(10);

10 8750

 

PL/SQL 過程已成功完成。

 

SQL> exec emp_1(20);

20 10875

 

PL/SQL 過程已成功完成。

 

SQL> exec emp_1(30);

30 9400

 

PL/SQL 過程已成功完成。

 

SQL> 

 

 

(3)輸出某個部門員工的姓名及工資檔次，5000以上高，3000-5000中等，2000-3000較低，2000以下低。

SQL> create or replace procedure empno_name_level

  2  (dnum in emp.empno%type,name out emp.ename%type,level out varchar2)

  3  as

  4  salary emp.sal%type;

  5  begin

  6     select sal,ename into salary,name from emp where dnum=empno;

  7     case

  8     when salary >5000 then level:=‘高‘;

  9     when salary between 3000 and 5000 then level:=‘中等‘;

 10     when salary between 2000 and 3000 then level:=‘較低‘;

 11     else  level:=‘低‘;

 12     end case;

 13  end;

 14  /

 

過程已建立。

 

SQL> declare

  2  level varchar2(15);

  3  name emp.ename%type;

  4  begin

  5  empno_name_level(‘7788‘,name,level);

  6  dbms_output.put_line(‘name:‘||name||‘ ‘||‘level:‘||level);

  7   end;

  8  /

name:SCOTT level:中等

 

PL/SQL 過程已成功完成。

 

 

 

　　2．建立儲存函數

(1) 擷取某僱員的工資：　　

 

SQL> create or replace function empno_sal(eno in emp.empno%type)

  2  return emp.sal%type

  3  as

  4  salary emp.sal%type;

  5  begin

  6     select sal into salary from emp where eno=empno;

  7     return salary;

  8  end;

  9  /

 

函數已建立。

 

SQL> begin

  2     dbms_output.put_line(‘salary:‘||emp_sal(‘7499‘));

  3  end;

  4  /

salary:1600

 

PL/SQL 過程已成功完成。

 

SQL>  begin

  2      dbms_output.put_line(‘salary:‘||emp_sal(‘7788‘));

  3  end;

  4  /

salary:3000

 

PL/SQL 過程已成功完成。

 

SQL>  begin

  2      dbms_output.put_line(‘salary:‘||emp_sal(‘7900‘));

  3  end;

  4  /

salary:950

 

PL/SQL 過程已成功完成。

   

 

(2) 擷取某部門的工資總和。

 

SQL> create or replace function salary_num(dno in emp.deptno%type)

  2  return emp.sal%type

  3  is

  4  total emp.sal%type;

  5  begin

  6     select sum(sal) into total from emp where         dno=deptno;

  7     return total;

  8  end;

  9  /

 

函數已建立。

 

SQL> begin

  2    dbms_output.put_line(‘total_salary:‘||total_salary(‘20‘));

  3  end;

  4  /

total_salary:10875

 

PL/SQL 過程已成功完成。

 

SQL> begin

  2    dbms_output.put_line(‘total_salary:‘||total_salary(‘10‘));

  3  end;

  4  /

total_salary:8750

 

PL/SQL 過程已成功完成。

 

SQL> begin

  2    dbms_output.put_line(‘total_salary:‘||total_salary(‘30‘));

  3  end;

  4  /

total_salary:9400

 

PL/SQL 過程已成功完成。

   

 

(3) 輸出某個員工的姓名及工資檔次，5000以上高，3000-5000中等，2000-3000較低，2000以下低。

 

SQL> create or replace function empno_name_level1(dnum in emp.empno%type,name out emp.ename%type)

  2  return varchar2

  3  as

  4  salary emp.sal%type;

  5  level varchar2(15);

  6  begin

  7     select sal,ename into salary,name from emp where                dnum=empno;

  8     case

  9     when salary >5000 then level:=‘高‘;

 10     when salary between 3000 and 5000 then level:=‘中等‘;

 11     when salary between 2000 and 3000 then level:=‘較低‘;

 12     else  level:=‘低‘;

 13     end case;

 14     return level;

 15  end;

 16  /

 

函數已建立。

 

SQL> declare

  2     name emp.ename%type;

  3     level varchar2(15);

  4  begin

  5     level:=empno_name_level1(‘7499‘,name);

  6     dbms_output.put_line(‘name:‘||name||‘ ‘||‘level:‘||level);

  7  end;

  8  /

name:ALLEN level:低

 

PL/SQL 過程已成功完成。

 

SQL> declare

  2     name emp.ename%type;

  3     level varchar2(15);

  4  begin

  5     level:=empno_name_level1(‘7788‘,name);

  6      dbms_output.put_line(‘name:‘||name||‘ ‘||‘level:‘||level);

  7   end;

  8   /

name:SCOTT level:中等

 

PL/SQL 過程已成功完成。

 

SQL>  declare

  2      name emp.ename%type;

  3      level varchar2(15);

  4   begin

  5      level:=empno_name_level1(‘7566‘,name);

  6      dbms_output.put_line(‘name:‘||name||‘ ‘||‘level:‘||level);

  7   end;

  8  /

name:JONES level:較低

 

PL/SQL 過程已成功完成。

 

 

（4）計算指定部門的工資總和，並統計其中的職工數量。

SQL> create or replace function salnum_empnonum(dnum in emp.deptno%type,total out emp.sal%type)

  2  return number

  3  as

  4  e_count number;

  5  begin

  6     select sum(sal),count(empno)

  7           into total,e_count from emp                        where deptno=dnum;

  8     return e_count;

  9  end;

 10  /

 

函數已建立。

 

SQL> declare

  2  total emp.sal%type;

  3  begin

  4  dbms_output.put_line(‘enum:‘||salnum_empnonum(‘10‘,total)||‘‘||‘total:‘||total);

  5  end;

  6  /

enum:3total:8750

 

PL/SQL 過程已成功完成。

 

 

 

實驗小結：

1. PL/SQL中的儲存子程式包括預存程序和(儲存)函數兩種。儲存子程式是以獨立對象的形式儲存在資料庫伺服器中，因此是一種全域結構，與之對應的是局部子程式，即嵌套在PL/SQL塊中的局部過程和函數，其儲存位置取決於其所在的父塊的位置。

2. 預存程序可以簡單的理解為一段可以執行某個活動/動作的子程式，可以作為一個系統對象被儲存在資料庫中，可以重複調用。

3. 通常，預存程序不需要返回值，如果需要返回一個值可以通過函數調用實現。但是，如果希望返回多個值，可以使用OUT或IN OUT模式參數來實現。

4. 在編譯過程中可能會產生錯誤，可以使用SHOW ERRORS來查看錯誤資訊。當前只有一個對象，要顯示錯誤可以用SHOW ERRORS,當有多個對象時，可以精確的指明要查看那個對象，例如此處可寫：SHOW ERRORS PROCEDURE myproc,同樣的要查看函數、包、觸發器的錯誤，用SHOW ERRORS FUNCTION/PACKAGE/TRIGER 名稱。

5. 儲存子程式是被命名的PL/SQL塊，以編譯的形式儲存在資料庫伺服器中，可以在應用程式中進行調用，是PL/SQL程式模組化的一種體現。

 

 

 

oracle-過程函數編程