PL / SQL is a structured programming language, block (block) is the most basic structure in the PL / SQL program, and all PL / SQL programs are composed of blocks. PL / SQL consists of variable description, program code, and exception handling code 3.
Declare ----- Tag Declaration Some Begin ------ Tag Profile Section Start Exception ------ Tag Exception Processing section starts END; -------- mark the body part end
PL / SQL example
1>
Set ServerOutput On; Declare Empname Varchar2 (20); Begin Select EMP_NAME INTO Empname from Cus_Emp_basic WHERE EMP_NO = '00000027'; dbms_output.put_line (empname);
Use the SET ServerOutput on command to set the environment variable serveroutput to open the state, so that the PL / SQL program can save the results in SQL * PLUS and SQL * Plus
Use the function dbms_output.put_line () to output the value of the parameter.
1> Conditional statement
Set ServerOutput on; Declare Num Integer: = 3; Begin if Num <0 Then DBMS_OUTPUT.PUT_LINE ('negative number); Elsif Num> 0 Then DBMS_OUTPUT.PUT_LINE (' positive "); Else DBMS_OUTPUT.PUT_LINE ('0') END IF;
2>
Cycle statement loop ... exit .... End
Set server number: = 1; v_sum integer: = 0; begin loop v_sum: = v_sum num; dbms_output.put_line (num); if Num = 4 THEN EXIT; END IF; dbms_output.put_line (' " ); Num: = Num 1; end loop; dbms_output.put_line ('='); dbms_output.put_line (v_sum);
3> Circular statement loop ... exit when ... End
set serveroutput on; declare v_num integer: = 1; v_sum integer: = 0; begin loop v_sum: = v_sum v_num; dbms_output.put_line (v_num); exit when v_num = 4; dbms_output.put_line ( ' '); v_num: = V_NUM 1; end loop; dbms_output.put_line ('='); dbms_output.put_line (v_sum);
4>
Cycle statement while ... loop ... end loop
Set ServerOutput on; Declare v_num integer: = 1; v_sum integer: = 0;
Begin while v_num <= 4 loop v_sum: = v_sum v_num; dbms_output.put_line (v_num); if v_num <4 THEN DBMS_OUTPUT.PUT_LINE (' '); end if; v_num: = v_num 1; End loop; dbms_output. PUT_LINE ('='); dBMS_OUTPUT.PUT_LINE (V_SUM);
5>
Circular statement for ... in. LOOP .... End loop
set serveroutput on; declare v_num integer; v_sum integer: = 0; begin for v_num in 1..4 loop v_sum: = v_sum v_num; dbms_output.put_line (v_num); if v_num <4 then dbms_output.put_line ( ' ') ; End if; end loop; dbms_output.put_line ('='); dbms_output.put_line (v_sum);
