Stored Procedure-Comparison Between MySQL and Oracle

Stored Procedure-Comparison Between MySQL and Oracle
1. introduction to Stored procedures our commonly used database language SQL statements must be compiled before execution, while Stored procedures (Stored Procedure) is a set of SQL statements for specific functions, which are compiled and stored in the database. You can specify the name of a stored procedure and specify parameters (if the stored procedure has parameters) to call and execute it. A stored procedure is a Programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed. Stored procedures generally have the following advantages: (1). Stored Procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation. (2) stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code. (3). the stored procedure can achieve fast execution speed. If an operation contains a large number of Transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow. (4). stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load. (5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security. Ii. MySQL Stored Procedure (1 ). format: MySQL stored PROCEDURE creation format: create procedure process name ([process parameter [,...]) [features...] case study of the process body: the MySQL stored procedure parameters are used IN the definition of the stored procedure. There are three parameter types: IN, OUT, And INOUT. The format is as follows: create procedure ([[IN | OUT | INOUT] Parameter Name Data class...]) IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, which is the default output parameter: this value can be changed within the stored procedure, and the INOUT input and output parameters can be returned: this parameter is specified during the call and can be changed or returned. 1) No parameter is required: mysql> delimiter // mysql> create procedure p1 ()-> begin-> declare v_ename varchar (10);-> decl Are 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 parameters: mysql> de Limiter // 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; run: 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 parameter: 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; run: m Ysql> 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) stored procedure loop while LOOP mysql> delimiter // mysql> create procedure p_while () begindeclare v_empno int; declare v_count int; set v_empno = 7788; set v_count = 0; while v_count <10 do insert into emp1 select * from emp where empno = v_empno; se T v_count = v_count + 1; end while; end; // mysql> delimiter; repeat loop: mysql> delimiter // mysql> create procedure p_repeat () begindeclare 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: mysql> delimiter // mysql> create procedure P_loop () begindeclare 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> 10 then leave loop_label; end if; end loop; end; // mysql> delimiter; 3. Oracle stored procedure without 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 ena Me, sal into v_ename, v_sal from emp where empno = & no; 8 dbms_output.put_line ('name is: '| v_ename |', '| 'salary is: '| v_sal); 9 exception 10 when no_data_found then 11 dbms_output.put_line ('You number is not crrect, please input again! '); 12 13 end; 14/Procedure created execution: SQL> exec proc1; PL/SQL procedure successfully completed SQL> set serverout onSQL>/Name is: SCOTT, Salary is: 3000 PL/SQL procedure successfully completed stored procedures with parameters: IN Parameter definition, IN, OUT, and IN OUT represent three different modes of parameters: IN: When a stored procedure is called, the form parameter in this mode receives the value of the corresponding real parameter, and it is read-only, that is, it cannot be modified. The default value is IN. OUT: this parameter can only be written and can only be assigned a value. The value cannot be read during the stored procedure. When the result is returned, the parameter value is passed to the corresponding real parameter. In out: all allow in parameters: SQL> create or replace procedure proc2 (v_empno emp. empno % type) is -- if the parameter mode is not specified, the default value 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 10 dbms_output.put_line ('You number is not crrect, pl Invalid input again! '); 11 12 end; 13/Procedure created execution: 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 10 dbms_output.put_line ('You number is not crrect, please input again! '); 11 12 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 number is not crrect, please Input again! '); 10 11 end; 12/Procedure created execution: SQL> var v_ename varchar2 (10); SQL> var v_sal number; // note: for the number type, the length of SQL> exec proc2 (7788,: v_ename,: v_sal) cannot be specified; Name is: SCOTT, Salary is: 3000 PL/SQL procedure successfully completed v_ename --------- SCOTTv_sal --------- 3000 run through anonymous blocks: SQL> var v_ename varchar2SQL> var v_sal numberSQL> declare 2 v_empno emp. empno % type: = 7788; 3 begin 4 proc2 (v_empno,: v_en Ame,: v_sal); 5 end; 6/Name is: SCOTT, Salary is: 3000 PL/SQL procedure successfully completedv_ename --------- SCOTTv_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 parameters: 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 into 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 number is not crrect, please input again! '); End;/run: SQL> var v_empno number; SQL> exec: v_empno: = 7788; PL/SQL procedure successfully completedv_empno --------- 7788 SQL> exec proc3 (: v_empno ); employees id is: 7788, Name is: SCOTT, Salary is: 3000 PL/SQL procedure successfully completedv_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/S QL 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; 10 dbms_output.put_line ('employee number is: '| v_empno |', '| 'name is:' | v_ename | ',' | 'salary is: '| v_sal ); 11 exception 12 when no_d Ata_found then 13 dbms_output.put_line ('You number is not crrect, please input again! '); 14 15 end; 16/Procedure created execution: 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 cycle: loop basic cycle: 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; 10 exit when v_count> = 10; 11 end loop; 12 13 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; 10 end loop; 11 12 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 <10 loop 9 insert into emp1 select * from emp where empno = v_empno; 10 v_count: = v_count + 1; 11 12 end loop; 13 end; 14/PL/SQL procedure successfully completed