跟我学plsql - 定义变量 （二）

 

This I will continue to intruduce the PLSQL:
 
  Guidelines for Declaring and initializing PL/SQL variables
 
  1: Follo naming conventions.
  2: Using meaningful identifiers for variables.
  3: Initialize variables with the assignment operator (:=) or the default keyword:
   v_nyname varchar2(20) :='john';
   v_myname varchar2(20) default 'john';
  4: Declare one identifier per line for better readability and code mantenance.
 
  5: Avoid using colume names as identifiers.
    
     below example is should be avioded:
    
     declare
       employee_id number(6);
     begin
       select employee_id
       into employee_id
       from employee
       where last_name = 'kochhar';
     end;
     /
   6:Use the NOT NULL constraint when the variable must hold a value.
  
Step2:
  Base Scalar Data Types:
 
  Char [(maxinum_length)]
  varchar2 [(maxinum_length)]
  number [(precision, scale)]
  binary_integer
  pls_integer
  boolean
  binary_float
  binary_double
 
  Note:
  1: The difference of char and varchar2
       char: the length can not be variable.
       varchar2: the length can be variable.
  2: number [(precision, scale)]
      Precision: the total length of the data
      scale:
      For instance,
      a scale of 2 rounds to the nearest hundredth (3.456   becomes  3.46). 
     
  example:
  Declare
   v_emp_job        varchar2(9);
   v_count_loop     binary_integer :=0
   v_dept_total_sal number(9,2) :=0;
   v_orderdate      DATE := sysdate + 7;
   c_tax_rate       constant number(3,2) :=8.25;
    v_valid          BOLLEAN NOT NULL :=TRUE
   
Step3:
  Following I will intruduce the key world of "%type".
 
   Type Is used to declare a variable according to:
   A database colume definition
   
  Instruction:
   identifier table.colume_name%type;
  
  example:
    emp_lname employee.last_name%type
    blance number(7,2);
    min_balance  blance%type :=1000;
   
Step4:

  This step, I will intruduce Boolean variable:
 
  1: Only the TRUE,FALSE, and null values can be assigned to a Boolean variable.
  2: Conaditional expressions use the logical operators AND ...

Step5:
  Bind variable:
 
  1: even the pl/sql block is executed, the bind variable is also have its value.
  2: referenced with a preceding colon when you want to use the value.
 
  example:
 
  Notice:
  below command : variable b_emp_variable number (this is not plsql setence, this is sqlplus sentence)
 
  SQL> variable b_emp_salary number
SQL> begin
  2    select sal into :b_emp_salary
  3    from emp where empno=7876;
  4  end;
  5  /

PL/SQL 过程已成功完成。

SQL> print b_emp_salary;

B_EMP_SALARY
------------
        1100

SQL> select ename,job from emp where sal=:b_emp_salary;

ENAME      JOB
---------- ---------
ADAMS      CLERK

---------------------
Id you want to print the bind variable automatic, you can use below setting:

set autoprint on

let us see below example:

  SQL> set autoprint on
SQL> declare
  2    v_empno number(6) :=&empno;
  3  begin
  4    select sal into :b_emp_salary
  5    from emp where empno = v_empno;
  6  end;
  7  /
输入 empno 的值:  7902
原值    2:   v_empno number(6) :=&empno;
新值    2:   v_empno number(6) :=7902;

PL/SQL 过程已成功完成。

B_EMP_SALARY
------------
        3000