oracle引用类型,Oracle游标、过程与引用类型-Oracle

0. 常用函数

substr(str,start,length);//可以反向索引。length不指定就到结尾

to_number(str);//str转number

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;//日期转字符

nvl(tt,0);//若是空值则为0

1. 游标

对于集合数据的处理，学会使用带参数的方式。

同一个会话默认最多300个光标

set system set open_cursors=400 scope=

{both;仅更改当前memory;spfile;重启生效}

//属性

//cus1%found cus1%notfound cus1%isopen cus1%rowcount影响行数

SET serveroutput ON;

DECLARE

CURSOR c1

IS

SELECT bookno,booktitle FROM bebook;

bookno bebook.bookno%type;//引用类型

booktitle bebook.booktitle%type;

rowbook bebook%rowtype;//行引用类型

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO bookno,booktitle;

EXIT

WHEN c1 %notfound;

dbms_output.put_line('this string breaks here.'||bookno||booktitle);

END LOOP;

END;

//带参数的光标

Cursor

cemp(dno number) is select ename from emp where deptno = dno;

Open cemp(10);

2. rowtype

利用这个数据类型增加程序的健壮性。不会受到表结构更改导致程序更改。

记录可以整体赋值

rowtype参考

rowtype参考

//读入数据到rowtype类型中

create table testtable();

r testtable%rowtype;

select * into r from testtable where pno=...;

//rowtype类型数据插入表中

insert into testtable2 values r;

-----------------------------------------------------

-----------------------------------------------------

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := 'sample';

v_dept.loc := 'chicago';

insert into dept values v_dept;

end;

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := 'sample2';

v_dept.loc := 'dallas';

update dept set ROW=v_dept where deptno=v_dept.deptno;

end;

declare

rwEmp t_mst_employee%rowtype;

begin

select * into rwEmp from t_mst_employee where emp_no='10001';

rwEmp.emp_no := '20001';

insert into t_mst_employee values rwEmp;

update t_mst_employee set ROW=rwEmp where emp_no='3900';

end;

3. 过程

create or replace PROCEDURE "STATISTICS_ORDERSUMxxx"(

branchNo BEbranch.branchNo%type,

reportPerson VARCHAR2,

ordersum_table_cursor OUT sys_refcursor)

IS

testcur sys_refcursor;

v_typegoodNo BEproduct.productClass%TYPE; --类别编号

v_pritypegoodNo BEproduct.productClass%TYPE; --上一游标读取的类别编号

v_branchNo BEbranch.branchNo%TYPE; --游标读取的分店编号

v_pribranchNo BEbranch.branchNo%TYPE; --上一个游标读取的分店编号

v_branchname BEbranch.branchName%type; --各分店

v_branchsum NUMBER; --分店总数

v_typenum NUMBER;

ordersum_table odreport1%rowtype;

--游标定义

CURSOR ordersum_cur(pno VARCHAR2)

IS

SELECT d.typegoodno,

SUM(b.quantity)

FROM BDprocureplan a,

BDplandetail b,

BEproduct c,

DTtypegood d

WHERE a.branchno =pno

AND a.planno = b.planno

AND b.productno = c.productno

AND c.productclass = d.typegoodno

GROUP BY d.typegoodno;

CURSOR branch_cur

IS

SELECT branchNo,branchName FROM BEbranch;

maketime DATE;

mycount INT:=0;

BEGIN

SELECT COUNT(*) INTO mycount FROM bebranch;

OPEN branch_cur;

LOOP

FETCH branch_cur INTO v_branchNo,v_branchname;

EXIT

WHEN branch_cur%NOTFOUND;

ordersum_table.branchname := v_branchname;

ordersum_table.branchno := v_branchno;

dbms_output.put_line(ordersum_table.branchname);

OPEN ordersum_cur( v_branchNo );

LOOP

FETCH ordersum_cur INTO v_typegoodNo,v_typenum;

EXIT

WHEN ordersum_cur%NOTFOUND;

CASE v_typegoodNo

WHEN'001'THEN

ordersum_table.clothessum := ordersum_table.clothessum+v_typenum;

WHEN'002'THEN

ordersum_table.shoesum:=ordersum_table.shoesum+v_typenum;

WHEN'003'THEN

ordersum_table.foodsum:=ordersum_table.foodsum+v_typenum;

WHEN'004'THEN

ordersum_table.sourcesum:=ordersum_table.sourcesum+v_typenum;

WHEN'005'THEN

ordersum_table.drinksum:=ordersum_table.drinksum+v_typenum;

WHEN'006'THEN

ordersum_table.drinkingsum:=ordersum_table.drinkingsum+v_typenum;

WHEN'007'THEN

ordersum_table.vegetablesum:=ordersum_table.vegetablesum+v_typenum;

WHEN'008'THEN

ordersum_table.fruitsum:=ordersum_table.fruitsum+v_typenum;

WHEN'009'THEN

ordersum_table.moatsum:=ordersum_table.moatsum+v_typenum;

WHEN'010'THEN

ordersum_table.electricsum:=ordersum_table.electricsum+v_typenum;

WHEN'011'THEN

ordersum_table.officesum:=ordersum_table.officesum+v_typenum;

WHEN'012'THEN

ordersum_table.studysum:=ordersum_table.studysum+v_typenum;

WHEN'013'THEN

ordersum_table.diansum:=ordersum_table.diansum+v_typenum;

END CASE;

ordersum_table.allsum := ordersum_table.allsum+v_typenum;

END LOOP;

insert into odreport1 values ordersum_table;

CLOSE ordersum_cur;

END LOOP;

CLOSE branch_cur;

COMMIT;

END "STATISTICS_ORDERSUMxxx";

Java中调用

//获取

CallableStatement cstmt = null;

String sAutoNo = null;

String sSql = "{call AUTO_No(?,?)}";

try {

cstmt = connDB.getConn().prepareCall(sSql);

cstmt.setString(1, tableName);

cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //注册字符变量

cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//注册游标变量

cstmt.execute();

sAutoNo = cstmt.getString(2);//cstmt.getInt(2);

ResultSet rs = ((OracleCallableStatement)call).getCursor(2);

while(rs.next()){

//do something

}