各位大侠快来救命 只能用动态sql,查一下动态sql的文档吧 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 编译时认为是一个字符串用动态sql 请简单说明怎么使用动态SQL。谢谢 dbms_sql就是动态包。挺麻烦的,查下资料罢。 在oracle8.0.5中:用dbms_sql来实现CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS cursor_name INTEGER; rows_processed INTEGER;BEGIN cursor_name := dbms_sql.open_cursor; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x', dbms_sql.native); DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary); rows_processed := dbms_sql.execute(cursor_name); DBMS_SQL.close_cursor(cursor_name);EXCEPTIONWHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cursor_name);END;在oracle8.1.5中:用execute immediate来实现tsql:='select * from t_item' || ' where fitemid=' || fitemid;execute immediate tsql; 我写了个动态sql例子,参考以下 nSQL := DBMS_SQL.OPEN_CURSOR; sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME'; DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ; DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', table_name ) ; DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ; ret := DBMS_SQL.EXECUTE( nSQL ) ; LOOP IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_V DBMS_SQL.COLUMN_VALUE( nSQL, 1, my_count ) ; END LOOP; 大侠们看看我这样出错,我用的是一条插入语句。应该这样是不能和游标绑定的?create or replace procedure twtest( GYSHO varchar,NYO char,NY1 char,ZBO char)is sqls string(1000); cursor_name INTEGER; rows_processed INTEGER;BEGIN cursor_name := dbms_sql.open_cursor; sqls:='Insert into find_zbtmp(id,sxh,ZB,Kmdm,XPZH,PZRQ,ZYNR,JFJE,DFJE,'|| ' YEJE,GYSH,ODR,NY) '|| ' Select 0,A.SXH,A.ZB,B.KMDM,'|| ' decode(A.pzid,1,''现金'',3,''银行'',5,''转帐'')||''-''||to_char(A.pzbh,''00000''),'|| ' A.PZRQ,B.ZYNR,B.JFJE,B.DFJE,0,B.GYSH,'|| ' Rpad(B.Kmdm,15,''0'')||RPad(B.gysh,15,''0'')||TO_CHAR(A.PZRQ,''YYYYMMDD'')||''00000'','|| ' TO_CHAR(A.PZRQ,''YYYYMM'')'|| ' From Find31 a,Find32 b'|| ' Where a.SXH=B.SXH And A.ZB=:ZB0 And A.NY>=:NY0 And A.NY<=:NY1'|| ' And A.ztm IN (''NA'',''OP'',''CL'') And B.GYSH IN (:GYSH)'|| ' And B.KMDM IN (Select KMDM From Find10 Where'|| ' ZB=:ZB0 And fzhs_g=''Y'')'; DBMS_SQL.PARSE(cursor_name, sqls, dbms_sql.native); DBMS_SQL.BIND_VARIABLE(cursor_name, ':GYSH', GYSHO); DBMS_SQL.BIND_VARIABLE(cursor_name, ':NY0', NYO); DBMS_SQL.BIND_VARIABLE(cursor_name, ':NY1', NY1); DBMS_SQL.BIND_VARIABLE(cursor_name, ':ZB', ZBO); rows_processed := dbms_sql.execute(cursor_name); DBMS_SQL.close_cursor(cursor_name);end; 编译可以通过,只是运行到绑定的时候出错DBMS_SQL.PARSE(cursor_name, sqls, dbms_sql.native); 急!!!ora-12801 并行查询服务器P004中发出错误信号 这个分段commit的语句写对了吗? 启动数据库错误 新手问题,怎样现实orcale的所有表? .net 中怎样能把oracle数据进行整库导出? sequence自动置位的问题~~高手救命~~~ 请问在VB中用ADO连接oracle数据服务器的字符串是什么? oracle中怎么对创建的表结构修改? 如何得到几个字段中最大值? ADO连接Oracle数据库出错 在oracle能设定像sql server2000那样的自动标识ID么? 游标变量在asp中的使用问题
用动态sql
挺麻烦的,查下资料罢。
用dbms_sql来实现
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
在oracle8.1.5中:
用execute immediate来实现
tsql:='select * from t_item' ||
' where fitemid=' || fitemid;
execute immediate tsql;
nSQL := DBMS_SQL.OPEN_CURSOR;
sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME';
DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ;
DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', table_name ) ;
DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ;
ret := DBMS_SQL.EXECUTE( nSQL ) ;
LOOP
IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_V
DBMS_SQL.COLUMN_VALUE( nSQL, 1, my_count ) ;
END LOOP;
is
sqls string(1000);
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
sqls:='Insert into find_zbtmp(id,sxh,ZB,Kmdm,XPZH,PZRQ,ZYNR,JFJE,DFJE,'||
' YEJE,GYSH,ODR,NY) '||
' Select 0,A.SXH,A.ZB,B.KMDM,'||
' decode(A.pzid,1,''现金'',3,''银行'',5,''转帐'')||''-''||to_char(A.pzbh,''00000''),'||
' A.PZRQ,B.ZYNR,B.JFJE,B.DFJE,0,B.GYSH,'||
' Rpad(B.Kmdm,15,''0'')||RPad(B.gysh,15,''0'')||TO_CHAR(A.PZRQ,''YYYYMMDD'')||''00000'','||
' TO_CHAR(A.PZRQ,''YYYYMM'')'||
' From Find31 a,Find32 b'||
' Where a.SXH=B.SXH And A.ZB=:ZB0 And A.NY>=:NY0 And A.NY<=:NY1'||
' And A.ztm IN (''NA'',''OP'',''CL'') And B.GYSH IN (:GYSH)'||
' And B.KMDM IN (Select KMDM From Find10 Where'||
' ZB=:ZB0 And fzhs_g=''Y'')';
DBMS_SQL.PARSE(cursor_name, sqls,
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':GYSH', GYSHO);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':NY0', NYO);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':NY1', NY1);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':ZB', ZBO);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
end;
DBMS_SQL.PARSE(cursor_name, sqls,
dbms_sql.native);