Variable declaration in PL/SQL programming

Variable declaration in PL/SQL programming

Create the first program hello worldbegindbms_output.put_line ('Hello World'); end;/set serveroutput on; (output statement to dos window command) begindbms_output.put_line ('Hello World'); end; /You can see in the dos window Hello world; (Declaration of simple variables) declared variable: declarev_name varchar2 (20); (variable name before, type after) beginv_name: = 'myname'; (assign a value to the variable) dbms_output.put_line (v_name); end;/(Exception Handling) declarev_num number: = 0; beginv_num: = 2/v_num; dbms_output.put_line (v_num); exceptionwhen others thendbms_output.put_line ('error'); end; rules for variable Declaration: 1 variable names cannot use reserved words :( from, select) 2. The first character must be a letter. 3. The variable name can contain a maximum of 30 characters. 4. Do not declare a variable with the same name as a database table or column. 5. Each row can only declare one common variable: binary_integer: integer, it is mainly used to count, not to represent the field type, (improve efficiency) number; number type char: Fixed Length string; date: date; long: long string, maximum 2G; boolean type, optional values: true, false, null; constant exercises; declarev_temp number (1); v_count binary_integer: = 0; v_sal number (4000.00): =; v_date date: = sysdate; v_pi constant number (3.14): =; v_valid boolean: = false; v_name varchar2 (20) not null: = 'myname '; begindbms_output.put_line ('v _ temp value' | v_temp); (| connector) end;/use '--' to comment out a section/**/dbms_output.put_line () boolean variables cannot be printed using the % type attribute: declare (variable declaration type varies according to the table type) v_empno number (4); v_empno2emp.empno % type; v_empno3v_empno2 % type; begindbms_output.put_line ('test'); end; composite variable Declaration: First: Table variable type (equivalent to java array) declaretypetype_table_emp_empnois table of emp. empno % type index by binary_integer; v_empnostype_table_emp_empno; beginv_empnos (0): = 7369; v_empnos (2): = 7839; v_empnos (-1): = 9999; -- (subscript can be smaller than 0) (comment) dbms_output.put_line (v_empnos (-1); end; Record variable type: (equivalent to java class) declaretypetype_record_dept isrecord (deptno dept. deptno % type, dname dept. dname % type, loc dept. loc % type); v_temp type_record_dept; beginv_temp.deptno: = 50; (equivalent to referencing to access the member variable) v_temp.dname: = 'aaa'; v_temp.loc: = 'bj '; declare (v_temp.deptno | ''| v_temp.dname); end; Use % rowtype to declare the record variable; declarev_tempdept % rowtype; declare: = 50; v_temp.dname: = 'aaa'; v_temp.loc: = 'bj '; dbms_output.put_line (v_temp.deptno | ''| v_temp.dname); end; application of the SQL (select) Statement in PlSQL: declarev_ename emp. ename % type; v_sal emp. sal % type; begin -- the select statement in plsql statement must have into, and the return records must be 'select ename, sal1_v_ename, v_sal from emp where empno = 7369; values (v_ename | ''| v_sal); end; declarev_emp emp % rowtype; beginselect * into v_emp from emp where empno = 7369; dbms_output.put_line (v_emp.empno); end; application of other SQL statements in plsql: declarev_deptno dept. deptno % type: = 50; v_dname dept. dname % type: = 'aaa'; v_loc dept. loc % type: = 'bj '; begin -- insert value; insert into dept2 values (v_deptno, v_dname, v_loc); commit; end; declarev_deptnoemp2.deptno % type: = 10; v_count number; beginupdate emp2 set sal = sal/2 where deptno = v_deptno;
-- Select deptno into v_deptno from emp2 where empno = 7369;
-- Select count (*) into v_count from emp2;