Question: I want some examples of the
PL/SQL functions syntax.
Answer: PL/SQL functions allow only INPUT
parameters and they must return a value.
Here is a PL/SQL function example to change Fahrenheit
temperatures to centigrade:
CREATE OR REPLACE FUNCTION
c_to_f (degree
NUMBER) RETURN NUMBER IS
buffer NUMBER;
BEGIN
buffer :=
(degree * 9/5) + 32;
RETURN buffer;
END;
/
;
show
errors
select c_to_f(45) from dual;
-- This is a PL/SQL function example to add tax
based on a state tax list:
reate or replace function plus_tax_2(i_stor_id in
varchar2, i_title_id in varchar2)
return number
as
v_total
number;
v_msrp number;
v_state varchar2(2);
v_tax_pct
number;
not_valid_state exception;
cursor c1 is
select
price,
state
from
titles ti,
sales sa,
stores st
where
st.stor_id = sa.stor_id
and
sa.title_id = ti.title_id
and
st.stor_id = i_stor_id
and
ti.title_id = i_title_id;
begin
open c1;
fetch c1 into v_msrp, v_state;
IF
v_state = 'CA' then
v_tax_pct := .08;
ELSIF v_state = 'WA'
THEN
v_tax_pct := .06;
ELSIF v_state = 'OR' THEN
v_tax_pct := .05;
ELSE
RAISE not_valid_state;
END IF;
v_total := v_msrp + (v_msrp * v_tax_pct);
return v_total;
close c1;
EXCEPTION
WHEN not_valid_state then
RAISE_APPLICATION_ERROR(-20020,
'This state is not valid: ' ||
v_state);
end;
/
show errors
select
plus_tax_2('8042', 'BU1111') from dual;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
