Stay Ahead with the Power of Upskilling - Invest in Yourself! 
Certify and Increase Opportunity.
Be
Govt. Certified PL SQL Developer
Variable
Variables, allows you to save values in memory. For example, you may want to keep a counter inside a loop, or store a string value for processing. In this section, we’ll see how to declare a variable and assign it a value. We’ll also look at how to turn a variable into a constant by permanently fixing its value.
The syntax for a variable declaration is:
name datatype(size) DEFAULT default_value ;
You can also assign the default value using the := operator. In this case, the syntax is:
name datatype(size) := default_value ;
The next three sections describe the name, datatype, and default value.
Name
The name may be up to 30 characters long, and may include letters, numbers, or underscores. Variable names must start with a letter. A good variable is descriptive; many programmers like to use one- or two-letter variable names like X or A1 because they are easy to type,[ 4 ] but this does not make for very readable code. If a variable represents an employee’s monthly pay rate, then call it “monthly_pay_rate,” not “mpr” or, even worse, “r.” Let’s face it — most of our time as developers is spent either fixing our old programs or helping someone else fix theirs, not writing new ones. You have only one chance to develop a program. You have the rest of your working life to support it. The few seconds you save by using a short, meaningless name are not worth the future maintenance hassle for either you or the poor sap who’ll take your place when you become a consultant.
Datatype
The datatype specifies the type and amount of data a variable can hold. While there are a variety of different datatypes, in this section we’ll look at the two most common: NUMBER and VARCHAR2.
The NUMBER type holds general numbers, such as 1, – 457, or 3.14. You can assign a number variable a precision and scale to set its maximum size. The precision is the maximum number of digits allowed. The scale controls rounding. A positive number indicates the number of places to the right of the decimal place to round, and a negative number indicates the number of units to the left of the decimal. The size used in the declaration is written as a combination of the precision and the scale. For example, the number 1523.567 is rounded to 1523.6 if the datatype is NUMBER(4,1); it would be 1500 if the datatype is NUMBER(4, -2).
The VARCHAR2 datatype holds character strings, like `Hello, world!’, `Saturday’, or `Buster Keaton’. In PL/SQL, the value of a string is enclosed by single quotation marks (ticks), not double quotes. The compiler can get very confused if you mistakenly use double quotes. Also, the declaration of the VARCHAR2 type must include the maximum size of the string, which can range from 1 to 32,767 characters.[ 5 ] For example, a string of 50 characters is declared as a VARCHAR2(50).
In addition to explicitly declaring a variable’s type, you can implicitly declare it using an anchored declaration . The anchored declaration directly associates a variable’s type with the type of a column in a database table. For example, suppose you want to use a PL/SQL variable to hold an employee’s last name from an employee table. Rather than hardcoding the column definition in the program, you can simply anchor the variable to the last-name column in the employee table.
Anchored declarations have other benefits besides ease of use. They simplify long-term maintenance in two ways: first, they improve readability, because the relationship between a variable and a column is explicit; second, they minimize problems caused by changes in the database schema. How many times have you had to fix a program because a column was redefined? The syntax for an anchored declaration is:
variable_name table_name . column_name %TYPE;
Here are a few examples that help reduce the chance of “overflowing” a VARCHAR2 variable with too many characters:
Emp_name EMP.ENAME%TYPE; Emp_Dept_code EMP.DEPT%TYPE; Dept_name DEPT.DNAME%TYPE;
Default value
Each parameter must have a name, a mode, a datatype, and (optionally) A variable default value is the value a variable contains when it’s referenced for the first time. If you don’t know what this value is, then you’re asking for trouble; it can be very difficult to track down bugs caused by uninitialized variables. Here are some sample variable declarations:
rec_count NUMBER default 0; yearly_interest_rate NUMBER(5,4) := 0.08; account_status_code VARCHAR2(10) default 'OPEN'; emp_last_name emp.lname%TYPE;
Assigning values
Once we’ve declared the variable, we can assign it a value in the body of our program. PL/SQL uses the := operator to assign a value to a variable.
Here are a few sample assignments:
count := 0; emp_count := emp_count + 1; annual_salary := hourly_rate * 2000;
You should make sure you declare the variables large enough to hold the full range of potential values. If the value you assign exceeds the maximum size declared for the variable, the RDBMS generates the VALUE_ERROR exception. For example, assigning the last name `Pantanizoupolos’ to a VARCHAR2(10) raises an exception.
Constants
A constant is a fixed variable, which means you can’t change its assigned value inside your program. To turn a variable into a constant, you simply include the CONSTANT keyword in the variable’s declaration. Note that, by convention, constant variable names are usually uppercase. Here are some examples:
PI CONSTANT NUMBER := 3.14159; YEARLY_WORK_HOURS CONSTANT NUMBER := 2000; LINES_PER_PAGE CONSTANT NUMBER := 60; OPEN_STATUS CONSTANT VARCHAR(1) := 'O';
The main use for constants is to replace a program’s magic values — numbers or strings meaningful only to the programmer or business — with more easily understood names. For instance, the last example in the previous section used the number 2000 to calculate an annual salary. While we can often deduce the meaning of a magic value (in this case, the number of hours in the work year), it’s just bad coding practice to randomly sprinkle your program with literals.
The problem with sticking these values directly into the code is that — believe it or not — constants can change. Your employer might decide to adopt a six-hour workday (perhaps a German conglomerate buys your company) and reduce the number of hours in the work year to 1500. Using literals, you would have to go through your code line by line and replace all the 2000s with 1500s. Constants eliminate this tedious make-work. We can simply create a constant called YEARLY_WORK_HOURS, assign it a value of 2000, or 1500, or whatever value we want, and use it to clarify our calculations. This is shown in the following example:
annual_salary := hourly_rate * YEARLY_WORK_HOURS;
-Oracle
Apply for PL SQL Certification Now!!
http://www.vskills.in/certification/Certified-PL/SQL-Developer
