In this case how the parameters pass

Question

I have a procedure in oracle

CREATE OR REPLACE 
PROCEDURE ReportCalculate
  (
        param_acTypeId in NUMBER
)
  AS 
  sqlstr VARCHAR2(500);
  result NUMBER;
  BEGIN
    sqlstr:='select count(1) from table1 where 1=1 and AC_TYPE_LEVEL1_ID=:acTypeId';
    execute immediate sqlstr into result using param_acTypeId;
    DBMS_OUTPUT.PUT_LINE(result);
  END;

But sometimes I would like to query all the data, sql looks like this select count (1) from table1 where 1 = 1 and AC_TYPE_LEVEL1_ID = AC_TYPE_LEVEL1_ID, then how should the parameters pass, or param_acTypeId should have any default value? Is it only in the stitching sql when to judge it?

Answer 1

A typical method would be to accept NULL as meaning "all":

sqlstr := 'select count(1) from table1 where AC_TYPE_LEVEL1_ID = :acTypeId or :acTypeId is null';

I should note that this version precludes the use of indexes. If performance is an issue, then use two queries:

if param_acTypeId is null then
    sqlstr := 'select count(1) from table1';
    execute immediate sqlstr into result;
else 
    sqlstr := 'select count(1) from table1 where AC_TYPE_LEVEL1_ID = :acTypeId';
    execute immediate sqlstr into result using param_acTypeId;
end if;
DBMS_OUTPUT.PUT_LINE(result);