数据库 函数/方法 插入语句(另一种写法)

先写插入语句,另一种写法

insert
	into
	public.plant_demand_temp (org_id,
	uid,
	user_id,
	row_num,
	inf_type,
	psi_period,
	bo_code,
	end_user_code,
	orderer_code,
	csn_code,
	customer_code,
	country_code,
	city_code,
	m_code,
	mfg_code,
	plt_shp_dt,
	shipping_dt,
	s_part_arv_dt,
	c_part_arv_dt,
	quantity,
	price,
	amount,
	currency,
	transport_mode,
	demand_accuracy,
	error_info,
	alert_info,
	del_flag,
	create_by,
	create_dt,
	modify_by,
	modify_dt)
select
	t1.org_id,
	t1.uid,
	t1.user_id,
	t1.row_num,
	'2' inf_type,
	t2.psi_period,
	'' bo_code,
	'' end_user_code,
	t1.oderer_code orderer_code,
	t1.consignee_code csn_code,
	t1.customer_code,
	t1.country_code,
	t1.city_code,
	t1.m_code,
	t1.plt_cd mfg_code,
	t1.delivery_date plt_shp_dt,
	t1.shipping_date shipping_dt,
	'' s_part_arv_dt,
	t1.eta_date c_part_arv_dt,
	t1.sales_qty::int quantity,
	0 price,
	0 amount,
	'' currency,
	t1.transport_mode,
	'' demand_accuracy,
	'' error_info,
	'' alert_info,
	'0' del_flag,
	'' create_by,
	hand_sys_now() create_dt,
	'' modify_by,
	hand_sys_now() modify_dt
from
	plant_if_gone t1
inner join (
	select
		psi_period
	from
		plant_m_psi_period_info
	where
		psi_in_use = '1') t2 on
	1 = 1
where
	t1.check_error is null
	and t1.del_flag = '0'
	and t1.org_id = 1
	and t1.uid = '20240416094216094_E0B6244'
	and t1.user_id = 'E0B6244'

函数/方法

CREATE OR REPLACE FUNCTION public.f_get_sales_price(p_org_id integer, p_m_code character varying, p_price_date character varying, p_cust_code character varying, p_currency character varying)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
declare
	p_result_count int8;
	p_f_price numeric;
	p_f_bo_code varchar;
begin
	select sale_price into p_f_price from plant_m_code_price where org_id=p_org_id and m_code=p_m_code and cust_code=p_cust_code and p_price_date>=effect_date and p_price_date<=lapse_date and currency_no=p_currency and price_type='0' and del_flag='0';
	//查询的数据的行数
	get diagnostics p_result_count = ROW_COUNT;
	//等于0表示没查到
	if p_result_count = 0 then
		select bo_code into p_f_bo_code from plant_m_cust where org_id=p_org_id and cust_code=p_cust_code and del_flag='0';
		select sale_price into p_f_price from plant_m_code_price where org_id=p_org_id and m_code=p_m_code and bo_code=p_f_bo_code and p_price_date>=effect_date and p_price_date<=lapse_date and currency_no=p_currency and price_type='1' and del_flag='0';
		get diagnostics p_result_count = ROW_COUNT;	
		if p_result_count = 0 then
			select sale_price into p_f_price from plant_m_code_price where org_id=p_org_id and m_code=p_m_code and p_price_date>=effect_date and p_price_date<=lapse_date and currency_no=p_currency and price_type='' and del_flag='0';
		end if;
	end if;
	//表示查到值了,返回这个值,没有查到这个值,则返回0.0 
	return coalesce(p_f_price,0.0);
END
$function$
;

函数调用方式

 insert into plant_demand_temp(
                org_id,
                uid,
                user_id,
                row_num,
                inf_type,
                psi_period,
                bo_code,
                end_user_code,
                orderer_code,
                csn_code,
                customer_code,
                country_code,
                city_code,
                m_code,
                mfg_code,
                plt_shp_dt,
                shipping_dt,
                s_part_arv_dt,
                c_part_arv_dt,
                quantity,
                price,
                amount,
                currency,
                transport_mode,
                demand_accuracy,
                create_by,
                modify_by
            ) values (
                {org_id},
                {uid},
                {user_id},
                {row_num},
                '1',
                {psi_period},
                {bo_code},
                {end_user_code},
                {orderer_code},
                {csn_code},
                {customer_code},
                {country_code},
                {city_code},
                {m_code},
                {mfg_code},
                {plt_shp_dt},
                {shipping_dt},
                f_get_days({org_id},{orderer_code},{shipping_dt}),
                f_get_days({org_id},{orderer_code},{shipping_dt}),
                {quantity},
                f_get_sales_price({org_id},{m_code},{shipping_dt},{orderer_code},{currency}),
                {quantity} * f_get_sales_price({org_id},{m_code},{shipping_dt},{orderer_code},{currency}),
                {currency},
                {transport_mode},
                {demand_accuracy},
                {create_by},
                {create_by});