Transcript plsql
PL/SQL and the Table API Benefits of Server-Side Code Speedy Pizza MENU PIZZA NAPOLITAINE Triggers Procedures Functions Packages • Reduced network traffic • Maintainability • Data integrity Older Editions of Designer • Most constraints only enforced in application • Server vulnerable to other access methods • Application code references tables and generates network traffic Database Trigger • NOT a form trigger – When-enter-block, When-validate-item… • • • • • Occurs in server Associated with Lock or DB modification Before or After DB event For every affected Row, or For SQL Statement DML action What Is a Database Trigger? Trigger • When – Before DML ITEMS – After DML • What – Row – Statement Database Trigger Code that is implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against its associated table or view Triggers and Packages • Triggers often call procedures in Packages • Packages provide Scope for Variables shared by Procedures • Each process gets own copy of Package Variables Packages RENTALS Public Definitions Body Procedures 1 +1 2 Specification Action Functions Public and Private Elements Value = 2 PL/SQL in the Repository Navigator - Server Model Server Model Distribution Implementation APPLICATION[1] Relational Table Definitions PL/SQL Definitions PL/SQL Composition Triggers Functions Procedures Triggers Structure Packages PL/SQL Definitions Server Model PL/SQL Definitions Function Definitions Package Definitions Procedure Definitions P_SET_PRICE Arguments Sub Program Units Program Data Datastructures Synonyms Cursor Definitions Trigger Definitions Undefined PL/SQL CREATE . . . ( p_name IN VARCHAR2) IS v_date DATE; TYPE table_type IS . . . BEGIN IF . . . THEN . . . ; ... callprocedure(v_date); ... END; Defining Triggers Navigator - Server Model Server Model APPLICATION[1] Relational Table Definitions TITLES Triggers TRIG1 Columns Trigger Header Definition PL/SQL Definition Complete Enabled Trigger . . . When Condition Trigger Logic TRIG1 PL/SQL Definitions Definitions PL/SQL TriggerComposition Definitions PL/SQL TRIG1 ... PL/SQL Block ... PL/SQL Logic Opening the Logic Editor • Drag the definition to the work surface Navigator - Server Model PL/SQL Definitions Procedure Definitions P_SET_PRICE Using the Logic Editor PL/SQL PROCEDURE PROCEDURE Select SELECT INTO FROM WHERE Outliner SELECT title Text Editor INTO v_title FROM titles WHERE product_code = PL/SQL p_product_code; ... Construct Tree Root Constructs Statements Static Data Generating PL/SQL Objects DDL file: • CREATE OR REPLACE statements • Header • Data declarations • PL/SQL logic Use the utility as you would for other database objects Fortunately ... • You do not have to write most triggers or packages • Designer writes them for you • You give specifications • You generate Table API • All this code used to be in Applications Table API Keeps Applications Thin Application Application Table API Tables Tables Validation code Table API Triggers • Fire on insert, update, or delete • Call the server packages Triggers Table API Packages • Are called by: – Applications – Triggers • Perform DML operations • Validate data • Derive column values Server Packages Package cg$titles procedures . . . call cg$titles.ins Insert TITLES # * PROD_CDE * TITLE ... Update Delete Lock trigger Insert Why API? • Table API provides a Table Handler for each package • Normal DML causes Triggers to Invoke Handlers • Applications can call API procedures directly – Disabling recursive call of trigger Applications and the API Triggers Form Builder application WebServer application TABLE Column_1 Column_2 Customization • API handles many standard Requirements. • You can add code to API for special Requirements. • Even specify whether it is called before or after standard handling.