Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

ORACLE PLSQL - Variables

Loading...

Published in: Oracle Training
1,272 Views

This document discuss Variables in Pl/SQL.

Debarun S / Kolkata

10 years of teaching experience

Qualification: B.Tech

Teaches: Computer, Mathematics, Science, Chemistry, Computer Science, Physics, School Level Computer, Defence Exams, IBPS, Insurance Exams, SSC Exams

Contact this Tutor
  1. In this chapter, we will discuss Variables in PI/SQL. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable's memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable. The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name. PL/SQL programming language allows to define various types of variables, such as date time data types, records, collections, etc. which we will cover in subsequent chapters. For this chapter, let us study only basic variable types. Variable Declaration in PL/SQL PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. The syntax for declaring a variable is variable name [CONSTANT] datatype [NOT NULL] [ : — I DEFAULT initial value] Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid PL/SQL data type or any user defined data type which we already have discussed in the last chapter. Some valid variable declarations along with their definition are shown below sales number (10, 2) pi CONSTANT double precision name varchar2 (25) address varchar2 (100) 3.1415; When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. For example sales number (10, 2) name varchar2 (25) address varchar2 (100) Initializing Variables in PL/SQL Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following
  2. The DEFAULT keyword The assignment operator For example counter binary integer 0; greetings varchar2 (20) DEFAULT 'Have a Good Day' You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable. It is a good programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the following example which makes use of various types of variables DECLARE a integer b integer c integer; f real; BEGIN c dbms output •put 70.0/3.0 f dbms output . put END; line( 'Value line ( 'Value of of c: f: c); f), When the above code is executed, it produces the following result Value of c: 30 Value of f: 23.333333333333333333 P L/ SQL procedure successfully completed. Variable scope in PL/SQL PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope Local variables Variables declared in an inner block and not accessible to outer blocks. Global variables Variables declared in the outermost block or a package. Following example shows the usage of Local and Global variables in its simple form
  3. DECLARE Global variables numl number num2 number BEGIN 95; dbms output. put line ( dbms output. put line ( DECLARE Local variables Outer Outer Variable Variable numl : num2 : Il Il numl num2 ) numl num2 BEGIN dbms dbms END; END; n umb er n umb er 195; 185; output. put line( output. put line( Inner Inner Variable Variable numl : num2 : Il Il numl num2 ) When the above code is executed, it produces the following result Outer Variable numl: Outer Variable num2 : Inner Variable numl: Inner Variable num2 : 95 85 195 185 P L/ SQL procedure successfully completed. Assigning SQL Query Results to PL/SQL Variables You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept. Let us create a table named CUSTOMERS (For SQL statements, please refer to the SOL tutorial) CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , DECIMAL (18, 2), SALARY PRIMARY KEY (ID) Let us now insert some values in the table INSERT INTO VALUES (1, CUSTOMERS (ID, SALARY) Ramesh ' 32 , Ahmedabad ' 2000.00 ) •
  4. INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) INSERT VALUES INSERT VALUES INSERT VALUES INSERT VALUES INSERT 4500.00 ) VALUES Khilan ' (2, 25, Delhi ' 1500.00 ) , INTO CUSTOMERS (3, ' kaushik' ( ID, NAME , ADDRESS, SALARY) Kota ' 2000.00 ) , INTO INTO (5, INTO (6, CUSTOMERS Cha itali ' CUSTOMERS 23, 25, ( ID, NAME , AGE, ADDRESS, SALARY) 6500.00 ) , ( ID, NAME , AGE, ADDRESS, SALARY) 8500.00 ) , Mumbai ' ' Bhopal ' Hardik ' 27, CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) Komai ' 22, 'MP' The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL DECLARE c id customers.id%type 1; c name customers . name%type, c addr customers . address%type, c sal customers . salary%type, BEGIN SELECT name, address, FROM cus tome rs WHERE id dbms output . put line ( ' Customer I c name END; salary INTO f rom c name , c addr , c addr c sal earns c sal) When the above code is executed, it produces the following result Customer Ramesh from Ahmedabad earns 2000 P L/ SQL procedure completed successfully

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related Study Notes

Upload Study Notes

If you have your own Study Notes which you think can benefit others, please upload on LearnPick. For each approved Study Note you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > Study Notes > ORACLE PLSQL - Variables