Pl-sql blocks and block types and variablesdeclaring.pptx

Pl/sql very help for you to start pl/sql language, it contains all the basics you need as a new learner.

1. PL/SQL Block , Declaring variables ,Flow of Control and Exception Handling Part 1

2. About PL/SQL PL/SQL: Stands for “Procedural Language” extension to SQL. Is Oracle Corporation's standard data access language for relational databases Seamlessly integrates procedural constructs with SQL

3. About PL/SQL Provides a block structure for executable units of code. Maintenance of code is made easier with such a well- defined structure. Provides procedural constructs such as: Variables, constants, and data types Control structures such as conditional statements and loops Reusable program units that are written once and executed many times

4. Benefits of PL/SQL Benefits of PL/SQL Integration of procedural constructs with SQL Improved performance Exception handling

5. PL/SQL Block

6. PL/SQL Block Structure DECLARE (optional) - Variables, cursors, user-defined exceptions BEGIN (mandatory) - SQL statements - PL/SQL statements EXCEPTION (optional) - Actions to perform when exceptions occur END; (mandatory)

7. Block Types Anonymous Procedure Function Anonymous [Declare] Begin --Statements [Exception] End Procedure PROCEDUER name IS Begin --Statements [Exception] End Function FUNCTION name RETURN Begin -- Statements RETURN value [Exception] End

8. Examining an Anonymous Block SET SERVEROUTPUT ON; DECLARE v_hello VARCHAR2(20):= 'Hello World!'; BEGIN dbms_output.put_line ( v_hello ); END;

9. Enabling Output of a PL/SQL Block To enable output in SQL Developer, execute the following command before running the PL/SQL block : SET SERVEROUTPUT ON; Use a predefined Oracle package and its procedure in the anonymous block : DBMS_OUTPUT.PUT_LINE(); Ex: DBMS_OUTPUT.PUT_LINE('The First Name of the Employee is' || v_fname );

10. Declaring PL/SQL Variables

11. Use of Variables Variables can be used for : Temporary storage of data Manipulation of stored values Reusability

12. Requirements for Variable Names A variable name : Must start with a letter Can include letters or numbers Can include special characters (such as $,_, and #) Must contain no more than 30 characters Must not include reserved words

13. Declaring and Initializing PL/SQL Variables Syntax: Var_Name DataType ; Var_Name DataType := value; Var_Name DataType Default value; Var_Name DataType NOT Null =: value; Var_Name CONSTANT DataType := value;

14. Guidelines for Declaring and Initializing PL/SQL Variables Follow consistent naming conventions. Use meaningful identifiers for variables . Initialize variables that are designated as NOT NULL and CONSTANT . Initialize variables with the assignment operator (:) or the DEFAULT keyword : V_myName VARCHAR2(20):= 'John ' ; V _myName VARCHAR2 (20) DEFAULT 'John '; 5.Declare one identifier per line for better readability and code maintenance. 6. Avoid using column names as identifiers. 7. Use the NOT NULL constraint when the variable must hold a value.

15. Declaring Variables DECLARE v_fname VARCHAR2(20); BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id = 100; dbms_output.put_line ( v_fname ); END ; /

16. Declaring Variables DECLARE v_fname VARCHAR2(20):= 'Ahmed'; BEGIN dbms_output.put_line ( v_fname ); v_fname :='Mohammed'; dbms_output.put_line ( v_fname ); END ; / --------------------------------------------------------------------------------------------------------------------- DECLARE v_fname VARCHAR2(20 ) Default 'Ahmed'; BEGIN dbms_output.put_line ( v_fname ); v_fname :='Mohammed'; dbms_output.put_line ( v_fname ); END ; /

17. Declaring Variables DECLARE v_fname VARCHAR2(20 ); BEGIN dbms_output.put_line ( v_fname ); END; / --------------------------------------------------------------------------------------------------------------------- DECLARE v_fname VARCHAR2(20) NOT NULL :='Ahmed'; BEGIN dbms_output.put_line ( v_fname ); END; /

18. Declaring Variables DECLARE v_fname CONSTANT VARCHAR2(20) :='Ahmed'; BEGIN dbms_output.put_line ( v_fname ); END; / --------------------------------------------------------------------------------------------------------------------- DECLARE v_fname CONSTANT VARCHAR2(20) :='Ahmed'; BEGIN v_fname :='Ali'; dbms_output.put_line ( v_fname ); END ; /

19. %TYPE Attribute Is used to declare a variable according to: A database column definition Another declared variables prefixed with : The database table and column name The name of the declared variable

20. %TYPE Attribute Syntax: Variable_Name TableName.ColumnName%Type ; Variable_Name Another_Variable_Name%Type ;

21. %TYPE Attribute DECLARE v_FirstName Employees.First_Name%Type ; BEGIN SELECT first_name INTO v_FirstName FROM Employees WHERE employee_id = 100; dbms_output.put_line ( v_FirstName ); END ;

22. %TYPE Attribute DECLARE V_FirstName Employees.First_Name%Type ; V_LastName V_FirstName%Type ; BEGIN SELECT last_name INTO V_LastName FROM Employees WHERE employee_id = 100; dbms_output.put_line ( V_LastName ); END;

23. Bind Variables Bind variables are : Created in the environment Also called host variables Created with the VARIABLE keyword * Used in SQL statements and PL/SQL blocks Accessed even after the PL/SQL block is executed Referenced with a preceding colon Values can be output using the PRINT command .

24. Using AUTOPRINT with Bind Variables Use the SET AUTOPRINT ON command to automatically display the bind variables used in a successful PL/SQL block.

25. Bind Variables SET AUTOPRINT ON ; VARIABLE b_result NUMBER BEGIN SELECT SALARY INTO : b_result FROM employees WHERE employee_id = 144; END; / PRINT b_result

26. IF Statement

27. Simple IF Statement IF condition THEN Statements End IF;

28. Simple IF Statement DECLARE v_MyNumber Number :=18; BEGIN IF v_MyNumber > 0 THEN dbms_output.put_line ('It is +'); End IF; END; /

29. IF THEN ELSE Statement IF condition THEN Statements ELSE Statements End IF;

30. IF THEN ELSE Statement DECLARE v_MyNumber Number :=-18 ; BEGIN IF v_MyNumber > 0 THEN dbms_output.put_line ('It is +'); ELSE dbms_output.put_line ('It is -'); End IF; END; /

31. IF THEN ELSE Statement IF condition THEN Statements ELSEIF condition THEN Statements ELSE Statements End IF;

32. IF THEN ELSE Statement DECLARE v_MyNumber NUMBER := 0; BEGIN IF v_MyNumber > 0 THEN dbms_output.put_line ('It is +'); ELSIF v_MyNumber < 0 THEN dbms_output.put_line ('It is -'); ELSE dbms_output.put_line ('It is Zero'); END IF; END;

33. Case Statement

34. Case Expression CASE WHEN expression1 THEN result1 WHEN expression2 THEN result2 ….. [ELSE result] END;

35. Case Statement DECLARE v_score NUMBER := 85; v_grade VARCHAR2(2); BEGIN CASE WHEN v_score >= 90 THEN v_grade := 'A'; WHEN v_score >= 80 THEN v_grade := 'B'; WHEN v_score >= 70 THEN v_grade := 'C'; WHEN v_score >= 60 THEN v_grade := 'D'; ELSE v_grade := 'F'; END CASE ; dbms_output.put_line ('Grade: ' || v_grade ); END; /

36. Iterative Control Loop Statements

37. Iterative Control: Loop Statements Loops repeat a statement (or a sequence of statements) multiple times . There are three loop types : Basic loop WHILE loop FOR loop

38. Basic Loops LOOP Statements Exit [When condition]; END LOOP;

39. Basic Loops DECLARE v_counter NUMBER := 1; BEGIN LOOP dbms_output.put_line ('Iteration: ' || v_counter ); v_counter := v_counter + 1; EXIT WHEN v_counter > 5; END LOOP; END; /

40. Basic Loops DECLARE v_countryid locations.country_id%TYPE := 'CA '; v_loc_id locations.location_id%TYPE ; v_counter NUMBER(2) := 1 ; v_new_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX( location_id ) INTO v_loc_id FROM locations WHERE country_id = v_countryid ; LOOP INSERT INTO locations ( location_id , city, country_id ) VALUES(( v_loc_id + v_counter ), v_new_city , v_countryid ); v_counter := v_counter + 1 ; EXIT WHEN v_counter > 3 ; END LOOP ; END ; /

41. While Loop While Condition LOOP Statement1; Statement2; …… END LOOP;

42. While Loop DECLARE v_countryid locations.country_id%TYPE := 'CA '; v_loc_id locations.location_id%TYPE ; v_new_city locations.city%TYPE := 'Montreal '; v_counter NUMBER:= 1 ; BEGIN SELECT MAX( location_id ) INTO v_loc_id FROM locations WHERE country_id = v_countryid ; WHILE v_counter <= 3 LOOP INSERT INTO locations ( location_id , city, country_id ) VALUES(( v_loc_id + v_counter ), v_new_city , v_countryid ); v_counter := v_counter + 1; END LOOP ; END ; /