SPECIAL FILES
LOGIN.sql
If you would like SQLPLUS to define your own environmental settings, put all the required
commands in a file named login.sql. This is a special filename that SQLPLUS always looks for whenever it starts
up. If it finds login.sql, it executes any commands in it as if you had entered
then by hand. You can put any command in login.sql that you can use in SQLPLUS, including SQLPLUS commands and SQL statements. All ot them executed
before SQLPLUS gives you the SQL> prompt.
GLOGIN.sql
This is used in the same ways as LOGIN.sql but to establish default SQLPLUS settings for all users of a database.
IMPORTANT QUERIES
1) To find the nth row of a table
SQL>
Select *from emp where rowid = (select max(rowid) from
emp where rownum
<= 4);
Or
SQL> Select *from emp where rownum <= 4
minus select *from emp where rownum
<= 3;
2) To find duplicate rows
SQL> Select *from emp where rowid in (select max(rowid) from emp group
by
empno,
ename, mgr, job, hiredate, comm, deptno, sal);
Or
SQL> Select
empno,ename,sal,job,hiredate,comm , count(*) from emp group by
empno,ename,sal,job,hiredate,comm
having count(*) >=1;
3) To delete duplicate rows
SQL> Delete emp where rowid in (select
max(rowid) from emp group by
empno,ename,mgr,job,hiredate,sal,comm,deptno);
4) To find the count of duplicate rows
SQL> Select ename, count(*) from emp group
by ename having count(*) >= 1;
5) How to display alternative rows in a
table?
SQL> select *from emp where (rowid,0) in
(select rowid,mod(rownum,2) from emp);
6) Getting employee details of each
department who is drawing maximum sal?
SQL> select *from emp where (deptno,sal) in
( select deptno,max(sal) from emp group by deptno);
7) How to get number of employees in each
department , in which department is
having more than 2500 employees?
SQL> Select deptno,count(*) from emp group
by deptno having count(*) >2500;
8)
To reset the
time to the beginning of the day
SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’)
from dual;
9) To find nth maximum sal
SQL> Select *from emp where sal in (select
max(sal) from (select *from emp order
by sal)
where rownum <= 5);
INTRODUCTION
CHARACTERSTICS
1
Highly structured, readable and accessible language.
2
Standard and Protable language.
3
Embedded language.
4
Improved execution authority.
10g FEATURES
1
Optimized compiler
.
To change the optimizer settings for
the entire database, set the database parameter PLSQL_OPTIMIZE_LEVEL. Valid settings are as follows
0 - No optimization
1 - Moderate optimization
2 - Aggressive optimization
These settings are also modifiable for
the current session.
SQL> alter session set
plsql_optimze_level=2;
Oracle retains optimizer settings on a
module-by-module basis. When you recompile a particular module with nondefault
settings, the settings will stick allowing you to recompile later on using REUSE SETTINGS.
SQL> Alter procedure proc compile
plsql_optimize_level=1;
SQL> Alter procedure proc compile reuse
settings;
2
Compile-time warnings.
Starting with oracle database 10g
release 1 you can enable additional compile-time warnings to help make your
programs more robust. The compiler can detect potential runtime problems with
your code, such as identifying lines of code that will never be run. This
process, also known as lint checking.
To enable these warnings fo the entire
database, set the database parameter PLSQL_WARNINGS. These settings are also modifiable for the current session.
SQL> alter session set plsql_warnings =
‘enable:all’;
The above can be achieved using the
built-in package DBMS_WARNING.
3
Conditional compilation.
Conditional compilation allows the
compiler to allow to compile selected parts of a program based on conditions
you provide with the $IF directive.
4
Support for non-sequential collections in FORALL.
5
Improved datatype support.
6
Backtrace an exception to its line number.
When handling an error, how can you
find the line number on which the error was originally raised?
In earlier release, the only way to do
this was allow you exception to go unhandled and then view the full error trace
stack.
Now you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to obtain that stack and
manipulate it programmatically within your program.
7
Set operators for nested tables.
8
Support for regular expressions.
Oracle database 10g supports the use
of regular expressions inside PL/SQL
code via four new built-in functions.
ü REGEXP_LIKE
ü REGEXP_INSTR
ü REGEXP_SUBSTR
ü REGEXP_REPLACE
9
Programmer-defined quoting mechanism.
Starting with oracle database 10g
release 1, you can define your own quoting mechanism for string literals in
both SQL and PL/SQL.
Use the characters q’(q followed by a
single quote) to note the programmer-defined deliemeter for you string literal.
Ex:
DECLARE
v varchar(10) := 'computer';
BEGIN
dbms_output.put_line(q'*v = *' || v);
dbms_output.put_line(q'$v = $' || v);
END;
Output:
v
= computer
v = computer
10 Many new built-in packages.
DBMS_SCHEDULER
Represents a major update to DBMS_JOB. DBMS_SCHEDULER provides much improved functionality
for scheduling and executing jobs defined via stored procedures.
DBMS_CRYPTO
Offers the ability to encrypt and
decrypt common oracle datatype, including RAWs, BLOBs, and CLOBs. It also provides globalization
support for encrypting data across different charactersets.
DBMS_MONITOR
Provides an API to control additional tracing and
statistics gathering of sessions.
DBMS_WARNING
Provides an API into the PL/SQL compiler warnings module, allowing
you to read and change settings that control which warnings are suppressed,
displayed, or treated as errors.
STANDARD PACKAGE
Oracle has defined in this special package. Oracle defines
quite a few identifiers in this package, including built-in exceptions,
functions and subtypes.
You can reference the built-in form by prefixing it with STANDARD.
The basic unit in any PL/SQL program is block. All PL/SQL
programs are composed of blocks which can occur sequentially or nested.
BLOCK STRUCTURE
Declare
-- declarative section
Begin
-- executable section
Exception
-- exception section
End;
In the above declarative and exceptiona sections are
optional.
BLOCK TYPES
1
Anonymous blocks
2
Named blocks
ü Labeled blocks
ü Subprograms
ü Triggers
ANONYMOUS BLOCKS
Anonymous blocks implies basic block structure.
Ex:
BEGIN
Dbms_output.put_line(‘My first program’):
END;
LABELED BLOCKS
Labeled blocks are anonymous blocks with a label which
gives a name to the block.
Ex:
<<my_bloock>>
BEGIN
Dbms_output.put_line(‘My first program’):
END;
SUBPROGRAMS
Subprograms are procedures and functions. They can be
stored in the database as stand-alone objects, as part of package or as methods
of an object type.
TRIGGERS
Triggers consists of a PL/SQL block that is associated with an
event that occur in the database.
NESTED BLOCKS
A block can be nested within the executable or exception
section of an outer block.
IDENTIFIERS
Identifiers are used to name PL/SQL objects, such as variables, cursors,
types and subprograms. Identifiers consists of a letter, optionally followed by
any sequence of characters, including letters, numbers, dollar signs,
underscores, and pound signs only. The maximum length for an identifier is 30
characters.
QUOTED IDENTIFIERS
If you want to make an identifier case sensitive, include
characters such as spaces or use a reserved word, you can enclose the
identifier in double quotation marks.
Ex:
DECLARE
"a"
number := 5;
"A" number := 6;
BEGIN
dbms_output.put_line('a
= ' || a);
dbms_output.put_line('A = ' || A);
END;
Output:
a = 6
A = 6
COMMENTS
Comments improve readability and make your program more
understandable. They are ignored by the PL/SQL compiler. There are two types of comments available.
1
Single line comments
2
Multiline comments
SINGLE LINE COMMENTS
A single-line comment can start any point on a line with
two dashes and continues until the end of the line.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); -- sample program
END;
MULTILINE COMMENTS
Multiline comments start with the /* delimiter and ends
with */ delimiter.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); /* sample program */
END;
VARIABLE DECLERATIONS
Variables can be declared in declarative section of the
block;
Ex:
DECLARE
a number;
b number := 5;
c number default 6;
CONSTANT DECLERATIONS
To declare a constant, you include the CONSTANT keyword, and you must supply a
default value.
Ex:
DECLARE
b constant number := 5;
c constant number default 6;
NOT NULL CLAUSE
You can also specify that the variable must be not null.
Ex:
DECLARE
b constant number not null:= 5;
c number not null default 6;
ANCHORED DECLERATIONS
PL/SQL offers two kinds of achoring.
1
Scalar anchoring
2
Record anchoring
SCALAR ANCHORING
Use the %TYPE
attribute to define your variable based on table’s column of some other PL/SQL scalar variable.
Ex:
DECLARE
dno dept.deptno%type;
Subtype t_number is number;
a t_number;
Subtype t_sno is student.sno%type;
V_sno t_sno;
RECORD ANCHORING
Use the %ROWTYPE attribute to define your record structure based on a table.
Ex:
`DECLARE
V_dept dept%rowtype;
BENEFITS OF ANCHORED DECLARATIONS
1
Synchronization with database columns.
2
Normalization of local variables.
PROGRAMMER-DEFINED TYPES
With the SUBTYPE statement, PL/SQL allows you to define your own
subtypes or aliases of predefined datatypes, sometimes referred to as abstract
datatypes.
There are two kinds of subtypes.
1
Constrained
2
Unconstrained
CONSTRAINED SUBTYPE
A subtype that restricts or constrains the values normally
allowd by the datatype itself.
Ex:
Subtype positive is binary_integer
range 1..2147483647;
In the above declaration a variable that is declared as
positive can store only ingeger greater than zero even though binary_integer
ranges from -2147483647..+2147483647.
UNCONSTRAINED SUBTYPE
A subtype that does not restrict the values of the original
datatype in variables declared with the subtype.
Ex:
Subtype float is number;
DATATYPE CONVERSIONS
PL/SQL can handle conversions between different families among the
datatypes.
Conversion can be done in two ways.
1
Explicit conversion
2
Implicit conversion
EXPLICIT CONVERSION
This can be done using the built-in functions available.
IMPLICIT CONVERSION
PL/SQL will automatically convert between datatype families
when possible.
Ex:
DECLARE
a varchar(10);
BEGIN
select deptno into a from dept where dname='ACCOUNTING';
END;
In the above variable a is char type and deptno is number
type even though, oracle will automatically converts the numeric data into char
type assigns to the variable.
PL/SQL can automatically convert between
1
Characters and numbers
2
Characters and dates
VARIABLE SCOPE AND VISIBILITY
The scope of a variable is the portion of the program in
which the variable can be accessed. For PL/SQL variables, this is from the variable declaration until the end of
the block. When a variable goes out of scope, the PL/SQL engine will free the memory used to
store the variable.
The visibility of a variable is the portion of the program
where the variable can be accessed without having to qualify the reference. The
visibility is always within the scope. If it is out of scope, it is not
visible.
Ex1:
DECLARE
a number; --
scope of a
BEGIN
--------
DECLARE
b number; -- scope of b
BEGIN
-----
END;
------
END;
Ex2:
DECLARE
a number;
b number;
BEGIN
-- a , b available here
DECLARE
b char(10);
BEGIN
-- a and char type b is available here
END;
-----
END;
Ex3:
<<my_block>>
DECLARE
a number;
b number;
BEGIN
-- a , b available here
DECLARE
b char(10);
BEGIN
-- a and char type b is available here
-- number type b is available using
<<my_block>>.b
END;
------
END;
PL/SQL CONTROL STRUCTURES
PL/SQL has a variety of control structures that allow you to control the
behaviour of the block as it runs. These structures include conditional
statements and loops.
1
If-then-else
2
Case
ü Case with no else
ü Labeled case
ü Searched case
3
Simple loop
4
While loop
5
For loop
6
Goto and Labels
No comments:
Post a Comment