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

in 多个数据使用动态SQL传入字符串<?xml version="1.0" encoding="UTF-8"?>

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 ;

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/highroom/p/7bade31f9db2f8b6a6c12a9758a882f7.html