oracle 游标问题

发布网友 发布时间：2022-04-07 22:14

我来回答

共5个回答

懂视网 时间：2022-04-08 02:35

//属性 
//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
}

Oracle游标、过程与引用类型

标签：游标   oracle11g   

热心网友 时间：2022-04-07 23:43

追答给你copy一段参考一下：

SQL> declare
2 type gsm_rec is record(
3 gsmno varchar2(11),
4 status varchar2(1),
5 price number(8,2));
6
7 type app_ref_cur_type is ref cursor return gsm_rec;
8 my_cur app_ref_cur_type;
9 my_rec gsm_rec;
10
11 begin
12 open my_cur for select gsmno,status,price
13 from gsm_resource
14 where store_id='SD.JN.01';
15 fetch my_cur into my_rec;
16 while my_cur%found loop
17 dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);
18 fetch my_cur into my_rec;
19 end loop;
20 close my_cur;
21 end;
22 /

热心网友 时间：2022-04-08 01:01

追答就是“名称、类型、顺序”都要跟游标sql里from前面的对应

热心网友 时间：2022-04-08 02:36

热心网友 时间：2022-04-08 04:27