Type Why type? • To store the single column type in a single variable When fetching data We don’t know the data type of column which we fetch until seeing the description of table.so,if we see the description of each column then time consume. It will not be efficient. To declare the variable in a efficient manner then use type which automatically assigns the data type of single column
Syntax: Variable name tablename.column name% type; How type should be used in pl/sql? In declaration part declare separate variable for each column So in INTO clause declare separate variables for each column While printing DBMS…..declare just variable name
--Simple program using type to print only one first name --where eid-101; declare i employees.first_name%type; j employees.department_id%type; begin select first_name,department_id into i,j from employees where employee_id=101; dbms_output.put_line(i||chr(10)||j); end; / OUTPUT:SQL> @f:\new\type.sql; Neena 90
Row type Why rowtype? • To store the entire rowtype in a single variable but we can’t print entire row Using single variable if you need to print entire row then use variable name. column name in DBMS……..(); example:dbms_output.put_line(i.first_name||chr(10)||i.department_id); •Don’t want to declare separate variables for each column •Single variable is enough to assign all column and also in fetching i.e. in select statement
Simple program to first name, department id where eid-101; declare i employees%rowtype; begin select * into i from employees where employee_id=101; dbms_output.put_line(i.first_name||chr(10)||i.department_id); end; / Output: SQL> @f:\new\rowtype.sql; Neena 90
difference b/n %type and %rowtype %type 1.declare sql->i employees.first_name%type; sql->j employees.department_id%type; we need to declare n number of variables for n number of columns
2.in select declare n no of columns to fetch sql->select first_name,department_id 3.In into clause n number of variables must be declared corresponding to n number of columns we fetch sql-> into i,j 4.While printing declare all the variables with pipe symbol(to concat) sql->dbms_output.put_line(i||chr(10)||j);
chr(10)->to enter i.e.\n in ‘c’ 5.To assign a single column to variable 6.Use type in cursors
%rowtype
ROW TYPE 1.Declare sql->i employees%rowtype; here we declare only one variable for n number of columns 2.in select clause use * to select all the columns sql->select * 3.Whereas in into clause only one variable is enough sql->into i 4.While printing declare Variable. Column name with pipe symbol(to concat) sql->dbms_output.put_line(i.first_name ||chr(10)||i.department_id); 5.To assign a entire row(i.e all columns) to variable Don’t use rowtype in cursors 6.Use rowtype in pl/sql block
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
ReplyDeleteType
ReplyDeleteWhy type?
• To store the single column type in a single variable
When fetching data We don’t know the data type of column which we fetch until seeing the description of table.so,if we see the description of each column then time consume. It will not be efficient.
To declare the variable in a efficient manner then use type which automatically assigns the data type of single column
Syntax:
ReplyDeleteVariable name tablename.column name% type;
How type should be used in pl/sql?
In declaration part declare separate variable for each column
So in INTO clause declare separate variables for each column
While printing DBMS…..declare just variable name
--Simple program using type to print only one first name --where eid-101;
ReplyDeletedeclare
i employees.first_name%type;
j employees.department_id%type;
begin
select first_name,department_id
into i,j
from employees
where employee_id=101;
dbms_output.put_line(i||chr(10)||j);
end;
/
OUTPUT:SQL> @f:\new\type.sql;
Neena
90
Row type
ReplyDeleteWhy rowtype?
• To store the entire rowtype in a single variable but we can’t print entire row
Using single variable if you need to print entire row then
use variable name. column name in DBMS……..(); example:dbms_output.put_line(i.first_name||chr(10)||i.department_id);
•Don’t want to declare separate variables for each column
•Single variable is enough to assign all column and also in fetching i.e. in select statement
Simple program to first name, department id where eid-101;
ReplyDeletedeclare
i employees%rowtype;
begin
select *
into i
from employees
where employee_id=101;
dbms_output.put_line(i.first_name||chr(10)||i.department_id);
end;
/
Output:
SQL> @f:\new\rowtype.sql;
Neena
90
difference b/n %type and %rowtype
ReplyDelete%type
1.declare
sql->i employees.first_name%type;
sql->j employees.department_id%type;
we need to declare n number of variables for n number of columns
2.in select declare n no of columns to fetch
sql->select first_name,department_id
3.In into clause n number of variables must be declared corresponding to
n number of columns we fetch
sql-> into i,j
4.While printing declare all the variables with pipe symbol(to concat)
sql->dbms_output.put_line(i||chr(10)||j);
chr(10)->to enter i.e.\n in ‘c’
5.To assign a single column to variable
6.Use type in cursors
%rowtype
ROW TYPE
1.Declare
sql->i employees%rowtype;
here we declare only one variable for n number of columns
2.in select clause use * to select all the columns
sql->select *
3.Whereas in into clause only one variable is enough
sql->into i
4.While printing declare
Variable. Column name with pipe symbol(to concat)
sql->dbms_output.put_line(i.first_name
||chr(10)||i.department_id);
5.To assign a entire row(i.e all columns) to variable
Don’t use rowtype in cursors
6.Use rowtype in pl/sql block
Query To display the hire date,first_name,last_name from employees and ordering as (sun,mon,tue.....)
ReplyDeleteselect to_char(hire_date,'d')||' '||hire_date days , first_name, last_name from employees
order by to_char(hire_date,'d') asc;
select hire_date, first_name, last_name, to_char(hire_date,'dy') from employees order by to_char(hire_date,'d')asc;
Delete