Three parameter modes of in out inout in oracle mode of Jincang database

inout parameter mode of Oracle

Stored procedures and functions in Oracle can have parameters. The types of parameters can be specified as in, out and in out modes.
IN: used to pass IN parameters
When calling a procedure, the arguments are passed to the procedure
Within a procedure, formal parameters are treated as read-only and cannot be changed
When the procedure is executed and returned to the calling environment, the value of the actual parameter will not change
OUT: used to return the value
When calling the procedure, the value of the incoming formal parameter will be ignored. The formal parameter is like an uninitialized variable, and the value is null
Formal parameters can be read and written
After the procedure is executed, it returns to the calling environment (only when the program ends normally), and the formal parameter will be assigned to the argument
IN OUT: used to pass in parameters and return values
When the procedure is called, the argument value is passed to the procedure
After the procedure is executed, it returns to the calling environment (only when the program ends normally), and the formal parameters will be copied to the arguments

inout parameter mode of gold warehouse database

IN order to be compatible with oracle, Jincang database supports IN, OUT and IN OUT parameter modes IN oracle mode:
The official documents are described as follows:
KingbaseES stored procedures and functions provide three parameter modes: IN, OUT and IN OUT, which correspond to input, output and input-output semantics respectively. The IN parameter passes the value to the called subroutine; When the OUT/INOUT parameter is called, the variable needs to be passed IN. After the call, the value of the argument variable will be modified. When the parameter mode is not declared, it defaults to IN type.
KingbaseES supports schema parameters. Compared with the standard specifications of mainstream databases and SQL, KingbaseES differs from them as follows:

The following is an example to understand

1. in parameter mode

If it is not specified, the default mode is IN parameter mode, and assignment creation is supported
Functions are consistent with stored procedures. Take functions as examples

create or replace function in_t000(i int) return void as 
begin
	i = i + 1;
        raise notice 'i = %', i;
end;

Call through anonymous blocks to view the results

As can be seen from the above function example:
1. When calling a function, the argument is passed to the function
2. Inside a function, formal parameters are read-only and cannot be changed
3. When the function is executed and returned to the calling environment, the value of the actual parameter will not change (k=1)
Consistent with oracle

The in parameter also supports creation without assignment,

create or replace function in_t000(i int) return void as 
begin
    raise notice 'i = %', i;
end;

Call block, view results anonymously

2. out parameter mode

out parameter: the calling method needs to pass parameters (the passed in value will be ignored), supports modifying the value of the actual parameter, and supports independent return value

create or replace procedure out_t000(j out int) as 
begin
    raise notice 'j = %', j;
	j := 10;
end;
/

Call via anonymous block

As can be seen from the above example:
When calling the procedure, the value of the incoming formal parameter will be ignored. The formal parameter is like an uninitialized variable, and the value is null (j =)
Formal parameters can be read and written
After the procedure is executed, it returns to the calling environment (only when the program ends normally), and the formal parameter will be assigned to the argument (k = 10)

3. inout parameter mode

in out parameter: the calling method needs to pass parameters, supports modifying the value of arguments, and supports independent return values

create or replace function inout_t001(i in out text)  return int as 
begin
    raise notice 'i = %', i;
	i := 't000';
	raise notice 'i = %', i;
	return 1;
end;

4. Merge example:

Create stored procedure p, including 2 in parameters, 1 out parameter and 1 in parameter. Procedure p is called twice through different actual parameters. The value of the actual parameter will be printed before each call, and the value of the actual parameter will be printed again after each call

CREATE OR REPLACE PROCEDURE p (
  a        int,  -- IN by default
  b     IN int,
  c    OUT int,
  d IN OUT int
) IS
BEGIN
  -- Print values of parameters:
  raise notice 'a = %', a;
  raise notice 'b = %', b;
  raise notice 'c = %', c;
  raise notice 'd = %', d;
 
  -- Can reference IN parameters a and b,
  -- but cannot assign values to them.
 
  c := a+10;  -- Assign value to OUT parameter
  d := 10/b;  -- Assign value to IN OUT parameter
END;
/

Call via anonymous block

DECLARE
  aa  CONSTANT int := 1;
  bb  int  := 2;
  cc  int  := 3;
  dd  int := 4;
  ee  int;
  ff  int := 5;
BEGIN
  raise notice 'aa = %', aa;
  raise notice 'bb = %', bb;
  raise notice 'cc = %', cc;
  raise notice 'dd = %', dd;
 
  p (aa, -- constant
     bb, -- initialized variable
     cc, -- initialized variable 
     dd  -- initialized variable
  );
 
  raise notice 'aa = %', aa;
  raise notice 'bb = %', bb;
  raise notice 'cc = %', cc;
  raise notice 'dd = %', dd;
  raise notice 'ee = %', ee;
  raise notice 'ff = %', ff;
 
  p (1,        -- literal 
     (bb+3)*2, -- expression 
     ee,       -- uninitialized variable 
     ff        -- initialized variable
   );
 
  raise notice 'ee = %', ee;
  raise notice 'ff = %', ff;
END;
/

The execution results are as follows, which is consistent with oracle:
NOTICE: aa = 1
NOTICE: bb = 2
NOTICE: cc = 3
NOTICE: dd = 4
NOTICE: a = 1
NOTICE: b = 2
NOTICE: c =
NOTICE: d = 4
NOTICE: aa = 1
NOTICE: bb = 2
NOTICE: cc = 11
NOTICE: dd = 5
NOTICE: ee =
NOTICE: ff = 5
NOTICE: a = 1
NOTICE: b = 10
NOTICE: c =
NOTICE: d = 5
NOTICE: ee = 11
NOTICE: ff = 1
ANONYMOUS BLOCK

[for more information about the National People's Congress Treasury database, see Warehouse document management system ]

Keywords: Database Oracle

Added by mobile target on Tue, 01 Mar 2022 18:10:55 +0200