MySQL/Oracle 存储过程 表名互换

MySQL存储过程

代码：

# 自定义结束符号，为 '$$'
delimiter $$
create procedure rename_table(in v_table_name varchar(255), in v_tmp_table_name varchar(255))
begin
	# 定义变量
    declare temp_table_name varchar(255);
    # 给变量赋值
	set temp_table_name = CONCAT(v_table_name, '_',  date_format(current_date, '%Y%m%d'));
		
	#定义语句
    set @sql_str = concat('alter table ',v_table_name, ' rename to ', temp_table_name, ';');
	#预定义sql语句，从用户变量中获取
    prepare sql_str from @sql_str;
	#执行sql语句
    execute sql_str;
    #deallocate prepare sql_str;
		
	set @sql_str = concat('alter table ',v_tmp_table_name, ' rename to ', v_table_name, ';');
    prepare sql_str from @sql_str;
    execute sql_str;
    #deallocate prepare sql_str;
		
	set @sql_str = concat('alter table ',temp_table_name, ' rename to ', v_tmp_table_name, ';');
    prepare sql_str from @sql_str;
    execute sql_str;
	#释放资源
    deallocate prepare sql_str;

end
$$
# 将初始修改的结束符号恢复为，';'
delimiter ;

注意点：

• 为什么要自定义结束符号呢？因为 MySQL 默认的结束符号为 ';'，遇到 ';' 就会执行语句， 而存储过程中是由多条 sql 语句组合而成的，每条语句之间都是以 ';' 分割的，如果我们没有声明分隔符的话，MySQL 会将存储过程当成 sql 语句来执行，则存储过程的编译过程会报错，所以我们要事先修改 MySQL 默认的结束符号，来使我们可以正确执行创建存储过程的语句，用完之后还要把分隔符还原。(注意：以上所说都是在使用命令行的方式创建存储过程时)

• MySQL默认不支持表名作为变量名。

若有存储过程如下：

drop procedure if exists selectByTableName;
create procedure selectByTableName(in tableName varchar(50))
begin
    select * from tableName;
end;

在调用存储过程时会报错：

call selectByTableName('emp');
> 1146 - Table 'db_name.tablename' doesn't exist
> 时间: 0s

原因是它把变量 tableName 作为了表名，并不是把传入的值作为表名。
如果要解决上述问题的话，可以使用 concat 函数，然后用预处理语句出入动态表名来执行 sql，对于增删改查都适用。

将上述存储过程修改为：

drop procedure if exists selectByTableName;
create procedure selectByTableName(in tableName varchar(50))
begin
    #定义语句
    set @sql_str = concat('select * from ',tableName);
    #预定义sql语句，从用户变量中获取
    prepare sql_str from @sql_str ;
    #执行sql语句
    execute sql_str ;
    #释放资源，后续还可以使用
    deallocate prepare sql_str;
end;

再调用时就能正常查询出结果了。在预处理语句中，使用了用户变量，变量名是自定义的。

创建存储过程格式：

delimiter 自定义结束符号
create procedure sp_name([in, out, inout] 参数名 数据类型...)
begin
	# 定义变量
	# declare variable_name [,variable_name...] datatype [default value];
    sql语句;
end
自定义结束符号
delimiter ;

调用存储过程：

call sp_name([参数|@参数]);
--@参数：可用来接收存储过程输出结果

删除存储过程：

drop procedure sp_name;

Oracle存储过程

代码

CREATE OR REPLACE PROCEDURE rename_table (
    v_table_name in VARCHAR2, --test_table
    v_tmp_table_name in VARCHAR2 --test_table_temp
)
AS
  temp_table_name VARCHAR2(100);
BEGIN

  temp_table_name := v_table_name || '_' || TO_CHAR(SYSDATE, 'yyyyMMdd');
  EXECUTE IMMEDIATE 'ALTER TABLE maleon.'||v_table_name||' RENAME TO '||temp_table_name; --test_table_yyyyMMdd
  EXECUTE IMMEDIATE 'ALTER TABLE maleon.'||v_tmp_table_name||' RENAME TO '||v_table_name; --test_table
  EXECUTE IMMEDIATE 'ALTER TABLE maleon.'||temp_table_name||' RENAME TO '||v_tmp_table_name; --test_table_temp

END;
/

参考文章：
https://www.cnblogs.com/zys2019/p/15171722.html