Oracle's PL/SQL learning notes

I am learning Oracle is to do notes and experimental code records, the content is very full, but also quite detailed, send a blog post to the needs of friends, a total of 1w learning notes. is done before, has been at the bottom of the pressure, today took out to tidy up a bit, to everyone to share, there are shortcomings also hope you criticize.

PL/SQL definition: PL/SQL is a programming language developed by Oracle and dedicated to Oracle. PL---procedural Language. SQL-Structure querylanguage. PL/SQL Includes procedural statements and statements

PL/SQL Unit: block. A chunk can be nested inside a block.

Three components of a block:

One: Definition section (DECLARE)

PL/SQL The names used in variables, constants, cursors, and exceptions must be defined before they are used. and defined in the definition section beginning with the DECLARE keyword

II: Executable part: (BEGIN)

is a PL/SQL topic that contains the executable statement of the block, which defines the function of the block, which is a necessary part. Begins with the keyword begin, end ends

III: Exception Handling section: (Exception)

This section starts with exception and ends with end

    Demo:        declare– Optional            variable, constant, cursor, user-defined special type        begin– must            --sql statement            --pl/sql statement        exception– Optional        --Exception handling Section        END; -Must be declared, executed, and made up of exceptions

DEMO:        DECLARE            v_value1 VARCHAE2 (5);        BEGIN            SELECT cn_name            to  v_value1 from            table_name;        EXCEPTION when            exception_name  then            -handler ...        END;

PL/SQL The advantages of:

1. Improved performance: PL/SQL sends the entire statement block to the server, which is done in a single call, reducing network congestion

2. reusability: Can be run wherever Oracle is available

3. modularity: Each block in the program implements a logical operation and is effectively segmented.

PL/SQL Class of the BLOCK:

1. Anonymous blocks: can only be stored once and cannot be stored in the database

2. procedures, functions, and Packages (Procedure,function,package): A named PL/SQL block that is stored in a database and can be used more than once, and can be executed with an external program display.

3. Trigger: is a named PL/SQL block that is stored in the database and executed automatically when an event is triggered.

PL/SQL naming conventions for variables in:

1. Up to 30 characters

2. cannot be a reserved word

3. must start with a letter

4. does not allow the same column names as tables in the database

5. Do not include characters other than $,_ and numbers

PL/SQL the variables in

1. PL/SQL variables

a) scalar type: Only a single value can be stored

b) composite type

c) reference type

d) lobx type: Large data storage

2. Defining variable Syntax

a) variable name variable type: = Variable value

b) v_number number (2) not NULL: =20;

c) definition of constants

　　i. v_number CONSTANT

Number (2,3): = 20.098;

1 DEMO: Query employee number 7369 employee, put its job into v_job and output 2 DECLARE 3--The variable that defines the storage job v_job the same as the type of the reference variable and--emp.job,%type implements 4          v_job emp.job% TYPE; 5          --Define the employee number variable and assign the initial value (: =) 6          n_empno emp.empno%type:=7369; 7 BEGIN 8          --query Statement 9          SELECT EMP.JOB10          -- Assign the results to v_job11 into V_JOB12 from   emp13          WHERE emp.empno = n_empno;14          --Print output 15          Dbms_output.put_line (v_job);

%TYPE Properties:

Declaring a variable by%type is actually the type of the variable that references the variable or field in the table, and is kept in sync.

The variable will follow the following type declaration:

　　1. variable types that have been declared

　　2. Types of table fields in the database

Demo1:

Create an anonymous block, output Hello World

1--Create an anonymous block, output Hello World 2 DECLARE 3   v_hello varchar2: = ' Hello World '; 4 BEGIN 5   dbms_output.put_line (v_he Llo); 6 END; 7  8--Create an anonymous block, query the EMP table and show the employee's salary as ' SCOTT ', shown through the Dbms_output package. 9 DECLARE10    v_sal  emp.sal%type;11    v_name emp.ename%type: = ' SCOTT ', BEGIN13   SELECT emp.sal14 Into   v_sal15   From emp16   WHERE emp.ename = v_name;17   dbms_output.put_line (v_sal);

Demo2:

-Find the largest department number in the department table and output it to the screen

1 – Find the largest department number from the department table and output it to screen 2 DECLARE3   v_deptno dept.deptno%type;4 BEGIN5   SELECT MAX (dept.deptno) 6 into   v_ Deptno7 from   dept;8   dbms_output.put_line (V_DEPTNO); 9 END;

DEMO3:

--pl/sql nesting and scope of variables

--pl/sql nesting and variable scope declare    v_parent number: = 10; BEGIN   DECLARE     v_child number: =20;   BEGIN     dbms_output.put_line (' chile= ' | | V_child);     Dbms_output.put_line (' parent= ' | | v_parent);   END;   --dbms_output.put_line (' chile= ' | | V_child);  --note the scope of the variable   dbms_output.put_line (' chile= ' | | V_parent); End; Result: chile=20parent=10chile=10

Demo4:

--Select and print the total salary in the EMP table

1--Select and print the total wage in the EMP table 2 DECLARE3    v_sal emp.sal%type;4 BEGIN5   Select sum (emp.sal) 6 into   v_sal7 from   emp;8   Dbms_output.put_line (v_sal); 9 END

Demo5: operation of the transaction

1 DECLARE 2   v_sal emp.sal%type: =800; 3 BEGIN  4     UPDATE emp 5     SET emp.sal = emp.sal+ v_sal 6     WHERE emp.job= ' ANALYST '; 7   savepoint A; 8     UPDATE emp 9     SET emp.sal = emp.sal+ v_sal10     WHERE emp.job= ' ANALYST ';   SavePoint b;12   ROLLBACK to savepoint a;13   commit;14 END;

Writing control structures

1. Conditional Branching Statements

a) IF statement:

1. 1. i. – if – then – END if
   2. II. – IF – then – ELSE – END IF
   3. III. – IF – then – ELSEIF – c29> END IF

2. Conditional statement Syntax

a) IF condition Then

　　　　　　i. Statement;

b) [elsif condition Then

　　　　　　i. Statement;]

c) [ELSE

　　　　　　i. Statement;]

d) ENDIF;

3. DEMO:

a) IF v_name = 'SCOTT' and SAL >=

　　　　　　i. v_dept: =20;

b) END IF;

1 demo:2     --null processing 3 DECLARE 4   v_x number: =null; 5   v_y Number: = NULL; 6 BEGIN 7   IF v_x = V_y then 8     dbms_output.put_line (' null equals null '); 9   ELSE10     dbms_output.put_line (' null is not equal to null '),   one END if;12 end;13 result: null is not equal  to null empty is an unknown thing

4. Case statement: syntax (with return value)

　　

1 case demo:2 DECLARE 3   v_sal emp.sal%type; 4   v_dept emp.deptno%type; 5   v_result VARCHAR; 6 BEGIN 7   S Elect Emp.deptno 8 to   v_dept 9 from   emp10   WHERE emp.sal = one   (         SELECT MAX (emp.sal) from         E Mp14   );   dbms_output.put_line (v_dept);   v_result: =17 case   v_dept18 when ten then     ' Department one ' 19 When the then     ' Department two '     ELSE ' Department three '   end;22   dbms_output.put_line (v_result);   END;24 output: 25 1026 Part One

5. Looping statement loop:

DEMO : Loop Insert 11 Data

1 DECLARE 2    v_count Number: = 0; 3 BEGIN 4   LOOP 5     --Insert data 6     INSERT INTO 7     test (Name,id,password) 8     VALUES (' TEST ' | | V_count,v_count, ' admin '); 9     --variable plus 110     v_count: = v_count+1;11     --to determine exit conditions when     v_count > 10;13   end Loop;14 end;

b) for loop Loop

　　

1 demo:2 DECLARE 3   v_counter number: =0; 4 BEGIN 5   --v_counter is an auto-increment of 6 for   v_counter in 0. Loop 7     DELETE from Test 8     WHERE test.id = v_counter; 9   end Loop;10 end;

b) while loop

1 DECLARE 2    v_count Number: = 0; 3 BEGIN 4 while   v_count<10 LOOP 5     --Insert data 6     INSERT INTO 7     test (name , Id,password) 8     VALUES (' TEST ' | | V_count,v_count, ' admin '); 9     --variable plus 110     v_count: = v_count+1;11   end Loop;12 end;

Three: Composite type

1. Composite data Types

A ) A compound variable can hold multiple values

b) composite variable can be used multiple times after creation

c) as enumerated type and array

2. PL/SQL Record

a) there are many different types of fields in each record

b) null for fields without an initial value

c) record type declaration user-defined type

3. Define a record

a) syntax:

1 I.    TYPE Type_name is RECORD (2 ii.    Field Name 1    field type 1,3 Iii.    Field Name 2     field type (Iv.    ) ；

b) DEMO

I.    TYPE Emp_record_name is record (Ii.    V_name        varchar (c), Iii.    V_password  varchar (+) Iv.    ); V.    Emp_record  Emp_record_name;

--records are defined with the use of declare  TYPE Test_record_name is  record (     v_name test.name%type,     v_id   Test.id%type,     V_password   test.password%type  );   Test_record Test_record_name; BEGIN  SELECT Test.name,test.id,test.password  to   test_record from  test  WHERE test.name= ' TEST0 ';d bms_output.put_line (test_record.v_name| | test_record.v_id| | Test_record.v_password); END;

5 another definition of a record: Table name%rowtype

a) exp_row table_name%rowtype

DEMO:

1--record definition and use 2 DECLARE 3   emp_record emp%rowtype; 4  5 BEGIN 6   SELECT * 7 into   Emp_record 8 from   EMP 9   WHERE emp.empno= ' 7788 ';   dbms_output.put_line (emp_record.empno| | "  | | Emp_record.sal); one END;

Writing cursors

1. cursor Definition: A cursor is a workspace that Oracle opens in a database to hold the results of a SELECT statement query.

2. Classification of Cursors

a) implicit cursor: PL/SQL implicitly establishes and manages this cursor.

b) display cursors: defined and controlled by the programmer, multiple rows of data are read from the database, and a row of rows is processed from the multiline data.

3. Declaration of the cursor:

a) syntax: CURSOR cursor_name is select_statement;

b) in the cursor declaration, the SELECT statement cannot use the into statement, and the order sentence can be used in the word sentence.

c) Demo:

1 CURSOR emp_cursor IS2             SELECT * 3 from EMP;    

4. Open Cursor

a) syntax: OPEN cursor_name;

b) cursors should be opened before using cursors

c) The open cursor is actually the SELECT statement that is executed when the cursor is defined, and the query results are retrieved to the workspace.

d) if there are no rows to return without an exception

5. extracting a value from a cursor

a) syntax

　　　　　　i. FETCH cursor_name into [v1,v2...]| Record_name]

b) Open the cursor before using fetch, otherwise it cannot be used.

c) when using fetch for the first time on a cursor, the cursor points to the first record, and the cursor points to the next record after use.

d) cursors can only be moved downward and cannot be rolled back, if you want to fall back to the previous record, only turn the cursor off after opening.

e) The number, order, and data type of the variables in the into sentence must be consistent with the work area;

6. Close Cursors

a) syntax: CLOSE cursor_name

b) The cursor must be closed after processing the data, and if it is necessary to open the cursor again, once the cursor is closed and no more data is extracted from the cursor, all resources related to the cursor will be closed when the cursor is closed.

7. Use of Cursors Demo

1--Use of Cursors 2 DECLARE 3   --Define temporary variables to hold the contents of the cursor 4   emp_empno emp.empno%type; 5 emp_ename emp.ename%type; 6   --the definition is named Emp_ Cursor Cursors 7 cursor   emp_cursor is 8   SELECT emp.empno,emp.ename 9 from   emp;10 BEGIN11   --Open cursor   Emp_cursor;13   --loop output cursor--   for I in 1..5 LOOP15     --extracting the contents of a cursor     fetch emp_cursor + into     emp_empno , emp_ename;18     dbms_output.put_line (emp_empno| | |  | Emp_ename); loop;20   --Closes the cursor close emp_cursor;22 end;

Oracle's PL/SQL learning notes