Using SQL in PL/SQL Oracle Database PL/SQL 10g Programming

1. Using SQL in PL/SQLOracle Database PL/SQL 10g Programming Chapter 4

2. Using SQL in PL/SQL • Using SQL Statements • Using SQL Built-in Functions • Using Pseudo Columns • Using Cursors • Dynamic SQL Statements • Regular Expressions Oracle Database PL/SQL 10g Programming (Chapter 4)

3. Using SQL in PL/SQLSQL Command Types • Data Control Language (DCL) command can be used directly inside PL/SQL. • Data Manipulation Language (DML) commands can be used directly inside PL/SQL blocks. • Data Definition Language (DDL) commands cannot be used directly inside PL/SQL blocks, but they can be used indirectly through dynamic SQL statements. Oracle Database PL/SQL 10g Programming (Chapter 4)

4. Using SQL in PL/SQLSQL Command Types: DCL • Single DML statements are an all or nothing proposition, known as autonomous transactions: • You type COMMIT to accept a DML SQL statement. • You type ROLLBACK to reject a DML SQL statement. • Two or more DML statements as a set of activities can act as autonomously but can be controlled as groups using DCL commands; and these are known as transactions, not autonomous transactions: • A transaction requires that all DML statements succeed or fail. • A transaction is ACID compliant and has four properties: Atomic, Consistent, Isolated, and Durable. Oracle Database PL/SQL 10g Programming (Chapter 4)

5. Using SQL in PL/SQLSQL Command Types: DCL • SAVEPOINT sets a named transaction marker. • COMMIT makes permanent any changes made by a user during a session. • ROLLBACK undoes any changes made by a user: • To the beginning of session when the command does not refer to a SAVEPOINT; which models autonomous transactions. • To the named SAVEPOINT provided as an actual parameter to the ROLLBACK command; which models transactions. Oracle Database PL/SQL 10g Programming (Chapter 4)

6. Using SQL in PL/SQLSQL Command Types: Autonomous Transactions BEGIN UPDATE a_table SET name = 'Autonomous' WHERE id = 1; EXCEPTION WHEN others THEN ROLLBACK; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

7. Using SQL in PL/SQL SQL Command Types: Transactions BEGIN SAVEPOINT beginning; INSERT INTO parent_table VALUES (parent_id, name); INSERT INTO child_table VALUES (child_id, parent_id, name); COMMIT; EXCEPTION WHEN others THEN ROLLBACK TO beginning; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

8. Using SQL in PL/SQL SQL Command Types: Transactions • SET TRANSACTION READ ONLY • Constrains the transaction scope of action, which is useful when working in snapshot databases. • SET TRANSACTION READ WRITE • The default state frees the transaction to write data. • SET TRANSACTION ISOLATION LEVEL READ COMMITTED • Constrains the transaction scope of action, requiring all pending transactions to wait on any locked row. • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE • Constrains the transaction scope of action, requiring all pending transactions to abort when encountering locked rows. • SET TRANSACTION USE ROLLBACK SEGMENT • Constrains the transaction to a named ROLLBACK segment, which enables you to target large transactions to large ROLLBACK segments, but this is not generally used when you’re using automatic undo management. Oracle Database PL/SQL 10g Programming (Chapter 4)

9. Using SQL in PL/SQL SQL Command Types: Query Locking Rows DECLARE CURSOR c IS SELECT * FROM a_table FOR UPDATE [NOWAIT]; -- NOWAIT aborts for locked rows. BEGIN FOR i IN c LOOP processing_statement; END LOOP; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

10. Using SQL in PL/SQLSQL Built-in Functions • PL/SQL supports SQL built-in functions, and they can be used: • In SQL statements inside PL/SQL blocks. • In PL/SQL statements and against PL/SQL variables. • SQL built-in functions are qualified in the STANDARD package owned by the SYS user. • The STANDARD package addresses DATE, NUMBER and VARCHAR2 data types. • The DBMS_LOB package addresses LOB data types. Oracle Database PL/SQL 10g Programming (Chapter 4)

11. Using SQL in PL/SQLPseudo Columns: ROWID and ROWNUM • ROWID is a pseudo column that contains the physical block address to a row. • ROWNUM is a pseudo column that contains the number of rows processed by an explicit cursor, which is the number of rows, which are selected from a table. • ROWNUM pseudo column can get Top-N SQL query results. Oracle Database PL/SQL 10g Programming (Chapter 4)

12. Using SQL in PL/SQLPseudo Columns: SQL%ROWCOUNT • SQL%ROWCOUNT is a cursor attribute that contains the number of rows processed by any SQL statement, like when you: • Insert rows into a table. • Update rows in a table. • Delete rows from a table. • Selecte rows from a table. Oracle Database PL/SQL 10g Programming (Chapter 4)

13. Using SQL in PL/SQLUsing Cursors • All DML statements (INSERT, UPDATE and DELETE) inside PL/SQL blocks are implicit cursors. • All DQL statements are implicit or explicit cursors: • Implicit DQL statements are not defined in the declaration section. • Explicit DQL statements are defined in the declaration section. • Cursors are copies of stored data in private work areas. • All system reference cursors are explicit cursors that are: • Strongly typed, or reference a catalog object. • Weakly typed, or do not reference a catalog object. • Capable of being passed as parameters to subroutines. • Capable of being returned values from subroutines. Oracle Database PL/SQL 10g Programming (Chapter 4)

14. Using SQL in PL/SQL Catalog Types: Implicit Cursor BEGIN FOR i IN (SELECT id, name FROM a_table) LOOP dbms_output.put_line('ID: ['||i.id||']'); dbms_output.put_line('Name: ['||i.name||']'); END LOOP; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

15. Using SQL in PL/SQL Catalog Types: Explicit Cursor DECLARE CURSOR c (id_in NUMBER) IS SELECT id, name FROM a_table WHERE id = id_in; BEGIN FOR i IN c LOOP dbms_output.put_line('ID: ['||i.id||']'); dbms_output.put_line('Name: ['||i.name||']'); END LOOP; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

16. Using SQL in PL/SQL Catalog Types: Explicit Cursor DECLARE a_name VARCHAR2(10); CURSOR c IS SELECT name FROM a_table; BEGIN OPEN c; LOOP FETCH c INTO a_number; EXIT WHEN c%NOTFOUND; dbms_output.put_line('Name: ['||a_name||']'); END LOOP; CLOSE c; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

17. Using SQL in PL/SQL Catalog Types: Strongly Typed Reference Cursor DECLARE TYPE strong_cursor IS REF CURSOR RETURN a_table%ROWTYPE; cursor_variableSTRONG_CURSOR; rowa_table%ROWTYPE; BEGIN OPENcursor_variableFOR SELECT * FROM a_table; LOOP FETCHcursor_variableINTOrow; EXIT WHENcursor_variable%NOTFOUND; dbms_output.put_line('Print ['||row.name||']'); END LOOP; CLOSEcursor_variable; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

18. Using SQL in PL/SQL Catalog Types: Weakly Typed Reference Cursor DECLARE TYPE weak_cursor IS REF CURSOR; cursor_variableWEAK_CURSOR; rowa_table%ROWTYPE; BEGIN OPENcursor_variableFOR SELECT * FROM a_table; LOOP FETCHcursor_variableINTOrow; EXIT WHENcursor_variable%NOTFOUND; dbms_output.put_line('Print ['||row.name||']'); END LOOP; CLOSEcursor_variable; END; / Oracle Database PL/SQL 10g Programming (Chapter 4)

19. Using SQL in PL/SQLDynamic SQL Statements • Dynamic SQL statements are executed as autonomous transactions, natively in a SQL*Plus subshell. • Dynamic SQL statements provide the means to run DDL statements in PL/SQL blocks, provided they don’t alter a table referenced in the same block. • Dynamic SQL is also known as NDS, which stands for Native Dynamic SQL. Oracle Database PL/SQL 10g Programming (Chapter 4)

20. Using SQL in PL/SQL Regular Expressions • REGEXP_LIKE() • Enables regular expression searches of character strings. • REGEXP_INSTR() • Enables regular expression searches to locate a position in a string. • REGEXP_REPLACE() • Enables regular expression search and replace actions. • REGEXP_SUBSTR() • Enables regular expression searches to locate a substring in a string. Oracle Database PL/SQL 10g Programming (Chapter 4)

21. Using SQL in PL/SQL Regular Expressions: Metacharacters • * Matches zero or more characters. • . A valid character. • ^ Begins pattern matching from beginning of a line. • [] Groups characters, treats them as by a logical OR operation. • $ Ends pattern matching at the end of the line. • \ Escape character back quotes a special character, signaling it should be treated as an ordinary one. • () Groups strings, which are delimited by a | symbol. • \ Escape character back quotes a special character, signaling it should be treated as an ordinary one. Oracle Database PL/SQL 10g Programming (Chapter 4)

22. Summary • Using SQL Statements • Using SQL Built-in Functions • Using Pseudo Columns • Using Cursors • Dynamic SQL Statements • Regular Expressions Oracle Database PL/SQL 10g Programming (Chapter 4)