1.1 PL / SQL Introduction 1.2 Creating a PL / SQL Block 1.3 PL / SQL Data Type 1.4 Processing PL / SQL Experient 1.4.1 PL / SQL Exception 1.4.2 Custom Abnormal Process 1.4 Customization 1.5 In PL / Single records in SQL 1.6 use cursor multi-record 1.6.1 Basic methods to use cursors 1.6.2 Use the cursor for loop 1.6.3 Create a variable with parameters 1.7 Create a variable representing database records and columns 1.8 How to use PL / SQL Table implementation array function
[Ref: http://www.oradb.net/plsql.htm]
1.1 PL / SQL Introduction
PL / SQL is Oracle's procedural language, including a complete set of data types, conditional structures, looping structures, and exception processing structures, and PL / SQL can perform SQL statements, and the PL / SQL function can also be used in the SQL statement.
Declare ... begin ... exceptionEND; 1.3 PL / SQL data type
Name Type Description Number Digital Digital Value Value and Real Value, and can define the accuracy and value range binary_integer digital storage band symbol integer, calculate the subtype of the DIUBER Digital Number, decimal Double Precision Digital Number Subtype, high-precision real INTEGER Digital Number Subtype The subtype of digital Number, the value range is stored in the Integer Small VARCHAR2 character type, with the maximum length Char characters set long string LONG character type becomes long string, the maximum length can reach 32,767 Date Date type The database is the same format storage date value Boolean Boolean True Or False Rowid RowID Store the line number of the database
Example: DECLAREORDER_NO NUMBER (3); Cust_Name Varchar2 (20); Order_Date Date; Emp_no Integer: = 25; Pi constant Number: = 3.1416; Beginnull; END; 1.4 Processing PL / SQL Abnormally 1.4.1 PL / SQL exception
For example: declarex number; beginx: = 'YYYY'; - Error Herexception when value_ERROR THENDBMS_OUTPUT.PUT_LINE ('Exception Handed');
Implementation Technology: Exception when first_exception the ... When Second_exception the ... WHEN OTHERS THEN * The Abnormal Processor must be ranked in the end, it handles all exceptions not explicitly listed. * / ... END;
1.4.2 Predefined abnormalities
1.4.3 Customization Processing
DECLAREBAD_ROWID EXCEPTION; X ROWID; PRAGMA EXCEPTION_INIT (BAD_ROWID, -01445); BEGINSELECT ROWID INTO X FROM TABWHERE ROWNUM = 1; EXCEPTION WHEN BAD_ROWID THENDBMS_OUTPUT.PUT_LINE ( 'CAN NOT QUERY ROWID FROM THIS VIEW'); END; Note: -01445 because PRAGMA The eXception_init command connects this variable (-01455) to this Oracle error, the syntax of this statement is as follows: Pragma Exception_init (Exception_name, Error_Number); where error_number is negative, because the error number is considered negative, remember to use the load when defined errors 1.4.4 Custom Abnormal Anomaly does not necessarily have to be an ORACLE returned system error, users can create trigger and handable custom exceptions in their own applications DeclareSalary_code varcha2 (1); invalid_salary_code exception; beginsalary_code: = 'x' ; IF SALARY_CODE NOT iN ( 'a', 'B', 'C') THENRAISE INVALID_SALARY_CODE; END IF; EXCEPTION WHEN INVALID_SALARY_CODE THENDBMS_OUTPUT.PUT_LINE ( 'INVALID SALARY CODE'); END; 1.5 single record in PL / SQL query
In PL / SQL, sometimes query single records without defining explicit cursors, and assigns the recorded data to the variable. DECLAREln_dno NUMBER; lvs_dname VARCHAR2 (40); BEGINSELECT DEPT_NO, DEPT_NAMEINTO ln_dno, lvs_dnameFROM deptWHERE DEPT_NO = 1; DBMS_OUTPUT.PUT_LINE ( '.' TO_CHAR (ln_dno) || || lvs_dname); EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE ( 'NO DATA_FOUND' ); WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS'); END;
1.6 query multi-record with cursor
The cursor is a pointer to a region called context-related area. This area is within the processing of the server. When a query is executed on the server, the recording set returned by the query is placed in the context-related area. These records can be retrieved to the client's application by operations on the cursor.
DECLARECURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM <= 10ORDER BY VIEW_NAME; VNAME VARCHAR2 (40); BEGINOPEN C1; FETCH C1 INTO VNAME; WHILE C1% FOUND LOOPDBMS_OUTPUT.PUT_LINE (TO_CHAR (C1% ROWCOUNT) || '' || VNAME) FETCH C1 INTO VNAME; END LOOP;
Attribute content% FOUND Boolean properties, when the record is successfully returned, the value is True% notfound Boolean properties, and its value is always the value of the value of the% FOUND attribute, the% isopen Boolean property, when the cursor is turned back True% ROWCOUNT digital properties, returns the number of records read from the cursor 1.6.2 Use the cursor for loop
1.7 Creating a variable variable name base table name representing database records and columns. Column name% TYPE variable name base table name% ROWTYPE Description: When the user wants to create a variable to represent a base table column or to create multiple variables to represent a whole When the record is recorded, you can actually use the% Type property and the% ROWTYPE attribute. Use the% Type property and the% ROWTYPE attribute to ensure that the user's PL / SQL code can still be normal when the structure of the base table is changed or the data type of the column changes. jobs. DECLARED VEQU12% ROWTYPE; BEGINSELECT ASSET12ID, ASSET12NAMEINTO D.ASSET12ID, D.ASSET12NAMEFROM VEQU12; DBMS_OUTPUT.PUT_LINE (D.ASSET12ID); EXCEPTIONWHEN NO_DATA_FOUND THENNULL; WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE ( 'TOO_MANY_ROWS'); END; 1.9 How to use the PL / SQL Table Implementation Array Functions The PL / SQL Table is similar to one-dimensional array of other proceduralization languages (such as C language). Implementing a PL / SQL table requires a data type and other variable descriptions. Type
1.6.3 Cursor with parameters
DECLARECURSOR C1 (VIEW_PATTERN VARCHAR2) ISSELECT VIEW_NAMEFROM ALL_VIEWSWHERE VIEW_NAME LIKE VIEW_PATTERN || '%' ANDROWNUM <= 10ORDER BY VIEW_NAME; VNAME VARCHAR2 (40); BEGINFOR I IN C1 ( 'USER_AR') LOOPDBMS_OUTPUT.PUT_LINE (I.VIEW_NAME); END LOOP DBMS_OUTPUT.PUT_LINE (); for i Iin c1 ('user') loopdbms_output.put_line (i.view_name); end loop; Exception when others thendbms_output.put_line ('aaa');
1.7 Creating a variable variable name base table name representing database records and columns. Column name% TYPE variable name base table name% ROWTYPE Description: When the user wants to create a variable to represent a base table column or to create multiple variables to represent a whole When the record is recorded, you can actually use the% Type property and the% ROWTYPE attribute. Use the% Type property and the% ROWTYPE attribute to ensure that the user's PL / SQL code can still be normal when the structure of the base table is changed or the data type of the column changes. jobs. DECLARED VEQU12% ROWTYPE; BEGINSELECT ASSET12ID, ASSET12NAMEINTO D.ASSET12ID, D.ASSET12NAMEFROM VEQU12; DBMS_OUTPUT.PUT_LINE (D.ASSET12ID); EXCEPTIONWHEN NO_DATA_FOUND THENNULL; WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE ( 'TOO_MANY_ROWS'); END; 1.9 How to use the PL / SQL Table Implementation Array Functions The PL / SQL Table is similar to one-dimensional array of other proceduralization languages (such as C language). Implementing a PL / SQL table requires a data type and other variable descriptions. Type
