Scribd
Upload
6 views

PL SQL

Uploaded by

alamnoor41950
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views

PL SQL

Uploaded by

alamnoor41950
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

Analytic functions calculate an aggregate value based on a group of rows.

Use analytic functions to compute moving averages, running totals, percentages or


top-N results within a group.
Analytic func is relatively
Analytic fun helps us avoid self join.

Analytical functions are similar to aggregate functions,but they provide much more
the functions are applied on partition independently
lead function and it lets your query from more than one row in a table without
using a join condition.
we have function avg,min,max,lead,sum
eg.select avg(salary) over (partition by department_id) as avg_dep_sal,a.* from
employees a order by employee_id;

nvl function:(nvl,nvl2,coalesce)
these function are use to handle null values in the table.
nvl2 used check with condition nvl2(commision,salary+salary*commision,salary) as
total_salary.
so here if commision is there run first one otherwise just show the salary.
eg: select name,salary,commision,salary*nvl(commision,0) as
commision_amount,nvl2(commision,salary+salary*commision,salary) as
total_salary from employee.

coalesce:it is also work just like checking condition


eg, coalesce(dept_id,commision,salary,123) will execute first not null values.

sql cuser:
A curser is temparary work area created in system memory.
>curser pointer pick up the single row from the result,process the row,After
process pointer moves on next row,
it goes on untill of last row of result.
>it allows you to process individual row returned by query.
>A sql curser is a set of rows together with a pointer the identifies a current
row.
>A curser can hold more than one row,but can process only one row at a time.
>The sets of row the curser holds is called as active set.
> A Curser is the pointer to the context area.PL/SQL controls the context area
through a curser.A curser holds the rows(one or more)
returned by sql statement.the set of rows that curser holds is reffered to as
the active set.

there are two types of curser


Implicit curser-for DML operation(insert,update,delete) it internally system will
create implicit curser as well as close it.also for select(single row).Programmer
cannot control implicit curser and the information in it.
In PL/SQL,you can refer to the most recent implicit curser as the SQl curser,which
always has attributes such as %FOUND,%ISOPEN,%NOTFOUND, and %ROWCOUNT.

Explicit Curser-It is Programer defined cursors for gaining more control over the
context area.An Explicit curser should be defined in the
declaration section of the PL/SQL block.it is created on SELECT statement
which returns more then one row
for this you have to create explicit curser and close also to delete from the
memory.
syntax: CURSER curser_name IS select_statement.
eg: steps need to do are: DECLARE curser-: DECLARE EMP_CURSER CURSER For Select
Rollno,StudentName,Marks from Student_Details.
Open: OPEN EMP_CURSER
Fetch: FETCH EMP_CURSER
INTORollno,StudentName,Marks
Close : CLOSE EMP_CURSER
Deallocate.

Fetch data from cursor: there are 6 method to access data from curser.
FIRST,LAST,NEXT,PRIOR,ABSOLUTE,RELATIVE
eg: DECLARE CURSER c_emp is select e_id,e_name from emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp into c_id,c_name;
EXIT WHEN c_emp%notfound;
dbms_output.put_line(c_id || '' || c_name);
END LOOP;
CLOSE c_emp;
END;

Table Partiton:
Table partitioning is a way to divide a large table into smaller, more manageable
parts
without having to create seprate table for each part.

diffrence Delete and truncate?


> Delete: comes under DML(Data manipulation language)
'Delete' removes some or all rows
It does not free the space containing the table.
The transaction log will still have the deleted rows.
Slower than the truncate.because before deleting it is going to
save into log file,
so that deleted data can be recoverd using rollback command.
eg: DELETE * from employee where id=100;

> Truncate: comes under DDL(Data defination language)


'Truncate' removes all the rows from the table.
It free up the space containing the table.
It is faster than Delete.
no 'Where' clause
eg: Truncate Table Employee;

> DROP: comes under DDL


removes tables and its structure from the database.
cannnot be rolled up
eg: DROP TABLE Employee;

Control structure and loops:


There are 3 category of control structure:
1. Conditional control
if-then,if-then-else,if-then-ifelse

2.Iterative control
loop,while loop,for loop

3.Sequential control
GOTO statement
Record: A collections of related fields used as a single unit is called record.
also called row.
it contains multiple fields/set of fields.

You might also like