1 / 37

Declaring Variables

Declaring Variables. Objectives. After completing this lesson, you should be able to do the following: List the benefits of PL/SQL Recognize the basic PL/SQL block and its sections Describe the significance of variables in PL/SQL Declare PL/SQL variables Execute a PL/SQL block.

claus
Download Presentation

Declaring Variables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Declaring Variables

  2. Objectives • After completing this lesson, you should be able to do the following: • List the benefits of PL/SQL • Recognize the basic PL/SQL block and its sections • Describe the significance of variables in PL/SQL • Declare PL/SQL variables • Execute a PL/SQL block

  3. About PL/SQL • PL/SQL is an extension to SQL with design features of programming languages. • Data manipulation and query statements of SQL are included within procedural units of code.

  4. Benefits of PL/SQL • Integration Application Oracle Server Shared library

  5. Benefits of PL/SQL • Improved Performance SQL Other DBMSs Application SQL SQL SQL SQL IF...THEN SQL ELSE SQL END IF; SQL Oracle with PL/SQL Application

  6. Benefits of PL/SQL • Modularize program development DECLARE BEGIN EXCEPTION END;

  7. Benefits of PL/SQL • It is portable. • You can declare identifiers.

  8. Benefits of PL/SQL • You can program with procedural language control structures. • It can handle errors.

  9. DECLARE BEGIN EXCEPTION END; PL/SQL Block Structure • DECLARE – Optional • Variables, cursors, user-defined exceptions • BEGIN – Mandatory • SQL statements • PL/SQL statements • EXCEPTION – Optional • Actions to perform whenerrors occur • END; – Mandatory

  10. DECLARE BEGIN EXCEPTION END; PL/SQL Block Structure DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN ... END;

  11. Block Types • Anonymous Procedure Function [DECLARE] BEGIN --statements [EXCEPTION] END; PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END;

  12. DECLARE BEGIN EXCEPTION END; Program Constructs Stored procedure/function Anonymousblock Applicationprocedure/function Applicationtrigger Databasetrigger Packagedprocedure/ function

  13. Use of Variables • Use variables for: • Temporary storage of data • Manipulation of stored values • Reusability • Ease of maintenance

  14. Handling Variables in PL/SQL • Declare and initialize variables in the declaration section. • Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters. • View results through output variables.

  15. Types of Variables • PL/SQL variables: • Scalar • Composite • Reference • LOB (large objects) • Non-PL/SQL variables: Bind and host variables

  16. “Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.” Types of Variables 25-OCT-99 TRUE 256120.08 Atlanta

  17. identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; Declaring PL/SQL Variables Syntax Examples

  18. Declaring PL/SQL Variables • Guidelines • Follow naming conventions. • Initialize variables designated as NOT NULL. • Initialize identifiers by using the assignment operator (:=) or the DEFAULT reserved word. • Declare at most one identifier per line.

  19. Naming Rules • Two variables can have the same name, provided they are in different blocks. • The variable name (identifier) should not be the same as the name of table columns used in the block. Adopt a naming convention for PL/SQL identifiers: for example, v_empno DECLARE empno NUMBER(4); BEGIN SELECT empno INTO empno FROM emp WHERE ename = 'SMITH'; END;

  20. Assigning Values to Variables Syntax Examples Set a predefined hiredate for new employees. • identifier := expr; v_hiredate := '31-DEC-98'; Set the employee name to “Maduro.” v_ename := 'Maduro';

  21. Variable Initialization and Keywords • Using: • Assignment operator (:=) • DEFAULT keyword • NOT NULL constraint

  22. Scalar Datatypes • Hold a single value • Have no internal components 25-OCT-99 TRUE “Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.” 256120.08 Atlanta

  23. Base Scalar Datatypes • VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • DATE • CHAR [(maximum_length)] • LONG • LONG RAW • BOOLEAN • BINARY_INTEGER • PLS_INTEGER

  24. Scalar Variable Declarations • Examples v_job VARCHAR2(9); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;

  25. The %TYPE Attribute • Declare a variable according to: • A database column definition • Another previously declared variable • Prefix %TYPE with: • The database table and column • The previously declared variable name

  26. Declaring Variables with the %TYPE Attribute • Examples ... v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10; ...

  27. Declaring Boolean Variables • Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. • The variables are connected by the logical operators AND, OR, and NOT. • The variables always yield TRUE, FALSE, or NULL. • Arithmetic, character, and date expressions can be used to return a Boolean value.

  28. Composite Datatypes • Have internal components that can be manipulated individually • PL/SQL TABLES • PL/SQL RECORDS • NESTED TABLE • VARRAY types

  29. LOB Datatype Variables Recipe (CLOB) Photo (BLOB) Movie (BFILE) NCLOB

  30. Server Bind Variables O/S Bind Variable

  31. Referencing Non-PL/SQL Variables • Store the annual salary into a SQL*Plus host variable. • Reference non-PL/SQL variables as host variables. • Prefix the references with a colon (:). :g_monthly_sal := v_sal / 12;

  32. Example: • VARIABLE g_salary NUMBER • BEGIN • SELECT salary INTO :g_salary FROM emp • WHERE emp_id = 178; • END; • / • PRINT g_salary

  33. Example • SET SERVEROUTPUT ON • DEFINE p_annual_sal = 60000 • DECLARE • v_sal NUMBER(9,2) := &p_annual_sal; • BEGIN • v_sal := v_sal/12; • DBMS_OUTPUT.PUT_LINE (‘The monthly salary is ‘||TO_CHAR(v_sal)); • END; • /

  34. DBMS_OUTPUT.PUT_LINE • An Oracle-supplied packaged procedure • An alternative for displaying data from a PL/SQL block • Must be enabled in SQL*Plus with • SET SERVEROUTPUT ON

  35. DECLARE BEGIN EXCEPTION END; Summary • PL/SQL blocks are composed of the following sections: • Declarative (optional) • Executable (required) • Exception handling (optional) • A PL/SQL block can be an anonymous block, procedure, or function.

  36. Summary • PL/SQL identifiers: • Are defined in the declarative section • Can be of scalar, composite, reference, or LOB datatype • Can be based on the structure of another variable or database object • Can be initialized

  37. Practice Overview • Determining validity of declarations • Developing a simple PL/SQL block

More Related