Where PL/SQL stored procedures can go wrong with IDEA use [High energy ahead!]

1. What are stored procedures?

A named PL/SQL block stored in a database that is a type of database object that an application can call to execute logic.

2. Grammar:

create [or replace] procedure Stored procedure name --Three parameter modes: -- IN Incoming parameter (default, can be omitted) -- OUT Outgoing parameters, mainly used to return the results of a program --IN OUT Incoming and outgoing parameters ↓ (Parameter Name Parameter Mode Type,Parameter Name 2 Parameter Mode Type,....) --Parameter specifies only type, not length is|as --Variable declaration begin --Logical Code end;

There are two main operations; one is return value, the other is return value

1. No return value: case

--No return value --Using Stored Procedures t_area Add data id,name --Requirement: id Autogrowth(Query Sequence First,Get data,Re-execution insert) --Step 1;Create Stored Procedures create procedure add_area (v_name in varchar2) --When defining storage parameter types,Must not be capitalized is --Declare Variables v_id t_area.id%type; begin --Query from Sequence id,And assign to v_id select seq_stuno.nextval into v_id from dual; --Insert statement insert into t_area(id,name) values(v_id,v_name); --Submit commit; end;

How to call it?

--Mode 1 call p_add('tom',1,'1-4','30412',sysdate,1); --Mode 2: Call in process begin p_add('lucy',1,'1-5','30413',sysdate,1); end;

2. Return value; case

--Has Return Value --Using Stored Procedures t_area Add data id,name,And when calling a stored procedure,Print id create procedure add_area2 ( v_id out number, v_name in varchar2 ) is --Declare Variables begin --Query Sequence,And assign the result to v_id select seq_stuno.nextval into v_id from dual; --insert data insert into t_area(id,name) values(v_id,v_name); --Submit commit; end;

How to call it?

declare v_id t_area.id%type; begin add_area2(v_id,'Brilliant'); dbms_output.put_line(v_id); end;

3. Combining IDEA:

1. No return value:

Test class:

/** * Stored procedures; no return value added */ @Test public void testproAddarea(){ //Call add_area stored procedure areaMapper.addArea("Huang 1"); }

Mapper:

/** * Stored procedures; no return value added * @param name */ @Insert("call add_area(#)") // If a run error occurs, add the following comment to indicate that the stored procedure is executed // @Options(statementType = StatementType.CALLABLE) public void addArea(@Param("name") String name);

Underline: But something very useful, if run error, use it directly, he can add later when run error, solve error oh!!

Of course, it doesn't really matter if you don't use it in the absence of return values now, or if it works properly, then look at the following oh!!

2. Has a return value:

Test class:

/** * Stored procedures; added with return values */ @Test public void testproAddarea2(){ //Call add_area2 stored procedure Area area = new Area(); area.setName("Huang 3"); areaMapper.addArea2(area); System.out.println(area.getId()); }

Mapper:

/** * Stored procedures; added with return values * @param area */ //mode=OUT was added successfully so that the returned data is not null, @Insert("call add_area2(#,#)") //As long as there is an error, add this comment directly below to fix it @Options(statementType = StatementType.CALLABLE) public void addArea2(Area area);