[SQL] PL/SQL Syntax of Oracle and summary of Extended Data Types

[SQL] PL/SQL Syntax of Oracle and its extended data types summary PL/SQL syntax PL/SQL program consists of three parts: Declaration, execution, and exception handling. Template:

[SQL] DECLARE/* variable Declaration */BEGIN/* Program subject */EXCEPTION/* EXCEPTION Handling part */END;

 

Receiving user input information PL/SQL block can also receive user input information. For example, you are required to enter an employee number and then query the employee name based on the input content. Use "&" to complete user input. Example:

[SQL] DECLARE t_eno NUMBER; t_ename VARCHAR2 (30); BEGIN -- the input information is saved in eno t_eno: = & no; -- and then based on the eno value, SELECT ename INTO t_ename FROM emp WHERE empno = t_eno; DBMS_OUTPUT.put_line ('Number: '| t_eno |' employee name: '| t_ename ); exception when no_data_found THEN DBMS_OUTPUT.put_line ('no employee '); END;

The Custom Data type % type defines a variable, which can be consistent with the data type of a column in the table. The preceding example can also be modified as follows.

[SQL] DECLARE t_eno emp. empno % type; t_ename emp. ename % type; BEGIN -- the input information is saved in eno t_eno: = & no; -- and then based on the value of eno, SELECT ename INTO t_ename FROM emp WHERE empno = t_eno; DBMS_OUTPUT.put_line ('Number: '| t_eno |' employee name: '| t_ename ); exception when no_data_found THEN DBMS_OUTPUT.put_line ('no employee '); END; [SQL] DECLARE t_eno emp. empno % type; t_ename emp. ename % type; BEGIN -- the input information is saved in eno t_eno: = & no; -- and then based on the value of eno, SELECT ename INTO t_ename FROM emp WHERE empno = t_eno; DBMS_OUTPUT.put_line ('Number: '| t_eno |' employee name: '| t_ename ); exception when no_data_found THEN DBMS_OUTPUT.put_line ('no employee '); END;

 

A record of the composite data type encapsulates one or more scalar values into an object. It is a composite structure consisting of scalar values in multiple columns in a single row and is similar to a one-dimensional array. In the following example, the two columns in the query table are put into the record type and the output is displayed.

[SQL] DECLARE type rec_test1 is record (t_empno emp. empno % type, t_ename emp. ename % type); -- defines a record type rt rec_test1; -- defines a variable rt using the record type you just defined. BEGIN select empno, ename into rt. t_empno, rt. t_ename from emp where empno = 1001; dbms_output.put_line ('empno: '| rt. t_empno); END; [SQL] DECLARE type rec_test1 is record (a1 number, a2 varchar2 (10); rt1 rec_test1; rt2 rec_test1; BEGIN rt1.a1: = 1; rt1.a2: = 'China'; rt2.a1: = 2; rt2.a2: = 'usa'; dbms_output.put_line ('rt1. a2: '| rt1.a2); dbms_output.put_line ('rt2. a2: '| rt2.a2); END;

 

% Rowtype % ROWTYPE of the composite data type is a simplified version of record. The difference is that the former structure is the table structure, and the latter is the custom structure. There is no big difference between the two. The former is convenient while the latter is flexible. It is used according to actual conditions. Use RECORD to update data. If you use RECORD to update data, you can only use RECORD Members. If you use % ROWTYPE to update data, you can use % ROWTYPE directly. For example:

[sql] DECLARE    vEmp emp%RowType;  Begin    select * InTo vEmp from empa where empa.EMPNO = '7934';    update  empa set ROW = vEmp where EMPNO ='1111';     commit;  End;  

[Note] before performing the preceding operations, you need to copy the table emp to empa. Copy the table structure

[SQL] create table empa as SELECT * FROM emp WHERE 1 = 2; copy TABLE data [SQL] INSERT INTO empa SELECT * FROM emp; Oracle extended data type Summary % type: the variable type can be consistent with a column in the specified table; t_empno table name. column name % type; % record: multiple columns in a single row, similar to an array; 1. create type 2 first. create another variable type XXX is record (column 1 data type (length), column 2 data type (length )...); X XXX; -- x is a variable, and XXX is its type. How do I reference a column? X. The column name can be % rowtype: Upgrade simplified version of % record, which can make it completely consistent with the structure of a table. x table name % rowtype; -- x is the variable name