Sunday, 23 April 2017

How to Declare Variable & Constant in PL/SQL Database Management System But not in SQL

--Variable & Constant--

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;

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;

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) );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ron', 32, 'America', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Rome', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Emma', 23, 'Manchester', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chibabva', 25, 'Mumbasa', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Henry', 27, 'london', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Katty', 22, 'Jamaica', 4500.00 );


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;


DECLARE  
-- constant declaration  
pi constant number := 3.141592654;  
-- other declarations   radius number(5,2);   
dia number(5,2);   
circumference number(7, 2);  
area number (10, 2);
BEGIN   
-- processing  
radius := 9.5;   
dia := radius * 2;   
circumference := 2.0 * pi * radius;  
area := pi * radius * radius;  
-- output  
dbms_output.put_line('Radius: ' || radius);  
dbms_output.put_line('Diameter: ' || dia);  
dbms_output.put_line('Circumference: ' || circumference);  
dbms_output.put_line('Area: ' || area);
END;

0 comments:

Post a Comment