Simple data processing with PL/SQL in Oracle Database 23c
summary
Builds a currency conversion API that takes a list of country codes and a USD price, converts each to local currency, rounds up to the major unit then subtracts the minor unit to produce “.99” prices, returns all-9s for XXS/XTS and null for SB currencies. Shows using qualified expressions with a cursor-for-initializer to execute the rate lookup once, replacing per-row calls; converting the PL/SQL conversion into scalar SQL macros (textual CLOB inlining) to get SQL-level performance; uses extended case expressions and built-in JSON <-> PL/SQL mapping.
AI-written summary · about
chapters (9)
- 0:00Introduction
- 0:27Currency Conversion API
- 5:07Looping through arrays
- 12:35Using indices
- 13:05Qualified Expressions
- 13:57Loops
- 15:58SQL Macros
- 18:43Textual Find Replace
- 23:53Review
related talks
52mExploring PL/SQL New Features and Best Practices for Better Performance
31mFuturistic SQL: Making the impossible possible
39mAccelerating PL/pgSQL Code Conversion
50mEffective PL/SQL Code Insight With PL/Scope–or another reason to put code in the dtb
21mA Hands-on Experience with Complex SQL
18mHow to write powerful SQL that's easy to maintain
47mSQL Utility Belt
29mDuckPL: A procedural language in DuckDB