PL/SQL Practice Basic PL/SQL statements

1. No variable anonymous fast

begin   dbms_output.put_line ('HelloWorld');   End;

2. Anonymous block with variable, define variable:

DeclareV_enamevarchar2(Ten); V_sal Number(7,2);begin SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=&N; Dbms_output.put_line ('Employee name is:'||v_ename); Dbms_output.put_line ('Employee Sal is:'||v_sal); End;

3. Define constants and variables and assign values:

DeclareV_enameVARCHAR2(Ten); V_sal Number(7,2); C_empno constant Number(4):=7788;//declaring a constantbegin   SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=C_empno; Dbms_output.put_line ('Employees name is:'||v_ename); Dbms_output.put_line ('Employees Salary is:'||v_sal); End;

4. Assign values to variables in the execution section:

DeclareV_enamevarchar2(Ten); V_sal Number(7,2); V_empno Number(4);beginV_empno:=7788; SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=V_empno; Dbms_output.put_line ('Employee name is:'||v_ename); Dbms_output.put_line ('Employee Sal is:'||v_sal); End;

5. Exception Handling:

DeclareV_enamevarchar2(Ten); V_sal Number(7,2);begin  SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=&N; Dbms_output.put_line ('Employee name is:'||v_ename); Dbms_output.put_line ('Employee Sal is:'||v_sal); Exception whenNo_data_found ThenDbms_output.put_line ('Please input current number!'); End;

6. With%type, ensure that the data type of the variable is consistent with the data type of the field in the table:

Declarev_ename Emp.ename%type;v_sal emp.sal%type;begin   SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=&N; Dbms_output.put_line ('Employee name is:'||v_ename); Dbms_output.put_line ('Employee Sal is:'||v_sal); Exception whenNo_data_found ThenDbms_output.put_line ('Please input current number!'); End;

PL/SQL Practice Basic PL/SQL statements