Special data types for Oracle

1.%type allows users to dynamically associate the data type of a column in a database with a variable in PL/SQL. The syntax is as follows: Variable_name Table.column%type 2.%rowtype allows a user to define a single variable that contains multiple variables for each column of a database table. The syntax rules are as follows: Variable_name Table%rowtype Once you have defined this variable, you can access each column in the following way: Row_variable.column_name Additionally: You can use the% A variable of type ROWTYPE inserts or updates an entire record into a database table as follows: Emprec Scott.emp%rowtype;insert into scott.emp values Emprec; 3.FOR cursors, the syntax is as follows for the record_variable in cursorloop//handles the logical end Loop, and is automatically defined as a variable with the help of%rowtype,record_variable. The loop opens the cursor, performs a logical operation for each record in the loop, and terminates the loop and closes the cursor when no more records exist. 4. An implicit cursor, a special form of a for cursor, that handles the logical end Loop by directly using the SQL statement without needing to display the declaration cursor for record_variable in (SELECT statement) loop//; Note: The SELECT statement must be wrapped with parentheses. A 5.REF cursor cursor references a result set. The REF cursor allows a cursor reference to be passed between PL/SQL program units. That is, it allows the user to create a variable that receives a cursor and has access to its result set. How to use:

1. Declares a type:type ref_cursor_name is REF CURSOR [RETURN_TYPE];RETURN_TYPE clause is a save, you can specify or not specify the type of data returned by Ref_cursor. If not specified, the cursor is weakly typed. If specified, the cursor is strongly typed. PL/SQL provides a pre-defined weakly ref CURSOR called Sys_refcursor.
2. Then create an instance of the type: cursor_variable ref_cursor_name; Sys_cursor Sys_refcursor

Note: Because the weak ref cursor is prone to mismatches between the data returned by the REF cursor and the variables used by the application, it is more likely to cause a bug. When creating a strongly typed REF cursor, you can use%rowtype, such as type Rc_employees HR. Employees%rowtype--Declares rc_employees type rc_emp is REF CURSOR Rc_employees%type--Declares the REF cursor type and formulates a return type of rc_employees Also You can use a PL/SQL RECORD that contains%type, such as type Jr_rec is record (employee_id job_history. employee%type,job_id job_history. Job_id%type,speed_of_promotion VARCHAR2 (4)); TYPE Rc_jr is REF CURSOR jr_rec%type; A 6.RECORD record is a composite data type--a single record can have multiple components. The typical use of a record is to be used in conjunction with%rowtype, such as: Record_name table%rowtype After this declaration, you can use Record_name.column_name to access a single column. You can also display a definition record that contains multiple columns with the following syntax: TYPE record_name is record (Col_name datatype[,...]); --declaration type rec Record_name; --Define variables when you define a variable of record type, you can

1. Receive the result of the SELECT statement: Select EMPLOYEE_ID, employee_name from employee to rec;
2. Insert:insert into employee values rec to the database table;
3. Update table records update:update Empee set Row rec where employee_id = rec. employee_id;
4. Can be used in DELETE statements: Delete from HR. Job_history WHERE depart_id =1 RETURN TYPE emploee_id,emploee_name into rec;

7. Associative array associative arrays are a collection of data indexed by a value that resembles a hash table in some other programming language. The syntax is as follows: Type Array_Name is TABLE of DATATYPE INDEX by VALUE; After declaration, you can define a variable of that type: V_arr array_name; Use loops to set values for associative arrays: for temp in (SELECT statement) loopv_arr[temp. Key] = Temp.value; END LOOP; Index value from associative array: v_temp: = V_arr (V_key); 8. Nested table nested tables use sequential integers as the index of the collection. Although arrays also use integers to index the contents of an array, the formal use of index data is sequential and sequential. By creating a kind of called sparse index, a nested table can use a discontinuous number as an index. The syntax for declaring a nested table is as follows: type collection_name is table of datatype [not NULL]; Here datatype can be a task-valid PL/SQL data type, except for REF cursor. Not a null key for the table name, and the collection cannot contain null values for the element. Object types allow you to save a nested table in a data column, and you can access a single element in the stored table. 9. Variable array variable array is also called Varray. This audit takes an integer index and can be saved in a column in the database. The definition of Varray also contains a specific index on the upper boundary. The syntax for defining Varray is as follows: TYPE collection_name is varray (size) of DataType [not NULL], where size is the upper limit of the varray size. 10. Using collections in collection Plsql is multiple instances of a single data block or composite data consisting of a single variable. Types of collections: associative arrays, nested tables, and mutable arrays can be assigned to audits in two different ways. For nested tables and mutable arrays, you can assign all values in a single statement: TYPE Number_array is Table of number;number_collection number_array: = (1,2,3,4,5,6,7,8,9); Using index values, only one value can be attached at a time: number_collection (1): = 1; You can also assign a value to another collection using one collection, as long as the two collections have the same data type. Collections can be used for two types of logical comparisons: to determine whether two sets are equal: if Collection1 = Collection2 to determine whether the collection is empty: if Collection1 is null; Collection operations:

Operation	Meaning	Grammar
EXISTS	Checks whether an element exists in the collection	Collection. EXISTS (Index)
COUNT	Returns the number of elements in the collection	Collection. COUNT
LIMIT	Returns the maximum number of entries in Varray	Collection. LIMIT
First	Returns the first element in a collection	Collection. First
Last	Returns the last element in a collection	Collection. Last
NEXT	Returns the next element in the collection	Collection. NEXT
PRIOR	Returns the previous element in the collection, or null if it does not exist	Collection. PRIOR
EXTEND	Expands the number of elements in the collection. cannot be used for associative arrays or other non- The Initialized Collection	Collection. EXTEND (adds a single element to the collection) Collection. EXTEND (n): Add n elements to a set Collection. EXTEND (n,i): Adds n elements to a set by copying the value of subscript I
TRIM	Remove an element from the end of a collection	Collection. TRIM; Remove a single element from the end Collection. TRIM (n): delete n elements from the end of the collection
DELETE	Remove an element from the collection	Collection. Delete: Remove all elements from the collection Collection. Delete (n): Removes an element of subscript n from the collection Collection. Delete (m,n): Removes the element from the collection where the subscript value is located between M,n

Collection Usage Considerations:

• All operations other than EXTEND, trim, and delete return a value
• Associative arrays containing VARCHAR2 subscript are arranged alphabetically according to the language defined by the environment
• If the collection is empty (null), first and last return null, which is not contained in any element
• If the next or previous element does not exist in the collection, then next and prior return null
• EXTEND and TRIM cannot be used with associative arrays
• Limit can be used to restrict the number of values added to the collection, the usual syntax is L SELECT ... into

Applicability of a collection: Differences between collection classes

	Associative arrays	Nested tables	Varray
Subscript type	Number or string	Digital	Digital
Declared as fixed size	Whether	Whether	Is
Allow sparse subscript	Is	Is	Whether
Storing in the database	Whether	Is	Is
Accessing individual elements stored in a collection	N/A	Is	Is

11.BULK collect with the combination of BULK collect and associative arrays, batch operations can be performed as follows: TYPE REC is RECORD (...,...); Defines the associative array type: Type rec_table is Table of rec index by Pls_integer; Declaration variable: Rec_table_array rec_table; OPEN cursor;fetch cursor bucket into rec_table_array;close cursor; For counter in Rec_table_array. First ... rec_table_array. lastloop//processing logic end LOOP; The 12.FORALL FORALL architecture allows write operations to achieve the same efficiency as the bulk collect, which encapsulates multiple write statements and sends them to the Oracle database in a single message, improving the overall performance of the operation. The syntax of the  FORALL structure is as follows: FORALL index_name in Lower_bound ... upper_bound sqlstatment  where index_ Name is a variable declared by a hermit for use in the ForAll loop. Lower_bound and Upper_bound limit the scope of the collection, which is for the collection. For example, if you want to use FORALL to insert all records that are located in the collection: FORALL jh_index in JH. First: Jh. Last INSERT into HR. Job_history VALUES JH (jh_index);  Note: When blocking the save ForAll keyword in the exceptions loop, the loop no longer reports an exception for errors in individual SQL statements. Conversely, if any exception occurs during the processing of the ForAll loop, an exception that is named-24381 is reported at the end of the process.   forall improvements 1.INDICES of this is a refinement of sparse collection processing. The records for this collection are not continuously distributed---they are continuously distributed across the entire range of the collection. FORALL INDICES of collection_name [between Lower_bound ... Upper_bound] sql_statement  The above statement tells ForAll to read the next subscript value instead of the next concatenated value. Use the INDICES of clause to make the FORALL structure suitable for sparse collections. By specifying the top and bottom bounds of the collection, the INDICES of clause also allows you to select only part of the entire collection to be processed.  2.values of this improvement makes the application of the FORALL structure more flexible. The VALUES of clause allows you to use another collection to determine which records are the targets of the forall structure.  forall index_values values of driver_collection the only requirement to use this clause is that the drive set must be a nested table or an associative array. The subscripts and elements of the associative array used for this structure must be Binary_integer or pls_integer  

Special data types for Oracle