Oracle存储过程新手入门教程(通俗理解存储过程)
小小野猪 人气:0一、存储过程通俗理解
- 简单理解存储过程是数据库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; -- 若是没有执行输出语句,可以使用该命令
七、总结
加载全部内容