如何使用oracle包删除Global Temp表
我有一个如下所示的示例查询：
INSERT INTO my_gtt_1 (fname, lname) (select fname, lname from users)
在努力摆脱临时表的过程中，我创建了一个包：
create or replace package fname_lname AS
Type fname_lname_rec_type is record (
fname varchar(10),
lname varchar(10)
);
fname_lname_rec fname_lname_rec_type
Type fname_lname_tbl_type is table of fname_lname_rec_type;
function fname_lname_func
(
v_fnam in varchar2,
v_lname in varchar2
)return fname_lname_tbl_type pipelined;
对于 Oracle 来说是新手...创建这个包花了很长时间。但现在我不知道如何摆脱 my_gtt_1
我该怎么说......
INSERT INTO <newly created package> (select fnma, name from users)
I have a sample query like below:
INSERT INTO my_gtt_1 (fname, lname) (select fname, lname from users)
In my effort to getting rid of temporary tables I created a package:
create or replace package fname_lname AS
Type fname_lname_rec_type is record (
fname varchar(10),
lname varchar(10)
);
fname_lname_rec fname_lname_rec_type
Type fname_lname_tbl_type is table of fname_lname_rec_type;
function fname_lname_func
(
v_fnam in varchar2,
v_lname in varchar2
)return fname_lname_tbl_type pipelined;
being new to oracle...creating this package took a long time. but now I can not figure out how to get rid of the my_gtt_1
how can i say...
INSERT INTO <newly created package> (select fnma, name from users)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要使用 TABLE() 语法调用管道函数：
请注意，USERS 上的子查询必须返回该表中的一行。
You need to call the pipelined function using the TABLE() syntax:
Note that the sub-query on USERS must return a single row from that table.
您不选择包。如果您打算在代码中使用它，您可以声明一个表类型的变量并批量收集到该变量中。我还质疑您对流水线功能的需求。如果您只是使用全局临时表作为另一个查询的跳板，您可能可以只使用WITH 子句。我们需要更好地了解全局来推荐特定的技术。全局临时表本质上也不是坏事。
You don't select into packages. You could declare a variable of your table type and bulk collect into that, if you intend to use it in code. I also question your need for a pipelined function. If you're just using the global-temporary table as a springboard for another query, you could probably just use a WITH clause instead. We need a better idea of the bigger picture to recommend a particular technique. Global temporary tables are not inherently bad, either.