in 多个数据使用动态SQL传入字符串

in 多个数据使用动态SQL传入字符串
1.动态SQL传入字符串
CREATE OR REPLACE PROCEDURE p_test (v_str VARCHAR2) AS
  TYPE cur_type IS REF CURSOR ;
  c_fzt cur_type;
      v_sql VARCHAR2( 2000);
      v_jrh VARCHAR2( 20);
BEGIN
  v_sql:=' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_name IN ('||v_str ||')' ;
      OPEN c_fzt FOR v_sql;
      LOOP
        FETCH c_fzt INTO v_jrh;
            EXIT WHEN c_fzt %NOTFOUND ;
           dbms_output.put_line (v_jrh );
      END LOOP;
END;
 

BEGIN
p_test( '''普通电话'',''宽带''' );
END;

2.动态SQL传入数字
CREATE OR REPLACE PROCEDURE p_test (v_str VARCHAR2) AS
  TYPE cur_type IS REF CURSOR ;
  c_fzt cur_type;
      v_sql VARCHAR2( 2000);
      v_jrh VARCHAR2( 20);
BEGIN
  v_sql:=' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_spec_id IN ('||v_str ||')' ;
      OPEN c_fzt FOR v_sql;
      LOOP
        FETCH c_fzt INTO v_jrh;
            EXIT WHEN c_fzt %NOTFOUND ;
           dbms_output.put_line (v_jrh );
      END LOOP;
END;
 

BEGIN
p_test( '9,2');
END;

3.动态SQL传入SQL表
CREATE OR REPLACE PROCEDURE p_test (v_str VARCHAR2) AS
  TYPE cur_type IS REF CURSOR ;
  c_fzt cur_type;
      v_sql VARCHAR2( 2000);
      v_jrh VARCHAR2( 20);
BEGIN
  v_sql:=' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_spec_id IN ('||v_str ||')' ;
      OPEN c_fzt FOR v_sql;
      LOOP
        FETCH c_fzt INTO v_jrh;
            EXIT WHEN c_fzt %NOTFOUND ;
           dbms_output.put_line (v_jrh );
      END LOOP;
END;
 

BEGIN
p_test( 'select prod_spec_id from fzt_dd where prod_name=''普通电话''' );
END;




郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。