Chapter -09 Creating Procedures 03

最新推荐文章于 2021-05-13 11:37:59 发布

转载最新推荐文章于 2021-05-13 11:37:59 发布 · 69 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/arcer/archive/2013/04/19/3031517.html

本文详细介绍了在 Oracle 数据库中使用 PL/SQL 编写存储过程的方法，包括参数传递、默认参数设置、异常处理等内容，并通过具体实例展示了如何创建及调用存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Passing Actula Parameters:Creating the add_dept Procedure

View Code

CREATE OR REPLACE PROCEDURE add_dept
(
        p_name IN dept.department_name%TYPE,
        p_loc  IN dept.location_id%TYPE
)
IS
BEGIN
        INSERT INTO dept(department_id,department_name,location_id)
        VALUES(departments_seq.NEXTVAL,p_name,p_loc);

        COMMIT;
END add_dept;
/
SQL> begin
  2  add_dept('ORACLE',1700);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Passing Actual Parameters:Examples

--Passing parameters using the postional notation.

EXECUTE add_dept('TRAINING',2500);

--Passing parameters using the named notation.

EXECUTE add_dept(p_loc=>2400,p_name=>'EDUCATION');

Using the DEFAULT Option for the Parameters

Define default values for parameters
Provides flexibility by combining the postion and named parameter-passing syntax

View Code

CREATE OR REPLACE PROCEDURE add_dept_withdefault
(
        p_name  dept.department_name%TYPE:='Unknow',
        p_loc   dept.location_id%TYPE DEFAULT 1700
)
IS
BEGIN
        INSERT INTO dept(department_id,department_name,location_id)
        VALUES(departments_seq.NEXTVAL,p_name,p_loc);

        COMMIT;
END add_dept_withdefault;
/
SQL> EXECUTE add_dept_withdefault

PL/SQL procedure successfully completed.

SQL> EXECUTE add_dept_withdefault('ADVERTISING',p_loc=>1200);

PL/SQL procedure successfully completed.

SQL> EXECUTE add_dept_withdefault(p_loc=>1200);

PL/SQL procedure successfully completed.

Calling Procedures

You can call procedures using anonymous blocks,another procedure,or packages.
You must own the procedure or have the EXECUTE privilege.

View Code

CREATE OR REPLACE PROCEDURE process_emp
IS
        CURSOR cur_emp_cursor IS
                SELECT employee_id FROM emp;
BEGIN
        FOR emp_rec IN cur_emp_cursor
        LOOP
                raise_salary(emp_rec.employee_id,10);
        END LOOP;
        COMMIT;
END process_emp;
/

Forward Declaration

If nested subprograms in the same PL/SQL block invoke each other,then one requries a forward declaration,because a subprogram must be declared before it can be invoked.
A forward declaration declares a nested subprogram but does not define it.You must define it later in the same block.The forward declaration and the definition must have the same subprogram heading.

DECLARE
        PROCEDURE proc1(number1 NUMBER);

        PROCEDURE proc2(number2 NUMBER) IS
        BEGIN
                proc1(number2);
        END;

        PROCEDURE proc1(number1 NUMBER) IS
        BEGIN
                proc2(number1);
        END;
BEGIN
        NULL
END;
/

Handled Exceptions

Handled Exceptions:Example

CREATE OR REPLACE PROCEDURE add_department_a
(
        p_name  VARCHAR2,
        p_mgr   NUMBER,
        p_loc   NUMBER
)
IS
BEGIN
        INSERT INTO DEPARTMENTS(department_id,department_name,manager_id,location_id)
        VALUES(DEPARTMENTS_SEQ.NEXTVAL,p_name,p_mgr,p_loc);

        DBMS_OUTPUT.PUT_LINE('Added Dept:' || p_name);

EXCEPTION
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Err:adding dept:' || p_name);
END;

/

CREATE OR REPLACE PROCEDURE create_dept_a
IS
BEGIN
        add_department_a('Media',100,1800);
        add_department_a('Editing',99,1800);
        add_department_a('Advertising',101,1800);
END;
/

SQL> begin
  2  create_dept_a;
  3  end;
  4  /
Added Dept:Media
Err:adding dept:Editing
Added Dept:Advertising

PL/SQL procedure successfully completed.

上述示例，会成功2条记录；因为在存储过程add_deptartment_a中，当有异常发生的时候，会在自己内部进行处理，外部调用它的函数会认为调用成功；即使此时调用它的外部存储过程create_dept_a存在事务处理机制，也不会发生回滚；再看下面的改造存储过程，就会发生事务回滚处理操作，因为取消了add_department_a中的异常处理；

CREATE OR REPLACE PROCEDURE add_department_a
(
        p_name  VARCHAR2,
        p_mgr   NUMBER,
        p_loc   NUMBER
)
IS
BEGIN
        INSERT INTO DEPARTMENTS(department_id,department_name,manager_id,location_id)
        VALUES(DEPARTMENTS_SEQ.NEXTVAL,p_name,p_mgr,p_loc);

        DBMS_OUTPUT.PUT_LINE('Added Dept:' || p_name);

--EXCEPTION
--      WHEN OTHERS THEN
--              DBMS_OUTPUT.PUT_LINE('Err:adding dept:' || p_name);
END;

/

CREATE OR REPLACE PROCEDURE create_dept_a
IS
BEGIN
        add_department_a('Media_01',100,1800);
        add_department_a('Editing_01',99,1800);
        add_department_a('Advertising_01',101,1800);
EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
                DBMS_OUTPUT.PUT_LINE('Err:adding dept!');
END;
/

转载于:https://www.cnblogs.com/arcer/archive/2013/04/19/3031517.html