PLSQL_9i_New_Features_Ppt

PLSQL_9i_New_Features_Ppt - Bryn Llewellyn Product Manager...

Info icon This preview shows pages 1–20. Sign up to view the full content.

View Full Document Right Arrow Icon
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Bryn Llewellyn Product Manager for PL/SQL Oracle Corporation
Image of page 2
PL/SQL enhancements in Oracle9 i paper #129, Oracle OpenWorld, San Francisco, Tue 4-Dec-2001
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
But before I start… But before I start…
Image of page 4
OTN homepage OTN homepage Technologies Technologies PL/SQL PL/SQL otn.oracle.com/tech/pl_sql otn.oracle.com/tech/pl_sql
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
What are the Benefits? y Speed and scalability y Functionality y Usability for the developer
Image of page 6
What’s been enhanced? y Implementation y Language features y Supplied packages
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
What kind of enhancements? y Transparent y Semi-transparent y New features New constructs in the language Richer APIs in the supplied packages
Image of page 8
Here comes the list… Here comes the list…
Image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Implementation Feature Perf Funct Use Feature Perf Funct Use Native compilation of PL/SQL 3 Native compilation of PL/SQL 3 Manipulating records faster by up to 5x 3 Manipulating records faster by up to 5x Manipulating records faster by up to 5x 3 Inter-package calls faster by up to 1.5x 3 Inter Inter - package calls faster by up to 1.5x package calls faster by up to 1.5x 3 Utl_Tcp native implementation 3 Utl_Tcp native implementation 3 Common SQL parser 3 3 Common SQL parser 3 3
Image of page 10
Language features Feature Perf Funct Use Feature Perf Funct Use Table functions 3 3 3 Table functions 3 3 3 Cursor expressions 3 3 3 Cursor expressions Cursor expressions 3 3 3 Multilevel collections 3 3 3 Multilevel collections 3 3 3 Bulk binding in native dynamic SQL 3 3 Bulk binding in native dynamic SQL 3 3 Exception handling in bulk binding DML operations 3 3 Exception handling in bulk Exception handling in bulk binding binding DML operations DML operations 3 3 CASE statements and CASE expressions 3 CASE statements and CASE expressions 3
Image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Exception handling in bulk DML y Consider giving employees a 10% raise where employee_id among the values in a table of numbers… y forall j in id.first..id.last y There might be a per department salary cap – so some intended updates will fail y Pre-Oracle9 i the whole bulk statement failed y Now the OK updates will fail and the exceptions can be reported y save exceptions sql%bulk_exceptions collection
Image of page 12
Multilevel collection syntax for j in my_multi.first..my_multi.last loop for k in my_multi(j).first..my_multi(j).last loop Show ( my_multi(j)(k) ); end loop; end loop; y Collections can be nested to arbitrary depth
Image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
CASE statement syntax case n when 1 then Action1; when 2 then Action2; when 3 then Action3; else ActionOther; end case;
Image of page 14