Oracle存储过程中变量赋值、取值判断、批量增加方法

示例

可复制：
CREATE OR REPLACE PROCEDURE SAVE_PRO_PER_RATE (v_pstp_id in VARCHAR2,v_PPI_ID in VARCHAR2,v_PSTP_DEP_ID in VARCHAR2,v_COMBINE_ID in VARCHAR2)
AS
countNum NUMBER;

BEGIN
SELECT COUNT(1) INTO countNum
FROM ppa_project_role_bouns where PPI_ID=v_PPI_ID AND PPRB_DEP_ID=v_PSTP_DEP_ID AND nvl(PPRB_COMBINE_ID,‘0’)=nvl(v_COMBINE_ID,‘0’);
if countNum > 0 then

return;

else

insert into 

ppa_project_role_bouns (
PPRB_ID, PPI_ID, PPRB_DEP_ID, PPRB_PRO_ROLE_ID,
PPRB_RATE_LOWER_LIMIT, PPRB_CURRENT_RATE,
PPRB_AE_RATE_LOWER_LIMIT, PPRB_AE_RATE,
PPRB_IS_FIXED,PPRB_IS_FIXED_PERSON,PPRB_OCCUPATION_TYPE
)
select
SYS_GUID(),
ppi.ppi_id, --项目ID
e.department_id, --部门ID
ppr.ppr_id, --角色ID
ppdi.ppdi_rate_lower_limit, --当期下限百分化
ppdi.ppdi_rate_lower_limit, --当期下限百分化
ppdi.ppdi_ae_rate_lower_limit, --预提下限百分化
ppdi.ppdi_ae_rate_lower_limit, --预提下限百分化
ppr.ppr_is_fixed, --是否固定
ppdi.ppdi_is_fixed , --是否指定到人
ppr.PPR_ROLE_TYPE --占比类型
from
ppa_pllc_detail_info ppdi
left join ppa_pllc_basic_info ppbi on ppdi.ppbi_id=ppbi.ppbi_id
left join PPA_SHEME_TO_PROJECT pstp on pstp.ppbi_id=ppbi.ppbi_id
left join ppa_project_info ppi on pstp.ppi_id=ppi.ppi_id
left join ppa_project_role ppr on ppdi.ppr_id=ppr.ppr_id
left join e_department e on pstp.pstp_dep_id=e.department_id
where ppbi.ppbi_scheme_draft=‘1’ and pstp.ppbi_id is not null
and ppdi.PPBI_ID=(SELECT PPBI_ID FROM PPA_SHEME_TO_PROJECT WHERE PSTP_ID=v_pstp_id AND PPBI_ID IS NOT NULL);

end if;
commit;
END;

存储过程在java中调用的方法

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestProcedure {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
Class.forName(“oracle.jdbc.driver.OracleDriver”);
conn = DriverManager.getConnection(“jdbc:oracle:thin:@xx.xxx.xxx.xxx:1521:orcl”,“xxx”,“xxx”);
CallableStatement cs = conn.prepareCall("{call SAVE_PRO_PER_RATE(?,?,?,? )}");
cs.setString(1,“179”);
cs.setString(2,“201210103049”);
cs.setString(3,“1001XT1000000000003K”);
cs.setString(4,“b1d7093ec8304719913b5b6355decc47”);
cs.execute();
cs.close();
}
}