A clean-up for MDEV-10654 add support IN, OUT, INOUT parameter qualifiers for stored functionspreview-10.8-MDEV-10654-inout-params

Changes:

1. Enabling IN/OUT/INOUT mode for sql_mode=DEFAULT,
   adding tests for sql_mode=DEFAULT based by mostly
   translating compat/oracle.sp-inout.test to SQL/PSM
   with minor changes (e.g. testing trigger OLD.column and
   NEW.column as IN/OUT parameters).

2. Removing duplicate grammar:
   sp_pdparam and sp_fdparam implemented exactly the same syntax after
   - the first patch for MDEV-10654 (for sql_mode=ORACLE)
   - the change #1 from this patch (for sql_mode=DEFAULT)
   Removing separate rules and adding a single "sp_param" rule instead,
   which now covers both PRDEDURE and FUNCTION parameters
   (and CURSOR parameters as well!).

3. Adding a helper rule sp_param_name_and_mode, which is a combination
   of the parameter name and the IN/OUT/INOUT mode. It allows to simplify
   the grammer a bit.

4. The first patch unintentionally allowed IN/OUT/INOUT mode
  to be specified in CURSOR parameters.
  This is good for the IN keyword - it is allowed in PL/SQL CURSORs.
  This is not good the the OUT/INOUT keywords - they should not be allowed.
  Adding a additional symantic post-check.

1 files changed, 21 insertions, 0 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b5f8cf4a886..2ca44721df7 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7130,6 +7130,27 @@ bool LEX::sp_declare_cursor(THD *thd, const LEX_CSTRING *name,
   uint offp;
   sp_instr_cpush *i;
 
+  /* In some cases param_ctx can be NULL. e.g.: FOR rec IN (SELECT...) */
+  if (param_ctx)
+  {
+    for (uint prm= 0; prm < param_ctx->context_var_count(); prm++)
+    {
+      const sp_variable *param= param_ctx->get_context_variable(prm);
+      if (param->mode != sp_variable::MODE_IN)
+      {
+        /*
+          PL/SQL supports the IN keyword in cursor parameters.
+          We also support this for compatibility. Note, OUT/INOUT parameters
+          will unlikely be ever supported. So "YET" may sound confusing here.
+          But it should be better than using a generic error. Adding a dedicated
+          error message for this small issue is not desirable.
+        */
+        my_error(ER_NOT_SUPPORTED_YET, MYF(0), "OUT/INOUT cursor parameter");
+        return true;
+      }
+    }
+  }
+
   if (spcont->find_cursor(name, &offp, true))
   {
     my_error(ER_SP_DUP_CURS, MYF(0), name->str);