The SQL Procedure

1. The SQL Procedure • Block of SQL statements stored in the Data dictionary and called by applications • Satisfies frequently-used or critical application logic • When called all code within the procedure is executed (unlike packages) • Action takes place on server not client • Does not return value to calling program • Not available in Oracle 6 or older • Aids security as DBA may grant access to procedures not tables, therefore some users cannot access tables except through a procedure

2. Building a procedure: contents • Create or replace command • Object to be created • Name of object • Any variables accessed or imported • Local variables declared • Code • End procedure declaration

3. This procedure is called inflation_rise and uses a variable accessed as inf_rate which is a number, this is passed in when the procedure is used. It simply updates the salary by the rate of inflation. Create or replace command Object to be created Name of object Any variables accessed or imported Declared local variables Code End procedure declaration Create or replace procedure inflation_rise (inf_rate in number) Begin update employee set salary = salary + (salary * inf_rate / 100); commit; End;

4. Compiling and executing procedures • Like any program the code needs to be compiled. • @inflation_rise • compiles the procedure in a file with this name • makes it available to the database • Execute inflation_rise executes the procedure. • Remember to compile a procedure again once it has been amended. • For ease of use, it is best to write procedures in notepad and then run them, this means that they can be easily edited and you have a backup copy

5. Local variables used by procedure Any variables passed into procedure SQL Example CREATE OR REPLACE PROCEDURE validate_customer (v_cust VARCHAR) AS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM CUSTOMER WHERE CUST_CODE = v_cust; IF v_count > 0 THEN DBMS_OUTPUT.PUT_LINE(‘customer valid’); ELSE DBMS_OUTPUT.PUT_LINE(‘customer not recognised’); END IF; END;