<title>In multiple data using dynamic SQL to pass in a string</title> 1. Dynamic SQL Incoming stringCREATE OR REPLACE PROCEDURE p_test (V_str VARCHAR2) as TYPE Cur_type is REF CURSOR ;c_fzt Cur_type;V_sql VARCHAR2( -);V_JRH VARCHAR2( -);BEGINV_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;
BEGINp_test( ' ordinary phone ', ' broadband ' );END;
2. Dynamic SQL Incoming numbersCREATE OR REPLACE PROCEDURE p_test (V_str VARCHAR2) as TYPE Cur_type is REF CURSOR ;c_fzt Cur_type;V_sql VARCHAR2( -);V_JRH VARCHAR2( -);BEGINV_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;
BEGINp_test( ' 9,2 ');END;
3. Dynamic SQL incoming SQL tableCREATE OR REPLACE PROCEDURE p_test (V_str VARCHAR2) as TYPE Cur_type is REF CURSOR ;c_fzt Cur_type;V_sql VARCHAR2( -);V_JRH VARCHAR2( -);BEGINV_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;
BEGINp_test( ' Select prod_spec_id from fzt_dd where prod_name= ' ordinary phone ' );END;
From for notes (Wiz)
In multiple data using dynamic SQL to pass in a string
