sql;"> /** *@author：zhengwei *@date:2017-04-28 *@desc:存储过程用法总结 */ CREATE OR REPLACE PROCEDURE MYPROCEDURE(P_ID IN VARCHAR,P_STATUS OUT VARCHAR) --P_ID为输入参数,P_STATUS为输出参数 AS ---变量声明 T_STATUS VARCHAR2(20); T_ID NUMBER; V_POSTYPE VARCHAR2(20); V_DESCRIPTION VARCHAR2(20); ---对象变量定义和声明 TYPE XRECORD IS RECORD( FUND VARCHAR2(50),BATCH_NO VARCHAR2(50),TRAN_AMT NUMBER,END_BAL NUMBER,TRAN_DATE VARCHAR2(50),TRAN_TIME VARCHAR2(50),SUB_WATER NUMBER); XWATER XRECORD; ---游标声明，并填充数据 CURSOR MY_CUR IS SELECT POS_TYPE,DESCRIPTION FROM VOTEMASTER; BEGIN ---变量赋值(注意:in类型的参数不能直接赋值) T_STATUS := '1'; P_STATUS := T_STATUS; DBMS_OUTPUT.put_line('P_STATUS:' || P_STATUS); BEGIN ---循环游标，使用游标 FOR V_ROW IN MY_CUR LOOP BEGIN V_POSTYPE := V_ROW.POS_TYPE; V_DESCRIPTION := V_ROW.DESCRIPTION; DBMS_OUTPUT.put_line('POSTYPE:' || V_POSTYPE || ',description:' || V_DESCRIPTION); END; END LOOP; END; ---WHILE循环用法 BEGIN WHILE i < 10 LOOP BEGIN i := i + 1; END; END LOOP; END; --将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) BEGIN SELECT col1,col2 INTO 变量1,变量2 FROM typestruct WHERE xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ---IF判断语句用法 BEGIN SELECT VOTETITLE,VATESUM INTO T_NAME,T_COUNT FROM VOTEMASTER WHERE ID = P_ID; IF T_COUNT <= 0 THEN P_STATUS := T_NAME || ':差'; ELSIF T_COUNT > 0 AND T_COUNT < 3 THEN P_STATUS := T_NAME || ':良好'; ELSE P_STATUS := T_NAME || ':优秀'; END IF; END; ---对象变量赋值 BEGIN SELECT FUND,BATCH_NO,TRAN_AMT,END_BAL,TRAN_DATE,TRAN_TIME,SUB_WATER INTO XRECORD FROM ACCT_WATER WHERE FUND = P_ID; --对象变量的使用 DBMS_OUTPUT.put_line(XRECORD.BATCH_NO || XRECORD.FUND); END; ---索引表 ---我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。索引表就是集合类型中的一种。 ---索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。 ---使用场景：如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。(也可以通过创建临时表替代，但就不那么科学了，而且后期还得维护临时表) ---索引表对象使用方案1: BEGIN ---索引表对象声明、定义、使用 DECLARE TYPE acct_table_type IS TABLE OF ACCT%ROWTYPE INDEX BY BINARY_INTEGER; ---定义了一个索引表v_acct_table，其表中的每行记录是ACCT表中的一行记录 v_acct_table acct_table_type; BEGIN SELECT * BULK COLLECT ---BULK COLLECT INTO指是一个成批聚合类型,简单的来说,它可以存储一个多行多列存储类型 INTO v_acct_table FROM ACCT WHERE acct_type = '570' AND ROWNUM < 5; FOR i IN 1 .. v_acct_table.COUNT LOOP DBMS_OUTPUT.put_line('ACCT:' || v_acct_table(i).fund || ',' || v_acct_table(i).bal || ',' || v_acct_table(i) .real_nmbr); END LOOP; END; END; ---索引表对象使用方案2: BEGIN --例子：利用记录RECORD可用整体赋值的特性来填充PL/sql表 DECLARE TYPE RECTYPE IS RECORD( FUND ACCT.FUND%TYPE,---表示定义的变量的类型为表Acct的fund字段的同样数据类型 BAL ACCT.BAL%TYPE,OWNER ACCT.OWNER%TYPE,REAL_NMBR VARCHAR(30)); ---定义了一个索引表MYTAB，其表中的每行记录是RECORD TYPE TABTYPE IS TABLE OF RECTYPE INDEX BY BINARY_INTEGER; MYTAB TABTYPE; VN NUMBER; BEGIN --填充 VN := 1; FOR VARR IN (SELECT FUND,BAL,OWNER,REAL_NMBR FROM ACCT WHERE ROWNUM <= 15 ORDER BY FUND ASC) LOOP MYTAB(VN) := VARR; --记录整体赋值 VN := VN + 1; END LOOP; --访问 VN := MYTAB.FIRST; FOR VARR IN VN .. MYTAB.COUNT LOOP DBMS_OUTPUT.PUT_LINE(VN || ' ' || MYTAB(VN).FUND || ' ' || MYTAB(VN).BAL || ' ' || MYTAB(VN).OWNER || ' ' || MYTAB(VN) .REAL_NMBR); VN := MYTAB.NEXT(VN); END LOOP; END; END;