•Character Functions
–UPPER(string) converts text to uppercase
–LOWER converts text to lowercase
–INITCAP capitalises first letter of each word
–LENGTH string length including pads
–LTRIM trims characters from left
–RTRIM trims characters from right
–RPAD pads a string on the right
•Number Functions –Trigonometric functions
–Mathematical functions
•Conversion Functions
–TO_CHAR converts dates, numbers to varchar2
–TO_DATE converts text to dates (correct format)
–TO_NUMBER converts text to numbers
–Mathematical functions
•Grouping Functions (cf SQL)
–AVG
–COUNT
–SUM
STORED PROCEDURE
Encapsulated collection of schema objects (procedures, functions, cursors, variables, constants and exceptions)
–TO_CHAR converts dates, numbers to varchar2
–TO_DATE converts text to dates (correct format)
–TO_NUMBER converts text to numbers
•Number Functions
–Trigonometric functions –Mathematical functions
•Conversion Functions
–TO_CHAR converts dates, numbers to varchar2
–TO_DATE converts text to dates (correct format)
–TO_NUMBER converts text to numbers
•Grouping Functions (cf SQL)
–AVG
–COUNT
–SUM
•Miscellaneous
–object functions
–SQLCODE
–SQLERRM
–UID
–USER
–object functions
–SQLCODE
–SQLERRM
–UID
–USER
STORED PROCEDURE
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
(arg1 [IN] datatype, .. arg2 OUT datatype, arg3 INOUT datatype)
AS | IS
{variables;}
BEGIN
{statements;}
END;
FUNCTION
Syntax
CREATE [OR REPLACE] FUNCTION function_name
(arg1 [IN] datatype, .. arg2 OUT datatype, .. arg4 INOUT datatype )
RETURN datatype
AS | IS
{variables;}
BEGIN
{statements;}
RETURN returnstatement;
END;
PACKAGE
Encapsulated collection of schema objects (procedures, functions, cursors, variables, constants and exceptions)
Advantages
•Better organisation of the application (each package has clear, well defined interfaces )•Privileges are granted more easily
•Variables and cursors persist for the session
•Allow procedure and function overloading
•Reduce I/O by loading schema objects simultaneously
•Code re-use
Syntax
CREATE [OR REPLACE] PACKAGE pack_name AS
{FUNCTION f_name(arg1…);}
{PROCEDURE p_name(arg1…);}
{variables;}
{cursors;}
END pack_name;
TRIGGER
Trigger Types and Events
15 triggers
Limitations on Views
–limited updatability
•distinct not specified
•every element is a column (not an aggregate function)
•only the many side of a 1:many
–inserts only into one table
•must contain all not null columns
•no violation of integrity constraints
15 triggers
–4 basic types
Before Statement
Before Row
After Row
After Statement
–for each of 3 statements
Insert, Update, Delete
–instead of triggers (Views)
Insert, Update, Delete
Syntax
CREATE [OR REPLACE] TRIGGER name
{ BEFORE | AFTER }
{INSERT | UPDATE | DELETE [OF column_name]}
ON table_name
[FOR EACH ROW [WHEN [(condition)]]]
DECLARE
{declarations;}
BEGIN
{code statements;}
END;
–limited updatability
•distinct not specified
•every element is a column (not an aggregate function)
•only the many side of a 1:many
–inserts only into one table
•must contain all not null columns
•no violation of integrity constraints
Instead of Triggers
CREATE [OR REPLACE] TRIGGER name
INSTEAD OF {INSERT | UPDATE | DELETE } ON view_name
FOR EACH ROW
BEGIN
{statements;}
END;
eg:-
CREATE OR REPLACE TRIGGER credit
AFTER INSERT ON ord
FOR EACH ROW
DECLARE
balance number;
BEGIN
SELECT credit_balance INTO balance FROM customer
WHERE customer_id = :NEW.customer_id;
balance := balance - :NEW.total;
UPDATE customer
SET credit_balance = balance
WHERE customer_id = :NEW.customer_id;
END;
CURSOR
eg:-
DECLARE
CURSOR c_emp IS
SELECT * FROM emp
WHERE department = ‘LONDON’;
BEGIN
FOR emp_record IN c_emp LOOP
IF emp_record.salary_type = ‘HOURLY’ THEN
emp_record.salary_rate := emp_record.salary_rate * 1.1;
ELSEIF emp_record.salary_type = ‘MONTHLY’ THEN
emp_record.salary_rate := emp_record.salary_rate + 90;
END IF;
END LOOP;
COMMIT;
END;
