SQL Foundation and branching loops for Oracle databases

first, the SQL base language

DECLARE -- Statement varchar2 (ten-- Variable or object BEGIN  A:=' Xiao Ming ') ; --   : =   denotes assigning a value to a variable -  - output with Dbms_output.put_line ()  END;

Second, branch

DECLARE  --StatementA Number(Ten); B Number(Ten);BEGINA:= 2; B:= 3; IFA<B ThenDbms_output. Put_Line ('A is less than B'); elsif A>B Then --pay attention to the wording of elsifDbms_output. Put_Line ('a greater than B'); ELSEDbms_output. Put_Line ('a equals b'); END IF; END;

Third, CirculationFirst Type: (Loop loop)

DECLAREM Number(5); BEGINM:= 5; LOOPEXIT  whenM< 0; Dbms_output.    Put_Line (M); M:=M- 1; ENDLOOP; END;

Execution Result:

The second type: (While loop)

DECLAREM Number(5);BEGINM:= 5;  whileM> 0LOOP--While LoopDbms_output.    Put_Line (M); M:=M- 1; ENDLOOP;END;

Execution Result:

Third type: (For loop)

DECLARE BEGIN   for inch 1 5 LOOP     -- A For loop can only traverse regular numbers such as the     Dbms_output. Put_Line (N);   END LOOP; END;

Execution Result:

Iv. Handling Exceptions

DECLAREM Number(Ten); SNAMEVARCHAR2(Ten);BEGINM:= 5; SELECTS.name intoSNAME fromZ_student SWHERES.id=M; Dbms_output. Put_Line ('Query Result:' ||SNAME);END;

Execution Result:

Then throw an exception exception

DECLAREM Number(Ten); SNAMEVARCHAR2(Ten);BEGINM:=  -;--change a value that does not exist  SELECTS.name intoSNAME fromZ_student SWHERES.id=M; Dbms_output. Put_Line ('Query Result:' ||SNAME); EXCEPTION--Throw Exception   whenNo_data_found ThenDbms_output. Put_Line ('no query to data'); END;

Execution Result:

Let me give you an example:

DECLARE  --StatementA Number(Ten); B Number(Ten); Nozero EXCEPTION; --define Nozero as Exception typeBEGINA:= 2; B:= 0; IFA= 0 ORB= 0  ThenRAISE Nozero;--RAISE throws an exception  END IF; IFA<B ThenDbms_output. Put_Line ('A is less than B'); elsif A>B Then    --pay attention to the wording of elsifDbms_output. Put_Line ('a greater than B'); ELSEDbms_output. Put_Line ('a equals b'); END IF; EXCEPTION whenNozero ThenDbms_output. Put_Line ('neither a nor B can be 0! ');  whenOTHERS ThenDbms_output. Put_Line ('Other exceptions! '); END;

The result of the execution is:

SQL Foundation and branching loops for Oracle databases