1 / 22

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

Using SQL in PL/SQL Oracle Database PL/SQL 10g Programming. Chapter 4. Using SQL in PL/SQL. Using SQL Statements Using SQL Built-in Functions Using Pseudo Columns Using Cursors Dynamic SQL Statements Regular Expressions. Using SQL in PL/SQL SQL Command Types.

silas
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related