Oracle DBA Forum  

Вернуться   Oracle DBA Forum > Oracle University Official Study Notes (ENG) > Oracle 10g PL/SQL Advanced > Oracle 10g PL/SQL Advanced

Ответ
 
Опции темы Опции просмотра
  #21  
Старый 26.12.2009, 18:44
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Predefined Data Types



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; 04.10.2015 в 04:19.
Ответить с цитированием
  #22  
Старый 26.12.2009, 18:44
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Subtypes



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #23  
Старый 26.12.2009, 18:44
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Benefits of Subtypes



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #24  
Старый 26.12.2009, 18:44
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Declaring Subtypes



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #25  
Старый 26.12.2009, 18:45
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Using Subtypes



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #26  
Старый 26.12.2009, 18:45
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Type Compatibility



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #27  
Старый 26.12.2009, 18:45
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Summary



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; 04.10.2015 в 04:20.
Ответить с цитированием
  #28  
Старый 26.12.2009, 18:45
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,192
По умолчанию Practice Overview



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;
/
__________________
Телеграм чат

Последний раз редактировалось Marley; 04.10.2015 в 04:20.
Ответить с цитированием
Ответ
Опции темы
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход


Текущее время: 16:02. Часовой пояс GMT +3.


Powered by vBulletin®