PL/SQL data types, definitions, and assignments

PL/SQL data type and definition, value assignment Java code www.2cto.com 1. simple variable types and definitions <strong> SQL and pl/SQL common variable types: </strong> varchar2 (max_length) up to 4000 bytes char (max_length) Max 2000 bytes number (x, y) the maximum length is x, and the y decimal value is seven bytes. The default format is DD-Mon-yy, for example: select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss ') from dual; Note: dual is a pseudo table with only one row and one column. It is easy to query in sys, time-minute mi and mm are both feasible <strong> pl/SQL dedicated variable type: </strong> boolean true/false/null binary_integer integer variable definition Value assignment: ": = "for example: declare v_gende R char (1); v_count binary_integer: = 0; v_totle_sal number (): = 0; v_date date: = sysdate + 7; -- default number of days added c_pi constant number): = 3.14; -- constant c_vaild boolean not null: true; -- the variable is not null begin dbms_output.put_line ('Hello! '); End; Java code www.2cto.com 2. composite variable (composite variable) Table A type (A column of data): similar to an array, which consists of the following table and data. It can be dynamically increased without length restrictions. Declare TYPE ename_table_type -- custom type is table of varchar2 (20) -- data type is varchar2 index by binary_integer; v_ename_table ename_table_type; begin select ename into v_ename_table (1) -- place the first subscript from emp where empno = 7788; dbms_output.put_line (v_ename_table (1); end;/B record type (equivalent to the object type of object-oriented encapsulation ): contains multiple components. The data types of components can be different. Each component is called field (field ). Declare TYPE emp_record_type is record (name varchar2 (20), job varchar2 (10); emp_record emp_record_type; begin select ename, job into emp_record.name, region from emp where empno = 7788; dbms_output.put_line (emp_record.name | '-' | emp_record.job); end;/Note: "|" is a string Connection Symbol, which can be used to connect strings. Java code 3% type and % rowtype: declare v_id emp. empno % type; v_job emp. job % type; begin select empno into v_id from emp where ename = 'Scott '; end; Note: % type indicates that the variable type is the field type in the database table. In the emp table, what is the empno type, and what is the v_id type. What if we want to define the same structure as the table's emp row record? Declare/* previous method, old method TYPE record_type is record (id emp. empno % type, name emp. ename % type, job emp. job % type -- other fields, which are omitted here ..); emp_record record_type; */-- new method, more concise emp_record emp % rowtype; begin select empno, ename into emp_record.empno, emp_record.ename from emp where empno = 7788; partition ('name is: '| emp_record.ename); end; Note: % rowtype: The variable type is the type of all fields in the emp table. That is, the type you currently define. It is consistent with all the field types of emp. It is equivalent to customizing a grid that is the same as that of the emp table, it can store data of the corresponding types of emp fields. Www.2cto.com Java code 4 assignment statement: A direct assignment and expression assignment: declare v_count number (2): = 0; v_married boolean; emp_record emp % rowtype; begin v_count: = 10; emp_record.ename: = 'fuzhi'; v_married: = (1 = 2); -- expression value end; note: "=" in SQL and plsql indicates comparison. B expression value: declare v_married boolean: = true; v_comm emp. comm % type; begin select comm into v_comm from emp where empno = 7788; v_married: = (v_comm is null); dbms_output.put_line (v_married); -- boolean type cannot be printed through this print, the end; C boolean can be used to determine whether declare v_married boolean: = true; v_comm emp. comm % type; begin select comm into v_comm from emp where empno = 7788; v_married: = (v_comm is null); if v_married = true then dbms_output.put_line ('true '); else dbms_output.put_line ('false'); end if; end; D value assignment using the declare emp_record emp % rowtype; v_count number; v_now char (30); v_user char (20 ); begin select ename into emp_record.ename from emp where empno = 7788; v_count: = length (emp_record.ename); v_now: = to_char (sysdate, 'yyyy-mm-dd'); v_user: = user; values (v_now); dbms_output.put_line (v_user); dbms_output.put_line (v_count); end; note: the user is the current user, and show user can check the use of www.2cto.com E substitution variables: declare emp_record emp % rowtype; v_count number; v_now char (30); v_user char (20); begin select ename into emp_record.ename from emp where empno = & no; values (emp_record.ename ); end;