IF/ELSE inside FOR LOOP-CodePudding

I want to implement an IF/ELSE statement inside a FOR-LOOP which will go over all column names for a specific table belonging to a given owner, when this matches a provided condition. However, how would I just return this value?

I am thinking about it in python syntax:

for val in table:
  if val == 'BRAND':
    return val

Here is what I have tried:

DECLARE
  vstmt VARCHAR2(4000);
  vresult VARCHAR2(4000);
  vcursor SYS_REFCURSOR;
BEGIN
FOR VAL IN (
SELECT column_name FROM all_tab_columns where owner like '%USER%' and table_name like '%TEST%')
LOOP
vstmt := 'IF VAL == "BRAND" '

Unsure of the syntax to return VAL in the THEN statement (still new to SQL.)

Here is a simpler example because I obviously do not know what I am doing in the above query:

BEGIN
FOR VAL IN (SELECT column_name FROM all_tab_columns where owner like '%USER%' and table_name like '%TEST%')
LOOP
IF (VAL like '%BRAND') 
THEN dbms_output.put_line(VAL);
ELSE dbms_output.put_line('No Matches Found');
END IF;
END LOOP;
END;

Returns this error:

Error report -
ORA-06550: line 4, column 5:
PLS-00306: wrong number or types of arguments in call to 'LIKE'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

CodePudding user response：

You could use:

DECLARE
  found BOOLEAN := FALSE;
BEGIN
  FOR VAL IN (
    SELECT column_name
    FROM   all_tab_columns
    WHERE  owner      LIKE '%USER%'
    AND    table_name LIKE '%TEST%'
  )
  LOOP
    IF (VAL.column_name LIKE '%BRAND%') 
    THEN
      found := TRUE;
      dbms_output.put_line(VAL.column_name);
    END IF;
  END LOOP;
  IF NOT found THEN
    dbms_output.put_line('No Matches Found');
  END IF;
END;
/

or:

DECLARE
  found BOOLEAN := FALSE;
BEGIN
  FOR VAL IN (
    SELECT column_name
    FROM   all_tab_columns
    WHERE  owner       LIKE '%USER%'
    AND    table_name  LIKE '%TEST%'
    AND    column_name LIKE '%BRAND%'
  )
  LOOP
    found := TRUE;
    dbms_output.put_line(VAL.column_name);
  END LOOP;
  IF NOT found THEN
    dbms_output.put_line('No Matches Found');
  END IF;
END;
/

But there does not appear to be a reason to use PL/SQL when you could simply use SQL:

SELECT column_name
FROM   all_tab_columns
WHERE  owner LIKE '%USER%'
AND    table_name LIKE '%TEST%'
AND    column_name LIKE '%BRAND%';

fiddle

CodePudding user response：

It is unclear (at least, to me) what final result you want, but - here's an example which might get you started.

It loops through tables (whose name contains "DEP") in my schema (why not all_tab_columns You restricted owner to USER, and that's currently logged user so - why not using user_tab_columns instead?), scanning all columns for string "Sales":

if it is found, number of occurrences is displayed
if not, it says so
if there's an error (for example, because you're searching NUMBER datatype columns for a string), it is also displayed

Can it be improved? Certainly, only if we know what you wanted.

SQL> set serveroutput on
SQL> declare
  2    l_str varchar2(200);
  3    l_cnt number;
  4  begin
  5    for cur_r in (select table_name, column_name
  6                  from user_tab_columns
  7                  where table_name like '