1.輸入一個參數,要求判斷表內容中字段存在的5,非5,所有等三種情况
方案1:
create or replace package body allan is procedure insert_test( imput VARCHAR2 , cur out mytype ) is begin open cur for select * from odrm o where (imput= 'y' and o.status=5) or (imput='n' and o.status<>5) or (imput='%'); end insert_test; end allan;
巧妙使用or的次序解决判斷問題
方案2:
create or replace package body allan is procedure insert_test( input VARCHAR2 , cur out mytype ) is
v_imput :=
WHEN (input=='y')
THEN '^[5]'
WHEN (input=='n')
THEN '^[01234]'
ELSE '^[012345]'
begin open cur for select * from odrm o where REGEXP_LIKE(b.ODR_STATUS, V_ISPAY);
end insert_test;
end allan;
REGEXP_LIKE 函數加上正規表達式