In Oracle PL and SQL the method by which a table name changes dynamically in a cursor declaration

  • 2020-06-12 10:51:48
  • OfStack

/*
My younger brother just contacted ORACLE stored procedure and asked for advice on one question. My younger brother wrote a stored procedure with the purpose of receiving one parameter as the table name and then querying the contents of one field of all records in the table and importing it into another table.
(
tabname in varchar
)
is
v_servicesname tabname. Service type %type; This variable is used to hold the contents of the field to be obtained, but how to define it
cursor curSort1 is select Service type from tabname order by code; This statement also does not indicate that the table name cannot be found

begin
.....
end getservicesname1;
An example:

create or replace procedure cal(tb varchar2) is
id pls_integer;
total pls_integer := 0;
type emp_cur is ref cursor;
cur emp_cur;
begin
open cur for 'select employee_id from ' || tb;
loop
fetch cur into id;
exit when cur%notfound;

total := total + id;
end loop;
close cur;

dbms_output.put_line(total)
end;*/

Related articles: