1SET sql_mode=ORACLE; 2# Testing routines with no parameters 3CREATE FUNCTION f1 RETURN INT 4AS 5BEGIN 6RETURN 10; 7END; 8/ 9SHOW CREATE FUNCTION f1; 10Function f1 11sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT 12Create Function CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN int(11) 13AS 14BEGIN 15RETURN 10; 16END 17character_set_client latin1 18collation_connection latin1_swedish_ci 19Database Collation latin1_swedish_ci 20SELECT f1(); 21f1() 2210 23DROP FUNCTION f1; 24CREATE PROCEDURE p1 25AS 26BEGIN 27SET @a=10; 28END; 29/ 30SHOW CREATE PROCEDURE p1; 31Procedure p1 32sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT 33Create Procedure CREATE DEFINER="root"@"localhost" PROCEDURE "p1"() 34AS 35BEGIN 36SET @a=10; 37END 38character_set_client latin1 39collation_connection latin1_swedish_ci 40Database Collation latin1_swedish_ci 41SET @a=0; 42CALL p1(); 43SELECT @a; 44@a 4510 46DROP PROCEDURE p1; 47# Testing ":=" to set the default value of a variable 48CREATE FUNCTION f1 () RETURN NUMBER(10) AS 49a NUMBER(10) := 10; 50BEGIN 51DECLARE 52b NUMBER(10) DEFAULT 3; 53BEGIN 54RETURN a+b; 55END; 56END; 57/ 58SELECT f1(); 59f1() 6013 61DROP FUNCTION f1; 62# Testing labels 63CREATE FUNCTION f1 (a INT) RETURN CLOB AS 64BEGIN 65<<label1>> 66BEGIN 67IF a = 1 THEN 68LEAVE label1; 69END IF; 70RETURN 'IS NOT 1'; 71END label1; 72RETURN 'IS 1'; 73END; 74/ 75SELECT f1(1); 76f1(1) 77IS 1 78SELECT f1(2); 79f1(2) 80IS NOT 1 81DROP FUNCTION f1; 82CREATE FUNCTION f1 (a INT) RETURN INT IS 83BEGIN 84<<label1>> 85LOOP 86IF a = 2 THEN 87LEAVE label1; 88END IF; 89SET a= a-1; 90END LOOP; 91RETURN a; 92END; 93/ 94SELECT f1(4); 95f1(4) 962 97DROP FUNCTION f1; 98CREATE FUNCTION f1 (a INT) RETURN INT AS 99BEGIN 100<<label1>> 101WHILE a>0 LOOP 102IF a = 2 THEN 103LEAVE label1; 104END IF; 105SET a= a-1; 106END LOOP label1; 107RETURN a; 108END; 109/ 110SELECT f1(4); 111f1(4) 1122 113DROP FUNCTION f1; 114CREATE FUNCTION f1 (a INT) RETURN INT AS 115BEGIN 116<<label1>> 117REPEAT 118IF a = 2 THEN 119LEAVE label1; 120END IF; 121SET a= a-1; 122UNTIL a=0 END REPEAT; 123RETURN a; 124END; 125/ 126SELECT f1(4); 127f1(4) 1282 129DROP FUNCTION f1; 130# Testing IN/OUT/INOUT 131CREATE PROCEDURE p1 (p1 IN VARCHAR2(10), p2 OUT VARCHAR2(10)) AS 132BEGIN 133SET p1='p1new'; 134SET p2='p2new'; 135END; 136/ 137SET @p1='p1', @p2='p2'; 138CALL p1(@p1, @p2); 139SELECT @p1, @p2; 140@p1 @p2 141p1 p2new 142DROP PROCEDURE p1; 143# Testing Oracle-style assigment 144CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) AS 145BEGIN 146p1:= 'p1new'; 147END; 148/ 149SET @p1='p1'; 150CALL p1(@p1); 151SELECT @p1; 152@p1 153p1new 154DROP PROCEDURE p1; 155# Testing that NULL is a valid statement 156CREATE PROCEDURE p1(a INT) AS 157BEGIN 158NULL; 159END; 160/ 161DROP PROCEDURE p1; 162CREATE PROCEDURE p1(a INT) AS 163a INT:=10; 164BEGIN 165IF a=10 THEN NULL; ELSE NULL; END IF; 166END; 167/ 168DROP PROCEDURE p1; 169# Keywords that are OK for table names, but not for SP variables 170CREATE TABLE function (function int); 171INSERT INTO function SET function=10; 172SELECT function.function FROM function; 173function 17410 175DROP TABLE function; 176# Testing that (some) keyword_sp are allowed in Oracle-style assignments 177CREATE PROCEDURE p1 (action OUT INT) AS BEGIN action:=10; END;/ 178DROP PROCEDURE p1/ 179CREATE PROCEDURE p1 (clob OUT INT) AS BEGIN clob:=10; END;/ 180DROP PROCEDURE p1/ 181CREATE PROCEDURE p1 (enum OUT INT) AS BEGIN enum:=10; END;/ 182DROP PROCEDURE p1/ 183CREATE PROCEDURE p1 (via OUT INT) AS BEGIN via:=10; END;/ 184DROP PROCEDURE p1/ 185# Testing keyword_directly_assignable 186CREATE PROCEDURE p1 (ascii OUT INT) AS BEGIN ascii:=10; END;/ 187DROP PROCEDURE p1/ 188CREATE PROCEDURE p1 (backup OUT INT) AS BEGIN backup:=10; END;/ 189DROP PROCEDURE p1/ 190CREATE PROCEDURE p1 (binlog OUT INT) AS BEGIN binlog:=10; END;/ 191DROP PROCEDURE p1/ 192CREATE PROCEDURE p1 (byte OUT INT) AS BEGIN byte:=10; END;/ 193DROP PROCEDURE p1/ 194CREATE PROCEDURE p1 (cache OUT INT) AS BEGIN cache:=10; END;/ 195DROP PROCEDURE p1/ 196CREATE PROCEDURE p1 (checksum OUT INT) AS BEGIN checksum:=10; END;/ 197DROP PROCEDURE p1/ 198CREATE PROCEDURE p1 (checkpoint OUT INT) AS BEGIN checkpoint:=10; END;/ 199DROP PROCEDURE p1/ 200CREATE PROCEDURE p1 (column_add OUT INT) AS BEGIN column_add:=10; END;/ 201DROP PROCEDURE p1/ 202CREATE PROCEDURE p1 (column_check OUT INT) AS BEGIN column_check:=10; END;/ 203DROP PROCEDURE p1/ 204CREATE PROCEDURE p1 (column_create OUT INT) AS BEGIN column_create:=10; END;/ 205DROP PROCEDURE p1/ 206CREATE PROCEDURE p1 (column_delete OUT INT) AS BEGIN column_delete:=10; END;/ 207DROP PROCEDURE p1/ 208CREATE PROCEDURE p1 (column_get OUT INT) AS BEGIN column_get:=10; END;/ 209DROP PROCEDURE p1/ 210CREATE PROCEDURE p1 (deallocate OUT INT) AS BEGIN deallocate:=10; END;/ 211DROP PROCEDURE p1/ 212CREATE PROCEDURE p1 (examined OUT INT) AS BEGIN examined:=10; END;/ 213DROP PROCEDURE p1/ 214CREATE PROCEDURE p1 (execute OUT INT) AS BEGIN execute:=10; END;/ 215DROP PROCEDURE p1/ 216CREATE PROCEDURE p1 (flush OUT INT) AS BEGIN flush:=10; END;/ 217DROP PROCEDURE p1/ 218CREATE PROCEDURE p1 (format OUT INT) AS BEGIN format:=10; END;/ 219DROP PROCEDURE p1/ 220CREATE PROCEDURE p1 (get OUT INT) AS BEGIN get:=10; END;/ 221DROP PROCEDURE p1/ 222CREATE PROCEDURE p1 (help OUT INT) AS BEGIN help:=10; END;/ 223DROP PROCEDURE p1/ 224CREATE PROCEDURE p1 (host OUT INT) AS BEGIN host:=10; END;/ 225DROP PROCEDURE p1/ 226CREATE PROCEDURE p1 (install OUT INT) AS BEGIN install:=10; END;/ 227DROP PROCEDURE p1/ 228CREATE PROCEDURE p1 (option OUT INT) AS BEGIN option:=10; END;/ 229DROP PROCEDURE p1/ 230CREATE PROCEDURE p1 (options OUT INT) AS BEGIN options:=10; END;/ 231DROP PROCEDURE p1/ 232CREATE PROCEDURE p1 (owner OUT INT) AS BEGIN owner:=10; END;/ 233DROP PROCEDURE p1/ 234CREATE PROCEDURE p1 (parser OUT INT) AS BEGIN parser:=10; END;/ 235DROP PROCEDURE p1/ 236CREATE PROCEDURE p1 (port OUT INT) AS BEGIN port:=10; END;/ 237DROP PROCEDURE p1/ 238CREATE PROCEDURE p1 (prepare OUT INT) AS BEGIN prepare:=10; END;/ 239DROP PROCEDURE p1/ 240CREATE PROCEDURE p1 (remove OUT INT) AS BEGIN remove:=10; END;/ 241DROP PROCEDURE p1/ 242CREATE PROCEDURE p1 (reset OUT INT) AS BEGIN reset:=10; END;/ 243DROP PROCEDURE p1/ 244CREATE PROCEDURE p1 (restore OUT INT) AS BEGIN restore:=10; END;/ 245DROP PROCEDURE p1/ 246CREATE PROCEDURE p1 (security OUT INT) AS BEGIN security:=10; END;/ 247DROP PROCEDURE p1/ 248CREATE PROCEDURE p1 (server OUT INT) AS BEGIN server:=10; END;/ 249DROP PROCEDURE p1/ 250CREATE PROCEDURE p1 (signed OUT INT) AS BEGIN signed:=10; END;/ 251DROP PROCEDURE p1/ 252CREATE PROCEDURE p1 (socket OUT INT) AS BEGIN socket:=10; END;/ 253DROP PROCEDURE p1/ 254CREATE PROCEDURE p1 (slave OUT INT) AS BEGIN slave:=10; END;/ 255DROP PROCEDURE p1/ 256CREATE PROCEDURE p1 (slaves OUT INT) AS BEGIN slaves:=10; END;/ 257DROP PROCEDURE p1/ 258CREATE PROCEDURE p1 (soname OUT INT) AS BEGIN soname:=10; END;/ 259DROP PROCEDURE p1/ 260CREATE PROCEDURE p1 (start OUT INT) AS BEGIN start:=10; END;/ 261DROP PROCEDURE p1/ 262CREATE PROCEDURE p1 (stop OUT INT) AS BEGIN stop:=10; END;/ 263DROP PROCEDURE p1/ 264CREATE PROCEDURE p1 (stored OUT INT) AS BEGIN stored:=10; END;/ 265DROP PROCEDURE p1/ 266CREATE PROCEDURE p1 (unicode OUT INT) AS BEGIN unicode:=10; END;/ 267DROP PROCEDURE p1/ 268CREATE PROCEDURE p1 (uninstall OUT INT) AS BEGIN uninstall:=10; END;/ 269DROP PROCEDURE p1/ 270CREATE PROCEDURE p1 (upgrade OUT INT) AS BEGIN upgrade:=10; END;/ 271DROP PROCEDURE p1/ 272CREATE PROCEDURE p1 (wrapper OUT INT) AS BEGIN wrapper:=10; END;/ 273DROP PROCEDURE p1/ 274CREATE PROCEDURE p1 (xa OUT INT) AS BEGIN xa:=10; END;/ 275DROP PROCEDURE p1/ 276# Testing that keyword_directly_not_assignable does not work in := 277CREATE PROCEDURE p1 (commit OUT INT) AS BEGIN commit:=10; END;/ 278ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':=10; END' at line 1 279CREATE PROCEDURE p1 (rollback OUT INT) AS BEGIN rollback:=10; END;/ 280ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':=10; END' at line 1 281CREATE PROCEDURE p1 (shutdown OUT INT) AS BEGIN shutdown:=10; END;/ 282ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':=10; END' at line 1 283CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN exception:=10; END;/ 284ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':=10; END' at line 1 285# Testing that keyword_directly_not_assignable works in SET statements. 286CREATE PROCEDURE p1 (contains OUT INT) AS BEGIN SET contains=10; END;/ 287DROP PROCEDURE p1/ 288CREATE PROCEDURE p1 (language OUT INT) AS BEGIN SET language=10; END;/ 289DROP PROCEDURE p1/ 290CREATE PROCEDURE p1 (no OUT INT) AS BEGIN SET no=10; END;/ 291DROP PROCEDURE p1/ 292CREATE PROCEDURE p1 (charset OUT INT) AS BEGIN SET charset=10; END;/ 293DROP PROCEDURE p1/ 294CREATE PROCEDURE p1 (do OUT INT) AS BEGIN SET do=10; END;/ 295DROP PROCEDURE p1/ 296CREATE PROCEDURE p1 (repair OUT INT) AS BEGIN SET repair=10; END;/ 297DROP PROCEDURE p1/ 298CREATE PROCEDURE p1 (handler OUT INT) AS BEGIN SET handler=10; END;/ 299DROP PROCEDURE p1/ 300CREATE PROCEDURE p1 (open OUT INT) AS BEGIN SET open=10; END;/ 301DROP PROCEDURE p1/ 302CREATE PROCEDURE p1 (close OUT INT) AS BEGIN SET close=10; END;/ 303DROP PROCEDURE p1/ 304CREATE PROCEDURE p1 (savepoint OUT INT) AS BEGIN SET savepoint=10; END;/ 305DROP PROCEDURE p1/ 306CREATE PROCEDURE p1 (truncate OUT INT) AS BEGIN SET truncate=10; END;/ 307DROP PROCEDURE p1/ 308CREATE PROCEDURE p1 (begin OUT INT) AS BEGIN SET begin=10; END;/ 309DROP PROCEDURE p1/ 310CREATE PROCEDURE p1 (end OUT INT) AS BEGIN SET end=10; END;/ 311DROP PROCEDURE p1/ 312CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN SET exception=10; END;/ 313DROP PROCEDURE p1/ 314# Testing that keyword_directly_not_assignable works in table/column names 315CREATE TABLE contains (contains INT); 316DROP TABLE contains; 317CREATE TABLE language (language INT); 318DROP TABLE language; 319CREATE TABLE no (no INT); 320DROP TABLE no; 321CREATE TABLE charset (charset INT); 322DROP TABLE charset; 323CREATE TABLE do (do INT); 324DROP TABLE do; 325CREATE TABLE repair (repair INT); 326DROP TABLE repair; 327CREATE TABLE handler (handler INT); 328DROP TABLE handler; 329CREATE TABLE open (open INT); 330DROP TABLE open; 331CREATE TABLE close (close INT); 332DROP TABLE close; 333CREATE TABLE savepoint (savepoint INT); 334DROP TABLE savepoint; 335CREATE TABLE truncate (truncate INT); 336DROP TABLE truncate; 337CREATE TABLE begin (begin INT); 338DROP TABLE begin; 339CREATE TABLE end (end INT); 340DROP TABLE end; 341CREATE TABLE exception (exception INT); 342DROP TABLE exception; 343# Testing ELSIF 344CREATE FUNCTION f1(a INT) RETURN CLOB 345AS 346BEGIN 347IF a=1 THEN RETURN 'a is 1'; 348ELSIF a=2 THEN RETURN 'a is 2'; 349ELSE RETURN 'a is unknown'; 350END IF; 351END; 352/ 353SELECT f1(2) FROM DUAL; 354f1(2) 355a is 2 356DROP FUNCTION f1; 357# Testing top-level declarations 358CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) 359AS 360p2 VARCHAR(10); 361BEGIN 362p2:='p1new'; 363p1:=p2; 364END; 365/ 366SET @p1='p1'; 367CALL p1(@p1); 368SELECT @p1; 369@p1 370p1new 371DROP PROCEDURE p1; 372CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20) 373AS 374p2 VARCHAR(10); 375BEGIN 376p2:='new'; 377RETURN CONCAT(p1, p2); 378END; 379/ 380SET @p1='p1'; 381SELECT f1(@p1); 382f1(@p1) 383p1new 384DROP FUNCTION f1; 385# Testing non-top declarations 386CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) 387AS 388BEGIN 389DECLARE 390p2 VARCHAR(10); 391BEGIN 392p2:='p1new'; 393p1:=p2; 394END; 395DECLARE 396t1 VARCHAR(10); 397t2 VARCHAR(10); 398BEGIN 399END; 400END; 401/ 402SET @p1='p1'; 403CALL p1(@p1); 404SELECT @p1; 405@p1 406p1new 407DROP PROCEDURE p1; 408CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20) 409AS 410BEGIN 411DECLARE 412p2 VARCHAR(10); 413BEGIN 414p2:='new'; 415RETURN CONCAT(p1, p2); 416END; 417DECLARE 418t1 VARCHAR(10); 419t2 VARCHAR(10); 420BEGIN 421END; 422END; 423/ 424SET @p1='p1'; 425SELECT f1(@p1); 426f1(@p1) 427p1new 428DROP FUNCTION f1; 429# Testing exceptions 430CREATE TABLE t1 (c1 INT); 431CREATE PROCEDURE sp1 (p1 IN VARCHAR2(20), p2 OUT VARCHAR2(30)) 432IS 433v1 INT; 434BEGIN 435SELECT c1 INTO v1 FROM t1; 436p2 := p1; 437EXCEPTION 438WHEN NOT FOUND THEN 439BEGIN 440p2 := 'def'; 441END; 442END; 443/ 444CALL sp1('abc', @a); 445SELECT @a; 446@a 447def 448DROP PROCEDURE sp1; 449DROP TABLE t1; 450CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT) 451IS 452BEGIN 453SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!'; 454v:= 223; 455EXCEPTION 456WHEN 30001 THEN 457BEGIN 458v:= 113; 459END; 460END; 461/ 462SET @v=10; 463CALL sp1(@v, 30001); 464CALL sp1(@v, 30002); 465ERROR 45000: User defined error! 466SELECT @v; 467@v 468113 469DROP PROCEDURE sp1; 470CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT) 471IS 472BEGIN 473BEGIN 474BEGIN 475SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!'; 476v:= 223; 477EXCEPTION 478WHEN 30001 THEN 479BEGIN 480v:= 113; 481END; 482END; 483END; 484END; 485/ 486SET @v=10; 487CALL sp1(@v, 30001); 488SELECT @v; 489@v 490113 491SET @v=10; 492CALL sp1(@v, 30002); 493ERROR 45000: User defined error! 494SELECT @v; 495@v 49610 497DROP PROCEDURE sp1; 498# 499# Testing EXIT statement 500# 501CREATE FUNCTION f1 RETURN INT 502IS 503i INT := 0; 504BEGIN 505EXIT; 506END; 507/ 508ERROR 42000: EXIT with no matching label: 509CREATE FUNCTION f1 RETURN INT 510IS 511i INT := 0; 512BEGIN 513<<lable1>> 514BEGIN 515<<label2>> 516LOOP 517EXIT label1; 518END LOOP; 519END; 520END; 521/ 522ERROR 42000: EXIT with no matching label: label1 523CREATE FUNCTION f1 RETURN INT 524IS 525i INT := 0; 526BEGIN 527LOOP 528LOOP 529i:= i + 1; 530IF i >= 5 THEN 531EXIT; 532END IF; 533END LOOP; 534i:= i + 100; 535EXIT; 536END LOOP; 537RETURN i; 538END; 539/ 540SELECT f1() FROM DUAL; 541f1() 542105 543DROP FUNCTION f1; 544CREATE FUNCTION f1 RETURN INT 545IS 546i INT := 0; 547BEGIN 548<<label1>> 549LOOP 550<<label2>> 551LOOP 552i:= i + 1; 553IF i >= 5 THEN 554EXIT label2; 555END IF; 556END LOOP; 557i:= i + 100; 558EXIT; 559END LOOP; 560RETURN i; 561END; 562/ 563SELECT f1() FROM DUAL; 564f1() 565105 566DROP FUNCTION f1; 567CREATE FUNCTION f1 RETURN INT 568IS 569i INT := 0; 570BEGIN 571<<label1>> 572LOOP 573<<label2>> 574LOOP 575i:= i + 1; 576IF i >= 5 THEN 577EXIT label1; 578END IF; 579END LOOP; 580i:= i + 100; 581EXIT; 582END LOOP; 583RETURN i; 584END; 585/ 586SELECT f1() FROM DUAL; 587f1() 5885 589DROP FUNCTION f1; 590CREATE FUNCTION f1 RETURN INT 591IS 592i INT := 0; 593BEGIN 594LOOP 595i:= i + 1; 596EXIT WHEN i >=5; 597END LOOP; 598RETURN i; 599END; 600/ 601SELECT f1() FROM DUAL; 602f1() 6035 604DROP FUNCTION f1; 605CREATE FUNCTION f1 RETURN INT 606IS 607i INT := 0; 608BEGIN 609<<label1>> 610LOOP 611<<label2>> 612LOOP 613i:= i + 1; 614EXIT label2 WHEN i >= 5; 615END LOOP; 616i:= i + 100; 617EXIT; 618END LOOP; 619RETURN i; 620END; 621/ 622SELECT f1() FROM DUAL; 623f1() 624105 625DROP FUNCTION f1; 626CREATE FUNCTION f1 RETURN INT 627IS 628i INT := 0; 629BEGIN 630<<label1>> 631LOOP 632<<label2>> 633LOOP 634i:= i + 1; 635EXIT label1 WHEN i >= 5; 636END LOOP; 637i:= i + 100; 638EXIT; 639END LOOP; 640RETURN i; 641END; 642/ 643SELECT f1() FROM DUAL; 644f1() 6455 646DROP FUNCTION f1; 647# Testing CURSOR declaration 648CREATE TABLE t1 (a INT); 649INSERT INTO t1 VALUES (1); 650CREATE FUNCTION f1 RETURN INT 651AS 652v_a INT:=10; 653CURSOR c IS SELECT a FROM t1; 654BEGIN 655OPEN c; 656FETCH c INTO v_a; 657CLOSE c; 658RETURN v_a; 659EXCEPTION 660WHEN OTHERS THEN RETURN -1; 661END; 662/ 663SELECT f1() FROM DUAL; 664f1() 6651 666DROP FUNCTION f1; 667DROP TABLE t1; 668# Testing RETURN in procedures 669CREATE PROCEDURE p1 (a IN OUT INT) 670AS 671BEGIN 672RETURN 10; 673END; 674/ 675ERROR 42000: RETURN is only allowed in a FUNCTION 676CREATE FUNCTION f1 (a INT) RETURN INT 677AS 678BEGIN 679RETURN; 680END; 681/ 682ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '; 683END' at line 4 684CREATE PROCEDURE p1 (a IN OUT INT) 685AS 686BEGIN 687IF a < 10 THEN 688BEGIN 689a:= a - 1; 690RETURN; 691END; 692END IF; 693a:= a + 1; 694EXCEPTION 695WHEN OTHERS THEN RETURN; 696END; 697/ 698SET @v=10; 699CALL p1(@v); 700SELECT @v; 701@v 70211 703SET @v=9; 704CALL p1(@v); 705SELECT @v; 706@v 7078 708DROP PROCEDURE p1; 709CREATE PROCEDURE p1 (a IN OUT INT) 710AS 711BEGIN 712DROP TABLE t1_non_existent; 713EXCEPTION 714WHEN OTHERS THEN 715BEGIN 716a:= 100; 717RETURN; 718END; 719END; 720/ 721SET @v=10; 722CALL p1(@v); 723SELECT @v; 724@v 725100 726DROP PROCEDURE p1; 727# Testing WHILE loop 728CREATE PROCEDURE p1 (a IN OUT INT) 729AS 730i INT:= 1; 731j INT:= 3; 732BEGIN 733WHILE i<=j 734LOOP 735a:= a + i; 736i:= i + 1; 737END LOOP; 738END; 739/ 740SET @v=0; 741CALL p1(@v); 742SELECT @v; 743@v 7446 745DROP PROCEDURE p1; 746CREATE PROCEDURE p1 (a IN OUT INT) 747AS 748i INT:= 1; 749j INT:= 3; 750BEGIN 751<<label>> 752WHILE i<=j 753LOOP 754a:= a + i; 755i:= i + 1; 756END LOOP label; 757END; 758/ 759SET @v=0; 760CALL p1(@v); 761SELECT @v; 762@v 7636 764DROP PROCEDURE p1; 765# Testing the FOR loop statement 766CREATE TABLE t1 (a INT); 767FOR i IN 1..3 768LOOP 769INSERT INTO t1 VALUES (i); 770END LOOP; 771/ 772SELECT * FROM t1; 773a 7741 7752 7763 777DROP TABLE t1; 778CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT 779AS 780total INT := 0; 781BEGIN 782FOR i IN lower_bound . . upper_bound 783LOOP 784NULL 785END LOOP; 786RETURN total; 787END; 788/ 789ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '. upper_bound 790LOOP 791NULL 792END LOOP; 793RETURN total; 794END' at line 5 795CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT 796AS 797total INT := 0; 798BEGIN 799FOR i IN lower_bound .. upper_bound 800LOOP 801total:= total + i; 802IF i = lim THEN 803EXIT; 804END IF; 805-- Bounds are calculated only once. 806-- The below assignments have no effect on the loop condition 807lower_bound:= 900; 808upper_bound:= 1000; 809END LOOP; 810RETURN total; 811END; 812/ 813SELECT f1(1, 3, 100) FROM DUAL; 814f1(1, 3, 100) 8156 816SELECT f1(1, 3, 2) FROM DUAL; 817f1(1, 3, 2) 8183 819DROP FUNCTION f1; 820CREATE FUNCTION f1 RETURN INT 821AS 822total INT := 0; 823BEGIN 824FOR i IN 1 .. 5 825LOOP 826total:= total + 1000; 827FOR j IN 1 .. 5 828LOOP 829total:= total + 1; 830IF j = 3 THEN 831EXIT; -- End the internal loop 832END IF; 833END LOOP; 834END LOOP; 835RETURN total; 836END; 837/ 838SELECT f1() FROM DUAL; 839f1() 8405015 841DROP FUNCTION f1; 842CREATE FUNCTION f1 (a INT, b INT) RETURN INT 843AS 844total INT := 0; 845BEGIN 846FOR i IN REVERSE 1..a 847LOOP 848total:= total + i; 849IF i = b THEN 850EXIT; 851END IF; 852END LOOP; 853RETURN total; 854END 855/ 856SELECT f1(3, 100) FROM DUAL; 857f1(3, 100) 8586 859SELECT f1(3, 2) FROM DUAL; 860f1(3, 2) 8615 862DROP FUNCTION f1; 863# Testing labeled FOR LOOP statement 864CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT 865AS 866total INT := 0; 867BEGIN 868<<la>> 869FOR ia IN 1 .. a 870LOOP 871total:= total + 1000; 872<<lb>> 873FOR ib IN 1 .. b 874LOOP 875total:= total + 1; 876EXIT lb WHEN ib = limitb; 877EXIT la WHEN ia = limita; 878END LOOP lb; 879END LOOP la; 880RETURN total; 881END; 882/ 883SELECT f1(1, 1, 1, 1) FROM DUAL; 884f1(1, 1, 1, 1) 8851001 886SELECT f1(1, 2, 1, 2) FROM DUAL; 887f1(1, 2, 1, 2) 8881001 889SELECT f1(2, 1, 2, 1) FROM DUAL; 890f1(2, 1, 2, 1) 8912002 892SELECT f1(2, 1, 2, 2) FROM DUAL; 893f1(2, 1, 2, 2) 8941001 895SELECT f1(2, 2, 2, 2) FROM DUAL; 896f1(2, 2, 2, 2) 8972003 898SELECT f1(2, 3, 2, 3) FROM DUAL; 899f1(2, 3, 2, 3) 9002004 901DROP FUNCTION f1; 902# Testing labeled ITERATE in a labeled FOR LOOP statement 903CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURN INT 904AS 905total INT := 0; 906BEGIN 907<<la>> 908FOR ia IN 1 .. a 909LOOP 910total:= total + 1000; 911DECLARE 912ib INT:= 1; 913BEGIN 914WHILE ib <= b 915LOOP 916IF ib > blim THEN 917ITERATE la; 918END IF; 919ib:= ib + 1; 920total:= total + 1; 921END LOOP; 922END; 923END LOOP la; 924RETURN total; 925END; 926/ 927SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL; 928f1(3,3,0) f1(3,3,1) f1(3,3,2) f1(3,3,3) f1(3,3,4) 9293000 3003 3006 3009 3009 930DROP FUNCTION f1; 931# Testing CONTINUE statement 932CREATE FUNCTION f1(a INT) RETURN INT 933AS 934total INT:= 0; 935BEGIN 936FOR i IN 1 .. a 937LOOP 938IF i=5 THEN 939CONTINUE; 940END IF; 941total:= total + 1; 942END LOOP; 943RETURN total; 944END; 945/ 946SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; 947f1(3) f1(4) f1(5) f1(6) 9483 4 4 5 949DROP FUNCTION f1; 950CREATE FUNCTION f1(a INT) RETURN INT 951AS 952total INT:= 0; 953BEGIN 954<<lj>> 955FOR j IN 1 .. 2 956LOOP 957FOR i IN 1 .. a 958LOOP 959IF i=5 THEN 960CONTINUE lj; 961END IF; 962total:= total + 1; 963END LOOP; 964END LOOP; 965RETURN total; 966END; 967/ 968SELECT f1(3), f1(4), f1(5) FROM DUAL; 969f1(3) f1(4) f1(5) 9706 8 8 971DROP FUNCTION f1; 972CREATE FUNCTION f1(a INT) RETURN INT 973AS 974total INT:= 0; 975BEGIN 976<<lj>> 977FOR j IN 1 .. 2 978LOOP 979FOR i IN 1 .. a 980LOOP 981CONTINUE lj WHEN i=5; 982total:= total + 1; 983END LOOP; 984END LOOP; 985RETURN total; 986END; 987/ 988SELECT f1(3), f1(4), f1(5) FROM DUAL; 989f1(3) f1(4) f1(5) 9906 8 8 991DROP FUNCTION f1; 992CREATE FUNCTION f1(a INT) RETURN INT 993AS 994total INT:= 0; 995i INT:= 1; 996BEGIN 997WHILE i <= a 998LOOP 999i:= i + 1; 1000IF i=6 THEN 1001CONTINUE; 1002END IF; 1003total:= total + 1; 1004END LOOP; 1005RETURN total; 1006END; 1007/ 1008SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; 1009f1(3) f1(4) f1(5) f1(6) 10103 4 4 5 1011DROP FUNCTION f1; 1012# 1013# Testing behaviour of unknown identifiers in EXIT and CONTINUE statements 1014# 1015CREATE PROCEDURE p1 1016AS 1017BEGIN 1018LOOP 1019EXIT WHEN unknown_ident IS NULL; 1020END LOOP; 1021END$$ 1022ERROR 42000: Undeclared variable: unknown_ident 1023CREATE PROCEDURE p1 1024AS 1025BEGIN 1026<<label>> 1027LOOP 1028EXIT label WHEN unknown_ident IS NULL; 1029END LOOP; 1030END$$ 1031ERROR 42000: Undeclared variable: unknown_ident 1032CREATE PROCEDURE p1 1033AS 1034BEGIN 1035LOOP 1036CONTINUE WHEN unknown_ident IS NULL; 1037END LOOP; 1038END$$ 1039ERROR 42000: Undeclared variable: unknown_ident 1040CREATE PROCEDURE p1 1041AS 1042BEGIN 1043<<label>> 1044LOOP 1045CONTINUE label WHEN unknown_ident IS NULL; 1046END LOOP; 1047END$$ 1048ERROR 42000: Undeclared variable: unknown_ident 1049# 1050# MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT 1051# 1052EXPLAIN EXTENDED SELECT sql%rowcount; 1053id select_type table type possible_keys key key_len ref rows filtered Extra 10541 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1055Warnings: 1056Note 1003 select SQL%ROWCOUNT AS "sql%rowcount" 1057CREATE TABLE t1 AS SELECT SQL%ROWCOUNT; 1058SHOW CREATE TABLE t1; 1059Table Create Table 1060t1 CREATE TABLE "t1" ( 1061 "SQL%ROWCOUNT" bigint(21) NOT NULL 1062) 1063DROP TABLE t1; 1064# 1065# UPDATE 1066# 1067CREATE TABLE t1 (a INT); 1068CREATE PROCEDURE p1 1069AS 1070BEGIN 1071UPDATE t1 SET a=30; 1072SELECT SQL%ROWCOUNT; 1073END; 1074$$ 1075CALL p1(); 1076SQL%ROWCOUNT 10770 1078DROP PROCEDURE p1; 1079DROP TABLE t1; 1080CREATE TABLE t1 (a INT); 1081INSERT INTO t1 VALUES (10); 1082INSERT INTO t1 VALUES (20); 1083CREATE PROCEDURE p1 1084AS 1085BEGIN 1086UPDATE t1 SET a=30; 1087SELECT SQL%ROWCOUNT; 1088END; 1089$$ 1090CALL p1(); 1091SQL%ROWCOUNT 10922 1093DROP PROCEDURE p1; 1094DROP TABLE t1; 1095# 1096# DELETE 1097# 1098CREATE TABLE t1 (a INT); 1099CREATE PROCEDURE p1 1100AS 1101BEGIN 1102DELETE FROM t1; 1103SELECT SQL%ROWCOUNT; 1104END; 1105$$ 1106CALL p1(); 1107SQL%ROWCOUNT 11080 1109DROP PROCEDURE p1; 1110DROP TABLE t1; 1111CREATE TABLE t1 (a INT); 1112INSERT INTO t1 VALUES (10); 1113INSERT INTO t1 VALUES (20); 1114CREATE PROCEDURE p1 1115AS 1116BEGIN 1117DELETE FROM t1; 1118SELECT SQL%ROWCOUNT; 1119END; 1120$$ 1121CALL p1(); 1122SQL%ROWCOUNT 11232 1124DROP PROCEDURE p1; 1125DROP TABLE t1; 1126# 1127# SELECT ... INTO var FROM ... - one row found 1128# 1129CREATE TABLE t1 (a INT); 1130INSERT INTO t1 VALUES (10); 1131INSERT INTO t1 VALUES (20); 1132CREATE PROCEDURE p1 1133AS 1134va INT; 1135BEGIN 1136SELECT a INTO va FROM t1 LIMIT 1; 1137SELECT SQL%ROWCOUNT; 1138END; 1139$$ 1140CALL p1(); 1141SQL%ROWCOUNT 11421 1143DROP PROCEDURE p1; 1144DROP TABLE t1; 1145# 1146# SELECT ... INTO var FROM ... - no rows found 1147# 1148CREATE TABLE t1 (a INT); 1149CREATE PROCEDURE p1 1150AS 1151va INT; 1152BEGIN 1153SELECT a INTO va FROM t1; 1154SELECT SQL%ROWCOUNT; 1155END; 1156$$ 1157CALL p1(); 1158SQL%ROWCOUNT 11590 1160Warnings: 1161Warning 1329 No data - zero rows fetched, selected, or processed 1162DROP PROCEDURE p1; 1163DROP TABLE t1; 1164CREATE TABLE t1 (a INT); 1165CREATE PROCEDURE p1 1166AS 1167va INT; 1168BEGIN 1169SELECT a INTO va FROM t1; 1170SELECT SQL%ROWCOUNT; 1171EXCEPTION 1172WHEN NO_DATA_FOUND THEN SELECT SQL%ROWCOUNT||' (EXCEPTION)'; 1173END; 1174$$ 1175CALL p1(); 1176SQL%ROWCOUNT||' (EXCEPTION)' 11770 (EXCEPTION) 1178DROP PROCEDURE p1; 1179DROP TABLE t1; 1180# 1181# SELECT ... INTO var FROM ... - multiple rows found 1182# 1183CREATE TABLE t1 (a INT); 1184INSERT INTO t1 VALUES (10); 1185INSERT INTO t1 VALUES (20); 1186CREATE PROCEDURE p1 1187AS 1188va INT:=1; 1189BEGIN 1190SELECT a INTO va FROM t1; 1191SELECT SQL%ROWCOUNT; 1192EXCEPTION 1193WHEN TOO_MANY_ROWS THEN SELECT SQL%ROWCOUNT||' (EXCEPTION) va='||va; 1194END; 1195$$ 1196CALL p1(); 1197SQL%ROWCOUNT||' (EXCEPTION) va='||va 11981 (EXCEPTION) va=10 1199DROP PROCEDURE p1; 1200DROP TABLE t1; 1201# 1202# INSERT INTO t2 SELECT ... 1203# 1204CREATE TABLE t1 (a INT); 1205CREATE TABLE t2 (a INT); 1206INSERT INTO t1 VALUES (10); 1207INSERT INTO t1 VALUES (20); 1208CREATE PROCEDURE p1 1209AS 1210BEGIN 1211INSERT INTO t2 SELECT * FROM t1; 1212SELECT SQL%ROWCOUNT; 1213END; 1214$$ 1215CALL p1(); 1216SQL%ROWCOUNT 12172 1218DROP PROCEDURE p1; 1219DROP TABLE t1, t2; 1220# 1221# End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT 1222# 1223# 1224# MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations 1225# 1226# 1227# Missing table 1228# 1229CREATE PROCEDURE p1 1230AS 1231a t1.a%TYPE; 1232BEGIN 1233NULL; 1234END; 1235$$ 1236CALL p1(); 1237ERROR 42S02: Table 'test.t1' doesn't exist 1238DROP PROCEDURE p1; 1239# 1240# Missing column 1241# 1242CREATE TABLE t1 (b INT); 1243CREATE PROCEDURE p1 1244AS 1245a t1.a%TYPE; 1246BEGIN 1247NULL; 1248END; 1249$$ 1250CALL p1(); 1251ERROR 42S22: Unknown column 'a' in 't1' 1252DROP PROCEDURE p1; 1253DROP TABLE t1; 1254# 1255# One %TYPE variable 1256# 1257CREATE TABLE t1 (a INT); 1258CREATE PROCEDURE p1 1259AS 1260a t1.a%TYPE; 1261BEGIN 1262a:= 123; 1263SELECT a; 1264END; 1265$$ 1266CALL p1(); 1267a 1268123 1269DROP PROCEDURE p1; 1270DROP TABLE t1; 1271# 1272# Two %TYPE variables, with a truncation warning on assignment 1273# 1274CREATE TABLE t1 (a TINYINT, b INT); 1275CREATE PROCEDURE p1 1276AS 1277a t1.a%TYPE; 1278b t1.b%TYPE; 1279BEGIN 1280a:= 200; 1281b:= 200; 1282SELECT a, b; 1283END; 1284$$ 1285CALL p1(); 1286a b 1287127 200 1288Warnings: 1289Warning 1264 Out of range value for column 'a' at row 1 1290DROP PROCEDURE p1; 1291DROP TABLE t1; 1292# 1293# %TYPE variables for fields with various attributes 1294# 1295CREATE TABLE t1 ( 1296id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 1297a TINYINT NOT NULL, 1298b INT NOT NULL, 1299ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 1300UNIQUE(a) 1301); 1302CREATE PROCEDURE p1 1303AS 1304id t1.id%TYPE; 1305a t1.a%TYPE; 1306b t1.b%TYPE; 1307ts t1.ts%TYPE; 1308BEGIN 1309SELECT id, a, b, ts; 1310CREATE TABLE t2 AS SELECT id, a, b, ts; 1311SHOW CREATE TABLE t2; 1312DROP TABLE t2; 1313END; 1314$$ 1315CALL p1(); 1316id a b ts 1317NULL NULL NULL NULL 1318Table Create Table 1319t2 CREATE TABLE "t2" ( 1320 "id" int(11) DEFAULT NULL, 1321 "a" tinyint(4) DEFAULT NULL, 1322 "b" int(11) DEFAULT NULL, 1323 "ts" timestamp NULL DEFAULT NULL 1324) 1325DROP PROCEDURE p1; 1326DROP TABLE t1; 1327# 1328# %TYPE + virtual columns 1329# 1330CREATE TABLE t1 ( 1331a INT NOT NULL, 1332b VARCHAR(32), 1333c INT AS (a + 10) VIRTUAL, 1334d VARCHAR(5) AS (left(b,5)) PERSISTENT 1335); 1336CREATE PROCEDURE p1 1337AS 1338c t1.c%TYPE; 1339d t1.d%TYPE; 1340BEGIN 1341SELECT c, d; 1342CREATE TABLE t2 AS SELECT c, d; 1343SHOW CREATE TABLE t2; 1344DROP TABLE t2; 1345END; 1346$$ 1347CALL p1(); 1348c d 1349NULL NULL 1350Table Create Table 1351t2 CREATE TABLE "t2" ( 1352 "c" int(11) DEFAULT NULL, 1353 "d" varchar(5) DEFAULT NULL 1354) 1355DROP PROCEDURE p1; 1356DROP TABLE t1; 1357# 1358# %TYPE + the ZEROFILL attribute 1359# 1360CREATE TABLE t1 ( 1361dz DECIMAL(10,3) ZEROFILL 1362); 1363CREATE PROCEDURE p1 1364AS 1365dzr t1.dz%TYPE := 10; 1366dzt DECIMAL(10,3) ZEROFILL := 10; 1367BEGIN 1368SELECT dzr, dzt; 1369CREATE TABLE t2 AS SELECT dzr,dzt; 1370SHOW CREATE TABLE t2; 1371DROP TABLE t2; 1372END; 1373$$ 1374CALL p1(); 1375dzr dzt 13760000010.000 0000010.000 1377Table Create Table 1378t2 CREATE TABLE "t2" ( 1379 "dzr" decimal(10,3) unsigned DEFAULT NULL, 1380 "dzt" decimal(10,3) unsigned DEFAULT NULL 1381) 1382DROP PROCEDURE p1; 1383DROP TABLE t1; 1384# 1385# Temporary tables shadow real tables for %TYPE purposes 1386# 1387CREATE TABLE t1 (a VARCHAR(10)); 1388INSERT INTO t1 VALUES ('t1'); 1389CREATE TEMPORARY TABLE t1 (a INT); 1390INSERT INTO t1 VALUES (10); 1391SELECT * FROM t1; 1392a 139310 1394CREATE PROCEDURE p1 1395AS 1396a t1.a%TYPE:=11; 1397BEGIN 1398CREATE TABLE t2 AS SELECT a; 1399END; 1400$$ 1401# 1402# Should use INT(11) as %TYPE, as in the temporary table 1403# 1404CALL p1(); 1405SHOW CREATE TABLE t2; 1406Table Create Table 1407t2 CREATE TABLE "t2" ( 1408 "a" int(11) DEFAULT NULL 1409) 1410SELECT * FROM t2; 1411a 141211 1413DROP TABLE t2; 1414SELECT * FROM t1; 1415a 141610 1417DROP TEMPORARY TABLE t1; 1418SELECT * FROM t1; 1419a 1420t1 1421# 1422# Should use VARCHAR(10) as %TYPE, as in the real table 1423# 1424CALL p1(); 1425SHOW CREATE TABLE t2; 1426Table Create Table 1427t2 CREATE TABLE "t2" ( 1428 "a" varchar(10) DEFAULT NULL 1429) 1430SELECT * FROM t2; 1431a 143211 1433DROP TABLE t2; 1434DROP PROCEDURE p1; 1435DROP TABLE t1; 1436# 1437# t1.a%TYPE searches for "t1" in the current database 1438# 1439CREATE TABLE t1 (a VARCHAR(10)); 1440CREATE DATABASE test1; 1441CREATE TABLE test1.t1 (a INT); 1442CREATE PROCEDURE p1 1443AS 1444a t1.a%TYPE:=11; 1445BEGIN 1446CREATE TABLE test.t2 AS SELECT a; 1447END; 1448$$ 1449# 1450# This interprets t1.a%TYPE as VARCHAR(10), as in test.t1.a 1451# 1452USE test; 1453CALL test.p1(); 1454SHOW CREATE TABLE test.t2; 1455Table Create Table 1456t2 CREATE TABLE "t2" ( 1457 "a" varchar(10) DEFAULT NULL 1458) 1459DROP TABLE test.t2; 1460# 1461# This interprets t1.a%TYPE as INT, as in test1.t1.a 1462# 1463USE test1; 1464CALL test.p1(); 1465SHOW CREATE TABLE test.t2; 1466Table Create Table 1467t2 CREATE TABLE "t2" ( 1468 "a" int(11) DEFAULT NULL 1469) 1470DROP TABLE test.t2; 1471# 1472# Error if there is no an active database 1473# 1474DROP DATABASE test1; 1475CALL test.p1(); 1476ERROR 3D000: No database selected 1477USE test; 1478DROP PROCEDURE p1; 1479DROP TABLE t1; 1480# 1481# A reference to a table in a non-existing database 1482# 1483CREATE PROCEDURE p1 1484AS 1485a test1.t1.a%TYPE; 1486BEGIN 1487CREATE TABLE t1 AS SELECT a; 1488END; 1489$$ 1490CALL p1; 1491ERROR 42S02: Table 'test1.t1' doesn't exist 1492DROP PROCEDURE p1; 1493# 1494# A reference to a table in a different database 1495# 1496CREATE TABLE t1(a INT); 1497CREATE DATABASE test1; 1498CREATE TABLE test1.t1 (a VARCHAR(10)); 1499CREATE PROCEDURE p1 1500AS 1501a t1.a%TYPE; 1502b test1.t1.a%TYPE; 1503BEGIN 1504CREATE TABLE t2 AS SELECT a,b; 1505END; 1506$$ 1507CALL p1; 1508SHOW CREATE TABLE t2; 1509Table Create Table 1510t2 CREATE TABLE "t2" ( 1511 "a" int(11) DEFAULT NULL, 1512 "b" varchar(10) DEFAULT NULL 1513) 1514DROP PROCEDURE p1; 1515DROP TABLE t2; 1516DROP DATABASE test1; 1517DROP TABLE t1; 1518# 1519# Using a table before it appears in a %TYPE declaration + multiple %TYPE declarations 1520# 1521CREATE TABLE t1 (a INT, b VARCHAR(10)); 1522INSERT INTO t1 (a,b) VALUES (10,'b10'); 1523CREATE PROCEDURE p1 1524AS 1525BEGIN 1526INSERT INTO t1 (a,b) VALUES (11, 'b11'); 1527SELECT * FROM t1; 1528DECLARE 1529va t1.a%TYPE:= 30; 1530vb t1.b%TYPE:= 'b30'; 1531BEGIN 1532INSERT INTO t1 (a,b) VALUES (12,'b12'); 1533SELECT * FROM t1; 1534INSERT INTO t1 (a,b) VALUES (va, vb); 1535SELECT * FROM t1; 1536END; 1537DECLARE 1538va t1.a%TYPE:= 40; 1539vb t1.b%TYPE:= 'b40'; 1540BEGIN 1541INSERT INTO t1 (a,b) VALUES (va,vb); 1542SELECT * FROM t1; 1543END; 1544END; 1545$$ 1546CALL p1; 1547a b 154810 b10 154911 b11 1550a b 155110 b10 155211 b11 155312 b12 1554a b 155510 b10 155611 b11 155712 b12 155830 b30 1559a b 156010 b10 156111 b11 156212 b12 156330 b30 156440 b40 1565DROP TABLE t1; 1566DROP PROCEDURE p1; 1567# 1568# %TYPE variables + TABLE vs VIEW 1569# 1570CREATE TABLE t1 ( 1571bit6 BIT(6), 1572bit7 BIT(7), 1573bit8 BIT(8), 1574i1 TINYINT, 1575i2 SMALLINT, 1576i3 MEDIUMINT, 1577i4 INT, 1578i8 BIGINT, 1579ff FLOAT, 1580fd DOUBLE, 1581cc CHAR(10), 1582cv VARCHAR(10), 1583cvu VARCHAR(10) CHARACTER SET utf8, 1584t1 TINYTEXT, 1585t2 TEXT, 1586t3 MEDIUMTEXT, 1587t4 LONGTEXT, 1588enum1 ENUM('a','b','c'), 1589set1 SET('a','b','c'), 1590blob1 TINYBLOB, 1591blob2 BLOB, 1592blob3 MEDIUMBLOB, 1593blob4 LONGBLOB, 1594yy YEAR, 1595dd DATE, 1596tm0 TIME, 1597tm3 TIME(3), 1598tm6 TIME(6), 1599dt0 DATETIME, 1600dt3 DATETIME(3), 1601dt6 DATETIME(6), 1602ts0 TIMESTAMP, 1603ts3 TIMESTAMP(3), 1604ts6 TIMESTAMP(6), 1605dc100 DECIMAL(10,0), 1606dc103 DECIMAL(10,3), 1607dc209 DECIMAL(20,9) 1608); 1609CREATE PROCEDURE p1(command enum('create','select')) 1610AS 1611bit6 t1.bit6%TYPE := 0x30; 1612bit7 t1.bit7%TYPE := 0x41; 1613bit8 t1.bit8%TYPE := 0x7E; 1614i1 t1.i1%TYPE := 11; 1615i2 t1.i2%TYPE := 12; 1616i3 t1.i3%TYPE := 13; 1617i4 t1.i4%TYPE := 14; 1618i8 t1.i8%TYPE := 18; 1619ff t1.ff%TYPE := 21; 1620fd t1.fd%TYPE := 22; 1621cc t1.cc%TYPE := 'char'; 1622cv t1.cv%TYPE := 'varchar'; 1623cvu t1.cvu%TYPE := 'varcharu8'; 1624t1 t1.t1%TYPE := 'text1'; 1625t2 t1.t2%TYPE := 'text2'; 1626t3 t1.t3%TYPE := 'text3'; 1627t4 t1.t4%TYPE := 'text4'; 1628enum1 t1.enum1%TYPE := 'b'; 1629set1 t1.set1%TYPE := 'a,c'; 1630blob1 t1.blob1%TYPE := 'blob1'; 1631blob2 t1.blob2%TYPE := 'blob2'; 1632blob3 t1.blob3%TYPE := 'blob3'; 1633blob4 t1.blob4%TYPE := 'blob4'; 1634yy t1.yy%TYPE := 2001; 1635dd t1.dd%TYPE := '2001-01-01'; 1636tm0 t1.tm0%TYPE := '00:00:01'; 1637tm3 t1.tm3%TYPE := '00:00:03.333'; 1638tm6 t1.tm6%TYPE := '00:00:06.666666'; 1639dt0 t1.dt0%TYPE := '2001-01-01 00:00:01'; 1640dt3 t1.dt3%TYPE := '2001-01-03 00:00:01.333'; 1641dt6 t1.dt6%TYPE := '2001-01-06 00:00:01.666666'; 1642ts0 t1.ts0%TYPE := '2002-01-01 00:00:01'; 1643ts3 t1.ts3%TYPE := '2002-01-03 00:00:01.333'; 1644ts6 t1.ts6%TYPE := '2002-01-06 00:00:01.666666'; 1645dc100 t1.dc100%TYPE := 10; 1646dc103 t1.dc103%TYPE := 10.123; 1647dc209 t1.dc209%TYPE := 10.123456789; 1648BEGIN 1649CASE 1650WHEN command='create' THEN 1651CREATE TABLE t2 AS SELECT 1652bit6, bit7, bit8, 1653i1,i2,i3,i4,i8, 1654ff,fd, dc100, dc103, dc209, 1655cc,cv,cvu, 1656t1,t2,t3,t4, 1657enum1, set1, 1658blob1, blob2, blob3, blob4, 1659dd, yy, 1660tm0, tm3, tm6, 1661dt0, dt3, dt6, 1662ts0, ts3, ts6; 1663WHEN command='select' THEN 1664SELECT 1665bit6, bit7, bit8, 1666i1,i2,i3,i4,i8, 1667ff,fd, dc100, dc103, dc209, 1668cc,cv,cvu, 1669t1,t2,t3,t4, 1670enum1, set1, 1671blob1, blob2, blob3, blob4, 1672dd, yy, 1673tm0, tm3, tm6, 1674dt0, dt3, dt6, 1675ts0, ts3, ts6; 1676END CASE; 1677END; 1678$$ 1679# 1680# TABLE 1681# 1682CALL p1('create'); 1683SHOW CREATE TABLE t2; 1684Table Create Table 1685t2 CREATE TABLE "t2" ( 1686 "bit6" bit(6) DEFAULT NULL, 1687 "bit7" bit(7) DEFAULT NULL, 1688 "bit8" bit(8) DEFAULT NULL, 1689 "i1" tinyint(4) DEFAULT NULL, 1690 "i2" smallint(6) DEFAULT NULL, 1691 "i3" mediumint(9) DEFAULT NULL, 1692 "i4" int(11) DEFAULT NULL, 1693 "i8" bigint(20) DEFAULT NULL, 1694 "ff" float DEFAULT NULL, 1695 "fd" double DEFAULT NULL, 1696 "dc100" decimal(10,0) DEFAULT NULL, 1697 "dc103" decimal(10,3) DEFAULT NULL, 1698 "dc209" decimal(20,9) DEFAULT NULL, 1699 "cc" char(10) DEFAULT NULL, 1700 "cv" varchar(10) DEFAULT NULL, 1701 "cvu" varchar(10) CHARACTER SET utf8 DEFAULT NULL, 1702 "t1" tinytext DEFAULT NULL, 1703 "t2" text DEFAULT NULL, 1704 "t3" mediumtext DEFAULT NULL, 1705 "t4" longtext DEFAULT NULL, 1706 "enum1" char(1) DEFAULT NULL, 1707 "set1" char(5) DEFAULT NULL, 1708 "blob1" tinyblob DEFAULT NULL, 1709 "blob2" longblob DEFAULT NULL, 1710 "blob3" mediumblob DEFAULT NULL, 1711 "blob4" longblob DEFAULT NULL, 1712 "dd" datetime DEFAULT NULL, 1713 "yy" year(4) DEFAULT NULL, 1714 "tm0" time DEFAULT NULL, 1715 "tm3" time(3) DEFAULT NULL, 1716 "tm6" time(6) DEFAULT NULL, 1717 "dt0" datetime DEFAULT NULL, 1718 "dt3" datetime(3) DEFAULT NULL, 1719 "dt6" datetime(6) DEFAULT NULL, 1720 "ts0" timestamp NULL DEFAULT NULL, 1721 "ts3" timestamp(3) NULL DEFAULT NULL, 1722 "ts6" timestamp(6) NULL DEFAULT NULL 1723) 1724SELECT * FROM t2; 1725bit6 0 1726bit7 A 1727bit8 ~ 1728i1 11 1729i2 12 1730i3 13 1731i4 14 1732i8 18 1733ff 21 1734fd 22 1735dc100 10 1736dc103 10.123 1737dc209 10.123456789 1738cc char 1739cv varchar 1740cvu varcharu8 1741t1 text1 1742t2 text2 1743t3 text3 1744t4 text4 1745enum1 b 1746set1 a,c 1747blob1 blob1 1748blob2 blob2 1749blob3 blob3 1750blob4 blob4 1751dd 2001-01-01 00:00:00 1752yy 2001 1753tm0 00:00:01 1754tm3 00:00:03.333 1755tm6 00:00:06.666666 1756dt0 2001-01-01 00:00:01 1757dt3 2001-01-03 00:00:01.333 1758dt6 2001-01-06 00:00:01.666666 1759ts0 2002-01-01 00:00:01 1760ts3 2002-01-03 00:00:01.333 1761ts6 2002-01-06 00:00:01.666666 1762DROP TABLE t2; 1763CALL p1('select'); 1764Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1765def bit6 bit6 16 6 1 Y 32 0 63 1766def bit7 bit7 16 7 1 Y 32 0 63 1767def bit8 bit8 16 8 1 Y 32 0 63 1768def i1 i1 1 4 2 Y 32768 0 63 1769def i2 i2 2 6 2 Y 32768 0 63 1770def i3 i3 9 9 2 Y 32768 0 63 1771def i4 i4 3 11 2 Y 32768 0 63 1772def i8 i8 8 20 2 Y 32768 0 63 1773def ff ff 4 12 2 Y 32768 31 63 1774def fd fd 5 22 2 Y 32768 31 63 1775def dc100 dc100 246 11 2 Y 32768 0 63 1776def dc103 dc103 246 12 6 Y 32768 3 63 1777def dc209 dc209 246 22 12 Y 32768 9 63 1778def cc cc 254 10 4 Y 0 0 8 1779def cv cv 253 10 7 Y 0 0 8 1780def cvu cvu 253 10 9 Y 0 0 8 1781def t1 t1 252 255 5 Y 16 0 8 1782def t2 t2 252 65535 5 Y 16 0 8 1783def t3 t3 252 16777215 5 Y 16 0 8 1784def t4 t4 252 4294967295 5 Y 16 0 8 1785def enum1 enum1 254 1 1 Y 256 0 8 1786def set1 set1 254 5 3 Y 2048 0 8 1787def blob1 blob1 252 255 5 Y 144 0 63 1788def blob2 blob2 252 4294967295 5 Y 144 0 63 1789def blob3 blob3 252 16777215 5 Y 144 0 63 1790def blob4 blob4 252 4294967295 5 Y 144 0 63 1791def dd dd 12 19 19 Y 128 0 63 1792def yy yy 13 4 4 Y 32864 0 63 1793def tm0 tm0 11 10 8 Y 128 0 63 1794def tm3 tm3 11 14 12 Y 128 3 63 1795def tm6 tm6 11 17 15 Y 128 6 63 1796def dt0 dt0 12 19 19 Y 128 0 63 1797def dt3 dt3 12 23 23 Y 128 3 63 1798def dt6 dt6 12 26 26 Y 128 6 63 1799def ts0 ts0 7 19 19 Y 9376 0 63 1800def ts3 ts3 7 23 23 Y 160 3 63 1801def ts6 ts6 7 26 26 Y 160 6 63 1802bit6 0 1803bit7 A 1804bit8 ~ 1805i1 11 1806i2 12 1807i3 13 1808i4 14 1809i8 18 1810ff 21 1811fd 22 1812dc100 10 1813dc103 10.123 1814dc209 10.123456789 1815cc char 1816cv varchar 1817cvu varcharu8 1818t1 text1 1819t2 text2 1820t3 text3 1821t4 text4 1822enum1 b 1823set1 a,c 1824blob1 blob1 1825blob2 blob2 1826blob3 blob3 1827blob4 blob4 1828dd 2001-01-01 00:00:00 1829yy 2001 1830tm0 00:00:01 1831tm3 00:00:03.333 1832tm6 00:00:06.666666 1833dt0 2001-01-01 00:00:01 1834dt3 2001-01-03 00:00:01.333 1835dt6 2001-01-06 00:00:01.666666 1836ts0 2002-01-01 00:00:01 1837ts3 2002-01-03 00:00:01.333 1838ts6 2002-01-06 00:00:01.666666 1839# 1840# VIEW 1841# 1842ALTER TABLE t1 RENAME t0; 1843CREATE VIEW t1 AS SELECT * FROM t0; 1844CALL p1('create'); 1845SHOW CREATE TABLE t2; 1846Table Create Table 1847t2 CREATE TABLE "t2" ( 1848 "bit6" bit(6) DEFAULT NULL, 1849 "bit7" bit(7) DEFAULT NULL, 1850 "bit8" bit(8) DEFAULT NULL, 1851 "i1" tinyint(4) DEFAULT NULL, 1852 "i2" smallint(6) DEFAULT NULL, 1853 "i3" mediumint(9) DEFAULT NULL, 1854 "i4" int(11) DEFAULT NULL, 1855 "i8" bigint(20) DEFAULT NULL, 1856 "ff" float DEFAULT NULL, 1857 "fd" double DEFAULT NULL, 1858 "dc100" decimal(10,0) DEFAULT NULL, 1859 "dc103" decimal(10,3) DEFAULT NULL, 1860 "dc209" decimal(20,9) DEFAULT NULL, 1861 "cc" char(10) DEFAULT NULL, 1862 "cv" varchar(10) DEFAULT NULL, 1863 "cvu" varchar(10) CHARACTER SET utf8 DEFAULT NULL, 1864 "t1" tinytext DEFAULT NULL, 1865 "t2" text DEFAULT NULL, 1866 "t3" mediumtext DEFAULT NULL, 1867 "t4" longtext DEFAULT NULL, 1868 "enum1" char(1) DEFAULT NULL, 1869 "set1" char(5) DEFAULT NULL, 1870 "blob1" tinyblob DEFAULT NULL, 1871 "blob2" longblob DEFAULT NULL, 1872 "blob3" mediumblob DEFAULT NULL, 1873 "blob4" longblob DEFAULT NULL, 1874 "dd" datetime DEFAULT NULL, 1875 "yy" year(4) DEFAULT NULL, 1876 "tm0" time DEFAULT NULL, 1877 "tm3" time(3) DEFAULT NULL, 1878 "tm6" time(6) DEFAULT NULL, 1879 "dt0" datetime DEFAULT NULL, 1880 "dt3" datetime(3) DEFAULT NULL, 1881 "dt6" datetime(6) DEFAULT NULL, 1882 "ts0" timestamp NULL DEFAULT NULL, 1883 "ts3" timestamp(3) NULL DEFAULT NULL, 1884 "ts6" timestamp(6) NULL DEFAULT NULL 1885) 1886SELECT * FROM t2; 1887bit6 0 1888bit7 A 1889bit8 ~ 1890i1 11 1891i2 12 1892i3 13 1893i4 14 1894i8 18 1895ff 21 1896fd 22 1897dc100 10 1898dc103 10.123 1899dc209 10.123456789 1900cc char 1901cv varchar 1902cvu varcharu8 1903t1 text1 1904t2 text2 1905t3 text3 1906t4 text4 1907enum1 b 1908set1 a,c 1909blob1 blob1 1910blob2 blob2 1911blob3 blob3 1912blob4 blob4 1913dd 2001-01-01 00:00:00 1914yy 2001 1915tm0 00:00:01 1916tm3 00:00:03.333 1917tm6 00:00:06.666666 1918dt0 2001-01-01 00:00:01 1919dt3 2001-01-03 00:00:01.333 1920dt6 2001-01-06 00:00:01.666666 1921ts0 2002-01-01 00:00:01 1922ts3 2002-01-03 00:00:01.333 1923ts6 2002-01-06 00:00:01.666666 1924DROP TABLE t2; 1925CALL p1('select'); 1926Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1927def bit6 bit6 16 6 1 Y 32 0 63 1928def bit7 bit7 16 7 1 Y 32 0 63 1929def bit8 bit8 16 8 1 Y 32 0 63 1930def i1 i1 1 4 2 Y 32768 0 63 1931def i2 i2 2 6 2 Y 32768 0 63 1932def i3 i3 9 9 2 Y 32768 0 63 1933def i4 i4 3 11 2 Y 32768 0 63 1934def i8 i8 8 20 2 Y 32768 0 63 1935def ff ff 4 12 2 Y 32768 31 63 1936def fd fd 5 22 2 Y 32768 31 63 1937def dc100 dc100 246 11 2 Y 32768 0 63 1938def dc103 dc103 246 12 6 Y 32768 3 63 1939def dc209 dc209 246 22 12 Y 32768 9 63 1940def cc cc 254 10 4 Y 0 0 8 1941def cv cv 253 10 7 Y 0 0 8 1942def cvu cvu 253 10 9 Y 0 0 8 1943def t1 t1 252 255 5 Y 16 0 8 1944def t2 t2 252 65535 5 Y 16 0 8 1945def t3 t3 252 16777215 5 Y 16 0 8 1946def t4 t4 252 4294967295 5 Y 16 0 8 1947def enum1 enum1 254 1 1 Y 256 0 8 1948def set1 set1 254 5 3 Y 2048 0 8 1949def blob1 blob1 252 255 5 Y 144 0 63 1950def blob2 blob2 252 4294967295 5 Y 144 0 63 1951def blob3 blob3 252 16777215 5 Y 144 0 63 1952def blob4 blob4 252 4294967295 5 Y 144 0 63 1953def dd dd 12 19 19 Y 128 0 63 1954def yy yy 13 4 4 Y 32864 0 63 1955def tm0 tm0 11 10 8 Y 128 0 63 1956def tm3 tm3 11 14 12 Y 128 3 63 1957def tm6 tm6 11 17 15 Y 128 6 63 1958def dt0 dt0 12 19 19 Y 128 0 63 1959def dt3 dt3 12 23 23 Y 128 3 63 1960def dt6 dt6 12 26 26 Y 128 6 63 1961def ts0 ts0 7 19 19 Y 160 0 63 1962def ts3 ts3 7 23 23 Y 160 3 63 1963def ts6 ts6 7 26 26 Y 160 6 63 1964bit6 0 1965bit7 A 1966bit8 ~ 1967i1 11 1968i2 12 1969i3 13 1970i4 14 1971i8 18 1972ff 21 1973fd 22 1974dc100 10 1975dc103 10.123 1976dc209 10.123456789 1977cc char 1978cv varchar 1979cvu varcharu8 1980t1 text1 1981t2 text2 1982t3 text3 1983t4 text4 1984enum1 b 1985set1 a,c 1986blob1 blob1 1987blob2 blob2 1988blob3 blob3 1989blob4 blob4 1990dd 2001-01-01 00:00:00 1991yy 2001 1992tm0 00:00:01 1993tm3 00:00:03.333 1994tm6 00:00:06.666666 1995dt0 2001-01-01 00:00:01 1996dt3 2001-01-03 00:00:01.333 1997dt6 2001-01-06 00:00:01.666666 1998ts0 2002-01-01 00:00:01 1999ts3 2002-01-03 00:00:01.333 2000ts6 2002-01-06 00:00:01.666666 2001DROP VIEW t1; 2002DROP TABLE t0; 2003DROP PROCEDURE p1; 2004# 2005# VIEW with subqueries 2006# 2007CREATE TABLE t1 (a INT,b INT); 2008INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4); 2009SELECT AVG(a) FROM t1; 2010AVG(a) 201125.0000 2012CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1); 2013SELECT * FROM v1; 2014a b 201530 1 201640 1 2017CREATE PROCEDURE p1 2018AS 2019a v1.a%TYPE := 10; 2020b v1.b%TYPE := 1; 2021BEGIN 2022SELECT a,b; 2023END; 2024$$ 2025CALL p1; 2026a b 202710 1 2028DROP PROCEDURE p1; 2029CREATE FUNCTION f1 RETURN INT 2030AS 2031a v1.a%TYPE := 10; 2032b v1.b%TYPE := 1; 2033BEGIN 2034RETURN a+b; 2035END; 2036$$ 2037SELECT f1(); 2038f1() 203911 2040DROP FUNCTION f1; 2041DROP VIEW v1; 2042DROP TABLE t1; 2043# 2044# %TYPE variables + INFORMATION_SCHEMA 2045# 2046CREATE PROCEDURE p1 2047AS 2048tables_table_name INFORMATION_SCHEMA.TABLES.TABLE_NAME%TYPE; 2049tables_table_rows INFORMATION_SCHEMA.TABLES.TABLE_ROWS%TYPE; 2050processlist_info INFORMATION_SCHEMA.PROCESSLIST.INFO%TYPE; 2051processlist_info_binary INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY%TYPE; 2052BEGIN 2053CREATE TABLE t1 AS SELECT 2054tables_table_name, 2055tables_table_rows, 2056processlist_info, 2057processlist_info_binary; 2058END; 2059$$ 2060CALL p1(); 2061SHOW CREATE TABLE t1; 2062Table Create Table 2063t1 CREATE TABLE "t1" ( 2064 "tables_table_name" varchar(64) CHARACTER SET utf8 DEFAULT NULL, 2065 "tables_table_rows" bigint(21) unsigned DEFAULT NULL, 2066 "processlist_info" longtext CHARACTER SET utf8 DEFAULT NULL, 2067 "processlist_info_binary" blob(65535) DEFAULT NULL 2068) 2069DROP TABLE t1; 2070DROP PROCEDURE p1; 2071# 2072# %TYPE + Table structure change 2073# Data type for both a0 and a1 is chosen in the very beginning 2074# 2075CREATE PROCEDURE p1 2076AS 2077a0 t1.a%TYPE; 2078BEGIN 2079ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1 2080DECLARE 2081a1 t1.a%TYPE; 2082BEGIN 2083CREATE TABLE t2 AS SELECT a0, a1; 2084SHOW CREATE TABLE t2; 2085DROP TABLE t2; 2086END; 2087END 2088$$ 2089CREATE TABLE t1 (a INT); 2090CALL p1; 2091Table Create Table 2092t2 CREATE TABLE "t2" ( 2093 "a0" int(11) DEFAULT NULL, 2094 "a1" int(11) DEFAULT NULL 2095) 2096DROP TABLE t1; 2097DROP PROCEDURE p1; 2098# 2099# %TYPE in parameters 2100# 2101CREATE TABLE t1 (a VARCHAR(10)); 2102CREATE DATABASE test1; 2103CREATE TABLE test1.t1 (b SMALLINT); 2104CREATE PROCEDURE p1(a t1.a%TYPE, b test1.t1.b%TYPE) 2105AS 2106BEGIN 2107CREATE TABLE t2 AS SELECT a, b; 2108END; 2109$$ 2110CALL p1('test', 123); 2111SHOW CREATE TABLE t2; 2112Table Create Table 2113t2 CREATE TABLE "t2" ( 2114 "a" varchar(10) DEFAULT NULL, 2115 "b" smallint(6) DEFAULT NULL 2116) 2117SELECT * FROM t2; 2118a b 2119test 123 2120DROP TABLE t2; 2121DROP PROCEDURE p1; 2122DROP TABLE test1.t1; 2123DROP DATABASE test1; 2124DROP TABLE t1; 2125# 2126# %TYPE in a stored function variables and arguments 2127# 2128CREATE TABLE t1 (a INT); 2129SET sql_mode=ORACLE; 2130CREATE FUNCTION f1 (prm t1.a%TYPE) RETURN INT 2131AS 2132a t1.a%TYPE:= prm; 2133BEGIN 2134RETURN a; 2135END; 2136$$ 2137SELECT f1(20); 2138f1(20) 213920 2140DROP FUNCTION f1; 2141DROP TABLE t1; 2142# 2143# %TYPE in function RETURN clause is not supported yet 2144# 2145CREATE FUNCTION f1 RETURN t1.a%TYPE 2146AS 2147BEGIN 2148RETURN 0; 2149END; 2150$$ 2151ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1.a%TYPE 2152AS 2153BEGIN 2154RETURN 0; 2155END' at line 1 2156# 2157# End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations 2158# 2159# 2160# MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword 2161# 2162CREATE FUNCTION f1 RETURN INT AS 2163BEGIN 2164RETURN 10; 2165END f1; 2166$$ 2167DROP FUNCTION f1; 2168CREATE FUNCTION test.f1 RETURN INT AS 2169BEGIN 2170RETURN 10; 2171END test.f1; 2172$$ 2173DROP FUNCTION f1; 2174CREATE FUNCTION test.f1 RETURN INT AS 2175BEGIN 2176RETURN 10; 2177END test2.f1; 2178$$ 2179ERROR HY000: END identifier 'test2.f1' does not match 'test.f1' 2180CREATE FUNCTION test.f1 RETURN INT AS 2181BEGIN 2182RETURN 10; 2183END test.f2; 2184$$ 2185ERROR HY000: END identifier 'test.f2' does not match 'test.f1' 2186CREATE FUNCTION f1 RETURN INT AS 2187BEGIN 2188RETURN 10; 2189END test.f2; 2190$$ 2191ERROR HY000: END identifier 'test.f2' does not match 'test.f1' 2192CREATE FUNCTION f1 RETURN INT AS 2193BEGIN 2194RETURN 10; 2195END test2.f1; 2196$$ 2197ERROR HY000: END identifier 'test2.f1' does not match 'test.f1' 2198CREATE PROCEDURE p1 AS 2199BEGIN 2200NULL; 2201END p1; 2202$$ 2203DROP PROCEDURE p1; 2204CREATE PROCEDURE test.p1 AS 2205BEGIN 2206NULL; 2207END test.p1; 2208$$ 2209DROP PROCEDURE p1; 2210CREATE PROCEDURE test.p1 AS 2211BEGIN 2212NULL; 2213END test2.p1; 2214$$ 2215ERROR HY000: END identifier 'test2.p1' does not match 'test.p1' 2216CREATE PROCEDURE test.p1 AS 2217BEGIN 2218NULL; 2219END test.p2; 2220$$ 2221ERROR HY000: END identifier 'test.p2' does not match 'test.p1' 2222CREATE PROCEDURE p1 AS 2223BEGIN 2224NULL; 2225END test.p2; 2226$$ 2227ERROR HY000: END identifier 'test.p2' does not match 'test.p1' 2228CREATE PROCEDURE p1 AS 2229BEGIN 2230NULL; 2231END test2.p1; 2232$$ 2233ERROR HY000: END identifier 'test2.p1' does not match 'test.p1' 2234# 2235# MDEV-12107 sql_mode=ORACLE: Inside routines the CALL keywoard is optional 2236# 2237CREATE OR REPLACE PROCEDURE p1(a INT) AS 2238BEGIN 2239SELECT 'This is p1' AS "comment"; 2240END; 2241/ 2242CREATE OR REPLACE PROCEDURE p2 AS 2243BEGIN 2244SELECT 'This is p2' AS "comment"; 2245END; 2246/ 2247BEGIN 2248p1(10); 2249p2; 2250test.p1(10); 2251test.p2; 2252END; 2253/ 2254comment 2255This is p1 2256comment 2257This is p2 2258comment 2259This is p1 2260comment 2261This is p2 2262CREATE PROCEDURE p3 AS 2263BEGIN 2264p1(10); 2265p2; 2266test.p1(10); 2267test.p2; 2268END 2269/ 2270CALL p3; 2271comment 2272This is p1 2273comment 2274This is p2 2275comment 2276This is p1 2277comment 2278This is p2 2279DROP PROCEDURE p3; 2280DROP PROCEDURE p2; 2281DROP PROCEDURE p1; 2282# 2283# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions 2284# 2285SELECT SQL%ROWCOUNT; 2286Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2287def SQL%ROWCOUNT 8 21 1 N 32897 0 63 2288SQL%ROWCOUNT 22890 2290# 2291# MDEV-13686 EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself 2292# 2293CREATE TABLE t1 (c1 int); 2294CREATE VIEW v1 AS SELECT c1 exception FROM t1; 2295SELECT exception FROM v1; 2296exception 2297DROP VIEW v1; 2298DROP TABLE t1; 2299# 2300# MDEV-14139 Anchored data types for variables 2301# 2302BEGIN NOT ATOMIC 2303DECLARE a a%TYPE; 2304END; 2305$$ 2306ERROR 42000: Undeclared variable: a 2307DECLARE 2308int11 INT; 2309dec103 DECIMAL(10,3); 2310flt0 FLOAT; 2311dbl0 DOUBLE; 2312enum0 ENUM('a','b'); 2313bit3 BIT(3); 2314varchar10 VARCHAR(10); 2315text1 TEXT; 2316tinytext1 TINYTEXT; 2317mediumtext1 MEDIUMTEXT; 2318longtext1 LONGTEXT; 2319time3 TIME(3); 2320datetime4 DATETIME(4); 2321timestamp5 TIMESTAMP(5); 2322date0 DATE; 2323a_int11 int11%TYPE; 2324a_dec103 dec103%TYPE; 2325a_flt0 flt0%TYPE; 2326a_dbl0 dbl0%TYPE; 2327a_bit3 bit3%TYPE; 2328a_enum0 enum0%TYPE; 2329a_varchar10 varchar10%TYPE; 2330a_text1 text1%TYPE; 2331a_tinytext1 tinytext1%TYPE; 2332a_mediumtext1 mediumtext1%TYPE; 2333a_longtext1 longtext1%TYPE; 2334a_time3 time3%TYPE; 2335a_datetime4 datetime4%TYPE; 2336a_timestamp5 timestamp5%TYPE; 2337a_date0 date0%TYPE; 2338aa_int11 a_int11%TYPE; 2339aa_dec103 a_dec103%TYPE; 2340aa_flt0 a_flt0%TYPE; 2341aa_dbl0 a_dbl0%TYPE; 2342aa_bit3 a_bit3%TYPE; 2343aa_enum0 a_enum0%TYPE; 2344aa_varchar10 a_varchar10%TYPE; 2345aa_text1 a_text1%TYPE; 2346aa_tinytext1 a_tinytext1%TYPE; 2347aa_mediumtext1 a_mediumtext1%TYPE; 2348aa_longtext1 a_longtext1%TYPE; 2349aa_time3 a_time3%TYPE; 2350aa_datetime4 a_datetime4%TYPE; 2351aa_timestamp5 a_timestamp5%TYPE; 2352aa_date0 a_date0%TYPE; 2353BEGIN 2354CREATE TABLE t1 AS 2355SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3, 2356a_enum0,a_varchar10, 2357a_text1,a_tinytext1,a_mediumtext1,a_longtext1, 2358a_time3,a_datetime4,a_timestamp5,a_date0; 2359SHOW CREATE TABLE t1; 2360DROP TABLE t1; 2361CREATE TABLE t1 AS 2362SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3, 2363aa_enum0,aa_varchar10, 2364aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1, 2365aa_time3,aa_datetime4,aa_timestamp5,aa_date0; 2366SHOW CREATE TABLE t1; 2367DROP TABLE t1; 2368END; 2369$$ 2370Table Create Table 2371t1 CREATE TABLE "t1" ( 2372 "a_int11" int(11) DEFAULT NULL, 2373 "a_dec103" decimal(10,3) DEFAULT NULL, 2374 "a_flt0" float DEFAULT NULL, 2375 "a_dbl0" double DEFAULT NULL, 2376 "a_bit3" bit(3) DEFAULT NULL, 2377 "a_enum0" char(1) DEFAULT NULL, 2378 "a_varchar10" varchar(10) DEFAULT NULL, 2379 "a_text1" text DEFAULT NULL, 2380 "a_tinytext1" tinytext DEFAULT NULL, 2381 "a_mediumtext1" mediumtext DEFAULT NULL, 2382 "a_longtext1" longtext DEFAULT NULL, 2383 "a_time3" time(3) DEFAULT NULL, 2384 "a_datetime4" datetime(4) DEFAULT NULL, 2385 "a_timestamp5" timestamp(5) NULL DEFAULT NULL, 2386 "a_date0" datetime DEFAULT NULL 2387) 2388Table Create Table 2389t1 CREATE TABLE "t1" ( 2390 "aa_int11" int(11) DEFAULT NULL, 2391 "aa_dec103" decimal(10,3) DEFAULT NULL, 2392 "aa_flt0" float DEFAULT NULL, 2393 "aa_dbl0" double DEFAULT NULL, 2394 "aa_bit3" bit(3) DEFAULT NULL, 2395 "aa_enum0" char(1) DEFAULT NULL, 2396 "aa_varchar10" varchar(10) DEFAULT NULL, 2397 "aa_text1" text DEFAULT NULL, 2398 "aa_tinytext1" tinytext DEFAULT NULL, 2399 "aa_mediumtext1" mediumtext DEFAULT NULL, 2400 "aa_longtext1" longtext DEFAULT NULL, 2401 "aa_time3" time(3) DEFAULT NULL, 2402 "aa_datetime4" datetime(4) DEFAULT NULL, 2403 "aa_timestamp5" timestamp(5) NULL DEFAULT NULL, 2404 "aa_date0" datetime DEFAULT NULL 2405) 2406# 2407# MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar" 2408# 2409CREATE TABLE t1 (x INT); 2410INSERT INTO t1 VALUES (10); 2411CREATE VIEW v1 AS SELECT x+1 AS a,x+1 AS b FROM t1; 2412CREATE PROCEDURE p1 2413AS 2414a INT := 1; 2415b INT := 2; 2416BEGIN 2417CREATE TABLE t2 AS SELECT a,b FROM v1; 2418SHOW CREATE TABLE t2; 2419SELECT * FROM t2; 2420DROP TABLE t2; 2421END; 2422$$ 2423CALL p1(); 2424Table Create Table 2425t2 CREATE TABLE "t2" ( 2426 "a" int(11) DEFAULT NULL, 2427 "b" int(11) DEFAULT NULL 2428) 2429a b 24301 2 2431DROP PROCEDURE p1; 2432DROP VIEW v1; 2433DROP TABLE t1; 2434# 2435# MDEV-14228 MariaDB crashes with function 2436# 2437CREATE TABLE t1 (c VARCHAR(16), KEY(c)); 2438INSERT INTO t1 VALUES ('foo'); 2439CREATE FUNCTION f1() RETURN VARCHAR(16) 2440IS 2441v VARCHAR2(16); 2442BEGIN 2443FOR v IN (SELECT DISTINCT c FROM t1) 2444LOOP 2445IF (v = 'bar') THEN 2446SELECT 1 INTO @a; 2447END IF; 2448END LOOP; 2449RETURN 'qux'; 2450END $$ 2451SELECT f1(); 2452ERROR HY000: Illegal parameter data types row and varchar for operation '=' 2453DROP FUNCTION f1; 2454CREATE FUNCTION f1() RETURN VARCHAR(16) 2455IS 2456v t1%ROWTYPE; 2457BEGIN 2458IF v = 'bar' THEN 2459NULL; 2460END IF; 2461RETURN 'qux'; 2462END $$ 2463SELECT f1(); 2464ERROR HY000: Illegal parameter data types row and varchar for operation '=' 2465DROP FUNCTION f1; 2466CREATE FUNCTION f1() RETURN VARCHAR(16) 2467IS 2468v ROW(a INT); 2469BEGIN 2470IF v = 'bar' THEN 2471NULL; 2472END IF; 2473RETURN 'qux'; 2474END $$ 2475SELECT f1(); 2476ERROR HY000: Illegal parameter data types row and varchar for operation '=' 2477DROP FUNCTION f1; 2478DROP TABLE t1; 2479DECLARE 2480v ROW(a INT); 2481BEGIN 2482SELECT v IN ('a','b'); 2483END $$ 2484ERROR HY000: Illegal parameter data types row and varchar for operation 'in' 2485DECLARE 2486v ROW(a INT); 2487BEGIN 2488SELECT 'a' IN (v,'b'); 2489END $$ 2490ERROR HY000: Illegal parameter data types varchar and row for operation 'in' 2491DECLARE 2492v ROW(a INT); 2493BEGIN 2494SELECT 'a' IN ('b',v); 2495END $$ 2496ERROR HY000: Illegal parameter data types varchar and row for operation 'in' 2497# 2498# MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly 2499# 2500DECLARE 2501totalprice DECIMAL(12,2):=NULL; 2502loop_start INTEGER := 1; 2503BEGIN 2504FOR idx IN REVERSE loop_start..10 LOOP 2505SELECT idx; 2506END LOOP; 2507END; 2508$$ 2509idx 251010 2511idx 25129 2513idx 25148 2515idx 25167 2517idx 25186 2519idx 25205 2521idx 25224 2523idx 25243 2525idx 25262 2527idx 25281 2529CREATE PROCEDURE p1 AS 2530loop_start INTEGER := 1; 2531BEGIN 2532FOR idx IN REVERSE 3..loop_start LOOP 2533SELECT idx; 2534END LOOP; 2535END; 2536$$ 2537CALL p1(); 2538DROP PROCEDURE p1; 2539CREATE PROCEDURE p1 AS 2540loop_start INTEGER := 1; 2541BEGIN 2542FOR idx IN REVERSE loop_start..3 LOOP 2543SELECT idx; 2544END LOOP; 2545END; 2546$$ 2547CALL p1(); 2548idx 25493 2550idx 25512 2552idx 25531 2554DROP PROCEDURE p1; 2555# 2556# Start of 10.4 tests 2557# 2558# 2559# MDEV-19637 Crash on an SP variable assignment to a wrong subselect 2560# 2561DECLARE 2562a INT; 2563BEGIN 2564SET a=(SELECT 1 FROM DUAL UNION SELECT HIGH_PRIORITY 2 FROM DUAL); 2565END; 2566$$ 2567ERROR 42000: Incorrect usage/placement of 'HIGH_PRIORITY' 2568# 2569# End of 10.4 tests 2570# 2571