示例：创建简单的存储过程。

    • 准备相关表
    1. create table t1(id number not null primary key, name varchar2(50) not null, gmt_create date not null default sysdate, unique (name));
    3. create sequence seq_t1 start with 10000 increment by 1 cache 50 nocycle;
    • 创建存储过程
    1. delimiter /
    2. CREATE OR REPLACE PROCEDURE sp_insert_t1( p_name varchar2 )
    3. AS  
    4. BEGIN
    5.     INSERT INTO t1(id, name) values(seq_t1.nextval, p_name) ;
    6.     COMMIT;
    7.     dbms_output.put_line('Add a row which name is : ' || p_name );
    8. EXCEPTION 
    9.     WHEN OTHERS THEN 
    10.         ROLLBACK;
    11.         dbms_output.put_line('Exception raised!');
    12. END ;
    13. /
    14. delimiter ;
    • 执行存储过程
    1. obclient> set serveroutput on ;
    2. Query OK, 0 rows affected (0.22 sec)
    4. obclient> select * from t1;
    5. Empty set (0.01 sec)
    7. obclient> call sp_insert_t1('A');
    8. Query OK, 0 rows affected (0.05 sec)
    10. Add a row which name is : A
    11. obclient> select * from t1;
    12. +-------+------+---------------------+
    13. | ID    | NAME | GMT_CREATE          |
    14. +-------+------+---------------------+
    15. | 10000 | A    | 2020-04-02 18:37:24 |
    16. +-------+------+---------------------+
    17. 1 row in set (0.00 sec)
    19. obclient> call sp_insert_t1('B');
    20. Query OK, 0 rows affected (0.00 sec)
    22. Add a row which name is : B
    23. obclient> call sp_insert_t1('A');
    24. Query OK, 0 rows affected (0.01 sec)
    26. Exception raised!
    27. obclient> select * from t1;
    28. +-------+------+---------------------+
    29. | ID    | NAME | GMT_CREATE          |
    30. +-------+------+---------------------+
    31. | 10000 | A    | 2020-04-02 18:37:24 |
    32. | 10001 | B    | 2020-04-02 18:37:41 |
    33. +-------+------+---------------------+
    34. 2 rows in set (0.00 sec)