oracle存储过程

说明：
　　1. parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型
　　2. 定义存储过程的参数时，只能指定数据类型，不能指定数据长度
　　3. IS/AS用于开始PL/SQL代码块
　　4. 创建存储过程时，既可以指定参数也可以不指定任何参数；
　　5. 存储过程参数：1）输入参数 IN   　IN用于接收调用环境的输入参数（创建存储过程时，输入参数的IN可以省略）
　　　　　　　　　　 2) 输出参数 OUT 　OUT用于将输出数据传递到调用环境
　　　　　　　　　　 3) 输入输出参数（IN OUT）其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境

2）删除存储过程
DROP PROCEDURE procedure_name;
 
3)编译存储过程
ALTER PROCEDURE procedure_name COMPILE

三、存储过程调用
1）说明：
　　1.在PL/SQL中可以直接引用存储过程(在SQL*PLUS中调用存储过程时需要使用call或者execute命令);
　　2.当调用存储过程时，如果无参数，那么直接引用存储过程名；如果有输入参数，则需提供输入参数数值；如果有输出参数，需要使用变量接收输出结果；
　　3.参数传递时有位置传递，名称传递和组合传递三种方法，三种参数传递方式如下：

DECLARE
　　v_para1            varchar2(10);
　　v_para2           nvarchar2(10);
　　v_para3            varchar2(30);
　　v_para4            varchar2(30);
BEGIN
     v_para1 := ‘123‘;
     v_para2 := ‘456‘;
     v_para4 := ‘789‘;
  
     USP_Learing(v_para1,v_para2,v_para3,v_para4);  --位置传递
　 USP_Learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4); --值传递
 　USP_Learing(v_para1,v_para2,p_para3=>v_para3,p_para4=>v_para4);  --组合传递
     dbms_output.put_line(v_para3);
     dbms_output.put_line(v_para4);
END;  

注：

　　DBMS_OUTPUT程序包是我们在Oracle开发过程中常用的一个包体，使用该包我们可以从存储过程、包或触发器发送信息(messages)。
　　DBMS_OUTPUT包主要用于调试PL/SQL程序，或者在SQL*PLUS命令中显示信息(displaying message)和报表，譬如我们可以写一个简单的匿名PL/SQL程序块，而该块出于某种目的使用DBMS_OUTPUT包来显示一些信息。
　　在该DBMS_OUTPUT包中存在2个存储过程，它们是PUT_LINE和PUT过程，使用这2个Procedure可以做到将信息存放到PL/SQL的Buffer中，以便其他的触发器、存储过程、程序包来读取。在独立的PL/SQL程序或匿名块中，我们还可以使用GET_LINES和GET这2个存储过程来将存放在PL/SQL Buffer中的信息输出(display)到屏幕。
DBMS_OUTPUT包子程序摘要

附：Oracle推荐在debug PL/SQL程序时使用该程序包，不推荐使用该包来做报表输出或其他格式化输出之用。
 

 
2）存储过程调用例子
　　CREATE OR REPLACE PROCUDURE print_Time
　　IS
　　BEGIN
 　　   DBMS_OUTPUT.PUT_LINE(SYSDATE);
　　END print_time;

1.pl/sql中直接在pl/sql代码块中调用 print_time()即可
2.sql*plus中  EXEC print_time();
 

四、存储过程中常用数据类型
1）记录（RECORD）（单行多列）
2) 表（TABLE）（多行多列）
3) 嵌套表（table）（多行多列）
4）变长数组（VARRY）（多行单列）
5）Common Table Expression (CTE)

五、存储过程中事务处理
1）事务说明：
　　1.事务用于确保数据的一致性，有一组相关的DML语句组成，改组DML语句所执行的操作要么全部确认，要么全部取消。
　　2.当执行事务操作DML时，oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加锁，以防止其他事务在该行上执行DML操作
　　3.当执行事务提交或者事务回滚时，oracle会确认事务变化或者回滚事务、结束事务、山粗保存点、释放锁。
　　4. 提交事务（commit）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变
　　5.保存点（savepoint）在当前事务中，标记事务的保存点
　　6. 回滚操作（rollback）回滚整个事务，删除该事务中所有保存点，释放锁，丢弃所有未决的数据改变
　　7. ROLLBACK TO SAVEPOINT 回滚到指定的保存点

2）存储过程中事务说明：
　　1.尽可能的让事务持续的越短越好
　　2.在事务中尽可能的存取最少的数据量

3）实例
  CREATE OR REPLACE PROCEDURE trancPro
  IS
  BEGIN
 　　 INSERT INTO tab1 VALUES(‘AA‘,‘1212‘,‘1313‘);
 　　 COMMIT;
  　　SAVEPOINT s1;
  　　INSERT INTO tab1  VALUES(‘BB‘,‘1414‘,‘1515‘);
 　　 DBMS_TRANSACTION.SAVEPOINT(‘s2‘);
 　　 UPDATE tab1 SET SNO=‘1515‘ WHERE ID=‘BB‘;
 　　 COMMIT;
　　  EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO SAVEPOINT s1;
  　　RAISE_APPLICATION_ERROR(-20010,‘ERROR:违反唯一索引约束‘);
  　　WHEN OTHERS THEN ROLLBACK;
 END trancPro;

六、存储过程例子
1)简单例子--利用存储过程打印日期
 CREATE OR REPLACE PROCUDURE print_Time
 IS
 BEGIN
 　　 DBMS_OUTPUT.PUT_LINE(SYSDATE);
 END print_time;
 
2)例2--包含输入输出参数
 CREATE OR REPLACE PROCEDURE para_Procedure
 (
   para1　　　　　　　　varchar2 :=‘paraString1‘,
   para2　　　　　　　　varchar2 default ‘paraString2‘,
   para3  out      　   varchar2,
   para4  in out　　　  varchar2
 )
 IS
 BEGIN
 DECLARE
     para5               varchar2(20);
 BEGIN
     para5 := ‘输入输出参数：‘|| para4;
     para3 := ‘输出参数：‘ || para1 || para2;
     para4 :=para5;
     dbms_output.put_line(para5);
 　　　 dbms_output.put_line(‘para4 is‘||para4);
 END;
 END para_Procedure;
 
 
七、java程序调用
在本节中，我们使用java语言调用存储过程。其中，关键是使用CallableStatement这个对象，代码如下：
String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
 
        // 以下使用的Test就是Oracle里的表空间
        String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        Connection myConnection = null;
        try {
            Class.forName(oracleDriverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            myConnection = DriverManager.getConnection(oracleUrlToConnect,
                    "xxxx", "xxxx");//此处为数据库用户名与密码
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            
            CallableStatement proc=null;
            proc=myConnection.prepareCall("{call xs_proc(?,?)}");
            proc.setString(1, "zhangsan");
            proc.registerOutParameter(2, Types.NUMERIC);
            proc.execute();
            String teststring=proc.getString(2);
            System.out.println(teststring);
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
 
 

oracle存储过程

标签：insert   disable   from   事务处理   作用   nts   参数   入参   未决