PL / SQL
SQL does not have any procedural capabilities such looping and branching nor does it have any conditional checking capabilities vital for data testing before storage. For this entire oracle provides PL / SQL. PL/SQL stands for procedural language / SQL. As its name implies, PL/SQL extends SQL by adding constructs found in other procedural languages, such as
Variables and types
Control structures such as IF - THEN - ELSE statements and loops.
Procedures and functions.
Features of PL/SQL
Improved performance : In SQL for each statement call being made to the DBA. It slows down the process considerably.
Portability : Applications written in PL/SQL are portable to any computer hardware and operating system where oracle is operational.
Normal plus additional data types. Boolean, real
TO DISPLAY OUTPUT ON SCREEN TYPE 'SET SEVEROUTPUT ON'
TO END PROGRAM END WITH '/ '
PL/SQL Block Structure
DECLARE
DECLARATIVE SECTION - PL/SQL VARIABLES, TYPES, CURSORS AND LOCAL SUBPROGRAMS GO HERE. - Optional
BEGIN
EXECUTABLE SECTION - PROCEDURAL AND SQL STATEMENTS GO HERE. THIS IS THE MAIN SECTION OF THE BLOCK, AND THE ONLY ONE THAT IS REQUIRED.
EXCEPTION
EXCEPTION HANDLING SECTION - ERROR HANDELING STATEMENTS GO HERE.
END;
Declare
Num1 number(2):=&num1;
Num2 number(2):=&num2;
Result number(3);
Begin
Result:=(num1+num2);
Dbms_output.put_line(result);
End;
Begin
Dbms_output.put_line(10+20);
End;
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
C NUMBER(2);
BEGIN
DBMS_OUTPUT.PUT_LINE('CURRENT A = ' || A || ' AND CURRENT B = ' || B);
C:=A;
A:=B;
B:=C;
DBMS_OUTPUT.PUT_LINE('NEW A = ' || A || 'AND NEW B = ' || B);
END;
Information is transmitted between PL/SQL and the database with variables. A variable is a storage location, which can be read from or assigned to by the program. Every variable has a specific type associated with it. PL/SQL variables can be of the same type as database columns.
Additional data types : Binary_Integer & Boolean
ADDITION OF TWO NUMBERS
DECLARING CONSTANTS
DBMS_OUTPUT.PUT_LINE
SET SERVEROUTPUT ON
|| OPERATOR
:= ASSIGNMENT OPERATOR
DECLARE
A NUMBER(2):=20;
B NUMBER(3):=300;
C NUMBER(3);
BEGIN
C:=A+B;
DBMS_OUTPUT.PUT_LINE('ANSWER IS ' || C);
END;
ACCEPTING INPUT FROM USER
DECLARE
A VARCHAR2(20):='&NAME';
BEGIN
DBMS_OUTPUT.PUT_LINE('YOUR NAME IS ' || A);
END;

ODD / EVEN PROGRAM
ACCEPTING NUMERIC VALUE FROM USER
IF THEN - ELSE STATEMENT
USE OF MOD()
DECLARE
NUM NUMBER(2):=#
BEGIN
IF MOD(NUM,2)=0 THEN
DBMS_OUTPUT.PUT_LINE('THE NUMBER IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ODD');
END IF;
END;
ASSIGNMENT : WRITE PROGRAM TO FIND OUT MINOR OR MAJOR. (AGE LIMIT 18)
USE OF USER INPUT FOR DATA MANIPULATION
DECLARE
NAME VARCHAR2(20):='&NAME';
BEGIN
UPDATE EMP SET SAL=SAL+500 WHERE ENAME=NAME;
END;


NESTED IF THEN - ELSE USING ELSIF
EXAMPLE 1
DECLARE
NAME VARCHAR2(20):='&NAME';
JOB VARCHAR2(20):='&JOB';
BEGIN
IF JOB='MANAGER' THEN
UPDATE EMP SET SAL=SAL+100 WHERE ENAME= NAME;
ELSIF JOB='SALESMAN' THEN
UPDATE EMP SET SAL=SAL+200 WHERE ENAME= NAME;
ELSE
UPDATE EMP SET SAL=SAL+10 WHERE ENAME= NAME;
END IF;
COMMIT;
END;
EXAMPLE 2
DECLARE
S1 NUMBER(2):=&FIRST ;
S2 NUMBER(2):=&SECOND;
S3 NUMBER(2):=&THIRD ;
TOTAL NUMBER(3);
AG NUMBER(3);
BEGIN
TOTAL:=(S1+S2+S3);
DBMS_OUTPUT.PUT_LINE('THE TOTAL IS '|| TOTAL);
AG:=(TOTAL/3);
DBMS_OUTPUT.PUT_LINE('THE PERCENTAGE IS '|| AG);
IF AG>=70 THEN
DBMS_OUTPUT.PUT_LINE('DISTINCTION');
ELSIF
AG<70 AND AG>=60 THEN
DBMS_OUTPUT.PUT_LINE('FIRST CLASS');
ELSIF
AG<60 AND AG>=50 THEN
DBMS_OUTPUT.PUT_LINE('SECOND CLASS');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
%type
FETCHING TABLE VALUES IN LOCAL VARIABLES
DECLARE
NAME EMP.ENAME%TYPE:='&NAME';
WORK EMP.JOB%TYPE;
SALARY EMP.SAL%TYPE;
BEGIN
SELECT JOB INTO WORK FROM EMP WHERE ENAME=NAME;
DBMS_OUTPUT.PUT_LINE(NAME || ' WORKS AS ' || WORK);
IF WORK='PRESIDENT' THEN
SELECT SAL INTO SALARY FROM EMP WHERE ENAME=NAME;
DBMS_OUTPUT.PUT_LINE(SALARY);
END IF;
END;
LOOPING CONSTRUCTS - WHILE
DECLARE
A NUMBER(2):=&A;
I NUMBER(2):=1;
BEGIN
WHILE (I<=10 )
LOOP
DBMS_OUTPUT.PUT_LINE(I*A);
I:=I+1;
END LOOP;
END;
While <Condition>
Loop
Executable statements;
End loop
LOOPING CONSTRUCTS - FOR
DECLARE
A NUMBER(2):=&A;
I NUMBER(2):=1;
BEGIN
FOR I IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(I*A);
END LOOP;
END;
LOOPING CONSTRUCTS - FOR - STEP
BEGIN
FOR I IN 1..10 STEP 2
LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;

PL / SQL ASSIGNMENTS
1. Fibonacci Series : 0 1 1 2 3 5 8 13
2. Prime Numbers between 1 to 100
3. Find out only odd numbers from 1 to 200
4. Check whether the number is Prime or not
5. Checking whether the number is an Armstrong number.
6. Validation program : Checking whether the input is correct or not - Character only or number only.
7. Write PL/SQL Block to get the deptno of the employee whose empno is taken from keyboard. If deptno = 20 then update sal by 10% and if deptno = 30 then update salary by 15%.
8. Accept empno from keyboard and display his name and deptno.
9. Accept empno from keyboard and update salary of managers to 3000.
10. Accept 3 numbers from user and find out Maximum and Minimum of it.
11. Accept a number from user and give addition of digits. 1234 = 10
12. Accept job from user and accordingly show its count i.e. Zero, Once, More than one
DECLERE
ENUM EMP.EMPNO%TYPE:=&ENUM;
NAME EMP.ENAME%TYPE;
DNO EMP.DEPTNO%TYPE;
BEGIN
SELECT ENAME,DEPTNO INTO NAME,DNO FROM EMP
WHERE EMPNO=ENUM;
DBMS_OUTPUT.PUT_LINE('YOUR NAME IS ' || NAME || ' AND
END;
Cursors
It is a answer to a multi row query
Memory area where one can access or process rows.
Cursor is of two types
1. Implicit (system defined)
2. Explicit (user defined)

DECLARE
CURSOR <CURSOR NAME> IS <ANY SELECT QUERY>;
OTHER DECLARATIONS;
BEGIN
OPEN <CURSOR NAME>;
FETCH <CURSOR NAME> INTO <VARIABLES>;
PROGRAM BODY;
CLOSE <CURSOR NAME>;
END;
Program to insert first 5 records (salary wise) in table temp
DECLARE
CURSOR C1 IS SELECT ENAME, JOB FROM EMP ORDER BY SAL DESC;
I NUMBER(3);
EN EMP.ENAME%TYPE; EJOB EMP.JOB%TYPE;
BEGIN
OPEN C1;
FOR I IN 1..5
LOOP
FETCH C1 INTO EN, EJOB;
INSERT INTO TEMP VALUES (EN,EJOB);
END LOOP;
CLOSE C1;
END;
Example 2 : Select only managers from employee table and insert those records in new table called temp with increase salary by 1000. Insert only ename and salary. Assume that temp table is in existence.
Declare
Cursor M1 is select ename,sal from emp where
job='MANAGER';
I NUMBER(1);
J NUMBER(2);
EN EMP.ENAME%TYPE;
ESAL EMP.SAL%TYPE;
begin
SELECT COUNT(*) INTO J from emp where
job='MANAGER';
OPEN M1;
FOR I IN 1..J
LOOP
FETCH M1 INTO EN,ESAL;
INSERT INTO TEMP VALUES(EN,ESAL+1000);
END LOOP;
CLOSE M1;
end;

Along with cursors one can use following 4 attributes
%notfound
%found
%rowcount
%isopen
Declare
cursor c1 is select ename, job from emp order by sal desc;
name emp.ename%type;
post emp.job%type;
begin
open c1;
loop
fetch c1 into name,post;
exit when c1%notfound or c1%rowcount =10;
insert into temp values (name,post);
end loop;
close c1;
end;

Cursor For Loop
In most situations that require an explicit cursor, you can simplify coding by using a cursor For loop instead of the open, fetch and close statement. A cursor for loop implicitly declares its loop index as a %rowtype record, opens a cursor, repeatedly fetches rows of values form the active set into fields in the record, and closes the cursor when all rows have been processed.
Declare
Salary_total real:= 0.0;
Cursor c1 is select ename,sal,hiredate,deptno from emp;
Begin
For emp_rec in c1 loop
salary_total := salary_total + emp_rec.sal;
Dbms_output.put_line(salary_total);
Dbms_output.put_line(emp_rec.ename);
End loop;
End;

For Update statement
Example 1
Declare
Cursor c1 is select ename from emp where sal < 1000 for update of sal;
Begin
For emp_rec in c1 loop
Update emp set sal = sal + (sal*.1) where current of c1;
End loop;
End;
Example 2
To promote clerks who earn more than 1000 and increase their salary by 10%
Declare
Cursor c1 is select empno, job from emp where job='CLERK' and sal > 1000
for update of job;
Begin
For emp_rec in c1 loop
Update emp set job = 'Chintu', sal = sal+(sal*.1) where current of c1;
End loop;
End;

Passing Parameters to Cursor
Example 1
Declare
Cursor c1(dnum number) is select sal,comm from emp where deptno=dnum for update of sal;
dnum emp.deptno%type:=&dnum;
Begin
For emp_rec in c1(dnum) loop
Update emp set sal=99 where current of c1;
End loop;
End;
Example 2
Declare
Cursor c1(tjob varchar, tsal number) is select empno, job from emp where job=tjob and sal > tsal for update of sal;
Begin
For emp_rec in c1('MANAGER',1000) LOOP
Update emp set job='chintu', sal=sal*1.1 where current of c1;
End loop;
End;
Declare
Cursor c1(tjob varchar2) is select ename,sal from emp where job=tjob;
tjob emp.job%type:='&Job';
Begin
For emp_rec in c1(tjob)
loop
insert into temp values (emp_rec.ename,emp_rec.sal);
End loop;
End;
Exception Handling
Error handling routines - To check run time errors, Design time errors and System errors.
Exceptions are of two types
Implicit - Raised by system
Explicit - Written and raised by user.
Example
Declare
Pay_ratio number(7,2);
Begin
Select (sal/comm) into pay_ratio from emp where empno=6666;
Dbms_output.put_line(pay_ratio);
Exception
When zero_divide then
Dbms_output.put_line('Comm is Zero');
When no_data_found then
Dbms_output.put_line('No such Employee');
When others then
Dbms_output.put_line('Invalid Values');
End;

Explicit Exception ( Declare - Raise - Action )
Declare
out_of_stock EXCEPTION;
St item.stock%type;
Begin
Select stock into st from item where item_code = 100;
If st < 10 then
raise out_of_stock;
else
update item set status=Null where item_code = 100;
end if;
Exception
When out_of_stock then
Update item set status = '*' where item_code = 100;
End;
GOTO Statement
It changes the flow of control within the PL/SQL block.
Declare
eno emp.empno%type:=&eno;
post emp.job%type;
salary emp.sal%type;
Begin
Select job,sal into post,salary from emp where empno=eno;
If post = 'CLERK' then
Goto new_basic;
Else
Dbms_output.put_line('Zamtai');
End if;
<<new_basic>>
Dbms_output.put_line(salary + 99);
End;

Sub routines in Oracle - Procedures and Functions
These are named PL/SQL Blocks
Accepts parameter and can be invoked as & when required
It reduces code, which is manageable
Provide extensibility
Reusable and maintainable
Procedure (Stored as Object) - Accept empno and comm and hike it by 10%
Create procedure <pname> (parameters)
Create procedure hikecomm (eno emp.empno%type, commn emp.comm%type) is
Comm_null Exception;
Begin
If commn = 0 then
Raise comm_null;
Else
Update emp set comm = commn + (commn*0.1) where empno = eno;
End if;
Exception
When comm_null then
Dbms_output.put_line('Commission Null ERROR');
When no_data_found then
Dbms_output.put_line('No data ERROR');
End;
Main Block (To call the predefined routine)
Declare
enum emp.empno%type := &enum;
cm emp.comm%type;
Begin
select nvl(comm,0) into cm from emp where empno=enum;
hikecomm(enum,cm);
end;
User defined Functions
create function netsal (eno emp.empno%type) return number is
pay emp.sal%type;
commn emp.comm%type;
netpay emp.sal%type
begin
select sal, nvl(comm,0) into pay,commn from emp where empno=eno;
netpay:=(pay+commn);
return netpay;
end;
Main Block
Declare
eno emp.empno%type:=&eno;
netsalary emp.sal%type;
Begin
netsalary:=netsal(eno);
dbms_output.put_line('Your net salary is' || netsalary);
end;
Find out the person who is earning Maximum
select deptno from emp
group by deptno having deptno=(select deptno from emp
group by deptno having count(*)=(select max(count(*)) from emp group by deptno));
TO WHOM NOBODY IS REPORTING
select empno, ename,job from emp
where empno not in
(select empno from emp
where empno in
(select mgr from emp group by mgr));

To whom Maximum people are reporting
select empno,ename from emp where empno=
(select mgr from emp group by mgr having count(*)=
(select max(count(*)) from emp group by mgr));

SQL Plus
Quit
Help [Command name]
Editing Commands
L = To list the current SQL command
L Last = List last line
C/T1/T2 = CHANGE TEXT1 TO TEXT2 OF CURRENT LINE
SELECT * FROM DEPT
WHERE DEPTNO=20;
L LAST
C/20/10
/
C/T1 = DELETE TEXT1
A T1 : APPEND TEXT1 TO END OF LINE
SELECT * FROM EMP;
A WHERE ENAME='JAMES';
/
Del = Delete a line
C buf = clear buffer

Ttitle center 'My First Report' skip 2
Btitle right 'Continued …' skip 2
Ttitle on / off
Btitle on / off
Ttitle center 'Monthly Analysis' skip 2 -
Center '11 January 2001' skip 2 -
left 'page:' Format 999 sql.pno skip 2
Ttitle center 'Monthly Analysis' skip 2 -
Center '11 January 2001' skip 2 -
left 'page:' Format 999 sql.pno skip 2 -
skip page
FORMATING COLUMNS
Syntax : COLUMN Column_name HEADING column_heading FORMAT format
Column sal heading salary format 9999.99
Column comm heading commission format $9999.99
Select sal,comm from emp;
COLUMN DEPTNO HEADING Department
Column ename heading Employee
Select empno,ename from emp;
set pagesize 65
column deptno heading 'Department | Number'
column dname heading 'Department | Name'
column ename heading 'Employee | Name'
column sal format 9,999.00 heading 'Monthly | Salary'
column comm like sal heading 'Monthly | Commission'
break on deptno
Select emp.deptno, dname, sal,comm
From emp,dept
Where emp.deptno=dept.deptno
Order by deptno;
Communicating with the user
Prompt [text]
Sends a specified text or blank line to the user screen.
Prompt 'Enter your age : '
Accept : reads a line of input and stores it in a given user variable
Accept variable [datatype] prompt text
Accept c number prompt enter your age : -
Input
Prompt Enter a valid Employee Number
Prompt For Example : 7123
Accept ab number prompt Employee Number :
Select ename,mgr,job,sal from emp
Where empno = & ab;

select * from emp where empno=&empnum;
set Pause on
Spool on : starts spooling the result in spool file
Spool out : stops spooling the contents
Spool <filename>
Spool temp
Table space : it is a partition or logical area of storage in a database that directly corresponds to one or more physical data files. Alter table space is a command that can be used to change the mode of the table space, to read-only or read-write.
Create tablespace tbssp
Datafile filename size
Creating database object
Create table stocks
(item varchar2(30),
quantity number(4),
storage (initial 1M next 400K minextents 1 maxextents 20 pctincrease 50);
initial and next specify the size of the first and next extents, respectively. Minextents specifies the total number of extents allocated when the segment is created. The parameter pctincrease specifies the percent by which each extent after the second grows over the previous extent.
An extent is the number of contiguous data blocks that oracle allocates for an object when more space is necessary for the object's data. The group of all the extents is called as segment.



VARUN TRAVELS
1950 SADASHIV PETH
PUNE
EMPLOYEE ATTENDANCE REPORT
PAGE : 1
EMPLOYEE JOIN DATE POST SALARY DEPARTMENT
1 01-JAN-90 CLERK $4000 RESEARCH

Creating users in oracle
In the Oracle8 Navigator 8 - Personal Oracle 8 - Local Database - User Folder - New User Icon.
Changing Password
SQL> passw scott
Changing password for scott
Old password: *****
New password: *****
Retype new password: *****
Password changed
SQL>

Viewing Index
Oracle navigator 8 (dbl Click) - Personal Oracle 8 (dbl Click) - Local Database (dbl Click) - Table (dbl Click) - Select Table Name (dbl Click) - Index (dbl Click) - New index properties (Icon) - Order option is Compulsory.
Set feedback on / off
Show all
Set time on
Set underline - Current - Off - *
Cursor Assignments
To Find Max Salary for the particular Department without using max().
DECLARE
DNO EMP.DEPTNO%TYPE:=&NO;
CURSOR C1 IS SELECT SAL FROM EMP WHERE DEPTNO=DNO;
SAL1 EMP.SAL%TYPE:=0;
BEGIN
FOR EMP_REC IN C1 LOOP
IF EMP_REC.SAL>SAL1 THEN
SAL1:=EMP_REC.SAL;
END IF;
EXIT WHEN C1% NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('MAX OF SAL IN DEPT'|| DNO ||':-'||SAL1);
END;
To Find average Salary for the particular Department without using avg().
DECLARE
CO NUMBER(2);
DP EMP.DEPTNO%TYPE:=&DEPT;
SALSUM NUMBER(7,2):=0;
AVQ NUMBER(7,2):=0;
CURSOR AV1 IS SELECT SAL FROM EMP WHERE DEPTNO=DP;
BEGIN
SELECT COUNT(*) INTO CO FROM EMP WHERE DEPTNO=DP;
FOR EMP_REC IN AV1 LOOP
SALSUM:=SALSUM+EMP_REC.SAL;
END LOOP;
--AVQ:=(SALSUM/CO);
DBMS_OUTPUT.PUT_LINE(SALSUM/CO);
END;
To Find Min Salary for the particular Department without using min().
DECLARE
DNO EMP.DEPTNO%TYPE:='&DNO';
CURSOR C1 IS SELECT SAL FROM EMP WHERE DEPTNO=DNO;
MIN1 EMP.SAL%TYPE;
BEGIN
SELECT MAX(SAL) INTO MIN1 FROM EMP;
FOR EMP_REC IN C1 LOOP
IF EMP_REC.SAL<MIN1 THEN
MIN1:=EMP_REC.SAL;
EXIT WHEN C1% NOTFOUND;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(MIN1);
END;
Triggers
It is a stored procedure that gets fired when an insert update or delete statement is issued against the associated table. It is used to enforce complex integrity constraint.
Syntax : create or replace trigger <trigger name>
[before/after] [insert/update/delete] on <table name>
[for each statement / for each row] [when <condition>] ;
The database trigger has normally three parts. Trigger statement, trigger body and trigger restriction.
Trigger statement : It fires the trigger body and also specifies the table name to which the trigger is associated.
Trigger body : It is the PL/SQL block which gets executed when triggering statement is issued.
Trigger restriction : Use of when clause and the condition.
Types of trigger
Before : fires the trigger before execution of triggering statement.
After : fires the trigger after execution of triggering statement.
For each row : trigger gets fired once per row.
For each statement (default)
Example
Create or replace trigger ployees
Before insert on emp for each row
Declare
eno emp.empno%type;
Begin
Select empno into eno from emp where empno < 7788;
If eno= 7788 then
Raise_application_error (-20001, ' Enter some other number');
End if;
End ;
Insert into emp (empno, ename,deptno) values(7788,'umesh',10);
Packages
Packages are PL/SQL constructs that allow related objects to be stored together. Packages consist of 2 parts, "Packages Specification" and "Package Body". Each of them is stored separately in data dictionary. Package specification is used to declare functions, procedures that are part of the package. It also contains variable and cursor declarations, which are used by the function and procedures. Any object declared in a package specification can be referenced from other PL/SQL blocks so packages provide global variables to PL/SQL.
Package body contains the function and procedure definitions, which are declared in the package specification. Package body is optional. If package specification does not contain any procedure or functions and contains only variables and cursor declarations then the body need not be present.
All functions and procedures declared in the package specification are accessible to all users who have permissions to access the package. Users cannot access subprograms, which are defined in the package body but not declared in the package specification
Syntax - Package Specification
Create package package_name as
Variable_declaration;
Cursor_declaration;
Function_decleration;
Procedure_declaration;
End package_name;
Syntax - Package Body
Create package body package_name as
Variable_declaration;
Cursor_declaration;
Procedure Proc_name (………….) is
Begin
PL/SQL Statements;
End proc_name;
Function func_name (…………) is
Begin
PL/SQL Statements;
End func_name;
End package_name;
For calling the packaged functions and procedures they have to be prefixed with package name.
package_name.proc_name(parameters)
package_name.func_name(parameters)
Abstract Data types - structures
One can use abstract data types to group related columns into objects. For Example columns that are part of address information can be grouped into an addr_ty data type via the create type command.
Create type addr_ty as object
(street varchar2(50),
city varchar2(25),
state char(2));
create type person_type as object
(name varchar2(25),
address addr_ty);
create table client
(client_id Number(3),
person person_type);
insert into client values (1, person_type ('anyname', addr_ty ('streetvalue', cityvalue', 'st')));
select client_id, person.name from client;
select person.name, person.address.city from client
where person.address.city like 'N%';
create index c1 on client (person.address.city);
Large Objects LOBs)
You can use a long data type to store character up to 2GB in length per row; the long raw data type provides storage for long binary data. If you use one of the new data types to store large objects (LOBs), you take advantage of new capabilities for viewing and manipulating the data.

Create table Offer
(offer_id number(10) primary key,
offer_name varchar2(25),
short_description varchar2(1000),
offer_text CLOB,
budget BLOB,
Cover_letter Bfile);
Insert into offer values
(1,'Arjun',null,'this is the text of a offer to clear arjun filed', EMPTY_BLOB,null);
insert into offer (offer_id,cover_letter) values (2,bfilename('offer_dir','p2.doc'));

User defined records
Objects of type RECORD are called "records". They have uniquely named fields, which can belong to different data types. For example suppose you have different kinds of data about an employee such as name salary hire date and so on. This data is dissimilar in type but logically related. A record that contains such fields as the name, salary, and hire date of an employee would let you treat the data as a logical unit.
Syntax
TYPE type_name IS RECORD
(field_name1 datatype(size) [not null],
field_name2 datatype(size) [not null]);
Declare
Type deptrectyp is record
(deptno number(2) not null := 20,
dname dept.dname%type,
loc dept.loc%type);
Once you define type "deptrectyp", you can declare records of that type, as follows
Dept_rec deptrectyp;
A record can be initialized in its declaration, as this example
Declare
Type timetyp is record
(second number:=0,
minute number:=0,
hour number:=0);
Declare
Type deptrectyp is record
(deptno number(2),
dname dept.dname%type,
loc dept.loc%type);
dept_rec deptrectyp;
begin
select deptno,dname,loc into dept_rec from dept where loc='NEW YORK';
dbms_output.put_line('The department no is ' ||dept_rec.deptno);
dbms_output.put_line('The department is ' ||dept_rec.dname);
dbms_output.put_line('The department location is ' ||dept_rec.loc);
end;
Rename Table
Rename emp to employee;
Listing Index - DBA Commands
Select index_name, Index_type, uniquness
From dba_indexes
Where table_owner = 'schema_name' and table_name = 'table_name';
Select column_name,column_position
From dba_ind_columns
Where index_owner = "schema_name" and
Index_name = 'index_name'
Order by column_position;
PL/SQL Tables
Objects of type TABLE are called PL/SQL tables where are modeled on (but not the same as) database tables. PL/SQL tables use a primary key to give you array-like access to rows. The size of a PL/SQL table is unconstrained. That is the number of rows in a PL/SQL table can increase dynamically.
PL/SQL tables must be declared in two steps. First you define a TABLE type then declares PL/SQL tables of that type. You can declare TABLE types in the declarative part of any block, subprogram, or package using the following syntax.
TYPE type_name is table of
{column_type | variable%type | table.column%type [not null]
index by binary integer;
declare
type jobtypetable is table of varchar2(14)
index by binary_integer;
job_tab jobtypetable;
begin
job_tab(1):='CLERK';
job_tab(1):=20;
job_tab(2):='manager';
job_tab(3):='analyst';
if job_tab(5)='CLERK' then -- no_data_found (as second tab is manager)
dbms_output.put_line('Correct Answer');
end if;
exception
when no_data_found then
dbms_output.put_line('No data error');
end;
Varying Arrays
It help you to store repeating attributes of a record in single row. The subscripts have a fixed lower bound of 1 and an extensible upper bound.
This array can be created using abstract daya types or oracle standard data types. One can create a new data type desg_ty that has only one column.
Create type desg_ty as object
(desg_name varchar2(10));
/
create type desgs_va as varray(5) of desg_ty;
/
The above command creates a varying array names desg_va to hold maximum of 5 values per record. This is done by using the as varry(5) clause. You can then create dep_desg table as follows
Create table dep_desg
(dname varchar2(25) primary key,
desgs desgs_va);
Describing varying arrays
Desc dep_desg
Name Null? Type
-------- -------- --------
DNAME NOT NULL VARCHR2(25)
DESGS DESGS_VA
To view he structure of desgs column uses the USER_TYPES data dictionary view.
Select typecode, attributes from user_types where type_name = 'desgs_va';
Type Code Attributes
-------- --------
collection 0
Inserting data into varying arrays
Insert into dep-desg values ('production',desgs_va(desg_ty ('MANAGER'),
Desg_ty ('ASST. MGR'),
Desg_ty ('SR. ENGR),
Desg_ty ('JR. ENGR')));
1 row created.
UPDATE EMP SET SAL=SAL+99 WHERE SAL BETWEEN &A AND &B;

Advanced PL/SQL
1. This block illustrates a numeric FOR loop.
BEGIN
FOR v_LoopCounter IN 1..50 LOOP
INSERT INTO emp (empno)
VALUES (v_LoopCounter);
END LOOP;
END;
/
2. This procedure illustrates a stored procedure.
CREATE OR REPLACE PROCEDURE PrintEmployee(
p_job IN emp.job%TYPE) AS
CURSOR c_emp IS
SELECT ename, mgr
FROM emp
WHERE job = p_job;
BEGIN
FOR v_empRec IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(v_empRec.ename || ' ' ||
v_empRec.mgr);
END LOOP;
END;
/
MAIN BLOCK
BEGIN
PrintEmployee('MANAGER');
END;
/
3. This block contains a simple loop.
DECLARE
v_LoopCounter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO emp (empno)
VALUES (v_LoopCounter);
v_LoopCounter := v_LoopCounter + 1;
EXIT WHEN v_LoopCounter > 50;
END LOOP;
END;
/
4. This file contains commands which can be executed from SQL*Plus.
Syntax :
BEGIN
FOR v_Count IN 1..10 LOOP
INSERT INTO temp_Table (num_col, char_col)
VALUES (v_Count, 'Hello World!');
END LOOP;
END;
/
Example
BEGIN
FOR v_Count IN 1..10 LOOP
INSERT INTO emp (empno, ename)
VALUES (v_Count, 'umesh!');
END LOOP;
END;
/
To add a snapshot to a project
1. On the left side of the Oracle8 Navigator, click the database connection to the remote database that contains the table you want to create a snapshot of. A list of the database objects folders in that database appears.
2. Double-click the Table folder for that database. A list of tables appears on the right side of the Navigator.
3. Drag the table you want to create a snapshot of to the desired project on the left side of the Navigator. The Copy Table to Project Dialog Box appears.
4. Click Snapshot, then click OK. The Create Snapshot Dialog Box appears.
5. Click either Updatable or Read Only.
6. Click OK. The Password Dialog Box appears.
7. Enter the owner's password for the table and click OK. A snapshot of the table is created in the local database and added to the project.

LOCKS
Types of Locks : it is mechanism which is used to prevent data inconsistency as multiple users are sharing the same data.
Row level lock : for update clause : row is locked exclusively so other users can not modify the row. Once the transaction is committed or rollback then automatically lock is released.
Select * from emp where ename='SCOTT' for update of job,sal;
Above command locks the rows in which ename is scott.
Table level lock
Syntax for locking the table : lock table <tablename> in <lock mode>
Lock table emp in share mode; : Allows other users to only query but not insert, update or delete rows in table.
Lock table emp in share update mode; : It locks rows that are to be updated in a table. It permits other users to concurrently query insert update or even lock other rows in the same table.
Lock table emp in exclusive mode; : it allows the other users to only query but not insert, update, delete. It is similar to share lock but only one user can place an exclusive lock on a table at a time, whereas many users can place a share lock on the same table at the same time.
Nowait : in the above case (exclusive mode) other users have to wait indefinitely until the usr who has initially locked the table issues a commit or rollback. This delay could be avoided by appending nowait clause to lock table statement.
Lock table emp in exclusive mode nowait;

Table partitioning
In oracle tables can be partitioned and stored in different locations as per requirement. A single logical table an be split into a number of physically separate pieces based on ranges of key values. Each part of the table is called as partition. Although the partitions are held and managed independently, they can be queried updated by reference to the name of the logical table. Oracle provides partition transparency. There is difference between table and partitioned table. Because a non-partitioned table can not be partitioned later. One can access and manipulate data in one partition even if some or all of the other partitions are unavailable.
Advantages of partitioning
1. It reduces possibility of data corruption.
2. Backup and recovery of each partition can be done independently.
3. Easy to control.
4. Better management of disk space and processing time.
5. Reduction in number of failures.
6. Reduction in downtime for scheduled maintenance.
7. Partition independence allows concurrent users to data manipulation
8. Partitions can be altered, dropped, rebuilt, merged and truncated. Partitions can not have synonyms.
Disadvantage
1. One can not use user defined data types in partitioned tables.

Syntax :
Create table <Table Name> (column1_name data type(size), column2_name data type(size)) partition by range (column name)
(partition <partition name> values less than <value>,
partition <partition name> values less than <value>);
Example :
Create table dummy
(rollno number(4) primary key,
sname varchar2(30),
bdate date) partition by range (rollno)
(partition d1 values less than (20), partition d2 values less than (60));
Inserting records in a partitioned table
Insert into dummy values(10,'abc','12-jan-01');
Insert into dummy values(30,'xyz','13-feb-01');
Insert into dummy values(50,'pqr','14-mar-01');
Selecting data from partitioned table
Select * from dummy partition(d1);
Select * from dummy partition(d2);
Select * from dummy;
A table can be partitioned on more than one column
Create table Order
(Ono varchar2(4) primary key,
supp_code varchar2(3) references supplier,
amount number(7,2)) partition by range (Ono,supp_code)
(partition O1 values less than ('O20', 'S10'), partition O2 values less than ('O30', 'S30'));
Partition Management
1. Moving partitions : Move partition clause is used to move a partition from a most active tablespace to a different tablespace in order to balance I/O Operations.
Alter table dummy move partition d1 tablespace newspace;
2. Adding Partitions : Add partition statement is used to add new partition after the existing last partition.
Alter table dummy add partition d3 values less than (30); - higher bound error
Alter table dummy add partition d3 values less than (70);
3. Splitting Partitions : used to split a partition into two. This is useful when a partition becomes too large.
Alter table dummy split partition d2 at (40) into (partition da1, partition db1);
4. Dropping partitions : alter table dummy drop partition da1;