Oracle SQL procedure
Definition
• A stored procedure in Oracle is a named PL/SQL block that performs
one or more specific tasks.
• It is a reusable set of SQL and PL/SQL statements stored in the
database and can be executed multiple times without recompilation.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1 datatype, parameter2 datatype, ..., parameterN datatype)
AS
-- Declarations (optional) ;
BEGIN
executable_section;
EXCEPTION
-- Exception handling section (optional) ;
END procedure_name;
/
Problem statement: create a procedure statement
for the bank database and provide a rate of
interest for their amount based on their age fall
within the condition range.
• SQL> create table bank ( accno number(5), name varchar2(20), age
number(3), amount number(5), interest number(5));
• Table created.
SQL> create or replace procedure probank (sage in number, eage in number,
rate in number) AS
begin
update bank
set interest = amount * rate / 100
where age >= sage and age <= eage;
end probank;
/
Procedure created.
SQL> select * from bank;
ACCNO NAME AGE AMOUNT INTEREST
---------- -------------------- ---------- ---------- ----------
100 Balu 65 10000 0
200 Charan 45 5000 0
300 Kamal 68 50000 0
400 Ragu 35 80000 0
SQL> execute probank(35, 45, 10);
PL/SQL procedure successfully completed.
SQL> select * from bank;
ACCNO NAME AGE AMOUNT INTEREST
---------- -------------------- ---------- ---------- ----------
100 Balu 65 10000 0
200 Charan 45 5000 500
300 Kamal 68 50000 0
400 Ragu 35 80000 8000
SQL> create or replace procedure probank (sage in number, eage in number, rate in number) AS
begin
update bank
set interest = amount * rate / 100
where age >= sage and age <= eage;
FOR rec IN (SELECT accno, name, age, amount, interest FROM bank) LOOP
DBMS_OUTPUT.PUT_LINE('ACCNO: ' || [Link] || ', Name : ' || [Link] || ', Age: ' || [Link]
|| 'Amount: ' || [Link] || ', Interest: ' || [Link]);
END LOOP;
end probank;
/
Procedure created.
SQL> execute probank (55,75, 50);
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> execute probank (55,75, 50);
ACCNO: 100, Name : Balu, Age: 65Amount: 10000, Interest: 5000
ACCNO: 200, Name : Charan, Age: 45Amount: 5000, Interest: 500
ACCNO: 300, Name : Kamal, Age: 68Amount: 50000, Interest: 25000
ACCNO: 400, Name : Ragu, Age: 35Amount: 80000, Interest: 8000
PL/SQL procedure successfully completed.
SQL>
Function
▪ A function in Oracle SQL is a named PL/SQL block that returns a single
value.
▪ It can accept input parameters and perform calculations or tasks
based on those parameters to produce a result.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name
(parameter1 datatype, parameter2 datatype, ..., parameterN datatype)
RETURN return_datatype
AS
-- Declarations (optional)
BEGIN
-- Execution section
RETURN return_value;
EXCEPTION
-- Exception handling section (optional)
END function_name;
/
Problem statement:
function named "calculate_interest" that calculates the interest
for each account in the bank table based on a specified interest
rate.
CREATE OR REPLACE FUNCTION calculate_interest( f_accno IN number,
f_rate IN NUMBER )
RETURN NUMBER
AS
v_principal [Link]%TYPE;
v_interest NUMBER;
BEGIN
SELECT amount INTO v_principal FROM bank WHERE accno = f_accno;
v_interest := v_principal * (f_rate / 100);
RETURN v_interest;
END calculate_interest;
/
SQL> SELECT accno, name, age, amount, calculate_interest(accno, 5) AS interest FROM bank;
ACCNO NAME AGE AMOUNT INTEREST
---------- -------------------- ---------- ---------- ----------
100 Balu 65 10000 500
200 Charan 45 5000 250
300 Kamal 68 50000 2500
400 Ragu 35 80000 4000
SQL> select * from bank;
ACCNO NAME AGE AMOUNT INTEREST
---------- -------------------- ---------- ---------- ----------
100 Balu 65 10000 0
200 Charan 45 5000 0
300 Kamal 68 50000 0
400 Ragu 35 80000 0
Indexes
• Many queries reference only a small proportion of the records in
a file.
• For example, a query like “Find all instructors in the Physics
department” or “Find the salary value of the instructor with ID
22201” references only a fraction of the instructor records.
• It is inefficient for the system to read every record and to check ID
field for the ID “32556,” or the building field for the value
“Physics”.
15
Indexes
• An index on an attribute of a relation is a data structure that
allows the database system to find those tuples in the relation
that have a specified value for that attribute efficiently, without
scanning through all the tuples of the relation.
• For example, if we create an index on attribute dept name of
relation instructor, the database system can find the record with
any specified dept name value, such as “Physics”, or “Music”,
directly, without reading all the tuples of the instructor relation.
• An index can also be created on a list of attributes, for example,
on attributes name and dept name of instructor.
16
Indexes
• Indices are important for efficient processing of transactions, including
both update transactions and queries.
• Indices are also important for efficient enforcement of integrity
constraints such as primary-key and foreign-key constraints.
• Most SQL implementations provide the programmer with control over
the creation and removal of indices via data-definition-language
commands.
• The attribute-list is the list of attributes of the relations that form the
search key for the index.
17
Indexes
• To define an index named dept index on the instructor relation
with dept name as the search key, we write:
• When a user submits an SQL query that can benefit from using an
index, the SQL query processor automatically uses the index.
• For example, given an SQL query that selects the instructor tuple
with dept name “Music”, the SQL query processor would use the
index dept_index defined above to find the required tuple
without reading the whole relation.
18
Indexes
• If we wish to declare that the search key is a candidate key, we
add the attribute unique to the index definition.
• Thus, the command:
• declares dept name to be a candidate key for instructor.
• The index name we specified for an index is required to drop an
index.
• The drop index command takes the form:
19
20
Synonyms
• In DBMS (especially Oracle), a synonym is an alias (alternate
name) for another database object like a table, view, sequence,
procedure, or even another synonym.
• It’s just like giving a nickname to an object so you can refer to it
easily.
21
Why Use Synonyms?
• Simplify long names
Instead of schema_name.table_name, you can use a short
name.
• Hide the owner/schema name
Makes queries look cleaner and hides where the data is
stored.
• Provide location transparency
If the actual object changes location, only the synonym
definition needs updating.
22
Example (Oracle SQL)
SQL>CREATE SYNONYM emp FOR [Link];
-- Now you can query like this:
SQL> SELECT * FROM emp;
-- Instead of:
SQL> SELECT * FROM [Link];
-- Dropping the synonym
SQL> DROP SYNONYM emp;
23