Composite Variable in PL/SQL

-

Composite Variable are also known as complex  variable used to create a variable consisting of many other component . It is of Basic two type;

1. User define type:

Such variable are to be created by user in declare section of a program , it syntax is given as follow:

TYPE type_name IS
(var1 datatype, var2 datatype,.......);

NOTE: 1-row at a time , no constraints.

After a type has been created for using it and object of same type must be created in declare section which can be done as follow:

OBJECTNAME typename;
After object has been created , it can be  used in begin section as objectname.varname;

2. System define :

Such variable are defined by database system which is again of two types.

a. %ColumnType
Such type can be used to create variable having data type from a column in the table. Even this variable has to be created in 
declare section.

Syntax:

VARNAME tablename.collumnname % TYPE;

example:

Eid salesrep.empid%TYPE;

b. %RowType
Such type can be used to create a variable which can hold one complete row from a table. Such variable also must be created in declare section.

Syntax:

row_var_name table_name % ROWTYPE;

example:

e salerep % ROWTYPE;

QUESTION: Write a program to illustrate use of complex variable .

DECLARE 
  TYPE emprecord  IS
  (
  eid salesrep.empid % TYPE, 
  ename salerep.name % TYPE
  );

  e emprecord;

  erecord salesrep % ROWTYPE;
  sales salerep.salesrep % TYPE;

BEGIN
SELECT sales INTO sales
FROM salesrep
WHERE empid=101;
DBMS_OUTPUT.PUT_LINE('SALES OF EMPLOYEE 101 IS'||sales);

SELECT empid, name INTO e 
FROM salerep
WHERE empid=101;
DBMS_OUTPUT.PUT_LINE('id & name of employee 101 is'||e.id||e.ename);

SELECT * INTO erec
FROM selesrep
WHERE empid=101;
DBMS_OUTPUT.PUT_LINE('name,sales and quota of employee 101                                 is'||erec.name||erac.sales||erac.quota);
END;
/


 
 

Comments

Post a Comment

Popular posts from this blog

Introduction to Python.

-
What is python? Python is an object-oriented programming language created by Guido Rossum in 1998. If you just beginning your programming career, python suits you best. With python you can do everything from GUI developement, web application, Machine learning, IOT, Game Developement etc. so it is general purpose high level programming language. It is very easy language,it is good for beginners. It is case sensitive language. Python is Dynamically Typed, Write less code do more. Introduction Easy to Learn and Use Python is easy to use and learn, it is user friendly and high level programming language. Expressive language Pyton language is more expressive means that is more understand and readable. Unique style Python is an interpreted language i.e interpreter execute the code line by line at a time . This makes debugging easy and thus suitable for beginners. Cross Platform Language Python can run equally on different platform such as windows, Linu...
Read more

Decision Making Statement.

-
Image
Conditional Statement Decision making of an anticipation of condition occuring by execution of program and dpecific action is taken according to condtion. Following is a general form of typical decision making structure found in most programming language. Python programming language uses any non-zero or non-null value as true and if it is either zero or null then it is assume false value. if Condition: In python the block of statement is executed if the condition is true. Syntax if (condition): Block(s) Example var1=100 if var1: print('i got an true value') print(var1) output i got an true value 100 Question: Write a program that prompt user to enter two integer values, print the message "equal" if both entered value are equal a=int(input("Enter first number")) b=int(input("Enter second number")) if a==b: print("equal") output Enter fir...
Read more