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程序模块化的一种体现。

 

 

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。