I have a SQL sentence where I make a minus of two tables to search the differences. As I use frequently I would like to create a function or procedure to make these and get output by screen. Someone could explain me how is the best way to make these, could you put me some example?
-
Is there a reason for not putting this query into a view?Peter Lang– Peter Lang2015-04-07 09:07:13 +00:00Commented Apr 7, 2015 at 9:07
Add a comment
|
2 Answers
If you frequently use the MINUS query, then it is better to create a view on the query. To fetch the resultset, you just need to select from the view.
For example,
CREATE OR REPLACE VIEW my_view AS
SELECT column_list FROM table1
MINUS
SELECT column_list FROM table2
And to fetch the result,
SELECT * FROM my_view;
Read the documentation for more details on CREATE VIEW
2 Comments
Luis perez
Thanks. I'm going to use views for the minus sql statement But I would like to create a function or procedure to can see these outputs with dbms_output. Cold you suggest me how could I do these? Thanks
Lalit Kumar B
@user104906 If you really want to do it in PL/SQL, then simply use
OPEN CURSOR FOR your_query and return a ref cursor. For example, see this stackoverflow.com/a/27812983/3989608Maybe this is the what you're looking for, if you're using Oracle 11g Release 2:
create or replace procedure prnt_my_view(my_view in varchar2, separator in varchar2 default ',') is
type myrefcur is ref cursor;
type rowtext is table of varchar2(256);
rowdef varchar2(256);
rows_cv myrefcur;
text rowtext;
begin
select listagg(column_name,'||'''||separator||'''||') within group (order by column_id) into rowdef from user_tab_columns where lower(table_name) = lower(my_view);
open rows_cv for 'select '||rowdef||' from '||my_view;
fetch rows_cv bulk collect into text;
for i in text.first..text.last loop
dbms_output.put_line(text(i));
end loop;
close rows_cv;
exception when others then
dbms_output.put_line('something is wrong:'||sqlerrm);
end;
edit: if you can't use listagg, check other solutions for example here: alternative to listagg in Oracle?
