Techno-Functional World : PLSQL syntax -Part1

 
DECLARE -- Declarative part (optional)
-- Declarations of local types, variables, & subprograms
BEGIN -- Executable part (required)
-- Statements (which can use items declared in declarative part)
EXCEPTION -- Exception-handling part (optional)
-- Exception handlers for exceptions raised in executable part]
END;

Example ; Back to Contents

2) %TYPE Attribute

v_column_name  table_name.column_name%TYPE;

Example ; Back to Contents

<

3) %ROWTYPE Attribute

 
v_row_name  table_name%ROWTYPE;
You use dot(.) notation for field reference.
v_column_name :=v_row_name.column_name;

Example ; Back to Contents

4) Simple IF-THEN Statement

IF condition THEN
--Statements…
END;

Example ; Back to Contents

5) IF-THEN-ELSE statements

IF condition THEN
--Statements…
ELSE
--Statements…
END;

Example ; Back to Contents

6) Nested IF-THEN-ELSE statement

IF condition1 THEN
--Statements…
ELSE
IF condition2 THEN
--Statements…
ELSE
--Statements…
END IF; --End of inner IF
END IF; --END of outer IF

Example ; Back to Contents

7) IF-THEN-ELSIF

IF condition THEN
--Statements…
ELSIF condition THEN
--Statements…
ELSE
--Statements…
END IF;

Example ; Back to Contents

8) Basic Loop

LOOP
--Sequence of statements
END LOOP;

Example ; Back to Contents

9) EXIT statement

LOOP
--Sequence of statements
IF condition THEN
EXIT;
END IF
--Sequence of statements
END LOOP;
--After EXIT, control resumes here

Example ; Back to Contents

10) EXIT-WHEN Statement

LOOP
--Sequence of statements
EXIT WHEN condition;
END LOOP;

Example ; Back to Contents

11) Using the CONTINUE Statement

 LOOP -- After CONTINUE statement, control resumes here
      IF condition THEN
      CONTINUE;
      END IF;
      EXIT WHEN condition;
 END LOOP;

Example ; Back to Contents

12) Using CONTINUE-WHEN Statement

LOOP -- After CONTINUE statement, control resumes here
 CONTINUE WHEN condition;
 EXIT WHEN condition;
END LOOP;

Example ; Back to Contents

13) Labelling PLSQL Loop

BEGIN
 <<outer_loop>>
  LOOP
  --Statements
       <<inner_loop>>
         LOOP
          --statements
         EXIT inner_loop WHEN condition;
         EXIT outer_loop WHEN condition;
         END LOOP inner_loop;
  END LOOP outer_loop;
--Statemetns
END;

Example ; Back to Contents

14) Using WHILE-LOOP Statements

WHILE condition LOOP
--sequence_of_statements
END LOOP;

Example ; Back to Contents

15) Simple FOR-LOOP Statements

BEGIN
   FOR var IN lower_bound..upper_bound LOOP
   --Sequence of statements
   END LOOP;
 END;

Example ; Back to Contents

16) REVERSE FOR-LOOP Statement

BEGIN
 FOR var IN REVERSE lower_bound..upper_bound LOOP
  --Sequence of statements
 END LOOP;
END;

Example ; Back to Contents

17) Using the GOTO Statement

 BEGIN
   FOR var INlower_bound..upper_bound LOOP
  --Sequence of statements
     IF condition THEN
       GOTO label_name;
     END IF;
   END LOOP;
 <<labale_name>>
    --Sequence of statements
 END;

Example ; Back to Contents

18) Using the NULL statements

BEGIN
  IF condition THEN
 --Sequence of statements
  ELSE
   NULL;
  END IF;
END;

Example ; Back to Contents

19) Create Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

Example ; Back to Contents

20) Create Function

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
IS | AS
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [function_name];

Example ; Back to Contents

1) PLSQL Block

DECLARE
 l_name VARCHAR2(10):='RG Hegde';
BEGIN
  DBMS_OUTPUT.PUT_LINE('I am '||l_name);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

2) %TYPE Attribute

DECLARE
 l_name fnd_user.user_name%TYPE :='RG Hegde';
BEGIN
  DBMS_OUTPUT.PUT_LINE('I am '||l_name);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

3) %ROWTYPE Attribute

DECLARE
 l_row_type fnd_user%ROWTYPE ;
BEGIN
SELECT user_id,user_name 
INTO l_row_type.user_id,l_row_type.user_name 
FROM fnd_user WHERE user_name='RHEGDE';
  DBMS_OUTPUT.PUT_LINE('I am '||l_row_type.user_name);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

4) Simple IF-THEN Statement

DECLARE
l_var_a  number:=5;
l_var_b  number:=10;
BEGIN
  IF l_var_a <l_var_b THEN
  DBMS_OUTPUT.PUT_LINE(l_var_a||' is less than '||l_var_b);
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

5) IF-THEN-ELSE statements

DECLARE
l_var_a  number:=10;
l_var_b  number:=5;
BEGIN
  IF l_var_a <l_var_b THEN
  DBMS_OUTPUT.PUT_LINE(l_var_a||' is less than '||l_var_b);
  ELSE
  DBMS_OUTPUT.PUT_LINE(l_var_a||' is greater than '||l_var_b);
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

6) Nested IF-THEN-ELSE statement

DECLARE
l_var_a  number:=5;
l_var_b  number:=15;
l_var_c number :=5;
BEGIN
  IF l_var_a <l_var_b  AND l_var_a <l_var_c THEN
  DBMS_OUTPUT.PUT_LINE(l_var_a||' is less than' ||l_var_b ||'and'||l_var_c );
  ELSE
  IF l_var_b<l_var_c and l_var_b<l_var_a THEN
  DBMS_OUTPUT.PUT_LINE(l_var_b||'is less than '||l_var_c ||'and'||l_var_c );
  ELSE
  DBMS_OUTPUT.PUT_LINE(l_var_c||' is less than or equal to  '||l_var_a||'and'||l_var_b);
  END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

7) IF-THEN-ELSIF

DECLARE
l_var_a  number:=5;
l_var_b  number:=15;
l_var_c number :=5;
BEGIN
  IF l_var_a <l_var_b  AND l_var_a <l_var_c THEN
  DBMS_OUTPUT.PUT_LINE(l_var_a||' is less than' ||l_var_b ||'and'||l_var_c );
  ELSIF l_var_b<l_var_c and l_var_b<l_var_a THEN
  DBMS_OUTPUT.PUT_LINE(l_var_b||'is less than '||l_var_c ||'and'||l_var_c );
  ELSE
  DBMS_OUTPUT.PUT_LINE(l_var_c||' is less than or equal to  '||l_var_a||'and'||l_var_b);
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

; Back to Contents

8) Basic Loop

DECLARE
l_count number:=1;
BEGIN
LOOP 
DBMS_OUTPUT.PUT_LINE('Loop :'||l_count);
l_count:=l_count+1;
IF l_count=11 THEN
EXIT;
END IF;
END LOOP;
END;

; Back to Contents

9) EXIT statement

DECLARE
l_count number:=1;
BEGIN
LOOP 
DBMS_OUTPUT.PUT_LINE('Loop :'||l_count);
l_count:=l_count+1;
IF l_count=11 THEN
EXIT;
END IF;
END LOOP;
END;

; Back to Contents

10) EXIT-WHEN Statement

DECLARE
l_count number:=1;
BEGIN
LOOP 
DBMS_OUTPUT.PUT_LINE('Loop :'||l_count);
l_count:=l_count+1;
EXIT WHEN l_count=11;
END LOOP;
END;

; Back to Contents

11) Using the CONTINUE Statement

DECLARE
l_count number:=1;
BEGIN
LOOP 

IF l_count=5 THEN
l_count:=l_count+1;
CONTINUE;
END IF;
EXIT WHEN l_count=11;
DBMS_OUTPUT.PUT_LINE('Loop :'||l_count);
l_count:=l_count+1;
END LOOP;
END;

; Back to Contents

12) Using CONTINUE-WHEN Statement

DECLARE
l_count number:=0;
BEGIN
LOOP 

l_count:=l_count+1;
CONTINUE when  l_count=5 ;
EXIT WHEN l_count=11;
DBMS_OUTPUT.PUT_LINE('Loop :'||l_count);

END LOOP;
END;

; Back to Contents

13) Labelling PLSQL Loop

DECLARE
l_outer  number:=1;
l_inner  number:=0;
BEGIN
  <<outerloop>>
    LOOP 
     DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' ||l_outer);
            l_outer:=l_outer+1;
            l_inner:=1;
            <<innerloop>>
              LOOP
               DBMS_OUTPUT.PUT_LINE(' Inner Loop counter is ' ||l_inner);
              EXIT  innerloop WHEN  (l_inner>4); 
               l_inner:=l_inner+1;                 
              END LOOP innerloop;
     EXIT  outerloop WHEN  (l_outer>3);
     END LOOP outerloop;
END;

; Back to Contents

14) Using WHILE-LOOP Statements

DECLARE
l_count number:=1;
BEGIN

WHILE l_count<=5 LOOP
DBMS_OUTPUT.PUT_LINE('Count :'||l_count);
l_count:=l_count+1;
END LOOP;
END;

; Back to Contents

15) Simple FOR-LOOP Statements

BEGIN
   FOR i IN 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE('Loop count: '||i);
   END LOOP;
END;

; Back to Contents

16) REVERSE FOR-LOOP Statement

BEGIN
   FOR i IN REVERSE 1..5  LOOP
  DBMS_OUTPUT.PUT_LINE('Loop count: '||i);
   END LOOP;
 END;

; Back to Contents

17) Using the GOTO Statement

BEGIN
    FOR i  IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE('Loop count : '||i);
     IF i=5 THEN
       GOTO goto_block;
     END IF;
   END LOOP;
 <<goto_block>>
  DBMS_OUTPUT.PUT_LINE('Control comes to go to block : ');
 END;

; Back to Contents

18) Using the NULL statements

DECLARE 
l_var number:=5;
BEGIN
  IF l_var !=10 THEN
 DBMS_OUTPUT.PUT_LINE('process...');
  ELSE
   NULL;
  END IF;
END;

; Back to Contents

19) Create Procedure

--Procedure Creation
CREATE OR REPLACE PROCEDURE test_procedure
IS
BEGIN
 DBMS_OUTPUT.PUT_LINE('You have called a procedure');
END test_procedure;

--Procedure calling
BEGIN
test_procedure;
END;

; Back to Contents

20) Create Function

--Function Creation
CREATE OR REPLACE FUNCTION test_function RETURN NUMBER
IS 
l_var_a NUMBER:=10;
l_var_b NUMBER:=20;
BEGIN
 return(l_var_a+l_var_b);
END test_function;

--Function calling
DECLARE 
l_result NUMBER;
BEGIN
l_result:=test_function;
DBMS_OUTPUT.PUT_LINE('Sum of 2 number is'||l_result);
END;

; Back to Contents

PLSQL syntax -Part1

No comments:

Popular Posts