Oracle存儲過程怎么理解

今天小編給大家分享一下Oracle存儲過程怎么理解的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

一、存儲過程通俗理解

• 簡單理解存儲過程是數據庫SQL的操作語言，用于操作表數據，類似Java的方法，可以有入參，也可以有出參。開發存儲過程需要熟悉一定的語法；

• 存儲過程是可以包含多個操作，如：表增刪改查、判斷、循環、異常捕獲、嵌套存儲過程等；

• 舉栗：有個業務需求，要求每天對購買商品大于1000元的買家發送抽獎信息短信及積分等級。 Java實現邏輯(僅僅是舉例哈)可能是通過訂單服務查詢訂單，然后再去用戶服務查詢用戶信息，然后再去積分服務獲取積分，獲取以上數據后，再通過定時任務去執行該需求。

• 分析：以上的操作涉及多個服務，并且發生多次數據庫的網絡IO連接，多次網絡交互會造成性能開銷大，導致不必要的資源浪費。 而若通過存儲過程實現，則邏輯是：【創建存儲過程–查詢用戶表–查詢訂單表–查詢積分表，使用存儲過程對上述數據遍歷判斷，數據插入到短信推送信息表】，這時Java代碼只需要一次與數據庫的IO鏈接獲取短信推送信息，直接去執行發送短信即可。

• Oracle數據庫有聲明，只要是對數據的操作可以使用存儲過程執行，速度比其他語言獲取數據再加工要快。

二、創建存儲過程基本語法(匯總)

CREATE OR REPLACE PROCEDURE P_存儲過程名(變量名 IN|OUT 數據類型)   -- 存儲過程名稱通常以P_開頭
IS													-- IS作為申明變量的關鍵詞
	V_NUM   	NUMBER;								-- 聲明NUMBER類型的變量，后以分號結束
	V_USERNAME  VARCHAR2(40);						-- 聲明VARCHAR2類型的變量
	V_SORT		INTEGER;							-- 聲明Integer類型的變量
	V_IS_BIND   NUMBER(12);							-- 聲明長度為12的NUMBER類型變量
	V_NAME 		T_USER.NAME%TYPE; 					-- 聲明變量直接賦值(表中NAME類型和長度就是V_NAME的類型和長度)
	V_USER 		T_USER%ROWTYPE;						-- 聲明記錄型變量，相當于Java的對象，可以使用點加變量名獲取值
	CURSOR T_USER IS SELECT NAME,AGE FROM T_USER;	-- 聲明游標
	-- 此處只作演示所用，其他變量聲明可參考博文下面的示例及注釋；
BEGIN												-- 執行代碼開始
	-- 執行的代碼邏輯，類似Java的方法體
	V_USER := '張三';								-- 給變量賦值。語法是冒號后面跟等號
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'YYYYMMDD'));	-- 打印語句，相當于Java中的println方法
EXCEPTION											-- 異常
  WHEN OTHERS THEN ...								-- OTHERS相當于Java中的Exception，會捕獲所有異常
END;												-- 執行代碼結束，以分號結束，也有一些是END后跟儲存過程名加分號

三、執行存儲過程的方式（5種）

-- 方式一，在SQL>后面執行
EXEC 存儲過程名(參數..);
或
EXECUTE 存儲過程名(參數..);

-- 方式二，在PL/SQL Developer的SQL窗口中執行（下有圖）
BEGIN
	存儲過程名(參數);	-- 執行的存儲過程需要加分號
END;

-- 方式三，在PL/SQL Developer的Test窗口中執行，可執行debug，或者編譯（下有圖）
BEGIN
	存儲過程名(參數);	--該種調用方式可調試
END;

-- 方式四，Java代碼調用
CallableStatement callableStatement = connection.divpareCall("{call 存儲過程名(?)}");

-- 方式五，在Mapper中調用，有入參和出參，使用<![CDATA[]]>包裹
<![CDATA[
	{CALL 存儲過程名(#{name,mode=IN,jdbcType=VARCHAR},#{age,mode=OUT,jdbcType=INTEGER})}
]]>

四、網上現有的創建存儲過程的兩種方式解釋（看注釋）

-- 方式一：該方式類似創建一個Java類，形成一個P_PUSH_MSG.sql的文件，該可以編譯后保存在數據庫，方便以后執行；
CREATE OR REPLACE PROCEDURE P_PUSH_MSG(V_RETCODE OUT VARCHAR2, V_RETINFO OUT VARCHAR2) IS...


-- 方式二：該方式類似直接寫的main方法，或者sql語句，不是文件，直接復制粘貼到其他地方執行、測試等。概念與上面方式一樣；
DECLARE
	V_IS_BIND   NUMBER(12);
BEGIN
	...
END;

五、一些存儲過程示例（僅供參考）

-- 示例一：數據庫造數，不用手動插入了
DECLARE
  I NUMBER := 0;
BEGIN
  FOR I IN 1 .. 500 LOOP					-- 循環語法，循環500次
    INSERT INTO T_USER
      (ID, USERID, USERNAME, AGE, CREATE_TIME)
    VALUES
      (I, 'U_' + I, 'XXYZ' + I, I, SYSDATE);
  END LOOP;									-- 循環語法結束
  COMMIT;									-- 事務提交
END;

-- 示例二：
CREATE OR REPLACE PROCEDURE P_ORDER_LIST(V_IN_DATE NUMBER) IS
BEGIN
  DECLARE									-- CREATE OR REPLACE PROCEDURE里面是可以有DECLARE的
    V_IS_BIND   NUMBER(12);
    V_IS_WORK NUMBER(12);
    V_COUNT_DATE    NUMBER(12);
  BEGIN
	V_COUNT_DATE := V_IN_DATE;
	DELETE FROM T_PUSH_MSG_TEMP WHERE DT = V_COUNT_DATE; --刪除臨時表表 T_PUSH_MSG_TEMP

	FOR I IN (SELECT A.ID CPU_ID, A.UCXM, A.ORGID, L.NAME ORGNAME, Y.REGINON_ID, Y.REGINON_NAME FROM JYXX A
				LEFT JOIN (SELECT LBO.ID   BRANCH_ID,LBO.ID   REGINON_ID,LBO.NAME REGINON_NAME FROM ORGANIZATION N1) Y
				  ON A.ORGID = Y.BRANCH_ID
				LEFT JOIN ORGANIZATION L
				  ON A.ORGID = L.ID) LOOP		-- 循環語法為：FOR X IN () LOOP...
	  
	  -- 插入表
	  INSERT INTO T_PUSH_MSG_TEMP
		(DT, CPU_ID, CPU_NAME, IS_BIND, IS_BIND_RATE, REGION_ID, REGION_NAME, BRANCH_ID, BRANCH_NAME)
	  VALUES
		(V_COUNT_DATE,
		 I.CPU_ID,
		 I.UCXM,
		 V_IS_BIND,
		 CASE V_IS_WORK WHEN 0 THEN 0 ELSE		-- CASE WHEN 用法
		 ROUND(V_IS_BIND / V_IS_WORK, 2) END,   -- ROUND函數用法
		 I.REGINON_ID,
		 I.REGINON_NAME,
		 I.ORGID,
		 I.ORGNAME);
	END LOOP;
  END;
END;

-- 示例三：
CREATE OR REPLACE PROCEDURE P_QYWX_TEXT_SHYJ_MSG() IS
	V_COUNT          	NUMBER; 				-- 數量
BEGIN  
	SELECT COUNT(*) INTO V_COUNT FROM T_ENTRY_INFO WHERE ENTRY_DT = V_DATE;		-- 賦值用法，INTO關鍵字到變量V_COUNT中
	IF V_COUNT = 0 THEN                                                         -- IF判斷用法
		-- 業務開始
		FOR A IN (SELECT CHARGE_NAME FROM T_ENTRY_INFO WHERE DEPT IN ('好好學習部','天天向上部')) LOOP	-- 循環用法
			SELECT SEQ_ENTRY_INFO.Nextval INTO ID FROM DUAL;					-- 查詢序列
			INSERT INTO T_PUSH_MSG
				(ID, BUSINESS_TYPE, RECIVE_MOBILE, RECIVE_NAME, TITLE, CONTENT, CONTENT_ORDER, STATUS, CREATE_DATE, UPDATE_DATE)
			VALUES
				(ID,
				'DX',
				(SELECT MOBILE FROM T_EXT_USER WHERE NAME = A.CHARGE_NAME),
				A.CHARGE_NAME,
				'PLSQL',
				'存儲過程示例',
				ID,
				'無',
				SYSDATE,
				NULL
			);
		END LOOP;
	ELSIF V_COUNT <10 THEN							-- 判斷語句，類似Java的else if。 注意：這里是ELSIF，少個字母E
		DBMS_OUTPUT.PUT_LINE('T_ENTRY_INFO表記錄數少于' || V_COUNT || '條。'); -- 拼接語法，使用雙豎線拼接，相當于Java的加號
	ELSE											-- 判斷語句，相當于Java的else
		DBMS_OUTPUT.PUT_LINE('T_ENTRY_INFO表記錄數大于' || V_COUNT || '條。');
	END IF;
EXCEPTION											-- 異常捕獲語句
  WHEN OTHERS THEN									-- OTHERS關鍵字，相當于Java中的Exception
	DBMS_OUTPUT.PUT_LINE(SQLERRM)					-- 異常原因：使用SQLERRM關鍵詞相當于Java代碼中的堆棧信息
END;

六、其他語法

DROP PROCEDURE 存儲過程名;	-- 刪除存儲過程名

SET SERVEROUTPUT ON;		-- 若是沒有執行輸出語句，可以使用該命令

以上就是“Oracle存儲過程怎么理解”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。