oracle存储过程(procedure)

需要同时rename或drop多个table，在excel中拼sql比较麻烦，用procedure方便。

0 创建/执行/删除procedure

create or replace procedure procedure_name as ......

set serveroutput on;    --打印输出
exec precedure_name;    --执行存储过程

drop procedure procedure_name;

1 同时获取多个table的行数

create or replace procedure get_table_rownum(str_table_name in varchar2) 
as
all_row_num number;
row_num number;
str_sql varchar2(100);
cursor csr_get_num is select table_name from user_tables where table_name like str_table_name;
begin
    all_row_num := 0;
    for dr in csr_get_num loop
        str_sql := 'select count(*) from ' || dr.table_name;
        execute immediate str_sql into row_num;
        all_row_num := all_row_num + row_num;
        dbms_output.put_line(dr.table_name || row_num);
    end loop;
    dbms_output.put_line('共计' || all_row_num);
end get_table_rownum;

2 同时rename多个table

create or replace procedure renames_tables(str_table_name in varchar2, str_suffix in varchar2) 
as
str_sql varchar2(100);
cursor csr_get_table_name is select table_name from user_tables where table_name like str_table_name;
begin
    for dr in csr_get_table_name loop
        str_sql := 'alter table ' || dr.table_name || ' rename to ' || dr.table_name || str_suffix;
        execute immediate str_sql;
        dbms_output.put_line(dr.table_name || ' 已重命名为 ' || dr.table_name || str_suffix);
    end loop;
end renames_tables;