OraclePLSQL--INTRO OF DECLARE VARIABLES PART#01 -

PL/SQL program

INTRO OF DECLARE VARIABLES PART#01

The use of variables in a PL/SQL program is usually same types we have already learned about in the SQL language. The important features of Oracle Database 11g is the integration of the SQL and PL/SQL engines into one system. From Oracle9 i forward we can run the same commands in PL/SQL that we use in SQL.

To assign a value to a variable in the declaration section, we would use the following format:

Variable_name      variable_type      := value;

DECLARE

   NewEmpSalary NUMBER(6) := 25000;

…

BEGIN

…

END;

DECLARE

     EndTheLoop      BOOLEAN := FALSE;

…

BEGIN

…

END;

DECLARE

  DefaultDNO   NUMBER(2) NOT NULL := 10;

…

BEGIN

…

END;

DECLARE

Date_variable      date;

…

BEGIN

…

END;

DECLARE

   EmpLname     VARCHAR2(20);

   EmpSalary    NUMBER(6);

   EmpBdate     DATE;

BEGIN

…

END;

Declare

      V_initail_date   date;

Begin

         V_initail_date := '18-JAN-1980';

         dbms_output.put_line (V_initail_date); --show date

End;

anonymous block completed

18-JAN-80

Declare

      V_initail_date   date;

Begin

         V_initail_date := SYSDATE;

         dbms_output.put_line (V_initail_date); --show date

End;

anonymous block completed

22-JUN-15

Declare

       V_initail_number   NUMBER;

Begin

         V_initail_number  := 100000;

         dbms_output.put_line (V_initail_number );

End;

anonymous block completed

100000

Declare

       V_name   VARCHAR2(20);

Begin

         V_name := 'ALI';

         dbms_output.put_line (V_name); --show date

End;

anonymous block completed

ALI

declare

    v_country_name varchar2(80);

 begin

    select COUNTRY_NAME

    into v_country_name

    from  COUNTRIES

     where rownum = 1;

    dbms_output.put_line(v_country_name);

  

    end;

anonymous block completed

United States of America

DECLARE

      v_whom VARCHAR2(30);

    BEGIN

      v_whom := '&input';

      dbms_output.put_line('Hello '|| v_whom ||'.');

   END;

Inputàali

anonymous block completed

Hello ali.

DECLARE

      v_hello VARCHAR2(5) := 'Hello';

      v_whom VARCHAR2(30);

    BEGIN

      v_whom := '&input';

      dbms_output.put_line(v_hello ||' '|| v_whom ||'.');

    END;

anonymous block completed

Hello ali.

DECLARE

      v_whom VARCHAR2(30);

    BEGIN

      v_whom := :bv;

      dbms_output.put_line('Hello '|| v_whom ||'.');

    END;

anonymous block completed

Hello ali.

Procedure Blocks

CREATE OR REPLACE PROCEDURE hello_country

    ( c_country VARCHAR2 ) IS

    BEGIN

      dbms_output.put_line('Hello '||c_country||'.');

       END;

PROCEDURE HELLO_COUNTRY compiled

EXECUTE hello_procedure('USA');

anonymous block completed

Hello USA.

Function Blocks

CREATE OR REPLACE FUNCTION hello_function

( c_country VARCHAR2 ) RETURN VARCHAR2 IS

BEGIN

  RETURN 'Hello '||c_country||'.';

END;

FUNCTION HELLO_FUNCTION compiled

CALL hello_function('USA') INTO :result;

 PRINT :result

RESULT

---

USA

SELECT :result FROM dual;

":RESULT"  .....IN TABLE

"USA"

DECLARE

      PROCEDURE a;

      FUNCTION b RETURN VARCHAR2;

      PROCEDURE a IS

      BEGIN

        dbms_output.put_line(b||' World!');

      END a;

      FUNCTION b RETURN VARCHAR2 IS

      BEGIN

       RETURN 'Hello';

     END b;

   BEGIN

     a;

   END;

anonymous block completed

Hello World!