请教关于ORACLE动态SQL的问题 execute immediate '执行动态语'返回记录集要用包来实现,在包在定义动态游标 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 sql = 动态语'; //拼装execute immediate sql; 将泥的输入参数拼成SQL,然后“execute immediate”即可。 如果execute immediate 不好用,使用dbms_sql包解决问题。 example:set serveroutput on-- Create test tabledrop table longtable;create table longtable (longcol long) tablespace TOOLS;insert into longtable values ( rpad('x', 257, 'QWERTY') );DECLARE cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;; rc NUMBER; long_piece VARCHAR2(256); piece_len INTEGER := 0; long_tab DBMS_SQL.VARCHAR2S; long_len INTEGER := 0;BEGIN DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1); rc := DBMS_SQL.EXECUTE(cur1); rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row -- Loop until all pieces of the long column are processed LOOP DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len); EXIT WHEN piece_len = 0; DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len); long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table long_len := long_len + piece_len; END LOOP; DBMS_SQL.CLOSE_CURSOR(cur1); DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len);END;/ oracle 8 以上的应该可以用sql = 动态语'; //拼装execute immediate sql;了。 新手求助关于sql语句 oracle不能安装的其中一个原因 请求一个统计的SQL. 如何写出sql语句实现按照数值区间分组统计求和 local&remote 的速度问题,超级疑难 请问CRLF在select语句中怎样写? 如何写SQL获得Oracle 服务器的IP地址 有关数据库创建的问题 oracle blob 字段,我想实现两个数据库之间的复制怎么办? 急的想吐血自杀! 倾情奉送!-pro*c动态查询结果集写文件 郁闷!!请教IIS和Oracle冲突的问题!!!
execute immediate sql;
drop table longtable;
create table longtable (longcol long) tablespace TOOLS;
insert into longtable values ( rpad('x', 257, 'QWERTY') );DECLARE
cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;;
rc NUMBER;
long_piece VARCHAR2(256);
piece_len INTEGER := 0;
long_tab DBMS_SQL.VARCHAR2S;
long_len INTEGER := 0;
BEGIN
DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1);
rc := DBMS_SQL.EXECUTE(cur1);
rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row -- Loop until all pieces of the long column are processed
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len);
EXIT WHEN piece_len = 0;
DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len); long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table
long_len := long_len + piece_len;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur1);
DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len);
END;
/
execute immediate sql;
了。