跟我学plsql - 写可执行的语句 （一）

 

This I will intruduce the PLSQL:

Step1:

  Using Literals:
  1: Character and date literals must be enclosed in quotation marks
  2: Number can be simple values or in scientific notation.
 
  example:
   v_name :='henderson';

Step2:
  Commenting Code
 
  1: Prefix fingle-line comments with two hyphens (--).
  2: Place a block comment between the symbols /* and */
 
  example:
 
    Declare
    ...
    v_annual_sal NUMBER (9,2);
   
    BEGIN
    /* computer the annual salary based on the
     monthly salary input from the user */
    v_annual_al :=monthly_sal * 12;
    --The following line display the annual salary
    DBMS_OUTPUT.put_line(v_annual_sal);
    END;
    /

Step3:
  Sql functions in PLSQL:
 
  1:Available in procedural statement:
   Single-row function:
   
   such as "to_char" ,up, to_date,...
 
  2: Not available in procedural statements:
    DECODE
    (this can not be in plsql setence, only in sql)
   
    Group functions:
    SUM
    (this can not be in plsql setence, only in sql).
   
Step4:
  Using Sequence in plsql expressions:
 
  Notices:
  Starting in 11g:
 
  DECLARE
  v_new_id number;
 BEGIN
  v_new_id := my_seq.NEXTVAL;
  END;
 /
 
  Before:
  DECLARE
  v_new_id number;
 BEGIN
  select my-seq.nextval into v_new_id from dual;
  END;
 /

Step5:
  Data Type Conversion
  Type:
  implicit conversion
  Explicit conversion
 
  example:
 
  1: implicit data type conversion:
  v_date_of_joining DATE := '02-FEB-2000'
 
  this is ok, even the charactor :02-FEB-2000 can data conversion.
  this is based on the nls_lang setting, if the nls_lang setting changed,
  the data conversion will cause error.
 
  2: Below is an error example:
  v_date_of_joining DATE :='February 02,2000';
 
  3: --explicit data type conversion
  v_date_of_joining DATE :=TO_DATE('February 02,2000','Month DD,YYYY');
  
We will do one workshop:

vi u1.sql

  declare
 a_number number;
BEGIN
 a_number :='125';
 a_number := a_number +3;
 DBMS_OUTPUT.PUT_LINE(to_char(a_number,'9999'));
END;
/

SQL> set serveroutput on
SQL> @u1
128

PL/SQL 过程已成功完成。

Step6:
  Nested blocks:
  plsql blocks can be nested:
  An executable section (BEGIN ... END) can contain nested blocks.
 
example:
  SQL> l
  1  declare
  2    v_outer_variable varchar2(20) := 'GLOABLE';
  3  BEGIN
  4    DECLARE
  5      V_inner_variable varchar2(20) := 'local variable';
  6    begin
  7      dbms_output.put_line(v_inner_variable);
  8      dbms_output.put_line(v_outer_variable);
  9    end;
 10  dbms_output.put_line(v_outer_variable);
 11* end;
SQL> /
local variable
GLOABLE
GLOABLE

PL/SQL 过程已成功完成。