Skip to content

Commit 2f84908

Browse files
authored
[enhancement](plsql) regression for routine select and show create procedure (#33608)
add regression for routines and show create procedure Issue Number: close #31297 add regression for routines and show create procedure
1 parent 7258692 commit 2f84908

2 files changed

Lines changed: 86 additions & 0 deletions

File tree

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !select --
3+
777 4
4+
5+
-- !select --
6+
2
7+
Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
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

Comments
 (0)