Avatar billede joki Mester
27. juli 2011 - 14:37 Der er 8 kommentarer og
1 løsning

Dynamiske antal parametre i stored procedure

Hej

Hvordan laver jeg en stored procudere som med tager et eller flere ord i sin søgning. som på google ?

Min tanke er at forud definere f.eks. 5 input.
Hvis der er et ord sendes dette '%søgeord%' og ellers '%%'
Er der bedre alternativer ?? Dette giver jo en begrænsning på altal ord der kan søges på.

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_cases_by_words`(IN para_words VARCHAR(100))
BEGIN
SELECT DISTINCT CASE_TABLE.ID,SYMPTOMS_HEADER, SYMPTOMS,SOLUTION  FROM CASE_TABLE
WHERE SYMPTOMS LIKE para1 AND SYMPTOMS LIKE para2.....;
END
Avatar billede arne_v Ekspert
28. juli 2011 - 03:35 #1
Jeg vil foreslaa noget a la dette:

DELIMITER //
CREATE PROCEDURE vararg(_a1 VARCHAR(50), _a2 VARCHAR(50), _a3 VARCHAR(50))
BEGIN
    SELECT * FROM t1 WHERE f2 LIKE IFNULL(_a1,f2) AND f2 LIKE IFNULL(_a2,f2) AND f2 LIKE IFNULL(_a3,f2);
END//
DELIMITER ;

CALL vararg('%B%',NULL,NULL);
CALL vararg('%B%','%A%',NULL);
CALL vararg('%B%','%A%','%C%');
Avatar billede joki Mester
28. juli 2011 - 07:50 #2
Jeg er stdig begrænset til bestemt antal ord ikke ?
Hvad er det præcis der sker hvis _a1 = null så LIKE f2, som er kolonnen, man søger i ?
Er denne metode hurtigere en den jeg har ?
Avatar billede arne_v Ekspert
28. juli 2011 - 15:03 #3
f2 = IFNULL(NULL,f2) er altid sand
Avatar billede arne_v Ekspert
28. juli 2011 - 15:04 #4
Maaske er det lidt hurtigere, men fordelen ved metoden er nu mere at den er generel - den virker ogsaa med = og ogsaa for tal.
Avatar billede mrgumble Nybegynder
28. juli 2011 - 15:15 #5
MySQL understøtter ikke variabelt antal parametre. Du kan snyde den som arne_v foreslår, men som du selv bemærker er den begrænset til det antal kunstige parametre du opretter ved start (dvs. hvad sker der, hvis du pludseligt opdager du skal bruge en 4 mulighed?).

Alt efter konteksten, kan du gøre følgende:
Oprette en midlertidig tabel ("TEMPORARY TABLE"), indsætte samtlige dine søgeord i den (Gøres udenfor proceduren).
I proceduren opretter du endnu en midlertidig tabel som du sætter resultaterne i.
Herefter itererer du over dine søgeord, laver din søgning og indsætter resultatet i den midlertidige tabel med resultater.
Når det er slut, læser du resultat-tabellen.

[code]
DROP TEMPORARY TABLE IF EXISTS searchwords;
CREATE TEMPORARY TABLE searchwords ( word VARCHAR(20) );

-- Insert values via your program, script, and this is perhaps the easist place to add % to the words
INSERT INTO searchwords (word) VALUES ('cute'),('kittie');

DELIMITER $$
CREATE PROCEDURE mySearch()
BEGIN
  DECLARE word_val VARCHAR(20);
  DECLARE cur CURSOR FOR SELECT word FROM searchwords;
  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

  DROP TEMPORARY TABLE IF EXISTS myresults;
  CREATE TEMPORARY TABLE myresults
    (CASE_TABLE.ID,SYMPTOMS_HEADER, SYMPTOMS,SOLUTION);
 
  OPEN cur;
  the_loop: LOOP
    FETCH cur INTO word_val;

    -- break out of the loop if
      -- 1) there were no records, or
      -- 2) we've processed them all
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;   
 
    INSERT INTO myresults
      SELECT DISTINCT CASE_TABLE.ID,SYMPTOMS_HEADER, SYMPTOMS,SOLUTION  FROM CASE_TABLE
      WHERE SYMPTOMS LIKE word_val;
  END LOOP the_loop;
END $$
DELIMITER ;

SELECT * FROM myresults;

[/code]

Fordelen ved at anvende midlertidige tabeller (TEMPORARY TABLES) er, at de kun eksistere for den aktuelle forbindelse. Dvs. hvis to processer (f.eks. to programmer eller to scripts) hver har en forbindelse til din database, kan de ikke læse hinandens midlertidige tabel, ej heller skrive data til dem.
Men vær påpasselig med dem i f.eks. php, da forbindelsen til databasen brydes, så snart scriptet er gennemført (dvs. siden er indlæst). Her skal der andre tricks til. Men du kan stadig anvende det så længe det hele køres i det samme php-script.
(Lidt af koden kom herfra: http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/)
Avatar billede joki Mester
28. juli 2011 - 15:38 #6
Tak for den fine forklaring/løsning.
Jeg tror jeg starte med Arne's forslag tror godt brugerne kan leve dette.
Vil i dele point, hvordan er reglerne ?
Avatar billede mrgumble Nybegynder
28. juli 2011 - 15:45 #7
Du bestemmer. ;) Vent til arne_v efterlader sit sædvanlige "Jeg behøver ingen point"-svar og fordel pointene som det passer dig. :)

Jeg ved dog ikke om min metode vil være hurtigere, den er mere generel og mere kompleks, og de der cursors er i min erfaring lidt træge. Fordelen er, at der ikke er nogen øvre grænse for antallet af søgeord, udover en time-out.
Avatar billede arne_v Ekspert
02. august 2011 - 02:41 #8
En temporaer tabel og en cursor er nok ret langsomt.

Lave en SP med 10 eller 20 argumenter er formentligt godt nok udfra et praktisk synspunkt.
Avatar billede arne_v Ekspert
02. august 2011 - 02:42 #9
Og saa tror jeg at jeg bliver forvekslet med Erik Jacobsen.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester