advertisement

Using Scalar Data Types

0 %
100 %
advertisement
Information about Using Scalar Data Types

Published on June 14, 2016

Author: yaqinov

Source: slideshare.net

advertisement

1. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Database Programming with PL/SQL 2-4 Using Scalar Data Types

2. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Objectives This lesson covers the following objectives: • Declare and use scalar data types in PL/SQL • Define guidelines for declaring and initializing PL/SQL variables • Identify the benefits of anchoring data types with the %TYPE attribute 3

3. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Purpose • Most of the variables you define and use in PL/SQL have scalar data types. • A variable can have an explicit data type, such as VARCHAR2, or it can automatically have the same data type as a table column in the database. You will learn the benefits of basing some variables on table columns. 4

4. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Character Variables • Character data types include CHAR, VARCHAR2, and LONG. DECLARE v_emp_job VARCHAR2(9); v_order_no VARCHAR2(6); v_product_id VARCHAR2(10); v_rpt_body_part LONG; … 5

5. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Number Variables • Number data types include NUMBER, PLS_INTEGER, BINARY_INTEGER, and BINARY_FLOAT. In the syntax, CONSTANT constrains the variable so that its value cannot change. Constants must be initialized. • INTEGER is an alias for NUMBER(38,0). DECLARE v_dept_total_sal NUMBER(9,2) := 0; v_count_loop INTEGER := 0; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; … 6

6. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Date Variables • Date data types include DATE, TIMESTAMP, and TIMESTAMP WITH TIMEZONE. DECLARE v_orderdate DATE := SYSDATE + 7; v_natl_holiday DATE; v_web_sign_on_date TIMESTAMP; … 7

7. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Boolean Variables • Boolean is a data type that stores one of the three possible values used for logical calculations: TRUE, FALSE, or NULL. DECLARE v_valid BOOLEAN NOT NULL := TRUE; v_is_found BOOLEAN := FALSE; v_underage BOOLEAN; … 8

8. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Boolean Variables Details When declaring boolean variables: • Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. • Conditional expressions use the logical operators AND and OR, and the operator NOT to check the variable values. • The variables always yield TRUE, FALSE, or NULL. • You can use arithmetic, character, and date expressions to return a Boolean value. 9

9. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Guidelines for Declaring and Initializing PL/SQL Variables • Use meaningful names and follow naming conventions. • Declare one identifier per line for better readability, code maintenance, and easier commenting. • Use the NOT NULL constraint when the variable must hold a value. • Avoid using column names as identifiers. 10

10. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Guidelines for Declaring and Initializing PL/SQL Variables DECLARE country_id CHAR(2); BEGIN SELECT country_id INTO country_id FROM countries WHERE country_name = 'Canada'; END; 11

11. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Anchoring Variables with the %TYPE Attribute • Rather than hard-coding the data type and precision of a variable, you can use the %TYPE attribute to declare a variable according to another previously declared variable or database column. • The %TYPE attribute is most often used when the value stored in the variable is derived from a table in the database. • When you use the %TYPE attribute to declare a variable, you should prefix it with the database table and column name. 12

12. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types %TYPE Attribute • Look at this database table and the PL/SQL block that uses it. This PL/SQL block stores the correct salary in the v_emp_salary variable. But what if the table column is altered later? CREATE TABLE myemps ( emp_name VARCHAR2(6), emp_salary NUMBER(6,2)); DECLARE v_emp_salary NUMBER(6,2); BEGIN SELECT emp_salary INTO v_emp_salary FROM myemps WHERE emp_name = 'Smith'; END; 13

13. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types %TYPE Attribute Details The %TYPE attribute: • Is used to automatically give a variable the same data type and size as: – A database column definition – Another declared variable • Is prefixed with either of the following: – The database table and column – The name of the other declared variable 14

14. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Declaring Variables with the %TYPE Attribute • Syntax: • Examples: identifier table.column_name%TYPE; ... v_emp_lname employees.last_name%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 1000; ... 15

15. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Advantages of the %TYPE Attribute Advantages of the %TYPE attribute are: • You can avoid errors caused by data type mismatch or wrong precision. – You need not change the variable declaration if the column definition changes. That is, if you have already declared some variables for a particular table without using the %TYPE attribute, then the PL/SQL block can return errors if the column for which the variable declared is altered. • When you use the %TYPE attribute, PL/SQL determines the data type and size of the variable when the block is compiled. This ensures that such a variable is always compatible with the column that is used to populate it. 16

16. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types %TYPE Attribute • Look again at the database table and the PL/SQL block. Now the PL/SQL block continues to work correctly even if the column data type is altered later. CREATE TABLE myemps ( emp_name VARCHAR2(6), emp_salary NUMBER(6,2)); DECLARE v_emp_salary myemps.emp_salary%TYPE; BEGIN SELECT emp_salary INTO v_emp_salary FROM myemps WHERE emp_name = 'Smith'; END; 17

17. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Terminology Key terms used in this lesson included: • %TYPE • Boolean 18

18. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.PLSQL 2-4 Using Scalar Data Types Summary In this lesson, you should have learned how to: • Declare and use scalar data types in PL/SQL • Define guidelines for declaring and initializing PL/SQL variables • Identify the benefits of anchoring data types with the %TYPE attribute 19

Add a comment

Related pages

Using Scalar Data Types - Cerritos College

Using Scalar Data Types Copyright © 2007, Oracle. All rights reserved. Tell Me/Show Me Guidelines for Declaring and Initializing PL/SQL Variables
Read more

Scalar Types (Windows) - msdn.microsoft.com

Scalar Types Developer resources ... Graphics drivers can implement minimum precision scalar data types by using any precision greater than or equal to ...
Read more

Scalar Types - msdn.microsoft.com

Scalar Type. C Data Type. Storage Size (in bytes) Recommended Alignment. INT8. char. 1. Byte. UINT8. unsigned char. 1. Byte. INT16. short. 2. Word. UINT16 ...
Read more

Vector Data Types | Programming with OpenCL C | InformIT

Vector Data Types. For the scalar integer and floating-point data types described in Table 4.1, OpenCL C adds support for vector data types. The vector ...
Read more

What is Scalar? - Definition from Techopedia

In C programming languages, scalar data types (such as char, int and float) are commonly used. However, scalar data types also may be scalar variables ...
Read more

3 PL/SQL Data Types - Oracle

3 PL/SQL Data Types. ... Scalar data types store single values with no internal components. ... see Using the %TYPE Attribute and Using the %ROWTYPE Attribute.
Read more

Oracle Quiz Questions: Section 2 Lesson 4: Using Scalar ...

Using Scalar Data Types Section 1 1. If you use the %TYPE attribute, you can avoid hard-coding the column name. True or False? (1) Points
Read more

Using Scalar Data Types - Documents

Test: Quiz: Using Scalar Data Types 1. When declared using %TYPE, a variable will inherit ____ from the column on which it is based. Mark for Review (1 ...
Read more

SQL Data Types for MS Access, MySQL, and SQL Server

SQL Data Types for Various DBs ... Stores up to 8,000 bytes of data of various data types, ... While using this site, ...
Read more