create or replace procedure 什么意思

当没有这个过程时，创建这个过程。
但是为了保证能够每次都创建，所以在有重名的旧过程存在时会将旧的替换为现在的。

存储过程的定义格式
,例子

CREATE OR REPLACE PROCEDURE SP_RECODE_BY_YEAR(INYEAR IN VARCHAR2,
MSG OUT VARCHAR2) AS

V_PROD_ID NUMBER(9);
V_PROD_NAME VARCHAR2(80);
V_QTY_YEAR NUMBER(9);
V_OLD_UNITPRICE NUMBER(9);
V_NEW_UNITPRICE NUMBER(9);

CURSOR CUR_PROD -- IS
SELECT A.PROD_ID, A.UNIT_PRICE, SUM(A.UNIT_PRICE * A.QTY)
FROM SALE_ITEM A
WHERE YEAR(A.ORDER_DATE) = INYEAR
GROUP BY A.PROD_ID, A.UNIT_PRICE;

BEGIN

OPEN CUR_PROD;
LOOP

FETCH CUR_PROD
INTO V_PROD_ID, V_OLD_UNITPRICE, V_QTY_YEAR;
EXIT WHEN CUR_PROD%NOTFOUND;

BEGIN
--
IF V_QTY_YEAR >= 30000 THEN
V_NEW_UNITPRICE := 0.6 * V_OLD_UNITPRICE;
ELSIF V_QTY_YEAR < 30000 THEN
V_NEW_UNITPRICE := 0.7 * V_OLD_UNITPRICE;
END IF;
END;
--QUERY_OUT
INSERT INTO QUERY_OUT
(产品编号, 产品名称, 年销售总额, 新单价)
VALUES
(V_PROD_ID,
(SELECT PROD_NAME FROM PRODUCT WHERE PROD_ID = V_PROD_ID),
V_QTY_YEAR,
V_NEW_UNITPRICE);
COMMIT;

END LOOP;
CLOSE CUR_CBL_SECT_ID;
MSG := 'Execute Successfully!';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SP_RECODE_BY_YEAR;

/

创建或更换程序