My First PL/SQL program

DECLARATION FOR DATE USING PLSQL

SET SERVEROUTPUT ON
DECLARE
MyName VARCHAR2(10);
MyWeight NUMBER;
today DATE DEFAULT SYSDATE;
BEGIN
MyName :='Khairi';
MyWeight :=60;

DBMS_OUTPUT.PUT_LINE('Hello from Oracle.');
DBMS_OUTPUT.PUT_LINE(MyName);

DBMS_OUTPUT.PUT_LINE('Welcome to Oracle,' || MyName);

DBMS_OUTPUT.PUT_LINE('my name is ' || MyName || 'and my weight is ' || MyWeight);
DBMS_OUTPUT.PUT_LINE('Today is ' || today);

END;

DECLARATION FOR CHAR AND NUMBER USING PLSQL

SET SERVEROUTPUT ON
DECLARE
MyName VARCHAR2(10);
MyWeight NUMBER;
BEGIN
MyName :='Khairi';
MyWeight :=60;

DBMS_OUTPUT.PUT_LINE('Hello from Oracle.');
DBMS_OUTPUT.PUT_LINE(MyName);

DBMS_OUTPUT.PUT_LINE('Welcome to Oracle,' || MyName);

DBMS_OUTPUT.PUT_LINE('my name is ' || MyName || 'and my weight is ' || MyWeight);

END;

SELECT EMPLOYEE NAME FROM EMP TABLE

SET SERVEROUTPUT ON
DECLARE
MyName VARCHAR2(10);
MyWeight NUMBER;
EmployeeName VARCHAR(10);
today DATE DEFAULT SYSDATE;
BEGIN
MyName :='Khairi';
MyWeight :=60;

DBMS_OUTPUT.PUT_LINE('Hello from Oracle.');
DBMS_OUTPUT.PUT_LINE(MyName);

DBMS_OUTPUT.PUT_LINE('Welcome to Oracle,' || MyName);

DBMS_OUTPUT.PUT_LINE('my name is ' || MyName || 'and my weight is ' || MyWeight);
DBMS_OUTPUT.PUT_LINE('Today is ' || today);


--put the name of employee 7499 from the emp table into the variable
--EmployeeName
SELECT ename INTO EmployeeName FROM EMP WHERE empno=7499;
DBMS_OUTPUT.PUT_LINE('Employee : ' || EmployeeName);

END;

LOOPING USING PL/SQL

SET SERVEROUTPUT ON
BEGIN

FOR counter IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(counter);
END LOOP;

DBMS_OUTPUT.NEW_LINE;

FOR counter IN REVERSE 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(counter);
END LOOP;

END;

COMPARISON BETWEEN SQL PLUS VARIABLE AND PL/SQL VARIABLE

OUTPUT :

SQL> start test2
What is your name?:khairi
old   4:         myName :='&name';
new   4:         myName :='khairi';
Hello, khairi Welcome to Oracle

PL/SQL procedure successfully completed.

note :

SQL PLUS handle job input and output from end user.

PL/SQL is a engine to keep input/output and process to database.