Preface
In a function that the business requires that part of the logic of data processing must be implemented through Oracle's stored procedures, because stored procedures are rarely used to process data logic, so a record is performed.
1. Oracle stored procedures
Oracle stored procedure is a reusable block of code stored in a database that is used to perform specific tasks or a series of SQL operations.
2. Creation of Oracle storage
2.1 Creation of package
2.1.1 package create
Define a method in a package, similar to an excuse in java.
(Example):
create or replace package testA is
PROCEDURE find(
pi$param1 IN NUMBER, --parameter1 number
pi$param2 IN VARCHAR2,--parameter2 String
pi$param3 IN DATE, --parameter3 date
--------------------输出parameter-----------------------
po$count OUT NUMBER, --Total number of
po$result OUT row_types.RESULTSET--Results Set
);
end testA ;- create or replace package testA is: testA corresponds to the package name;
- PROCEDURE find: find is the stored procedure name corresponding to the testA package name;
- pi$param1 IN NUMBER, --parameter 1 Number: IN represents the input parameter, that is, the parameter to be passed;
- po$count OUT NUMBER,: out represents the returned data
2.1.2 Stored procedures Common input/output parameter types
Number type (Number)
- Definition: used to store numerical data, which can represent integers, decimals, etc. For example, NUMBER(5,2) represents a number with a total of 5 digits, with the decimal part taking up 2 digits.
- Purpose: In the input parameters, it can be used to receive values such as product prices, employee wages, quantity, etc. In the output parameters, the calculated numerical results can be returned, such as the calculated total price, average value, etc.
Character type (VARCHAR2, CHAR)
definition:
- VARCHAR2 is a variable-length character type, and the storage length is determined based on the actual stored number of characters. For example, VARCHAR2(100) can store strings of up to 100 characters.
- CHAR is a fixed-length character type. If the actual number of characters is less than the defined length when stored, it will be filled with spaces. For example, CHAR(10) stores the length of 10 characters.
Purpose: As an input parameter, it is often used to receive strings such as name (such as employee name, product name), description information, etc. As output parameters, strings such as formatted names, error messages, etc. can be returned.
Date and time type (DATE, TIMESTAMP)
definition:
- The DATE type is used to store date and time information, precise to seconds. It stores information such as century, year, month, day, hour, minute, and second.
- The TIMESTAMP type is more precise than DATE, it can store decimal seconds and is more useful in handling scenarios such as date and time across time zones.
Purpose: In terms of input parameters, you can receive date and time values such as order date and employee entry date. In the output parameters, such as the calculated expiration date, operation completion time, etc. can be returned.
2.2 Creation of package body
package body is a specific implementation of stored procedures in package (example):
(1) Package body example:
create or replace package body testA is
PROCEDURE find(
pi$param1 IN NUMBER, --parameter1 number
pi$param2 IN VARCHAR2,--parameter2 String
pi$param3 IN DATE, --parameter3 date
--------------------输出parameter-----------------------
po$count OUT NUMBER, --Total number of
po$result OUT row_types.RESULTSET--Cursor result set
)
is
-- Variables can be declared here：exist Oracle In the stored procedure，useDECLAREKeywords to declare variables。
-- 变量声明通常放exist存储过程的ISorASAfter the keyword，BEGINBefore keywords。The basic format of a declared variable is variable name Data Type [ := Initial value];
DECLARE
v_name VARCHAR2(50);
v_count NUMBER := 0;
v_1 VARCHAR2(50) :='';
begin
-- Processing business logic here
-- Finally return the result：
-- Direct assignment
po$count := quantity
-- passselect into Assignment
SELECT COUNT(1) INTO po$count FROM xxx;
-- pass游标返回数据集合
OPEN po$result FOR sql Statement;
end;
end testA ;(2) The result set definition cursor returns:
CREATE OR REPLACE PACKAGE row_types AS TYPE resultSet IS REF CURSOR; TYPE strSplit IS TABLE OF VARCHAR2 (40); Type uuidArray is Table OF number(20); END;
(3) Use the CALL or EXECUTE keyword to call stored procedures:
DECLARE
count_1 NUMBER;
result_1 row_types.RESULTSET;
TYPE result_record_type IS RECORD (
str1 VARCHAR2(500),
str2 VARCHAR2(4000),
str3 VARCHAR2(500),
str4 VARCHAR2(500),
str5 VARCHAR2(500),
str6 VARCHAR2(500),
str7 VARCHAR2(500),
str8 VARCHAR2(500),
str9 VARCHAR2(500),
str10 VARCHAR2(500),
str11 VARCHAR2(500),
str12 VARCHAR2(500),
str13 VARCHAR2(500),
str14 VARCHAR2(500),
str15 VARCHAR2(500),
str16 VARCHAR2(500),
str17 VARCHAR2(500),
str18 VARCHAR2(500),
str19 VARCHAR2(500),
str20 VARCHAR2(500),
str21 VARCHAR2(500),
str22 VARCHAR2(500),
str23 VARCHAR2(500),
num1 NUMBER,
num2 NUMBER,
num3 NUMBER,
num4 NUMBER,
num5 NUMBER,
num6 NUMBER,
num7 NUMBER,
num8 NUMBER,
num9 NUMBER,
DT DATE,
DT1 DATE,
DT2 DATE,
DT3 DATE,
DT4 DATE
);
result_row result_record_type;
begin
(1,'test',count_1,result_1);
DBMS_OUTPUT.put_line('DHIDHDIDIH:'||count_1);
loop
fetch result_1 into result_row.str1,result_row.DT;
EXIT WHEN result_1%NOTFOUND;
dbms_output.put_line('Result content:'||result_row.str1 ||'=='||
result_row.DT
);
end loop;
close result_1;
end;Summarize
This article records the creation of oracle stored procedures.
The above is personal experience. I hope you can give you a reference and I hope you can support me more.