The following are personal study notes

The plsql language is case insensitive.

1. Program structure:

Plsql is divided into three parts: declaration part, executable part and exception handling part. Syntax:

declare
  --Declaring variables, cursors
  i integer;
begin
  --Execute statement

  --exception handling
end;

Example: print hello world (written in the test window)

begin
-- Print hello world
  DBMS_OUTPUT.PUT_LINE('hello world');
end;

(write in the command window) "set serveroutput on" to start the output command
To execute the plsql program in Sqlplus, you need to add / at the end of the program to indicate the end of the program.

SQL> set serveroutput on
SQL> begin
-- Print hello world
  DBMS_OUTPUT.PUT_LINE('hello world');
end;
/

2. Variables

(1) Common data type (char,varchar2,date,number,boolean,long)
(2) Special variable type (reference variable, record variable)

How to declare variables:

Variable name variable type(Variable length)   For example: v_name varchar2(20);

Variable assignment:
(1) Direct assignment (variable: = 'value')
V_name :='zhangsan'
(2) Statement assignment (select value into variable)

Common variables:

--Print personal information of personnel, including name, salary and address
declare
  -- full name
  v_name varchar2(20) := 'Zhang San';--Direct assignment when declaring variables
  --salary
  v_sal number;
  --address
  v_addr varchar2(200);
begin
  -- Direct assignment in program
  v_sal := 10000;
  -- Statement assignment
  select 'Beijing' into v_addr from dual;
  -- Print variables
  dbms_output.put_line('full name' || v_name || '，salary' || v_sal || '，address' || v_addr);
end;

Reference variable:
The type and length of variables depend on the type and length of fields in the table
Syntax: variable name table name Column name% TYPE;

--query emp Personal information of employee No. 1089 in the table, including name and salary
declare
  -- full name
  v_name emp.ename%TYPE; 
  --salary
  v_sal emp.sal%TYPE;
begin
  -- Query the information in the table and assign values to keep the field order consistent
  select ename,sal into v_name,v_sal from emp where id=1089;
  -- Print variables
  dbms_output.put_line('full name' || v_name || '，salary' || v_sal);
end;

Record type variable:
Accept a whole row of records in the table, which is equivalent to an object in java
Syntax: variable name table name% ROWTYPE

--query emp Personal information of employee No. 1089 in the table, including name and salary
declare
  -- Record type variable
  v_emp emp%ROWTYPE;
  --salary
  v_sal emp.sal%TYPE;
begin
  -- Query a row of records in the table and assign values
  select * into v_mep from emp where id=1089;
  -- Print variables
  dbms_output.put_line('full name' || v_emp.ename || '，salary' || v_emp.sal);
end;

3. Process control

(1) Conditional branch
Syntax:

begin
  if Condition 1 then Execution 1
   -- Pay attention to keywords elsif
  elsif Condition 2 then Execution 2
  else Execution 3
  end if;
end;

Example:

--judge emp Number of records in the table
declare
  -- Record type variable
  v_count number;
begin
  select count(1) into v_count from emp;
  if v_count > 20 then
    dbms_output.put_line('emp If the number of records in the table is more than 20, it is:' || v_count);
  elsif v_count >= 10 then 
    dbms_output.put_line('emp The number of records in the table is between 10 and 20, which is:' || v_count);
  else 
    dbms_output.put_line('emp If the number of records in the table is less than 10, it is:' || v_count);
  end if;
end;

(2) Loop loop
Syntax:

begin
  loop
    exit when Exit cycle condition
  end loop;
end;

Example:

--Print number 1-10
declare
  -- Declare loop variable
  v_num number := 1;
begin
  loop
    exit when v_num > 10;
    dbms_output.put_line(v_num);
    -- Cyclic variable self increment
    v_num := v_num + 1;
  end loop;
end;

4. Cursor

(1) Concept: it is used to temporarily store multiple rows of data returned by a query (result set, which is similar to the ResultSet set returned by Java jdbc connection). Through traversing the cursor, you can access and process the data of the result set row by row.
Cursor usage: declare - Open - read - close
(2) Cursor declaration syntax

-- Cursor declaration
  cursor Tour label[(parameter list)] is Query statement;
  --Cursor open:
  open Tour label;
  -- Value of cursor
  fetch Tour label into Variable list;
  -- Cursor closing
  close Tour label;

(3) Properties of cursors

Cursor application without parameters:

--Use cursor query emp The names and salaries of all employees in the form shall be printed
declare
  -- Cursor declaration
  cursor c_emp is select ename,sal from emp;
  -- Declare variables to receive data from cursors
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
begin
  
  --Cursor open:
  open c_emp;
  loop 
  -- Get data in cursor
  fetch c_emp into v_ename,v_sal;
  -- Exit cycle condition
  exit when c_emp%NOTFOUND;
  dbms_output.put_line(v_ename || '-' || v_sal);
  close loop;
  -- Cursor closing
  close c_emp;
end;

Cursor application with parameters:

--Use the cursor to query and print the name and salary of employees in a department. The department number is entered manually during operation
declare
  -- Cursor declaration(v_deptno Is the Department parameter passed)
  cursor c_emp(v_deptno emp.deptno%TYPE) is select ename,sal from emp where deptno = v_deptno;
  -- Declare variables to receive data from cursors
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
begin
  
  --Cursor open (pass parameters) v_deptno (value of)
  open c_emp(10);
  loop 
  -- Get data in cursor
  fetch c_emp into v_ename,v_sal;
  -- Exit cycle condition (Note: first fetch Get the data and make a judgment)
  exit when c_emp%NOTFOUND;
  dbms_output.put_line(v_ename || '-' || v_sal);
  close loop;
  -- Cursor closing
  close c_emp;
end;

5. Stored procedure

(1) Concept: the PLSQL program we wrote before can perform table operation, judgment, circulation and logical processing, but cannot be called repeatedly. It can be understood that the previous code is written in the main method, which is an anonymous program. Java can solve the problem by encapsulating objects and methods. PLSQL stores and reuses the business processing procedures of PLSQL. These stored PLSQL programs are called stored procedures.
(2) In order to reduce the performance of I/O, you need to connect to the database for many times. In order to develop a specific program, you need to close a database for many times. If we put these businesses into plsql, we can connect and close the database once only by calling plsql in the application, which can greatly improve the efficiency.
(3) Syntax (is can be replaced by as)

create or replace procedure Process name[(parameter list)] is
-- Declare variable (no) declare keyword, declare (used in statement blocks)
begin

end[Process name];

Nonparametric example:

create or replace procedure p_hello is
begin
  -- Print hello world
  dbms_output.put_line('hello world');
end p_hello;

Call in the test window:

begin
  -- plsql Call stored procedure
   pl_hello;
end;

Call in the command window:

SQL> set serveroutput on
SQL> exec p_hello;

Examples with input parameters:

-- Query and print the name and salary of an employee (such as employee 2189)
-- Stored procedure: pass in the employee number when calling, and print it automatically on the console
-- Stored procedure name(Input parameter name in Input parameter type)
create or replace procedure p_querynameAndSal(i_empno in emp.empno%TYPE) is
-- Declare variable
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
  -- query emp The name and salary of an employee in the table are assigned to the variable
  select ename,sal into v_name,v_sal from emp where empno = i_empno;
  dbms_output.put_line(v_name || '-' || v_sal);
end;

-- Called in the test window
begin
  -- plsql Call stored procedure
   pl_querynameAndSal(2189);
end;

Stored procedure with output parameter (return value):

-- Query and return the salary of an employee (such as employee 2189)
-- Stored procedure name(Input parameter name in Input parameter type,Output parameter name out Output parameter type)
create or replace procedure p_querySal_out(i_empno in emp.empno%TYPE,o_sal out emp.sal%TYPE) is
begin
  select sal into o_sal from emp where empno = i_empno;
end;

-- Test window call stored procedure
declare
 -- Declaration variables receive output parameters from stored procedures
 v_sal emp.sal%TYPE;
begin
  -- plsql Call stored procedure
   p_querySal_out(2189,v_sal);
   dbms_output.put_line(v_sal);
end;

Java program calls stored procedure:
Requirements: if a statement cannot realize the result set, such as multi table query or complex logical query, we can choose to call the stored procedure to query the required results.

public class ProcedureTest {
    public static void main(String[] args) {
        // 1. Load drive
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2. Create a connection
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        // 3. Get statement object
        String sql = "{call p_querySal_out(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
        // 4. Set input parameters
        call.setInt(1,2189);
        // 5. Register output parameters
        call.registerOutParameter(2, OracleTypes.DOUBLE);
        // 6. Execute stored procedures
        call.execute();
        // 7. Obtain output parameters
        double sal = call.getDouble(2);
        System.out.println(sal);
        // 8. Release resources
        call.close();
        conn.close();
    }
}

Hibernate call stored procedure:

/**
 * Direct call to stored procedure
 */

public void callProcedure(String procString,List<Object> params) throws Exception {
    CallableStatement stmt = null;
    try {
        stmt = this.getSession().connection().prepareCall(procString);
        if (params != null){
            int idx = 1;
            for (Object obj : params) {
                if (obj != null) {
                    stmt.setObject(idx, obj);
                } else {
                    stmt.setNull(idx, Types.NULL);
                }
                idx++;
            }
        }
        stmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
        throw new Exception("An error occurred while calling the stored procedure[sql = " + procString + "]", e);

    }

Or:

tx = session.beginTransaction();
Connection con=session.connection();
String procedure = "{call batchUpdateStudent(?) }";
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0); //Set the age parameter to 0
cstmt.executeUpdate();
tx.commit();

Test window and command window mentioned above: