Databases Reference
In-Depth Information
END LOOP;
END;
/
SET TIMING OFF
4.
Create a function that acts as the one in step 2, making use of bind variables:
CREATE FUNCTION CONDITIONAL_COLUMN_LEN_BIND(
TABLE_NAME IN VARCHAR2, COLUMN_NAME IN VARCHAR2,
COND_FIELD IN VARCHAR2, COND_VALUE IN VARCHAR2) RETURN NUMBER
IS
L_RESULT NUMBER := 0;
L_STMT VARCHAR2(2000);
BEGIN
L_STMT := 'SELECT MAX(LENGTH(' || COLUMN_NAME ||
')) FROM ' || TABLE_NAME || ' WHERE ' || COND_FIELD ||
' = :COND_VALUE';
EXECUTE IMMEDIATE L_STMT INTO L_RESULT USING COND_VALUE;
RETURN L_RESULT;
END;
/
5.
Recalculate the same values as in step 3 to compare the results:
SET TIMING ON
DECLARE X NUMBER := 0;
BEGIN
FOR J IN 1..10000 LOOP
X := X + CONDITIONAL_COLUMN_LEN_BIND ('CUSTOMERS',
'CUST_FIRST_NAME', 'CUST_ID', J);
END LOOP;
END;
/
SET TIMING OFF
6.
Clear the functions created:
DROP FUNCTION CONDITIONAL_COLUMN_LEN;
DROP FUNCTION CONDITIONAL_COLUMN_LEN_BIND;
