DECLARE
/*GLOBAL VARIABLE*/
message VARCHAR2(20):='Hello World!';

num1 INTEGER:=23;
num2 INTEGER:=54;

SUBTYPE photo is VARCHAR(100);
greeting photo;

section VARCHAR(20) DEFAULT 'FA';
section1 INT :=40.2;

BEGIN
/*LOCAL VARIABLE*/
greeting := 'Nice';

/*dbms_output.put_line(message);
*dbms_output.put_line(NUM1);
*dbms_output.put_line(NUM2);
dbms_output.put_line('Hello' || greeting);*/

dbms_output.put_line(section);
dbms_output.put_line(section1);

END;
/



CREATE table employee(
  id int  PRIMARY KEY,
  name VARCHAR2(10) ,
  salary int 
);

INSERT INTO employee VALUES(1,'Anshu',2400000);
SELECT * FROM EMPLOYEE;

DECLARE
emp_id employee.id%type:=1;
emp_name employee.name%type;
emp_salary employee.salary%type;

BEGIN
SELECT id,name,salary INTO emp_id,emp_name,emp_salary 
from employee 
WHERE id=emp_id;
dbms_output.put_line('Employee ' || emp_name || 'earns ' || Emp_salary);

END;
/



DECLARE
a INTEGER:=4;
b INTEGER:=5; 
c INTEGER:=4;

BEGIN
IF (a>b) THEN 
c:=c+1;
ELSE
c:=c*1;
END IF;
dbms_output.put_line(c);

END;
/



DECLARE
x NUMBER:=10;

BEGIN
LOOP
x:x+10;
IF (x>50) THEN 
exit;
END IF;
END LOOP;
dbms_output.put_line(x);

END;
/
by

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;