1 / 20

11g New Features for PL/SQL Developers

11g New Features for PL/SQL Developers. Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCA PL/SQL Developer http://www.oracle-base.com Oracle PL/SQL Tuning (Rampant) Oracle Job Scheduling (Rampant). Named and Mixed Notation in SQL.

elijahd
Download Presentation

11g New Features for PL/SQL Developers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 11g New Features for PL/SQL Developers Tim Hall Oracle ACE Director Oracle ACE of the Year 2006OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCA PL/SQL Developer http://www.oracle-base.com Oracle PL/SQL Tuning (Rampant) Oracle Job Scheduling (Rampant) http://www.oracle-base.com

  2. http://www.oracle-base.com

  3. Named and Mixed Notation in SQL • Prior to 11g, PL/SQL invoked from SQL had to have its parameters passed using positional notation • This made it difficult to determine the meaning of parameters. • Oracle 11g allows positional, named and mixed notation to be used when calling PL/SQL from SQL. -- Positional Notation. SELECT test_func(10, 20) FROM dual; -- Mixed Notation. SELECT test_func(10, p_value_2 => 20) FROM dual; -- Named Notation. SELECT test_func(p_value_1 => 10, p_value_2 => 20) FROM dual; http://www.oracle-base.com

  4. Sequences in PL/SQL Expressions • The NEXTVAL and CURRVAL sequence pseudocolumns can now be accessed in PL/SQL expressions as well as queries. • This makes the code look simpler, and the documentation suggests it improves performance. SELECT test1_seq.NEXTVAL INTO l_value FROM dual; l_value := test1_seq.NEXTVAL; SELECT test1_seq.CURRVAL INTO l_value FROM dual; l_value := test1_seq.CURRVAL; http://www.oracle-base.com

  5. SIMPLE_INTEGER • SIMPLE_INTEGER is a new subtype of PLS_INTEGER. • It shows only marginal performance improvements in interpreted code, but shows considerable improvements in natively compiled code. • Demo • Improved performance over PLS_INTEGER because: • It wraps round (-2,147,483,648 to 2,147,483,647), rather than doing a bounds check. • It cannot be declared as or set to NULL. http://www.oracle-base.com

  6. CONTINUE statement • The CONTINUE statement jumps out of the current loop interation and starts the next one. • It can be used on its own, or as part of a CONTINUE WHEN statement, as shown below. FOR i IN 1 .. 100 LOOP IF MOD(i,2) = 0 THEN CONTINUE; END IF; -- Do something here! END LOOP; FOR i IN 1 .. 100 LOOP CONTINUE WHEN MOD(i,2) = 0; -- Do something here! END LOOP; http://www.oracle-base.com

  7. Dynamic SQL Enhancements • Native dynamic SQL and the DBMS_SQL package now support dynamic SQL statements larger than 32 KB. • The EXECUTE IMMEDIATE statement, OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs. • DBMS_SQL.TO_REFCURSOR function converts a DBMS_SQL cursor ID into a REF CURSOR. • DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR into a DBMS_SQL cursor ID. • The DBMS_SQL package now supports all datatypes supported by native dynamic SQL. http://www.oracle-base.com

  8. Generalized Invocation • Generalized invocation allows a subtype to invoke a method of a parent type (supertype) using:(SELF AS supertype_name).method_name • A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance. CREATE OR REPLACE TYPE child_type UNDER parent_type ( short_desc VARCHAR2(10), OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2); / CREATE OR REPLACE TYPE BODY child_type AS OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2 IS BEGIN RETURN (self AS parent_type).show_attributes || ' short_desc=' || short_desc; END; END; / http://www.oracle-base.com

  9. Automatic Subprogram Inlining • Every call to a procedure or function causes a slight performance overhead, which is especially noticeable when the subprogram is called within a loop. • Automatic subprogram inlining replaces procedure calls with a copy of the code at compile time. • PLSQL_OPTIMIZE_LEVEL=2 : “PRAGMA INLINE (func, 'YES')” is used to indicate code to be inlined or not. • PLSQL_OPTIMIZE_LEVEL=3 : “PRAGMA INLINE (func, ‘NO')” indicates that code should not be inlined. • Demo • The compiler inlines subprograms early in the optimization process, which may preventing later, more powerful optimizations taking place. FOR i IN 1 .. l_loops LOOP PRAGMA INLINE (add_numbers, 'YES'); l_return := add_numbers(l_num, i); END LOOP; http://www.oracle-base.com

  10. PL/Scope • PL/Scope is a tool that gathers information about user defined identifiers at compile time and stores it in the SYSAUX tablespace. • Controlled by the PLSCOPE_SETTINGS parameter. Default "IDENTIFIERS:NONE". Enable collection "IDENTIFIERS:ALL“. • The PL/Scope data is available from the %_IDENTIFIERS views. ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'; ALTER PROCEDURE test_plscope COMPILE; NAME TYPE USAGE ------------------ ----------- ----------- TEST_PLSCOPE PROCEDURE DECLARATION TEST_PLSCOPE PROCEDURE DEFINITION P_IN FORMAL IN DECLARATION L_VAR VARIABLE DECLARATION L_VAR VARIABLE ASSIGNMENT P_IN FORMAL IN REFERENCE L_VAR VARIABLE ASSIGNMENT L_VAR VARIABLE REFERENCE http://www.oracle-base.com

  11. PL/SQL Hierarchical Profiler • The hierarchical profiler includes the DBMS_HPROF package and the “plshprof” command line utility. • The DBMS_HPROF package needs a directory object with read/write privilege and optionally access to some profiler tables (?/rdbms/admin/dbmshptab.sql). • The DBMS_HPROF package profiles and analyzes the data. • The analysis can be queried from the DBMSHP_% tables. • The “plushprof utility” converts the raw data to several HTML reports. BEGIN DBMS_HPROF.start_profiling ('PROFILER_DIR', 'profiler.txt'); procedure_1(p_times => 10); DBMS_HPROF.stop_profiling; END; / NAME FUNCTION FUNCTION_ELAPSED_TIME CALLS ------------------------------ ------------------------- --------------------- ---------- TEST.PROCEDURE_2 PROCEDURE_2 510 10 TEST.PROCEDURE_3 PROCEDURE_3 17681 100 TEST.PROCEDURE_3 __static_sql_exec_line5 83080 1000 http://www.oracle-base.com

  12. PL/SQL Native Compiler Enhancements • In previous version, PL/SQL was converted to C code, then compiled, which required a C compiler. • Oracle 11g compiles PL/SQL directly to native code with no need for an additional C compiler. • Setting the PLSQL_CODE_TYPE to NATIVE, rather than the default of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. • Once called, the code is loaded into shared memory, making it accessible for all sessions in that instance. • The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object. ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE; ALTER SESSION SET PLSQL_CODE_TYPE=INTERPRETED; http://www.oracle-base.com

  13. New PL/SQL Compiler Warning • A new PL/SQL compiler warning has been added to identify WHEN OTHERS exception handlers that do no re-raise errors using RAISE or RAISE_APPLICATION_ERROR. • Such exception handlers can often hide code failures that result in hard to identify bugs. ALTER SESSION SET plsql_warnings = 'enable:all'; ALTER PROCEDURE others_test COMPILE; SHOW ERRORS SP2-0804: Procedure created with compilation warnings Errors for PROCEDURE OTHERS_TEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/8 PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR http://www.oracle-base.com

  14. PL/SQL Function Result Cache • The cross-session PL/SQL function result cache boosts the performance of PL/SQL functions by caching results in the SGA. • The cache is accessible to any session calling the same function with the same parameters. • This results in significant performance improvements when functions are called for each row in an SQL query, or within a loop in PL/SQL. • The optional RELIES_ON clause is used to specify dependent tables and views so the result cache can be invalidated if the dependent objects are modified. • Demo • The cache is managed using the RESULT_CACHE_% parameters and the DBMS_RESULT_CACHE package. • Information about the cache is displayed using the V$RESULT_CACHE_% views. CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE RELIES_ON (res_cache_test_tab) http://www.oracle-base.com

  15. Triggers: ENABLE and DISABLE clauses • It has long been possible to enable and disable triggers: • ALTER TRIGGER <trigger-name> DISABLE; • ALTER TRIGGER <trigger-name> ENABLE; • ALTER TABLE <table-name> DISABLE ALL TRIGGERS; • ALTER TABLE <table-name> ENABLE ALL TRIGGERS; • Oracle 11g allows triggers to be created in a DISABLED state, rather than creating it as enabled, then altering it to disabled. CREATE OR REPLACE TRIGGER trigger_control_test_trg BEFORE INSERT ON trigger_control_test FOR EACH ROW DISABLE BEGIN DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed'); END; / http://www.oracle-base.com

  16. Triggers: FOLLOWS clause • Oracle allows more than one trigger to be created for the same timing point, but has never guaranteed the execution order. • The FOLLOWS clause in 11g guarantees execution order of triggers defined with the same timing point. CREATE OR REPLACE TRIGGER test_tab_trigger_2 BEFORE INSERT ON test_tab FOR EACH ROW FOLLOWS test_tab_trigger_1 BEGIN -- Do something. NULL; END; / http://www.oracle-base.com

  17. Triggers: Compound triggers CREATE OR REPLACE TRIGGER <trigger-name> FOR <trigger-action> ON <table-name> COMPOUND TRIGGER -- Global declaration. g_global_variable VARCHAR2(10); BEFORE STATEMENT IS BEGIN NULL; -- Do something here. END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN NULL; -- Do something here. END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN NULL; -- Do something here. END AFTER EACH ROW; AFTER STATEMENT IS BEGIN NULL; -- Do something here. END AFTER STATEMENT; END <trigger-name>; / • A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger. • Global declaration is in scope for the whole operation. • Complete clean-up once operation is complete, even when exceptions are raised. • Demo http://www.oracle-base.com

  18. Miscellaneous Changes • XML DB Native Web Services allow PL/SQL procedures and functions to be published as web services. • Referencing columns in WHERE clause of FORALL no longer causes PLS-00436. Makes using bulk-binds for DML even simpler. • PIVOT and UNPIVOT operators available from SQL. • REGEXP_INSTR and REGEXP_SUBSTR now include sub-expression pattern matches. REGEXP_COUNT counts occurrences of a pattern in a string. • Query Result Cache allows caching of query data, similar to PL/SQL Function Result Cache. • Database Resident Connection Pool (DRCP) brings connection pooling to languages that don’t natively support it. http://www.oracle-base.com

  19. Summary • Named and Mixed Notation in PL/SQL Subprogram Invocations • Sequences in PL/SQL Expressions • SIMPLE_INTEGER Datatype • CONTINUE Statement • Dynamic SQL Enhancements • Generalized Invocation • Automatic Subprogram Inlining • PL/Scope • PL/SQL Hierarchical Profiler • PL/SQL Native Compiler Generates Native Code Directly • New PL/SQL Compiler Warning • Cross-Session PL/SQL Function Result Cache • Trigger Enhancements in Oracle Database 11g Release 1 • Miscellaneous additional enhancements. http://www.oracle-base.com

  20. The End… • Questions? • References:http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php • Demos:http://www.oracle-base.com/workshops/11g-new-features-for-plsql-developers/demos.zip http://www.oracle-base.com

More Related