Oracle PL/SQL Language Pocket Reference
Format: PDF / Kindle (mobi) / ePub
Be more productive with the Oracle PL/SQL language. The fifth edition of this popular pocket reference puts the syntax of specific PL/SQL language elements right at your fingertips, including features added in Oracle Database 12c.
Whether you’re a developer or database administrator, when you need answers quickly, the Oracle PL/SQL Language Pocket Reference will save you hours of frustration with concise summaries of:
- Fundamental language elements, such as block structure, datatypes, and declarations
- Statements for program control, cursor management, and exception handling
- Records, procedures, functions, triggers, and packages
- Execution of PL/SQL functions in SQL
- Compilation options, object-oriented features, collections, and Java integration
This handy pocket reference is a perfect companion to Steven Feuerstein and Bill Pribyl’s bestselling Oracle PL/SQL Programming.
c_lines CONSTANT INTEGER := 50; v_offset INTEGER := c_lines * (p_page# - 1); BEGIN SELECT name, terms BULK COLLECT INTO v_names, v_terms FROM vendors ORDER BY terms, name OFFSET v_offset ROWS FETCH NEXT c_lines ROWS ONLY; ... The next example deletes products in an input list of categories, and the SQL RETURNING clause returns a list of deleted products: FUNCTION cascade_category_delete ( categorylist clist_t) RETURN prodlist_t IS prodlist prodlist_t; BEGIN FORALL aprod IN categorylist.FIRST ..
optimization. RESTRICT_REFERENCES This pragma is deprecated; use DETERMINISTIC and PARALLEL_ENABLE instead. SERIALLY_REUSABLE Tells the runtime engine that package data should not persist between references. This is used to reduce per-user memory requirements when the package data is needed only for the duration of the call and not for the duration of the session. See “Packages” for more information on this pragma. UDF (Oracle Database 12c and higher) Tells the compiler that you intend for
technique as well as the newer, more efficient bulk binds: -- First row-by-row way CREATE OR REPLACE TRIGGER old_trg AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO employee_audit VALUES (:new.employee_id ,:old.salary ,:new.salary ,SYSTIMESTAMP); END old_trg; /* This next trigger is created disabled and must be enabled for use Here is the bulk bind approach: */ CREATE OR REPLACE TRIGGER new_trg FOR UPDATE OF salary ON employees DISABLE COMPOUND TRIGGER -- General declarations
of the decimal precision used in the NUMBER family. So, if you are developing financial applications that are concerned with rounding errors or require decimal precision, you probably should not use these floating-point datatypes. The following table lists the PL/SQL numeric datatypes with ANSI and IBM compatibility. In this table: precision is the precision for the subtype. scale is the scale of the subtype. binary is the binary precision of the subtype. PL/SQL datatype Compatibility Oracle
functions M MAP method, Object Types, Comparison methodsmapping datatypes, Publishing Java to PL/SQLmatch modifiers for regular expressions, Match modifiersMEMBER keyword, Member methodsMEMBER OF function, Nested Table Functionsmetacharacters supported by Oracle, Metacharactersmethodscollection, Collection Methodscomparison, Comparison methodsconstructor, Constructor methodsin subtypes, Methods in Subtypes-Methods in Subtypestypes of, Methods-Comparison methodsMINUS set operation, Nested Table