RESETCOMM is a public procedure used to reset the standard commission based on

Resetcomm is a public procedure used to reset the

This preview shows page 226 - 235 out of 267 pages.

RESET_COMMis a publicprocedure used to reset the standard commission based on some business rules. It is implemented in the package body.-- The package spec with a public variable and a -- public procedure that are accessible from -- outside the package. CREATE OR REPLACE PACKAGE comm_pkg ISv_std_comm NUMBER := 0.10; --initialized to 0.10PROCEDURE reset_comm(p_new_comm NUMBER);END comm_pkg;/
Background image
Creating the Package BodyThe ORREPLACEoption drops and re-creates the package body.Identifiers defined in the package body are privateand not visible outside the package body.All privateconstructs must be declared before they are referenced.Public constructs are visible to the package body.CREATE [OR REPLACE] PACKAGE BODY package_name IS|ASprivate type and variable declarationssubprogram bodies[BEGIN initialization statements]END [package_name];
Background image
Example of a Package Body: comm_pkgCREATE OR REPLACE PACKAGE BODY comm_pkg ISFUNCTION validate(p_comm NUMBER) RETURN BOOLEAN ISv_max_comm employees.commission_pct%type;BEGINSELECT MAX(commission_pct) INTO v_max_commFROM employees;RETURN (p_comm BETWEEN 0.0 AND v_max_comm);END validate;PROCEDURE reset_comm (p_new_comm NUMBER) IS BEGINIF validate(p_new_comm) THENv_std_comm := p_new_comm; -- reset public varELSE RAISE_APPLICATION_ERROR(-20210, 'Bad Commission');END IF;END reset_comm;END comm_pkg;
Background image
Invoking the Package Subprograms: Examples-- Invoke a function within the same packages:CREATE OR REPLACE PACKAGE BODY comm_pkg IS ...PROCEDURE reset_comm(p_new_comm NUMBER) ISBEGINIF validate(p_new_comm) THENv_std_comm := p_new_comm;ELSE ... END IF;END reset_comm;END comm_pkg;-- Invoke a package procedure from SQL*Plus:EXECUTE comm_pkg.reset_comm(0.15)-- Invoke a package procedure in a different schema:EXECUTE scott.comm_pkg.reset_comm(0.15)
Background image
Invoking the Package Subprograms: Using SQL Developer
Background image
Creating and Using Bodiless PackagesCREATE OR REPLACE PACKAGE global_consts ISc_mile_2_kilo CONSTANT NUMBER := 1.6093;c_kilo_2_mile CONSTANT NUMBER := 0.6214;c_yard_2_meter CONSTANT NUMBER := 0.9144;c_meter_2_yard CONSTANT NUMBER := 1.0936;END global_consts;BEGIN DBMS_OUTPUT.PUT_LINE('20 miles = ' ||20 * global_consts.c_mile_2_kilo || ' km');END;CREATE FUNCTION mtr2yrd(p_m NUMBER) RETURN NUMBER ISBEGINRETURN (p_m * global_consts.c_meter_2_yard);END mtr2yrd;/EXECUTE DBMS_OUTPUT.PUT_LINE(mtr2yrd(1))
Background image
Removing Packages: Using SQL Developer or the SQL DROPStatementDrop package specification and bodyDrop package body only-- Remove the package specification and bodyDROP PACKAGE package_name;-- Remove the package body onlyDROP PACKAGE BODYpackage_name;
Background image
Viewing Packages Using the Data Dictionary-- View the package specification.SELECT textFROM user_sourceWHERE name = 'COMM_PKG' AND type = 'PACKAGE';-- View the package body.SELECT textFROM user_sourceWHERE name = 'COMM_PKG' AND type = 'PACKAGE BODY';
Background image
1Copyright © 2009, Oracle. All rights reserved.
Background image
Image of page 235

You've reached the end of your free preview.

Want to read all 267 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture