It is currently Mon May 01, 2017 2:20 am

All times are UTC




 Page 1 of 1 [ 1 post ] 
Author Message
 Post subject: Procdimientos Almacenados / Stored Procedure
PostPosted: Tue May 03, 2011 2:46 am 

Joined: Thu Dec 30, 2010 12:57 am
Posts: 7
Creamos el procedimiento almacenado desde el cliente mysql de nuestra preferencia

  1. DELIMITER $
  2. DROP PROCEDURE IF EXISTS born_in_year;
  3. CREATE PROCEDURE born_in_year( year_of_birth INT )
  4. BEGIN
  5.     SELECT first_name, last_name, birth, death from president where year( birth ) = year_of_birth;
  6. END $
  7. DELIMITER ;
  8.  
  9.  
  10. DELIMITER $
  11. DROP PROCEDURE IF EXISTS count_born_in_year;
  12. CREATE PROCEDURE `count_born_in_year`(IN `year_of_birth` int,OUT `how_many` int)
  13. BEGIN
  14.     DECLARE c CURSOR FOR
  15.     SELECT COUNT(*) FROM president WHERE YEAR(birth) = year_of_birth;
  16.     OPEN c;
  17.     FETCH c INTO how_many;
  18.     CLOSE c;
  19. END$
  20.  


para ejecutarlo simplemento lo hacenmos pro medio de un query

cText = "CALL born_in_year( 1908 )"
oQry := oServer:Query( cText )

el siguiente llamdo es importante para "terminar" el proceso
oServer:NextResult()

veammos el ejemplo completo

  1. #include "tdolphin.ch"
  2.  
  3. #define CRLF Chr( 13 ) + Chr( 10 )
  4.  
  5. PROCEDURE Main()
  6.    
  7.    LOCAL cText := ""
  8.    LOCAL oQry, oServer
  9.    
  10.    SET CENTURY ON
  11.    SET DATE FORMAT "dd/mm/yyyy"
  12.    
  13.    D_SetCaseSensitive( .T. )
  14.    
  15.    IF ( oServer := ConnectTo() ) == NIL
  16.       RETURN NIL
  17.    ENDIF
  18.    cls
  19.    
  20.    cText = "CALL born_in_year( 1908 )"
  21.  
  22.    oQry := oServer:Query( cText )
  23.    
  24.    DolphinBrw( oQry, "Test" )
  25.    
  26.    oServer:NextResult()
  27.    
  28.    oQry:End()
  29.  
  30.    cText = "CALL born_in_year( 1913 )"
  31.  
  32.    oQry := oServer:Query( cText )
  33.    
  34.    DolphinBrw( oQry, "Test" )
  35.    
  36.    oServer:NextResult()
  37.    
  38.    oQry:End()
  39.  
  40.    cText = "call count_born_in_year( 1913, @count )"
  41.  
  42.    oQry := oServer:Execute( cText )
  43.    
  44.    oServer:NextResult()
  45.    
  46.    oQry := oServer:Query( "select @count as count" )
  47.      
  48.    ? "count is:"
  49.    ?? oQry:count
  50.    
  51.    oQry:End()
  52.    
  53.    
  54.    
  55. RETURN
  56.  


Image
Image



esto verifica que no exista otro query con resultado (por motivos de posibles multiples sentencias en los precedimeintos) y cierra el ciclo de existir otro query con resultado deberiamos hacer lo siguiente

oQry:LoadNextQuery( )

asi cargamnos automaticamente el proximo resultado de un query para multi sentencias

ejemplo de multiples sentencias

  1. #include "tdolphin.ch"
  2.  
  3.  
  4. FUNCTION Main()
  5.  
  6.    LOCAL oServer, oQry
  7.  
  8.    D_SetCaseSensitive( .T. )
  9.    Set_MyLang( "esp" )
  10.    
  11.    IF ( oServer := ConnectTo() ) == NIL
  12.       RETURN NIL
  13.    ENDIF
  14.    
  15.    oQry = oServer:Query( "select * from president; select * from student" )
  16.  
  17.    DolphinBrw( oQry, "President" )
  18.    
  19.    oQry:LoadNextQuery( )
  20.    
  21.    DolphinBrw( oQry, "Student" )
  22.          
  23.    oQry = NIL
  24.    
  25.    oServer:End()
  26.    
  27. RETURN NIL
  28.  
  29.  
  30. #include "connto.prg"
  31. #include "brw.prg"
  32.  


Image
Image


Offline
 Profile Send private message  
 
Display posts from previous:  Sort by  
 Page 1 of 1 [ 1 post ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

cron