Базы данных -> SQL -> Понимание SQL (Understanding SQL)

ЧТО ТАКОЕ - ВЛОЖЕНИЕ SQL

ЗАЧЕМ ВКЛАДЫВАТЬ SQL?

КАК ДЕЛАЮТСЯ ВЛОЖЕНИЯ SQL.

ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ОСНОВНОГО ЯЗЫКА В SQL

 
      EXEC SQL INSERT INTO Salespeople 
         VALUES ( :id_num, :salesperson, :loc, :comm) 

 
        while not end-ot-file (input) do 
           begin 
           readln (id_num, salesperson, loc, comm); 
           EXEC SOL INSERT INTO Salespeople 
              VALUES (:id_num, :salesperson, :loc, :comm); 
           end; 

 
          while not end-ot-file (input) do 
             begin 
             readln (id_num, salesperson, loc, comm); 
             if comm > = .12 then 
             EXEC SQL INSERT INTO Salespeople 
                VALUES (:id_num, :salesperson, :loc, :comm); 
             end; 

ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ

 
          EXEC SQL BEGIN DECLARE SECTION; 
          Var 
             id-num:       integer; 
             Salesperson:  packed array (1 . .10) ot char; 
             loc:            packed array (1. .10) ot char; 
             comm:         real; 
          EXEC SQL END DECLARE SECTION; 

ИЗВЛЕЧЕНИЕ ЗНАЧЕНИЙ ПЕРЕМЕННЫХ

 
             EXEC SQL SELECT snum, sname, city, comm 
               INTO :id_num, :salesperson, :loc, :comm 
               FROM Salespeople 
               WHERE snum = 1001; 

 
           EXEC SQL SELECT DISTINCT snum 
             INTO :salesnum 
             FROM Customers 
             WHERE snum = 
               (SELECT snum 
                   FROM Salespeople 
                   WHERE sname = 'Motika'); 

КУРСОР

 
                EXEC SQL DECLARE CURSOR Londonsales FOR 
                  SELECT * 
                  FROM Salespeople 
                  WHERE city = 'London'; 

 
       EXEC SQL OPEN CURSOR Londonsales; 

 
       EXEC SQL FETCH Londonsales INTO :id_num, 
        :salesperson, :loc, :comm; 

 
         Look_at_more:= True; 
            EXEC SQL OPEN CURSOR Londonsales; 
             while Look_at_more do 
               begin 
               EXEC SQL FETCH Londonsales 
               INTO :id_num, :Salesperson, :loc, :comm; 
               writeln (id_num, Salesperson, loc, comm); 
               writeln ('Do you want to see more data? (Y/N)'); 
               readln (response); 
               it response = 'N' then Look_at_more: = False 
               end; 
            EXEC SQL CLOSE CURSOR Londonsales; 

SQL КОДЫ

ИСПОЛЬЗОВАНИЕ SQLCODE ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ

 
            Look_at_more: = lhe; 
            EXEC SQL OPEN CURSOR Londonsales; 
              while Look_at_more 
              and SQLCODE = O do 
                begin 
                EXEC SQL FETCH London$ales 
                   INTO :id_num, :Salesperson, :loc, :comm; 
                writeln (id_num, Salesperson, loc, comm); 
                writeln ('Do you want to see more data? (Y/N)'); 
                readln (response); 
                If response = 'N' then Look_at_more: = Fabe; 
                end; 
            EXEC SQL CLOSE CURSOR Londonsales; 

ПРЕДЛОЖЕНИЕ WHENEVER

 
  EXEC SQL WHENEVER SQLERROR GOTO Error_handler; 
  EXEC SQL WHENEVER NOT FOUND CONTINUE; 

 
 EXEC SQL WHENEVER NOT FOUND GOTO No_rows; 

 
 EXEC SQL WHENEVER NOT FOUND CONTINUE; 

МОДИФИЦИРОВАНИЕ КУРСОРОВ

 
                 EXEC SQL DELETE FROM Customers 
                   WHERE rating < 
                      ( SELECT AVG (rating) 
                        FROM Customers); 

 
               EXEC SQL DECLARE Belowavg CURSOR FOR 
                 SELECT * 
                   FROM Customers 
                   WHERE rating < 
                      (SELECT AVG (rating) 
                        FROM Customers); 

 
            EXEC SQL WHENEVER SQLERROR GOTO Error_handler; 
             EXEC SQL OPEN CURSOR Belowavg; 
             while not SOLCODE = 100 do 
               begin 
               EXEC SOL FETCH Belowavg INTO :a, :b, :c, :d, :e; 
               EXEC SOL DELETE FROM Customers 
                 WHERE CURRENT OF Belowavg; 
               end; 
             EXEC SOL CLOSE CURSOR Belowavg; 

 
        EXEC SOL DECLARE CURSOR High_Cust AS 
           SELECT * 
              FROM Salespeople 
              WHERE snum IN 
                 (SELECT snum 
                    FROM Customers 
                    WHERE rating = 300); 

 
            EXEC SQL OPEN CURSOR High_cust; 
            while SQLCODE = 0 do 
               begin 
               EXEC SOL FETCH High_cust 
                  INTO :id_num, :salesperson, :loc, :comm; 
               EXEC SQL UPDATE Salespeople 
                  SET comm = comm + .01 
                  WHERE CURRENT OF High_cust; 
               end; 
            EXEC SQL CLOSE CURSOR High_cust; 

 
             EXEC SQL DECLARE CURSOR High_Cust AS 
                SELECT * 
                   FROM Salespeople 
                   WHERE snum IN 
                      (SELECT snum 
                          FROM Customers 
                          WHERE rating = 300) 
                 FOR UPDATE OF comm; 

 
      EXEC SQL OPEN CURSOR High_cust; 
      while SQLCODE = O do 
         begin 
         EXEC SQL FETCH High_cust 
            INTO :id_num, :salesperson, 
               :loc:i_a, :commINDlCATOR:i_b; 
         If i_a > = O and i_b > = O then 
                       {no NULLs produced} 
              EXEC SQL UPDATE Salespeople 
                 SET comm = comm + .01 
                 WHERE CURRENT OF Hlgh_cust; 
         else 
                    {one or both NULL} 
         begin 
            If i_a < O then 
                 writeln ('salesperson ', id_num, ' has no city'); 
            If i_b < O then 
                 writeln ('salesperson ', id_num, ' has no 
                 commission'); 
         end; 
                   {else} 
         end; {while} 
      EXEC SQL CLOSE CURSOR High_cust; 

 
        If sity = 'London' then 
             comm: = comm + .01 
        else comm: = comm - .01 

 
         EXEC SQL UPDATE Salespeople 
            SET comm = comm + .01 
            WHERE sity = 'London'; 

 
         EXEC SQL UPDATE Salespeople 
            SET comm = comm  .01; 
            WHERE sity < > 'London'; 

 
        If i_city > = O then 
             begin 
             If city = 'London' then 
                  comm: = comm + .01 
             else comm: = comm - .01; 
             end; 
       {begin and end нужны здесь только для понимания} 

EXEC SQL INSERT INTO Salespeople 
 VALUES (:Id_num, :salesperson, :loc:i_a, :comm:i_b);