PreparedStatement执行pgsql 传参 sql执行procedure语句

使用的是MySQL数据库

1. 存储过程的优点

-- 1）可以存储和管理SQL代码；
-- 2）执行快；
-- 3）可以加强数据安全性；（如：限制用户对数据的访问权限，取消对表的直接访问权限）

2. 创建存储过程 CREATE PROCEDURE

• 存储过程可以在Stored Procedures中查看；

-- 存储过程存储SQL语句，写法：
-- CREATE PROCEDURE 存储过程名字(参数)
-- BEGIN
--     存储过程的主体（SQL语句），*MySQL要求SQL语句用分号隔开，一条SQL语句也需要加分号
-- END
 
-- 如何将一整个存储过程打包给MySQL，而不只是存储过程主体呢？（其他DBMS不需要改动默认分隔符）
-- 需要将分割SQL语句的分号改成其他的分隔符，在CREATE PROCEDURE语句前加上默认分隔符DELIMITER语句，一般开发者使用两个美元符号$$，或者其他不会在SQL语句中用到的字符序列；
-- 注意END后面也要加上自定义的分隔符号，并且最后要把默认分隔符改为分号。
-- 添加默认分隔符后的存储过程写法：
-- DELIMITER $$
-- CREATE PROCEDURE 存储过程名字(参数)
-- BEGIN
--     存储过程的主体-SQL语句，*MySQL要求SQL语句用分号隔开，一条SQL语句也需要加分号
-- END$$
-- 
-- DELIMITER ;
 
-- 调用存储过程，用CALL语句，写法：
-- CALL 存储过程名字()


-- 创建存储过程：查询clients表，并将这个查询作为存储过程
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END$$

DELIMITER ;
-- 调用存储过程get_clients
CALL get_clients();
 
 
-- 练习：创建名字为get_invoices_with_balance的存储过程，存储发票结余大于0的查询结果
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
   -- 因为在之前已经创建过带有结余的视图，这里可以直接查询视图
	SELECT *
	FROM invoice_with_balance
	WHERE balance > 0;
END$$

DELIMITER ;

3. 使用MySQL工作台创建存储过程

• 选中“Stored Procedures”，右击“Create Stored Procedure”；
• 右侧会出现编写存储过程的窗口，在这里不需要改默认分隔符；
• 只需要修改存储过程名称，并编写存储过程主体即可。

4. 删除存储过程 DROP PROCEDURE

• 同一个存储过程的删除和创建代码放在同一个SQL文件中，不同的存储过程放在不同的SQL文件中，视图也一样；

-- 删除存储过程写法;
-- DROP PROCEDURE 存储过程名称
-- 若删除一个不存在的存储过程，MySQL会报错，因此加上IF EXISTS关键字
-- DROP PROCEDURE IF EXISTS 存储过程名称


-- 保存 删除和创建存储过程的代码,先写删除再写创建，这样每次执行后都会先删除原来的过程再创建更新后的存储过程；
DROP PROCEDURE IF EXISTS get_clients;

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END$$

DELIMITER ;

5. 参数

-- 实现：创建按洲state获取客户的存储过程，获取洲名，并返回所在洲的客户信息
DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
	 -- CHAR是参数类型，表示是确定字符个数的字符型，括号里的2表示2个字符；
    -- 也可以使用VARCHAR 也是字符型，但表示可变长度字符，即输入的字符个数不固定，只要不超过规定的字符个数即可，VARCHAR使用较多；
	state CHAR(2)
    -- 由于参数名与列名相同，需要给参数加上前缀或后缀区分，常用的方法有：
    -- 1、前缀 p_state  pState
    -- 2、后缀 state_param
    -- 3、也可以给列名之前加表别名用于区分
)
BEGIN
	SELECT *
    FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;
 
-- 调用存储过程，查询洲名为CA的数据
CALL get_clients_by_state('CA');
 
 
-- 练习：写一个存储过程，返回给定客户的发表，存储过程名字为get_invoices_by_client
DROP PROCEDURE IF EXISTS get_invoices_by_client;

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client
(
	-- 类型为INT时，可以不用规定个数；VARCHAR需要规定字符个数
	client_name VARCHAR(20)
)
BEGIN
    -- 注意：直接用*表示结果显示invoices表和clients表中所有的列
	 SELECT *
    FROM invoices i
    JOIN clients c USING (client_id)
    WHERE c.name = client_name;
END$$

DELIMITER ;
 
-- *注意：因为存储过程添加了形参，所以调用的时候，括号中一定要填写实参，不可以空着；
-- 查询客户名为Vinte的所有发票信息；
CALL get_invoices_by_client('Vinte');

6. 带默认值的参数

• 查找数据库数据类型的网址：mysql int size

-- 若存储过程调用者无法明确具体的实参，可以设置一个默认值；
-- 使用IF语句就需要用END IF结束；告诉MySQL IF语句改到哪里结束；

-- 第一种：查询值为空时，返回符合设置的默认值的数据；
-- 实现：创建按洲state获取客户的存储过程，且洲名为空，默认返回洲名为CA的客户信息；
DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
	state CHAR(2)
)
BEGIN
	IF state IS NULL THEN
		SET state = 'CA';
	END IF;
    
	SELECT *
    FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;
 
-- 查询洲名为空的客户
CALL get_clients_by_state(NULL);
 
 
-- 第二种：查询值为空，返回所有数据；
-- (1) 不为参数设置默认值，而是写成分段查询；以下为存储过程主体；这种写法不专业！
	IF state IS NULL THEN
		SELECT * FROM clients;
	ELSE
		SELECT *
		FROM clients c
		WHERE c.state = state;
	END IF;
 
-- (2) 把IF语句中的两段查询合并成一段，使用IFNULL()，以下为存储过程主体；
	SELECT *
	FROM clients c
    -- 使用IFNULL() 第一个参数为空时，就使用第二个参数
    -- 第一个state是存储过程定义的参数，第二个c.state是查询语句的state;
    -- WHERE子句也等于c.state = c.state;自身等于自身，则返回的也是整个表数据
	WHERE c.state = IFNULL(state,c.state);
 
-- 练习：创建存储过程get_payments,要求查询涉及client_id 和 payment_method_id的数据,
-- 若两个参数都为NULL，则输出表的所有数据；
-- 若其中一个参数不为NULL，则根据其中一个参数输出数据；
-- 若两个参数都不为NULL，则输出的数据要同时满足这两个条件；（以下代码直接在工作台创建）
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_payments`(
	client_id INT,
    payment_method_id TINYINT
)
BEGIN
	SELECT *
    FROM payments p
    WHERE
		p.client_id = IFNULL(client_id,p.client_id) AND
		p.payment_method = IFNULL(payment_method_id,p.payment_method);
END
 
-- 调用查询存储过程
-- CALL get_payments(NULL,NULL);

7. 参数验证 - 确保存储过程不会往数据库存储错误数据

• 查找错误代码的字符串常值的网址：sqlstate errors

-- *注意存储过程可以存储负值，但根据实际情况，有些数据不能为负值，因此还需要使用IF语句设置数据范围；
-- 过程更新数据
-- 添加三个参数 发票id INT; 付款金额 小数 DECIMAL(参数位数,小数位数); 付款日期 DATE
-- 实现：根据输入的发票id，修改这张发票的付款金额和付款日期。
DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment
(
	 invoice_id INT,
    -- 付款金额位数总共9位，其中小数位2位f
    payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
    -- 以下为验证存储过程的参数的方法
    -- 因为传递的参数可以为负数，而金额<=0 是错误数据，因此需要添加IF语句来验证付款总额参数
    IF payment_amount <= 0 THEN
    -- SIGNAL SQLSTATE '错误代码的字符串常量'
    -- 22003 - A numeric value is out of range 数值超出范围，也叫超位值
	     SIGNAL SQLSTATE '22003'
           -- 设置描述信息，可以帮助调用者识别失败原因
		SET MESSAGE_TEXT = '付款金额不得小于0';
    END IF;
    
   -- 以下为更新语句
	UPDATE invoices i
   SET 
	   i.payment_total = payment_amount,
      i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END$$

DELIMITER ;

8. 输出参数

-- 实现：查询客户发票未付款的发票总数和发票总金额
-- 直接在sql工作台创建的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_paid_invoices_for_client`(
	client_id INT,
   -- 因为需要输出发票数量和发票总金额，所以需要在参数前面添加OUT关键字前缀，表示输出参数
   OUT invoice_count INT,
   OUT invoice_total DECIMAL(9,2)
)
BEGIN
    -- 查询不同客户，且付款金额为0的发票个数和发票总金额
	 SELECT COUNT(*),SUM(invoice_total)
    -- 以下代码表示读取数据后，复制给下面这些输出参数
    INTO invoice_count,invoice_total
    FROM invoices i
    WHERE i.client_id = client_id
		AND payment_total = 0;
END
 
-- 调用get_paid_invoices_for_client过程
-- *用@符号作前缀定义变量，这里定义了两个变量，初始值设为0；
set @invoice_count = 0;
set @invoice_total = 0;
-- 当调用过程时，需要传递这些变量，（用来存储调用过程后产生的结果集（我的理解））
call sql_invoicing.get_paid_invoices_for_client
   -- 第一个参数是invoice_id为3；
	(3, @invoice_count, @invoice_total);
-- 调用后，需要用选择语句来读取这些值并显示。
select @invoice_count, @invoice_total;

9. 变量 - 通常在调用有输出参数的存储过程时使用这些变量

• 使用场景：通常使用这些类型变量在存储过程中执行计算任务。

-- 两种变量
-- 第一种：用户或会话变量 - 在客户端会话过程中会被保存，但从MySQL断线后变量会被清空；
-- 定义变量：用SET语句定义，并用@符号作前缀 （如：set @invoice_count = 0;）
 
-- 第二种：本地变量 - 可以在存储过程或者函数内定义
-- 本地变量不会在整个客户端会话过程中被保存，一旦存储过程完成执行任务，这些变量就会被清空
-- 用DECLARE定义
 
-- 实现：定义风险因素 = 发票总金额 / 发票份数 * 5，并显示计算结果
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`()
BEGIN
	-- 以下为在存储过程中声明并使用本地变量
   -- *DECLARE只能在BEGIN 和 END 之间使用
   -- 定义risk_factor为精准的浮点型，位数为9，小数位有2位，默认值为0；
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
   -- 注意这里的发票总金额名称要与发票表中的发票金额名称区分开来，不然结果为NULL（我自己试的是这样）
	DECLARE invoices_total DECIMAL(9,2);
   DECLARE invoices_count INT;
    
	-- 把发票份数和发票总金额给到变量
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoices_count,invoices_total
    FROM invoices;  
    
    -- 计算risk_factor
    SET risk_factor = invoices_total / invoices_count * 5;
    
    -- 获取并显示risk_factor
    SELECT risk_factor;
END

10. 函数 - 与存储过程很像，但只能返回单一值

• 函数和存储过程的区别：函数只能返回单一值，存储过程可以返回多行和多列的结果集。
• 我们自己创建了函数后，可以在选择语句中使用。

-- 实现：将风险因素创建为函数，进行调用
-- 以下是直接在工作台创建的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`(
	client_id INT
) 
-- RETURNS后面接的是数据类型，这里是整型，也可以换成MySQL其他的数据类型；
RETURNS int(11)
-- RETURN函数后面紧接着就要设置函数属性，每个MySQL都至少要有一个属性
-- 这里只需要读取数据库中的数据，所以只添加了读取属性即可；
    READS SQL DATA
-- 除此之外，MySQL还有很多函数属性
-- 如 DETERMINISTIC 确定性 意思是给这个函数的是同样的一组值，那么返回值也永远是一样的值；（没看懂，我理解的是，这组值是确定的，所以给的一直都是这组值，所以是同样的一组值）
-- 使用场景：不想根据数据库中的数据返回值的时候可以使用；
-- 如 MODIFIES SQL DATA 修改SQL数据 意思是函数中有插入、更新或者删除函数；
BEGIN
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
   -- 注意这里的发票总金额名称要与发票表中的发票金额名称区分开来，不然结果为NULL
	DECLARE invoices_total DECIMAL(9,2);
   DECLARE invoices_count INT;
    
	-- 把发票份数和发票总金额给到变量
   SELECT COUNT(*),SUM(invoice_total)
   INTO invoices_count,invoices_total
   FROM invoices i
   -- 这里要根据客户id来计算每个客户的风险因素
   WHERE i.client_id = client_id;
    
   SET risk_factor = invoices_total / invoices_count * 5;
    
	-- 函数最后需要一个返回值，这里是返回计算的风险因素risk_factor
   -- 因为该客户无发票时，他的COUNT(*) = 0,SUM(invoice_total) = NULL
   -- NULL / 0 = NULL，所以我们希望risk_factor为NULL时，输出为0，因此可以使用IFNULL()
	RETURN IFNULL(risk_factor, 0);
END
 

-- 实现：查询在客户表中客户id、姓名对应的风险因素
SELECT
	 client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients;
 

-- 删除函数
DROP FUNCTION IF EXISTS get_risk_factor_for_client;

11. 其他约定

-- 函数、存储过程的命名有很多种，以下列举可以有哪些命名约定：
-- 1）加前缀，函数加fn，存储过程加proc
-- 2）小驼峰，如函数fnGetRiskFactor，存储过程procGetRiskFactor
-- 3）加下划线，如函数fn_get_risk_factor，存储过程proc_get_risk_factor
-- 4）或者有人不喜欢加前缀，直接写名字，遵循当前公司的约定即可。
 
-- 存储过程中需要添加默认分隔符DELIMITER 有$$ 或者 // 或者其他，也是遵循当前公司的约定即可。