Before we plunge into code analysis techniques, let us write down a standard PL/SQL program for demonstrating code analysis, profiling, and tracing. The following PL/SQL procedure calculates the score of a user in an exam (Note that negative scoring is applicable). The procedure P_CALC_USER_POINTS
declares a local function and a procedure to calculate the points:
/*Create a PL/SQL procedure*/ CREATE OR REPLACE PROCEDURE p_calc_user_points (p_user VARCHAR2 DEFAULT USER, p_correct NUMBER, p_wrong NUMBER) IS l_num NUMBER; /*A local function F_CALC_POINTS */ FUNCTION f_calc_points (p_ques NUMBER, p_factor NUMBER) RETURN NUMBER IS BEGIN RETURN (p_ques*p_factor); END; /*A local procedure */ PROCEDURE P_NET_CALC (p_net_points OUT NUMBER) IS BEGIN p_net_points := f_calc_points (p_correct,4) + f_calc_points (p_wrong,-2); END; /*Main procedure body */ BEGIN p_net_calc (l_num); DBMS_OUTPUT.PUT_LINE (USER||' earned '||TO_CHAR (l_num)||' points'); END; /
We will...