Oracle PL/SQL programming variables

2022-07-10 0 By

A variable can be used in any program block, subroutine (function, stored procedure).Variables must start with a letter, including letters, digits, underscores (_), dollar signs, and #2.The name of a variable can contain a maximum of 30 characters. 3. The name of a variable cannot be the same as the name of a table or column in the database.5. Variables are case insensitive, ‘v_id’ and ‘v_id’ are the same variable declare v_id number(20) not null := 1;v_address varchar2(50) :='Facai Road, Facai Cityv_name constant varchar2(10) :='Chong money & # 39;;v_birthday date;begin dbms_output.put_line('v_id:'||v_id); dbms_output.put_line('v_address:'||v_address); dbms_output.put_line('v_name:'||v_name); dbms_output.put_line('v_birthday:'||v_birthday);end;The following output is displayed:V_id :1v_address: 88888v_name: chengda_v_birthday ::= not null indicates that a variable cannot be empty and must be assigned at the time it is defined.If there is no “not NULL” constraint, Oracle will set the value of a variable to null by default. If the value of a variable is not null, Oracle will set the value to null by default.It is recommended that variables be initialized when they are declared, otherwise if any value is evaluated with NULL, the result will be NULL.Note that at the end of each line of a declared variable, you need to add;Declare v_id number(20) not null := 1;v_address varchar2(50) := 'Facai Road, Facai Cityv_name constant varchar2(10) := 'Chong money & # 39;;v_birthday date;Begin — reassign v_id to v_id := '& # 39; 2022;dbms_output.put_line('v_id:' || v_id); dbms_output.put_line('v_address:' || v_address); dbms_output.put_line('v_name:' || v_name); dbms_output.put_line('v_birthday:' || v_birthday);end;The result is as follows: v_id:2022v_address: Facai Road 88888v_name: Zheng Daqian v_birthday:q'We said earlier that an Oracle string is always enclosed in two single quotes, but what if the string contains single quotes?At this point you can use q'The following is an example of the operator: declare v_id number(20) not NULL := 1;v_address varchar2(50) := q'@China'Tang@'; v_name constant varchar2(10) := q'[Magic'C]'; v_birthday date;begin dbms_output.put_line('v_id:' || v_id); dbms_output.put_line('v_address:' || v_address); dbms_output.put_line('v_name:' || v_name); dbms_output.put_line('v_birthday:' || v_birthday);end;V_id :1v_address:China'Tangv_name:Magic'Cv_birthday: this delimiter can specify any character that is not present in a character. Cv_birthday: this delimiter can specify any character that is not present in a character.Varchar2 (size): specifies the data type and length of a variable. Size indicates the length of the variable. Char (size): specifies the length of the variable.Number (p, S): indicates the number of digits in a number, and s indicates the precision of a number. Boolean: Indicates a Boolean data type, consisting of three values: TRUE, FALSE, and NULLdate.Timestamp: char, varchar, varchar2Varchar is defined in standard SQL, while Varchar2 is a unique data type provided by Oracle. Varchar takes up two bytes for Chinese characters, full corners and other characters, and one byte for English characters, which occupies a small memory. For empty strings, VARCHAR is still treated as empty strings.Varchar2 normally handles all characters in two bytes, depending on the character set.For example, GBK, Chinese characters will take up two bytes, one for English; for UTF-8, Chinese characters will normally take up three bytes, one for English, and varchar2 stores variable length strings, varchar2 treats empty strings as null.Char is a fixed-length type, regardless of how much data is stored, and can be filled with Spaces if the inserted length is less than the defined length.% TYPE data types in programming, we variables are the most commonly used is to save the data in the database, which requires the data in the database and the TYPE of variable is consistent, if we went through to lookup table structure definition, it is too much trouble, Oracle provides an easy way, don’t need a lookup table structure, you just need to specify the table fields,The following is an example :declare — Declare a variable of Type %Type to receive ename data v_emp_name emp.ename% Type;Select ename into v_emp_name from emp where empno='7369';Dbms_output. Put_line (& # 39; v_emp_name: & # 39; | | v_emp_name); end; the results are as follows: v_emp_name: SMITH, we can also use % Type, references to his own definition of variables, the sample is as follows:Declare V_name VARchar2 (20):= v_name2 v_name%TYPE:= #39; V_name2 v_name%TYPE:= #39; BEGIN DBMS_output.V_name: & # 39; | | v_name); dbms_output. Put_line (& # 39; v_name2: & # 39; | | v_name2); end; the results are as follows:V_name2:3 %Type attribute variable, NOT NULL constraint binding variable (known as host variable), we previously declared variables in the statement block, after the program runs, but the binding variable is NOT the same, after the execution of the program block, the binding variable is released in memory.Variables are still accessible and can be understood as global variables, as shown in the following code:SQL> variable v_name varchar2(20);SQL> SQL> begin 2 :v_name :='2022'; 3 end;4 /PL/SQL procedure successfully Completedv_name ———2022SQL> print v_namev_name———2022SQL> Reference binding variable,BLOB: used to store large binary objects BLOB: Used to store large binary objects Variable name Naming Specification V_ Variable name: common variable C_ Variable name:Constant p_ variable name: substitute variable g_ variable name: global variable