2 Declaring PLSQL Variables Copyright 2006 Oracle All

Objectives After completing this lesson, you should be able to do the following: • Identify valid and invalid identifiers • List the uses of variables • Declare and initialize variables • List and describe various data types • Identify the benefits of using the %TYPE attribute • 2 -2 Declare, use, and print bind variables Copyright © 2006, Oracle. All rights reserved.

ﺍﻟﻤﻌﺮﻓﺎﺕ Identifiers are used for Naming a variable -: ﺑﻤﻌﻨﻰ ﺃﻨﻬﺎ ﺗﺴﺘﺨﺪﻡ ﻟﺘﺴﻤﻴﺔ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻭﻟﻬﺎ ﺷﺮﻭﻁ – – – Must start with a letter ﺗﺒﺪﺃ ﺑﺤﺮﻑ Can include letters or numbers ﻳﻤﻜﻦ ﺃﻦ ﺗﺤﺘﻮﻯ ﻋﻠﻰ ﺣﺮﻑ ﺃﻮ ﺭﻗﻢ Must limit the length to 30 characters ﺣﺮﻑ 30 ﻻﻳﺰﻳﺪ ﺍﻟﻄﻮﻝ ﻋﻦ ﺃﻦ Must not be reserved words ﻻﺗﻜﻮﻥ ﻣﻦ ﺍﻟﻜﻠﻤﺎﺕ ﺍﻟﻤﺤﺠﻮﺯﺓ ﺃﻦ Can include special characters (such as dollar sign, underscore, and pound sign) ﻳﻤﻜﻦ ﺃﻦ ﺗﺤﺘﻮﻯ ﻋﻠﻰ ﺍﻟﺤﺮﻭﻑ ﻼﻣﺎﺕ ﺍﻟﺨﺎﺻﺔ ﻭﺍﻟﻌ What Is the Difference Between a Variable and an Identifier? 2 -5 Copyright © 2006, Oracle. All rights reserved.

Declaring and Initializing PL/SQL Variables PL/SQL ﻼﻥ ﻭﺗﻬﻴﺌﺔ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻓﻰ ﺑﻴﺌﺔ ﻻﻋ ﺍ Syntax identifier [CONSTANT] datatype [NOT NULL] [: = | DEFAULT expr]; Examples DECLARE emp_hiredate emp_deptno location c_comm 2 -7 DATE; NUMBER(2) NOT NULL : = 10; VARCHAR 2(13) : = 'Atlanta'; CONSTANT NUMBER : = 1400; Copyright © 2006, Oracle. All rights reserved.

Declaring and Initializing PL/SQL Variables PL/SQL ﻼﻥ ﻭﺗﻬﻴﺌﺔ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻓﻰ ﺑﻴﺌﺔ ﻻﻋ ﺍ 1 2 2 -8 SET SERVEROUTPUT ON DECLARE Myname VARCHAR 2(20); BEGIN DBMS_OUTPUT. PUT_LINE('My name is: '||Myname); Myname : = 'John'; DBMS_OUTPUT. PUT_LINE('My name is: '||Myname); END; / SET SERVEROUTPUT ON DECLARE Myname VARCHAR 2(20): = 'John'; BEGIN Myname : = 'Steven'; DBMS_OUTPUT. PUT_LINE('My name is: '||Myname); END; / Copyright © 2006, Oracle. All rights reserved.

Delimiters in String Literals SET SERVEROUTPUT ON DECLARE event VARCHAR 2(15); BEGIN event : = q'!Father's day!'; DBMS_OUTPUT. PUT_LINE('3 rd Sunday in June is : '||event); event : = q'[Mother's day]'; DBMS_OUTPUT. PUT_LINE('2 nd Sunday in May is : '||event); END; / 2 -9 Copyright © 2006, Oracle. All rights reserved.

Types of Variables ﺃﻨﻮﺍﻉ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ • PL/SQL variables: – – • 2 -10 Scalar

Types of Variables ﺃﻨﻮﺍﻉ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ TRUE 25 -JAN-01 The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. 256120. 08 2 -11 Atlanta Copyright © 2006, Oracle. All rights reserved.

Guidelines for Declaring and Initializing PL/SQL Variables PL/SQL ﻼﻥ ﻭﺗﻬﻴﺌﺔ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻓﻰ ﺑﻴﺌﺔ ﻃﺮﻳﻘﺔ ﻻﻋ ﺍ ﺍﺭﺷﺎﺩ ﻋﻦ • • Follow naming conventions. SQL ﺃﺘﺒﺎﻉ ﻗﻮﺍﻋﺪ ﺍﻟﺘﺴﻤﻴﺔ ﺍﻟﻤﺴﺘﺨﺪﻣﺔ Use meaningful names for variables. ﺍﺳﺘﺨﺪﺍﻡ ﺃﺴﻤﺎﺀ ﺫﺍﺕ ﻣﻐﺰﻯ ﻟﻠﻤﺘﻐﻴﺮﺍﺕ • Initialize variables designated as NOT NULL and CONSTANT. ﻭﺿﻊ ﻗﻴﻤﺔ ﺍﺑﺘﺪﺍﺋﻴﺔ ﻋﻨﺪ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﻳﻦ ﺍﻟﻘﻴﺪﻳﻦ • Initialize variables with the assignment operator (: =) or the DEFAULT keyword: Myname VARCHAR 2(20): ='John'; Myname VARCHAR 2(20) DEFAULT 'John'; • Declare one identifier per line for better readability and code maintenance. ﻣﻦ ﺍ ﻻﻓﻀﻞ ﻛﺘﺎﺑﺔ ﻛﻞ ﻣﻌﺮﻑ ﻓﻰ ﺳﻄﺮ ﻟﻮﺣﺪﻩ ﻭﻫﻮ ﺃﻔﻀﻞ ﻟﻠﺘﻮﺛﻴﻖ ﻭ ﻟﺼﻴﺎﻧﺔ ﺍﻟﻜﻮﺩ 2 -12 Copyright © 2006, Oracle. All rights reserved.

Guidelines for Declaring PL/SQL Variables PL/SQL ﻼﻥ ﻭﺗﻬﻴﺌﺔ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻓﻰ ﺑﻴﺌﺔ ﻃﺮﻳﻘﺔ ﻻﻋ ﺍ ﺍﺭﺷﺎﺩ ﻋﻦ • Avoid using column names or tables as identifiers. ﻋﺪﻡ ﻭﺟﻮﺩ ﻣﺘﻐﻴﺮ ﺑﻨﻔﺲ ﺍﺳﻢ ﺟﺪﻭﻝ ﺃﻮﻋﻤﻮﺩ ﻓﻰ ﺟﺪﻭﻝ ﺳﻴﺘﻢ ﺍﺳﺘﺨﺪﺍﻣﻪ ﻓﻰ . ﻧﻔﺲ ﺍﻟﻮﺣﺪﺓ DECLARE employee_id NUMBER(6); BEGIN SELECT employee_id INTO employee_id FROM employees WHERE last_name = 'Kochhar'; END; / • Use the NOT NULL constraint when the variable must hold a value. ﺍﺫﺍ ﻛﺎﻥ ﺍﻟﻤﺘﻐﻴﺮ ﻳﺠﺐ ﺍﻥ ﺗﻜﻮﻥ ﺑﻬﺎ ﻗﻴﻤﺔ ﻳﺠﺐ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﺍ ﺍﻟﻘﻴﺪ 2 -13 Copyright © 2006, Oracle. All rights reserved.

Scalar Data Types ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﺍﻟﻤﻔﺮﺩﺓ ﺍﻟﻘﻴﻤﺔ • • Hold a single value Have no internal components ﻻﻳﻤﻜﻦ ﺃﻦ ﺑﻤﻌﻨﻰ ﺃﻨﻬﺎ ﻫﻰ ﻣﺘﻐﻴﺮﺍﺕ ﺗﺤﺘﻮﻯ ﻓﻘﻂ ﻋﻠﻰ ﻗﻴﻤﺔ ﻣﻔﺮﺩﺓ ﻭﺍﺣﺪﺓ . ﺗﺠﺰﺀ ﺍﻟﻰ ﻗﻴﻢ ﺃﺼﻐﺮ TRUE 25 -JAN-01 The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. 256120. 08 2 -14 Atlanta Copyright © 2006, Oracle. All rights reserved.

Base Scalar Data Types • DATE ﻼﺩ • • • 2 -16 ﺍﻟﻤﻴ ﻗﺒﻞ ﺍﻟﻤﻴ 4712 ﺑﻴﺎﻧﺎﺕ ﻋﻠﻰ ﺷﻜﻞ ﺗﺎﺭﻳﺦ ﻳﺒﺪﺍﺀ ﻣﻦ ﺑﻌﺪ 9999 ﻭﺣﺘﻰ TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND Copyright © 2006, Oracle. All rights reserved.

Declaring Scalar Variables ﻼﻥ ﻋﻦ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﺍﻟﻤﻔﺮﺩﺓ ﻻﻋ ﺍ Examples DECLARE emp_job VARCHAR 2(9); count_loop BINARY_INTEGER : = 0; dept_total_sal NUMBER(9, 2) : = 0; orderdate DATE : = SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3, 2) : = 8. 25; valid BOOLEAN NOT NULL : = TRUE; . . . 2 -17 Copyright © 2006, Oracle. All rights reserved.

%TYPE Attribute %TYPE ﺍﻟﺨﺎﺻﻴﺔ The %TYPE attribute • Is used to declare a variable according to: – A database column definition – Another declared variable • ﺗﺴﺨﺪﻡ ﻟﺘﻌﺮﻳﻒ ﻣﺘﻐﻴﺮ ﺑﺎ ﻻﻋﺘﻤﺎﺩ ﻋﻠﻰ ﺗﻌﺮﻳﻒ ﻣﺘﻐﻴﺮ ﺃﺨﺮ ﺃﻮ ﺗﻌﺮﻳﻒ ﻋﻤﻮﺩ ﻓﻰ ﺟﺪﻭﻝ ﻓﻰ ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ Is prefixed with: ﺗﻜﻮﻥ ﻣﺴﺒﻮﻗﺔ ﺏ – The database table and column – The name of the declared variable 2 -18 Copyright © 2006, Oracle. All rights reserved.

• • 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 unary operator NOT to check the variable values. not , ﻳﺠﺪﺭ ﺍ ﻻﺷﺎﺭﺓ ﻫﻨﺎ ﺃﻨﻪ ﻳﻤﻜﻦ ﺭﺑﻂ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﺑﻮﺍﺳﻄﺔ ﺍﻟﻌﻤﻠﻴﺎﺕ ﺍﻟﻤﻨﻄﻘﻴﺔ and , or • • The variables always yield TRUE, FALSE, or NULL. Arithmetic, character, and date expressions can be used to return a Boolean value. ( ﺗﻌﻴﺪ ﺍﻣﺎ A<B) ﻭﺍﺳﺘﺨﺪﺍﻡ ﺍﻟﺘﻌﺎﺑﻴﺮ ﺍﻟﺤﺴﺎﺑﻴﺔ ﻭﺍﻟﺮﻣﺰﻳﺔ ﻭﺍﻟﺘﺎﺭﻳﺨﻴﺔ ﻟﻠﺤﺼﻮﻝ ﻋﻠﻰ ﻧﺘﺎﺋﺞ ﻣﻨﻄﻘﻴﺔ false ﺃﻮ true 2 -21 Copyright © 2006, Oracle. All rights reserved.

Printing Bind Variables ﻃﺒﺎﻋﺔ ﻣﺘﻐﻴﺮﺍﺕ ﺍﻟﺮﺑﻂ Example ﺍ ﻻﻣﺮ ﻳﻤﻜﻦ ﺍﻟﻄﺒﺎﻋﺔ ﺑﺎﺳﺘﺨﺪﺍﻡ VARIABLE emp_salary NUMBER BEGIN SELECT salary INTO : emp_salary FROM employees WHERE employee_id = 178; END; / PRINT emp_salary SELECT first_name, last_name FROM employees WHERE salary=: emp_salary; 2 -24 Copyright © 2006, Oracle. All rights reserved.

Printing Bind Variables ﻃﺒﺎﻋﺔ ﻣﺘﻐﻴﺮﺍﺕ ﺍﻟﺮﺑﻂ Example ﺍ ﻻﻣﺮ ﻳﻤﻜﻦ ﺍﻟﻄﺒﺎﻋﺔ ﺗﻠﻘﺎﺋﻴﺎ ﺑﺎﺳﺘﺨﺪﺍﻡ VARIABLE emp_salary NUMBER SET AUTOPRINT ON BEGIN SELECT salary INTO : emp_salary FROM employees WHERE employee_id = 178; END; / 2 -25 Copyright © 2006, Oracle. All rights reserved.

Substitution Variables ﻼﻝ ﻣﺘﻐﻴﺮﺍﺕ ﻻﺣ ﺍ ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﺍﻟﺒﺪﻳﻠﺔ ﺃﻮ VARIABLE emp_salary NUMBER SET AUTOPRINT ON DECLARE empno NUMBER(6) : = &empno; BEGIN SELECT salary INTO : emp_salary FROM employees WHERE employee_id = empno; END; / 2 -27 Copyright © 2006, Oracle. All rights reserved.

Substitution Variables 1 2 3 SET VERIFY OFF ﺍ ﻻﻣﺮ 2 -28 ﻭﻫﺬﻩ ﺗﻈﻬﺮ

Prompt for Substitution Variables ﻟﻜﻦ Enter value for ﺍﻟﺮﺳﺎﻟﺔ ﺍ ﻻﺳﺎﺳﻴﺔ ﺍﻟﻤﻔﺘﺮﺿﺔ ﻫﻰ ﻳﻤﻜﻦ ﺗﻐﻴﺮﻫﺎ ﺑﺎﺳﺘﺨﺪﺍﻡ ﺍ ﻻﻣﺮ SET VERIFY OFF VARIABLE emp_salary NUMBER ACCEPT empno PROMPT 'Please enter a valid employee number: ' SET AUTOPRINT ON DECLARE empno NUMBER(6): = &empno; BEGIN SELECT salary INTO : emp_salary FROM employees WHERE employee_id = empno; END; / 2 -29 Copyright © 2006, Oracle. All rights reserved.

Composite Data Types ﺍﻟﻤﺘﻐﻴﺮﺍﺕ ﻣﺮﻛﺒﺔ ﺍﻟﻘﻴﻢ TRUE 23 -DEC-98 PL/SQL table structure 1 2 3 4 SMITH JONES NANCY TIM ATLANTA PL/SQL table structure 1 2 3 4 5000 2345 12 3456 VARCHAR 2 PLS_INTEGER 2 -30 NUMBER PLS_INTEGER Copyright © 2006, Oracle. All rights reserved.

LOB Data Type Variables ﻫﻰ ﻣﺘﻐﻴﺮﺍﺕ ﺗﺤﺘﻮﻯ ﻋﻠﻰ ﻛﺎﺋﻨﺎﺕ ﻛﺒﻴﺮﺓ ﺍﻟﺤﺠﻢ Book (CLOB) Photo

Summary In this lesson, you should have learned how to: • Recognize valid and invalid identifiers • Declare variables in the declarative section of a PL/SQL block • Initialize variables and use them in the executable section • Differentiate between scalar and composite data types • Use the %TYPE attribute • 2 -32 Use bind variables Copyright © 2006, Oracle. All rights reserved.

Practice 2: Overview This practice covers the following topics: • Determining valid identifiers • Determining valid variable declarations • Declaring variables within an anonymous block • Using the %TYPE attribute to declare variables • • 2 -33 Declaring and printing a bind variable Executing a PL/SQL block Copyright © 2006, Oracle. All rights reserved.