SQL - looping with distinct names

I am new to SQL. I want to loop through my code with each distinct name from a table and I want to use that name in 开发者_JS百科the rest of the program, so I would like that name in a variable. Can anyone give any hints on how to accomplish this? My idea was to store all the names from the table into an array (but i haven't been able to find how to make an array) or a list and loop through those names. Any help is appreciated.

I'm continuing the work off of someone elses code, this may be pl/sql but heres some code just to give an idea of where im going

    CURSOR def_stud_pglts is
        select portal_objname_pgt, portal_row_num, portal_col_num, portal_objname
          from psprsmhpasgpglt
          where portal_name='EMPLOYEE' and portal_objname = 'NRPA_ACADEMICS'
          and portal_layoutbehav in ('2REQ', '3DEF');

        cursor name_cur is
        select distinct oprid from PSPRUHTABPGLT
            where portal_objname = 'NRPA_ACADEMICS';

       namerow name_cur%ROWTYPE;
       pageletRow def_stud_pglts%ROWTYPE;
       v_oprid VARCHAR2(30);
       tmp tmpcur;
       v_students NUMBER;
       v_min NUMBER;

       BEGIN

   select count(distinct oprid) 
    into v_students
    from (select distinct oprid from PSPRUHTABPGLT
    where portal_objname = 'NRPA_ACADEMICS');

     FOR pageletRow IN def_stud_pglts LOOP
    OPEN tmp FOR select count(oprid) from pspruhtabpglt 
   where portal_objname_pgt = pageletRow.portal_objname_pgt
     and portal_objname = 'NRPA_ACADEMICS'
   and portal_minimize=1;
    FETCH tmp INTO v_min;
    CLOSE tmp;

      INSERT INTO prtlpgltreport 
      VALUES (pageletRow.portal_objname_pgt, 
       'DEFAULT',NULL,v_min, v_clo, v_mov, pageletRow.portal_objname); 
  END LOOP;

So this is all working code. And i want to loop this while changing where it says 'NRPA_ACADEMICS' to other names in my table

---

AFAIK, what you're asking isn't possible using plain SQL. It can be done using procedural variants of SQL some databases provide. For example, Oracle has PL SQL. (Look at similar languages for other databases).

If you want to use a programming language to do this, then how this is done depends on the language. It boils down to this:

1. write an SQL query that will fetch you your results and execute it
2. iterate over results in any way you please, putting some results in variables
3. do what you want with your variables

---

I assume you are working solely in a database - not in an application. You can achieve your requirement in a database using a cursor or a loop; the database essentially iterates over a selection and you can write a control block which will execute on each iteration.

I don't know Oracle syntax but this link describes the basics: http://www.techonthenet.com/oracle/cursors/declare.php

Before you go and do that, consider that most people avoid cursors and loops whenever possible, some ferevently refuse to use them altogether, and they are bad practice unless required. If you can possibly achieve your outcome using set-based operations (i.e. all the records at once, rather than looping), then that is the preferred path.

As they say, the vast majority of tasks can be completed without using cursors. You can apply functions and temporary tables; there are a variety of other tools available. What exactly do you need to do for each 'variable'?

---

Not quite sure what "rest of the program" means, but I'll assume you mean a stored procedure. Personally, I don't recommend the use of cursors but since I don't know the scope of the question, I'll just answer it as directly as I can.

Here's how to loop through SQL table results:

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

---

Thank you for everyones reply, it helped me understand sql much more. I was able to solve the problem by the following modifications to the code.

  FOR pageletRow IN def_stud_pglts LOOP
 v_tab := pageletRow.portal_objname;
   select count(distinct oprid) 
     into v_students
     from (select distinct oprid from PSPRUHTABPGLT 
     where portal_objname = v_tab);
OPEN tmp FOR select count(oprid) from pspruhtabpglt 
     where portal_objname_pgt = pageletRow.portal_objname_pgt
 and portal_objname = v_tab
     and portal_minimize=1;
FETCH tmp INTO v_min;
CLOSE tmp;

so I moved the count of "oprids" inside the loop and put the line v_tab := pageletRow.portal_objname; to store the current name of the loop. Hopefully this may help anyone else with this problem