SAGE Computing Services Customised Oracle Training Workshops and Consulting

SAGE Computing Services Customised Oracle Training Workshops and Consulting Are you making the most of PL/SQL? Hints and tricks and things you may have forgotten Kate Marshall Systems Consultant

Making the most of PL/SQL • Bulk binds • Implicit cursors • Table functions • Reminders • NOCOPY • Scalar subqueries • Functions in the WITH clause • PGA • Using PL/SQL when we should just use SQL

Oracle Server PL/SQL Runtime Engine SQL Runtime Engine PL/SQL Block Procedural statement executor SQL statement executor FOR r_rec IN c_rec LOOP UPDATE emp SET salary = salary * 1.1 WHERE empno = r_rec.empno; END LOOP;

Bulk Binding • We can use bulk binding to pass collections of rows between the SQL engine and PLSQL engine • Reduce overheads from context switching • If a single DML statement is not possible and you need to process many rows individually use bulk binds. • If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. • BULK COLLECT • SELECT statements that fetch multiple rows with a single fetch into a collection before returning to the PLSQL engine • FORALL • DML statements that use collections to change multiple rows • The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop.

BULK COLLECT • SELECT statements that fetch multiple rows with a single fetch into a collection before returning to the PLSQL engine • FORALL • DML statements that use collections to change multiple rows

Cursor FOR Loop • There are occasions when a cursor FOR loop is better than a BULK COLLECT • When you are performing complex processing on each row within the loop, and perhaps need to exit the loop processing • BULK COLLECT will use up PGA memory to store the collections. FETCH employees_cur BULK COLLECT INTO l_employee_ids LIMIT 100;

Implicit v Explicit Cursors • EXPLICIT • Declare cursor • Open loop • Open cursor • Fetch cursor • Close cursor • End Loop • IMPLICIT • Open loop • Execute cursor • End Loop

Implicit v Explicit Cursors • PL/SQL is an interpreted language so every extra line of code adds to the total processing time. • Choose EXPLICIT only if you are planning to reuse the cursor

Table Functions • Data Loading INSERT INTO employees_new (emp_no, first_name, last_name, salary) SELECT emp_no, first_name, last_name, salary FROM TABLE(emppivot_pkg.pipe_emp(CURSOR(SELECT * FROM employees))); FUNCTION pipe_emp(p_source_data IN emppivot_pkg.emptable_rct ) RETURN emppivot_nttPIPELINED;

Table Functions • Data Loading INSERT INTO employees_new (emp_no, first_name, last_name, salary) SELECT emp_no, first_name, last_name, salary FROM TABLE(emppivot_pkg.pipe_emp(CURSOR(SELECT * FROM employees))); FUNCTION pipe_emp(p_source_data IN emppivot_pkg.emptable_rct ) RETURN emppivot_nttPIPELINED; • Avoid Report Redundancy • Remove the need for similar Apex IR reports • Move the complexity of the query into a database function that determines the query required and returns a collection back to the TABLE function

Reminders • Short circuit evaluation • COALESCE v NVL SELECT NVL(expr1, expr2) FROM some_table; SELECT COALESCE(expr1, expr2, ..., exprN) FROM some_table;

Reminders • Short circuit evaluation • COALESCE v NVL SELECT NVL(expr1, expr2) FROM some_table; SELECT COALESCE(expr1, expr2, ..., exprN) FROM some_table; • CASE CASEwhen n_numb = 1 then v_status := ‘very small’;when n_numb < 4 then v_status := ‘small’;when n_numb = 5 then v_status := ‘even’;when n_numb > 4 then v_status := ‘large’;else v_status := ‘very large’; END CASE;

Reminders • Short circuit evaluation • COALESCE v NVL SELECT NVL(expr1, expr2) FROM some_table; SELECT COALESCE(expr1, expr2, ..., exprN) FROM some_table; • CASE CASEwhen n_numb = 1 then v_status := ‘very small’;when n_numb < 4 then v_status := ‘small’;when n_numb = 5 then v_status := ‘even’;when n_numb > 4 then v_status := ‘large’;else v_status := ‘very large’; END CASE; • Group related subprograms into packages

Reminders • Avoid implicit datatype conversions • Speed

Reminders • Avoid implicit datatype conversions • Speed • Error WHERE job_no = 1311100; • CREATE TABLE job ( • job_noVARCHAR2(12)...

Reminders • Avoid implicit datatype conversions • Speed • Error • Incorrect

Reminders • Function Based Indexes • Use subselects to reduce unnecessary function calls .

NOCOPY • OUT and IN OUT parameters passed by value

Scalar Subqueries • SELECT clause • WHERE clause

Functions in the WITH CLAUSE

PGA Memory Issues • The Program Global Area is used to hold: • Bind variables • PL/SQL arrays • SQL work areas (hash operations, sorting) • Cursors and cursor state information • Our extra use of collections will use extra PGA memory • Ensure that the PGA memory size is appropriate to actions being performed • Remember the LIMIT clause when using bulk binds

Using PLSQL when you should just use SQL • Regular Expressions • Remove unnecessary loops, functions and conditional processing with regular expressions • https://regex101.com/

v_string 'www.sagecomputing.com.au' SELECT :v_string, LEVEL AS element_no, REGEXP_SUBSTR(:v_string, '[^\.]+', 1, LEVEL) AS single_element FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(:v_string, '\.') + 1;

Using PLSQL when you should just use SQL • Methods previously mentioned can reduce context switching load. • Many advanced data operations can be done without using PL/SQL and are readily available as a SQL built-in function.

ROLLUP and CUBE • These two operators allow a select statement to calculate totals and a grand total at any level • Totals for • Job and Stage • Job • Grand Total

CUBE • Totals for • Job and Stage • Job • Grand Total • Job and Trade • Stage and Trade • Stage Total • Trade Total

Other Analytic Functions • RANK and DENSE RANK • RATIO_TO_REPORT • LAG and LEAD • LISTAGG

Making the most of PL/SQL • Bulk binds • Implicit cursors • Table functions • Reminders • NOCOPY • Scalar subqueries • Functions in the WITH clause • PGA • Using PL/SQL when we should just use SQL

SAGE Computing Services Customised Oracle Training Workshops and Consulting Presentations are available from our website: http://www.sagecomputing.com.au enquiries@sagecomputing.com.au kate.marshall@sagecomputing.com.au

SAGE Computing Services Customised Oracle Training Workshops and Consulting