Oracle--pl/sql variable definition----

Variable introduction when writing a PL/SQL program, you can define variables and constants, including: 1 in a PL/SQL program, scalar type (scalar) 2), composite type (composite)--for manipulating a single record 3), reference type (reference)-- Used to manipulate multiple records 4), LOB (large object) two, scalar (scalar)-common type 1), when writing PL/SQL blocks, if you want to use a variable, you define the variable in the definition section. The syntax for defining variables and constants in PL/SQL is as follows: identifier [constant] datatype [NOT NULL] [: =| default Expr]identifier: Name constant: Specify constants. You need to specify its initial value, and its value cannot be changed datatype: data type NOT NULL: Specified variable value cannot specify initial value for null:= to variable or constant default is used to specify the initial value expr: a PL/SQL expression that specifies the initial value, which can be a literal value, Other variables, functions, and so on. 2), scalar definition Case 1. Define a variable-length string v_ename varchar2 (10); 2. Define a decimal, range -9999.99~9999.99v_sal number (6,2), 3. Define a decimal and give an initial value of 5.4,:= is pl /sql v_sal2 Number (6,2): =5.4;4. Defines a date type of data V_hiredate date;5. Defines a Boolean variable that cannot be null, with an initial value of Falsev_valid Boolean NOT NULL       Default false; Scalars (scalar)--using scalars to define the variables, you can use them. It should be noted here that the PL/SQL block assigns a variable value to a different programming language, with a colon (: =) before the equal sign. The following is an example of entering an employee number that shows the employee's name, salary, and personal income tax (tax rate of 0.03). Describe the use of variables and see how to write them. Set serveroutput on; --Open Output Option declarec_tax_rate number (3,2): =0.03;--tax rate is 0.03v_ename varchar2 (5);--Employee name V_sal number (7,2);--Wages v_tax_sal Number (7,2); --Personal income tax begin--execute select ename,sal into V_ename,v_sal from EMPWhere empno=&empno;--7369v_tax_sal:=v_sal*c_tax_rate;--calculates income tax dbms_output.put_line (' Employee Name: ' | | v_ename| | ' Salary: ' | | v_sal| | ' Tax: ' | | V_tax_sal);--Output end;/ Scalar--Using the%type type has a problem with the PL/SQL block above: If the employee's name exceeds 5 characters, there will be "Ora-06502:pl/sql: Number or value error: String buffer too small" error, in order to reduce the pl/ SQL Program maintenance work, you can use the%type property to define variables, so that it will follow the database column to determine the type and length of the variables you define. Let's see how this is used: identifier name table name. column name%type; for example, v_ename of the above example, this definition: V_ename emp.ename%type;set serveroutput on;  --Open output option declare--the tax rate is 0.03 c_tax_rate number (3, 2): = 0.03;  --Employee name V_ename emp.ename%type;--Recommended use of%type type--Salary v_sal number (7, 2); --Personal income tax v_tax_sal number (7, 2); BEGIN-Executes SELECT ename, SAL into V_ename, v_sal from EMP WHERE empno=&empno;  --7777--Calculate income tax V_tax_sal: = V_sal * c_tax_rate; --Output dbms_output. Put_Line (' Employee Name: ' | | V_ename | | ' Wages: ' | | V_sal | | ' Tax: ' | | V_tax_sal); end;/five, compound variable (composite)--describes the variables used to hold multiple values. These include: 1), PL/SQL Records 2), PL/SQL table 3), nested table 4), Varray Six, composite type--pl/sql records similar to those in high-level languages, it is important to note that when you reference a PL/SQL record member, you must add a record variable as a prefix ( The record variable. Record member) is as follows: Set serveroutput on; --Open output option declare--Define a PL/sql record Type Emp_record_type,--type contains 3 data name, SALARY, TITLE. Plainly, it is a type that can hold 3 data, primarily for the convenience of managing the type Emp_record_type is RECORD (NAME EMP. Ename%type, SALARY EMP. Sal%type, TITLE EMP.  Job%type); --Defines a Sp_record variable, the type of which is Emp_record_type Sp_record emp_record_type;  BEGIN SELECT ename, SAL, JOB into Sp_record from EMP WHERE EMPNO = 7788; Dbms_output. Put_Line (' Employee Name: ' | | Sp_record.name | | ' Wages: ' | | Sp_record. SALARY); end;/VII, compound type--PL/SQL table is equivalent to an array in a high-level language, but it is important to note that the subscript of an array in a high-level language cannot be negative, and PL/SQL is negative, and the subscript of the table element is not limited. Examples are as follows: Method one (recommended): Set serveroutput on; --Open output option declare--defines a PL/SQL table type Sp_table_type, which is used to hold Emp.ename%type--index by VARCHAR2 (20) indicating that the subscript is a string type Sp_table_ TYPE is TABLE of EMP.  Ename%type INDEX by VARCHAR2 (20); --Defines a sp_table variable, the type of which is Sp_table_type sp_table sp_table_type;  BEGIN SELECT ename, sal into sp_table (' ename '), sp_table (' Sal ') from EMP WHERE EMPNO = 7788; Dbms_output. Put_Line (' Employee Name: ' | | Sp_table (' ename ') | | ' Salary: ' | | Sp_table (' Sal ')); end;/method Two: Set serveroutput on; --Open output option declare--Defines a pl/sQL table Type Sp_table_type, which is used to hold Emp.ename%type--index by Binary_integer indicates that the subscript is an integer type Sp_table_type is Table of EMP. Ename%type INDEX by Binary_integer; --Pay attention to Binary_integer if you change to an integer will be error, know friend welcome tell me next--Define a sp_table variable, the type of this variable is sp_table_type sp_table sp_table_type;  BEGIN SELECT ename,sal to Sp_table ( -1), sp_table ( -2) from EMP WHERE EMPNO = 7788; Dbms_output. Put_Line (' Employee Name: ' | | Sp_table (-1) | | ' Salary: ' | | Sp_table (-2)); end;/Description: Sp_table_type is a PL/SQL table type Emp.ename%type specifies the type and length of the elements of the table sp_table is a PL/SQL table variable sp_table (0) that represents the element with the subscript 0 Note: If the Select Ename into Sp_table ( -1) from emp where empno = 7788; becomes select ename into sp_table ( -1) from EMP; The run-time error occurs with the following error: ORA-01422: actual return The number of rows returned exceeds the number of rows requested the workaround is to use the reference variable (not spoken here) eight, compound variable--nested table (nested table) compound variable--variable length array (varray) omit nine, reference variable--introduce reference variable refers to the variable that holds the numeric pointer. By using reference variables, you can make your application share the same objects, thereby reducing the space consumed. When you write a PL/SQL program, you can use both the cursor variable (ref CURSOR) and the object type variable (ref Obj_type) for the reference variable type. It is recommended to use cursor variables. X. Reference variable--REF cursor cursor variable when using cursors, you do not need to specify the corresponding SELECT statement when you define a cursor, but when you use a cursor (open) You need to specify a SELECT statement, so a cursor is combined with a SELECT statement. Examples are as follows: 1. Use PL/SQL to write a block, enter the department number, and show all of the department'sEmployee's name and his salary. 2. On the basis of 1, if an employee's salary is less than 200 yuan, add 100 yuan. SET serveroutput on;declare--define cursor type sp_emp_cursor is ref cursor;--define a cursor variable SP sp_emp_cursor;--define variable V_ename emp.ename%  Type;v_sal Emp.sal%type;beginopen SP for select E.ename,e.sal from emp e where e.deptno=10;--method one loop loop/*loopfetch sp into V_ename,v_sal;exit when Sp%notfound;dbms_output.put_line (' Name: ' | | v_ename| | ' Salary: ' | | v_sal); End loop;*/--method Two while loop/*while 1=1 Loopfetch sp into v_ename,v_sal;exit when Sp%notfound;dbms_output.put_line (' Name: ' | | v_ename| | ' Salary: ' | | v_sal); End loop;*/--method three for loop for cur in (select E.ename,e.sal from emp e where e.deptno=10) loopdbms_output.put_line (' Name: ' || v_ename| | ' Salary: ' | | v_sal); end Loop;end;

　　

Oracle--pl/sql variable definition----