Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.14
Description
proc
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; |
BEGIN |
DELETE FROM employees |
WHERE employees.employee_id = remove_emp.employee_id; |
tot_emps := tot_emps - 1;
|
END; |
/
|
error:
MariaDB [test]> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 |
Attachments
Activity
This example with semi colon works in pl/sql. I believe this is from one of
oracle's tutorials.
Thanks,
Manjot Singh
On Mon, Sep 9, 2019, 7:47 PM Alexander Barkov (Jira) <jira@mariadb.org>
manjot Sorry, the semicolon is OK.
So this script works without problems in MariaDB:
SET sql_mode=ORACLE; |
DELIMITER /
|
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; |
BEGIN |
DELETE FROM employees |
WHERE employees.employee_id = remove_emp.employee_id; |
tot_emps := tot_emps - 1;
|
END; |
/
|
DELIMITER ;
|
Should we close the issue?
I just tested with 10.3.14, this script works without problems:
SET sql_mode=ORACLE; |
DELIMITER /
|
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; |
BEGIN |
DELETE FROM employees |
WHERE employees.employee_id = remove_emp.employee_id; |
tot_emps := tot_emps - 1;
|
END; |
/
|
DELIMITER ;
|
You got a syntax error because you forgot the DELIMITER command before CREATE PROCEDURE.
So this query fragment:
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; |
is interpreted by the "mysql" command line client as a separate statement.
The server returns a syntax error because on unexpected end-of-query.
By the way, there should not be a semicolon after NUMBER. It should be followed by BEGIN immediately.
manjot, can you clarify please, what is the problem?
Does it work for you if you remove the redundant semicolon and add DELIMITER commands?