Oracle 11G basic learning-(4) PLSQL language basics

 

Where can I start learning about Oracle? Here, I will take you to the door of Oracle. One chapter a day, seven days of entry is not a dream.

 

1. PL/SQL

PL/SQL is a procedural extension of Oracle in the standard SQL language that allows embedding SQL statements, defining variables and constants, and allowing the process language structure (conditional branch statements and cyclic statements ), allow exceptions to be used to handle Oracle errors. PL/SQL can be used to create stored procedures, triggers, and packages, or to process services.

Rules, database events, or adding program logic to SQL Command Execution

1.2 advantages of PL/SQL improve application running performance provide modular programming functions allow defining identifiers with a process language control structure with good compatibility to handle running errors

1.3 All PL/SQL programs in PL/SQL blocks use blocks as the basic unit blocks and contain procedural statements and SQL DML statements. These blocks can appear in sequence or nested with each other (one block is inside another) block Classification anonymous block anonymous blocks are blocks that appear in applications without names and are not stored in the database where anonymous blocks can appear in SQL statements. They can call other programs, but it cannot be called by other programs. The name block is an anonymous block with tags. The tag specifies a name for the block. The subroutine is stored in the database as a procedure process), function ), after being generated, the program package can be executed multiple times. The program package is a group of subroutines stored in the database. The program in the variable definition package can be called by other programs or subroutines. The trigger is a kind of name stored in the database. block, after the trigger event is generated, it can be executed multiple times before or after the trigger event occurs.

2. PL/SQL is a procedural extension of Oracle in the standard SQL language. It allows embedding SQL statements, defining variables and constants, and allowing the process language structure (conditional branch statements and cyclic statements ), allow exceptions to be used to handle Oracle errors. PL/SQL can be used to create stored procedures, triggers, and packages. It can also be used to process business rules, database events, or add program logic to SQL command execution.

2.2 PL/SQL comment a single line comment can start from two dashes (--) anywhere in a line, and until the last multi-line comment of the row starts with "/*" and ends with "*/", the multi-line comment can span multiple rows, But nesting is not allowed

2.3 constants and variables in the Declaration section, you can declare the required constants, variables, functions, cursors, and Exception Handling names to declare identifier_name [CONSTANT] data_type [not null] [: = value_expression] | [DEFAULT value_expression] parameter description identifier_name specifies the constants to be declared, and the variable name data_type specifies the Data Type: = the value assignment operator DEFAULT assigns the DEFAULT value value_expression to the variable. The value assignment expression CONSTANT indicates that the declared CONSTANT is not null, indicating that the declared variable cannot be empty, except that constants can be used to assign values to the variable, you can also use the select into statement to assign the query results from the database table to the variable.

2.4 The data type CHAR indicates a fixed-length string. If the length is insufficient, spaces are used to supplement it. Up to 2000 bytes can be stored. VARCHAR2 VARCHAR2 indicates a variable-length string, you can store up to 4000 bytes of NUMBER type. You can store positive, negative, zero, fixed point, and 38-bit floating point DATE data types. This data type is used to store the DATE and time data types in the table. the length is 7 to 7 bytes, indicating the century, year, month, day, hour, minute, and second TIMESTAMP data type. It is used to store the year, month, day, hour, minute, and second of the date. value. The second value is accurate to six decimal places. This data type also contains time zone information. The CLOB large string object type CLOB data type is used to store variable-length character data, A maximum of 4 GB can be stored. This data type is used to store long text information that cannot be stored by VARCHAR2. BLOB large binary BLOB data type is used to store large binary objects, this TYPE can store up to 4 GB Data % TYPE data TYPE. When PL/SQL variables are defined to store values, make sure that the appropriate data TYPE and length are used for the variable. Otherwise, a PL/SQL running error may occur during running. To avoid this unnecessary error, you can use the % TYPE attribute to define the variable.

When the % TYPE attribute is used to define a variable, Oracle automatically determines the TYPE and length of the new variable according to the database column or other variables. If a table contains more columns, you can use % ROWTYPE to define a variable that represents a record in a table. In order to make the data type of a variable consistent with the data type of each column recorded in a table, oracle provides the % ROWTYPE definition method. The RECORD data type PL/SQL record is similar to the structure in advanced languages. Each PL/SQL RECORD generally contains multiple members.

When using PL/SQL records, you must first define the record type and record variable in the definition section, and then reference the record variable in the Execution Section When referencing record members, record variables must be prefixed in the format of record variables. RECORD member TYPE record_name is record (v1 data_type1 [not null] [: = default_value], v2 data_type2 [not null] [: = default_value], vn data_typen [not null] [: = default_value]); the TABLE data type TABLE (index TABLE) is equivalent to a set of key values. The key is unique and used to find the corresponding value. The key can be an integer or string TYPE table_name is table of element_type [not null] index by [BINARY_INTEGER | PLS_INTEGER | VARCHAR2]; VARRAY data type varray (array) is a set of members of the same data type. Each member has a unique subscript, which depends on the position syntax OF the member in the array TYPE varray_name is varray (size) OF element_type [not null]; varray_name is the name size of the varray data type, which specifies the maximum number of members that can be accommodated. The data type of each member is element_type. For the VARRAY data type, the first three steps are definition, declaration, and initialization. These three steps are indispensable; otherwise, errors will occur during subsequent use. When referencing a member in the VARRAY array, you need to use the sequence subscript in a pair of parentheses. The subscript starts from 1. flow Control IF condition expression THEN statement segment end if; IF condition expression THEN statement segment 1 ELSE statement segment 2END IF; IF condition expression 1 THEN statement segment 1 ELSIF condition expression 2 THEN statement Segment 2 ELSIF condition expression 3 THEN statement segment 3 ...... ELSIF conditional expression n statement segment nEND IF;

3.1 Conditional Control CASE expression WHEN condition expression result 1 THEN statement segment 1; WHEN condition expression result 2 THEN statement Segment 2 ;...... ELSE statement segment n; end case; CASEWHEN condition expression result 1 THEN statement segment 1; WHEN condition expression result 2 THEN statement Segment 2 ;...... ELSE statement segment n; end case;

3.2 LOOP control basic LOOP: LOOP statement segment; EXIT [WHEN conditional expression] end loop; EXIT statement must be placed in the LOOP body, and can only EXIT the LOOP body, cannot EXIT the PL/SQL block. When you need to exit the PL/SQL block, you should use the RETURN statement WHILE LOOP WHILE conditional expression LOOP statement segment; END LOOP; FOR LOOP when using the basic LOOP or WHILE LOOP, when the FOR loop is used, Oracle implicitly defines the loop variable FOR loop variable in [REVERSE] Initial Value expression .. END value expression LOOP statement segment; end loop;

3.3 sequence control the GOTO statement is used to jump to a specific label to execute the statement. Using the GOTO statement increases the complexity of the program and makes the application readable, therefore, when developing an application, we recommend that you do not use the NULL statement of the GOTO statement. The NULL statement does not perform any operation, the advantage of passing the control directly to the next statement using the NULL statement is that it can improve the readability of PL/SQL programs.

4. exception Handling PL/SQL exceptions are caused by errors generated during the running of compiled PL/SQL programs, such as memory exhaustion, hardware faults, violation of table integrity constraints, and design defects. PL/SQL provides an exception error handling mechanism, it helps to capture and handle errors. When an exception occurs, PL/SQL automatically captures exceptions and automatically transfers the program control process to the exception handling part of the program EXCEPTIONWHEN Exception error name 1 [OR Exception error name 2 ......] THEN statement segment 1; WHEN Exception error name 3 [OR Exception error name 4 ......] THEN statement Segment 2 ;...... when others then statement segment 3;

4.1 pre-defined exceptions are applicable to some common errors. PL/SQL pre-defined exceptions when PL/SQL programs violate Oracle regulations or exceed system-defined restrictions, a predefined error is implicitly thrown PL/SQL contains these predefined exceptions in the package STANDARD ORACLE error description LOGIN_DENIED ORA-1017 invalid user name or password NO_DATA_FOUND ORA-1403 query unfound data NOT_LOGGED_ON ORA-1012 not connected try database operation PROGRAM_ERROR ORA-6501 internal error ROWTYPE_MISMATCH ORA-6504 primary variable and light object type incompatible STORAGE_ERROR ORA-6500 internal error access_assist_null ORA-6530 CASE_NOT_FOUND ORA-6592 CASE statement appears when trying to access an uninitialized object the option in does not match the user input data when CURSOR_ALREADY_OPEN ORA-6511 tries to open an opened cursor DUP_VAL_ON_INDEX ORA-0001 trying to corrupt a uniqueness restriction INVALID_CURSOR ORA-1001 trying to use an invalid cursor INVALID_NUMBER ORA-1722 trying to not number value for numeric operation TIMEOUT_ON_RESOURCE ORA-0051 timeout TOO_MANY_ROWS ORA-1422 select into command returned multiline TRANSACTION_BACKED_OUT ORA-006 due to deadlock commit being returned VALUE_ERROR ORA-6502 conversion or cropping error ZERO_DIVIDE ORA-1476 trying to be division by zero

4.2 custom exception pre-defined exceptions are all Oracle-determined exceptions. In the actual PL/SQL program development process, for specific business rules, programming and debugging needs, programmers can customize some exceptions. Custom exceptions must be declared and must be explicitly thrown using RAISE statements. If no matching record exists during table update, data is not updated. This is not an error, so there will be no error prompts, but you can use the custom error exception method to prompt

5. developing dynamic SQL in PL/SQL programming, you can directly use DML and transaction control statements, however, DDL statements and system control statements cannot be directly used in PL/SQL. When DDL statements and system control statements need to be used in PL/SQL, dynamic SQL can be used, for example, to dynamically create a table or an uncertain operation, the SQL statements used in PL/SQL blocks need to be clearly defined during compilation, and the objects to be determined are executed, static SQL statements are uncertain during PL/SQL block compilation. For example, different operations are performed based on different parameters entered by the user, the dynamic SQL Compilation Program does not process the dynamic statement, but dynamically creates the statement when the program is running, performs syntax analysis on the statement, and runs the statement.

5.2 process non-query statement execute immediate dynamic_string [INTO {define_variable [, define_variable]... | record}] [USING [IN | OFF | in out] bind_argument [, [IN | OUT | in out] bind_argument]...] [{RETURNING | RETURN} INTO bind_argument [, bind_argument]...]; parameter description dynamic_string specifies the string variable define_variable that stores SQL statements or PL/SQL blocks.

Specify the variable bind_argument (IN) that stores the query results of a single row. Specify the variable bind_argument (OUT) that is passed to the dynamic SQL value) specifies the variable that stores the dynamic SQL return value. EXECUTE IMMEDIATE is used to process DDL and DCL operations. EXECUTE IMMEDIATE is used to process DML statements containing the RETURNING clause. When EXECUTE IMMEDIATE is used to process DML statements containing the RETURNING clause, you must use the returning into clause to receive the returned data. When you directly use the execute immediate statement to process DML statements containing the RETURNING clause, only DML statements acting on a single row can be processed. execute immediate can be used to process a single row query. execute immediate can be used to process DDL, DCL, and DML statements, it can also be used to process a single-row query statement, but when the execute immediate statement is used to process a single-row query statement To use the INTO clause to receive the returned data, use the BULK clause to process multi-row queries. By introducing the BULK clause in the execute immediate statement, not only single-row queries can be processed, it can also process multi-line query statements. Summary PL/SQL is a procedural extension of Oracle in the standard SQL language. It not only allows embedding SQL statements, but also allows defining variables and constants, the PL/SQL block can be divided into anonymous blocks, naming blocks, subprograms, packages, and triggers. the PL/SQL block consists of three parts: the declaration, execution, and Exception Handling sections are in the PL/SQL block. You can use the SELECT INTO statement to assign the query result to the variable. When you use the % TYPE attribute to define the variable, oracle automatically determines the type and length of the new variable according to the database column or other variables. You can use % ROWTYPE to define a variable that represents a record in a TABLE. The TABLE index TABLE is equivalent to a set of key values, the key is unique and used to find the corresponding value. The key can be an integer or string VARRAY array, which is a set of members of the same data type.

Each member has a unique subscript, which depends on the position of the Member in the array. A custom exception must be declared, in addition, you must use RAISE statements to explicitly enable the use of DDL statements and system control statements in PL/SQL.

 

 

From happy pig's column