pl/sql development diego thanh nguyen 10/8/12

PL/SQL DEVELOPMENTDiego Thanh Nguyen

10/8/12 www.smartbiz.vn 1

I. Test-Driven PL/SQL DevelopmentI.1. Front-end & Back-endI.2. Development Workflow

II. Exception Handling Architecture III. Optimize SQL in PL/SQL IV. Java & PL/SQL

IV.1. Client Server Java/PLSQL ArchitectureIV.2. Java - PL/SQL: Ad-hoc CallIV.3. Java - PL/SQL: Function/Procedure Call


Table Of Content


I.1. Front-end & Back-end

Backend(in the Oracle Database)

We usually just talk about front-end and back-end, but there's more to it than that.

FrontendBrowserJavaOracle Developer

Data (tables)

Data Access

Infrastructure

Generic Utilities

Business Rules

Application LogicBackend

Developers spend most of their time in the top two layers.– But they often

don't have clearly defined boundaries.

"Assign calls"app_call_mgr"Is call open?"cm_calls_rp.is_open

"Parse string"tb_string_utils

"Log error"em_errors

"Add new call"cm_calls_cp.ins"Call Table"cm_calls


I.2. Development Workflow

DefineReq’ments

2 ConstructHeader

3 Define Tests

4

BugReport

Enhance.Request

Post-Production

Post-Production

ErrorMgt

1

SQLAccess

Coding Conventions

ApplicationPreparation

Single Unit Preparation

Build / Fix Code6

Debug

Test and Review

7 The Build Cycle

To QA /Production

Build Test Code

5

Functional/System TestsPerformed by QA teams and users, tests entire

application.Stress Tests

Program works for 1 user, how about 10,000? Usually done by DBAs and system admins.

Quest's Benchmark Factory automates this process.Unit Tests, aka ”Programmer Tests"

The test of a single unit of code.These are the responsibility of developers, of the

people who wrote the program.All testing is important, but unit tests are the

most fundamental kind of testing. 10/8/12 www.smartbiz.vn 5

I.3. Testing


II.1. Error Management

Manage errors effectively and consistently: Errors are raised, handled, logged and

communicated in a consistent, robust manner.Some special issues for PL/SQL developers

The EXCEPTION datatypeHow to find the line on which the error is raised?Communication with non-PL/SQL host environments

Achieving ideal error managementDefine your requirements clearlyPL/SQL error management & best practices


II.2. PL/SQL Error Management

Defining exceptionsGive name to Error with EXCEPTION_INIT PRAGMA

Raising exceptionsRAISE_APPLICATION_ERROR: communicates specific

error back to a non-PL/SQL host environmentDML aren’t rolled back when exception is raised

Rollback HandlingLog information is committed, while leaving the

business transaction unresolvedHanding exceptions


II.3. Handing Exceptions

The EXCEPTION section consolidates all error handling logic in a block.But only traps errors raised in executable section of block.

Several useful functions usually come into play:SQLCODE and SQLERRMDBMS_UTILITY.FORMAT_ERROR_STACKDBMS_UTILITY.FORMAT_ERROR_BACKTRACE

The DBMS_ERRLOG packageQuick and easy logging of DML errors.

The AFTER SERVERERROR triggerInstance-wide error handling: most useful for non-PL/SQL

front ends executing SQL statements directly.


II.4. Best Practices for Error Management

Some general guidelines - standards for logging mechanisms, error message text; must consistentAvoid hard-coding of Error Numbers and Messages.Build and use reusable components for Raising,

Handling and Logging Errors.Application-level code should not contain:

RAISE_APPLICATION_ERROR: don't leave it to the developer to decide how to raise.

PRAGMA EXCEPTION_INIT: avoid duplication of error definitions.

Object-like representation of an exception (DB)


II.4.1. Prototype Exception ManagerThe rule: developers should only call a pre-

defined handler inside an exception sectionEasier for developers to write consistent, high-quality codeThey don't have to make decisions about form of log & how

the process should be stoppedPACKAGE errpkgIS PROCEDURE raise (err_in IN PLS_INTEGER); PROCEDURE raise (err_in in VARCHAR2);

PROCEDURE record_and_stop ( err_in IN PLS_INTEGER := SQLCODE ,msg_in IN VARCHAR2 := NULL);

PROCEDURE record_and_continue ( err_in IN PLS_INTEGER := SQLCODE ,msg_in IN VARCHAR2 := NULL);

END errpkg;

EXCEPTION WHEN NO_DATA_FOUND THEN errpkg.record_and_continue ( SQLCODE, ' Not Exist ' || TO_CHAR (v_id));

WHEN OTHERS THEN errpkg.record_and_stop; END;

Generic Raises

Recordand Stop

Record& Continue

Advantage of PL/SQL enhancements for SQL. III.1. BULK COLLECT

Use with implicit and explicit queries.Move data from tables into collections.

III.2. FORALLUse with inserts, updates and deletes.Move data from collections to tables.

III.3. Table functionsIII.4. Top Tip: Stop Writing So Much SQL

A key objective of this presentation is to have you stop taking SQL statements for granted inside your PL/SQL code.

Instead, you should think hard about when, where and how SQL statements should be written in your code.


III. Optimize SQL in PL/SQL

12

Oracle Server

PL/SQL Runtime Engine SQL Engine

PL/SQL blockProcedural statement executor

SQL statement executor

FOR rec IN emp_cur LOOP UPDATE employee SET salary = ... WHERE employee_id = rec.employee_id;END LOOP;

Performance penalty for many “context switches”

III.1. SQL and PL/SQL

13

III.1. Different process with FORALL

Oracle server

PL/SQL Runtime Engine SQL Engine

PL/SQL blockProcedural statement executor

SQL statement executor

FORALL indx IN list_of_emps.FIRST.. list_of_emps.LAST UPDATE employee SET salary = ... WHERE employee_id = list_of_emps(indx);

Much less overhead for context switching

14

III.1. Use BULK COLLECT INTO for Queries

DECLARE TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;

l_employees employees_aat;BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP process_employee (l_employees(indx)); END LOOP;END;

Declare a collection of

records to hold the queried data.

Use BULK COLLECT to

retrieve all rows.

Iterate through the collection

contents with a loop.

WARNING! BULK COLLECT will not raise NO_DATA_FOUND if no rows are found.

Always check contents of collection to confirm that something was retrieved.

15

III.2. FORALL Bulk Bind Statement

Instead of executing repetitive, individual DML statements, you can write your code like this:

Things to be aware of: You MUST know how to use collections to use this feature! Only a single DML statement is allowed per FORALL. New cursor attributes: SQL%BULK_ROWCOUNT returns number of rows affected by

each row in array. SQL%BULK_EXCEPTIONS... Use SAVE EXCEPTIONS to continue past errors.

When Cursor FOR Loop vs. BULK COLLECT? If you want to do complex processing on each row as it is queried –

and possibly halt further fetching.

PROCEDURE upd_for_dept (...) ISBEGIN FORALL indx IN list_of_emps.FIRST .. list_of_emps.LAST UPDATE employee SET salary = newsal_in WHERE employee_id = list_of_emps (indx);END;

BULK COLLECT Collection is always filled sequentially, starting at row 1. Production-quality code should generally use the LIMIT clause to

avoid excessive memory usage. Note: Oracle will automatically optimize cursor FOR loops to BULK

COLLECT performance levels.FORALL

Use whenever executing multiple single-row DML statements. Used with Collection; Collection subscripts cannot be expressions. Cannot reference fields of collection-based records inside FORALL.

But you can use FORALL to insert & update entire records. Use the INDICES OF clause to use only the row numbers defined in

another array. Use VALUES OF clause to use only values defined in another array.


III.2. Tips and Fine Points

The Wonder Of Table Functions Table function allow to perform arbitrarily complex transformations

of data and then make that data available through a query.Not everything can be done in SQL.

Combined with REF CURSORs, you can now more easily transfer data from within PL/SQL to host environments. Java, works very smoothly with cursor variables

Building a table function Return nested table or varray based on schema-defined type.

Types defined – only used with pipelined table functions. The function header and the way it is called must be SQL-compatible:

all parameters use SQL types; no named notation.In some cases (streaming and pipelined functions), the IN

parameter must be a cursor variable -- a query result set.


III.3. Table Function

"stream" data through several stages within a single SQL statement. Example: transform one row in the stocktable to two rows in

the tickertable.


III.3. Streaming Data with Table Functions

CREATE OR REPLACE PACKAGE refcur_pkgIS TYPE refcur_t IS REF CURSOR RETURN stocktable%ROWTYPE;END refcur_pkg;/

CREATE OR REPLACE FUNCTION stockpivot (dataset refcur_pkg.refcur_t) RETURN tickertypeset ...

BEGIN INSERT INTO tickertable SELECT * FROM TABLE (stockpivot (CURSOR (SELECT * FROM stocktable)));END;/

Return data iteratively, asynchronous to termination of function. As data is produced within the function, it is passed back to the calling process/query.


III.3. Piping rows out from Pipelined function

CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t) RETURN tickertypeset PIPELINED IS out_rec tickertype := tickertype (NULL, NULL, NULL); in_rec p%ROWTYPE;BEGIN LOOP FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; out_rec.ticker := in_rec.ticker; out_rec.pricetype := 'O'; out_rec.price := in_rec.openprice; PIPE ROW (out_rec); END LOOP; CLOSE p; RETURN;END;

Add PIPELINED keyword to header

RETURN...nothing at all!

Pipe a row of data back to calling block

or query

Bottom line: if everyone writes SQL whenever and wherever they want to, it is very difficult to maintain and optimize the code.

General principle: figure out what is volatile and then hide that stuff behind a layer of code to minimize impact on application logic.

Single Point of (SQL) Robust Definition: if same statement appears in multiple places in application code, very difficult to maintain and optimize that code.

Avoid SQL Repetition


III.4. Top Tip: Stop Writing So Much SQL

OrderTable

ItemTable

Order EntryProgram

OrderTable

ItemTable

ApplicationCode

Intermediate Layer

PL/SQL Programming Language: If/then/else; Loops;Function calls Transactional Procedural


IV.1. Client Server Java/PLSQL Architecture

ADHOCSend the PL/SQL block of code from the Client-Java

program to the Server for processing.Query based on Prepared Statement

FUNCTIONClient Side calls a PL/SQL function in Oracle.

PL/SQL is already compiled and loaded in Oracle.Function will return a value

PROCEDUREClient calls a PL/SQL procedureSimilar to function, but does not return values.


IV.2. Ways to call PL/SQL

PreparedStatement ps=null; sSQL=new String("declare" +

" l_id number:=0;" + " ret number:=0;" + " lssno varchar2(32):=?;" + " error_msg varchar2(1026):=null;" + " BEGIN" + " select count(*) into ret from person where SSNO= lssno ;" + " if(ret=0) then" + " insert into person(id,ssno) values(seq_id.nextval, lssno) returning id into ret;" + " end if;" + " ?:=ret;" + " EXCEPTION WHEN OTHERS THEN" + " ?:=SQLERRM;" + " END;");

ps =connection.prepareStatement(sbSQL.toString());int indx=1;

ps.setString(indx++,SSNO);

int rettype=Types.INTEGER;ps.registerOutParameter(indx++,rettype);

rettype=Types.VARCHAR;ps.registerOutParameter(indx++,rettype);

ps.executeUpdate();l_sError=ps.getString(2);if(l_sError!=null){

System.err.println("PL/SQL Error: " + l_sError); return;

}else{ ret=ps.getInt(1); System.out.println("New USERID: " + ret);}


IV.2. ADHOC: PL/SQL Example

1

2

3

45

6

7

Key Things to Note:Minimize SQL code on

client side.loaded into Oracle once at

install time.Ready for portability

Within Oracle – yes JDBC Standard - using other

database – perhaps SQL Standard - SQL92 syntax?

Consider using global temporary table.

import java.sql.*;import oracle.sql.*;… PreparedStatement ps =null; ArrayDescriptor desc =

ArrayDescriptor.createDescriptor("JOEL.STR_VARRAY”,m_Conn);

String sCar=""; String cars[]=new String[10];

for(int i=0;i<10;i++) { sCar="carX"+i; // CONTRIVE A NAME of a CAR cars[i]=new String(sCar); }

ARRAY array3 = new ARRAY (desc, m_Conn, cars);

String sql=new String("{call api_person_pkg.storePersonCar('joelt',?) }");

ps= m_Conn.prepareStatement(sql);

// Set the values to insert

((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(1, array3);

// Insert the new row

ps.executeUpdate();

m_Conn.commit();


IV.3. Java PL/SQL: Function/Procedure Call

Step1: Create and Prepare a CallableStatement Object CallableStatement myCallableStatement = myConnection.prepareCall( "{call update_product_price(?, ?)}”);

Step2: Provide Parameter Values myCallableStatement.setDouble(2, 1.1);

Step3: Call the execute() Method myCallableStatement.execute(); myCallableStatement.close();


III.3. Calling PL/SQL Procedures

Step 1: Create and Prepare a CallableStatement Object CallableStatement myCallableStatement = myConnection.prepareCall( "{call update_product_price_func (?, ?)}”);

Step 2: Register the Output Parameter myCallableStatement.registerOutParameter(1,java.sql.Types.INTEGER);

Step2: Provide Parameter Values myCallableStatement.setInt(2, 1);

Step 4: Run the CallableStatement myCallableStatement.execute();

Step 5: Read the Return Value int result = myCallableStatement.getInt(1); myCallableStatement.close();


III.3. Calling PL/SQL Functions

pl/sql development diego thanh nguyen 10/8/12

Documents