《2022年Oracle存储过程的基本语法 .pdf》由会员分享，可在线阅读，更多相关《2022年Oracle存储过程的基本语法 .pdf（6页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、3.1 oracle 存储过程的基本语法3.1.1 基本结构CREATE OR REPLACE PROCEDURE 存储过程名字( 参数 1 IN NUMBER, 参数 2 IN NUMBER ) IS 变量 1 INTEGER :=0; 变量 2 DATE; BEGIN END 存储过程名字3.1.2 SELECT INTO STATEMENT 将 select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常( 如果没有记录抛出 NO_DATA_FOUND) 例子：BEGIN SELECT col1,col2 into 变量 1, 变量 2 FROM ty
2、pestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; . 3.1.3 IF 判断IF V_TEST=1 THEN BEGIN do something END; END IF; 3.1.4 while 循环WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 3.1.5 变量赋值V_TEST := 123; 3.1.6 用 for in 使用 cursor . 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心
3、整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名 1+cur_result.列名 2 END; END LOOP; END; 3.1.7 带参数的 cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值 ); LOOP FETCH
4、 C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER; 3.2 过程返回记录集：CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); END pkg_test; / CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get (p_id NUMBER,
5、p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN OPEN p_rc FOR SELECT ID, NAME, sex, address, postcode, birthday FROM student; ELSE sqlstr := select id,name,sex,address,postcode,birthday from student where id=:w_id; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 -
6、 - - - - - - 第 2 页，共 6 页 - - - - - - - - - OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; / 3.3 函数返回记录集 : 建立带 ref cursor定义的包和包体及函数：CREATE OR REPLACE package pkg_test as /* 定义 ref cursor类型不加 return类型，为弱类型，允许动态sql 查询，否则为强类型，无法使用动态sql 查询; */ type myrctype is ref cursor; - 函数申明function
7、get(intID number) return myrctype; end pkg_test; / CREATE OR REPLACE package body pkg_test as - 函数体function get(intID number) return myrctype is rc myrctype; - 定义 ref cursor变量sqlstr varchar2(500); begin if intID=0 then - 静态测试，直接用select语句直接返回结果open rc for select id,name,sex,address,postcode,birthday
8、from student; else - 动态 sql 赋值，用 :w_id 来申明该变量从外部获得sqlstr := select id,name,sex,address,postcode,birthday from student where id=:w_id; - 动态测试，用 sqlstr字符串返回结果，用using 关键词传递参数open rc for sqlstr using intid; end if; return rc; end get; end pkg_test; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - -
9、- 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - oracle 存储过程的常用语法create or replace procedure sp_test ( aa in varchar, bb in number default 0 ) is v_pos varchar(50); v_num number; begin - 字符串查找和替换select SUBSTR(PARAMETER, 1, INSTR(PARAMETER,branchId)-2) | SUBSTR(PARAMETER, INSTR(PARAMETER,branchId
10、)+length(branchId) into v_pos from dual; - 循环的使用loop if bb is null then -if 判断exit; -退出循环end if; if v_num = bb then exit; end if; v_num := v_num +1; end loop; - 输出信息dbms_output.put_line(aaaaa); . commit; exception when NO_DATA_FOUND then - 没有记录的异常dbms_output.put_line(ddddd); when others then begin -
11、 输出错误信息dbms_output.put_line(sqlerrm); rollback; - 抛出异常raise; end; end sp_test; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - 3.4 创建表：CREATE TABLE QJFUND.TEST1 ( TESTID VARCHAR2(80 BYTE), NAME VARCHAR2(20 BYTE), PASSWORD VARCHAR2(20 BYTE)
12、) 3.5 创建存储过程：(a) 无返回值的存储过程：create or replace PROCEDURE TESTA(PARA1 IN VARCHAR2) AS -PARA1 为输入参数BEGIN update Test1 set testid = para1; END TESTA; (b) 有返回值的存储过程：create or replace PACKAGE pkg_test -本例中包名为 pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); - 存储过程名为
13、get ，有一输入参数 NUMBER，一输出参数 myrctype END pkg_test; / -该斜线不可省略，否则有错create or replace PACKAGE BODY pkg_test AS PROCEDURE get (p_id NUMBER, p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN OPEN p_rc FOR SELECT * FROM test1; ELSE sqlstr := select name,password from test1; OPEN p_rc FOR s
14、qlstr;- USING p_id END IF; END get; END pkg_test; / -该斜线不可省略，否则有错3.6 Java 调用：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - Session oracleSession = HibernateSessionFactory.getSession(); /Hibernate Session Connection conn = oracleSession.co
15、nnection(); /获取 Oracle Connection try CallableStatement proc = conn.prepareCall( call TESTA(?) ); /调用无返回值的存储过程TESTA proc.setString(1, ss); /设置存储过程中定义的参数，参数从1 开始oracleSession.beginTransaction(); proc.execute(); /运行存储过程oracleSession.getTransaction().commit(); proc = conn.prepareCall( call pkg_test.get
16、(?,?) ); /调用返回列表的存储过程proc.setInt(1, 1); /设置存储过程的参数，从1 开始proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); oracleSession.beginTransaction(); proc.execute(); /执行存储过程oracleSession.getTransaction().commit(); ResultSet rs = (ResultSet) proc.getObject(2); /获取返回值，该处的2与存储过程定义时的输出参数位置对应。StringBui
17、lder result = new StringBuilder(); result.append( ); while (rs.next() result.append( + rs.getString(1) + + rs.getString(2) + ); / rs.getString(1)对应查询结果的第一列result.append( ); request.setAttribute(result, result.toString(); catch (Exception e) e.printStackTrace(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - -