Shulou(Shulou.com)06/01 Report--

Oracle stored procedures, packages, method usage summary, the specific code is as follows:

/** *@author: zhengwei *@date:2017-04-28 *@desc: summary of stored procedure usage */ CREATE OR REPLACE PROCEDURE MYPROCEDURE(P_ID IN VARCHAR, P_STATUS OUT VARCHAR) --P_ID is input parameter,P_STATUS is output parameter AS ---Variable declaration T_STATUS VARCHAR2(20); T_ID NUMBER; V_POSTYPE VARCHAR2(20); V_DESCRIPTION VARCHAR2(20); ---Object variable definitions and declarations 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 declaration and fill data CURSOR MY_CUR IS SELECT POS_TYPE, DESCRIPTION FROM VOTEMASTER;BEGIN --Variable assignment (Note: Parameters of type in cannot be assigned directly) T_STATUS : ='1 '; P_STATUS := T_STATUS; DBMS_OUTPUT.put_line ('P_STATUS:'|| P_STATUS); BEGIN ---Loop cursor, use cursor 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 Cyclic Usage BEGIN WHILE i < 10 LOOP BEGIN i := i + 1; END; END LOOP; END;--Store the result of select query into variable, multiple columns can be stored in multiple variables at the same time, there must be one record, otherwise throw exception (if no record throws NO_DATA_FOUND) BEGIN SELECT col1, col2 INTO variable 1, variable 2 FROM typestruct WHERE xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ---IF judgement usage BEGIN SELECT VOTETITLE, VATESUM INTO T_NAME, T_COUNT FROM VOTEMASTER WHERE ID = P_ID; IF T_COUNT 0 AND T_COUNT < 3 THEN P_STATUS := T_NAME ||': Good'; ELSE P_STATUS := T_NAME ||': excellent'; END IF; END; ---Object variable assignment BEGIN SELECT FUND, BATCH_NO, TRAN_AMT, END_BAL, TRAN_DATE, TRAN_TIME, SUB_WATER INTO XRECORD FROM ACCT_WATER WHERE FUND = P_ID; --Use of object variables DBMS_OUTPUT.put_line(XRECORD.BATCH_NO|| XRECORD.FUND); END; ---Index tables--We often need to work with recordsets when using stored procedures, that is, multiple data records. Divided into a single column and multiple rows and multiple columns and rows, these types can be called collection types. An index table is one of the collection types. ---index table, also known as pl/sql table, cannot be stored in the database, the number of elements is not limited, subscripts can be negative. ---Usage scenarios: Index tables are the best choice if only used as collection variables in stored procedures. (It can also be replaced by creating temporary tables, but it is not so scientific, and temporary tables have to be maintained later) --index table object using scheme 1: BEGIN --index table object declaration, definition, use DECLARE TYPE acct_table_type IS TABLE OF ACCT%ROWTYPE INDEX BY BINARY_INTEGER; ---defines an index table v_acct_table, each row of which is a row of ACCT table v_acct_table acct_table_type; BEGIN SELECT * BULK COLLECT ---BULK COLLECT INTO refers to a batch aggregation type, which can store a multi-row multi-column storage type in simple terms. 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; ---Index table object usage scheme 2: BEGIN --Example: PL/SQL table DECLARE populated with RECORD global assignment feature TYPE RECTYPE IS RECORD( FUND ACCT.FUND%TYPE,, ---Indicates that the type of the variable defined is the same data type as the fund field of table Acct BAL ACCT.BAL%TYPE, OWNER ACCT.OWNER%TYPE, REAL_NMBR VARCHAR(30)); ---defines an index table MYTAB in which each row of records is RECORD TYPE TABTYPE IS TABLE OF RECTYPE INDEX BY BINARY_INTEGER; MYTAB TABTYPE; VN NUMBER; BEGIN --Fill VN := 1; FOR VARR IN (SELECT FUND, BAL, OWNER, REAL_NMBR FROM ACCT WHERE ROWNUM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.