Writing basic Pl/sql statements

PL/SQL means procedural language SQL/sequel, one major need for PL/SQL is for reusability,ability to store some names of our codes stored in the database dictionary and then re-execute them by names.
Types of PL/SQL
1.  Anonymous blocks : blocks of codes that has no name.
Example: lets login as user scott,password tiger.
Now lets take a look at the tables user scott owns.
Note: user scott is a sample table that comes with installing oracle 11g,you can write the following statement to make user scott active. Writing pl/sql statements

Run this query below to login as sysdba,startup instance then unlock user scott.

          sqlplus /*hit enter*/
          sys / as sysdba  /*Login as sysdba and input password when prompted*/
          startup  /*opens/starts database if instance is idle*/
          alter user scott identified by tiger account unlock;  /*unlocks user scott and assigns a password tiger*/
          

Now we can write a query to check tables that belongs to user scott but first we login as user scott.

          conn scott /*to connect user scott input password tiger*/
          select table_name from user_tables;
          

The image below shoes the tables that belongs to user scott.Writing pl/sql statements

 We shall work with the emp table. to check the structure of emp table,use the desc function.  Take a time to really look at the table closely.

           desc emp;
           

Name                                                       Null?                           Type
--------------------------------------        ----------------------------      -------------
EMPNO                                                     NOT                         NULL NUMBER(4)
ENAME                                                                                       VARCHAR2(10)
JOB                                                                                             VARCHAR2(9)
MGR                                                                                            NUMBER(4)
HIREDATE                                                                                 DATE
SAL                                                                                              NUMBER(7,2)
COMM                                                                                         NUMBER(7,2)
DEPTNO                                                                                    NUMBER(2)

Run the following codes.
Typing SET SERVEROUTPUT ON in SQL*Plus has the effect of invoking DBMS_OUTPUT.ENABLE (buffer_size => NULL); with no limit on the output.

           set serveroutput ON 
           DECLARE
           eid NUMBER :=7566;
           --eid emp.empno%TYPE;
           f emp.ename%TYPE; 
           j emp.job%TYPE;
           BEGIN
           SELECT ename,job
           into f,j
           from emp
           where empno=eid; 
           dbms_output.put_line('Employee' || eid || ':' || f || ' ' || j);
           EXCEPTION
           WHEN no_data_found THEN
           dbms_output.put_line ( ' No employee record ' || eid || ' found.' );
           END; 
           /
           /* Note:its better you write the codes yourself than copy and paste.*/
           

This is an example of an anonymous block that displays the name and job of a particular employee that has an employee number 7566.
In the block of code, set serveroutput ON is not a PL/SQL code,its SQL*Plus code and its means we are turning the ability to print out lines at the dbms_output.put_line ('employee' || eid || ':' || f || ' ' || j);
The block of code is beginning at line SQL>DECLARE keyword,from DECLARE keyword to BEGIN is were we declare any variables,Then from BEGIN to EXCEPTION contains our block of code,also called the body.then we have an optional section called the EXCEPTION.

SQL>DECLARE
1        eid NUMBER :=7566;
2 --    eid emp.empno%TYPE;
3        f emp.ename%TYPE;
4        j emp.job%TYPE; 

In the Declare block,we declared variables,eid as the variable name, NUMBER as the variable datatype,then we assign it a value of 7566 and we terminate it with a semi-colon ";"


Line 2 is a comment,anything that starts with a "-- ..."is definately a comment,what it means is that variable name eid has the same datatype as empno column in the emp table.

In lines 3 and 4 we are declaring a variable name f and j, they have the same datatype as ename and job column respectively in the emp table.

5       BEGIN
6       SELECT ename,job
7       into f,j
8       from emp
9       where empno=eid;

In the BEGIN block of code,we write our SELECT statement that selects records in ename,job columns into variables f,j. where empno=eid and which ends with semi-colon.
If the SELECT statement returns no value then an exception occurs,which is why we have an EXCEPTION handler in lines 11 to 13.

10     dbms_output.put_line('Employee' || eid || ':' || f || ' ' || j);

In line10, after the execution of the SQL statement returns a single value, then dbms statement displays the employee CONCAT also ( '||' ) with his employee number,eid which is (7566),CONCAT Name and Job.
when their is no record with employee number 7566,then an exception occurs,and in line 13,the dbms statment prints out 'No employee record 7566 found'.