Over the last couple of months I have been working on a MySQL based system for my current customer. This made me realize, once again, how easy we can do stuff in an Oracle based system.
On one hand this is kind of awkward, not being able to do what you are used to, on the other hand it makes you come up with solutions to fix the issues. Some are better than others, but in the end it all works.
These are just a few of the quirks I have come across and for which I had to find another solution.<\/p>\n\n\n\n\n\n\n\n
In my PL\/SQL code I like to use packages for just about everything. This way I can logically group programs that belong together. In MySQL there is no object like package. Everything is a stand-alone function or procedure. That also means there are no private programs. On the other hand, in this project I have a user that \u2018owns\u2019 the objects (tables, view, programs etc.) and other users a granted access to the objects they need. In this case they have no access to tables and just read access to views.<\/p>\n\n\n\n
In Oracle you can create or replace almost every object (except for tables). In MySQL you can use create or replace only on views. In all the other situations you have to drop the object first before you can create a new version. Not a big issue, since I am using scripts for the objects anyway, so one of the first lines is to drop the object.<\/p>\n\n\n\n
Somehow, we want to minimize the errors when running scripts, even if these errors aren\u2019t a problem. If you try to drop an object that doesn\u2019t exist, the database (Both Oracle and MySQL) will return an error, something like \u2018object to be dropped doesn\u2019t exist\u2019. I actually don\u2019t see this as an error, because the end result is the same. But somehow DBA\u2019s are allergic to these kinds of errors, or any error. MySQL provides us with the IF EXISTS syntax. This will never result in an error, just the desired situation, the object gone.
In Oracle I have created something similar. In this case it is an anonymous block, but you could also create a stored procedure for this. Also, if you need to suppress a different error code, you can adapt the code accordingly.<\/p>\n\n\n
\ndeclare\n table_or_view_does_not_exist exception;\n pragma exception_init(table_or_view_does_not_exist, -00942);\n object_does_not_exist exception;\n pragma exception_init(object_does_not_exist, -04043);\n sequence_does_not_exist exception;\n pragma exception_init(sequence_does_not_exist, -02289);\nbegin\n execute immediate q'[drop table t purge]'; -- execute the drop\nexception\n when table_or_view_does_not_exist\n or object_does_not_exist\n or sequence_does_not_exist then null; -- on purpose, hide any error\nend;\n\/\n<\/pre><\/div>\n\n\nFunctions needs purity info<\/h2>\n\n\n\n
Every function you create need information about its purity level. It doesn\u2019t have to be accurate (I found out ;-)) but you have to add at least one of the items: DETERMINISTIC, NO SQL, READS SQL DATA. You can, and probably should, add one or more of the other items, although some combinations are not allowed, which makes sense, I think.<\/p>\n\n\n\n
Delimiter<\/h2>\n\n\n\n
The default delimiter for a statement is the semi-colon (;), in Oracle this is officially the slash (\/). When you want to create a program, the first thing you do is tell MySQL to use a different delimiter to end a statement.<\/p>\n\n\n
\nDelimiter \/\/\nCreate procedure foo\nBegin\n Select \u2018bar\u2019;\nEnd\/\/\nDelimiter ;\n<\/pre><\/div>\n\n\nNo DBMS_OUTPUT.PUT_LINE<\/h2>\n\n\n\n
I don\u2019t know about you, but when I am building code and I don\u2019t know exactly how stuff works (new features or even a new database environment) I put a lot of DBMS_OUTPUT.PUT_LINE statements in my code, so I can see what it is doing. That is, only during development and when I don\u2019t have access to a nice debugging IDE.<\/p>\n\n\n\n
But there is no such thing a DBMS_OUTPUT.PUT_LINE in MySQL. For as far as I know, there is no way to output messages to the console. If there is, please let me know in the comments ;-).<\/p>\n\n\n\n
So, I create a procedure PL myself (When you have a choice, you choose a shorter program name than the 20 character DBMS_OUTPUT.PUT_LINE ;-)). If you just select something, without an into, the results gets echoed to the console.<\/p>\n\n\n
\nDROP PROCEDURE if exists PL;\nDELIMITER \/\/\nCREATE PROCEDURE PL (output VARCHAR(255))\nBEGIN\n SELECT output;\nEND;\n\/\/\nDELIMITER ;\n<\/pre><\/div>\n\n\nMissing SQLERRM<\/h2>\n\n\n\n
There is no function to return the text for an error code. At least, I couldn\u2019t find it. But I needed a function to return the text associated with the error code my code could return. What better name for such a function than SQLERRM ;-). It doesn\u2019t return the text for the MySQL error codes, but at least I can work a bit like I do in my Oracle databases.<\/p>\n\n\n\n
No Sequences<\/h2>\n\n\n\n
There are no sequences available in MySQL, at least not the way we know them in Oracle. I needed a cross session counter to return me the numbers from 1 to 99 in order and at the end loop back to 1.<\/p>\n\n\n\n
In Oracle I would create a sequence like this:<\/p>\n\n\n
\ncreate sequence s \n minvalue 1 maxvalue 99\n start with 1 increment by 1\n cycle nocache\n\/\n<\/pre><\/div>\n\n\nBut, like I said, in MySQL there is nothing like that available. So I came up with my own version. It consists of a table and some code:<\/p>\n\n\n
\ndrop table if exists sequences;\n\ncreate table sequences\n( name varchar(32)\n, min integer\n, max integer\n, increment tinyint\n, last integer\n, primary key (name)\n)ENGINE = InnoDB;\n\ninsert into sequences(name, min, max, increment, last) values ('my_seq', 1, 99, 1, 0);\n\ndrop procedure if exists my_seq_val;\ndrop function if exists my_seq_currval;\ndrop function if exists my_seq_nextval;\ndelimiter $$\ncreate procedure my_seq_val(\n in increment boolean\n, out result integer)\n reads sql data\n modifies sql data\n comment 'gets (and increments) the last value'\n contains sql \n sql security invoker\nbegin\n if increment then\n update sequences set last=case\n when last+increment > max then min\n else last+increment\n end\n where 1=1\n and name = 'my_seq';\n-- Even though this is a procedure, it gets called from a function\n-- Error code 1422: Explicit or implicit commit is not allowed in a stored function or trigger\n-- commit;\n end if; \n select last into result from sequences where name = 'my_seq';\nend$$\n\ncreate function my_seq_currval() \n returns integer\n deterministic\n reads sql data\n comment 'Gets the current value from the sequence'\n contains sql \n sql security invoker\nbegin\n declare result integer;\n call my_seq_val(false, result);\n return result;\nend$$\n\ncreate function my_seq_nextval() \n returns integer\n deterministic\n modifies sql data\n comment 'Gets the next value from the sequence'\n contains sql \n sql security invoker\nbegin\n declare result integer;\n call my_seq_val(true, result);\n return result;\nend$$\n\ndelimiter ;\n<\/pre><\/div>\n\n\nProbably not as fast as the Oracle version and I don\u2019t know what will happen if two sessions increment the sequence at exactly the same moment, but the chance this will happen is low enough that I\u2019ll take my chances \ud83d\ude09<\/p>\n\n\n\n
Writing files<\/h2>\n\n\n\n
One of the requirements of the application was to write certain files. Files with status changes that can be transported to different systems. When it comes to filehandling, MySQL is rather limited compared to Oracle (which is limited itself). It is not possible to overwrite or to delete a file. When you try to write a file that already exists you\u2019ll run into an error. This is one of the reasons I needed the sequences. The good news, in this case, is that we have a process in place that move the file from the location where it is written to a different location. That way we can write a file with the same number at a later stage.<\/p>\n\n\n\n
No dynamic SQL in Function\/Trigger<\/h2>\n\n\n\n
To create these files, with their different sequence numbers in the name, I wanted to use a bit of dynamic SQL. This worked really well until I wanted to call my procedure from a function or a trigger. You are not allowed to run dynamic SQL in a function or a trigger. Since it was a rather simple statement and there were \u2018only\u2019 100 options I ended up with a big case statement where I had to specify a different filename for every value of the sequence.<\/p>\n\n\n\n
All in all it was a fun project to work on. It made me realize (again) what a great environment the Oracle database is to work in. How easy things can be done, how many possibilities you have. On the other hand, if you can come up with some solutions, that take a bit more thinking and coding, you can also create a nice system in MySQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
Over the last couple of months I have been working on a MySQL based system for my current customer. This made me realize, once again, how easy we can do stuff in an Oracle based system.On one hand this is kind of awkward, not being able to do what you are used to, on the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,2,5],"tags":[27,26],"class_list":["post-950","post","type-post","status-publish","format-standard","hentry","category-mysql","category-oracle","category-plsql","tag-mysql","tag-oracle"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/950","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=950"}],"version-history":[{"count":5,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/950\/revisions"}],"predecessor-version":[{"id":969,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/950\/revisions\/969"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=950"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=950"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=950"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}