PL/SQL learning Summary

Document directory

• 1: ex: A comprehensive example: Note:
• 2: ex2: Use of joined Arrays: this is also a good example:
• 3: Use of joined arrays whose key is string:

1: Introduction

SQL is a non-procedural language. Procedural Control is always used in programming. PL/SQL is a language that provides procedural control to make up for this defect of the SQL language.

In the execution of PL/SQL statements, PL/SQL statements are executed by the PL/SQL engine, and SQL statements are executed by the SQL engine. PL/SQL integrates process and SQL to improve performance, because after a process is executed,
Returns results in a unified manner. Reduces data transmission. As shown in:

2: PL/SQL Block Structure

The PL/SQL block consists of four parts;

1: declare (optional) can have variables, cursors, user-defined exceptions, and so on.

2: Begin (mandatory) must be: Begin contains SQL statements and PL/SQL statements

3: exception (optional) can be selected and executed when an error occurs,

4: end; (mandatory) Mandatory

The difference between a function and a process is that a function requires a return value, while procedure does not.

Query the official documentation for PL/SQL variable rules

Note: non-empty variables and constants must be initialized. For example:

V_myname varchar2 (20): = 'hooo'
V_myname varchar2 (20) default 'hooo'

About the % Type type declaration

% Type usage: identifier table. column_name % Type

Ex:
Emp_lname employees. last_name % Type

It can also be the same as the declared variable name:
Balance number (7,2 );
Min_balance balance % Type: = 1000;

Note: There are three types of Boolean: Boolean variables in Oracle: True, false, and null.

Variable binding (also called session variable)

Bind Variable:
Environment-related:
Ex:

VARIABLE b_emp_salary NUMBERBEGIN  select  salary into  :b_emp_salary  from  employees where  employee_id=178;END;

PL/SQL supports embedded blocks;
Ex:
Declare
Begin
Declare
Begin
....................
End;
End;

3: cursor 1: Oracle faq2: Oracle 11g cursor Introduction

A cursor is a pointer pointing to a private memory zone, which is allocated by the Oracle server.

ACursorsIs a pointer used to fetch rows from
A result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in
This structure is a pointer to the next record to be fetched from the query results.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you wocould soon run into theORA-01000: Maximum Number
Of open cursors exceeded error.

Two types of cursors are available: Implicit cursors and explicit cursors. Implicit cursors include Oracle servers for creation and management. Explicit cursors: programmers must declare and create them;

Explicit and implicit cursors

Cursor attributes:
At least one SQL % found record is affected in the SQL statement operation.
SQL % notfound and % found are opposite
SQL % rowcount

The cursor needs to be studied in depth.

4: Write control structure 1: If Control

IF condition   THEN  statementsELSIF condition   THEN  statementsELSE  statementsEND IF;

There are three types of condition: True, false, and null. Example:

SQL> declare 2 v_myage number; 3 begin 4 If v_myage <11 then 5 dbms_output.put_line ('I am a child! '); 6 else 7 dbms_output.put_line (' I am not a child '); 8 end if; 9 end; 10/PL/SQL process completed successfully. SQL> set serveroutput onsql>/I am not a childpl/SQL process has been completed successfully.

Null at initialization:

SQL> declare 2 v_myage number; 3 begin 4 If v_myage is null then 5 dbms_output.put_line ('I am a child! '); 6 else 7 dbms_output.put_line (' I am not a child '); 8 end if; 9 end; 10/I am a child! The PL/SQL process is successfully completed.

2: Case

CASE selector   WHEN  expression1  THEN  result1   WHEN  expression2  THEN  result2   .........   WHEN  expressionN  THEN  resultN   [ELSE resultN+1]END;

A simple example:

SQL> set verify offsql> declare 2 v_grade char (1): = upper ('& grad'); 3 v_appraisal varchar2 (20); 4 5 begin 6 v_appraisal: = case v_grade 7 when 'a 'then' excellent '8 when 'B' then' very good '9 when 'C' then 'Good '10 else' no such grde '11 end; 12 dbms_output.put_line ('grade: '| v_grade | 'apprasisal' | v_appraisal); 13 end; 14/enter the grade value: Agrade: A apprasisal excellentpl/SQL process has been completed successfully.

The second case is the same as the example, and the second case does not have a selection character. When it is followed, it can be directly judged.
Sometimes it is more flexible to judge multiple fields.

SQL> declare 2 v_grade char (1): = upper ('& grade'); 3 v_appraisal varchar2 (20); 4 5 begin 6 v_appraisal: = case 7 when v_grade = 'A' then' excellent '8 when v_grade in ('B', 'C ') then 'very good '9 10 else' no such grde '11 end; 12 dbms_output.put_line ('grade: '| v_grade | 'apprasisal' | v_appraisal); 13 end; 14/enter the grade value: bgrade: B apprasisal very goodpl/SQL. The process has been completed successfully.

The above is an introduction to the case expression.

The following describes the case statement;
Case
End case;
In case statements, end with end case;

The difference between a case expression and a case statement: an expression can be assigned a value.
The case statement is a statement and cannot be assigned to other results. The case statement must end with the end case. Note: If a and B
Or if A or B
We should put a relatively good computing result with less memory consumption in the position,
Because a is calculated first, for and, B after a is false does not need to be calculated,
Saves instruction and memory.
The same as when or A is true.

Using null can enhance program readability:
If condition
Then
Else
Then NULL;

Another usage of null: declare ..... begin if condition Then goto lastpoint end if ;....... <lastpoint> -- this label must be followed by a statement, so it can be expressed as null. NULL; end;

3: There are three cycles in the loop control PL/SQL:
Basic Loop
For Loop
While loop1: Basic loop:

LOOP  statement1；  EXIT  [WHEN condition];END LOOP

Exit is equivalent to break in other languages; 2: while loop:

WHILE condition LOOP     statement1;     statement2;......END LOOP;

3: For Loop;

For counter in [reverse] -- reverse indicates lover_bound... upper_bound loop statement1; statement2 ;............ end loop; the step of the For Loop must be 1.

The continue keyword is added to the 11g version, which is consistent with the continue usage in Java.
The continue and exit syntaxes are the same.
Three methods.
Exit;
Exit when condition;
Exit label when condition; exit to the position where the label is located. Example:

SQL> declare 2 v_total number: = 0; 3 begin 4 <beforetoploop> 5 for I in 1 .. 10 loop 6 v_total: = v_total + 1; 7 dbms_output.put_line ('total is: '| v_total); 8 for J in 1 .. 10 loop 9 continue beforetoploop when I + j> 5; 10 v_total: = v_total + 1; 11 end loop; 12 end loop; 13 end; 14/total is: 1 total is: 6 total is: 10 total is: 13 Total is: 15 Total is: 16 total is: 17 total is: 18 total is: 19 Total is: 20pl/SQL process has become Completed.

4: GOTO statement:

Syntax: goto label_name;

SQL> begin 2 goto second_output; 3 dbms_output.put_line ('This line will never execute. '); 4 <second_output> 5 dbms_output.put_line ('We are here! '); 6 end; 7/We are here! The PL/SQL process is successfully completed.

The first output of the preceding statement is never executed. 5. Composite data types: the difference between collections and records sets and records:
Different data types are stored in the record, while the same data types are stored in the set.
The record is equivalent to the entity class in Java.
A set is equivalent to an array. Java sets are implemented using arrays at the underlying layer. 1: Create PL/SQL record Syntax:

Record Declaration; Type type_name is record (field_declaration [, field_declaration] ......); identifier type_name; field declaration field_declaration; field_name {field_type | variable % type | table. column % type | table % rowtype} [[not null] {:=| default} expr]}

2: % rowtype

SQL> declare 2 person employees % rowtype; 3 begin 4 select * into person from employees where employee_id = 100; 5 dbms_output.put_line ('name: '| person. first_name); 6 end; 7/Name: Steven PL/SQL process completed successfully.

% Type % rowtype all adopt the anchor lock technology,
The anchor reference is parsed during compilation. If the data variable type, table structure column type, and number of Columns
Changed. The code containing % Type % rowtype must be re-compiled.

Some knowledge about the anchor statement:

It is equivalent to a ----> B a references B and B is changed. Re-compilation is required to maintain synchronization. Two good examples:
Use % rowtype

First, create a table: SQL> Create Table retired_emps (empno number (6), ename varchar (25), job varchar (10), Mgr number (6), hiredate date, leavedate date, sal number (8, 2), comm number (2, 2), deptno number (4); the table has been created.

SQL> select * From retired_emps; unselected rows SQL> declare 2 v_employee_number number: = 124; 3 v_emp_rec retired_emps % rowtype; 4 5 begin 6 select employee_id, last_name, job_id, manager_id, 7 hire_date, sysdate, salary, commission_pct, partition into 8 v_emp_rec from employees where employee_id = v_employee_number; 9 insert into partition values v_emp_rec; 10 end; 11/PL/SQL has been completed successfully. SQL> select * from orders; empno ename job Mgr hiredate ---------- orders ---------- -------------- leavedate Sal deptno orders ---------- 124 mourgos st_man 100-11-9908-6-13 5800 50

Update retired_emps set ROW = v_emp_rec where empno = v_employee_number; example: one example illustrates three methods;
This example illustrates how to declare rowtype through a table column
Explicit cursor declaration,
Three methods are directly declared using type.

create table cust_sales_roundup(     customer_id NUMBER(5),     customer_name VARCHAR2(100),     total_sales NUMBER(15,2)   );

SQL> declare 2 rows % rowtype; 3 cursor cust_sales_cur is select * From rows; 4 cust_sales_rec cust_sales_cur % rowtype; 5 6 type rows is RECORD 7 (8 customer_id number (5 ), 9 customer_name cust_sales_roundup.customer_name % type, 10 total_sales number (15,2) 11); 12 13 prefererred_cust_rec customer_sales_rectype; 14 15 begin 16 -- ass IGN one recored to another 17 cust_sales_roundup_rec: = cust_sales_rec; 18 prefererred_cust_rec: = cust_sales_rec; 19 end; 20/PL/SQL has been completed successfully.

3: The associated array is a PL/SQL set with two columns:
Key values key: It can be integer or string data type
Values: a scalar or record
Sequence of joined Arrays:
How to define correlated Arrays:

TYPE  type_name  IS TABLE OF         { column_type | variable%TYPE         |table.column%TYPE} [NOT NULL]         |INDEX BY PLS_INTEGER | BINARY_INTEGER          |VARCHAR2(<size>);       }     identifier type_name;

1: ex: A comprehensive example: Note:

Happyfamily. First Index
Happyfamily. Next next Index
Happyfamily. exists (key) determines whether the key exists.

SQL> declare 2 type list_of_names_t is table of employees. first_name % Type 3 index by pls_integer; 4 happyfamily list_of_names_t; 5 l_row pls_integer; 6 7 begin 8 happyfamily (2020202): = 'topwqp '; 9 happyfamily (-15070 ): = 'steven '; 10 happyfamily (-90900): = 'chris'; 11 happyfamily (88): = 'veva '; 12 13 l_row: = happyfamily. first; 14 While (l_row is not null) 15 loop 16 dbms_output.put_line (l_row | '-- > '| Happyfamily (l_row); 17 l_row: = happyfamily. next (l_row); 18 end loop; 19 20 l_row: = 88; 21 22 if happyfamily. exists (l_row) then 23 dbms_output.put_line ('it is here! ----> '| Happyfamily (l_row); 24 else 25 dbms_output.put_line ('it is not here! ----> '| Happyfamily (l_row); 26 end if; 27 end; 28/-90900 --> Chris-15070 --> Steven 88 --> veva2020202 --> topwqpit is here! ----> The vevapl/SQL process has been completed successfully.

Method for associating Arrays:

Exists prior count next first Delete last
These methods require you to learn from the books recommended by PL/SQL. 2: ex2: Use of joined Arrays: this is also a good example:

SQL> declare 2 type emp_table_type is table of 3 employees % rowtype index by pls_integer; 4 my_emp_table emp_table_type; 5 max_count number (3): = 104; 6 begin 7 For I 100 .. max_count 8 loop 9 select * into my_emp_table (I) from employees 10 where employee_id = I; 11 end loop; 12 13 For I in my_emp_table.first .. my_emp_table.last 14 loop 15 dbms_output.put_line (my_emp_table (I ). last_name); 16 end Loop ; 17 end; 18/kingkochharde haanhunoldernstpl/SQL process completed successfully.

3: Use of joined arrays whose key is string: This example is also very typical. You can run the following command:

SQL> declare 2 subtype location_t is varchar2 (64); 3 type population_type is table of number index by location_t; 4 5 l_country_population population_type; 6 l_count pls_integer; 7 l_location location_t; 8 begin 9 l_country_population ('greeland '): = 100000; 10 l_country_population ('usa'): = 3000000000; 11 l_country_population ('iceland'): = 750000; 12 l_country_population ('Australia '): = 230000000; 13 l_country_population ('usa'): = 40000000; 14 15 l_count: = l_country_population.count; 16 dbms_output.put_line ('count = '| l_count); 17 18 l_location: = l_country_population.first; 19 dbms_output.put_line ('first ROW = '| l_location); 20 dbms_output.put_line ('first value =' | l_country_population (l_location); 21 22 l_location: = l_country_population.last; 23 dbms_output.put_line ('Last ROW = '| l_locat Ion); 24 dbms_output.put_line ('Last value = '| l_country_population (l_location); 25 end; 26/COUNT = 5 first row = export aliafirst value = 230000000 last row = usalast value = 40000000pl/SQL process completed successfully.

4: nested table 5: explicit cursor Syntax:
Cursor cursor_name is SELECT statement; execution flow of the explicit cursor: ex: Demo execution:

SQL> declare 2 cursor is 3 select employee_id, last_name from employees 4 where department_id = 30; 5 v_emp_record c_emp_cursor % rowtype; 6 begin 7 Open cursor; 8 loop 9 fetch c_emp_cursor into cursor; 10 exit when c_emp_cursor % notfound; 11 dbms_output.put_line (v_emp_record.employee_id | ''| v_emp_record.last_name); 12 end loop; 13 close c_emp_cursor; 14 end; 15/114 Raphae The ly115 khoo116 baida117 tobias118 himuro119 colmenarespl/SQL process has been completed successfully.

Another variant: a simple form:

SQL> begin 2 for I in (select employee_id, last_name from employees where department_id = 30) 3 Loop 4 dbms_output.put_line (I. employee_id | '---->' | I. last_name); 5 end loop; 6 end; 7/114 ----> raphaely115 ----> hoo116 ----> baida117 ----> tobias118 ----> himuro119 ----> colmenarespl/SQL process has been completed successfully.

Cursor attributes:

% Isopen

% Notfound

% Found

% Rowcount is not a fixed value and increases with the increase in the number of fetch;

You can use the following statement to determine the number of fetch loads:
Exit when c_emp_cursor % rowcount> 10 or c_emp_cursor % notfound; 6: Parameter-based cursor: bulk collect batch import query results to the set Example 1: Using Arrays

SQL> declare 2 type emp_type is table of employees % rowtype index by pls_integer; 3 l_emp emp_type; 4 l_row pls_integer; 5 begin 6 select * Bulk collect into l_emp from employees; 7 dbms_output.put_line ('The count is: '| l_emp.count); 8 l_row: = l_emp.first; 9 While (l_row is not null) 10 loop 11 dbms_output.put_line (l_row | ': '| l_emp (l_row ). employee_id | '---->' | l_emp (l_row ). first_name); 12 l_row: = L_emp.next (l_row); 13 end loop; 14 end; 15/the count is: 1071: 198 ----> donald2: 199 ----> douglas3: 200 ----> assumer4: 201 ----> michael5: 202 ----> pat6: 203 ----> susan7: 204 ----> hermann8: 205 ----> shelley9: 206 ----> William 10: 100 ----> Steven 11: 101 ----> neena12: 102 ----> lex13: 103 ----> Alexander er14: 104 ----> bruce15: 105 ----> David 16: 106 ----> valli17: 107 ----> diana18: 108 ----> nancy19: 109 ----> daniel20: 110 ----> john21: 111 ----> Ismael22: 112 ----> Jose manuel23: 113 ----> luis24: 114 ----> den25: 115 ----> Alexander 26: 116 ----> shelli27: 117 ----> sigal28: 118 ----> guy29: 119 ----> karen30: 120 ----> invalid w31: 121 ----> adam32: 122 ----> payam33: 123 ----> shanta34: 124 ----> kevin35: 125 ----> julia36: 126 ----> irene37: 127 ----> james38: 128 ----> Steven 39: 129 ----> laura40: 130 ----> memory he41: 131 ----> james42: 132 ----> tj43: 133 ----> jason44: 134 ----> michael45: 135 ----> ki46: 13 6 ----> hazel47: 137 ----> renske48: 138 ----> Stephen 49: 139 ----> john50: 140 ----> joshua51: 141 ----> trenna52: 142 ----> curtis53: 143 ----> randall54: 144 ----> peter55: 145 ----> john56: 146 ----> karen57: 147 ----> Albert to58: 148 ----> gerald59: 149 ----> eleni60: 150 ----> peter61: 151 ----> David 62: 152 ----> peter63: 153 ----> cycler64: 154 ----> nanette65: 155 ----> oliver66: 156 ----> janette67: 157 ----> patrick68: 158 ----> allan69: 159 ----> Lind Sey70: 160 ----> louise71: 161 ----> sarath72: 162 ----> clara73: 163 ----> danielle74: 164 ----> mattea75: 165 ----> David 76: 166 ----> sundar77: 167 ----> amit78: 168 ----> lisa79: 169 ----> harrison80: 170 ----> MAID: 171 ----> William 82: 172 ----> MAID: 173 ----> sundita84: 174 ----> ellen85: 175 ----> alyssa86: 176 ----> jonathon87: 177 ----> jack88: 178 ----> kimberely89: 179 ----> charles90: 180 ----> winston91: 181 ----> jean92: 182 ----> martha9 3: 183 ----> girard94: 184 ----> nandita95: 185 ----> alexis96: 186 ----> julia97: 187 ----> anthony98: 188 ----> kelly99: 189 ----> assumer100: 190 ----> timothy101: 191 ----> randall102: 192 ----> sarah103: 193 ----> britney104: 194 ----> samuel105: 195 ----> vance106: 196 ----> alana107: 197 ----> the kevinpl/SQL process has been completed successfully.

Example 2: Use a cursor

DECLARE     CURSOR  ee　IS  SELECT * FROM employees;     TYPE emp_type IS TABLE OF ee%ROWTYPE INDEX BY PLS_INTEGER;     l_emp emp_type;     l_row PLS_INTEGER;BEGIN    OPEN ee;    FETCH ee BULK COLLECT INTO l_emp;    CLOSE ee;    DBMS_OUTPUT.PUT_LINE('The count is: '|| l_emp.COUNT);    l_row := l_emp.FIRST;    WHILE(l_row IS NOT NULL)    LOOP      DBMS_OUTPUT.PUT_LINE(l_row || ':'||l_emp(l_row).employee_id||'---->'||l_emp(l_row).first_name);           l_row:=l_emp.NEXT(l_row);     END LOOP;END;