mysql 视图与存储过程

#  CREATE VIEW

CREATE VIEW total_order AS 
SELECT 
	lo.loan_order_id,
	lo.submit_time,
	lo.loan_money
FROM t_loan_order lo
WHERE date(lo.submit_time) >= '2018-11-01'

SELECT * FROM total_order WHERE loan_order_id in (226855, 226861)

DROP VIEW total_order  # DELETE VIEW 

# 不能创建包含子查询的视图，解决方法是分成多个视图然后联结

# E.G

CREATE VIEW v1 AS SELECT 	  # 视图1
	loan_order_id,
	loan_money
FROM
	t_loan_order
WHERE 
	submit_time >= '2018-11-30'
AND order_status = 60

CREATE VIEW v2 AS SELECT 	  # 视图2
	lci.loan_order_id,
	lci.claims_status
FROM
	t_loan_claims_info lci

LEFT JOIN t_loan_order lo on lci.loan_order_id=lo.loan_order_id
WHERE lo.submit_time >= '2018-11-30'
AND order_status = 60

SELECT   # 联结视图
	v1.*,
	v2.claims_status
FROM v1, v2
WHERE v1.loan_order_id=v2.loan_order_id



#  CREATE PROCEDURE 
# CREATE PROCEDURE can't show data
CREATE PROCEDURE test_procedure()  # 无参数
BEGIN 
	SELECT 
		AVG(loan_money)
	FROM t_loan_order
	WHERE date(submit_time) >= '2018-11-01';
END;

CALL test_procedure()  # 调用过程

DROP PROCEDURE test_procedure   # DELETE PROCEDURE

# 将存储过程返回给一个特定的变量，并调用它

CREATE PROCEDURE test (
	OUT Avg_Loan_Money DECIMAL (8, 2),
	OUT User_Num DECIMAL (8, 2),
	OUT Total_Money DECIMAL (8, 2)
)
BEGIN

	SELECT AVG(loan_money) INTO Avg_Loan_Money
FROM t_loan_order 
WHERE 
	submit_time >= '2018-11-30' 
AND order_status = 60; # 每一条语句结束后加分号

SELECT
	count(*) INTO User_Num
FROM
	t_loan_order
WHERE
	submit_time >= '2018-11-30'
AND order_status = 60;

SELECT
	sum(loan_money) INTO Total_Money
FROM
	t_loan_order
WHERE
	submit_time >= '2018-11-30'
AND order_status = 60;

END;

# 调用过程
CALL test(@Avg_Loan_Money,
					@User_Num,
					@Total_Money);

# 展示变量

SELECT @Avg_Loan_Money

SELECT 
	@Avg_Loan_Money as 'Avg_Loan_Money',
	@User_Num as 'User_Num',
	@Total_Money as 'Total_Money'

SELECT  # 运用变量
FLOOR(loan_money-@Avg_Loan_Money) as 'test' 
FROM t_loan_order
WHERE
	submit_time >= '2018-11-30'
AND order_status = 60;

SELECT  # 变量相互运算
(a1 + a2+ a3) as 'sum'
FROM 
(SELECT 
	@Avg_Loan_Money as 'a1',
	@User_Num as 'a2',
	@Total_Money as 'a3') t2
 
# 未完待续...