CREATE OR REPLACE PROCEDURE analysizTables (
year VARCHAR ,
owner VARCHAR,
MSG OUT VARCHAR2) is
cursor c_sqls is SELECT 'analyze table ' || TABLE_NAME || ' compute statistics' AS s FROM all_tables WHERE owner = owner AND TABLE_NAME LIKE '%_' || year || '%'; //循环执行sql结果
BEGIN
for item in c_sqls loop //开始循环
EXECUTE IMMEDIATE item.s; //执行循环当前值
MSG := MSG || item.s; //记录当前执行语句
end loop;
END analysizTables;
--执行存储过程
BEGIN
DECLARE
V_RESULT VARCHAR2(32767); --定义一个和返回结果相同的参数类型
BEGIN
analysizTables(
'2022','XJB',V_RESULT); --调用存储过程
DBMS_OUTPUT.PUT_LINE(V_RESULT); --把返回结果打印到输出区域
END;
END;
存储过程创建和执行
最新推荐文章于 2024-07-12 14:26:59 发布