pl/sql enhancements in oracle9i - ppt
TRANSCRIPT
![Page 1: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/1.jpg)
![Page 2: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/2.jpg)
Bryn Llewellyn
Product Managerfor PL/SQLOracle Corporation
![Page 3: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/3.jpg)
PL/SQL enhancementsin Oracle9i
paper #129, Oracle OpenWorld, San Francisco, Tue 4-Dec-2001
![Page 4: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/4.jpg)
But before I start…But before I start…
![Page 5: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/5.jpg)
OTN homepageOTN homepage
TechnologiesTechnologies
PL/SQLPL/SQL
otn.oracle.com/tech/pl_sqlotn.oracle.com/tech/pl_sql
![Page 6: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/6.jpg)
What are the Benefits?
Speed and scalabilityFunctionalityUsability for the developer
![Page 7: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/7.jpg)
What’s been enhanced?
ImplementationLanguage featuresSupplied packages
![Page 8: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/8.jpg)
What kind of enhancements?
TransparentSemi-transparentNew features
– New constructs in the language– Richer APIs in the supplied packages
![Page 9: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/9.jpg)
Here comes the list…Here comes the list…
![Page 10: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/10.jpg)
Implementation
Feature Perf Funct UseFeatureFeature PerfPerf FunctFunct UseUse
Native compilation of PL/SQLNative compilation of PL/SQLNative compilation of PL/SQL
Manipulating records faster by up to 5xManipulating records faster by up to 5xManipulating records faster by up to 5x
Inter-package calls faster by up to 1.5xInterInter--package calls faster by up to 1.5xpackage calls faster by up to 1.5x
Utl_Tcp native implementationUtl_TcpUtl_Tcp native implementationnative implementation
Common SQL parserCommon SQL parserCommon SQL parser
![Page 11: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/11.jpg)
Language features
Feature Perf Funct UseFeatureFeature PerfPerf FunctFunct UseUse
Table functionsTable functionsTable functions
Cursor expressionsCursor expressionsCursor expressions
Multilevel collectionsMultilevel collectionsMultilevel collections
Bulk binding in native dynamic SQLBulk binding in native dynamic SQLBulk binding in native dynamic SQL
Exception handling in bulk binding DML operationsException handling in bulkException handling in bulk binding binding DML operationsDML operations
CASE statements and CASE expressionsCASE statements and CASE expressionsCASE statements and CASE expressions
![Page 12: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/12.jpg)
Exception handling in bulk DML
Consider giving employees a 10% raise where employee_id among the values in a table of numbers…forall j in id.first..id.last
There might be a per department salary cap– so some intended updates will failPre-Oracle9i the whole bulk statement failedNow the OK updates will fail and the exceptions can be reportedsave exceptionssql%bulk_exceptions collection
![Page 13: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/13.jpg)
Multilevel collection syntax
for j in my_multi.first..my_multi.lastloopfor k in my_multi(j).first..my_multi(j).lastloopShow ( my_multi(j)(k) );
end loop;end loop;
Collections can be nested to arbitrary depth
![Page 14: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/14.jpg)
CASE statement syntax
case nwhen 1 then Action1;when 2 then Action2;when 3 then Action3;else ActionOther;
end case;
![Page 15: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/15.jpg)
CASE statement syntax
casewhen p = 1 then Action1;when q = 1 then Action2;when r > 1 then Action3;else ActionOther;
end case;
“searched” variety
![Page 16: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/16.jpg)
CASE expression syntax
text := case nwhen 1 then 'one'when 2 then 'two'when 3 then 'three'else 'other'
end;
![Page 17: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/17.jpg)
CASE expression syntax
text := casewhen p = 1 then 'one'when r = 2 then 'two'when q > 1 then 'three'else 'other'
end;
“searched” variety
![Page 18: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/18.jpg)
CASE_NOT_FOUND exceptionbeginp:=0; q:=0; r:=0;casewhen p = 1 then Action1;when r = 2 then Action2;when q > 1 then Action3;
end case;exceptionwhen case_not_found thenShow ('Exception: case_not_found' );
end;
![Page 19: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/19.jpg)
Language features – cont.
Feature Perf Funct UseFeatureFeature PerfPerf FunctFunct UseUse
VARCHAR2 <-> NVARCHAR2 (etc) assignmentVARCHAR2 <VARCHAR2 <--> NVARCHAR2 (etc) assignment> NVARCHAR2 (etc) assignment
VARCHAR2 <-> CLOB assignmentVARCHAR2 <VARCHAR2 <--> CLOB assignment> CLOB assignment
SUBSTR and INSTR w/ CLOBSUBSTR and INSTR w/ CLOBSUBSTR and INSTR w/ CLOB
Seamless access to new SQL features
e.g. MERGE, multitable insert, new time datatypes…
Seamless access to new SQL featuresSeamless access to new SQL features
e.g. MERGE, multitable insert, new time datatypese.g. MERGE, multitable insert, new time datatypes……
OO: Schema evolutioninheritance supporta.k.a. subtyping and polymorphism
OOOO: : Schema evolutionSchema evolutioninheritance supportinheritance supporta.k.a. subtyping and polymorphisma.k.a. subtyping and polymorphism
![Page 20: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/20.jpg)
Supplied packages
Feature Perf Funct UseFeatureFeature PerfPerf FunctFunct UseUse
Utl_Http enhancedUtl_HttpUtl_Http enhancedenhanced
Utl_Raw enhancedUtl_Utl_Raw Raw enhancedenhanced
Utl_File enhancedUtl_FileUtl_File enhancedenhanced
Nineteen new packagesNineteenNineteen new packagesnew packages
![Page 21: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/21.jpg)
Utl_Http enhancementsUsed to send HTTP request and handle the reply mechanically – especially in B2B applicationsOracle9i adds…
– “POST” method – arbitarily long request– Authentication– Access to return status code– RAW reply– cookie support
I.e. provides full support for the semantics that can be expressed via HTTPFull functionality for character set conversion for request and reply
![Page 22: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/22.jpg)
I can’t possiblytalk abouteverythingon that list
in detail!
I can’t possiblytalk abouteverythingon that list
in detail!
![Page 23: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/23.jpg)
So insteadI’m going topick just a coupleto look atin depth…
So insteadI’m going topick just a coupleto look atin depth…
![Page 24: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/24.jpg)
• Native compilation• Native compilation
• Table functionsand cursor expressions
• Table functionsand cursor expressions
![Page 25: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/25.jpg)
Native compilation of PL/SQL
When PL/SQL is used as a thin wrapper for SQL its execution speed is rarely an issueBut we see an increasing trend to use PL/SQL for computationally intensive database independent tasksHere it is the execution speed of the PL/SQL code that determines the performance
![Page 26: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/26.jpg)
Background
Pre-Oracle9i, compilation of PL/SQL source code always results in bytecode which is stored in the database and interpreted at run-time by a virtual machine implemented within ORACLE
![Page 27: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/27.jpg)
What’s new?
In Oracle9i PL/SQL source code may optionally be compiled into native object code which is linked into ORACLE
![Page 28: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/28.jpg)
What’s the benefit?
Speed increased by up to 40%“Thin wrapper” programs won’t speed up so much
…but they won’t slow down!
![Page 29: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/29.jpg)
How does it work?
The code generator translates the PL/SQL source code into C source codeThis is compiled on the given platform and stored as object files on the filesystem……and then linked into ORACLE
![Page 30: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/30.jpg)
How do you do it?
One-time DBA setupThe developer chooses at compile time via the session parameter…
plsql_compiler_flags
![Page 31: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/31.jpg)
One-time DBA setup
Specify 3rd party utilities for compiling and linking– Should be owned by the ORACLE owner– Only the ORACLE owner should have
write accessSpecify directories for the compiled object librariesDone via system parameters
![Page 32: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/32.jpg)
Developer choice
alter sessionset plsql_compiler_flags =
'NATIVE'/* or 'INTERPRETED' */;
sets the compilation mode for subsequently compiled PL/SQL library units
![Page 33: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/33.jpg)
Oracle recommends…
All PL/SQL library units that are called from a given natively compiled top-level unit should also be compiled natively
there is a cost for the context switch when a library unit compiled in native mode invokes one compiled in interpreted mode
Recommendation includes the Oracle-supplied library units (by default these are compiled in interpreted mode)
![Page 34: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/34.jpg)
Oracle9i in Action
170 Systems, Inc have been an Oracle Partner for eleven years and participated in the Beta Program for the Oracle9i Database with particular interest in PL/SQL Native CompilationThey have now certified their 170 MarkView Document Management and Imaging System™against Oracle9iThey have updated the install scripts to optionally turn on Native Compilation
![Page 35: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/35.jpg)
170 MarkViewDocument Managementand Imaging System
Provides Content Management, Document Management, Imaging and Workflow solutionsTightly integrated with the Oracle9i Database, Oracle9i Application Server andthe Oracle E-Business SuiteEnables businesses to capture and manage all of their information online in a single, unified system
![Page 36: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/36.jpg)
170 MarkView™
Customers include…– British Telecommunications– E*TRADE Group– the Apollo Group– the University of Pennsylvania
Very large numbers of documents, images, concurrent users, and high transaction ratesPerformance and scalability especially important
Large-scale multi-user, multi-access system
![Page 37: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/37.jpg)
170 MarkView™
Business logic, including preparation of data for presentation, is implemented in the database in PL/SQL
Involves complex logic and intensive string processing supported by stacks and lists of values modeled as PL/SQL collections.
Visit 170 Systems at Booth # 1914
![Page 38: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/38.jpg)
170 Systems
Have observed a performance increase of up to 40% for computationally intensive routines
…and no performance degradation
![Page 39: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/39.jpg)
Native Compilation - summary
It’s a semi-transparent enhancementIt gives you improved performanceTry it !
![Page 40: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/40.jpg)
• Table functionsand cursor expressions
• Table functionsand cursor expressions
![Page 41: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/41.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 42: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/42.jpg)
Cursor variables - recap
Supported pre-Oracle9iAllow encapsulation of logicspecific to tuples of a particular formatto be independent ofthe concrete SELECT statementwhich retrieves them
![Page 43: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/43.jpg)
Cursor variables - recap
procedure Bulk_Fetch_From_Cursor( p_cursor in sys_refcursor )
is...
SYS_REFCURSOR is a new Oracle9iusability featureDefines a generic weak cursor once and for all
![Page 44: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/44.jpg)
Set up the target
Declare an index-by PL/SQL table for thefetched rows
...istype names_t istable of varchar2(4000)index by binary_integer;
the_names names_t;begin...
![Page 45: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/45.jpg)
Do the bulk fetch
fetch p_cursorbulk collectinto the_names;
Works pre-Oracle9iPerformance boost because reduces context switching between PL/SQL and SQL engines
![Page 46: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/46.jpg)
Process the results
for j in the_names.first..the_names.lastloopShow ( the_names(j) );
end loop;
Works pre-Oracle9i
![Page 47: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/47.jpg)
Invoke the proceduredeclarethe_cursor sys_refcursor;
beginopen the_cursor for'select last_name from employees
order by last_name';Bulk_Fetch_From_Cursor ( the_cursor );close the_cursor;
Note we’re using Native Dynamic SQL
![Page 48: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/48.jpg)
New in Oracle9i
This is just one example of bulk binding working with native dynamic SQLThe combination now works in all situations
– DefiningSELECT
– In-bindingFORALL … USING
– Out-bindingFORALL … USING … RETURNING
![Page 49: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/49.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 50: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/50.jpg)
Cursor expressions and PL/SQL
Suppose we want to make a pretty print
…listing departments
…and under each department list its employees
![Page 51: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/51.jpg)
Explicit 3GL approach
for department in ( select ... )loopShow ( department.department_name );for employee in ( select ...where department_id =
department.department_id )loopShow ( employee.last_name );
![Page 52: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/52.jpg)
This SQL does the job in one go
selectdepartment_name,cursor (
select last_namefrom employees ewhere e.department_id =
d.department_id)
from departments d;
![Page 53: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/53.jpg)
Use in it PL/SQL – new in Oracle9i
declarecursor depts isselect
department_name,cursor ( select ... )
from departments d;
Since there’s only one SQL statement(we had two in the explicit approach)the CBO has a better chance
![Page 54: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/54.jpg)
Set up the fetch targets
Just for fun we’ll do some bulk fetching
v_dname dep...%type;emps sys_refcursor;
type enames_t is table of emp...%typeindex by...;
v_enames enames_t;
![Page 55: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/55.jpg)
Outer loop
Fetch the cursor into the ref cursor targetand then fetch it (bulk) into its targetopen depts;loopfetch depts into v_dname, emps;exit when depts%notfound;Show ( v_dname );fetch empsbulk collect into v_ename;
![Page 56: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/56.jpg)
Inner loop
Process the bulk fetched results
for j in v_ename.first..v_ename.lastloopShow ( v_ename(j) );
end loop;
![Page 57: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/57.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 58: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/58.jpg)
Cursor terminology
A cursor variable (i.e. a variable of typeREF CURSOR) points to an actual cursorIt may be used as a formal parameter to a PL/SQL procedure or functionA cursor expression defines an actual cursor…and is legal in a SQL statement
Func ( cursor ( select c from t ) )
So you’d expect to write…
![Page 59: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/59.jpg)
Cursor expression as an actual parameter to a PL/SQL function
Pre-Oracle9i this was never allowedAt Oracle9i it is allowed…
…when a function is invoked ina top level SQL statement
![Page 60: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/60.jpg)
Cursor expression inWHERE clause functionselect ...from employees managers where Func(cursor ( < select stuff for
this manager's reports > ),managers.hire_date
) = 1;
![Page 61: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/61.jpg)
Cursor expression inWHERE clause function
We’re deciding on the basis of elaborate procedural logicWriting the whole thing as a procedure you need to output to a tableWith the logic in a WHERE clause function you can define a VIEW for dynamic reuse
![Page 62: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/62.jpg)
A cursor expressioncan be an actual parameterto a PL/SQL functionin a top levelSQL statement
A cursor expressioncan be an actual parameterto a PL/SQL functionin a top levelSQL statement
Hold that thought…
![Page 63: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/63.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 64: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/64.jpg)
Pre-Oracle9i table function
We define our row, and a table of our rows…
create type my_rowas object ( idx number,
text varchar2(20) );
create type my_tab as table of my_row;
![Page 65: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/65.jpg)
Pre-Oracle9i table function
function Func return my_tab isv_tab my_tab;
beginv_tab := my_tab ( my_row ( ... ) );< extend the table and compute the rows >return v_tab;
end Func;
All the rows are created before the function returns
![Page 66: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/66.jpg)
Pre-Oracle9i table function
select *from table( cast
( Func() as my_tab ));
Nice, but the response characterstics are not what we’re used to with a rowsource
![Page 67: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/67.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 68: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/68.jpg)
Oracle9i pipelined table function
function Func return my_tab pipelined isv_row my_row;
beginfor ...loopv_row := my_row ( ... );pipe row ( v_row );
end loop;return;
end Func;
![Page 69: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/69.jpg)
As soon asa row is computed,it’s deliveredrowsource-style
As soon asa row is computed,it’s deliveredrowsource-style
Hold that thought too…
![Page 70: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/70.jpg)
Note:
The pipelined and pipe row keywordscan only be used together as shownA pipelined table function can be invokedonly in the FROM list of a SELECT clause
select * from table ( Func() );
We can now use a simpler syntax…
We can now use package-level types for the row and for the table
![Page 71: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/71.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 72: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/72.jpg)
Remember…
A cursor expression can be an actual parameter to a PL/SQL function in a top level SQL statementSo a table function may now be defined with an input parameter of type REF CURSOR and invoked with a cursor expression as the actual parameter
![Page 73: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/73.jpg)
function Func_2 ( p_cur in sys_refcursor )return Pkg.my_tab pipelined isv_in_row Pkg.my_in_row;v_out_row Pkg.my_row;
beginloopfetch p_cur into v_in_row;exit when p_cur%notfound;
< compute out-row(s) from in-row(s) >pipe row ( v_out_row );
end loop;close p_cur;return;
end Func_2;
![Page 74: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/74.jpg)
We’re consuming in-rows,Transforming them procedurally to out-rowswith an arbitrarily complex M:N algorithm,and piping out-rowsas soon as they’re ready
We’re consuming in-rows,Transforming them procedurally to out-rowswith an arbitrarily complex M:N algorithm,and piping out-rowsas soon as they’re ready
![Page 75: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/75.jpg)
How do we use it?
Suppose t is a table which supports a select list compatible with Pkg.my_rowWe can now invoke the table function thus…
select * from table(Func_2( cursor ( select * from t ) )
);
![Page 76: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/76.jpg)
Think about it…
We now have a very powerful generic transformation techniqueIt starts with the results from a SELECT and transforms them into something you can SELECTfromSounds familiar?Note: don’t forget, the table function can have ordinary input parameters
![Page 77: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/77.jpg)
Think of a table functionas a deluxe,parameterizable view
Think of a table functionas a deluxe,parameterizable view
![Page 78: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/78.jpg)
t might have been a view…create view t asselect * from table ( Func() );
So we’d actually be doing…
select * from table(Func_2 ( cursor(select * from table ( Func() ) )
));
![Page 79: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/79.jpg)
Now we’re daisy-chainingNow we’re daisy-chaining
![Page 80: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/80.jpg)
Daisy-chaining
We can plug any number of transformationsback-to-backEach can perform an arbitrarily complex transformationThe starting point could be an Oracle tableOr it could be a table functionthat accesses external datavia Utl_Fileor via the call-out framework
![Page 81: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/81.jpg)
Table functionsand cursor expressions
Cursor variables – recapManipulating cursor expressions in PL/SQLUsing a cursor expressionas an actual parameter to a PL/SQL functionTable functions pre-Oracle9iOracle9i pipelined table functionsDaisy-chaining table functionsParallelization
![Page 82: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/82.jpg)
Who’s going to use them?
The driver for developing table functions was datawarehousing, especially the ETL phaseSo of course the story wouldn’t be complete without a parallel capabilityLet’s look at the syntax…
![Page 83: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/83.jpg)
PARALLEL_ENABLE syntax
function Func_2 ( p_cur in sys_refcursor )return Pkg.my_tabpipelinedparallel_enable ( partition p_cur by any )
is...
Must have an input REF CURSOR parameter to drive the partitioningANY is the simple, special case – results don’t depend on the order of processing
![Page 84: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/84.jpg)
Caution…
If you use ANY and your assertion is wrong, the results will be unpredictableThe developer must design the algorithm so that the results don’t depend on the degree of parallelism
![Page 85: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/85.jpg)
What if the results do dependon the order of processing?
We can be more explicit when we declare how we want the input rows to be partitionedThis requires that we have a strongly typed input cursor, e.g…
package Pkg istype my_row is record ( n number, ... );type cur_t is ref cursor return my_row;
end Pkg;
![Page 86: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/86.jpg)
CLUSTER … BYfunction Func_3 ( p_cur in Pkg.cur_t )return Pkg.my_tabpipelinedparallel_enable( partition p_cur by hash (n) )
cluster p_cur by (n)is...
The algorithm requires that all rows for a given value of n come together to a single slave……but doesn’t care about the order
![Page 87: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/87.jpg)
ORDER … BYfunction Func_4 ( p_cur in Pkg.cur_t )return Pkg.my_tabpipelinedparallel_enable( partition p_cur by range (n) )
order p_cur by ( n, m )is...
Not only must all rows for a given value of n come together to a single slave……but also they must be ordered by n, m
![Page 88: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/88.jpg)
Before…
stage1stage1t1t1 stage2stage2t2t2 t3t3
datadatawarehouse
oltpoltpwarehouse
![Page 89: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/89.jpg)
Using Oracle9i table functions…
t3t3t2t2t1t1
t3t3t2t2t1t1
t3t3t2t2t1t1
t3t3t2t2t1t1
t3t3t2t2t1t1
datadatawarehouse
oltpoltpwarehouse
pipelined parallelized
![Page 90: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/90.jpg)
Comparison…
Sun Ultra-Enterprise 45003 GB RAM, 10 CPUs of 168 MHz1,000,000 row source table1 row in to 7 rows out pivot transformOracle8i – PL/SQL cursor loop with 7 INSERTsOracle9i – table function with 7 PIPE ROWsPerformance and Scalability in DSS Environmentwith Oracle9i,Neil Thombre, Oracle Corp – OOW paper #822also on otn.oracle.com/deploy/performance/
![Page 91: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/91.jpg)
87 87 minmin
Oracle8Oracle8iiOracle9Oracle9iipipelinedpipelined
37 37 minmin
2.4 2.4 xx
Oracle9Oracle9iipipelinedpipelinedparallel 20parallel 20
12 12 minmin
7.5 7.5 xx
![Page 92: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/92.jpg)
Table Functions - summary
An exciting new language feature
Generic applicability…think deluxe, parameterizable view
Especially beneficial inExtraction Load Transformationphase in warehousing applications
![Page 93: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/93.jpg)
Oracle9i PL/SQL gives you…
Improved performanceIncreased functionalityBetter usability for the developer
you should upgrade !you should upgrade !
![Page 94: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/94.jpg)
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S
![Page 95: PL/SQL enhancements in Oracle9i - ppt](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d3761736caf36b75ab24b/html5/thumbnails/95.jpg)