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

Stored procedures in PL/SQL combined with IDEA

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(#{name})")
   // 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(#{id, mode=OUT,jdbcType=INTEGER},#{name})")
   //As long as there is an error, add this comment directly below to fix it
   @Options(statementType = StatementType.CALLABLE)
    public  void  addArea2(Area area);

Note here; if the parameter id in @Insert needs to be accompanied by its type and state

mode=OUT, let the returned data not be null, otherwise the error is null

 

jdbcType=INTEGER If not added, the following error will be reported,

 

So the underlined areas are indispensable, as long as all are added, the problem can be solved and run normally!!

 

 

Congratulations, you know a little more!!!

The more you know, the more you don't know!

~Thank you for reading like-minded, your support is my greatest motivation to learn! Go on, strangers work together, reluctantly!!

Tags: Database Stored Procedure SQL

Posted on Sat, 06 Jun 2020 12:41:23 -0400 by Daguse