Oracle PL/SQL basic syntax summary, oracle basic syntax

Oracle PL/SQL basic syntax summary, oracle basic syntax

Basic Oracle PL/SQL Syntax:

Declare (optional, declaring a variable or cursor)

Begin (required, where the statement is executed)

Exception)

End; (required. The semicolon cannot be omitted from this end)

PL/SQL Assignment Methods:

1.: =

2.

Example 1: (basic type variable)

Declare

Pname varchar2 (20 );

Page number (10 );

Pdate date;

Begin

Pname: = 'kin ';

Page: = 20;

Pdate: = sysdate;

Dbms_output.put_line (pname );

Dbms_output.put_line (page );

Dbms_output.put_line (pdate );

Dbms_output.put_line (pdate + 1 );

End;

Note:

1. dbms_output.put_line (); // output data

2. pdate + 1; // Add one to indicate tomorrow

Example 2: (reference variable) query the employee name and salary numbered 7839

Declare

Pname emp. ename % type; // The type of the pname variable is the type of the ename field in the emp table.

Psal emp. sal % type;

Begin

Select ename, sal into pname, psal from emp where empno = 7839;

Dbms_output.put_line (pname | 'salary is '| psal );

End;

Note:

1. The order of variables before into and after into must be consistent;

2. | it is a connector

Example 3: (record-type variable) query the employee name and salary numbered 7839

Declare

Emp_rec emp % rowtype; // The emp_rec variable type is the content of a row in the emp table, which is an array.

Begin

Select * into emp_rec from emp where empno = 7839;

Dbms_output.put_line (emp_rec.ename | 'salary is '| emp_rec.sal );

End;

PL/SQL process-oriented language:

IF judgment statement: (judge the number entered by the user from the keyboard)

If judgment condition... Then execution statement... Elsif judgment condition... Then executes Statement 2... None of the above else conditions are met... End if ;,

-Accept the number entered on the keyboard. num indicates an address value, which stores the input value.

Accept num prompt 'enter a number :';

Declare

-Define variables and save the input values from the keyboard

Pnum number: = #

Begin

If pnum = 0 then dbms_output.put_line ('Number is 0 ');

Elsif pnum = 1 then dbms_output.put_line ('Number is 1 ');

Elsif pnum = 2 then dbms_output.put_line ('Number is 2 ');

Else dbms_output.put_line ('other numbers ');

End if;

End;

Note:

Pnum = 1 indicates the sentence to be judged, not the meaning of the value assignment.

While loop statement: (loop output 1-10)

While judgment condition loop body end loop;

Example:

Declare

-Initialize the variable value.

Pnum number: = 1;

Begin

While pnum <= 10 loop

Dbms_output.put_line (pnum );

-Auto-Increment

Pnum: = pnum + 1;

End loop;

End;

Note:

Pnum: = pnum + 1 indicates adding 1 at a time for each loop, that is, auto-increment. Pnum ++ is not supported;

Loop: (recommended) (loop output 1-10)

Loop exit when cycle condition; loop body; end loop;

Example 1:

Declare

-Initialize the variable value.

Pnum number: = 1;

Begin

Loop exit when pnum> 10;

Dbms_output.put_line (pnum );

-Auto-Increment

Pnum: = pnum + 1;

End loop;

End;

Example 2: (loop output 20-10)

Declare

-Initialize the variable value.

Pnum number: = 20;

Begin

Loop exit when pnum <10;

Dbms_output.put_line (pnum );

-Auto-Increment

Pnum: = pnum-1;

End loop;

End;

Note:

Loop: exit the loop according to the conditions;

While loop is: the body that meets the conditions for executing the loop.

For Loop: (loop output 1-10)

For variable in variable range (must be consecutive intervals) loop body; end loop;

Example:

Declare

-Declare Variables

Pnum number;

Begin

For pnum in 1 .. 10 loop

Dbms_output.put_line (pnum );

End loop;

End;

Note:

After in, the range of variables must be consecutive intervals. (And can only be in ascending order. data cannot be printed in descending order)