Tutorial by Examples

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7). The basic un...

set serveroutput on DECLARE message constant varchar2(32767):= 'Hello, World!'; BEGIN dbms_output.put_line(message); END; / Command set serveroutput on is required in SQL*Plus and SQL Developer clients to enable the output of dbms_output. Without the command nothing is displayed. T...

PL/SQL stands for Procedural Language extensions to SQL. PL/SQL is available only as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side applicati...

%TYPE: Used to declare a field with the same type as that of a specified table's column. DECLARE vEmployeeName Employee.Name%TYPE; BEGIN SELECT Name INTO vEmployeeName FROM Employee WHERE RowNum = 1; DBMS_OUTPUT.PUT_LINE(vEmp...

In this example we are going to create a view. A view is mostly used as a simple way of fetching data from multiple tables. Example 1: View with a select on one table. CREATE OR REPLACE VIEW LessonView AS SELECT L.* FROM Lesson L; Example 2: View with a select o...

Below we are going to create a table with 3 columns. The column Id must be filled is, so we define it NOT NULL. On the column Contract we also add a check so that the only value allowed is 'Y' or 'N'. If an insert in done and this column is not specified during the insert then default a 'N' is i...