[MySQL] Stored Procedure

**cms9651** · 16-03-2012, 17:10

Ciao a tutti.

Avrei necessità di creare una stored procedure in mysql ma sono a `digiuno`.

Che client devo utilizzare?

Adesso uso il Navicat 9.1.11 oppure Mysql Query Browser version 1.2.12

Una volta creata la stored procedure questa può essere richiamata ed eseguita via web o tramite vbscript?

codice:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
7 rows in set

Grazie mille

**cms9651** · 17-03-2012, 00:51

Cosa sbaglio in questa stored procedure?

[Err] 1054 - Unknown column 'q1' in 'field list'

codice:

[SQL] SET @result = 0;
Affected rows: 0
Time: 0.000ms

[SQL] 
CALL sproc(
	CONCAT(YEAR(MAKEDATE(YEAR(CURDATE()) - 1, 1)),'0'
       ,MONTH(MAKEDATE(YEAR(CURDATE()) - 1, 1)))
       ,CONCAT(YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
       ,CASE WHEN MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR)) 
        BETWEEN 1 AND 9 THEN '0' ELSE '' END
       ,MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))),'TO',@result);
[Err] 1054 - Unknown column 'q1' in 'field list'


mysql> SET @result = 0;
CALL sproc(
	CONCAT(YEAR(MAKEDATE(YEAR(CURDATE()) - 1, 1)),'0'
       ,MONTH(MAKEDATE(YEAR(CURDATE()) - 1, 1)))
       ,CONCAT(YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
       ,CASE WHEN MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR)) 
        BETWEEN 1 AND 9 THEN '0' ELSE '' END
       ,MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))),'TO',@result);
SELECT @result;
Query OK, 0 rows affected

+--------+-------------+
| q1     | q2          |
+--------+-------------+
| 259979 | 134182.7097 |
+--------+-------------+
1 row in set

+---------+
| @result |
+---------+
|       0 |
+---------+
1 row in set


BEGIN
  declare this char(6);
  set this=ymstart, result=0;
  while this <= ymstop do 
    set result = result + 
                 (select sum(my_number_of_flight_hours_e+my_number_of_flight_hours_e) 
                  from tbl_my_flight_zone_hours 
                  where years=left(this,4) 
                           and `months`=right(this,2) 
                           and left(my_flight_zone,2)=flightspec
                 );
    if right(this,2) >= '12' then
      set this = concat( 1+left(this,4), '01' );
    else
      set this = concat( left(this,4), if(right(this,2)<'09','0',''), 1+right(this,2) );
    end if;
  end while;

(
	SELECT
       sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) q1
     , sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
       DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())) q2
	FROM
		tbl_my_flight_zone_hours
	WHERE
		Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND LEFT(my_Flight_zone, 2)= 'TO'
);


SELECT 
(
(
( 
	SELECT
		COUNT(*) As Infort
	FROM `tbl_my_flight_zone`
	WHERE
		`my_Flight_zone` LIKE '%TO%'
	AND  `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
)
 / 
(result-q1+q2) * 953.8
) * 1000 
) x;

END

**Joe Taras** · 17-03-2012, 12:52

Originariamente inviato da cms9651
Cosa sbaglio in questa stored procedure?

[Err] 1054 - Unknown column 'q1' in 'field list'

codice:

[SQL] SET @result = 0;
Affected rows: 0
Time: 0.000ms

[SQL] 
CALL sproc(
	CONCAT(YEAR(MAKEDATE(YEAR(CURDATE()) - 1, 1)),'0'
       ,MONTH(MAKEDATE(YEAR(CURDATE()) - 1, 1)))
       ,CONCAT(YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
       ,CASE WHEN MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR)) 
        BETWEEN 1 AND 9 THEN '0' ELSE '' END
       ,MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))),'TO',@result);
[Err] 1054 - Unknown column 'q1' in 'field list'


mysql> SET @result = 0;
CALL sproc(
	CONCAT(YEAR(MAKEDATE(YEAR(CURDATE()) - 1, 1)),'0'
       ,MONTH(MAKEDATE(YEAR(CURDATE()) - 1, 1)))
       ,CONCAT(YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
       ,CASE WHEN MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR)) 
        BETWEEN 1 AND 9 THEN '0' ELSE '' END
       ,MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))),'TO',@result);
SELECT @result;
Query OK, 0 rows affected

+--------+-------------+
| q1     | q2          |
+--------+-------------+
| 259979 | 134182.7097 |
+--------+-------------+
1 row in set

+---------+
| @result |
+---------+
|       0 |
+---------+
1 row in set


BEGIN
  declare this char(6);
  set this=ymstart, result=0;
  while this <= ymstop do 
    set result = result + 
                 (select sum(my_number_of_flight_hours_e+my_number_of_flight_hours_e) 
                  from tbl_my_flight_zone_hours 
                  where years=left(this,4) 
                           and `months`=right(this,2) 
                           and left(my_flight_zone,2)=flightspec
                 );
    if right(this,2) >= '12' then
      set this = concat( 1+left(this,4), '01' );
    else
      set this = concat( left(this,4), if(right(this,2)<'09','0',''), 1+right(this,2) );
    end if;
  end while;

(
	SELECT
       sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) q1
     , sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
       DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())) q2
	FROM
		tbl_my_flight_zone_hours
	WHERE
		Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND LEFT(my_Flight_zone, 2)= 'TO'
);


SELECT 
(
(
( 
	SELECT
		COUNT(*) As Infort
	FROM `tbl_my_flight_zone`
	WHERE
		`my_Flight_zone` LIKE '%TO%'
	AND  `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
)
 / 
(result-q1+q2) * 953.8
) * 1000 
) x;

END

Manca l'as prima di q1 ed anche di q2

**cms9651** · 17-03-2012, 13:11

Grazie, risolto!

codice:

BEGIN
  declare this char(6);
  set this=ymstart, result=0;
  while this <= ymstop do 
    set result = result + 
                 (select sum(my_number_of_flight_hours_e+my_number_of_flight_hours_w) 
                  from tbl_my_flight_zone_hours 
                  where years=left(this,4) and `months`=right(this,2) and left(my_flight_zone,2)=flightspec
                 );
    if right(this,2) >= '12' then
      set this = concat( 1+left(this,4), '01' );
    else
      set this = concat( left(this,4), if(right(this,2)<'09','0',''), 1+right(this,2) );
    end if;
  end while;


(
	SELECT
       sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) `tot h curr month`
      ,sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) * 
           DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())) `part h curr month`
      ,(result-(sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e)) +
               (sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) * 
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())))) `result - tot h curr month + part h curr month`
	FROM
		tbl_my_flight_zone_hours
	WHERE 1
	   AND	Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	   AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
     AND
     CASE WHEN LENGTH(flightspec) = 2 THEN
	        LEFT(my_Flight_zone, 2)= flightspec
     ELSE my_Flight_zone = flightspec END
);



	SELECT
		COUNT(*) As Infort
	FROM
		`tbl_my_flight_zone`
	WHERE 1
	   AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	   AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
     AND
     CASE WHEN LENGTH(flightspec) = 2 THEN
	        LEFT(my_Flight_zone, 2)= flightspec
     ELSE my_Flight_zone = flightspec END;


UPDATE tbl_my_update t
JOIN
(
SELECT 
(
(
( 
	SELECT
		COUNT(*) As Infort
	FROM
		`tbl_my_flight_zone`
	WHERE 1
	   AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	   AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
     AND
     CASE WHEN LENGTH(flightspec) = 2 THEN
	        LEFT(my_Flight_zone, 2)= flightspec
     ELSE my_Flight_zone = flightspec END
)
 / 
(
	SELECT
       (result-(sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e)) +
               (sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) * 
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())))) `result-tot h curr month+part h curr month`
	FROM
		tbl_my_flight_zone_hours
	WHERE 1
	   AND	Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	   AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	   AND
     CASE WHEN LENGTH(flightspec) = 2 THEN
	        LEFT(my_Flight_zone, 2)= flightspec
     ELSE my_Flight_zone = flightspec END
)
  * 953.8
) * 1000
) q
) x
SET
       t.FieldsUpdate = x.q
       WHERE 1
       AND
       CASE WHEN LENGTH(flightspec) = 2 THEN
	        LEFT(my_Flight_zone, 2)= flightspec
       ELSE my_Flight_zone = flightspec END;

END

Discussione: [MySQL] Stored Procedure

Strumenti discussione

Ricerca discussione

Visualizza

[MySQL] Stored Procedure

Permessi di invio