6.7PLSQL中的存储过程和函数

Oracle PLSQL 存储过程

1、PLSQL中的存储过程

1，在PLSQLDEVELOPER中开发存储过程

File->New->Program Window->Procedure

2，PLSQL存储过程的参数模式

CREATE OR REPLACE PROCEDURE query_emp
     (p_id IN employees.employee_id%TYPE,
     p_name OUT employees.last_name%TYPE,
     p_salary OUT employees.salary%TYPE,
     p_comm OUT employees.commission_pct%TYPE)
IS
BEGIN
     SELECT last_name, salary, commission_pct
     INTO p_name, p_salary, p_comm
     FROM employees
     WHERE employee_id = p_id;
END query_emp;
/

CREATE OR REPLACE PROCEDURE format_phone
         (p_phone_no IN OUT VARCHAR2)
IS
BEGIN
     p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
     ')' || SUBSTR(p_phone_no,4,3) ||
     '-' || SUBSTR(p_phone_no,7);
END format_phone;
/

3，参数传递方式(按顺序传递或者使用=>符号传递)

A.顺序传递

CREATE OR REPLACE PROCEDURE add_dept
   (p_name IN departments.department_name%TYPE
   DEFAULT 'unknown',
   p_loc IN departments.location_id%TYPE
   DEFAULT 1700)
IS
BEGIN
   INSERT INTO departments(department_id,
   department_name, location_id)
   VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

B.使用=>符号传递

BEGIN
   add_dept;                                           #使用默认值
   add_dept ('TRAINING', 2500);                        #按顺序传递
   add_dept ( p_loc => 2400, p_name =>'EDUCATION');         #使用=>符号传递，无
顺序要求
   add_dept ( p_loc => 1200) ;
END;
/
     SELECT department_id, department_name, location_id
FROM departments;

4，过程调用的例外处理

5，删除储存过程

DROP PROCEDURE procedure_name

2、PLSQL中的存储函数

1，定义存储函数的语法

CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1,
 parameter2 [mode2] datatype2,
 . . .)]
RETURN datatype
IS|AS
PL/SQL Block;

CREATE OR REPLACE FUNCTION get_sal
 (p_id IN employees.employee_id%TYPE)
 RETURN NUMBER
IS
 v_salary employees.salary%TYPE :=0;
BEGIN
 SELECT salary
 INTO v_salary
 FROM employees
 WHERE employee_id = p_id;
 RETURN v_salary;
END get_sal;
/

2，使用PLSQLDeveloper 开发函数

3，可以使用用户自定义的函数的SQL语句

• select 语句
• Where条件和Having子句
• CONNECT BY, START WITH, ORDER BY, 和GROUP BY 子句
• INSERT的Values子句

• UPDATE的Set子句

  SELECT employee_id, tax(salary)
  FROM employees
  WHERE tax(salary)>(SELECT MAX(tax(salary))
  FROM employees WHERE department_id = 30)
  ORDER BY tax(salary) DESC;

  4，对于用户定义函数在SQL语句中使用的限制

• 必须是个函数（不能是过程-Procedure）

• 只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）
• 只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内 存表）
• 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
• 在SQL中使用的函数，其函数体内部不能有DML语句。
• 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句
• 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER)
 RETURN NUMBER IS
BEGIN
 INSERT INTO employees(employee_id, last_name, email,
 hire_date, job_id, salary)
 VALUES(1, 'employee 1', 'emp1@company.com',
SYSDATE, 'SA_MAN', 1000);
 RETURN (p_sal + 100);
END;
/

5，删除储存函数

DROP FUNCTION function_name

3、函数过程对数据访问的权限概念

定义者权限和调用者权限

1，定义者权限

函数执行时，对表的访问默认使用定义者权限

2，调用者权限

在写函数的时候有特殊语句标识：AUTHID CURRENT_USER

CREATE PROCEDURE query_emp
    (p_id IN employees.employee_id%TYPE,
    p_name OUT employees.last_name%TYPE,
    p_salary OUT employees.salary%TYPE,
    p_comm OUT employees.commission_pct%TYPE)
    AUTHID CURRENT_USER
IS
BEGIN
     SELECT last_name, salary,
     commission_pct
     INTO p_name, p_salary, p_comm
     FROM employees
     WHERE employee_id=p_id;
END query_emp;
/