PL/SQL Basic exercises

PL/SQL Blocks:
1 anonymous blocks----cannot be stored, cannot be shared
2 stored procedure function The trigger package----a block with a name that can be stored on an Oracle server and can be shared and called multiple times.

1 Anonymous Blocks
Begin
Sql
Sql
End
Anonymous block Example:
SQL >begin
For I in 1..1000 loop
INSERT into T1 values (i);
End Loop;
Commit
End
/


A PL/SQL application consists of one or more blocks.



PL/SQL Language basics
Block structure:
Declare
Declaring variables (constant variable cursors)
Begin
Executive Section
exception
Exception section (optional)
End




Set Serveroutput on
The output information of the server is printed in the plus window.

Sql> set Serveroutput on
Sql>
Sql> begin
2 Dbms_output.put_line (' Hello world ');
3 END;
4/
Hello World
PL/SQL procedure successfully completed




An anonymous block can only be used by the current user's current session and cannot be called by another user.
Named blocks: Anonymous blocks with names


Variable action: Passing Data



Case: Print employee's name and salary

Set Serveroutput on
Declare
V_ename VARCHAR2 (10);
V_sal number (7,2);
Begin
Select Ename,sal
Into V_ename,v_sal
From EMP where empno=#
Dbms_output.put_line (' Employees name is: ' | | V_ename);
Dbms_output.put_line (' Employees sal is: ' | | V_sal);
End
/
Employees name Is:scott
Employees Sal is:3000
PL/SQL procedure successfully completed





Set Serveroutput on
Declare
V_ename VARCHAR2 (10);
V_sal number (7,2);
C_EMPNO constant Number (4): = 7788; ----Declare a constant
Begin
Select Ename,sal
Into V_ename,v_sal
from EMP where empno=c_empno;
Dbms_output.put_line (' Employees name is: ' | | V_ename);
Dbms_output.put_line (' Employees sal is: ' | | V_sal);
End
Assigning values when defining variables or assigning values at execution time
Set Serveroutput on
Declare
V_ename VARCHAR2 (10);
V_sal number (7,2);
C_empno number (4);
Begin
c_empno:=7788;
Select Ename,sal
Into V_ename,v_sal
from EMP where empno=c_empno;
Dbms_output.put_line (' Employees name is: ' | | V_ename);
Dbms_output.put_line (' Employees sal is: ' | | V_sal);
End



Set Serveroutput on
Declare
V_ename VARCHAR2 (10);
V_sal number (7,2);
Begin
Select Ename,sal
Into V_ename,v_sal
From EMP where empno=#
Dbms_output.put_line (' Employees name is: ' | | V_ename);
Dbms_output.put_line (' Employees sal is: ' | | V_sal);
exception
When No_data_found Then
Dbms_output.put_line (' Please input currect number! ');
End



Defines a Boolean type and sets the default value
V_valid Boolean NOT null default false;

V_name Emp.ename%type; -----variable type changes with the Emp.ename field type, that is,%type guarantees that the data type of the variable is consistent with the data type of the field in the table

PL/SQL Basic exercises