使用光标插入和更新表

发布于 2025-02-02 23:45:41 字数 878 浏览 3 评论 0原文

我正在尝试将记录插入

来自信息_schema的表输入：

select table_schema,table_name,table_type
from information_schema.tables
where table_schema = 'MYSCHEMA';

预期输出：

与table_name的ddl一起插入新表格要获得DDL：选择get_ddl（'table'，'invoicing'）

您可以帮助我吗？

create or replace procedure proc_getddl
is
v_tableschema varchar(30);
v_tablename varchar(30);
v_tabletype varchar(30);
v_getddl varchar(110);

cursor getddl is 
select table_schema,table_name,table_type,get_ddl('table','INVOICING')
from information_schema.tables
where table_schema = 'MYSCHEMA';
begin
open getddl;
LOOP
fetch getddl into v_tableschema,v_tablename,v_tabletype,v_getddl;
EXIT WHEN getddl%NOTFOUND;
INSERT INTO backup_table
values (v_tableschema,v_tablename,v_tabletype,v_getddl);

END LOOP;
close getddl;
end proc_getddl;

我可以使用此功能，但我希望它在信息架构中执行所有表

原文

I'm trying to insert records to table

input from information_schema :

select table_schema,table_name,table_type
from information_schema.tables
where table_schema = 'MYSCHEMA';

expected output:

insert into new table along with DDL of table_name
to get DDL: select get_ddl('table','INVOICING')

Can you help me?

create or replace procedure proc_getddl
is
v_tableschema varchar(30);
v_tablename varchar(30);
v_tabletype varchar(30);
v_getddl varchar(110);

cursor getddl is 
select table_schema,table_name,table_type,get_ddl('table','INVOICING')
from information_schema.tables
where table_schema = 'MYSCHEMA';
begin
open getddl;
LOOP
fetch getddl into v_tableschema,v_tablename,v_tabletype,v_getddl;
EXIT WHEN getddl%NOTFOUND;
INSERT INTO backup_table
values (v_tableschema,v_tablename,v_tabletype,v_getddl);

END LOOP;
close getddl;
end proc_getddl;

I can use this but I want it to execute for all tables in information schema

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随心而道 2025-02-09 23:45:41

类似此类尝试

create table backup_table as 

select table_schema,table_name,table_type,get_ddl('table','INVOICING')
from information_schema.tables
where table_schema = 'MYSCHEMA';

根据此 https：//sparkbyexamples.com/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/snowflake/ -greate-table-as-select/

Try something like

create table backup_table as 

select table_schema,table_name,table_type,get_ddl('table','INVOICING')
from information_schema.tables
where table_schema = 'MYSCHEMA';

According to this https://sparkbyexamples.com/snowflake/snowflake-create-table-as-select/

回复收藏 0 原文

~没有更多了~