mysql创建存储过程并调用和doris创建存储过程并调用的对比

一、 mysql的存储过程的创建和调用

1、创建表

-- 创建表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

2、插入数据

-- 插入数据
INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 50000.00);
INSERT INTO employees (first_name, last_name, salary) VALUES ('Jane', 'Smith', 60000.00);

3、查看数据

-- 查询数据
SELECT * FROM employees;

"1"	"John"	"Doe"	"50000.00"
"2"	"Jane"	"Smith"	"60000.00"

4、创建存储过程

-- 创建存储过程
DELIMITER $$

CREATE PROCEDURE InsertEmployee (
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_salary DECIMAL(10, 2)
)
BEGIN
    DECLARE var_message VARCHAR(255);

    -- 输出调试信息
    SET var_message = CONCAT('Inserting employee: ', p_first_name, ' ', p_last_name, ' with salary ', p_salary);
    SELECT var_message;

    -- 插入记录
    INSERT INTO employees (first_name, last_name, salary)
    VALUES (p_first_name, p_last_name, p_salary);

    -- 输出调试信息
    SET var_message = 'Employee inserted successfully';
    SELECT var_message;
END $$

DELIMITER ;

5、调用存储过程

-- 调用存储过程
CALL InsertEmployee('Alice', 'Johnson', 55000.00);

6、查看数据

-- 查询数据
SELECT * FROM employees;

"1"	"John"	"Doe"	"50000.00"
"2"	"Jane"	"Smith"	"60000.00"
"3"	"Alice"	"Johnson"	"55000.00"

二、doris的存储过程创建和调用

1、创建表

-- 创建表
CREATE TABLE IF NOT EXISTS employees (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(1024) DEFAULT NULL COMMENT 'first name',
    `last_name` VARCHAR(1024) DEFAULT NULL COMMENT 'last_name',
    `salary` DECIMAL(15,4) DEFAULT NULL COMMENT '薪水'
  )
  UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

或者
/*DDL 信息*/------------
CREATE TABLE `employees` (
  `id` bigint NOT NULL AUTO_INCREMENT(1),
  `first_name` varchar(1024) NULL COMMENT 'first name',
  `last_name` varchar(1024) NULL COMMENT 'last_name',
  `salary` decimal(15,4) NULL COMMENT '薪水'
) ENGINE=OLAP
UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

2、插入数据

-- 插入数据
INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 50000.00);
INSERT INTO employees (first_name, last_name, salary) VALUES ('Jane', 'Smith', 60000.00);

3、查看数据

-- 查询数据
SELECT * FROM employees;

"1"	"John"	"Doe"	"50000.0000"
"40641"	"Jane"	"Smith"	"60000.0000"

4、创建存储过程

-- 创建存储过程
DELIMITER $$

CREATE PROCEDURE InsertEmployee (
    IN p_first_name VARCHAR(1024),
    IN p_last_name VARCHAR(1024),
    IN p_salary DECIMAL(15, 4)
)
BEGIN
    DECLARE var_message VARCHAR(255);

    -- 输出调试信息
    SET var_message = CONCAT('Inserting employee: ', p_first_name, ' ', p_last_name, ' with salary ', p_salary);
    SELECT var_message;

    -- 插入记录
    INSERT INTO employees (first_name, last_name, salary)
    VALUES (p_first_name, p_last_name, p_salary);

    -- 输出调试信息
    SET var_message = 'Employee inserted successfully';
    SELECT v_message;
END $$

DELIMITER ;

5、调用存储过程

-- 调用存储过程
CALL InsertEmployee('Alice', 'Johnson', 55000.00);

6、查看数据

-- 查询数据
SELECT * FROM employees;

"1"	"John"	"Doe"	"50000.0000"
"40641"	"Jane"	"Smith"	"60000.0000"

7、发生了什么？问题是什么？没有数据。

发生了什么？问题是什么？没有数据。