Details
-
New Feature
-
Status: In Progress (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
To continue with better Oracle compatibility, let's implement CHARACTER SET ANY and CHARACTER SET param_or_var%CHARSET in SP parameters and variables.
CHARACTER SET param%CHARSET should also be supported in stored functions return type.
Example:
SET sql_mode=ORACLE; |
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1( |
v_in VARCHAR2 CHARACTER SET ANY_CS, |
v_out VARCHAR2 CHARACTER SET v_input%CHARSET) AS |
BEGIN
|
NULL; |
END; |
$$
|
SET sql_mode=ORACLE; |
DELIMITER $$
|
CREATE OR REPLACE FUNCTION f1(v_in CLOB CHARACTER SET ANY_CS) |
RETURN CLOB CHARACTER SET v_in%CHARSET AS |
BEGIN
|
RETURN v_in; |
END; |
$$
|
CHARACTER SET ANY_CS
CHARACTER SET ANY_CS is a special attribute used in stored routine parameters to accept character data in any character set without implicit conversion.
It prevents data loss or performance overhead from unwanted character set conversions when handling values in different character sets.
It is only allowed on routine parameters, not on local variable declarations.
DELIMITER $$
|
DECLARE
|
v_input VARCHAR2(100) CHARACTER SET ANY_CS; -- This fails |
BEGIN
|
NULL; |
END; |
$$
|
CHARACTER SET src%CHARSET
%CHARSET is a special parameter or variable attribute used to anchor the character set property from a previously defined parameter or variable.
Unlike %TYPE (which copies an entire data type), %CHARSET explicitly forces a variable or parameter to inherit only the character set from a preceding variable or parameter.
Example:
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1( |
p_in1 IN VARCHAR2 CHARACTER SET ANY_CS, |
-- Parameters inherit the character set from other parameters |
p_in2 IN VARCHAR2 CHARACTER SET p_in1%CHARSET, -- from a ANY_CS parameter |
p_out1 OUT VARCHAR2 CHARACTER SET p_in2%CHARSET) -- from a %CHARSET parameter |
AS
|
-- Variables inherit the character set from parameters |
v_in1 VARCHAR2(100) CHARACTER SET p_in1%CHARSET; -- from an IN ANY_CS parameter |
v_in2 VARCHAR2(100) CHARACTER SET p_in2%CHARSET; -- from an IN %CHARSET parameter |
v_out1 VARCHAR2(100) CHARACTER SET p_out1%CHARSET; -- from an OUT %CHARSET parameter |
-- Variables inherit the character set from other anchored variables |
v_v_in1 VARCHAR2(100) CHARACTER SET v_in1%CHARSET; -- from a variable |
v_v_in2 VARCHAR2(100) CHARACTER SET v_in2%CHARSET; -- from a variable |
v_v_out2 VARCHAR2(100) CHARACTER SET v_out1%CHARSET; -- from a variable |
BEGIN
|
NULL; |
END; |
$$
|
Syntax
Let's use CHARACTER SET ANY_CS in all sql_modes.
Let's support this syntax for "get character set from a variable or a parameter":
- param_or_var%CHARSET - for sql_mode=ORACLE
- CHARACTER SET OF param_or_var - for sql_mode=DEFAULT, to be inline with TYPE OF param_or_var
ANY_CS and %CHARSET will be possible only in combination with an explicit data type. Thus these combinations won't be allowed:
var3 %var1%TYPE CHARACTER SET ANY_CS; |
var3 var1%TYPE var2%CHARSET;
|
 |
var3 TYPE OF var CHARACTER SET ANY_CS; |
var3 TYPE OF var1%TYPE CHARACTER SET OF var2%CHARSET; |
Misc
The two new attributes allow to write stored routines handling data in different character sets, like built-in MariaDB function can do.
Attachments
Issue Links
- is blocked by
-
MDEV-41151 SF erroneously caches an ancored return data type
-
- Open
-
