SQL笔记-存储过程

待我称王封你为后i 2023-10-12 11:43 48阅读 0赞

### 一、 Oracle ###

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>p_name</td> 
   <td>存储过程名称</td> 
  </tr> 
  <tr> 
   <td>p_in_param1</td> 
   <td>输入参数1</td> 
  </tr> 
  <tr> 
   <td>p_out_param1</td> 
   <td>输出参数1</td> 
  </tr> 
  <tr> 
   <td>p_inout_param1</td> 
   <td>输入输出参数1</td> 
  </tr> 
 </tbody> 
</table>

CREATE OR REPLACE PROCEDURE p_name (
       p_in_param1 IN data_type1,
       p_out_param1 OUT data_type2,
       p_inout_param1 IN OUT data_type3
    ) AS
       -- 存储过程语句
    BEGIN
       -- 存储过程执行体
    END;
    /

### 二、MySQL ###

CREATE PROCEDURE p_name (
       IN p_in_param1 data_type1,
       OUT p_out_param1 data_type2,
       INOUT p_inout_param1 data_type3
    )
    BEGIN
       -- 存储过程执行体
    END;

### 三、SQL Server ###

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>@p_name</td> 
   <td>存储过程名称</td> 
  </tr> 
  <tr> 
   <td>@p_in_param1</td> 
   <td>输入参数1</td> 
  </tr> 
  <tr> 
   <td>@p_out_param1</td> 
   <td>输出参数1</td> 
  </tr> 
  <tr> 
   <td>@p_inout_param1</td> 
   <td>输入输出参数1</td> 
  </tr> 
 </tbody> 
</table>

CREATE PROCEDURE p_name (
       @p_in_param1 data_type1,
       @p_out_param1 data_type2 OUT,
       @p_inout_param1 data_type3 INOUT
    ) AS
    BEGIN
       -- 存储过程执行体
    END;

### 四、PostgreSQL ###

CREATE OR REPLACE FUNCTION p_name (
       p_in_param1 data_type1,
       OUT p_out_param1 data_type2,
       INOUT p_inout_param1 data_type3
    ) AS $$
    BEGIN
       -- 存储过程执行体
    END;
    $$ LANGUAGE 'plpgsql';

### 五、例子 ###

#### Oracle ####

CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       total_price DECIMAL(10, 2)
    );
    
    CREATE TABLE order_items (
       order_id INT,
       product_id INT,
       quantity INT,
       price DECIMAL(10, 2)
    );
    
    INSERT INTO orders VALUES (1, 1, '2022-01-01', 1000);
    INSERT INTO orders VALUES (2, 1, '2022-01-02', 2000);
    INSERT INTO orders VALUES (3, 2, '2022-01-03', 3000);
    
    INSERT INTO order_items VALUES (1, 1, 10, 100);
    INSERT INTO order_items VALUES (1, 2, 20, 200);
    INSERT INTO order_items VALUES (2, 1, 30, 100);
    INSERT INTO order_items VALUES (2, 2, 40, 200);
    INSERT INTO order_items VALUES (3, 1, 50, 100);
    INSERT INTO order_items VALUES (3, 2, 60, 200);
    
    CREATE OR REPLACE PROCEDURE get_order_summary (
       p_customer_id IN orders.customer_id%TYPE
    ) AS
       v_total_orders orders.total_price%TYPE;
       v_total_items NUMBER;
    BEGIN
       SELECT SUM(total_price)
       INTO v_total_orders
       FROM orders
       WHERE customer_id = p_customer_id;
    
       SELECT SUM(quantity)
       INTO v_total_items
       FROM order_items
       WHERE order_id IN (
          SELECT order_id FROM orders WHERE customer_id = p_customer_id
       );
    
       DBMS_OUTPUT.PUT_LINE('Total Orders: ' || v_total_orders);
       DBMS_OUTPUT.PUT_LINE('Total Items: ' || v_total_items);
    END;
    /
    
    EXECUTE get_order_summary(1);

#### MySQL ####

CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       total_price DECIMAL(10, 2)
    );
    
    CREATE TABLE order_items (
       order_id INT,
       product_id INT,
       quantity INT,
       price DECIMAL(10, 2)
    );
    
    INSERT INTO orders VALUES (1, 1, '2022-01-01', 1000);
    INSERT INTO orders VALUES (2, 1, '2022-01-02', 2000);
    INSERT INTO orders VALUES (3, 2, '2022-01-03', 3000);
    
    INSERT INTO order_items VALUES (1, 1, 10, 100);
    INSERT INTO order_items VALUES (1, 2, 20, 200);
    INSERT INTO order_items VALUES (2, 1, 30, 100);
    INSERT INTO order_items VALUES (2, 2, 40, 200);
    INSERT INTO order_items VALUES (3, 1, 50, 100);
    INSERT INTO order_items VALUES (3, 2, 60, 200);
    
    DELIMITER //
    
    CREATE PROCEDURE get_order_summary (
       IN p_customer_id INT
    )
    BEGIN
       DECLARE v_total_orders DECIMAL(10, 2);
       DECLARE v_total_items INT;
    
       SELECT SUM(total_price) INTO v_total_orders
       FROM orders
       WHERE customer_id = p_customer_id;
    
       SELECT SUM(quantity) INTO v_total_items
       FROM order_items
       WHERE order_id IN (
          SELECT order_id FROM orders WHERE customer_id = p_customer_id
       );
    
       SELECT CONCAT('Total Orders: ', v_total_orders) AS output1,
              CONCAT('Total Items: ', v_total_items) AS output2;
    END//
    
    DELIMITER ;
    
    CALL get_order_summary(1);

#### SQL Server ####

CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       total_price DECIMAL(10, 2)
    );
    
    CREATE TABLE order_items (
       order_id INT,
       product_id INT,
       quantity INT,
       price DECIMAL(10, 2)
    );
    
    INSERT INTO orders VALUES (1, 1, '2022-01-01', 1000);
    INSERT INTO orders VALUES (2, 1, '2022-01-02', 2000);
    INSERT INTO orders VALUES (3, 2, '2022-01-03', 3000);
    
    INSERT INTO order_items VALUES (1, 1, 10, 100);
    INSERT INTO order_items VALUES (1, 2, 20, 200);
    INSERT INTO order_items VALUES (2, 1, 30, 100);
    INSERT INTO order_items VALUES (2, 2, 40, 200);
    INSERT INTO order_items VALUES (3, 1, 50, 100);
    INSERT INTO order_items VALUES (3, 2, 60, 200);
    
    CREATE PROCEDURE get_order_summary (
       @p_customer_id INT
    ) AS
    BEGIN
       SELECT SUM(total_price) AS total_orders,
              SUM(quantity) AS total_items
       FROM orders
       JOIN order_items ON orders.order_id = order_items.order_id
       WHERE customer_id = @p_customer_id;
    END;
    
    EXECUTE get_order_summary @p_customer_id = 1;

#### PostgreSQL ####

CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       total_price DECIMAL(10, 2)
    );
    
    CREATE TABLE order_items (
       order_id INT,
       product_id INT,
       quantity INT,
       price DECIMAL(10, 2)
    );
    
    INSERT INTO orders VALUES (1, 1, '2022-01-01', 1000);
    INSERT INTO orders VALUES (2, 1, '2022-01-02', 2000);
    INSERT INTO orders VALUES (3, 2, '2022-01-03', 3000);
    
    INSERT INTO order_items VALUES (1, 1, 10, 100);
    INSERT INTO order_items VALUES (1, 2, 20, 200);
    INSERT INTO order_items VALUES (2, 1, 30, 100);
    INSERT INTO order_items VALUES (2, 2, 40, 200);
    INSERT INTO order_items VALUES (3, 1, 50, 100);
    INSERT INTO order_items VALUES (3, 2, 60, 200);
    
    CREATE OR REPLACE FUNCTION get_order_summary (
       p_customer_id INT
    )
    RETURNS TABLE (
       total_orders DECIMAL(10, 2),
       total_items INT
    ) AS $$
    BEGIN
       RETURN QUERY SELECT SUM(total_price), SUM(quantity)
       FROM orders
       JOIN order_items ON orders.order_id = order_items.order_id
       WHERE customer_id = p_customer_id;
    END;
    $$ LANGUAGE 'plpgsql';
    
    SELECT * FROM get_order_summary(1);

### 存储过程调用后的结果 ###

#### Oracle ####

执行 `EXECUTE get_order_summary(1);` 后，控制台输出：

Total Orders: 3000
    Total Items: 100

#### MySQL ####

执行 `CALL get_order_summary(1);` 后，结果为：

<table> 
 <thead> 
  <tr> 
   <th>output1</th> 
   <th>output2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Total Orders: 3000.00</td> 
   <td>Total Items: 100</td> 
  </tr> 
 </tbody> 
</table>

#### SQL Server ####

执行 `EXECUTE get_order_summary @p_customer_id = 1;` 后，结果为：

<table> 
 <thead> 
  <tr> 
   <th>total_orders</th> 
   <th>total_items</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3000.00</td> 
   <td>100</td> 
  </tr> 
 </tbody> 
</table>

#### PostgreSQL ####

执行 `SELECT * FROM get_order_summary(1);` 后，结果为：

<table> 
 <thead> 
  <tr> 
   <th>total_orders</th> 
   <th>total_items</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3000.00</td> 
   <td>100</td> 
  </tr> 
 </tbody> 
</table>

以上的例子都是基于多表关联查询的，通过存储过程可以将查询语句封装成一个可重用的模块，提高了代码的可维护性和可复用性。