Procedure with in , out parameters and having IS keyword with parameters list before BEGIN. In this case which result we will get after executing procedure.

PROCEDURE sample_proc (id in VARCHAR2, name in VARCHAR2, Course out varchar2)
IS error EXCEPTION, error_code VARCHAR2(100), courseID NUMBER, price NUMBER
BEGIN
 select * from student where ID =id and name=name;
END executePROC;

Which fields we will get as result after executing via mybatis.

The stored procedure having parameters with data type before BEGIN keyword. In this case the parameters after IS and Before BEGIN keyword how it will work. Please explain

CodePudding user response：

Which fields we will get as result after executing via mybatis.

None, the code has syntax errors and will not compile in Oracle.

You have:

• A statement starting with PROCEDURE and not CREATE PROCEDURE (assuming that it is not part of a package as you do not include a preceding CREATE PACKAGE statement).
• , following the variable declarations and not ;
• A SELECT statement in the PL/SQL scope that does not have an INTO clause.
• WHERE id = id AND name=name is (almost) the same as doing WHERE 1=1 AND 1=1 as the id and name values on both sides of the equality comparison will be from the local scope of the SQL statement and will not reference the procedure's arguments.
• Variables that you are not using.
• The procedure's identifier does not match the identifier after the final END statement.

To fix it you want something like:

CREATE PROCEDURE sample_proc (
  i_id     in  STUDENT.ID%TYPE,
  i_name   in  STUDENT.NAME%TYPE,
  o_Course out STUDENT.COURSEID%TYPE
)
IS
BEGIN
 SELECT courseid
 INTO   o_course
 FROM   student
 WHERE  id   = i_id
 AND    name = i_name;
END sample_proc;
/

db<>fiddle here

CodePudding user response：

IS keyword with parameters list before BEGIN.

They are not parameters, they are local variables.

As MTO has explained, the example you gave is not valid for a number of reasons.

But for the sake of argument, if you did actually have something like:

... PROCEDURE sample_proc (...)
IS
  error EXCEPTION;
  error_code VARCHAR2(100);
  courseID NUMBER;
  price NUMBER;
BEGIN
...

then the bit between IS and BEGIN is the optional declaration part, as described in the documention:

Declarative part (optional)

This part declares and defines local types, cursors, constants, variables, exceptions, and nested subprograms. These items cease to exist when the subprogram completes execution.

This part can also specify pragmas.

Note: The declarative part of a subprogram does not begin with the keyword DECLARE, as the declarative part of an anonymous block does.

The bit that is particularly relevant to your question (as I understand it anyway) is "These items cease to exist when the subprogram completes execution." They are local variables for use within the procedure, and not visible to or accessible by whatever calls the procedure.

The only data made visible to the caller is whatever it put into the OUT parameters.