CREATE TABLE member_info( memberid NUMBER, memberlevel VARCHAR2(20), memberdis NUMBER) ; CREATE TABLE cost_info( memberid NUMBER, spend FLOAT, COST FLOAT) ; -- INSERT INTO member_info VALUES(1,'VIP',10); INSERT INTO member_info VALUES(2,'VIP',20); INSERT INTO member_info VALUES(3,'VIP',30); INSERT INTO member_info VALUES(4,'VIP',40); --创建函数 CREATE OR REPLACE FUNCTION function_mem(p_memeberid member_info.MEMBERID%TYPE,p_spend cost_info.SPEND%TYPE) return FLOAT IS v_cost cost_info.COST%TYPE; v_memberdis member_info.MEMBERDIS%TYPE; BEGIN SELECT memberdis INTO v_memberdis FROM member_info WHERE memberid = p_memeberid; v_cost := v_memberdis* p_spend; RETURN v_cost; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END ; --创建procedure CREATE OR REPLACE PROCEDURE proc_mem(p_memeberid member_info.MEMBERID%TYPE,p_spend cost_info.SPEND%TYPE) IS v_cost cost_info.COST%TYPE; BEGIN -- SELECT function_mem(p_memeberid,p_spend) INTO v_cost FROM dual; v_cost:=function_mem(p_memeberid,p_spend); INSERT INTO cost_info VALUES(p_memeberid,p_spend,v_cost); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(SQLERRM); END;
存储过程解题
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章