程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle游標、過程與引用類型

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
}

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved