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; /
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.
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.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
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;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;