Не могу создать функцыю. прошу помощи.

IIIpAm4iK

DROP FUNCTION `CRT_CARDS_NEW_1`//
CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`(
p_id_marki integer,
p_id_meta integer,
p_id_firms integer,
p_number_tz CHAR(11),
p_id_country integer,
p_number_pricep CHAR(11),
p_id_pricep_country integer,
p_date_in DATETIME,
p_weight_tz_in integer,
p_fio CHAR(255),
p_comment CHAR(255),
p_old_num CHAR(10),
p_number_pp CHAR(10),
p_availability_container CHAR(1),
p_weight_more60_in CHAR(1),
p_id_user_reg integer) RETURNS int(11)
BEGIN

	if IFNULL(p_number_tz,'0') != '0' then

 select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz;

 if IFNULL(@l_id_tz,0) = 0 then

 insert into ID_TZ(DM_ID,ID_NOMER,ID_DT,ID_DC_ID)

 values(@l_id_mark,p_number_tz,sysdate(),IFNULL(p_id_country,222));

 select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz;

 end if;

 end if;


insert into CARDS(
cs_guid,
cs_id_tz,
cs_id_marki,
cs_id_meta,
cs_id_firms,
cs_number_tz,
cs_id_country,
cs_number_pricep,
cs_id_pricep_country,
cs_date_in,
cs_weight_tz_in,
cs_fio,
cs_comment,
cs_old_num,
cs_number_pp,
cs_targeted_weight_out,
cs_availability_container,
cs_weight_more60_in,
cs_id_user_reg
)values(
UUID(),
@l_id_tz,
p_id_marki,
IFNULL(p_id_meta,1),
p_id_firms,
p_number_tz,
IFNULL(p_id_country,222),
IFNULL(p_number_pricep,p_number_tz),
IFNULL(p_id_pricep_country,222),
IFNULL(p_date_in,SYSDATE()),
IFNULL(p_weight_tz_in,0),
p_fio,
p_comment,
p_old_num,
p_number_pp,
IFNULL(p_weight_tz_in,0),
IFNULL(p_availability_container,'N'),
IFNULL(p_weight_more60_in,'N'),
p_id_user_reg);



select SP_VALUE+1 into @l_identifier from USP_SYS_PARAMS WHERE SP_NAME = 'CARDS_IDENTIFIER';
update USP_SYS_PARAMS set SP_VALUE = @l_identifier WHERE SP_NAME = 'CARDS_IDENTIFIER';

return LAST_INSERT_ID(); 

END
Вероятно, SQL-запрос содержит ошибку. При наличии таковой, ниже будет выведена ошибка MySQL-сервера, облегчающая диагностику проблемы.ERROR: Неизвестная пунктуация @ 32STR: //SQL: DROP FUNCTION `CRT_CARDS_NEW_1`//CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`(p_id_marki integer,p_id_meta integer,p_id_firms integer,p_number_tz CHAR(11),p_id_country integer,p_number_pricep CHAR(11),p_id_pricep_country integer,p_date_in DATETIME,p_weight_tz_in integer,p_fio CHAR(255),p_comment CHAR(255),p_old_num CHAR(10),p_number_pp CHAR(10),p_availability_container CHAR(1),p_weight_more60_in CHAR(1),p_id_user_reg integer) RETURNS int(11)BEGINif IFNULL(p_number_tz,'0') != '0' thenselect ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz;SQL-запрос: DROP FUNCTION `CRT_CARDS_NEW_1`// CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`( p_id_marki integer, p_id_meta integer, p_id_firms integer, p_number_tz CHAR(11), p_id_country integer, p_number_pricep CHAR(11), p_id_pricep_country integer, p_date_in DATETIME, p_weight_tz_in integer, p_fio CHAR(255), p_comment CHAR(255), p_old_num CHAR(10), p_number_pp CHAR(10), p_availability_container CHAR(1), p_weight_more60_in CHAR(1), p_id_user_reg integer) RETURNS int(11) BEGIN if IFNULL(p_number_tz,'0') != '0' then select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz; Ответ MySQL: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`(p_id_marki integer,p_i' at line 1
Версия сервера: 5.1.57-logне могу понять где проблема :(прикол в том что на Версия сервера: 5.0.51a все работате нормально. подскажите куда рыть.
6 ответов

IIIpAm4iK

убрал первую строчку. выводит не много другую ошибку
ОшибкаSQL-запрос: CREATE DEFINER = `root`@`%` FUNCTION `CRT_CARDS_NEW_1` (p_id_marki integer,p_id_meta integer,p_id_firms integer,p_number_tzCHAR( 11 ) ,p_id_country integer,p_number_pricepCHAR( 11 ) ,p_id_pricep_country integer,p_date_in DATETIME,p_weight_tz_in integer,p_fioCHAR( 255 ) ,p_commentCHAR( 255 ) ,p_old_numCHAR( 10 ) ,p_number_ppCHAR( 10 ) ,p_availability_containerCHAR( 1 ) ,p_weight_more60_inCHAR( 1 ) ,p_id_user_reg integer) RETURNS int( 11 ) BEGIN IF IFNULL( p_number_tz, '0' ) != '0' THEN SELECT ID_IDINTO @l_id_tz FROM ID_TZWHERE ID_NOMER = p_number_tz;Ответ MySQL: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 22


IIIpAm4iK

А что там делает два слеша?И где DELIMITER?


IIIpAm4iK

Akina,вот сделал. но тоже ошибка. только другая.
DELIMITER $$

DROP FUNCTION IF EXISTS `VMK`.`CRT_CARDS_NEW_1`$$
CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`(
p_id_marki integer,
p_id_meta integer,
p_id_firms integer,
p_number_tz CHAR(11),
p_id_country integer,
p_number_pricep CHAR(11),
p_id_pricep_country integer,
p_date_in DATETIME,
p_weight_tz_in integer,
p_fio CHAR(255),
p_comment CHAR(255),
p_old_num CHAR(10),
p_number_pp CHAR(10),
p_availability_container CHAR(1),
p_weight_more60_in CHAR(1),
p_id_user_reg integer) RETURNS int(11)
BEGIN

	if IFNULL(p_number_tz,'0') != '0' then

 select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz;

 if IFNULL(@l_id_tz,0) = 0 then

 insert into ID_TZ(DM_ID,ID_NOMER,ID_DT,ID_DC_ID)

 values(@l_id_mark,p_number_tz,sysdate(),IFNULL(p_id_country,222));

 select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz;

 end if;

 end if;


insert into CARDS(
cs_guid,
cs_id_tz,
cs_id_marki,
cs_id_meta,
cs_id_firms,
cs_number_tz,
cs_id_country,
cs_number_pricep,
cs_id_pricep_country,
cs_date_in,
cs_weight_tz_in,
cs_fio,
cs_comment,
cs_old_num,
cs_number_pp,
cs_targeted_weight_out,
cs_availability_container,
cs_weight_more60_in,
cs_id_user_reg
)values(
UUID(),
@l_id_tz,
p_id_marki,
IFNULL(p_id_meta,1),
p_id_firms,
p_number_tz,
IFNULL(p_id_country,222),
IFNULL(p_number_pricep,p_number_tz),
IFNULL(p_id_pricep_country,222),
IFNULL(p_date_in,SYSDATE()),
IFNULL(p_weight_tz_in,0),
p_fio,
p_comment,
p_old_num,
p_number_pp,
IFNULL(p_weight_tz_in,0),
IFNULL(p_availability_container,'N'),
IFNULL(p_weight_more60_in,'N'),
p_id_user_reg);



select SP_VALUE+1 into @l_identifier from USP_SYS_PARAMS WHERE SP_NAME = 'CARDS_IDENTIFIER';
update USP_SYS_PARAMS set SP_VALUE = @l_identifier WHERE SP_NAME = 'CARDS_IDENTIFIER';

return (LAST_INSERT_ID()); 

END$$

DELIMITER ;
SQL-запрос: CREATE DEFINER=`root`@`%` FUNCTION `CRT_CARDS_NEW_1`( p_id_marki integer, p_id_meta integer, p_id_firms integer, p_number_tz CHAR(11), p_id_country integer, p_number_pricep CHAR(11), p_id_pricep_country integer, p_date_in DATETIME, p_weight_tz_in integer, p_fio CHAR(255), p_comment CHAR(255), p_old_num CHAR(10), p_number_pp CHAR(10), p_availability_container CHAR(1), p_weight_more60_in CHAR(1), p_id_user_reg integer) RETURNS int(11) BEGIN if IFNULL(p_number_tz,'0') != '0' then select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz; if IFNULL(@l_id_tz,0) = 0 then insert into ID_TZ(DM_ID,ID_NOMER,ID_DT,ID_DC_ID) values(@l_id_mark,p_number_tz,sysdate(),IFNULL(p_id_country,222)); select ID_ID into @l_id_tz from ID_TZ where ID_NOMER = p_number_tz; end if; end if; insert into CARDS( cs_guid, cs_id_tz, cs_id_marki, cs_id_meta, cs_id_firms, cs_number_tz, cs_id_country, cs_number_pricep, cs_id_pricep_country[...] Ответ MySQL: #1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)


IIIpAm4iK

Эммм... Вам перевести собщение об ошибке?


IIIpAm4iK

Эммм... Вам перевести собщение об ошибке?
спасибо. разобрался.вечером тупить начинаю :(


IIIpAm4iK

IIIpAm4iK,Серега, ты?