02 Design Considerations

[Marley]

PL/SQL Data Types

Every constant, variable, and parameter has a data type, which specifies a storage format, constraints, and a valid range of values. PL/SQL provides a variety of predefined data types. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL/SQL enables you to define your own subtypes.

[Marley]

Definition of Subtypes

A subtype is a data type based on an existing data type. It does not define a new data type, instead it places a constraint on an existing data type. There are several predefined subsets specified in the standard package. DECIMAL and INTEGER are subtypes of NUMBER. CHARACTER is a subtype of CHAR.

Standard Subtypes




With the NATURAL and POSITIVE subtypes, you can restrict an integer variable to non-negative and positive values, respectively. NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. You can use SIGNTYPE to restrict an integer variable to the values 1, 0, and 1, which is useful in programming tri-state logic.

A constrained subtype is a subset of the values normally specified by the data type on which the subtype is based. POSITIVE is a constrained subtype of BINARY_INTEGER.

An unconstrained subtype is not a subset of another data type; it is an alias to another data type. FLOAT is an unconstrained subtype of NUMBER.

Use the subtypes DEC, DECIMAL, and NUMERIC to declare fixed-point numbers with a maximum precision of 38 decimal digits.

Use the subtypes DOUBLE PRECISION and FLOAT to declare floating-point numbers with a maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits.

Use the subtypes INTEGER, INT, and SMALLINT to declare integers with a maximum precision of 38 decimal digits.

You can create your own user-defined subtypes.

Note: You can use these subtypes for compatibility with ANSI/ISO and IBM types. Currently, VARCHAR is synonymous with VARCHAR2. However, in future releases of PL/SQL, to accommodate emerging SQL standards, VARCHAR may become a separate data type with different comparison semantics. It is a good idea to use VARCHAR2 rather than VARCHAR.

[Marley]

Benefits

If your applications require a subset of an existing data type, you can create your own subtypes. By using subtypes, you can increase the reliability and improve the readability by indicating the intended use of constants and variables. Subtypes can increase reliability by detecting the out-of-range values.

With the predefined subtypes, you have compatibility with other data types from other programming languages.

[Marley]

Declaring Subtypes

Subtypes are defined in the declarative section of a PL/SQL block, subprogram, or package.

Using the SUBTYPE keyword, you name the subtype and provide the name of the base type. The base type may be constrained starting in Oracle8i, but cannot be constrained in earlier releases.

You can use the %TYPE attribute on the base type to pick up a data type from a database column or from an existing variable data type. You can also use the %ROWTYPE attribute.

Examples

CREATE OR REPLACE PACKAGE mytypes

IS

  SUBTYPE   Counter IS INTEGER;  -- based on INTEGER type

  TYPE typ_TimeRec IS RECORD (minutes INTEGER, hours

  INTEGER);

  SUBTYPE Time IS typ_TimeRec;   -- based on RECORD type

  SUBTYPE ID_Num IS customers.customer_id%TYPE; 

  CURSOR cur_cust IS SELECT * FROM customers;

  SUBTYPE CustFile IS cur_cust%ROWTYPE; -- based on cursor

END mytypes;

/

[Marley]

Using Subtypes

After the subtype is declared, you can assign an identifier for that subtype. Subtypes can increase reliability by detecting out-of-range values.

DECLARE

  v_rows    mytypes.Counter; --use package subtype dfn

  v_customers  mytypes.Counter;

  v_start_time mytypes.Time;

  SUBTYPE    Accumulator IS NUMBER;

  v_total      Accumulator(4,2);  

SUBTYPE    Scale IS NUMBER(1,0);   -- constrained subtype

  v_x_axis     Scale;  -- magnitude range is -9 .. 9

BEGIN

  v_rows := 1;

  v_start_time.minutes := 15;

  v_start_time.hours   := 03;

 dbms_output.put_line('Start time is: '||  

 v_start_time.hours|| ':' || v_start_time.minutes);

END;

/

[Marley]

Type Compatibility

An unconstrained subtype is interchangeable with its base type. Different subtypes are interchangeable if they have the same base type. Different subtypes are also interchangeable if their base types are in the same data type family.

DECLARE

   v_rows      mytypes.Counter;

   v_customers mytypes.Counter;

   SUBTYPE   Accumulator IS NUMBER; 

   v_total     Accumulator(6,2);

BEGIN

   SELECT COUNT(*) INTO v_customers FROM customers;

   SELECT COUNT(*) INTO v_rows FROM orders;

   v_total := v_customers + v_rows;

   DBMS_OUTPUT.PUT_LINE('Total rows from 2 tables: '||

   v_total);

EXCEPTION

   WHEN value_error THEN

   DBMS_OUTPUT.PUT_LINE('Error in data type.');

END;

/

[Marley]

Summary

Use the guidelines for designing the cursors
Take advantage of the features of cursor variables and pass pointers to result sets to different applications.

You can use subtypes to organize and strongly type data types for an application.

[Marley]

Practice Overview

In this practice you will determine the output of a PL/SQL code snippet and modify the snippet to improve the performance. Next, you will implement subtypes and use cursor variables to pass values to and from a package.


Practice 2

Note: You will be using oe/oe as the username/password for the practice exercises. Files mentioned in the practice exercises are found in /labs folder. Additionally, solution scripts are provided for each question and are located in the /soln folder. Your instructor will provide you with the exactly location of these files.

Determine the output of the following code snippet.

SET SERVEROUTPUT ON

BEGIN

	UPDATE orders SET order_status = order_status;

	FOR v_rec IN ( SELECT order_id FROM orders )

	LOOP

		IF SQL%ISOPEN THEN

		   DBMS_OUTPUT.PUT_LINE('TRUE  ' || SQL%ROWCOUNT);

		ELSE

		   DBMS_OUTPUT.PUT_LINE('FALSE  ' || SQL%ROWCOUNT);

		END IF;

	END LOOP;

END;

/

Modify the following snippet of code to make better use of the FOR UPDATE clause and improve the performance of the program.

DECLARE

	CURSOR cur_update 

    IS SELECT * FROM customers 

    WHERE credit_limit < 5000 FOR UPDATE;

BEGIN

	FOR v_rec IN cur_update

	LOOP

		IF v_rec IS NOT NULL

		THEN

			UPDATE customers 

			SET credit_limit = credit_limit + 200

			WHERE customer_id = v_rec.customer_id;

		END IF;

	END LOOP;

END;

/