PL/SQL programming
1
Contents
Chapter 1 Introduction to PL/SQL program design ................................ ........................................ ............ 4
§ 1. 2 SQL and PL/SQL .................................. ........................................ ........................................ .... 4
§ 1. 2.1 What is PL/SQL ?................................. ........................................ ...................................... 4
§ 1. 2.1 Advantages of PL/SQL .................................. ........................................ ...................................... 4
§ 1. 2.2 PL/SQL available SQL statements ................................ ........................................ ......................... 5
§ 1. 3. Run the PL/SQL program .................................. ........................................ ........................................ 5
Chapter 2 PL/SQL block structure and composition elements .............................. ........................................ ........... 6
2. 1 PL/SQL block ................................... ........................................ ........................................ ........... 6
2. 2 PL/SQL structure ................................... ........................................ ........................................ ........ 6
2. 3 identifier ...................................... ........................................ ........................................ ............. 6
2. 4 PL/SQL variable type .................................. ........................................ ...................................... 7
2. 4.1 variable type ..................................... ........................................ ........................................ ....... 7
2. 4.2 composite type ..................................... ........................................ ........................................ ..... 9
2. 4.3 Use % rowtype .................................... ........................................ ................................. 11
2. 4.4 lob type *.................................... ........................................ ........................................ .... 11
2. 4.5 Bind Variable ..................................... ........................................ ........................................... 11
2. 4.6 index by tables .................................... ........................................ ........................... 12
2. 4.7 Data type conversion *.................................. ........................................ .................................. 13
2. 5 Operators and expressions (data definition )................................ ........................................ ........................ 13
2. 5.1 Relational operators ..................................... ........................................ ......................................... 13
2. 5.2 General operators ..................................... ........................................ ......................................... 13
2. 5.3 logical operators ..................................... ........................................ ......................................... 14
2. 6. assign values to variables ..................................... ........................................ ........................................ ......... 14
2. 6.1 characters and numeric operation features .................................. ........................................ ............................ 14
2. 6.2 Boolean value assignment ..................................... ........................................ ................................ 14
2. 6.3 database assignment ..................................... ........................................ ......................................... 14
2. 6.4 Value assignment of convertible types .................................. ........................................ ................................ 15
2. 7. Scope and visibility of variables .................................. ........................................ ............................. 15
2. 8 Note ...................................... ........................................ ........................................ ............... 16
2. 9. A simple example ..................................... ........................................ ........................................ ......... 17
2. 9.1 example of inserting simple data ................................... ........................................ ............................. 17
2. 9.2 simple data deletion example ................................... ........................................ ............................. 17
Chapter 3 PL/SQL flow control statements ................................ ........................................ ................ 18
§ 3. 1. conditional statements ..................................... ........................................ ........................................ ......... 18
§ 3. 2 case expression ..................................... ........................................ ........................................ 19
§ 3. 3 loop ...................................... ........................................ ........................................ ............... 19
§ 3. 3. Mark and goto .................................... ........................................ ........................................ .... 21
§ 3. 4 null statement ..................................... ........................................ ........................................ ....... 21
Chapter 4 Use of a cursor .................................... ........................................ ............................ 23-1-
2
§ 4. 1. The concept of a cursor ..................................... ........................................ ........................................ ............ 23
§ 4. 1.1 handle explicit cursors ................................... ........................................ ......................................... 23
§ 4. 1.2 handle implicit cursors .................................... ........................................ ........................................ 26
§ 4. 1.3 modify and delete a cursor .................................. ........................................ ............................ 27
Chapter 5 Exception Handling ................................... ........................................ ...................... 29
§ 5. 1. Exception Handling concepts .................................... ........................................ ........................................ ...... 29
§ 5. 1.1 pre-defined exception handling .................................. ........................................ .................................. 29
§ 5. 1.2 non-predefined exception handling ................................. ........................................ ............................... 30
§ 5. 1.3 user-defined exception handling ................................. ........................................ ........................... 31
§ 5. 1.4 user-defined exception handling .................................. ........................................ ............................ 32
§ 5. 2 abnormal error propagation .................................... ........................................ ........................................ ...... 33
§ 5. 2.1 An exception occurs during execution ................................. ........................................ ....................... 33
§ 5. 2.2 An exception occurs in the Declaration section ................................. ........................................ ....................... 34
§ 5. 3 Programming for exception handling ................................... ........................................ ....................................... 34
§ 5. 4. Use sqlcode in PL/SQL, sqlerrm ....................................... ............................. 35
Chapter 6 stored functions and procedures .................................. ........................................ ....................... 36
§ 6. 1 Introduction ...................................... ........................................ ........................................ ................. 36
§ 6. 2. Create a function ..................................... ........................................ ........................................ ........... 36
§ 6. 3. stored procedures ..................................... ........................................ ........................................ ........... 39
§ 6. 3.1 creation process ..................................... ........................................ ........................................ ..... 39
§ 6. 3.2 call the stored procedure .................................... ........................................ ...................................... 40
§ 6. 3.3 Stored Procedure for Development ................................... ........................................ ............................... 42
§ 6. 3.4 process-related data dictionary .................................. ........................................ ............................ 43
Chapter 7 create and apply a package ................................. ........................................ ........................ 44
§ 7. 1 Introduction ...................................... ........................................ ........................................ ................. 44
§ 7. the definition of the 2-pack .................................... ........................................ ........................................ ............ 44
§ 7. 3. Develop the package ................................... ........................................ ........................................ ..... 45
§ 7. 4. Description of the package definition ................................... ........................................ ........................................ ..... 45
§ 7. 5. The subroutine is overloaded ..................................... ........................................ ........................................ ....... 48
§ 7. 6. Delete the process, function, and package ................................. ........................................ ................................... 50
§ 7. 7. Manage packages .................................... ........................................ ........................................ ............ 50
Chapter 8 triggers ..................................... ........................................ .................................. 51
§ 8. 1. Trigger type ..................................... ........................................ ........................................ ....... 51
§ 8. 1.1 DML triggers ..................................... ........................................ ........................................... 51
§ 8. 1.2 Replace the trigger ..................................... ........................................ ........................................... 51
§ 8. 1.3 system triggers ..................................... ........................................ ........................................... 51
§ 8. 2. Create a trigger ..................................... ........................................ ........................................ ....... 51
§ 8. 2.1 trigger sequence .................................... ........................................ .................................... 53
§ 8. 2.2 create a DML trigger .................................... ........................................ ................................... 53
§ 8. 2.3 create substitution (instead_of) trigger ....................................... ........................................ ............. 54
§ 8. 2.3 create a system event trigger ................................... ........................................ ............................. 54-2-
3
§ 8. 2.4 system trigger event attributes ................................... ........................................ ............................. 55
§ 8. 2.5 use the trigger predicate .................................... ........................................ .................................... 56
§ 8. 2.6 re-compile the trigger .................................... ........................................ .................................... 56
§ 8. 3. Delete and enable triggers .................................. ........................................ ...................................... 56
§ 8. 4. Trigger and data dictionary ................................... ........................................ ..................................... 57
§ 8. 5. Application Example of database triggers .................................. ........................................ ........................ 57
-3-
4
Chapter 1 Introduction to PL/SQL programming
PL/SQL is an advanced database programming language used to access Oracle databases in various environments. Since the language is integrated into the database server, PL/SQL code can process data quickly and efficiently. In addition, PL/SQL is also a feature of Oracle Database client tools. This chapter mainly discusses the necessity of introducing PL/SQL and the main features of this language, as well as the importance of PL/SQL and the database version. I also want to introduce some more detailed advanced concepts throughout the book, and at the end of this chapter I will explain some of the database tables we use in the case of this book.
This chapter focuses on:
