gpt4 book ai didi

mysql - 如何用mysql在触发器中声明变量?

转载 作者:行者123 更新时间:2023-11-29 21:49:36 26 4
gpt4 key购买 nike

CREATE OR REPLACE TRIGGER ATTENDANCE_NOTIFY AFTER INSERT OR UPDATE ON ATTENDANCE
FOR EACH ROW
DECLARE
V_STUDENT_ID STUDENT.STUDENT_ID%TYPE := NULL;
V_HOD_ID HEAD_OF_DEPARTMENT.HOD_ID%TYPE := NULL;
V_SUBCODE STUDENT.SUBCODE%TYPE := NULL;
V_ATTENDANCE ATTENDENCE%TYPE := NULL;
BEGIN
SELECT SUB_CODE, SUB_NAME INTO V_SUB_CODE, FROM SUBJECT WHERE STUDENT_ID = :NEW.STUDENT_ID;
SELECT STUDENT_ID INTO V_STUDENT_ID FROM STUDENT WHERE SUBJECT_CODE = :NEW.SUBJECT_CODE;
SELECT HOD_ID INTO V_HOD_ID FROM STUDENT_HOD WHERE STUDENT_ID = :NEW.STUDENT_ID;
SELECT ATTENDENCE INTO V_ATTENDENCE FROM ATTENDENCE WHERE STUDENT_ID=:NEW_STUDENT_ID
IF (V_ATTENDENCE IS NOT NULL AND V_SUB_CODE IS NOT NULL AND V_STUDENT_ID IS NOT NULL AND V_HOD_ID IS NOT NULL) THEN
IF (:NEW.ATTENDENCE < (V_ATTENDENCE * 0.85)) THEN
INSERT INTO NOTIFY VALUES(V_HOD_ID, V_STUDENT_ID || ' (ID:- ' || :NEW.STUDENT_ID ||') HAS LESS THAN 85% ATTENDENCE IN SUBJECT ' || V_SUB_CODE);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;

我在声明时遇到语法错误

最佳答案

MySQL 中无法引用列的数据类型。 DECLARE 必须静态声明变量的类型和大小。

类似的东西

DECLARE myvar VARCHAR( 8 ) -- This is valid in Mysql

不是

DECLARE myvar mytable.myfield%TYPE    --This is invalid in Mysql

希望这有帮助。

关于mysql - 如何用mysql在触发器中声明变量?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33775826/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com