存储过程解题

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;