|
| 1 | +// Licensed to the Apache Software Foundation (ASF) under one |
| 2 | +// or more contributor license agreements. See the NOTICE file |
| 3 | +// distributed with this work for additional information |
| 4 | +// regarding copyright ownership. The ASF licenses this file |
| 5 | +// to you under the Apache License, Version 2.0 (the |
| 6 | +// "License"); you may not use this file except in compliance |
| 7 | +// with the License. You may obtain a copy of the License at |
| 8 | +// |
| 9 | +// http://www.apache.org/licenses/LICENSE-2.0 |
| 10 | +// |
| 11 | +// Unless required by applicable law or agreed to in writing, |
| 12 | +// software distributed under the License is distributed on an |
| 13 | +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 14 | +// KIND, either express or implied. See the License for the |
| 15 | +// specific language governing permissions and limitations |
| 16 | +// under the License. |
| 17 | + |
| 18 | +// Add PL-SQL regression test notice: |
| 19 | +// 1. JDBC does not support the execution of stored procedures that return results. You can only Into the execution |
| 20 | +// results into a variable or write them into a table, because when multiple result sets are returned, JDBC needs |
| 21 | +// to use the prepareCall statement to execute, otherwise the Statemnt of the returned result executes Finalize. |
| 22 | +// Send EOF Packet will report an error; |
| 23 | +// 2. The format of the result returned by Doris Statement is xxxx\n, xxxx\n, 2 rows affected (0.03 sec). |
| 24 | +// PL-SQL uses Print to print variable values in an unformatted format, and JDBC cannot easily obtain them. Real results. |
| 25 | +suite("test_plsql_routine") { |
| 26 | + def dbName = "plsql_routine" |
| 27 | + sql "CREATE DATABASE IF NOT EXISTS ${dbName}" |
| 28 | + sql "use ${dbName}" |
| 29 | + def tableName = "plsql_tbl_4" |
| 30 | + sql "DROP TABLE IF EXISTS ${tableName}" |
| 31 | + sql """ |
| 32 | + create table ${tableName} (id int, name varchar(20)) DUPLICATE key(`id`) distributed by hash (`id`) buckets 4 |
| 33 | + properties ("replication_num"="1"); |
| 34 | + """ |
| 35 | + |
| 36 | + sql """ |
| 37 | + CREATE OR REPLACE PROCEDURE routine_insert(IN id int, IN name STRING) |
| 38 | + BEGIN |
| 39 | + INSERT INTO ${tableName} VALUES(id, name); |
| 40 | + END; |
| 41 | + """ |
| 42 | + sql """call routine_insert(111, "plsql111")""" |
| 43 | + sql """call routine_insert(222, "plsql222")""" |
| 44 | + sql """call routine_insert(333, "plsql333")""" |
| 45 | + sql """call routine_insert(111, "plsql333")""" |
| 46 | + qt_select "select sum(id), count(1) from ${tableName}" |
| 47 | + |
| 48 | + sql """ |
| 49 | + CREATE OR REPLACE PROCEDURE routine_cursor_select(IN id_arg INT, IN name_arg STRING) |
| 50 | + BEGIN |
| 51 | + DECLARE a INT; |
| 52 | + DECLARE b, c STRING; |
| 53 | +
|
| 54 | + DECLARE cur1 CURSOR FOR select * from ${tableName} where id=id_arg limit 5; |
| 55 | + OPEN cur1; |
| 56 | + read_loop: LOOP |
| 57 | + FETCH cur1 INTO a, b; |
| 58 | + IF(SQLCODE != 0) THEN |
| 59 | + LEAVE read_loop; |
| 60 | + END IF; |
| 61 | + print a, b; |
| 62 | + END LOOP; |
| 63 | +
|
| 64 | + CLOSE cur1; |
| 65 | +
|
| 66 | + END; |
| 67 | + """ |
| 68 | + |
| 69 | + qt_select "select count(*) from information_schema.routines where routine_schema=\"${dbName}\";" |
| 70 | + sql """select * from information_schema.routines;""" |
| 71 | + sql """call routine_cursor_select(111, "plsql111")""" |
| 72 | + sql """call routine_cursor_select(111, "plsql333")""" |
| 73 | + sql """SHOW CREATE PROCEDURE routine_cursor_select;""" |
| 74 | + // TODO call show command before drop |
| 75 | + sql """DROP PROCEDURE routine_cursor_select""" |
| 76 | + sql """DROP PROC routine_insert""" |
| 77 | + sql "DROP DATABASE ${dbName}" |
| 78 | + // TODO call show command after drop |
| 79 | +} |
0 commit comments