[ Pobierz całość w formacie PDF ]
.executeUpdate();pstmt.close();} catch (SQLException e) {System.err.println(e.getMessage());}}}The classAdjuster has one method, which raises the salary of an employee by agiven percentage.BecauseraiseSalary is avoid method, you publish it as aprocedure using this call spec:CREATE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)AS LANGUAGE JAVANAME Adjuster.raiseSalary(int, float) ;7-44 PL/SQL User s Guide and Reference Using PL/SQL Server PagesLater, you might call procedureraise_salary from an anonymous PL/SQLblock, as follows:DECLAREemp_id NUMBER;percent NUMBER;BEGIN-- assign values to emp_id and percentraise_salary(emp_id, percent); -- call external routine.END;Typically, external C routines are used to interface with embedded systems, solveengineering problems, analyze data, or control real-time devices and processes.External C routines let you extend the functionality of the database server, andmove computation-bound programs from client to server, where they execute faster.For more information about Java stored procedures, see Oracle8i Java StoredProcedures Developer s Guide.For more information about external C routines, seeOracle8i Application Developer s Guide - Fundamentals.Using PL/SQL Server PagesPL/SQL Server Pages (PSPs) enable you to develop Web pages with dynamiccontent.Using special tags, you can embed PL/SQL scripts in HTML pages.Thescripts are executed when the pages are requested by Web clients (browsers forexample).A script can accept user input, then customize page content accordingly.During development, PSPs can act like templates with a static part for page layoutand a dynamic part for content.You can design the layouts using your favoriteHTML authoring tools, leaving placeholders for the dynamic content.Then, youcan write the PL/SQL scripts that generate the content.When finished, you simplyload the resulting PSP files into the database as stored procedures.For more information about creating and using PSPs, see Oracle8i ApplicationDeveloper s Guide - Fundamentals.Subprograms 7-45 Using PL/SQL Server Pages7-46 PL/SQL User s Guide and Reference 8PackagesGoods which are not shared are not goods. Fernando de RojasThis chapter shows you how to bundle related PL/SQL programming resourcesinto a package.The resources might include a collection of procedures or a pool oftype definitions and variable declarations.For example, a Human Resourcespackage might contain hiring and firing procedures.Once written, yourgeneral-purpose package is compiled, then stored in an Oracle database, where itscontents can be shared by many applications.Major TopicsWhat Is a Package?Advantages of PackagesThe Package SpecThe Package BodySome ExamplesPrivate versus Public ItemsOverloading Packaged SubprogramsPackage STANDARDProduct-specific PackagesPackages 8-1 What Is a Package?What Is a Package?A package is a schema object that groups logically related PL/SQL types, items, andsubprograms.Packages usually have two parts, a specification and a body, althoughsometimes the body is unnecessary.The specification (spec for short) is the interfaceto your applications; it declares the types, variables, constants, exceptions, cursors,and subprograms available for use.The body fully defines cursors and subprograms,and so implements the spec.As Figure 8 1 shows, you can think of the spec as an operational interface and ofthe body as a "black box." You can debug, enhance, or replace a package bodywithout changing the interface (package spec) to the package.Figure 8 1 Package InterfaceApplication Package DatabasespecificationbodyTo create packages, use theCREATEPACKAGE statement, which you can executeinteractively from SQL*Plus.Here is the syntax:CREATE [OR REPLACE] PACKAGE package_name[AUTHID {CURRENT_USER | DEFINER}]{IS | AS}[PRAGMA SERIALLY_REUSABLE;][collection_type_definition.][record_type_definition.][subtype_definition.][collection_declaration.][constant_declaration.][exception_declaration.][object_declaration.][record_declaration.][variable_declaration.]8-2 PL/SQL User s Guide and Reference What Is a Package?[cursor_spec.][function_spec.][procedure_spec.][call_spec.][PRAGMA RESTRICT_REFERENCES(assertions).]END [package_name];[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}[PRAGMA SERIALLY_REUSABLE;][collection_type_definition.][record_type_definition.][subtype_definition.][collection_declaration.][constant_declaration.][exception_declaration.][object_declaration.][record_declaration.][variable_declaration.][cursor_body.][function_spec.][procedure_spec.][call_spec.][BEGINsequence_of_statements]END [package_name];]The spec holds public declarations, which are visible to your application.The bodyholds implementation details and private declarations, which are hidden from yourapplication.Following the declarative part of the package body is the optionalinitialization part, which typically holds statements that initialize package variables.TheAUTHIDclause determines whether all the packaged subprograms execute withthe privileges of their definer (the default) or invoker, and whether their unqualifiedreferences to schema objects are resolved in the schema of the definer or invoker.For more information, see "Invoker Rights versus Definer Rights" on page 7-29.A call spec lets you publish a Java method or external C function in the Oracle datadictionary.The call spec publishes the routine by mapping its name, parametertypes, and return type to their SQL counterparts.To learn how to write Java callspecs, see Oracle8i Java Stored Procedures Developer s Guide.To learn how to write Ccall specs, see Oracle8i Application Developer s Guide - Fundamentals.Packages 8-3 What Is a Package?In the example below, you package a record type, a cursor, and two employmentprocedures.Notice that the procedurehire_employeeuses the database sequenceempno_seq and the functionSYSDATE to insert a new employee number and hiredate, respectively.CREATE OR REPLACE PACKAGE emp_actions AS -- specTYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);CURSOR desc_salary RETURN EmpRecTyp;PROCEDURE hire_employee (ename VARCHAR2,job VARCHAR2,mgr NUMBER,sal NUMBER,comm NUMBER,deptno NUMBER);PROCEDURE fire_employee (emp_id NUMBER);END emp_actions;CREATE OR REPLACE PACKAGE BODY emp_actions AS -- bodyCURSOR desc_salary RETURN EmpRecTyp ISSELECT empno, sal FROM emp ORDER BY sal DESC;PROCEDURE hire_employee (ename VARCHAR2,job VARCHAR2,mgr NUMBER,sal NUMBER,comm NUMBER,deptno NUMBER) ISBEGININSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,mgr, SYSDATE, sal, comm, deptno);END hire_employee;PROCEDURE fire_employee (emp_id NUMBER) ISBEGINDELETE FROM emp WHERE empno = emp_id;END fire_employee;END emp_actions;Only the declarations in the package spec are visible and accessible to applications [ Pobierz całość w formacie PDF ]