What is the difference between MySQL and Oracle's stored procedures?

MySQL stored procedures

(1). Format

Format created by the MySQL stored procedure: CREATE PROCEDURE procedure name ([process parameters [,...]])

[Features ...] Process Body

Case Analysis:

Parameters

The parameters of the MySQL stored procedure are used in the definition of stored procedure, there are three kinds of parameter types, in,out,inout, form such as:

CREATE PROCEDURE ([[In | Out | INOUT] Parameter name Data class ...])

In input parameter: The value that represents the parameter must be specified when the stored procedure is called, and the value of the parameter modified in the stored procedure cannot be returned as the default value

Out output parameter: This value can be changed inside the stored procedure and can be returned

INOUT input and OUTPUT parameters: specified at call, and can be changed and returned

1) without parameters:

Mysql>delimiter//

Mysql> CREATE PROCEDURE P1 ()

Begin

DECLARE v_ename varchar (10);

-Declare v_sal int;

Select Ename,sal into V_ename,v_sal from EMP where empno=7788; Select V_ename;

Select V_sal;

-End;

//

Query OK, 0 rows Affected (0.00 sec) mysql> delimiter;

Mysql> call P1 ();

+---------+

| V_ename |

+---------+

| SCOTT |

+---------+

1 row in Set (0.00 sec)

+-------+

| V_sal |

+-------+

| 3000 |

+-------+

1 row in Set (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

2) in Parameter:

Mysql> delimiter//

Mysql> CREATE PROCEDURE P2 (in V_empno int)

Begin

DECLARE v_ename varchar (10);

-Declare v_sal int;

Select Ename,sal into V_ename,v_sal from EMP where empno=v_empno; Select V_ename;

Select V_sal;

-End;

//

Query OK, 0 rows Affected (0.00 sec) mysql> delimiter;

Perform:

Mysql> call P2 (7788);

+---------+

| V_ename |

+---------+

| SCOTT |

+---------+

1 row in Set (0.00 sec)

+-------+

| V_sal |

+-------+

| 3000 |

+-------+

1 row in Set (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

3) Out Parameters:

Mysql> delimiter//

Mysql> CREATE PROCEDURE P3 (in v_empno int,out v_sal int)

Begin

DECLARE v_ename varchar (10);

Select Ename,sal into V_ename,v_sal from EMP where empno=v_empno; Select V_ename;

Select V_sal;

-End;

//

Query OK, 0 rows Affected (0.00 sec) mysql> delimiter;

Perform:

Mysql> Call P3 (7788, @v_sal);

+---------+

| V_ename |

+---------+

| SCOTT |

+---------+

1 row in Set (0.00 sec)

+-------+

| V_sal |

+-------+

| 3000 |

+-------+

1 row in Set (0.00 sec)

4) Cycle of stored procedures

While loop

Mysql> delimiter//

Mysql> CREATE PROCEDURE P_while ()

Begin

declare v_empno int;

declare v_count int;

Set v_empno=7788;

Set v_count=0;

While V_count <

INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

Set V_count=v_count + 1;

End while;

End

//

Mysql> delimiter;

Repeat cycle:

Mysql> delimiter//

Mysql> CREATE PROCEDURE P_repeat ()

Begin

declare v_empno int;

declare v_count int;

Set v_empno=7788;

Set v_count=0;

Repeat

INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

Set V_count=v_count + 1;

Until V_count > 10

End repeat;

End

//

Mysql> delimiter;

Loop loop:

Mysql> delimiter//

Mysql> CREATE PROCEDURE P_loop ()

Begin

declare v_empno int;

declare v_count int;

Set v_empno=7788;

Set v_count=0;

Loop_label:loop

INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

Set V_count=v_count + 1;

If V_count > ten Then

Leave Loop_label;

End If;

End Loop;

End

//

Mysql> delimiter;

Iii. Oracle Stored Procedures

With no parameters:

sql> Create or replace procedure Proc1 is

3 V_ename Emp.ename%type;

4 V_sal Emp.sal%type;

5

6 begin

7 Select Ename,sal to V_ename,v_sal from EMP where empno=&no;

8 Dbms_output.put_line (' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

9 exception

Ten when No_data_found then

Dbms_output.put_line (' You are not crrect, please input again! ');

12

The end;

14/

Procedure created

Perform:

sql> exec Proc1;

PL/SQL procedure successfully completed

Sql> set Serverout on

Sql>/

Name Is:scott, Salary is:3000

PL/SQL procedure successfully completed

Stored procedure with parameters:

In the parameter definition, in, out, and out represent three different modes of the parameter:

In: When a stored procedure is called, the parameter of the pattern receives the value of the corresponding argument and is read-only, which cannot be modified. Default is in.

Out: The formal parameter is considered to be writable and can be assigned only. Its value cannot be read in a stored procedure. When returned, passes the parameter value to the corresponding argument.

In out: both allow

In Parameters:

sql> Create or Replace procedure proc2 (V_empno emp.empno%type) is

--If the mode of the parameter is not specified, the default is in

2 V_ename Emp.ename%type;

3 V_sal Emp.sal%type;

4

5 begin

6 Select Ename,sal into V_ename,v_sal from EMP where empno=v_empno;

7 Dbms_output.put_line (' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

8 exception

9 when No_data_found Then

Ten Dbms_output.put_line (' You are not crrect, please input again! ');

11

The end;

13/

Procedure created

Perform:

sql> exec proc2 (7369);

Name Is:smith, Salary is:8000

PL/SQL procedure successfully completed

sql> Create or Replace procedure proc2 (V_empno in Emp.empno%type) is

2 V_ename Emp.ename%type;

3 V_sal Emp.sal%type;

4

5 begin

6 Select Ename,sal into V_ename,v_sal from EMP where empno=v_empno;

7 Dbms_output.put_line (' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

8 exception

9 when No_data_found Then

Ten Dbms_output.put_line (' You are not crrect, please input again! ');

11

The end;

13/

Out parameters:

sql> Create or Replace procedure proc2 (V_empno in Emp.empno%type, v_ename out emp.ename%type,v_sal out Emp.sal%type)

2 is

3

4 begin

5 Select Ename,sal into V_ename,v_sal from EMP where empno=v_empno;

6 Dbms_output.put_line (' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

7 exception

8 when No_data_found Then

9 Dbms_output.put_line (' You are not crrect, please input again! ');

10

one end;

12/

Procedure created

Perform:

sql> var v_ename varchar2 (10);

sql> var v_sal number; Note: The length cannot be specified for the number type

sql> exec proc2 (7788,:v_ename,:v_sal);

Name Is:scott, Salary is:3000

PL/SQL procedure successfully completed

V_ename

---------

SCOTT

V_sal

---------

3000

Execute via anonymous block:

sql> var v_ename varchar2

sql> var v_sal number

Sql> Declare

2 v_empno Emp.empno%type: = 7788;

3 begin

4 Proc2 (V_empno,: v_ename,:v_sal);

5 end;

6/

Name Is:scott, Salary is:3000

PL/SQL procedure successfully completed

V_ename

---------

SCOTT

V_sal

---------

3000

Sql> Declare

2 v_empno Emp.empno%type: = 7836;

3 V_ename Emp.ename%type;

4 V_sal Emp.sal%type;

5 begin

6 proc2 (V_empno, v_ename,v_sal);

7 End;

8/

In Out parameter:

Sql>

Create or Replace procedure proc3 (V_empno in Out Emp.empno%type)

Is

V_ename Emp.ename%type;

V_sal Emp.sal%type;

Begin

Select Ename,sal to V_ename,v_sal from EMP where empno=v_empno;

Dbms_output.put_line (' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

exception

When No_data_found Then

Dbms_output.put_line (' You are not crrect, please input again! ');

End

/

Perform:

sql> var v_empno number;

Sql> exec:v_empno:=7788;

PL/SQL procedure successfully completed

V_empno

---------

7788

Sql> exec proc3 (: v_empno);

Employees ID is:7788, Name is:scott, Salary is:3000

PL/SQL procedure successfully completed

V_empno

---------

7788

Sql> Declare

2 V_empno Emp.empno%type;

3 begin

4 v_empno:=7788;

5 proc3 (V_empno);

6 end;

7/

Name Is:scott, Salary is:3000

PL/SQL procedure successfully completed

sql> Create or Replace procedure proc3 (V_empno in Out Emp.empno%type)

2

3 is

4

5 V_ename Emp.ename%type;

6 V_sal Emp.sal%type;

7

8 begin

9 Select Empno,ename,sal into V_empno, v_ename,v_sal from EMP where empno=v_empno;

Ten dbms_output.put_line (' Employee number is: ' | | v_empno| | ', ' | | ' Name is: ' | | v_ename| | ', ' | | ' Salary is: ' | | V_sal);

exception

No_data_found Then

Dbms_output.put_line (' You are not crrect, please input again! ');

14

The end;

16/

Procedure created

Perform:

Sql> Declare

2 V_empno Emp.empno%type;

3 begin

4 V_empno: = 7788;

5 proc3 (V_empno);

6 end;

7/

Employee number is:7788, Name Is:scott, Salary is:3000

PL/SQL procedure successfully completed

Oracle Stored Procedure loops:

Loop Basic loop:

Sql> Declare

2 v_empno Emp.empno%type: = 7788;

3 V_count Number: = 0;

4

5 begin

6

7 loop

8 INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

9 V_count: = V_count + 1;

Ten exit when V_count >= 10;

one end loop;

12

The end;

14/

PL/SQL procedure successfully completed

For loop:

Sql> Declare

2

3 V_empno Emp.empno%type;

4

5 begin

6 V_empno: = 7788;

7

8 for I in 1..10 loop

9 INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

Ten end Loop;

11

The end;

13

14

15

16/

PL/SQL procedure successfully completed

While loop:

Sql> Declare

2 V_empno Emp.empno%type;

3 V_count Number: = 0;

4

5 begin

6 V_empno: = 7788;

7

8 while V_count < ten loops

9 INSERT INTO EMP1 SELECT * from emp where empno=v_empno;

Ten V_count: = V_count + 1;

11

The end loop;

The end;

14/

PL/SQL procedure successfully completed

What is the difference between MySQL and Oracle's stored procedures?