Summary of Oracle stored procedures, packages, methods usage

Source: Internet
Author: User

/** * @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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.