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) asall_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) asstr_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;