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

 Class 1:

This I will intruduce the PLSQL:

  First, list the useful of plsql:
  1: variable,constant, and data types
  2: Control structures such as conditional statement and loops
  3: Reusable program units that are written once and executed many times
 
Plsql structure:
 declare (optional)
  variables, cursors,user-defined,exception
 
 begin (mandatory)
  sql statement
  plsql statement
 
 exception (optional)
  actions to perform
  when errors occur
 
 end; (mandatory)
 
From above,we can know the declare and exception are optional, the other is mandatory.
Notice , there is a ";" end by "end"
----------
Type:
 Anonymous  (there is no produce name)(can only use on time)
 procedure  (this have its name )
 Function  (this have its name ,and have it return item)
 
ok, let us start with case of "Anonymous"
 
  Because this is anonyous, when it finished, end with '/', it will completed successfully.
 
  if you want to see the output, you should set below setting:
 
 
SQL> declare
  2    v_fname varchar2(20);
  3  begin
  4    select ename into v_fname from emp where empno=7844;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> set serveroutput on;
(this command function is to enable the dbms_output )

SQL> l  (using charactor : l to list the latest command quickly)
  1  declare
  2    v_fname varchar2(20);
  3  begin
  4    select ename into v_fname from emp where empno=7844;
  5* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> declare
  2    v_fname varchar2(20);
  3  begin
  4    select ename into v_fname from emp where empno=7844;
  5  dbms_output.put_line('The result is ' || v_fname);
  6  end;
  7  /
The result is TURNER

PL/SQL 过程已成功完成。

Class 2:

Step1:
  After completing this lesson, you should be able to do the following:
 
  1: Recognize valid and invalid identifiers
  2: List the uses of variables
  3: Declare and initialize variables
  4: List and describe various data types
  5: Identify the benefits of using the %TYPE attrubute
  6: Declare,use,and print bind variables
 
Step2:
  variables can be used for:
  1: Temporary storage of data
  2: Manipulation of stored values
  3: Reusability
 
  example:
  select
   first_anme
   department_id
  into
   v_fname,
   v_deptno
  from
   ...
 
 
  A variable name:
 
  1: Must start with a letter
  2: Can include letters or numbers
  3: Can include special characters (such as $,_,and #)
  4: Must contain no more than 30 characters
  5: Must not include reserved words
 
 
  Declaring an initializing PL/SQL:
 
  identifier [CONSTANT] datatype [ not null ]
       [ := | DEFAULT expr ];
       
  example:
 
  DECLARE
   V_hiredate   DATE
   V_deptno     NUMBER NOT NULL :=10;
   V_location    varchar2(13)    := 'Atlant';
   c_comm       CONSTANT NUMBER :=1400;
   
  NOTES:
  c_comm is keep the values of "1400".
 
Step3:

  Let us start with two example:
 
  SQL> declare
  2    v_myname varchar2(20);
  3  begin
  4    dbms_output.put_line('My name is :'|| v_myname);
  5  v_myname :='john';
  6  dbms_output.put_line('My name is :'|| v_myname);
  7  end;
  8  /
My name is :
My name is :john

PL/SQL 过程已成功完成。

SQL> declare
  2    v_myname varchar2(20) :='John';
  3  begin
  4    v_myname := 'Steven';
  5  dbms_output.put_line('My name is :'|| v_myname);
  6  end;
  7  /
My name is :Steven

PL/SQL 过程已成功完成。

Step4:
 Delimiters in String Literals:
 
 let us follow with one workshop:
 
 example:
 
 SQL> l
  1  declare
  2    v_event varchar2(15);
  3  begin
  4    v_event := q'!Father's day!';
  5  dbms_output.put_line('3rd Sunday in June is :'|| v_event );
  6    v_event := q'[Mother's day]';
  7  dbms_output.put_line('2nd Sunday in May is :'|| v_event )
  8* end;
SQL> /
end;
*
第 8 行出现错误:
ORA-06550: 第 8 行, 第 1 列:
PLS-00103: 出现符号 "END"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "END" 后继续。

SQL> 7
  7* dbms_output.put_line('2nd Sunday in May is :'|| v_event )
SQL> c/)/);;
  7* dbms_output.put_line('2nd Sunday in May is :'|| v_event );
SQL> l
  1  declare
  2    v_event varchar2(15);
  3  begin
  4    v_event := q'!Father's day!';
  5  dbms_output.put_line('3rd Sunday in June is :'|| v_event );
  6    v_event := q'[Mother's day]';
  7  dbms_output.put_line('2nd Sunday in May is :'|| v_event );
  8* end;
SQL> /
3rd Sunday in June is :Father's day
2nd Sunday in May is :Mother's day

PL/SQL 过程已成功完成。

SQL>

From above case, we learn:
  1: How to list one line of procedure.
  2: How to clarify the one charactor using "c/".
  3: How to voide the charactor spring of "'" of output. (using q'[]' or q'!!' ).