9CBS - expert clinic -
Main topic: How do you use string variables in the where in () clause to pass inquiry conditions in PL / SQL? Servers: Web_Gus (Penny) Credit Value: 100 Forum: Oracle Development Problem Click: 100 Reply Time: 5 Published: 2004-11-25 18:52:08
Create Or Replace Procedure Test1 (i_misecode in varcha2, o_totalcount out number) IS
Begin
o_totalcount: = 0;
SELECT Count (*) INTO (O_Totalcount) from GCM_Shohin
WHERE MISE_CD IN (i_misecode);
END TEST1;
Enter multiple conditions such as parameters i_misecode = '1001', '1002', now only when one condition is input, and can not bring quotes, if you enter more than 2 or more, what will happen?
Reply to: Web_gus (penny) () Reputation: 100 2004-11-25 18:55:46 Score: 0 Up
TOP
Reply to: LIALIN () Reputation: 99 2004-11-25 18:58:14 Score: 20 You use dynamic statements to achieve it!
v_sql: = 'SELECT Count (*) from gcm_shohin where mise_cd in (' || i_misecode || ')';
EXEC V_SQL INTO _TOTALCOUNT;
TOP
Reply to: Web_gus (penny) () Reputation: 100 2004-11-25 19:23:17 Score: 0 Thank you Alin, my colleague was written like this, but the boss did not let the dynamic statement, so he modified In this way, I am responsible for testing, now I don't know how to achieve it, he wants to use subString, recycled the skewers, feel too much, I don't know if there is a better way? SELECT Count (*) INTO (O_Totalcount) from GCM_Shohin
WHERE MISE_CD IN (i_misecode);
Where is this wrong?
TOP
Reply to: zmgowin) () Reputation: 100 2004-11-25 19:38:08 Score: 80 Change the grammar
Create Or Replace Procedure Test1 (i_misecode in varcha2, o_totalcount out number) IS
Begin
SELECT Count (*) Into Totalcount from GCM_SHOHIN
WHERE INSTR (i_misecode, mise_cd)> 0;
END TEST1;
Use this structure when testing, such as:
Exec Test1 ('' '1' ',' '2' ',: B)
Don't enter '1', '2'
TOP
Reply to: Web_gus (penny) () Reputation: 100 2004-11-26 8:39:16 Score: 0 Thank you ZMGowin (Hobei (Dragon Zan))
TOP
