动态游标的使用.

declare
  i integer;
  str_sql varchar2(800) default '';
  vw_hotelname hotel_master_cg.hotel_name%type;
  vw_yoyaku_no cn_fax_data.yoyaku_no%type;
    cursor1 number;
  dummy number;       
BEGIN
        str_sql := 'select yoyaku_no,g.hotel_name '
                ||' from cn_fax_data  f  inner join hotel_master_cg g on f.no=g.hotel_no '
                ||'  where no=:p_hotelno and trunc(f.uketuke_date) between trunc(:star_date) and trunc(:end_date)      '; 
               
               begin    
              cursor1 := dbms_sql.open_cursor;
              dbms_sql.parse(cursor1, str_sql, dbms_sql.native);
              dbms_sql.bind_variable(cursor1, ':p_hotelno', 28190);
              dbms_sql.bind_variable(cursor1, ':star_date', sysdate-100);             
              dbms_sql.bind_variable(cursor1, ':end_date', sysdate);   
              dbms_sql.define_column(cursor1, 1, vw_yoyaku_no,15);
              dbms_sql.define_column(cursor1, 2, vw_hotelname,50);                                
              dummy := dbms_sql.execute(cursor1);
              loop
            If dbms_sql.fetch_rows(cursor1) > 0 then
                Dbms_sql.column_value(cursor1,1,vw_yoyaku_no);
                Dbms_sql.column_value(cursor1,2,vw_hotelname);
                dbms_output.put(vw_hotelname||'  '||vw_yoyaku_no);
              End if;
              end loop;
              dbms_sql.close_cursor(cursor1);
              exception when others then
                if dbms_sql.is_open(cursor1) then
                  dbms_sql.close_cursor(cursor1);
                end if;
            end;
 
end;

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

 

DBMS_SQL
一：参数解释
1、OPEN_CURSOR：返回新游标的ID值 
2、PARSE：解析要执行的语句 
3、BIND_VARIABLE：将给定的数量与特定的变量相连接 
4、DEFINE_COLOUMN：定义字段变量，其值对应于指定游标中某个位置元素的值 
（仅用于SELECT语句） 
5、EXECUTE：执行指定的游标 
6、EXECUTE_AND_FETCH：执行指定的游标并取记录 
7、FETCH_ROWS：从指定的游标中取出记录 
8、COLUMN_VALUE：返回游标中指定位置的元素 
9、IS_OPEN：当指定的游标状态为OPEN时返回真值 
10、CLOSE_CURSOR：关闭指定的游标并释放内存 
11、LAST_ERROR_POSITION：返回出错SQL语句的字节偏移量 
12、LAST_ROW_ID：返回最后一条记录的ROWID 
13、LAST_SQL_FUNCTION_CODE：返回语句的SQL FUNCTION CODE