Oracle 存储过程学习

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER,
参数3 OUT NUMBER
) IS
变量1 INTEGER := 默认值;
变量2 DATE;
BEGIN
…
EXCEPTION
WHEN 异常类别 THEN
…
WHEN OTHERS THEN
…
END 存储过程名字;

2.基本操作

变量赋值 V_TEST := 1;
动态赋值 SELECT COL1,COL2,… INTO VAR1,VAR2,… FROM … WHERE …;
字符相加 ‘STRING1′ || ‘STRING2′;
相等判断 =而不是==;
逻辑判断 AND,OR,>,<,<>;
打印输出 dbms_output.put_line();
是否为空 V_TEST IS (NOT) NULL;
提交回滚 COMMIT/ROLLBACK;
异常捕捉 BEGIN … EXCEPTION WHEN … THEN … END;

3.IF 判断

IF (判断条件) THEN
BEGIN
…
END;
ELSIF THEN
…
ELSE
…
END IF;

4.WHILE 循环

WHILE (判断条件) LOOP
BEGIN
…
END;
END LOOP;

5.FOR 循环

FOR X IN (SELECT col1,col2 …) LOOP
BEGIN
引用X.col1、X.col2
…
END;
END LOOP;

6.常见FUNCTION

常用函数的普通用法：

SYSDATE：用来得到系统的当前日期；
TO_NUMBER(STR)：将字符串转换成数字；若转换不成功，则抛错：invalid number；
TO_CHAR(?)：?可支持很多的类型，比如NUM，DATE……；DATE用得比较多，一般来说有如下用法：TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’)：将当前日期按照格式输出完整字符串；其中的日期格式可以自行定义；
TO_DATE(STR,FORMAT)：对字符串按照一定格式解析成Date型；
NVL(col,defaultValue)：对col判断是否为NULL,若为NULL，则返回defaultValue；
CONCAT(STR1,STR2)：连接两个字符串，功能与||相同；
LENGTH(STR)：返回字符串的长度，其中中文字符的长度计算要视DB的编码而定，在GBK编码下，中文也算1个字符；若需要中文字符长度算2个字符，则可以用LENGTHB(STR)代替；简单来说，LENGTH返回字符长度；LENGTHB返回字节长度；
SUBSTR(str,start,count)：截取子字符串,从start开始,取count个；start为0和1的效果是一样的，都是从str的第一个字符开始截取；若start>=LENGTH(str)，直接返回空，不会抛错；
INSTR(STR1,STR2,I,J)：在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
STR1:被搜索的字符串
STR2:希望搜索的字符串
I:搜索的开始位置,默认为1
J:第几次出现的位置,默认为1
LOWER/UPPER(STR)：将字符串全部转成小写或者大写；
在GBK编码下，该函数对中文无效；
LTRIM/RTRIM(STR)：删除左边货右边出现的空格字符串；
FLOOR(NUMBER)：对给定的数字取整数；
MOD(num1,num2)：返回num1除以num2的余数；
num1和num2可以带有小数位；
返回的值也可能有小数位；
ROUND(num)：对num四舍五入取值；
TRUNC(num)：对num截取整数，去掉小数位，不进行四舍五入；
功能与FLOOR类似；当然也可以在参数中指定精度；
TRUNC(Date)：对date截取到天，去掉小时分钟之类的，其返回类型仍然为Date；
类似的，也可以在参数中指定Date截取级别，比如’hh’；
ADD_MONTHS(Date,num)：对Date加上num个月，num可以为负数，表示减去几个月；
num可以有小数位，但是并不会起效，效果相当于ADD_MONTHS(Date,TRUNC(num))；
CHARTOROWID/ROWIDTOCHAR：将字符数据类型转换为ROWID类型或者相反；
AVG/MIN/MAX/SUM/COUNT(DISTINCT|ALL col)：对某字段取平均、最小、最大、求和、计数；默认为ALL，若需要对不重复值运算，则用DISTINCT参数；

CASE WHEN … THEN …
WHEN … THEN …
ELSE …
END：根据不同条件取不同的值，与JAVA中的CASE也很类似；

7.CURSOR 游标

无参数申明：

CURSOR CUR_NAME IS SELECT COL1,COL2,… FROM … WHERE …;

带参数申明：

CURSOR CUR_NAME(PARAM1 DATA_TYPE,PARAM2 DATA_TYPE,…) IS SELECT COL1,COL2,… FROM … WHERE …;

在大多数时候我们在设计程序的时候都遵循下面的步骤：

打开游标 open cs1;
开始循环 while cs1%found loop | for column_name in .. LOOP
从游标中取值 fetch .. into.. |
检查那一行被返回
处理
关闭循环 end loop;
关闭游标 if cs1&isopen then close cs1;

游标属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。

静态游标：

分为显式游标和隐式游标。

REF游标：

是一种引用类型，类似于指针。

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

REF CURSOR游标：

动态游标，在运行的时候才能确定游标使用的查询。分类：
强类型（限制）REF CURSOR，规定返回类型
弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。

8.用FOR IN使用CURSOR

FOR CUR_RS IN CUR_NAME LOOP
BEGIN
引用CUR_RS.COL1,CUR_RS.COL2,…
…
END;
END LOOP;

9.用FETCH INTO使用CURSOR

LOOP
FETCH CUR_NAME INTO V_COL1,V_COL2,…;
…
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

10.用BULK COLLECT使用CURSOR，用于批量操作，提高效率

–申明TABLE数据类别

TYPE TYPE_NAME IS TABLE OF DATA_TYPE;
eg： TYPE T_ROWID IS TABLE OF UROWID;

–申明变量

VAR_NAME TYPE_NAME;
eg：VAR_ROWID T_ROWID;

–LOOP 1000条一个批次

FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,… LIMIT 1000;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
…ONE SQL;
COMMIT;
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

11.EXIT WHEN CUR_NAME%NOTFOUND 位置

在配合FETCH INTO语句使用时，EXIT WHEN CUR_NAME%NOTFOUND可以放在循环的开始处，也可以放在最后面；视具体场景而定；
WHEN CUR_NAME%NOTFOUND也可以用其他的判断条件替换；
比如在用BULK COLLECT时，就可以用EXIT WHEN VAR_ROWID.COUNT = 0;不过此时需要放在FETCH之后FORALL之前；

12. 其他使用注意事项

1.SELECT INTO语句要求SELECT出来的RESULT至少有一条记录；
若RS为NULL,则会抛出NO_DATA_FOUND的EXCEPTION；所以需要进行异常捕获；

2.进行UPDATE、DELETE操作时可以用ROWID替换PK可以提高执行效率；
因为ROWID是直接定位到物理磁盘地址，而无需先从PK Index中查询，从而有效降低IO次数；
比如UPDATE MD_USER SET NAME = XX WHERE ROWID = ?;
不过用ROWID时需要小心，因为1)ROWID会被重用；2)在有GROUP BY语句的条件中无法使用；

3.EXCEPTION捕获之后若不需要做任何事情，则可以如下处理：
BEGIN … EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;

4.EXCEPTION能够捕获的常见类别有：
NO_DATA_FOUND：代码块中某条SELECT INTO语句返回的RS为NULL；
TOO_MANG_ROWS：SELECT INTO语句符合条件的记录有多条返回；
DUP_VAL_ON_INDEX：对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值；
VALUE_ERROR：在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常；
STORAGE_ERROR：内存溢出；
ZERO_DIVIDE：除数为零；
CASE_NOT_FOUND：对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件；
CURSOR_ALREADY_OPEN：程序试图打开一个已经打开的游标；
TIMEOUT_ON_RESOURCE：系统在等待某一资源，时间超时；
OTHERS：所有；
除此之外，还可以用RAISE来抛出一个自定义的EXCEPTION；比如
DECLARE MY_ERROR EXCEPTION;
BEGIN
IF (…) THEN
RAISE MY_ERROR;
END IF;
EXCEPTION WHEN MY_ERROR THEN
…
WHEN OTHERS THEN
…
END;

5.批量操作BULK COLLECT INTO情况下，FORALL下面只能有一条SQL操作，也不能是存过调用；若需要执行多条SQL，则需要用多个FORALL，比如
…
LOOP
–1000条一个批次
FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,… LIMIT 1000;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
INSERT INTO …;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
UPDATE TABLE_NAME SET …;
COMMIT;
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

6.重复数据高效清理SQL：
DELETE FROM TABLE_NAME tn
WHERE tn.ROWID >
(SELECT MIN(x.ROWID)
FROM TABLE_NAME x
WHERE x.col1 = tn.col1
AND x.col2 = tn.col2);
–根据col1、col2两个字段清理，清理后两个字段满足unique约束；

7.一条SQL搞定如下场景：当数据存在进行更新，否则进行新增；
MERGE INTO TABLE_NAME tn
USING (SELECT v_pk AS pk FROM DUAL) tmps
ON (tn.pk = tmps.pk)
WHEN MATCHED THEN
UPDATE
SET col1 = ?, col2 = ? ,…
WHEN NOT MATCHED THEN
INSERT (col1, col2, …)
VALUES (v_col1, v_col2, …);
注意：ON子句中的字段不能在UPDATE子句中进行SET操作；

8.CONNECT BY语句使用
SQL> SELECT rownum rn FROM dual CONNECT BY rownum <= 5;

RN
———-
1
2
3
4
5
该语句一般配合笛卡尔积使用，一条语句搞定很多条INSERT语句的事情，可以大大减少SQL代码量；

13. 用java调用oracle存储过程

–一般过程：

//1、call＋包名＋存储过程名（传入、传出值用？）  
   String str="{call SMSBUSINESS.deleteZhZMember(?,?,?)}";
//2、建立连接
 Connection conn=null;
 conn=DriverManager.getConnection();
//3、使用java.sql.*类
 CallableStatement cs=conn.prepareCall(str);
//4、传入in值
 cs.setInt(1,id);
 cs.setInt(2,-2);
//5、设置out值
 cs.registerOutParameter(3,Types.NUMERIC);
//6、执行
 cs.excuse();
//7、取出out值
 int flag=cs.getInt(3);
//8、关连接
 conn.close();

–无返回值的存储过程

存储过程为：

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
   INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;

Java调用如下：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @ClassName: 
 * @Description: TODO
 * @author: 
 * @Date: 2015-05-12 16:33:53
 */
 
public class TestProcedureOne {

    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
            CallableStatement proc = null; // 创建执行存储过程的对象
            proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }"); // 设置存储过程
                                                                // call为关键字.
            proc.setString(1, "100"); // 设置第一个输入参数
            proc.setString(2, "TestOne");// 设置第二个输入参数
            proc.execute();// 执行
        } catch (SQLException ex2) {
            ex2.printStackTrace();
        } catch (Exception ex2) {
            ex2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                }
            } catch (SQLException ex1) {
            }
        }
    }
}

–有返回值的存储过程

存储过程为：

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
   SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;

Java调用如下：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
 * @ClassName: 
 * @Description: TODO
 * @author: 
 * @Date: 2015-05-12 16:33:53
 */
 
public class TestProcedureTWO {
 public TestProcedureTWO() {
 }
 public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }"); //设置存储过程
      proc.setString(1, "100");//设置第一个参数输入参数
      proc.registerOutParameter(2, Types.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
      proc.execute();//执行
      String testPrint = proc.getString(2);//获得输出参数
      System.out.println("=testPrint=is="+testPrint);
    }
    catch (SQLException ex2) {
      ex2.printStackTrace();
    }
    catch (Exception ex2) {
      ex2.printStackTrace();
    }
    finally{
      try {
        if(rs != null){
          rs.close();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
        }
      }
      catch (SQLException ex1) {
      }
    }
 }
}