Learning destination:
- PLSQL programming: Hello World, program structure, variables, process control, cursor
- Stored procedure: concept, parameterless storage, parameterless storage (input, output)
- JAVA call stored procedure
1. PLSQL programming
1.1. Concept and purpose
What is PL/SQL?
- PL/SQL(Procedure Language/SQL)
- PLSQL is a procedural extension of Oracle to sql language (similar to Basic)
- It refers to the addition of process processing statements (such as branches, loops, etc.) in SQL command language, so that SQL language has process processing ability
1.2. Program structure
Create a program template through the Test Window of the Plsql Developer tool or write it in the SQL Window through statements
Tip: the PLSQL language is case insensitive
PL/SQL can be divided into three parts: declaration part, executable part and exception handling part.
DECLARE -- Declare variables and cursors. I INTEGER; BEGIN -- Execute statement --[exception handling] END;
The DECLARE part is used to DECLARE variables or cursors (result set type variables). If there are no variables in the program, the declaration can be omitted
1.3. Hello World
BEGIN
--Print hello world
DBMS_OUTPUT.PUT_LINE('hello world');
END;
Where DBMS_OUTPUT is a built-in package for oracle, which is equivalent to System.out in Java, while PUT_LINE () is the method called, which is equivalent to the println () method
You can also write and run PLSQL programs in sqlplus:
SQL> BEGIN
2
3 --Print hello world
4
5 DBMS_OUTPUT.PUT_LINE('hello world');
6
7 END;
8 /
PL/SQL The process completed successfully.
The output result is not displayed after execution. By default, the output option is off. We need to turn on set serveroutput on
To execute the PLSQL program in SQLPLUS, you need to add a / to mark the end of the program at the end of the program
1.4. Variables
Common variables in PLSQL programming are divided into two categories:
- Common data type (char,varchar2, date, number, boolean, long)
- Special variable type (reference variable, record variable)
Variables are declared by
Variable name variable type (variable length) for example: v_name varchar2(20);
1.4.1. Common variables
There are two ways to assign variables:
- Direct assignment statement: = e.g.: v_name := ‘zhangsan’
- Statement assignment, using select... Into... Assignment: (Syntax: select value into variable)
[example] print personal information of personnel, including name, salary and address
-- Print personal information of personnel, including name, salary and address
DECLARE
-- full name
V_NAME VARCHAR2(20) := 'Zhang San'; -- Direct assignment of declared variables
--salary
V_SAL NUMBER;
--address
V_ADDR VARCHAR2(200);
BEGIN
--Direct assignment in program
V_SAL := 1580;
--Statement assignment
SELECT 'Shanghai Chuanzhi Podcast' INTO V_ADDR FROM DUAL;
--Print variables
DBMS_OUTPUT.PUT_LINE('full name:' || V_NAME || ',Salary:' || V_SAL || ',Address:' ||V_ADDR);
END;
1.4.2. Reference variable
The type and length of variables depend on the type and length of fields in the table
Specify the type and length of the variable through table name. Column name% type, for example: v_name emp.ename%TYPE;
[example] query the personal information of employee No. 7839 in emp table, and print the name and salary
-- query emp The personal information of employee No. 7839 in the form, print the name and salary
DECLARE
-- full name
V_NAME EMP.ENAME%TYPE; -- Direct assignment of declared variables
--salary
V_SAL EMP.SAL%TYPE;
BEGIN
--Query the name and salary in the table and assign it to the variable
--Note that the order, number and type of the queried fields and the assigned variables should be consistent
SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;
--Print variables
DBMS_OUTPUT.PUT_LINE('full name:' || V_NAME || ',Salary:' || V_SAL);
END;
Benefits of referential variables:
Using the common variable definition method, you need to know the TYPE of the column in the table, while using the reference TYPE does not need to consider the TYPE of the column. Using% TYPE is a very good programming style, because it makes PL/SQL more flexible and more suitable for updating the database definition.
1.4.3. Recorded variables
Accept a whole row of records in the table, which is equivalent to an object in Java
Syntax: variable name table name% ROWTYPE, for example: v_emp emp%rowtype;
[example]
Query and print the name and salary of employee 7839
-- query emp The personal information of employee No. 7839 in the form, print the name and salary
DECLARE
-- Record type variable accepts one line
V_EMP EMP%ROWTYPE;
BEGIN
--Record type variables accept a row of data in the table by default, and fields cannot be specified.
SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839;
--Print variables by variable name.Property to get the value in the variable
DBMS_OUTPUT.PUT_LINE('full name:' || V_EMP.ENAME || ',Salary:' || V_EMP.SAL);
END;
If there is a table with 100 fields, if your program wants to use these 100 fields, it will be particularly troublesome if you use reference variables to declare one by one. Record variables can easily solve this problem
Incorrect use:
1. Record type variables can only store one complete row of data
2. There are too many returned lines, and record variables cannot be received
1.5. Process control
1.5.1. Conditional branch
Syntax:
BEGIN
IF Condition 1 THEN Execution 1
ELSIF Condition 2 THEN Execution 2
ELSE Execution 3
END IF;
END;
Key words: ELSIF
[example] judge whether there are more than 20 records in the emp table, between 10-20, or less than 10 records
DECLARE
--Declare variable acceptance emp Number of records in table
V_COUNT NUMBER;
BEGIN
--query emp The number of records in the table is assigned to the variable
SELECT COUNT(1) INTO V_COUNT FROM EMP;
--Judgment printing
IF V_COUNT > 20 THEN
DBMS_OUTPUT.PUT_LINE('EMP There are more than 20 records in the table:' || V_COUNT || 'Article.');
ELSIF V_COUNT >= 10 THEN
DBMS_OUTPUT.PUT_LINE('EMP The number of records in the table is 10~20 Between:' || V_COUNT || 'Article.');
ELSE
DBMS_OUTPUT.PUT_LINE('EMP The number of records in the table is below 10:' || V_COUNT || 'Article.');
END IF;
END;
1.5.2. Cycle
There are three loop modes in ORACLE. We will not expand here, but only introduce one of them: loop loop
Syntax:
BEGIN
LOOP
EXIT WHEN Exit loop condition
END LOOP;
END;
[example] print numbers 1-10
DECLARE
--Declare a cyclic variable and assign an initial value
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;
2. Cursor
2.1. What is a cursor
It is used to temporarily store multiple rows of data returned by a query (result set, similar to the ResultSet set returned by Java's Jdbc connection). By traversing the cursor, you can access and process the data of the result set row by row.
Cursor usage: declare - > Open - > read - > close
2.2. Grammar
Cursor declaration:
CURSOR tag name [(parameter list)] IS query statement;
Cursor opening:
OPEN name;
Value of cursor:
FETCH cursor name INTO variable list;
Cursor closing:
CLOSE tag name;
2.3. Cursor properties
| Properties of the cursor | return type | explain |
|---|
Where% NOTFOUND returns TRUE when the element cannot be found in the cursor. It is usually used to judge whether to exit the loop
2.4. Create and use
[example] use the cursor to query the names and wages of all employees in the emp table and print them out in turn.
--Use cursor query emp The names and salaries of all employees in the table shall be printed out in turn.
DECLARE
--declare cursor
CURSOR C_EMP IS
SELECT ENAME, SAL FROM EMP;
--Declared variables are used to accept elements in cursors
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
--Open cursor
OPEN C_EMP;
--Traverses the value in the cursor
LOOP
--adopt FETCH Statement gets the value in the cursor and assigns it to a variable
FETCH C_EMP
INTO V_ENAME, V_SAL;
--adopt%NOTFOUND Judge whether there is a value,Value printing,If not, exit the cycle
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('full name:' || V_ENAME || ',salary:' || V_SAL);
END LOOP;
--Close cursor
CLOSE C_EMP;
END;
Execution results:
2.5. Cursor with parameters
[example] use the cursor to query and print the name and salary of employees in a department. The department number is entered manually during operation.
--Use the cursor to query and print the name and salary of employees in a department. The department number is entered manually at run time.
DECLARE
--Declare cursor pass parameters
CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS
SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO;
--Declared variables are used to accept elements in cursors
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
--Open cursor and pass parameters
OPEN C_EMP(10);
--Traverses the value in the cursor
LOOP
--adopt%NOTFOUND Judge whether there is a value,Value printing,If not, exit the cycle
EXIT WHEN C_EMP%NOTFOUND;
--adopt FETCH Statement gets the value in the cursor and assigns it to a variable
FETCH C_EMP
INTO V_ENAME, V_SAL;
DBMS_OUTPUT.PUT_LINE('full name:' || V_ENAME || ',salary:' || V_SAL);
END LOOP;
--Close cursor
CLOSE C_EMP;
END;
Note: the default value of NOTFOUND attribute is flash, so you should pay attention to the position of judgment conditions in the cycle. If you first judge that it is in FETCH, the value of the last record will be printed twice (multiple cycles are the default);
3. Stored procedure
3.1. Conceptual role
The PLSQL program we wrote before can perform table operation, judgment and circular logic processing, but can not be called repeatedly
It can be understood that the previous code is all written in the main method, which is an anonymous program. JAVA can solve the reuse problem by encapsulating objects and methods
PLSQL stores and reuses PLSQL business processing procedures. These stored PLSQL programs are called stored procedures
Stored procedure functions:
1. In the development program, for a specific business function, the connection to the database will be closed many times (connection and closing are very resource consuming), and the database needs to be read and written many times, with low performance. If these services are put into PLSQL, we can connect and close the database once only by calling PLSQL in the application, which can greatly improve the efficiency
2. ORACLE official advice: do not put those that can operate the database in the program. Basically, there will be no errors in the database, and there may be errors in the operation in the program. (if the data is operated in the database, there can be certain log recovery and other functions.)
3.2. Grammar
CREATE OR REPLACE PROCEDURE Process name[(parameter list)] IS BEGIN END [Process name];
According to the type of parameters, we divide them into three categories:
l without parameters
l with input parameters
l with input and output parameters (return value).
3.3. Storage without parameters
3.3.1. Create storage
Create stored procedures through Plsql Developer or statements:
[example] print hello world by calling stored procedures
Create stored procedure:
--Print by calling stored procedures hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END P_HELLO;
View the created stored procedure through the tool:
3.3.2. Calling stored procedure
- Call via PLSQL program:
BEGIN --Directly enter the name of the calling stored procedure P_HELLO; END P_HELLO;
- In SQLPLUS, call through EXEC command:
Tip: set serveroutput on is the prerequisite for displaying results in SQLPLUS
be careful:
The first problem: is and as can be used mutually. It doesn't matter which one to use. The second problem: there is no declare keyword in the process, and declare is used in the statement block
3.4. Stored procedure with input parameters
[example] query and print the name and salary of an employee (e.g. employee No. 7839) – stored procedure: it is required to pass in the employee number when calling and print it on the console automatically.
--Query and print the name and salary of an employee (such as employee 7839)
--It is required to pass in the employee number when calling and print it on the console automatically.
CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
--Declare variables to accept query results
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
--Query the name and salary according to the employee number passed by the user
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;
--Print results
DBMS_OUTPUT.PUT_LINE('full name:' || V_ENAME || ',salary:' || V_SAL);
END P_QUERYNAMEANDSAL;
Command call:
SQL> exec p_querynameandsal(7839); full name:KING,salary:5000 PL/SQL The process completed successfully.
PLSQL program call:
BEGIN P_QUERYNAMEANDSAL(7839); END;
Execution results:
3.5. Stored procedure with output parameters
[example] enter the employee number to query the information of an employee (employee 7839). It is required to output the salary as the return value to the calling program.
--Enter the employee number to query the information of an employee (employee 7839). It is required to output the salary as the return value to the called program. 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 P_QUERYSAL_OUT;
PLSQL program call:
DECLARE --Declare a variable that accepts the output parameters of the stored procedure V_SAL EMP.SAL%TYPE; BEGIN P_QUERYSAL_OUT(7839, V_SAL); --Note the order of parameters DBMS_OUTPUT.PUT_LINE(V_SAL); END;
Note: when calling, the parameters should be consistent with the order and type of the defined parameters
3.7. JAVA program calling 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 storage query to find your results
3.7.1. Analysis jdk API
The stored procedure can be called through the prepareCall method of the Connection object
Draw a conclusion: call the prepareCall method through the Connection object to pass an escape sql statement to call the stored procedure, and directly call the set method to pass the input parameters. After the output parameters need to be registered, execute the stored procedure and obtain them through the get method. The subscript of the parameter list starts from 1
3.7.2. Implementation code
Preparation environment:
l Import Oracle of jar package
[Example: query employee name and salary by employee number
package cn.itcast.oracle.jdbc;
import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class ProcedureTest {
public static void main(String[] args) throws Exception {
//1. Load drive
Class.forName("oracle.jdbc.driver.OracleDriver");
//2. Get the connection object
//2.1 setting connection string
String url ="jdbc:oracle:thin:@localhost:1521:xe" ;
String name = "scott";
String password = "tiger";
Connection conn = DriverManager.getConnection(url, name, password);
//3. Get statement object
String sql = "{call p_querysal_out(?,?)}";//Escape syntax, {call stored procedure (parameter list)}
CallableStatement call = conn.prepareCall(sql);
//4. Set input parameters
call.setInt(1,7839);
//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("salary:"+sal);
//8. Release resources
call.close();
conn.close();
}
}