Summary of Oracle stored procedures, packages, methods usage

/** * @author: Zhengwei * @date: 2017-04-28 * @desc: Summary of Stored procedure usage*/CREATE OR REPLACE PROCEDUREMyprocedure (p_idinch VARCHAR, P_status outVARCHAR)--p_id as input parameter, p_status as output parameter  as  ---Variable DeclarationT_statusVARCHAR2( -); t_id Number; V_postypeVARCHAR2( -); V_descriptionVARCHAR2( -); ---object variable definition and declarationTYPE Xrecord isRECORD (FUNDVARCHAR2( -), Batch_noVARCHAR2( -), Tran_amt Number, End_bal Number, Tran_dateVARCHAR2( -), Tran_timeVARCHAR2( -), Sub_water Number);  Xwater Xrecord; ---Cursor declaration, and populating data  CURSORMy_cur is    SELECTPos_type, DESCRIPTION fromVotemaster;BEGIN  ---Variable Assignment (note: Parameters in type cannot be assigned directly)T_status:= '1'; P_status:=T_status; Dbms_output.put_line ('P_status:' ||p_status); BEGIN    ---loop cursors, using Cursors     forV_rowinchmy_cur LOOPBEGINV_postype:=V_row.        Pos_type; V_description:=V_row.        DESCRIPTION; Dbms_output.put_line ('Postype:' ||V_postype|| ', Description:' ||v_description); END; ENDLOOP; END; ---while Cycle Usage  BEGIN     whileI< TenLOOPBEGINI:=I+ 1; END; ENDLOOP; END; --depositing the results of a select query into a variable, you can store multiple columns in multiple variables at the same time, you must have one record, otherwise throw an exception (if no record throws No_data_found)  BEGIN    SELECTCol1, col2 intoVariable 1, variable 2 fromTypestructWHERExxx; EXCEPTION whenNo_data_found Thenxxxx; END; ---if Judgment Statement usage  BEGIN    SELECTVotetitle, Vatesum intoT_name, T_count fromVotemasterWHEREId=p_id; IFT_count<= 0  ThenP_status:=T_name|| ': Poor'; elsif T_count> 0  andT_count< 3  ThenP_status:=T_name|| ': Good'; ELSEP_status:=T_name|| ': Excellent'; END IF; END; ---Assign values to object variables  BEGIN    SELECTFUND, Batch_no, Tran_amt, End_bal, Tran_date, Tran_time, Sub_water intoXrecord fromAcct_waterWHEREFUND=p_id; --use of object variablesDbms_output.put_line (Xrecord. Batch_no||Xrecord.  FUND); END; ---Index Table  ---We often need to process a recordset when using stored procedures, that is, multiple data records. Divided into single-row and multi-column multiple rows, these types can be called collection types. The 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, subscript can be negative.   ---usage Scenario: The index table is the best choice if it is used as a collection variable only in stored procedures. (You can also create temporary table overrides, but not so scientifically, and later maintain temporary tables)  ---Index Table object using Scenario 1:  BEGIN    ---Index Table object declaration, definition, use    DECLARETYPE Acct_table_type is TABLE  ofACCT%ROWTYPEINDEX  byBinary_integer; ---Defines an index table v_acct_table, where each row of records in a table is a row of records in the Acct tablev_acct_table Acct_table_type; BEGIN      SELECT * BULKCOLLECT intov_acct_table fromACCTWHEREAcct_type= '570'          andROWNUM< 5;  forIinch 1.. V_acct_table.COUNTLOOP Dbms_output.put_line ('ACCT:' ||V_acct_table (i). Fund|| ',' ||V_acct_table (i). Bal|| ',' ||v_acct_table (i). real_nmbr); ENDLOOP; END; END; ---Index Table object using Scenario 2:  BEGIN    DECLARETYPE Acctrow isRECORD (FUNDVARCHAR( -), BAL Number); TYPE accttable is TABLE  ofAcctrowINDEX  byBinary_integer; ---Defines an index table v_acct_table, with each row of records in its table being V_acct_rowV_acct_row Acctrow;    V_acct_table accttable; BEGIN      SELECTFUND, BALBULKCOLLECT intov_acct_table fromACCTWHEREAcct_type= '570'          andROWNUM< 5;  forIinch 1.. V_acct_table.COUNTLOOP Dbms_output.put_line ('ACCT:' ||V_acct_row. FUND|| ',' ||V_acct_row.      BAL); ENDLOOP; END; END;END;

Summary of Oracle stored procedures, packages, methods usage