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_hel LO); 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 E mp.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:
-
- i. – if – then – END if
- II. – IF – then – ELSE – END IF
- III. – IF – then – ELSEIF – c26> 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 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 em P14 ); 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 F ROM 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 --Extract the contents of the cursor from the fetch emp_cursor to Emp_empno, Emp_ename;18 dbms_output.put_line (emp_empno| | "| | Emp_ename); loop;20 --Closes the cursor close emp_cursor;22 end;
PL SQL Basics