1 / 22

PL/SQL Programming

PL/SQL Programming. 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. Benefits of PL/SQL. Integration of applications, shared library and Oracle server. Improve performance.

jescie-duncan
Download Presentation

PL/SQL Programming

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. PL/SQL Programming • 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.

  2. Benefits of PL/SQL • Integration of applications, shared library and Oracle server. • Improve performance. application SQL Other DBMSs IF … THEN SQL ELSE SQL END IF; Oracle with PL/SQL application

  3. PL/SQL Block Structure • DECLARE –Optional • Variables, cursors, uer-define exceptions • BEGIN – Mandatory • SQL statements • PL/SQL statements • EXCEPTION – Optional • Actions to perform when errors occur • END; - Mandatory

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

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

  6. Program Constructs • Anonymous Block • Unnamed PL/SQL block that is embedded within an application or is issued interactively • Stored procedure or function Named PL/SQL block stored in Oracle Server that can accept parameters and be invoked repeatedly by name. • Package Named PL/SQL module that groups related procedures, functions and identifiers. Stored in Oracle server or Oracle Developer components – for example, Forms.

  7. 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.

  8. Types of PL/SQL Variables • Scalar • BOOLEAN, DATE, NUMBER, VARCHAR2 • Composite • Such as records. • Reference • Pointers • LOB (large objects) • Used to hold large objects such as images. • Non-PL/SQL variables: bind and host variables.

  9. Declaring PL/SQL Variables • Examples: • Declare v_hiredate DATE v_deptno NUMBER(2) NOT NULL :=10; v_location VARCHAR(13) := ‘New York’; v_tax_rate NUMBER(3,2) := 3.0; c_comm CONSTANT NUMBER := 1400; • Guidelines: • Follow naming conventions. • Initialize variables designated as NOT NULL. • Declare at most one identifier per line.

  10. Base Scalar Datatypes • VARCHAR2(maximum_length) • No default size; max = 32767 bytes • NUMBER[(precision, scale)] • For fixed and floating numbers. • CHAR[(maximum_length)] • For fixed-length character string. Default to 1. • BOOLEAN • TURE, FALSE, or NULL • BINARY_INTEGER • Base type of integers between -2,147,483,647 and 2,147,483,647. • PLS_INTEGER • Same range as BINARY_INTEGER but requires less storage and are faster than NUMBER and BINARY_INTEGER values.

  11. Declaring Variables with the % TYPE attribute • Declare a variable according to • A database column definition • Another previously declared variables. • Prefix % TYPE • Examples: v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;

  12. PL/SQL Block Syntax and Guidelines • Statements can continue over several lines. • Identifiers • Can contain up to 30 characters. • Must begin with alphabetic character. • Literals • Enclosed in single quotation marks. • A PL/SQL block is terminated by a slash / on a line by itself. • Commenting code • Single-line comments with two dashes (--). • Place multi-line comments between /* and */

  13. 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;

  14. 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';

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

  16. 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

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

  18. 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;

  19. 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

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

  21. 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.

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

More Related