In this part, we will examine Variables in Pl/SQL. A variable is only a name given to a capacity territory that our projects can control. Every factor in PL/SQL has a particular information type, which decides the size and the design of the variable's memory; the scope of qualities that can be put away inside that memory and the arrangement of activities that can be applied to the variable.
The name of a PL/SQL variable comprises of a letter alternatively followed by more letters, numerals, dollar signs, underscores, and number signs and ought not surpass 30 characters. Of course, factor names are not case-delicate. You can't utilize a saved PL/SQL catchphrase as a variable name.
PL/SQL programming language permits to characterize different kinds of factors, for example, date time information types, records, assortments, and so on which we will cover in resulting parts. For this section, let us concentrate just essential variable sorts.
Variable Declaration in PL/SQL
PL/SQL factors should be pronounced in the statement area or in a bundle as a worldwide variable. At the point when you announce a variable, PL/SQL designates memory for the variable's worth and the capacity area is recognized by the variable name.
The sentence structure for announcing a variable is −
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Where, variable_name is a substantial identifier in PL/SQL, datatype should be a legitimate PL/SQL information type or any client characterized information type which we as of now have talked about in the last part. Some legitimate variable announcements alongside their definition are appeared beneath −
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
At the point when you give a size, scale or exactness limit with the information type, it is known as an obliged affirmation. Compelled statements require less memory than unconstrained revelations. For instance −
sales number(10, 2);
name varchar2(25);
address varchar2(100);
Initializing Variables in PL/SQL
At whatever point you announce a variable, PL/SQL doles out it a default estimation of NULL. On the off chance that you need to introduce a variable with a worth other than the NULL worth, you can do as such during the statement, utilizing both of the accompanying −
- The DEFAULT watchword
- The task administrator
For instance −
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
You can likewise indicate that a variable ought not have a NULL worth utilizing the NOT NULL requirement. On the off chance that you utilize the NOT NULL limitation, you should unequivocally relegate an underlying incentive for that variable.
It is a decent programming practice to instate factors appropriately something else, here and there projects would deliver surprising outcomes. Attempt the accompanying model which utilizes different kinds of factors −
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
At the point when the above code is executed, it creates the accompanying outcome −
Value of c: 30
Value of f: 23.333333333333333333
PL/SQL procedure successfully completed.
Variable Scope in PL/SQL
PL/SQL permits the settling of squares, i.e., each program square may contain another internal square. On the off chance that a variable is pronounced inside an internal square, it isn't open to the external square. Notwithstanding, if a variable is proclaimed and open to an external square, it is additionally available to all settled inward squares. There are two kinds of variable degree −
- Local variables − Variables announced in an inward square and not available to external squares.
- Global variables − Variables pronounced in the furthest square or a bundle.
Following model shows the utilization of Local and Global factors in its basic structure −
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
At the point when the above code is executed, it delivers the accompanying outcome −
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185
PL/SQL procedure successfully completed.
Assigning SQL Query Results to PL/SQL Variables
You can utilize the SELECT INTO articulation of SQL to dole out qualities to PL/SQL factors. For every thing in the SELECT rundown, there should be a relating, type-viable variable in the INTO list. The accompanying model delineates the idea. Allow us to make a table named CUSTOMERS −
(For SQL articulations, if it's not too much trouble allude to the SQL instructional exercise)
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Table Created
Allow us currently to embed a few qualities in the table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
The accompanying system relegates values from the above table to PL/SQL factors utilizing the SELECT INTO condition 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, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/
At the point when the above code is executed, it creates the accompanying outcome −
Customer Ramesh from Ahmedabad earns 2000
PL/SQL procedure completed successfully