PL_SQL Procedures and Functions

Document Sample
PL_SQL Procedures and Functions Powered By Docstoc
					PL/SQL Modular Structure

Data types Loops

Variables Cursors


Exceptions Blocks



Procedure A named PL/SQl block that performs action(s) Information is passed into and out of procedure using a parameter list

Function A named PL/SQl block that returns a single value Information is passed into a function using a parameter list

Package A named collection of procedures, functions, types and variables

PL/SQL Procedures
PROCEDURE apply_discount (company_id_in IN company.company_id%TYPE, discount_in IN NUMBER) IS min_discount CONSTANT NUMBER max_discount CONSTANT NUMBER invalid_discount EXCEPTION; BEGIN IF discount_in BETWEEN min_discount AND max_discount THEN UPDATE item SET item_amount = item_amount * (1-discount_in) WHERE EXISTS (SELECT * from order WHERE order.order_id = item.order_id AND order_company_id = company_id_in); IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF; ELSE RAISE invalid_discount; END IF EXCEPTION WHEN invalid_discount THEN DBMS_OUTPUT.PUT_LINE('The specified discount is invalid.'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No orders found for company:' || TO_CHAR(company_id_in)); END apply_discount; Exception := .05; := .25;




PL/SQL Functions
FUNCTION total_sales (company_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE :=NULL) RETURN NUMBER IS status_int order.status_code%TYPE := UPPER ( status_in); Declaration


CURSOR sales_cur (status_in IN status_code%TYPE) IS SELECT SUM (amount * discount) FROM item WHERE EXISTS (SELECT * FROM order WHERE order.order_id = item.order_id AND company_id = company_id_in AND status_code LIKE status_in ); return_value BEGIN OPEN sales_cur (status_int); FETCH sales_cur INTO return_value; IF sales_cur%NOTFOUND THEN CLOSE sales_cur; RETURN NULL; ELSE CLOSE sales_cur; RETURN return_value; END IF; END tot_sales; NUMBER;


Table item order_id 234 .. 234 status_code CODE .. CODE company_id 432 .. 432 amount 100 .. 50 discount 0.80 .. 0.90

my_number := tot_sales (432, 'Code') 125

Creating Stored Procedure / Function
CREATE OR REPLACE PROCEDURE <procedure name> [( parameters)] IS <procedure body> END <procedure name>; *************** CREATE OR REPLACE FUNCTION <function name> [(parameters)] RETURN <data type> IS <function body> END <function name>;

Convert day4_3.sql into Procedure and Function Analog Take Attendance

Procedure vs Function
Header Function The function name The parameter list, if any The RETURN datatype The declaration section (optional) The declaration section (optional) The executable section The executable section (at least 1 statement) (at least 1 RETURN statement) The exception section (optional) The exception section (optional) apply_discount ( the_company_id, 0.15); sales_1995 := tot_sales(432, 'v'); DECLARE sales_1995 NUMBER := tot_sales(432, 'v'); BEGIN IF tot_sales(432, 'v') > 1000 THEN … Number: Too few parameters can limit the reusability of the program Too many parameters, and no one will want to reuse your program Types: Read-only, write-only, or read-write Constrained Only unconstrained parameters should be used Names: Through the names their purpose in a module is easily understood Defaults: They can have default values. Procedure The procedure name The parameter list, if any




Mode IN (default) OUT IN OUT Description Read-only Default value Can be provided Usage The value of the actual parameter can be referenced inside the module, but the parameter cannot be changed. The module can assign a value to the parameter, but the parameter's value cannot be referenced The module can both reference (read) and modify (write) the parameter

Write-only Read-write

Cannot be provided Cannot be provided

Actual and Formal Parameters
FUNCTION tot_sales (company_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE := NULL) RETURN NUMBER

new_sales := tot_sales (company_id, 'N'); paid_sales := tot_sales( 432, 'P'); all_sales := tot_sales( my_company_id); Positional Notation Procedure Header: PROCEDURE calc_all (id_in IN INTEGER, total_out OUT NUMBER)

Procedure Call calc_all ( 1007, tot_sales);

Named Notation formal_parameter_name => argument_value; => combination symbol new_sales := tot_sales (company_id_in => company_id, status_in => 'N' ); paid_sales := tot_sales( status_in => 'P', company_id_in => 432 ); all_sales := tot_sales( company_id_in => my_company_id);

Local Modules
see day5_24lp, day5_24lf.

Module Overloading
Modules having the same name but different parameters are overloaded. DECLARE FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN IS BEGIN RETURN date_in <= SYSDATE; END; FUNCTION value_ok (number_in_IN NUMBER) RETURN BOOLEAN IS BEGIN RETURN number_in > 0; END; BEGIN

Build-In Functions: date_string := TO_CHAR (SYSDATE, 'MMDDYY'); number_string := TO_CHAR (1000);

Where to Owerload Modules



Restrictions on Overloading
 Different datatype families. INTEGER, REAL, DECIMAL, FLOAT … are NUMBER CHAR, VARCHAR, LONG are character sybtypes.

FUNCTION calculate_profit (revenue_in IN POSITIVE)… FUNCTION calculate_profit (revenue_in IN BINARY_INTEGER)…  Named notation for different parameters name FUNCTION calculate_profit (revenue_in IN NUMBER) FUNCTION calculate_profit (total_revenue_in IN NUMBER)  Parameter mode only does not make difference FUNCTION calculate_profit (revenue IN NUMBER) FUNCTION calculate_profit (revenue IN OUT NUMBER)

 

Return type only does not make difference All overloaded programs must be defined within the same PL/SQL block

Shared By: