Skip to main content

Example of an Oracle script using a cursor 

 
SET SERVEROUTPUT ON;
declare 
  cursor myCursor is (select auth_code, lastname, dob, driverlicensenumber, driverlicensestate
                      from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0);
  vAuthCode       snap_driver_record.auth_code%type;
  vLastName       snap_driver_record.lastname%type;
  vDob            snap_driver_record.dob%type;
  vLicenseNumber  snap_driver_record.driverlicensenumber%type;
  vLicenseState   snap_driver_record.driverlicensestate%type;
  vCrashCount     integer;
  vInspCount      integer;
  vCrashTot       integer;
  vInspTot        integer;
  vDirCount       integer;
begin
  select count(*) into vDirCount from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0;
  open myCursor;
  loop
    fetch myCursor into vAuthCode, vLastName, vDob, vLicenseNumber, vLicenseState;
    EXIT WHEN myCursor%NOTFOUND;
    -- get crash record count
    select count(*) into vCrashCount
      from   crash_driver cd
              ,crash_master cm
        where cd.crash_id                                         = cm.crash_id
        and   cd.updated_last_name                                = vLastName
        and   cd.updated_license_number                           = vLicenseNumber
        and   cd.updated_license_state                            = vLicenseState
        and   trunc(cd.updated_dob)                               = to_date(vDob, 'mmddyyyy')
        and   cd.dir_flag           = 1;
    vCrashTot := vCrashTot + vCrashCount;
    
    --Get Inspection record count
    select count(*) into vInspCount
      from   insp_driver idr
            ,inspection  insp
      where  insp.inspection_id                                   = idr.inspection_id
      and    idr.updated_last_name                                = vLastName
      and    idr.updated_license_number                           = vLicenseNumber
      and    idr.updated_license_state                            = vLicenseState
      and    trunc(idr.updated_dob)                               = to_date(vDob, 'mmddyyyy')
      and  idr.dir_flag            = 1;
    vInspTot := vInspTot + vInspCount;  

    dbms_output.put_line('Auth Code = ' || vAuthCode || ' Crash Count = ' || vCrashCount || ' Insp Count = ' || vInspCount);
  end loop;
  close myCursor;
  
  dbms_output.put_line('Dir Total = ' || vDirCount || ' Crash Total = ' || vCrashTot || ' Insp Total = ' || vInspTot);
end;

Comments

Post a Comment

Popular posts from this blog

Oracle: sorting by an aliased column

I recently added an ad hoc column to a heavily used query with paging and sorting. To get the sorting to work on the new aliased column it was necessary to pull the "row_number() over" clause in to an outer query. I just wanted to document how this was done for future reference. SELECT * FROM (SELECT my_table.*, ROW_NUMBER () OVER ( ORDER BY UPPER ( DECODE ( pSortDir, 'asc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data pc.legal_name))), UPPER ( DECODE ( pSortDir, 'desc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data ...
Post a Comment
Read more

Sorting an ICollection

Have you ever wanted to sort an ICollection? It took me awhile to figure this one out so I thought I should blog it. I originally posted this quite awhile ago. Since then I have discovered a much easier way to sort a collection. Here's an update on sorting collections using LINQ. Much simpler: var orderedList = customer.Users .OrderBy(x => x.UserType) .ToList(); And here is my original post on the subject. You decide which one looks easier. private IList<EventReqFormSection> GetSections() { ICollection<EventReqFormSection> sections = EventReqFormSection.GetByEventReqFormId(_businessObject.Id, ObjectManager); // sort by Display Sequence List<EventReqFormSection> sortedValues = new List<EventReqFormSection>(sections); sortedValues.Sort(new EventReqFormSection.DisplaySequenceSort()); return sortedValues; } The above function retrieves an ICollection puts it into a List object and calls the sort method usin...
2 comments
Read more

ASP.NET Anti-pattern - redone in AngularJs

I came across this code today. This is the ultimate anti-pattern for ASP.NET and jQuery. The idea here is that when a user clicks on the cdbDriverConsent checkbox; if the customer account is "inProbation", the "probationBox" will slide down with further instructions. The problem is, when looking at strictly the markup. There is absolutely no way of knowing that this is what is happening. The markup looks like this: ' ToolTip="I attest" /> ... In the code behind the OnItemDatabind function grabs the checkbox and assigns a jQuery click handler: protected void dgDIR_OnItemDataBound(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { ... if (IsOnProbation()) { ...
Post a Comment
Read more