Oracle Basic PL/SQL with examples

L/SQL Body

PL/SQL Statement start with BEGIN keyword and close with END keywords. In this example, you tell PL/SQL do nothing for you (null);

Use the slash (/) to compile and run the block.

PL/SQL Declaration

The DECLARATION statement will be used to define program’s variables, cursor, type, etc. In this declaration, you have no variable or cursor defined.

  

SET SERVEROUTPUT

When you use the DBMS_OUTPUT.PUT_LINE procedure, the procedure will write the passing string into the Oracle buffer. In order to print the content of the Oracle buffer, you should use the SET SERVEROUTPUT command to display the content of the Oracle buffer into your screen. 

DBMS_OUTPUPT.PUT_LINE procedure

Write a PL/SQL block, to output the "First PL/SQL Procedure" message.

 

 

Define Variables in PL/SQL

Write a PL/SQL block, to declare a department name variable with the same datatype of the department name. Then assign "HR" to the variable and output the variable.

Save the PL/SQL block

The following is one of the ways that you can save your stored procedure PL/SQL program.

%TYPE keyword

If you are using a variable in the stored procedure that deals with a table column, it should have the same datatype and length as the column itself. Instead of look it up, you use the %TYPE keyword. The advantage of doing that is: if a DBA changes the column datatype or length, you don’t have to modify your stored procedure.

The tablename.column%TYPE syntax means use the same datatype of such column in such table.

Use the %type keyword, to declare a variable as the same datatype and size of the department name column of the dept table. Then save the file.

  

Get a PL/SQL block

%ROWTYPE keyword

The %ROWTYPE keyword creates a composite datatype in which all the columns of a row are pieced together into a record. For example: if I have in the department table two columns (c1, c2) and you need to put them in a variable, you should use %ROWTYPE (v_abc department%ROWTYPE). Now, the v_abc variable contains two columns (c1, c2).

Implicit Cursor

If you define your cursor in the PL/SQL body, it will be called an implicit cursor. In the following example, you see how %ROWTYPE works.

In this example the implicit cursor query the department table information where dept_id = 1. Check if no record was found then print “Record was not found.” else print the department name only. The SQL%NOTFOUND reserved word returns the FALSE value if there are records to read from cursor and returns the TRUE value if there are not record exist to read from cursor.