plSQl function PROCEDURE 使用实例

Function 的使用

参数即为输入参数

使用return返回参数

格式CREATE OR REPLACE FUNCTION+函数名+（参数+类型）+RETURN 类型 IS+名称+类型+BEGIN+END 函数名;

CREATE ORREPLACEFUNCTION get_employee_salary(empoyee_idNUMBER)RETURN NUMBER IS

  salary NUMBER;

BEGIN

  SELECT e.salary

    INTO salary

    FROM employees e

   WHERE e.employee_id = empoyee_id;

  RETURN(salary);

EXCEPTION

  WHEN no_data_found THEN

    RETURN(-1);

  WHEN OTHERSTHEN

    RETURN(0);

 

END get_employee_salary;

 PROCEDURE 使用

输入参数使用in关键字标识

输出参数使用out参数表示

格式 CREATE OR REPLACE PROCEDURE+名称+参数（名称+in/out+类型）+BEGIN+程序体+END 名称;

CREATE ORREPLACEPROCEDURE update_employee_salary(empoyee_id   INNUMBER,

                                                  employee_name OUT VARCHAR2,

                                                  salary        OUT VARCHAR2)IS

BEGIN

  BEGIN

    SELECT e.first_name || ' ' || e.last_name employee_name,

           e.salary

      INTO employee_name,

           salary

      FROM employees e

     WHERE e.employee_id = empoyee_id;

  EXCEPTION

    WHEN no_data_found THEN

      employee_name := 'E';

      salary        := 0;

    WHEN OTHERS THEN

      employee_name := 'N';

      salary        := 0;

  END;

  UPDATE employees e

     SET e.salary = e.salary + 2000

   WHERE e.employee_id = empoyee_id;

ENDupdate_employee_salary;