Four stored procedures are created, which are simple stored procedure without parameters, stored procedure with two input parameters IN, stored procedure with one input parameter IN, one output parameter OUT and stored procedure with two input and output parameters IN OUT.
Create a global temporary emp table

CREATE GLOBAL TEMPORARY TABLE emp(
empno NUMBER(4),
ename VARCHAR2(8)
);

Add data

INSERT INTO emp VALUES(6500,'Bomb');

1. Create a simple stored procedure update_emp, which is used to change the name of empno 6500 employees in EMP table to CANDY, as follows:

CREATE PROCEDURE update_emp AS
BEGIN
    UPDATE emp SET ename='CANDY' WHERE empno =6500;
END update_emp;
/

Tip: [update_emp in END update_emp may not be written, but writing it will increase readability]

Calling stored procedures

EXECUTE update_emp;

View the results and see if they have been modified

SELECT ename FROM emp WHERE empno=6500;

The result: ename was modified.

2. Create two stored procedures update_emp2 with IN parameters, and set two IN parameters for the process, which are used to accept empno and ename values provided by users, respectively.

CREATE PROCEDURE update_emp2
( emp_num IN NUMBER , emp_name IN VARCHAR2) AS
BEGIN
 UPDATE emp SET ename=emp_name
 WHERE empno =emp_num;
END update_emp2;
/

To call a stored procedure, two parameters need to be set. There are two ways to call a stored procedure, as follows:
(1) No parameter name is specified

EXECUTE update_emp2 (6500,'XIAOQI');

(2) Designated parameter name

EXEC update_emp2(emp_name=>'XIAOQI',emp_num=>6500);

View the results and see if they have been modified

SELECT ename FROM emp WHERE empno=6500;

The same result: ename was modified.
3. Create the stored procedure select_emp, set an IN parameter and an OUT parameter for the procedure, in which the IN parameter accepts the empno value provided by the user, and then pass the ename value corresponding to the empno to the OUT parameter in the process body, as follows:

CREATE PROCEDURE select_emp
(emp_num IN NUMBER, emp_name OUT  VARCHAR2 ) AS
BEGIN
        SELECT ename INTO emp_name
        FROM emp WHERE empno =emp_num;
END select_emp;
/

When calling a stored procedure, if you need to display the return value of OUT parameter in the procedure, you also need to declare the corresponding variable to accept the return value in advance by using VARIABLE statement, and bind the variable when calling the procedure as follows:

VARIABLE emp_name VARCHAR2;//This is the wrong way to write it.
EXEC select_emp (6500, :emp_name);

A BUG appeared.

By searching for data, we all say that the type length does not match the problem. Finally, we carefully look for errors. We find that BUG does not occur when we do not set the length when defining variables. Solution:

VARIABLE emp_name VARCHAR2(8);

Note: This 8 must be the same as the character length set when creating the table at the beginning, otherwise it will cause an error. This is required by the VARCHAR2 type, not the NUMBER type.

Note: In the EXECUTE statement, when calling a bound variable, you need to add a colon (:) before the variable name.

To see the value in the variable emp_name, you need to use the PRINT command, as follows:

PRINT emp_name;

4. Create a stored procedure exchange_value by which the values in two variables are exchanged. The procedure is created as follows:

CREATE PROCEDURE exchange_value
(value1 IN OUT NUMBER,value2 IN OUT NUMBER)
AS
temp1 NUMBER;
temp2 NUMBER;
BEGIN
        temp1 := value1;
        temp2 := value2;
        value1 := temp2;
        value2 := temp1;
END exchange_value;
/

The exchange_value procedure is invoked, and the variables declared as IN OUT parameter assignment before invocation are used. After invocation, the SELECT statement is used to output the results of the exchange value, as follows:
Declare variables

VARIABLE value1 NUMBER;
VARIABLE value2 NUMBER;

Assignment of declarative variables (assignment symbols: =)

EXEC  :value1 :=2009;
EXEC  :value2 :=9002;

Execute stored procedures

EXEC exchange_value(:value1,:value2);

Look at the results of the swap

SELECT :value1,:value2 FROM dual;

Note: The EXEC command can be used to assign values to variables, and colons (:) need to be added before binding variable names.

5. Delete the stored procedure and delete the stored procedure exchange_value created above

DROP PROCEDURE exchange_value;

So far, the simple creation of stored procedures is over.