Oracle pl/SQL entry (ONE)

Oracle pl/SQL entry (ONE) I) PL/SQL when writing rule definition variables, we recommend that you use v _ as the prefix. When defining constants for eg: v_sid and v_name, we recommend that you use c _ as the prefix to define the cursor. When _ cursor is used as the suffix to define an exception, we recommend that you use e _ as the prefix to define the pl/SQL table type, we recommend that you use _ table_type as the suffix to define pl/SQL table variables. When _ table is used as the suffix to define pl/SQL record types, we recommend that you use _ record_type as the suffix to define pl/SQL record variables. _ record is recommended as the suffix and is case insensitive in pl/SQL, but to improve program readability and performance, generally, dml, ddl, or dcl statements such as insert and delete use uppercase pl/SQL keywords, such as declare, begin, and end data types, the identifier and parameters are in lowercase. Database objects and columns are in lowercase. Ii) PL/SQL format

       declare              begin                 exception                    end；                  /

 

Declare indicates the declaration part. In this part, variables, constants, cursors, and composite data types can be defined as optional. Begin is the statement execution part. It is required to execute sal or pl/SQL blocks. Exception is an error that may occur when defining the running part. It is an optional part. Note that it is behind the end; if you want to execute this pl/SQL, you need to use/for execution. There are two types of annotations in pl/SQL, one is single-line annotation --, and the other is multi-line annotation using /**/. Eg:

[SQL] SYS @ orcl # r 1 DECLARE 2 a NUMBER; 3 BEGIN 4 a: = 2 + 200; 5 DBMS_OUTPUT.PUT_LINE (); 6 EXCEPTION 7 when others then 8 dbms_output.put_line ('the error'); 9 * END; 202 PL/SQL process completed successfully. SYS @ orcl </a>

 

Conclusion: note that each keyword, such as the value after BEGIN, must be included, and some actions of when must be specified after exception. Ru, such as no_data_found and others. 3) condition branch statement

      if   condition1 then            statements1      elsif condition2 then            statements2      else             statements3       end if;              eg:[sql] SCOTT@orcl#r    1  DECLARE    2  a NUMBER;    3  BEGIN    4  a:=200;    5  if a>200 then    6  dbms_output.put_line('high');    7  elsif a=200 then    8  dbms_output.put_line('=');    9  else   10  dbms_output.put_line('lower');   11* end if; end;  

 

= PL/SQL process completed successfully. SCOTT @ orcl </a> #4) case statement

        case             when condition1 then statements1             when  condition2 then statement2        .....         else          end case;     eg：   [sql] SCOTT@orcl#declare    2  grade char(1):='B';    3  appraisal varchar2(20);    4  begin    5  appraisal:=    6  case grade    7  when 'A' then 'z'    8  when 'B' then 'j'    9  when 'C' then 'D'   10  else 'b'   11  end;   12  dbms_output.put_line(appraisal);   13  end;   14  /  j  

 

The PL/SQL process is successfully completed. SCOTT @ orcl #5) the loop statement loop and while and for loop are the basic loop format: loop statements exit [when condition] end loop; the statement behind each loop is executed once. When the exit condition is reached, it exits and ends the loop. Eg:

[sql] SCOTT@orcl#declare    2  total number:=0;    3  salary number :=1800;    4  begin    5  loop    6  total:=total+salary;    7  exit when total>25000;    8  end loop;    9  dbms_output.put_line('total salaryis'||total);   10  end;   11  /  total salaryis25200  

 

The PL/SQL process is successfully completed. SCOTT @ orcl # while loop statement This statement must execute at least one statement in the loop body. For a while loop, the statement in the loop body is executed only when the condition is true, while statement to while... Start with loop and end with end loop. Eg:

[SQL] SCOTT @ orcl # r 1 declare 2 total number: = 0; 3 salary number: = 2000; 4 begin 5 while total <200000 loop 6 total: = total + salary; 7 end loop; 8 dbms_output.put_line (total); 9 * end; 200000 PL/SQL process completed successfully. SCOTT @ orcl #