Oracle创建函数和过程

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); END;

调用实例二： DECLARE

ept_20000 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20000, -20000); ept_20001 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20001, -20001); ept_20002 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20002, -20002); BEGIN

insert_dept(v_dept_name => '部门310', v_dept_id => 310, v_mgr_id => 100, v_loc_id => 2400);

insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900); EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); END; */

6.3.2 调用存储过程

存储过程建立完成后，只要通过授权，用户就可以在

SQLPLUS 、ORACLE开发工

具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：

EXEC[UTE] procedure_name( parameter1, parameter2…);

例10：

EXECUTE logexecution;

例11：查询指定员工记录；

CREATE OR REPLACE PROCEDURE QueryEmp

(v_empno IN employees.employee_id%TYPE, v_ename OUT employees.first_name%TYPE, v_sal OUT employees.salary%TYPE) AS BEGIN

SELECT last_name || last_name, salary INTO v_ename, v_sal FROM employees

WHERE employee_id = v_empno;

DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!'); EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END QueryEmp; --调用 DECLARE

v1 employees.first_name%TYPE; v2 employees.salary%TYPE; BEGIN

QueryEmp(100, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(103, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(104, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); END;

例12．计算指定部门的工资总和，并统计其中的职工数量。

CREATE OR REPLACE PROCEDURE proc_demo (

dept_no NUMBER DEFAULT 10,

sal_sum OUT NUMBER, emp_count OUT NUMBER ) IS BEGIN

SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count FROM employees WHERE department_id = dept_no; EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END proc_demo;

DECLARE V_num NUMBER; V_sum NUMBER(8, 2); BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num); Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num); END;

在

PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数

据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

例13：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；

DECLARE V_num NUMBER; V_sum NUMBER(8, 2); PROCEDURE proc_demo (

Dept_no NUMBER DEFAULT 10, Sal_sum OUT NUMBER, Emp_count OUT NUMBER ) IS BEGIN

SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count FROM employees WHERE department_id=dept_no;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END proc_demo; --调用方法： BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num); Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num); END;

6.3.3 AUTHID

过程中的AUTHID 指令可以告诉ORACLE ，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行．

例14：建立过程，使用

Connect HR/qaz DROP TABLE logtable;

CREATE table logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution AUTHID DEFINER IS BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT / AS SYSDBA

GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE); EXECUTE HR.logexecution

AUTOID DEFINER；