PL-SQL - can someone tell me what this code is doing

 FUNCTION encounter_for_dataset(p_check_answer_master_id IN check_list_answer_master.check_answer_master_id%TYPE) RETURN NUMBER
  IS

    l_key_type      check_list_answer_master.key_type%TYPE;
    l_key           check_list_answer_master.key%TYPE;
    l_encounter_id  NUMBER := 0;

  BEGIN

    IF p_check_answer_master_id IS NOT NULL THEN

      SELECT clam.key_type, NVL(clam.key,'0')
      INTO   l_key_type, l_key
      FROM check_list_answer_master clam
      WHERE clam.check_answer_master_id = p_check_answer_master_id;

      IF l_key_type = 'E' THEN

        BEGIN
          l_encounter_id := TO_NUMBER(l_key);
        EXCEPTION
          WHEN OTHERS THEN
            l_encounter_id := 0;
        END;

      END IF; -- l_key_type = 'E'

    END IF; -- p_check_answer_master_id is not null

    RETURN l_encounter_id;

  END encounter_for_dataset;

Answers


here's a line-by-line summary

FUNCTION encounter_for_dataset(
    p_check_answer_master_id IN check_list_answer_master.check_answer_master_id%TYPE 
    --this is the input variable (note the table.column%type this forces the variable to adhere that the column type if it changes
    ) RETURN NUMBER --what type to return
  IS
  /** declaration section, note it is using the table.column%type --this is good practice in case they change
  */
    l_key_type      check_list_answer_master.key_type%TYPE;
    l_key           check_list_answer_master.key%TYPE;
    l_encounter_id  NUMBER := 0;

  BEGIN
    --if the passed in value is NOT null do the logic within the IF statement
    IF p_check_answer_master_id IS NOT NULL THEN


      --insert key_type into l_key_type,
      --and insert the key (if null then 0) into l_key
      --where the check_answer_master_id is equal to the passed in variable
      --DO NOTE, IF THERE is NO DATA FOUND it will throw a NO_DATA_FOUND exception which is not handled
      SELECT clam.key_type, NVL(clam.key,'0')
      INTO   l_key_type, l_key
      FROM check_list_answer_master clam
      WHERE clam.check_answer_master_id = p_check_answer_master_id;

      -- if the key type is e, then 'cast' the l_key into a number
      --when any exception happens during the 'cast' just set it to 0
      IF l_key_type = 'E' THEN

       /**this begin..end block allows encapsulation of exception logic as it is used, pretty much a nested try/catch within the function -- this error will not bubble up to the calling program, whereas if the p-check_answer_master_id is not in clam, then that error will bubble up*/
        BEGIN 
          l_encounter_id := TO_NUMBER(l_key);
        EXCEPTION
          WHEN OTHERS THEN
            l_encounter_id := 0;
        END;

      END IF; -- l_key_type = 'E'

    END IF; -- p_check_answer_master_id is not null

       --retrun the value (note it defaults to 0)
    RETURN l_encounter_id;

  END encounter_for_dataset;

The Oracle documentation is all freely available from the internet. It is fairly readable and useful for a novice user. The PL/SQL user guide and the SQL Reference would be a good place to start if Randy hasn't answered the question to your satisfaction.

This is creating a function, which is passed in a value and returns a number. The value passed in is used to look up a table, based on the value found in the table different values are passed back. If l_key_type (which is found in the table) then the value of l_key is passed back, otherwise 0.


Need Your Help

jQuery focusout method not working within jquery Tabs

jquery asp.net jquery-ui webforms

within a ascx (usercontrol) I have a created a tab section which contains a number of fields

How to have loop sync with UTC timer and execute at every new minute?

python timer sync clock

I want to have a loop be executed once every minute when datetime.utcnow().second is zero.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.