Oracle learning path (4): oracle learning path

Oracle learning path (4): oracle learning path
LOB type

ORACLE provides the LOB (Large OBject) type for storing Large data objects. ORACLE currently supports the BFILE, BLOB, CLOB, and NCLOB types.

• NCLOB

  Stores large NCHAR character data types. Each variable stores the location of a large character object, which refers to a large character data block. Size of a large character object <= 4 GB.

• CLOB (Book)

  Stores large character data types. Each variable stores the location of a large character object, which refers to a large character data block. Size of a large character object <= 4 GB.

• BLOB (Photo)

  Stores large binary data types. Variable stores the location of a large binary object. Size of a large binary object <= 4 GB.

• BFILE (Movie)

  Stores enlarged binary data objects. These data files are not stored in the database, but in a directory of the operating system. The database table only stores the directories of files.

BIND Variable

　Variable binding is a variable defined in the host environment. In PL/SQL programs, bind variables as other variables they will use. To declare the bound VARIABLE in the PL/SQL environment, run the VARIABLE command. For example:

VARIABLE return_code NUMBERVARIABLE return_msg VARCHAR2(20)

You can use PRINT in the SQL * Plus command to display the value of the bound variable. For example:

PRINT return_codePRINT return_msg

PL/SQL TABLE)

　　Defines the Data Type of the record table (or index table. It is similar to the record type, but it is an extension of the record type. It can process multiple rows of records, similar to the two-dimensional array in advanced mode, so that tables in the database can be imitated in PL/SQL.
The syntax for defining the record table type is as follows:

TYPE table_name IS TABLE OF element_type [NOT NULL]INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2]

EXISTS (n) returns true if the nth Member of the Set EXISTS.
COUNT returns the number of members that have been assigned a bucket.
FIRST: returns the lowest value of a member.
LAST: returns the highest value of a member.
PRIOR (n) returns the subscript of the previous Member whose subscript is n. If not, NULL is returned.
NEXT (N) returns the subscript of the next Member of the member whose subscript is n. If not, NULL is returned.
TRIM: Delete the last member.
TRIM (n): delete n members at the end
DELETE: deletes all members.
DELETE (n): DELETE the nth member.
DELETE (m, n): DELETE a Member from n to m
EXTEND: Add a null member.
EXTEND (n): Add n null members
EXTEND (n, I): Add n members with the same value as the I member.
LIMIT returns the maximum value that appears in the varray type variable.

• Example 1
  Declare a table type and a number for loop. The first loop inserts four records into the table type array through four cycles, and the second loop loops through the entire table array, output the data we need,

DECLARE  TYPE dept_table_type IS TABLE OF  dept%ROWTYPE INDEX BY BINARY_INTEGER;  my_dname_table dept_table_type;  v_count number(2) :=4;BEGIN  FOR int IN 1 .. v_count LOOP    SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10;  END LOOP;  FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP  DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).loc);  DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);  END LOOP;END;

• Example 2
  Write a one-dimensional array, assign values, and output cyclically

Declare -- Define the record TABLE data TYPE reg_table_type is table of varchar2 (25) index by BINARY_INTEGER; -- declare the variable v_reg_table reg_table_type; I int: = 1; begin v_reg_table (1): = 'Li Yan 1 '; v_reg_table (2): = 'Li Yan 2'; v_reg_table (3): = 'Li Yan 3 '; v_reg_table (4 ): = 'Li yanhao 4'; v_reg_table (5): = 'Li yanhao 5'; for int in I .. v_reg_table.LAST LOOP DBMS_OUTPUT.put_line (' '| I | 'name' | v_reg_table (I); I: = I + 1; END LOOP; end;

• Example 3: using a record table by two-dimensional array

Declare -- Define the Data type of the record TABLE: TYPE employee_table_type is table of t_employee % rowtype index by BINARY_INTEGER; -- declare the variable my_employee_table employee_table_type; I int: = 1; begin SELECT fnumber, fname, fage, fsalary INTO my_employee_table (1 ). fnumber, my_employee_table (1 ). fname, my_employee_table (1 ). fage, my_employee_table (1 ). fsalary FROM t_employee WHERE t_employee.fnumber = 'dev002 '; SELECT fnumber, fname, fage, fsalary INTO my_employee_table (2 ). fnumber, my_employee_table (2 ). fname, my_employee_table (2 ). fage, my_employee_table (2 ). fsalary FROM t_employee WHERE t_employee.fnumber = 'sales001'; FOR int IN I .. 2 LOOP Dbms_Output.put_line ('employee DEV002: '| my_employee_table (I ). fname); I: = I + 1; END LOOP; end;