Designs and implementations of stored procedure in OceanBase

doi:10.3969/j.issn.1000-5641.2016.05.016

Abstract

Abstract:

As an extension of standard SQL(Structured Query Language), the stored procedure is an important feature in modern databases. OceanBase is a new type of
distributed database system which supports massive data processing, but the open-sourced version OceanBase does not support stored procedure, which influences its adoption in enterprises. In this paper, we analyze the principle of stored procedure and the query processing mechinism of OceanBase in detail. Then, the complete design and implementation of stored procedure which supports PL/SQL are presented.

Key words: stored procedure, SQL, OceanBase

ZHU Jun, LIU Bai-zhong, YU Sheng-jun, GONG Xue-qing, ZHOU Min-qi. Designs and implementations of stored procedure in OceanBase[J]. Journal of East China Normal University(Natural Sc, doi: 10.3969/j.issn.1000-5641.2016.05.016.

References

[ 1 ] CHANG F, DEAN J, GHEMAWAT S, et al. BigtableµA distributed storage system for structured data [C]//Proceedings of the 7th Symposium on Operating Systems Design and Implementation (OSDI). 2006: 205-218.
[ 2 ] CORBETT J C, DEAN J, EPSTEIN M, et al. SpannerµGoogle’s globally distributed database [J]. ACM Transactions on Computer Systems, 2013, 31(3): 251-264.
[ 3 ] STONEBRAKER M, WEISBERG A. The voltDB main memory DBMS [J]. IEEE Data Eng Bull, 2013, 36(2): 21-27.
[ 4 ] OceanBase [EB/OL]. [2016-03-01]. https://github.com/alibaba/oceanbase/.
[ 5 ] HURSCH C J, HURSCH J L. SQL: The Structured Query Language [M]. [S.l.]: TAB books Inc, 1988.
[ 6 ] PL/pgSQL [EB/OL]. [2016-03-02]. http://www.postgresql.org/docs/8.3/static/plpgsql.html.
[ 7 ] 彭智勇, 彭煜玮. PostgreSQL数据库内核分析 [M]. 北京: 机械工业出版社, 2011.
[ 8 ] MySQL [EB/OL]. [2016-03-02]. http://www.mysql.com/.
[ 9 ] Stored Procedure [EB/OL]. [2016-03-02]. http://en.wikipedia.org/wiki/Stored procedure.
[10] URMAN S. Oracle9i PL/SQL Programming [M].北京：机械工业出版社, 2002.
[11] 杨传辉. 大规模分布式存储系统原理解析和架构实战 [M].北京: 机械工业出版社, 2013.
[12] LEVINE J, JOHN L. Flex & Bison [M]. 南京: 东南大学出版社, 2010.
[13] AHO A V, SETHI R, ULLMAN J D. Compilers: Principles, techniques, and tools [M]. Boston: Addison-Wesley Publishing Company, 1986.
[14] 阳振坤. OceanBase关系数据库架构 [J]. 华东师范大学学报(自然科学版),2014(5): 141-148+163.
[15] 高朝瑞. GKD-Base PL/SQL存储过程和包的研究与实现 [D].长沙: 国防科学技术大学, 2004.
[16] 汪琦. 基于解释器的数据库存储过程研究 [D]. 武汉:华中科技大学, 2007.
[17] GARCIA-MOLINA H,ULLMAN J D, WIDOM J. 数据库系统实现[M]. 杨冬青, 吴愈青, 包小源, 译. 2版. 北京:机械工业出版社, 2010.
[18] 朱涛, 周敏奇, 张召. 面向OceanBase的存储过程实现技术研究 [J].华东师范大学学报(自然科学版), 2014(5): 281-289.

[1]	Yaoyi HU, Huiqi HU, Xuan ZHOU, Aoying ZHOU. SQLite-CC based on non-volatile memory cache [J]. Journal of East China Normal University(Natural Science), 2021, 2021(6): 124-134.
[2]	XU Shi-lei, WEI Xing, JIANG Hong, QIAN Wei-ning, ZHOU Ao-ying. Implementation of the parallel GroupBy and Aggregation functions in a distributed database system [J]. Journal of East China Normal University(Natural Sc, 2018, 2018(5): 56-66.
[3]	WANG Yan-zhao, HU Hui-qi, ZHANG Zhao, LIU Xiao-bing, DUAN Hui-chao. Sublink elimination and optimization in data storage and processing separation architecture [J]. Journal of East China Normal University(Natural Sc, 2018, 2018(4): 90-98.
[4]	SONG Guang-xuan, ZHAO Da-peng, WANG Xiao-ling. IM²: Improved MIN/MAX window functions optimizer in relational database [J]. Journal of East China Normal University(Natural Sc, 2018, 2018(1): 103-116.
[5]	XU Shi-lei, WANG Lei, HU Hui-qi, QIAN Wei-ning, ZHOU Ao-ying. Parallel join based on distributed system OceanBase [J]. Journal of East China Normal University(Natural Sc, 2017, 2017(5): 1-10.
[6]	XIAO Bing, GUO Jin-wei, QIAN Wei-ning. Network request service mechanisms in a scalable database management system [J]. Journal of East China Normal University(Natural Sc, 2016, 2016(5): 165-172.
[7]	MAO Xiao-xiao, DUAN Hui-chao, GAO Ming. A join algorithm based on bloom filter in OceanBase [J]. Journal of East China Normal University(Natural Sc, 2016, 2016(5): 67-74.
[8]	LONG Fei, WENG Hai-xing, GAO Ming, ZHANG Zhao. Distributed secondary index based on LSM Tree [J]. Journal of East China Normal University(Natural Sc, 2016, 2016(5): 36-44.
[9]	GU Ling, WENG Hai-Xing, HU Hua-Liang, ZHAO Qiong. OceanBase schema design for OLAP application [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 301-310.
[10]	PANG Tian-Ze, ZHANG Chen-Dong, GAO Ming, GONG Xue-Qing. Implementation of database schema design in distributed environment [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 290-300.
[11]	ZHU Tao, ZHOU Min-Qi, ZHANG Zhao. Study on stored procedure implementation oriented to OceanBase [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 281-289.
[12]	PEI Ou-Ya, LIU Wen-Jie, LI Zhan-Huai, TIAN Zheng. A nested query strategy oriented massive distributed database [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 271-280.
[13]	BIAN Hao-Qiong, CHEN Yue-Guo, DU Xiao-Yong, GAO Yan-Jie. Equi-join optimization on spark [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 261-270.
[14]	ZHOU Huan, FAN Qiu-Shi, HU Hua-Liang. Consistency and availability in OceanBase [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 103-116.
[15]	KONG Chao, QIAN Wei-Ning, ZHOU Ao-Ying. Fault tolerance in NoSQL systems: Principles and system analysis [J]. Journal of East China Normal University(Natural Sc, 2014, 2014(5): 1-16.