Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

23 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

19 maart 2024, 10:29:09

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: querrie in stored procedure  (gelezen 19382 keer)

0 leden en 1 gast bekijken dit topic.

Offline Tomjan
  • Net nieuw
  • *
  • Berichten: 1
  • Geslacht: Man
querrie in stored procedure
« Gepost op: 21 februari 2017, 11:40:13 »
Hallo iedereen,

Kan iemand mij wat meer uitleg geven over de structuur en opbouw van de onderstaande querries? en hoe ik dit moet lezen? Ik ken al sommige functies van sql maar onderstaand querries vind ik verwarrend.

[size=2][font=arial, sans-serif]drop procedure DagEnUurVolgendeVluchtPerLuchthaven ;   [/font][/size]

[size=2][font=arial, sans-serif]delimiter //[/font][/size]

[size=2][font=arial, sans-serif]create procedure DagEnUurVolgendeVluchtPerLuchthaven()[/font][/size]
[size=2][font=arial, sans-serif]    begin [/font][/size]
[size=2][font=arial, sans-serif]        select a.AirportName Luchthaven, min(fd.depday) Dag, min(fd.deptime) Uur[/font][/size]
[size=2][font=arial, sans-serif]            from airport a[/font][/size]
[size=2][font=arial, sans-serif]                join route r on r.`from`= a.AirportID[/font][/size]
[size=2][font=arial, sans-serif]                join flight f on f.routeid = r.routeID[/font][/size]
[size=2][font=arial, sans-serif]                join flightdep fd on fd.flightid = f.FlightID[/font][/size]
[size=2][font=arial, sans-serif]            where fd.DepDay >= DayOfWeek(now())[/font][/size]
[size=2][font=arial, sans-serif]                and fd.DepTime > Time(now())[/font][/size]
[size=2][font=arial, sans-serif]            group by a.AirportName ;[/font][/size]
[size=2][font=arial, sans-serif]    end //[/font][/size]

[size=2][font=arial, sans-serif]delimiter ;[/font][/size]


[size=2][font=arial, sans-serif]CREATE PROCEDURE RoutesNaarAirport(in MyAirportode char(3)) 
    begin 
    
drop table if exists AfstandsCategorieen;
        create temporary table AfstandsCategorieen(CategorieNaam char(21), van int, tot int);
        insert into AfstandsCategorieen values ('short distance',0, 999);
        insert into AfstandsCategorieen values ('intermediate distance',1000, 3000);
        insert into AfstandsCategorieen values ('long distance',3001, 999999);
        
        select a_from.Airportname as van, a_to.AirportName as naar, r.distance as afstand, ac.CategorieNaam as afstandscategorie
            from airport as a_from, airport as a_to, route as r, AfstandsCategorieen as ac
            where (r.`from`   =  a_from.AirportID )
and   (r.`to`     =  a_to.AirportID )
            and   (r.Distance between ac.van and ac.tot)
            and   (a_to.AirportCode = MyAirportode);
        
        -- drop table if exists AfstandsCategorieen;   
        
end//

delimiter ;[/font][/size]



Hopelijk kan er iemand mij verder helpen hiermee?

Groeten
Tom

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: querrie in stored procedure
« Reactie #1 Gepost op: 21 februari 2017, 12:56:01 »
Hoi Tom,

De titel van dit topic is "SQL Server procedure", maar de SQL die je hebt geplaatst bevat een paar elementen die in SQL Server niet correct zijn. Ik denk dat dit MySQL is? Of wellicht Oracle?
Gelukkig zijn de verschillen beperkt en kan ik de code nog wel toelichten.

Onderstaande is overigens een beschrijving van wat de queries LOGISCH doen. De meeste DBMSen zullen de query in een andere volgorde uitvoeren. SQL definiëert het resultaat op basis van logische stappen maar de DBMS is vrij om dat resultaat op eender welke wijze te verkrijgen. En dus probeert de DBMS de meest efficiënte manier te vinden.

EERSTE QUERY/PROCEDURE:

Kern van de query is de FROM clause (inclusief alle JOINs). Ik ken het schema van de database niet maar op basis van wat ik hier zie lijkt het er op dat FlightDep alle departures bevat (bv "vlucht KL903, zaterdag, 14:03"). Die wordt gejoind met de tabel Flight (met details over vlucht KL903, o.a. dat deze route XYZ vliegt); met tabel Route (die vermoedelijk duidelijk maakt dat route XYZ van AMS naar SEA gaat); en tenslotte met Airport op basis van de vertrek-luchthaven ("from") - dus AMS is "Amsterdam Schiphol Airport".
(Zoals je ziet heb ik in omgekeerde volgorde gelezen. Bij inner joins is de volgorde irrelevant en deze volgorde beschrijft voor mijn gevoel het beste wat hier gebeurt)
Het gebruik van kolomnamen als "FlightID" en "AirportID" suggereert dat de ontwerpen van deze tabellen het bizarre idee heeft dat het handiger is om nietszeggende numerieke codes te gebruiken voor identificatie van luchthavens en vluchten in plaats van de in de luchtvaart alom ingeburgerde codes. Een dramatisch slechte keuze, als dat zo is, maar misschien heb ik het mis en zijn alleen de namen onhandig gekozen.

Tweede deel van de query is de WHERE. Er wordt in dit geval alleen op de FlightDep tabel gefilterd en de rijen die worden gekozen moeten aan twee criteria voldoen: de DepDay moet groter zijn dan de "DayOfWeek" van de huidige datum/tijd en tijd [ik neem aan dat DayOfWeek een getal van 1-7 oplevert dat de dag van de week voorstelt] en de DepTime moet groter zijn dan de "Time" van de huidige datum/tijd.
De bedoeling is hier vermoedelijk om alleen vluchten te selecteren in de toekomst, maar dit gaat op twee punten miis. Ten eerste: Als je dit vandaag om 12:30 draait, dan zie je de vluchten van morgen 11:00 niet (omdat de tijd kleiner is). En ten tweede, als je dit op dag "7" van de week draait, dan zie je de vluchten van dag "1" niet, terwijl dat wel de volgende dag is.

De GROUP BY zorgt ervoor dat de overgebleven rijen worden samengevoegd tot één rij per AirpoirtName.
In de SELECT zie je dat die naam getoond wordt, samen met de laagste DepDay en de laagste DepTime. Ik denk dat de bouwer van deze stored procedure hoopte dat het resultaat de dag van de week en de tijd van de eerstvolgende departure (per vliegveld) oplevert. Dat is echter niet gegarandeerd zo, omdat de twee MIN functies onafhankelijk zijn en dus uit verschillende rijen kunnen komen. Stel dat er op een heel klein vliegveld twee vluchten vertrekken, om maandag om 15:40 en op dinsdag om 12:08, dan is de laagste weekdag Maandag en de laagste tijdstip 12:08 - de query zal maandag 12:08 rapporteren, als er helemaal geen enkele vlucht vertrekt!


TWEEDE QUERY/PROCEDURE:

De stored procedure wordt aangeroepen met een input parameter, een luchthavencode.

Eerst wordt een tijdelijke tabel verwijderd (indien deze bestaat), gemaakt, en gevuld met wat vaste data (de onder- en bovengrezen van short, intermediate, en long distance). Erg vreemde keuze. Waarom niet een vaste tabel maken? Dat scheelt de DBMS werk om steeds dezelfde tabel te maken en weer weg te kieperen, en het maakt het makkelijker om de data in deze tabel te wijzigen als de wensen wijzigen zonder dat code hoeft te worden aangepast.

De FROM in de query noemt vier tabellen met komma's ertussen. Dat is een alternatieve notatie voor de meer expliciete JOIN syntax in de eerste query; je wordt in dit geval geacht in de WHERE de join criteria te benoemen. Dit is een verouderde en verwarrende syntax; het is toegestaan maar ten zeerste afgeraden om deze te gebruiken.

De vier tabllen die gejoint worden zijn twee kopiëen van de Airport tabel, de Route tabel, en de tijdelijke tabel die eersder gemaakt is. De routes worden gefilterd: we willen alleen routes met de opgegeven luuchthaven als bestemming. Dan worden de routes gejoind met de tijdelijke tabel op basis van de afstand. Er wordt ook gejoind met twee kopiën van de Airport tabel, om zo de naam van zowel vertrek- als aankomst-luchthaven te kunnen tonen.

In de SELECT worden die twee luchthaven-namen getoond, samen met de afstand en de categorie van die afstand volgens de tijdelijke tabel.

Deze query bevat slechts één inhoudelijke fout: als een vlucht bv 999,3 km lang is, dan is dat een afstand die in geen enkele categorie valt en wordt de hele vlucht niet getoond! (De join is een inner join, dus als er geen match is vervalt de rij uit het resultaat).

Ik hoop dat je hiermee verder kan!
--
Hugo Kornelis, SQL Server MVP