1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Use of & and &&

Discussion in 'SQL PL/SQL' started by Evans Bett, Jul 5, 2016.

  1. Evans Bett

    Evans Bett Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    ELDORET
    Can anyone give me a simpler use of single and double amperstand in Oracle Sql
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Simpler than what? You provide no point of reference.

    Since you give no starting point we'll start at the beginning. & is used to create replaceable parameters, variables that will 'ask' for input at run time. For example:

    Code (SQL):
    SQL> SELECT empno, ename, sal
      2  FROM
      3  (SELECT empno, ename, sal, rownum rn
      4          FROM
      5  (SELECT empno, ename, sal
      6          FROM emp
      7          ORDER BY sal DESC))
      8  WHERE rn <= &1;
    Enter VALUE FOR 1: 5
    OLD   8: WHERE rn <= &1
    NEW   8: WHERE rn <= 5

         EMPNO ENAME             SAL
    ---------- ---------- ----------
          7839 KING             5000
          7902 FORD             3000
          7788 SCOTT            3000
          7566 JONES            2975
          7698 BLAKE            2850

    SQL>
    The supplied value will be used once then cleared; any subsequent calls to the same variable will ask for input:

    Code (SQL):
    SQL> SELECT empno, ename, sal
      2  FROM
      3  (SELECT empno, ename, sal,
      4          rank() OVER (ORDER BY sal DESC) rk
      5          FROM emp)
      6  WHERE rk<= &1;
    Enter VALUE FOR 1: 7
    OLD   6: WHERE rk<= &1
    NEW   6: WHERE rk<= 7

         EMPNO ENAME             SAL
    ---------- ---------- ----------
          7839 KING             5000
          7788 SCOTT            3000
          7902 FORD             3000
          7566 JONES            2975
          7698 BLAKE            2850
          7782 CLARK            2450
          7499 ALLEN            1600

    7 ROWS selected.

    SQL>
    Using the double & (&&) causes SQL*Plus to ask once for the parameter value, then it maintains that setting until it is cleared by 'undefine':

    Code (SQL):
    SQL> --
    SQL> -- Generate an age list for the girls
    SQL> --
    SQL> -- Maximum age is &&age
    SQL> --
    SQL> WITH age_list AS (
      2          SELECT rownum age
      3          FROM all_objects
      4          WHERE rownum <= &&age
      5  )
      6  SELECT *
      7  FROM age_list;
    Enter VALUE FOR age: 36
    OLD   4:        WHERE rownum <= &&age
    NEW   4:        WHERE rownum <= 36

           AGE
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11

           AGE
    ----------
            12
            13
            14
            15
            16
            17
            18
            19
            20
            21
            22

           AGE
    ----------
            23
            24
            25
            26
            27
            28
            29
            30
            31
            32
            33

           AGE
    ----------
            34
            35
            36

    36 ROWS selected.

    SQL>
    SQL> --
    SQL> -- Return only the age groupings whose product
    SQL> -- is &&age
    SQL> --
    SQL> -- Return, also, the sum of the ages
    SQL> --
    SQL> -- This restricts the set of values needed to
    SQL> -- solve the problem
    SQL> --
    SQL> WITH age_list AS (
      2          SELECT rownum age
      3          FROM all_objects
      4          WHERE rownum <= &&age
      5  ),
      6  product_check AS (
      7          SELECT
      8          age1.age AS youngest,
      9          age2.age AS middle,
    10          age3.age AS oldest,
    11          age1.age+age2.age+age3.age AS SUM,
    12          age1.age*age2.age*age3.age AS product
    13          FROM age_list age1, age_list age2, age_list age3
    14          WHERE age2.age >= age1.age
    15          AND age3.age >= age2.age
    16          AND age1.age*age2.age*age3.age = &&age
    17  )
    18  SELECT *
    19  FROM product_check
    20  ORDER BY 1,2,3;
    OLD   4:        WHERE rownum <= &&age
    NEW   4:        WHERE rownum <= 36
    OLD  16:        AND age1.age*age2.age*age3.age = &&age
    NEW  16:        AND age1.age*age2.age*age3.age = 36

      YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT
    ---------- ---------- ---------- ---------- ----------
             1          1         36         38         36
             1          2         18         21         36
             1          3         12         16         36
             1          4          9         14         36
             1          6          6         13         36
             2          2          9         13         36
             2          3          6         11         36
             3          3          4         10         36

    8 ROWS selected.

    SQL>
    SQL> --
    SQL> -- Find, amongst the acceptable values,
    SQL> -- those sets where the summed value is
    SQL> -- the same
    SQL> --
    SQL> -- This is necessary as providing the sum
    SQL> -- was of little direct help in solving the
    SQL> -- problem
    SQL> --
    SQL> WITH age_list AS (
      2          SELECT rownum age
      3          FROM all_objects
      4          WHERE rownum <= &&age
      5  ),
      6  product_check AS (
      7          SELECT
      8          age1.age AS youngest,
      9          age2.age AS middle,
    10          age3.age AS oldest,
    11          age1.age+age2.age+age3.age AS SUM,
    12          age1.age*age2.age*age3.age AS product
    13          FROM age_list age1, age_list age2, age_list age3
    14          WHERE age2.age >= age1.age
    15          AND age3.age >= age2.age
    16          AND age1.age*age2.age*age3.age = &&age
    17  ),
    18  summed_check AS (
    19          SELECT youngest, middle, oldest, SUM, product
    20          FROM (
    21                  SELECT youngest, middle, oldest, SUM, product,
    22                  COUNT(*) OVER (partition BY SUM) ct
    23                  FROM product_check
    24          )
    25          WHERE ct > 1
    26  )
    27  SELECT *
    28  FROM summed_check;
    OLD   4:        WHERE rownum <= &&age
    NEW   4:        WHERE rownum <= 36
    OLD  16:        AND age1.age*age2.age*age3.age = &&age
    NEW  16:        AND age1.age*age2.age*age3.age = 36

      YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT
    ---------- ---------- ---------- ---------- ----------
             2          2          9         13         36
             1          6          6         13         36

    SQL>
    SQL> --
    SQL> -- Return the one set of values meeting all of
    SQL> -- the criteria:
    SQL> --
    SQL> -- Product of &&age
    SQL> -- Sum of some unknown number
    SQL> -- Oldest child exists
    SQL> --
    SQL> WITH age_list AS (
      2          SELECT rownum age
      3          FROM all_objects
      4          WHERE rownum <= &&age
      5  ),
      6  product_check AS (
      7          SELECT
      8          age1.age AS youngest,
      9          age2.age AS middle,
    10          age3.age AS oldest,
    11          age1.age+age2.age+age3.age AS SUM,
    12          age1.age*age2.age*age3.age AS product
    13          FROM age_list age1, age_list age2, age_list age3
    14          WHERE age2.age >= age1.age
    15          AND age3.age >= age2.age
    16          AND age1.age*age2.age*age3.age = &&age
    17  ),
    18  summed_check AS (
    19          SELECT youngest, middle, oldest, SUM, product
    20          FROM (
    21                  SELECT youngest, middle, oldest, SUM, product,
    22                  COUNT(*) OVER (partition BY SUM) ct
    23                  FROM product_check
    24          )
    25          WHERE ct > 1
    26  )
    27  SELECT *
    28  FROM summed_check
    29  WHERE oldest > middle;
    OLD   4:        WHERE rownum <= &&age
    NEW   4:        WHERE rownum <= 36
    OLD  16:        AND age1.age*age2.age*age3.age = &&age
    NEW  16:        AND age1.age*age2.age*age3.age = 36

      YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT
    ---------- ---------- ---------- ---------- ----------
             2          2          9         13         36

    SQL>
    SQL> undefine age
    SQL>
    Notice in the above code that the age variable is asked for only once, then the assigned value is passed through the script to every place where &&age appears. This makes it easier to pass one value through an entire script. The caveat is that the variable MUST be undefined or its value will be used in any subsequent scripts that use that same variable. The & and && can be used on numeric and named variables, as shown in the examples above.
     
    Sadik likes this.
  3. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    & -- To prompt the user for a value.
    create table t1(col_name number); --
    insert into t1 values('&col_name');



    && -- If you want to reuse the variable values without prompting the user each time....
    SELECT &&col_id, col_name from t1;