过程与函数

准备工作:

(1)解锁HR用户:

alter user hr account unlock;

password hr

(2)以hr用户登录:

conn hr/admin

show user

(3)打开服务器端信息显示

set serveroutput on ;

2、存储过程:

(1)存储过程的创建

基本语法:

create [or replace] procedure procedure_name (parameter1_name[mode] datatype [default|:=value]  ,(parameter2_name[mode] datatype    [default|:=value] )

as|is

begin

exception

end;

model(参数模式的选择in、out、in out)

in:(默认参数模式)函数被调用时实参值传递给形参,过程内形参起常量作用,只能读不能写

out:过程被调用时,实参被忽略,形参起到PL/SQL变量的作用,可读可写

in out :函数被调用时,实参传值给形参,过程中,形参起到已初始化的变量的作用,可读可写

显示错误信息:

show error

(2)存储过程的调用

execute 存储过程名(参数)

(3)存储过程的删除

drop procedure procedure_name;

(4)案例

创建一个存储过程,以部门号为参数,查询该部门平均工资,并输出该部门中比平均工资高的员工号,员工名

CREATE OR REPLACE PROCEDURE PRO_EMP_SAL(p_deptno EMPLOYEES.DEPARTMENT_ID%TYPE)

AS

  v_sal EMPLOYEES.SALARY%TYPE;

BEGIN

  SELECT AVG(EMPLOYEES.SALARY) INTO v_sal

  FROM EMPLOYEES

  WHERE EMPLOYEES.DEPARTMENT_ID=p_deptno;

  dbms_output.put_line(‘部门号:’||p_deptno||' '||’平均工资:’||v_sal||CHR(10));

  for v_emp in (select * from employees where department_id=p_deptno and employees.salary>v_sal)

  LOOP

    dbms_output.put_line('员工号:'||v_emp.employee_id||'员工名:'||v_emp.first_name);

  END LOOP;

EXCEPTION

  WHEN NO_DATA_FOUND THEN dbms_output.put_line('WRONG');

END;

 

 

3、函数:

(1)函数的创建

create (or replace) function function_name(参数 [mode] 参数类型(%type))

return 变量类型

as|is

begin

exception

end;

注意事项:

函数列表之后,必须有一个return语句指明函数返回值的类型,不能约束返回值的精度、长度等,如果使用%type可以隐含的包括长度、精度、刻度等约束信息

(2)函数的调用

在SQL语句中调用:

在PL/SQL中调用函数(函数只能作为表达式的一部分使用)

 

(3)函数的删除

drop function function_name;

(4)案例

创建一个函数,以员工编号为参数,返回员工工资

create or replace function func_emp_salary(p_empno employee_id%type)

         return employees.salary%type

as

         v_sal employees.salary%type;

begin

 select salary into v_sal%type

from employees

where employee_id = p_empno;

return v_sal;

exception

 when no_data_found then

 raise _application_error(-2000,’There is no such an employee !’);

end;

调用:

declare v_salary employees.salary%type;

begin v_salary :=func_emp_salary(100);

dbms_output.put_line(‘salary’||v_salary);

end;

 

 

4、总结:

函数和存储过程的相关题目解题思路:

(1)找出题目中需要的变量和参数

参数的确定在变量之后,先找出需要的变量,再找变量,变量能输出(能通过into给其赋值),能传值,而参数不能做运算,只能用于函数调用时传值使用

(2)理清sql语句中逻辑关系,查询最终结果需要先查什么,后查什么,怎样嵌套

(3)记清过程和函数的一般结构:

create procedure(function) procedure_name(function_name) (参数名 [in / out / in out] 参数类型%type)

(函数 return 变量类型)

as|is

begin

exception

end;

feihu分享到:

          

You may also like...

Scroll Up