Oracle 存储过程学习
转自:http://blog.chinaunix.net/uid-20495387-id-174394.html
http://www.cnblogs.com/rootq/articles/1100086.html
http://blog.sina.com.cn/s/blog_7540bf5f0100q82e.html
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) { } } } }