General Basic in PL/SQL (Part I)

I assume that everyone has an Oracle Database software installed on their machine before proceeding to any of the below tutorial.
The tutorials below are to help you and me (myself) to master PL/SQL as i am writing the formula.

Specification we need before proceeding:
1. Oracle Database Installed (we can download from here if you do not have this installed)
2. Oracle SQL *PLUS (it comes with the Database package from above)
3. Oracle SQL Developer (this is optional* it is an GUI for users to enter data more efficiently. click here to download)

Before you start running any script, please make sure that SQL *PLUS has server output turned on.
If not, please run the following command.

Code:

set serveroutput on;


Following is a basic example of PL/SQL: Do this in SQL *PLUS

Code:

DECLARE
current_date Date;
BEGIN
current_date := SYSDATE;
DBMS_OUTPUT.PUT_LINE('Today's Date is: ');
DBMS_OUTPUT.PUT_LINE(current_date);
END;


Alternatively, you can save your script as a SQL file and run the following command in SQL *PLUS.
( You can download my script: basic1.sql ).

Code:

start D:\basic1.sql

Assuming that I have saved the sql file in D Drive.
When you have done the above method of sourcing and running the sql file, SQL *PLUS will display all information of your script to the screen. You need to enter a / and press ENTER to execute it. (Check out the output screen below.)


You will have an output like the following:

Output:

SQL> set serveroutput on
SQL> start D:\basic1.sql
SQL> DECLARE
2 current_date Date;
3 BEGIN
4 current_date := SYSDATE;
5 DBMS_OUTPUT.PUT_LINE('Today is: ');
6 DBMS_OUTPUT.PUT_LINE(current_date);
7 END;
8 /
SQL> /
Today is:
01-JAN-10


Changes to the code to look more tidy: We can concatenate line 5 and 6 into one DBMS_OUTPUT by adding an Oracle PL / SQL string addition operator ||.

Improved Code:

DBMS_OUTPUT.PUT_LINE('Today is: ' || (current_date);

1 | 2 | 3 | 4 | 5 | 6 | 7

COMMENTS:
If you have any enquiry, please comment or feedback here.


RELATED TOPIC:
How to design your own webpage to control Maya in vbscript?
Prerequisites: HTML, VBScript, MEL