Oracle创建函数和过程

无论采用哪一种参数传递方法,实际参数和形式参数之间的数据传递只有两种方法:传址法和传值法。所谓传址法是指在调用函数时,将实际参数的地址指针传递给形式参数,使形式参数和实际参数指向内存中的同一区域,从而实现参数数据的传递。这种方法又称作参照法,即形式参数参照实际参数数据。输入参数均采用传址法传递数据。 传值法是指将实际参数的数据拷贝到形式参数,而不是传递实际参数的地址。默认时,输出参数和输入/输出参数均采用传值法。在函数调用时,ORACLE将实际参数数据拷贝到输入/输出参数,而当函数正常运行退出时,又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

3. 参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

例5:

CREATE OR REPLACE FUNCTION demo_fun( Name VARCHAR2, Age INTEGER,

Sex VARCHAR2 DEFAULT '男') RETURN VARCHAR2 AS

V_var VARCHAR2(32); BEGIN

V_var := name||':'||TO_CHAR(age)||'岁.'||sex; RETURN v_var; END;

具有默认值的函数创建后,在函数调用时,如果没有为具有默认值的参数提供实际参数值,函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时,函数将使用实际参数值。在创建函数时,只能为输入参数设置默认值,而不能为输入/输出参数设置默认值。

DECLARE

var VARCHAR(32); BEGIN

Var := demo_fun('user1', 30); DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40); DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var); END;

6.3 存储过程

6.3.1 创建过程

建立存储过程

在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

创建过程语法:

CREATE [OR REPLACE] PROCEDURE procedure_name

([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]], ......

[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen]) [ AUTHID DEFINER | CURRENT_USER ] { IS | AS } <声明部分> BEGIN <执行部分> EXCEPTION

<可选的异常错误处理程序> END procedure_name;

说明:相关参数说明参见函数的语法说明。

例6.用户连接登记记录;

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

CREATE OR REPLACE PROCEDURE logexecution IS BEGIN

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

例7.删除指定员工记录;

CREATE OR REPLACE PROCEDURE DelEmp

(v_empno IN employees.employee_id%TYPE) AS

No_result EXCEPTION; BEGIN

DELETE FROM employees WHERE employee_id = v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF;

DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!'); EXCEPTION

WHEN no_result THEN

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

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

例8.插入员工记录:

CREATE OR REPLACE PROCEDURE InsertEmp(

v_empno in employees.employee_id%TYPE, v_firstname in employees.first_name%TYPE, v_lastname in employees.last_name%TYPE, v_deptno in employees.department_id%TYPE ) AS

empno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT(empno_remaining, -1); /* -1 是违反唯一约束条件的错误代码 */ BEGIN

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID) VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno); DBMS_OUTPUT.PUT_LINE('温馨提示:插入数据记录成功!'); EXCEPTION

WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!'); WHEN OTHERS THEN

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

例9.使用存储过程向

departments表中插入数据。

CREATE OR REPLACE PROCEDURE insert_dept

(v_dept_id IN departments.department_id%TYPE, v_dept_name IN departments.department_name%TYPE, v_mgr_id IN departments.manager_id%TYPE, v_loc_id IN departments.location_id%TYPE) IS

ept_null_error EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_null_error, -1400); ept_no_loc_id EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291); BEGIN

INSERT INTO departments

(department_id, department_name, manager_id, location_id) VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id); DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功'); EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复'); WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空'); WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR(-20002, '没有该地点'); END insert_dept;

/*调用实例一: 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(300, '部门300', 100, 2400); insert_dept(310, NULL, 100, 2400); insert_dept(310, '部门310', 100, 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

联系客服:779662525#qq.com(#替换为@)