oracle的存储过程

kiddwyl

2012-08-15

关注关注

1.存储过程的定义

createorreplaceprocedure存储过程名称(可以带参数)

is/as

变量的声明

begin

方法体

end

2.基本操作

变量赋值V_TEST:=1;

动态赋值SELECTCOL1,COL2,…INTOVAR1,VAR2,…FROM…WHERE…;

字符相加‘STRING1′||‘STRING2′;

相等判断=而不是==;

逻辑判断AND,OR,>,<,<>;

打印输出dbms_output.put_line();

是否为空V_TESTIS(NOT)NULL;

提交回滚COMMIT/ROLLBACK;

异常捕捉BEGIN…EXCEPTIONWHEN…THEN…END;

3.循环控制

a.Loop

LOOP和ENDLOOP之间的语句无限次的执行显然是不行的，那么在使用LOOP语句时必须使用EXIT语句，强制循环结束.

b.for...in..Loop

以endLoop结束

X:=100;
FOR v_counter in 1..10 loop
x:=x+10;
end loop
y:=x;

退出for循环可以使用EXIT语句

c.while..Loop

以endLoop结束

X:=100;
WHILE X<=1000 LOOP
　X:=X+10;
END LOOP;
Y=X;

4.控制结构

if...then

elseif...then

else

endif

5.常见FUNCTION

以下列出的是一些常用函数的普通用法，并不是按照ORACLE函数标准进行说明的，所以有些用法并不全面，只是列举了最常用的情况；

SYSDATE：用来得到系统的当前日期；

TO_NUMBER(STR)：将字符串转换成数字；若转换不成功，则抛错：ORA-01722:invalidnumber；

TO_CHAR(?)：?可支持很多的类型，比如NUM，DATE……；DATE用得比较多，一般来说有如下用法：

TO_CHAR(SYSDATE,‘YYYY-MM-DDHH24: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|ALLcol)：对某字段取平均、最小、最大、求和、计数；默认为ALL，若需要对不重复值运算，则用DISTINCT参数；

6.java调用存储过程

/** 
     * 使用游标查询所有信息 无输入参数 有输出参数 
     */  
    try {  
      CallableStatement call=conn.prepareCall("{call all_t_test(?,?)}");  
      call.registerOutParameter(1, Types.INTEGER);  
      call.registerOutParameter(2, OracleTypes.CURSOR);  
      call.execute();  
      Integer result=call.getInt(1);  
      ResultSet rs=(ResultSet) call.getObject(2);  
      while(rs.next()){  //值和值的类型都与游标中的表相对应
        System.out.println(rs.getInt(1));  
        System.out.println(rs.getString(2));  
        System.out.println(rs.getString(3));  
      }  
    } catch (SQLException e) {  
      e.printStackTrace();  
    }

7.优点

好处1:

存储过程里面的代码，都是已经被“编译”过的了。直接可以执行。

如果不是存储过程，那么你每次执行代码，都要再编译。

好处2:

减少网络流量.假如你的存储过程，是要分别检索几个大的表，然后作复杂的计算，最后产生一个小的汇总结果。如果全部在客户端处理，那么会有大量的数据，从服务器传递到客户端。然后客户端将多个大的结果集合，处理成一个小的汇总集合。如果用存储过程，那么这些网络上传递的数据，将仅仅包含那个小的汇总集合。

存储过程 oracle函数