IF-THEN-ELSE
Syntax:
If <condition1> then
Sequence of statements;
Elsif <condition1>
then
Sequence of statements;
……
Else
Sequence of statements;
End if;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
if dno = 10 then
dbms_output.put_line('Location is NEW YORK');
elsif dno = 20 then
dbms_output.put_line('Location
is DALLAS');
elsif dno = 30 then
dbms_output.put_line('Location
is CHICAGO');
else
dbms_output.put_line('Location
is BOSTON');
end if;
END;
Output:
Location is NEW YORK
CASE
Syntax:
Case test-variable
When
value1 then sequence of statements;
When
value2 then sequence of statements;
……
When
valuen then sequence of statements;
Else
sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location
is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output:
Location is NEW YORK
CASE WITHOUT ELSE
Syntax:
Case test-variable
When
value1 then sequence of statements;
When
value2 then sequence of statements;
……
When
valuen then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location
is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output:
Location is NEW YORK
LABELED CASE
Syntax:
<<label>>
Case test-variable
When
value1 then sequence of statements;
When
value2 then sequence of statements;
……
When
valuen then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
<<my_case>>
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location
is BOSTON');
end case my_case;
END;
Output:
Location is NEW YORK
SEARCHED CASE
Syntax:
Case
When
<condition1> then sequence of statements;
When
<condition2> then sequence of statements;
……
When
<conditionn> then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
case dno
when dno = 10 then
dbms_output.put_line('Location is NEW YORK');
when dno = 20 then
dbms_output.put_line('Location is DALLAS');
when dno = 30 then
dbms_output.put_line('Location is CHICAGO');
when dno = 40 then
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output:
Location is NEW YORK
SIMPLE LOOP
Syntax:
Loop
Sequence
of statements;
Exit when
<condition>;
End loop;
In the syntax exit when <condition> is
equivalent to
If <condition>
then
Exit;
End if;
Ex:
DECLARE
i number := 1;
BEGIN
loop
dbms_output.put_line('i = ' || i);
i := i + 1;
exit when i > 5;
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
WHILE LOOP
Syntax:
While <condition> loop
Sequence
of statements;
End loop;
Ex:
DECLARE
i number := 1;
BEGIN
While i <= 5 loop
dbms_output.put_line('i = ' ||
i);
i := i + 1;
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
FOR LOOP
Syntax:
For <loop_counter_variable>
in low_bound..high_bound loop
Sequence
of statements;
End loop;
Ex1:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i =
' || i);
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
Ex2:
BEGIN
For i in reverse 1..5 loop
dbms_output.put_line('i = ' || i);
end loop;
END;
Output:
i = 5
i = 4
i = 3
i = 2
i = 1
NULL STATEMENT
Usually when you write a statement in a program, you want
it to do something. There are cases, however, when you want to tell PL/SQL to do absolutely nothing, and that is
where the NULL comes.
The NULL
statement deos nothing except pass control to the next executable statement.
You can use NULL
statement in the following situations.
1
Improving program readability.
Sometimes, it is helpful to avoid any
ambiguity inherent in an IF
statement that doesn’t cover all possible cases. For example, when you write an
IF statement, you do not have to include an ELSE clause.
2
Nullifying a raised exception.
When you don’t want to write any
special code to handle an exception, you can use the NULL statement to make
sure that a raised exception halts execution of the current PL/SQL block but does not propagate any
exceptions to enclosing blocks.
3
Using null after a label.
In some cases, you can pair NULL with GOTO to avoid having to execute additional
statements. For example, I use a GOTO
statement to quickly move to the end of my program if the state of my data
indicates that no further processing is required. Because I do not have to do
anything at the termination of the program, I place a NULL statement after the label because at
least one executable statement is required there. Even though NULL deos nothing, it is still an
executable statement.
GOTO AND LABELS
Syntax:
Goto label;
Where label is a label defined in the PL/SQL block. Labels are enclosed in double
angle brackets. When a goto statement is evaluated, control immediately passes
to the statement identified by the label.
Ex:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i = ' || i);
if i = 4 then
goto exit_loop;
end if;
end loop;
<<exit_loop>>
Null;
END;
Output:
i = 1
i = 2
i = 3
i = 4
RESTRICTIONS ON GOTO
1
It is illegal to branch into an inner block, loop.
2
At least one executable statement must follow.
3
It is illegal to branch into an if statement.
4
It is illegal to branch from one if statement to another if
statement.
5
It is illegal to branch from exception block to the current block.
PRAGMAS
Pragmas are compiler directives. They serve as instructions
to the PL/SQL compiler. The compiler will act on
the pragma during the compilation of the block.
Syntax:
PRGAMA instruction_to_compiler.
PL/SQL offers several pragmas:
1 AUTONOMOUS_TRANSACTION
2 EXCEPTION_INIT
3 RESTRICT_REFERENCES
4 SERIALLY_REUSABLE
SUBPROGRAMS
PROCEDURES
A procedure is a module that performs one or more actions.
Syntax:
Procedure [schema.]name [(parameter1
[,parameter2 …])]
[authid definer |
current_user] is
-- [declarations]
Begin
-- executable statements
[Exception
-- exception handlers]
End [name];
In
the above authid clause defines whether the procedure will execute under
the authority of the definer of the procedure or under the authority of the
current user.
FUNCTIONS
A function is a module that returns a value.
Syntax:
Function [schema.]name [(parameter1
[,parameter2 …])]
Return
return_datatype
[authid definer |
current_user]
[deterministic]
[parallel_enable] is
-- [declarations]
Begin
-- executable statements
[Exception
-- exception handlers]
End [name];
In
the above authid clause defines whether the procedure will execute under
the authority of the definer of the procedure or under the authority of the
current user.
Deterministic clause defines, an optimization hint
that lets the system use a saved copy of the function’s return result, if
available. The quety optimizer can choose whether to use the saved copy or
re-call the function.
Parallel_enable clause defines, an optimization hint
that enables the function to be executed in parallel when called from within SELECT statement.
PARAMETER MODES
1
In (Default)
2
Out
3
In out
IN
In parameter will act as pl/sql constant.
OUT
1
Out parameter will act as unintialized variable.
2
You cannot provide a default value to an out parameter.
3
Any assignments made to out parameter are rolled back when
an exception is raised in the program.
4
An actual parameter corresponding to an out formal parameter
must be a variable.
IN OUT
5
In out parameter will act as initialized variable.
6
An actual parameter corresponding to an in out formal
parameter must be a variable.
DEFAULT PARAMETERS
Default Parameters will not allow in the beginning
and middle.
Out and In Out parameters can not
have default values.
Ex:
procedure p(a in number default 5, b in number default 6, c
in number default 7) – valid
procedure p(a in number, b in number
default 6, c in number default 7) – valild
procedure p(a in number, b in number,
c in number default 7) – valild
procedure p(a in number, b in number
default 6, c in number) – invalild
procedure p(a in number default 5, b
in number default 6, c in number) – invalild
procedure p(a in number default 5, b
in number, c in number) – invalild
NOTATIONS
Notations are of two types.
Ø Positional notation
Ø Name notation
We can combine positional and name notation but positional
notation can not be followed by the name notation.
Ex:
Suppose we have
a procedure proc(a number,b number,c number) and we have one
anonymous block
which contains v1,v2, and v3;
SQL> exec proc (v1,v2,v3) --
Positional notation
SQL> exec proc
(a=>v1,b=>v2,c=>v3) --
Named notation
FORMAL AND ACTUAL PARAMETERS
1
Parametes which are in calling subprogram are actual parameters.
2
Parametes which are in called subprogram are formal parameters.
3
If any subprogram was called, once the call was completed then the
values of formal
parameters are copied to the actual parameters.
Ex1:
CREATE OR REPLACE PROCEDURE SAMPLE(a in number,b out number,c in
out
number) is
BEGIN
dbms_output.put_line('After call');
dbms_output.put_line('a = ' || a ||' b = ' || b || ' c = ' || c);
b := 10;
c := 20;
dbms_output.put_line('After
assignment');
dbms_output.put_line('a = ' || a ||' b = ' || b || ' c = ' || c);
END SAMPLE;
DECLARE
v1 number := 4;
v2 number := 5;
v3 number := 6;
BEGIN
dbms_output.put_line('Before call');
dbms_output.put_line('v1 = ' || v1 || ' v2 = ' || v2 || ' v3 = ' || v3);
sample(v1,v2,v3);
dbms_output.put_line('After completion of call');
dbms_output.put_line('v1 = ' || v1 || ' v2 = ' || v2 || ' v3 = ' || v3);
END;
Output:
Before call
v1 = 4 v2 = 5 v3 = 6
After call
a = 4 b = c = 6
After assignment
a = 4 b = 10 c = 20
After completion of call
v1 = 4 v2 = 10 v3 = 20
Ex2:
CREATE OR REPLACE FUN(a in number,b out number,c in out
number) return
number IS
BEGIN
dbms_output.put_line('After call');
dbms_output.put_line('a = ' || a || ' b = ' || b || ' c = ' || c);
dbms_output.put_line('Before assignement Result = ' || (a*nvl(b,1)*c));
b := 5;
c := 7;
dbms_output.put_line('After assignment');
dbms_output.put_line('a = ' || a || ' b = ' || b || ' c = ' || c);
return (a*b*c);
END FUN;
DECLARE
v1 number := 1;
v2 number := 2;
v3 number := 3;
v number;
BEGIN
dbms_output.put_line('Before call');
dbms_output.put_line('v1 = ' || v1 || ' v2 = ' || v2 || ' v3 = ' || v3);
v := fun(v1,v2,v3);
dbms_output.put_line('After call completed');
dbms_output.put_line('v1 = ' || v1 || ' v2 = ' || v2 || ' v3 = ' || v3);
dbms_output.put_line('Result = ' || v);
END;
Output:
Before call
v1 = 1 v2 = 2 v3 = 3
After call
a = 1 b = c = 3
Before assignement Result = 3
After assignment
a = 1 b = 5 c = 7
After call completed
v1 = 1 v2 = 5 v3 = 7
Result = 35
RESTRICTIONS ON FORMAL PARAMETERS
1
By declaring with specified size in actual parameters.
2
By declaring formal parameters with %type specifier.
USING NOCOPY
1
Nocopy is a hint, not a command. This means that the compiler might
silently decide that it can’t fulfill your request for a nocopy
parameter.
2
The copying from formal to actual can be restricted by issuing nocopy
qualifier.
3
To pass the out and in out parameters by reference use nocopy
qualifier.
Ex:
CREATE OR REPLACE PROCEDURE PROC(a in out nocopy number) IS
BEGIN
----
END PROC;
CALL AND EXEC
Call is a SQL
statement, which can be used to execute subprograms like exec.
Syntax:
Call subprogram_name([argument_list])
[into host_variable];
7
The parantheses are always required, even if the subprogram takes
no arguments.
8
We can not use call with out and in out parameters.
9
Call is a SQL
statement, it is not valid inside a PL/SQL
block;
10 The INTO clause is used for the output
variables of functions only.
11 We can not use ‘exec’ with out
or in out parameters.
12 Exec is not valid inside a PL/SQL block;
Ex1:
CREATE OR REPLACE PROC IS
BEGIN
dbms_output.put_line('hello
world');
END PROC;
No comments:
Post a Comment