Oracle兼容-存储过程-CREATE OR REPLACE

1. 语法

  1. CREATE OR REPLACE PROCEDURE proc_name ...
  3. CREATE OR REPLACE FUNCTION func_name ...

2. 定义和用法

GreatSQL支持 CREATE OR REPLACE 创建存储过程/存储函数，无论是在 DEFAULT 还是 ORACLE 模式均支持。

3. 示例

  1. -- 1. DEFAULT模式
  2. greatsql> DELIMITER //
  3. greatsql> SET sql_mode = DEFAULT; //
  5. greatsql> CREATE OR REPLACE PROCEDURE p1()
  6. BEGIN
  7.   SELECT 3306 AS PORT FROM DUAL;
  8. END;//
  10. greatsql> CALL p1()//
  11. +------+
  12. | PORT |
  13. +------+
  14. | 3306 |
  15. +------+
  17. -- 在DEFAULT模式下还支持不带BEGIN ... END语句
  18. greatsql> CREATE OR REPLACE PROCEDURE p1()
  19.   SELECT 3306 AS PORT FROM DUAL; //
  21. greatsql> CALL p1()//
  22. +------+
  23. | PORT |
  24. +------+
  25. | 3306 |
  26. +------+
  28. -- 2. ORACLE模式
  29. greatsql> SET sql_mode = ORACLE; //
  31. greatsql> CREATE OR REPLACE PROCEDURE p2()
  32. AS
  33. BEGIN
  34.   SELECT 3306 AS PORT FROM DUAL;
  35. END; //
  37. greatsql> CALL p2()//
  38. +------+
  39. | PORT |
  40. +------+
  41. | 3306 |
  42. +------+
  44. -- 和DEFAULT模式不同，在ORACLE模式下不支持不带BEGIN ... END语句
  45. greatsql> CREATE OR REPLACE PROCEDURE p2()
  46. AS
  47.   SELECT 3306 AS PORT FROM DUAL;
  48. //
  49. ERROR 1064 (42000): 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 'SELECT 3306 AS PORT FROM DUAL' at line 3

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx