Oracle cursor and table

 

 

1、索引表
TYPE type_name IS TABLE OF element_type INDEX BY index_type;  
table_name            type_name ; 

element_type：集合中的元素的类型。如number、char, 也可以是记录
index_type    ：只能是整型或者字符串 pls_integer , binary_integer or char

复制代码
DECLARE
    type acct_num_type IS TABLE OF VARCHAR2(20) INDEX BY pls_integer;
    acct_num_tab acct_num_type;
    v_index pls_integer :=0;
BEGIN
    FOR i IN (SELECT ACCT_NUM FROM ACCOUNT WHERE ROWNUM<5) LOOP
        v_index := v_index+1;
        acct_num_tab(v_index) := i.ACCT_NUM;
    END LOOP;
    
    FOR i IN acct_num_tab.FIRST .. acct_num_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(acct_num_tab(i));
    END LOOP;
END;
/ 

set serveroutput on;
DECLARE
    type acct_num_type IS TABLE OF VARCHAR2(20) INDEX BY pls_integer;
    acct_num_tab acct_num_type;
    v_index pls_integer :=0;

BEGIN
    for i in (select acct_num from account where rownum<5) loop
        v_index := v_index+1;
        acct_num_tab(v_index) := i.acct_num;
    end loop;
    for i in acct_num_tab.first ..acct_num_tab.last loop
        dbms_output.put_line(acct_num_tab(i));        
    end loop;
END;
/

DECLARE
    CURSOR acct_cursor IS SELECT ACCT_NUM FROM ACCOUNT WHERE ROWNUM<5;
    acct_record account.acct_num%TYPE;
BEGIN
    open acct_cursor;
    LOOP
        fetch acct_cursor into acct_record;
        exit when acct_cursor%NOTFOUND;
        dbms_output.put_line('acct num:'||acct_record);
    END LOOP;
    close acct_cursor;
END;
/

DECLARE
    CURSOR acct_cursor(no NUMBER) IS SELECT ACCT_NUM FROM ACCOUNT WHERE ROWNUM<no;
    v_acct_num varchar2(18);
BEGIN
    open acct_cursor(6);
    loop
        fetch acct_cursor into v_acct_num;
        exit when acct_cursor%notfound;
        dbms_output.put_line('acct_num:'||v_acct_num);
    end loop;
    close acct_cursor;
END;
/

DECLARE
    CURSOR acct_cursor(no NUMBER) IS SELECT ACCT_NUM FROM ACCOUNT WHERE ROWNUM<no;
    v_acct_num varchar2(18);
BEGIN
    open acct_cursor(6);
    loop
        fetch acct_cursor into v_acct_num;
        exit when acct_cursor%notfound;
        dbms_output.put_line('acct_num:'||v_acct_num);
    end loop;
    close acct_cursor;
END;
/

DECLARE 
    v_acct_num varchar2(18);
BEGIN
    for c_acct in (SELECT ACCT_NUM FROM ACCOUNT WHERE ROWNUM<5) LOOP 
        dbms_output.put_line(c_acct.acct_num);
     END LOOP;
END;
/

declare
  l_sql varchar2(123);        -- variable that contains a query
  l_c   sys_refcursor;        -- cursor variable(weak cursor). 
  l_res your_table%rowtype;   -- variable containing fetching data  
begin
  l_sql := 'select * from your_table';

  -- Open the cursor and fetching data explicitly 
  -- in the LOOP.

  open l_c for l_sql;

  loop
    fetch l_c into l_res;
    exit when l_c%notfound;   -- Exit the loop if there is nothing to fetch.
     -- process fetched data 
  end loop;

  close l_c; -- close the cursor
end;


开发过程
CREATE [OR REPLACE] PROCEDURE procedure_name
(arg1 datatype1, arg2 datatype2,...)
IS [AS]
PL/SQL Block;

指定参数类型是不要长度

开发函数
CREATE [OR REPLACE] FUNCTION function_name
(arg1 datatype1, arg2 datatype2)
RETURN datatype1
IS|AS
 PL/SQL Block;
 

create or replace FUNCTION "TO_TEST_CACS" (DATE_STR IN VARCHAR, DATE_FMT IN VARCHAR)
RETURN DATE
IS
BEGIN
RETURN(TO_DATE(DATE_STR, DATE_FMT));
EXCEPTION
WHEN OTHERS THEN
RETURN (null);
END;

create or replace FUNCTION "TEST_SYSTEM_DATE"
RETURN DATE
IS
syst_date date;
BEGIN
select opd.curr_date into syst_date from ONLINE_PROCESSING_DATE opd;
if sql%found then
return (syst_date);
else
RETURN (sysdate);
end if;
EXCEPTION
WHEN OTHERS THEN
RETURN (sysdate);
END;

CREATE OR REPLACE PROCEDURE query_lc
(acct_n varchar2, lc out varchar2)
IS
BEGIN
SELECT LOCATION_CODE INTO LC FROM ACCOUNT WHERE ACCT_NUM=acct_n;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-2000,'no such acct');
END;
/

var acct varchar2(18)
exec query_lc('4563XXXXXXXXXXXXX',:acct);
print acct