1. Introduction to PLSQL
- Oracle database extends SQL, and then adds some programming language features, which can control the execution process of SQL, and then realize some complex business functions.
1.1 Composition of PLSQL
-
Component
declare - It's the declaration part. begin - yes plsql Subject part exception - Exception handling section end -
Note: There can be no declare and exception, but there can be no begin and end.
-
dbms_output.put_line(): Output content to the console.
- If you want to output content to the console, you must turn on the output function.
- set serveroutput on;
- This command only needs to be executed once, not every time.
1.2 Operator
- Arithmetic operators:+-*/
- The comparison operator: >>= <=!= <=!= <> (and!= are the same)
- Logical operators: or and not
- Assignment operator::=
-
Scope operator:..
-
Special assignments:
- You can use select in
1.3 Variable
- Syntax: variable name [constant] data type [:= value];
- If the constant keyword is specified, the variable must be initialized when it is declared, and the value of the variable cannot be changed.
- Common data types:
| data type | Effect |
|---|---|
| NUMBER | Numerical model |
| VARCHAR2 | String type |
| CHAR | Character |
| DATE | Date type |
| TIMESTAMP | Time type |
| BOOLEAN | Boolean type |
- Special data types
- % Type: The data type of this variable is the same as that of a variable or a field.
- % rowtype: Specifies that the data type is the same as a table and that only one row of data can be received using variables of that type.
1.4 Process Control
1.4.1 Conditional Judgment
-
Grammatical Format:
if conditional expression then Code... elsif conditional expression then Code... ... else Code... end if; -
Be careful:
- elsif has no e, no space
-
Code
-- Query the salary levels of employees with specified employee numbers,If it is greater than 3000, it is A;2000-3000 yes B,Other grades are C declare p_sal emp.sal%type; p_empno emp.empno%type := 7900; begin select sal into p_sal from emp where empno = p_empno; if p_sal > 3000 then dbms_output.put_line('The wage scale is A'); elsif p_sal > 2000 then dbms_output.put_line('The wage scale is B'); else dbms_output.put_line('The wage scale is C'); end if; end;
1.4.2 Loop Statement
-
Loop loop (similar to do..while loop in java)
-
Grammatical Format
loop Circulatory body exit when condition; -- Exit condition end loop; -
Example
-- Achieve additions of 1 to 10 declare p_sum number(4) := 0; -- Preserve the results of additions p_num number(4) := 1; begin loop p_sum := p_sum + p_num; p_num := p_num +1; exit when p_num > 10; end loop; dbms_output.put_line(p_sum); end;
-
-
while...loop loop (similar to the while loop in java)
-
Grammatical Format
while conditional expression loop Circulatory body end loop; -
Examples:
-- Achieve odd number addition between 1 and 10
declare p_sum number(4) := 0; p_count number(4) := 1; begin while p_count <= 10 loop if mod(p_count,2) = 1 then p_sum := p_sum + p_count; end if; p_count := p_count + 1; end loop; dbms_output.put_line(p_sum); end;
-
-
for loop
-
Grammatical Format
for variable name in start value... end value loop loop end loop;
-
Example
-- Realization 1-10 Addition of even numbers between declare p_sum number(2) := 0; begin for i in 1..10 loop if mod(i,2) = 0 then p_sum := p_sum + i; end if; end loop; dbms_output.put_line(p_sum); end;
-
-
Exercise: Output 99 multiplication tables
- dbms_output.put() output without newline
- dbms_output.new_line() to a new line
-
Examples:
set SERVEROUTPUT ON; begin for i in 1..9 loop for j in 1..i loop dbms_output.put(j || '*' || i || '=' || (j*i)); dbms_output.put(' '); end loop; dbms_output.new_line(); end loop; end;
1.5 exception handling
1.5.1 Grammatical Structure of Exception Handling
exception
when exception name 1 then
Exception handling statement
when exception name 1 then
Exception handling statement
.....
when others then
Exception handling statements...1.5.2 Keyword
- sqlcode: Number used to retrieve exceptions
- sqlerrm: Information used to retrieve exceptions
1.5.3 Classification of exceptions: predefined and custom exceptions
1.5.4 predefined anomalies
- Predefined exceptions can be searched in documents according to the number of exceptions.
- Some exceptions have names that can be captured, but not all exceptions have names. Some exceptions have only exceptions.
exceptionname.png
-
An example of anomaly capture with a name
set SERVEROUTPUT ON; declare p_temp number(4); begin p_temp := 10/0; -- Abnormal information exception when zero_divide then dbms_output.put_line('Divisor cannot be 0'); when others then dbms_output.put_line('Exception code:' || sqlcode); dbms_output.put_line('Abnormal information' || sqlerrm); end;
1.5.5 custom exception
- Exceptional name of grammatical structure
-
How Oracle throws exceptions
- Mode 1: Oracle automatically throws exceptions
- Way 2: Use raise keyword to throw exception by hand.
-
Mode 1 example:
-- Custom exception declare myEx exception; p_sal emp.sal%type; begin -- Judging employees'wages,If the wage is less than 1000 yuan, throw an exception. select sal into p_sal from emp where empno = 7369; -- 800 if p_sal < 1000 then raise myEx; --throw else dbms_output.put_line('Wages are still acceptable.'); end if; exception when myEx then dbms_output.put_line('alas,Wages are too low!'); when others then dbms_output.put_line('Exception code:' || sqlcode); dbms_output.put_line('Abnormal information' || sqlerrm); end; -
Throw an exception using raise_application_error(sqlcode,sqlerrms)
-- Custom exception declare myEx exception; p_sal emp.sal%type; pragma exception_init(myEx,-20001);-- Bind the specified exception number to an exception variable begin -- Judging employees'wages,If the wage is less than 1000 yuan, throw an exception. select sal into p_sal from emp where empno = 7369; -- 800 if p_sal < 1000 then RAISE_APPLICATION_ERROR(-20001, 'Well, the salary is too low.');-- throw else dbms_output.put_line('Wages are still acceptable.'); end if; exception when others then dbms_output.put_line('Exception code:' || sqlcode); dbms_output.put_line('Abnormal information' || sqlerrm); end;- Note: For anomalies without names, if you need to capture these anomalies, you need to bind the number of the exception to an anomaly variable first; use pragma exception_init (anomaly name, sqlcode) to bind the number of the exception to the name of the anomaly variable.
1.5 Transaction Control
1.5.1 The Concept of Transaction in Database
-
In a transaction, all DML operations are executed as a whole, either completely successful or totally unsuccessful. If a transaction does not end, the data in the transaction will only be valid in the current session.
-
Question: When will a transaction end?
- Submission transaction
- Roll back transactions
-
DDL (adding tables, modifying tables, deleting tables), CONN and other operations were performed.
-- Account Statement create table tb_account ( id number(4) primary key, name varchar2(20) unique, mon number(7,2) ) insert into tb_account values(1,'Dog baby',5000); insert into tb_account values(2,'godson',5000); declare p_temp number(2) := 12; begin update tb_account set mon = mon -1000 where name='Dog baby'; p_temp := p_temp / 0; update tb_account set mon = mon + 1000 where name='godson'; commit; -- Transaction submission dbms_output.put_line('Successful implementation'); exception when others then dbms_output.put_line('error message' || sqlerrm); rollback; -- rollback dbms_output.put_line('Transfer failure,Transaction rolled back'); end;
-
Setting savepoints: Syntax savepoint savepoints
- Setting a savepoint: Getting the transaction back to the savepoint
-
Note: When a transaction rollback is performed, the DML statement before the savepoint is unaffected.
-- Preservation Points begin savepoint a; insert into emp(empno,ename) values (1200,'jacky'); SAVEPOINT b; -- Preservation Points b insert into emp(empno,ename) values (1300,'rose'); --Transaction rollback to b rollback to b; end;
2. Cursor
2.1 Cursor Concept
- A cursor is a data type used to save a result set. A cursor is somewhat similar to an iterator in java.
- When using cursor to obtain data, first move the cursor, and then get the row data where the cursor is located.
2.2 Create cursors
-
Grammatical Format:
Cursor cursor name (parameter) is select statement.
2.2 Use cursors
-
open
- open cursor name;
-
Grab cursor data:
- fetch cursor into row variable
-
Close the cursor
- close cursor.
-
Example
-- Create a Cursor declare -- declare cursor cursor p_empCursor is select * from emp; -- Declare row variables p_emp emp%rowtype; begin -- open open p_empCursor; -- Get data for cursors fetch p_empCursor into p_emp; dbms_output.put_line(p_emp.ename); -- Close the cursor close p_empCursor; end;
2.3 Cursor Properties
- %found:If fetch Success,This property returns true,Otherwise return false - %notfound:If fetch Not Successful,This property will true,Otherwise return false - %rowcount:Returns the number of rows that the current cursor has traversed. - %isopen:Determine whether the cursor has been opened,If it's already opened,This property returns true,If not opened,This property returns false;Called if the cursor is not opened fecth,Errors will occur.(Tips:Invalid cursor.) set SERVEROUTPUT ON; -- Create a Cursor declare -- declare cursor cursor p_empCursor is select * from emp; -- Declare row variables p_emp emp%rowtype; begin -- open open p_empCursor; -- loop loop -- Get data for cursors fetch p_empCursor into p_emp; -- Close the cursor exit when p_empCursor%notfound; dbms_output.put_line('The employee's name is:' || p_emp.ename || 'Employee's salary is:' || p_emp.sal); end loop; dbms_output.put_line('Number of rows currently traversed:' || p_empCursor%rowcount); close p_empCursor; end;
2.3 Cursor for Loop
-
If you use the for loop to traverse the cursor, you do not need to open the cursor, fetch, and close the cursor operation.
-
Grammatical Format:
for variable in cursor loop Circulatory body end loop; -
Code examples
-- for...in Way traversal cursor declare cursor cur_emp is select * from emp; begin for p_emp in cur_emp loop dbms_output.put_line(p_emp.ename || 'The salary is' || p_emp.sal); end loop; end;
2.4 Parametric cursors
-
Usage: If you use a cursor with parameters, pass the parameters when you open the cursor
declare -- Declare cursors with parameters cursor p_empCursor(p_deptno emp.deptno%type) is select * from emp where deptno= p_deptno; -- Row variables p_emp emp%rowtype; begin -- open open p_empCursor(10); loop -- Get data for cursors fetch p_empCursor into p_emp; exit when p_empCursor%notfound; dbms_output.put_line('The employee's name is:' || p_emp.ename || 'Employee's salary is:' || p_emp.sal); end loop; dbms_output.put_line('Number of rows currently traversed:' || p_empCursor%rowcount); close p_empCursor; end; -
If it is a cursor for loop, you need to pass parameters to the cursor after the cursor name.
2.5 Implicit Cursor
- Implicit cursor is also called SQL cursor. Implicit cursor does not need to be created by users. When users perform DML operations, Oracle database will automatically create a cursor through which the state information of DML operations can be manipulated.
2.5.1 Implicit cursor properties
-
% notfound: If the DML operation is unsuccessful, the property returns true, or false.
-
% found: If the DML operation is successful, the property returns true, or false.
-
% rowcount: Returns the number of rows affected.
-- Implicit Cursor declare begin delete from emp where deptno is null; if sql%found then dbms_output.put_line('Successful deletion,Has been deleted' || sql%rowcount || 'Record'); else dbms_output.put_line('Delete failed'); end if; end;
3. Stored Procedures
3.1 Concept
- Stored procedure is a named PLSQL block. Through stored procedure, client can call PLSQL block of database server.
- benefit
- Improve the security of database
- The IO times are reduced and the execution efficiency of the program is improved.
- Enhanced code reusability
- shortcoming
- The portability ratio is poor. If you change the database code, it won't work.
3.2 Create stored procedures
-
Grammatical Format:
create or replace procedure Process name(parameter...) as --Declarations begin -- The main part of the process(PLSQL block) end - Examples:
-- Create stored procedures create or replace procedure proc_hello as begin SYS.DBMS_OUTPUT.PUT_LINE('hello procedure'); end; - Be careful
- No parentheses are needed if there are no parameters.
3.3 Use stored procedures
-
Mode 1: Call stored procedures inside PLSQL blocks:
begin Process name (parameter) end;-
Examples:
begin proc_hello; -- No parentheses are required if there are no parameters end;
-
-
Mode 2: Calling stored procedure outside PLSQL, the key word to be used is exec.
-- Calling stored procedures exec proc_hello;
3.4 Classification of Stored Procedures
1. According to the parameters:
- Storage Procedures with Input Parameters - Stored Procedures with Output Parameters - Storage Procedures with Input and Output Parameters
3.5 Storage Procedures with Input Parameters
-
Define input parameters: parameter name [in] parameter type;
-- Create a stored procedure,to emp Table Query Data create or replace procedure proc_add_emp(p_empno emp.empno%type,p_ename emp.ename%type) as begin insert into emp(empno,ename) values(p_empno,p_ename); end; -- to emp Insert data in exec proc_add_emp(1313,'to one's heart's content');
3.6 Storage Procedures with Output Parameters
- The function of output parameters is to export data from stored procedures to the outside of stored procedures.
-
Define output parameters: parameter name out type
-- Create stored procedures with output parameters,Achieve 1 to 10 additions,And output the result to the outside of the stored procedure /* Using stored procedures with output parameters, the sum of 1 to 10 is calculated and returned by parameters. */ create or replace procedure pro_1to10_sum( p_sum out number ) as tem_sum number(4):=0; begin for i in 1..10 loop tem_sum := tem_sum + i; end loop; p_sum := tem_sum; end; / -- Calling stored procedures declare p_sum number(4); begin pro_1to10_sum(p_sum); dbms_output.put_line('1 To sum up to 10:'|| p_sum); end;
3.7 Storage Procedures with Input and Output Parameters
-
Definition format: parameter name in out type; can be passed in or out
/* Use stored procedures with input and output parameters; query the employee's name and salary according to empno */ create or replace procedure pro_query_enameAndSal_by_empno( s_empno emp.empno%type, s_ename out emp.ename%type, s_sal out emp.sal%type ) as begin select ename,sal into s_ename, s_sal from emp where empno= s_empno; end; / -- Calling stored procedures declare p_ename emp.ename%type; p_sal emp.sal%type; begin --pro_query_enameAndSal_by_empno(7369, p_ename, p_sal); pro_query_enameAndSal_by_empno(7369, s_sal => p_sal, s_ename => p_ename); dbms_output.put_line('Employee number 7369 employee name is:'|| p_ename||'，Their wages are:'|| p_sal); end;
3.8 Call stored procedures using JDBC
3.8.1 Preparatory phase
-
Prepare a stored procedure:
-- demand,Inquire the number of the supervisor according to the number of the employee create or replace procedure proc_get_mgr_no( p_empno in out emp.empno%type) as begin select mgr into p_empno from emp where empno = d_empno; end; -
CallableStatement:
- It is a sub-interface of PrepareStatement; this interface is used to execute the stored procedure of SQL.
- setXxx(): Used to set input parameters;
- getXxx(): Used to get the value of the output parameter;
- RegiserOutParameter (): If the stored procedure has an output parameter, then the output parameter must be registered; (Specify the type of the output parameter).
3.8.1 Steps to invoke stored procedures using JDBC:
- Step 1: Get the connection to the database
- Step 2: Create a CallableStatement object;
- Step 3: If the stored procedure contains input parameters, set the value of the input parameters, and if the stored procedure contains output parameters, register the output parameters.
- Step 4: Execute stored procedures
- Step 5: If you have an output parameter, get the value of the output parameter.
- Step 6: Release resources
-
Code example:
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; /** * Calling stored procedures using JDBC * * @author like * */ public class Demo { public static void main(String[] args) { // First get the connection object, because jdbc4.0 has been automatically registered after the driver try (Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); // Create CallableStatement object CallableStatement call = con .prepareCall("{call proc_get_mgr_no(?)}"); ) { // Setting input parameters call.setInt(1, 7900); // Register output parameters. The purpose of registration is to set parameters. call.registerOutParameter(1, OracleTypes.NUMBER); call.execute(); // If there is an output parameter, the output parameter value is obtained. int mgrNo = call.getInt(1); System.out.println("The employee's supervisor's number is:" + mgrNo); } catch (SQLException e) { e.printStackTrace(); } } }
3.8 Delete stored procedures
- Grammar:
- drop procedure stored procedure name
4. Storage function
4.1 Concept
- Storage functions are very similar to stored procedure usage, but stored functions have return values, but stored procedures have no return values.
4.1 Create Functions
-
Grammatical Format:
create or replace function Function name(parameter...) return return type as -- Declarations begin -- The main part of a function end; -
Note that one difference between a function and a stored procedure is that the declaration of a function must have a return return value type.
-
Examples:
-- Create functions create or replace function func_hello return varchar2 as begin return 'hello function'; end;
4.2 Use functions
-
Using Functions in QLSQL Blocks
-- Using functions declare p_temp varchar2(20); begin p_temp := FUNC_HELLO; -- If there are no parameters, don't write parentheses dbms_output.put_line('The return value of the function is:' || p_temp); end; -
Use in SQL statements
-- stay sql Use functions in statements select func_hello from dual;
Classification of 4.3 Functions
- Functions with input parameters
- Functions with Output Parameters
- Functions with input and output parameters
4.4 Functions with Input Parameters
-- Define a function,Return the employee's annual salary based on the employee's number create or replace function func_get_year_sal (p_empno emp.empno%type) return number as p_sal emp.sal%type; p_comm emp.comm%type; begin select sal,comm into p_sal,p_comm from emp where empno = p_empno; return p_sal*12 + p_comm; end; select FUNC_GET_YEAR_SAL(7788) from dual;
4.5 Functions with Output Parameters
-- Define a function,Return the employee's name,wages,Annual salary;Requirement:Name of Employee,Wages are output by output parameters, -- Annual salary returned by return value create or replace function func_get_user_info( p_empno emp.empno%type, p_ename out emp.ename%type, p_sal out emp.sal%type ) return number as -- Local Variables of Functions p_comm emp .comm%type; begin select ename,sal,comm into p_ename,p_sal,p_comm from emp where empno = p_empno; return p_sal*12 + p_comm; end; -- Call function declare p_ename emp.ename%type; p_sal emp.sal%type; p_yearSal number; begin p_yearSal := func_get_user_info(7900,p_ename,p_sal); dbms_output.put_line('Full name:' || p_ename || ' wages:' || p_sal || ' Annual salary:' || p_yearSal); end;
4.5 Functions with Input and Output Parameters
- Generally, output parameters are seldom used and can be understood by oneself.
4.6 Use JDBC to call functions
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; /** * Calling functions using JDBC * * @author like * */ public class Demo2 { public static void main(String[] args) { // First get the connection object, because jdbc4.0 has been automatically registered after the driver try (Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); // Create CallableStatement object CallableStatement call = con .prepareCall("{?= call func_get_user_info(?,?,?)}");) { // Registered Return Value Type call.registerOutParameter(1, OracleTypes.NUMBER); // Setting the value of the input parameter call.setInt(2, 7900); // Register input parameters call.registerOutParameter(3, OracleTypes.VARCHAR); call.registerOutParameter(4, OracleTypes.NUMBER); // Execution function call.execute(); // Get the return value and output parameters int yearSal = call.getInt(1); String ename = call.getString(3); double sal = call.getDouble(4); System.out.println(ename + "The salary is:" + sal + "Annual salary is:" + yearSal); } catch (SQLException e) { e.printStackTrace(); } } }
- Be careful:
- To call a function with JDBC, the return value must be registered, and if there are output parameters, the output parameters must also be registered.
4.6 Delete function
- Grammatical Format:
- drop function function name
5. Zoning Technology
5.1 Partition Concept
- Partitioning technology is to divide a table into different parts, each part is a separate partition.
-
Advantage
- Dispersing data in different areas reduces the possibility of data corruption
- Separate partitions can be backed up and restored
- Mapping partitions to different physical disks
- Improve manageability, availability and performance.
-
Generally, it includes range partition, hash partition, list partition, composite partition, interval partition and system partition.
5.2 Scope Partition
- Range partitioning: Partitioning is based on the range of a column value.
-
The grammar of range partitioning:
After creating table Partition by range (field) Partition partition 1 values less than (value 1), Partition partition 2 values less than (value 2), .... Partition partition 3 values less than (maxvalue), ) -
Example
-- Scope partition create table emp2( empno number(4) primary key, ename varchar2(20), sal number(7,2), deptno number(4) ) partition by range(sal) ( partition p1 values less than(2000), partition p2 values less than(3000), partition p3 values less than(maxvalue) ); -- Initialization data insert into emp2 select empno,ename,sal,deptno from emp; -- Using partitions select * from emp2 partition(p1);
5.3 List Partition
-
List partitions can be managed according to the values of a column.
-- List partitioning create table emp3( empno number(4) primary key, ename varchar2(20), sal number(7,2), deptno number(4) ) partition by list(deptno) ( partition p1 values(10), partition p2 values(20), partition p3 values(30), partition p4 values(default) ); -- Initialization data insert into emp3 select empno,ename,sal,deptno from emp; -- Using partitions select * from emp3 partition(p4);