 -- Create EMP table
CREATE TABLE EMP (
    Empno INT,
    Ename VARCHAR(255),
    Job VARCHAR(255),
    Hiredate DATE,
    Sal DECIMAL(10, 2),
    Comm DECIMAL(10, 2),
    Deptno INT
);

-- Insert values into EMP table
INSERT INTO EMP (Empno, Ename, Job, Hiredate, Sal, Comm, Deptno)
VALUES
(7369, 'Smith', 'Clerk', '1980-12-17', 800.00, NULL, 20),
(7499, 'Allen', 'Salesman', '1981-02-20', 1600.00, NULL, 30),
(7521, 'Ward', 'Salesman', '1981-02-22', 1250.00, 3000.00, 30),
(7566, 'Jones', 'Manager', '1981-04-02', 2975.00, 500.00, 20),
(7654, 'Marm', 'Salesman', '1981-09-28', 1250.00, NULL, 20),
(7698, 'Blake', 'Manager', '1981-05-01', 2850.00, 1400.00, 30),
(7782, 'Clark', 'Manager', '1981-06-09', 2450.00, NULL, 10),
(7788, 'Scott', 'Analyst', '1982-12-09', 3000.00, NULL, 20),
(7839, 'King', 'President', NULL, 5000.00, NULL, 10),
(7844, 'Turner', 'Salesman', '1981-09-08', 1500.00, 0.00, 30),
(7876, 'Adams', 'Clerk', '1983-01-12', 1100.00, NULL, 20),
(7900, 'James', 'Clerk', '1981-12-03', 950.00, NULL, 30),
(7902, 'Ford', 'Analyst', '1981-12-04', 3000.00, NULL, 20),
(7934, 'Miller', 'Clerk', '1982-01-23', 1300.00, NULL, 10);

-- Create DEPT table
CREATE TABLE DEPT (
    Deptno INT,
    Dname VARCHAR(255),
    Loc VARCHAR(255)
);

-- Insert values into DEPT table
INSERT INTO DEPT (Deptno, Dname, Loc)
VALUES
(10, 'Accounting', 'New York'),
(20, 'Research', 'Dallas'),
(30, 'Sales', 'Chicago'),
(40, 'Operations', 'Boston');

-- Add a new field named Depthead in the table DEPT
ALTER TABLE DEPT ADD COLUMN Depthead VARCHAR(255);

-- Drop the column Depthead from the table DEPT
ALTER TABLE DEPT DROP COLUMN Depthead;

created 1 year ago by Biswarup Bhuiya

PL/SQL Online Compiler

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.

Syntax help

Following is the syntax structure for the PL/SQL code blocks

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Example

DECLARE 
   message  varchar2(100):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Named procedures

CREATE OR REPLACE FUNCTION 
hello_user
   (user_name IN VARCHAR2) 
    RETURN VARCHAR2
IS
BEGIN
   RETURN 'Hello ' || user_name;
END hello_user;
/

BEGIN
   dbms_output.put_line(hello_user('Peter'));
END;
/

Exception handling

BEGIN
  DBMS_OUTPUT.put_line (1/0);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;