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