PL/SQL Basic Examples
2021-03-12 08:22:50

PL/SQL Basic Examples

PL/SQL Basic Examples

In this topic, we will see different basic examples to understand the concept of PL/SQL.

Program to declare a variable using a general syntax

 DECLARE
 V_NUMB VARCHAR2(50) NOT NULL DEFAULT 'HELLO';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB);
 END;

Output:

When we execute the code as mentioned above, the compiler will give the output -

STATEMENT PROCESSED
HELLO

Here in this program, first, we have declared a variable V_NUMB with a data type of VARCHAR2 with a maximum length of 50 and set the default value to HELLO and later called the variable to display the output

Program to overwrite a pre-declared variable using a general syntax

 DECLARE
 V_NUMB VARCHAR2(50) NOT NULL DEFAULT 'HELLO';
 BEGIN
 V_NUMB := ' PL / SQL ';
 DBMS_OUTPUT.PUT_LINE (V_NUMB);
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
PL / SQL

Here in this program, first, we have declared a variable V_NUMB with a data type of VARCHAR2 with a maximum length of 50 and set the default value to HELLO, and later called the variable with a value which overwrites the default value and displayed the output.

Program to overwrite a pre-declared variable using general syntax and "||."

Example-1

 DECLARE
 V_NUMB VARCHAR2(50) NOT NULL DEFAULT 'HELLO';
 BEGIN
 V_NUMB := ' PL / SQL ' || ' COURSE ';
 DBMS_OUTPUT.PUT_LINE (V_NUMB);
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
PL / SQL COURSE

Here in this program, first, we have declared a variable V_NUMB with a data type of VARCHAR2 and a maximum length of 50, and set the default value to HELLO. Later called the variable with a value which overwrites the default value and displayed the output PL / SQL, and then added another command using "||".

Example-2

 DECLARE
 V_NUMB VARCHAR2(50) NOT NULL DEFAULT 'HELLO';
 BEGIN
 V_NUMB := ' PL / SQL ' || ' COURSE ';
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output:

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
PL / SQL COURSE BEGINNER TO ADVANCE

In this, we have called the variable with a value which overwrites the default value and displayed the output PL / SQL and then added another command using "||" and altered the output two times.

Program to directly declare a keyword and assign the value

 DECLARE
 V_NUMB VARCHAR2(50) NOT NULL:= ' WELCOME ';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output as-

Statement processed.
WELCOME BEGINNER TO ADVANCE

Here in this program, first, we have declared a variable V_NUMB with a data type of VARCHAR2 with a maximum length of 50, and set the default value to welcome. Then we have called the declared variable to get the output.

Program to use a number as a data type 

 DECLARE
 V_NUMB NUMBER NOT NULL := 50.42;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
50.42 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of NUMBER. In numeric data type, we don't have to define maximum length. We have assigned the value of V_NUMB to 50.42, and then called the declared variable to get the output with a text.

Program to use a number as a data type with precision

 DECLARE
 V_NUMB NUMBER(2) NOT NULL := 50.42;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
50 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of NUMBER. In numeric data type with a precision parameter of 2. We have assigned the value of V_NUMB to 50.42, and then called the declared variable to get the output with a text. Due to the precision, it will only display the output to 2 characters.

Program to use a number as a data type with maximum length and precision.

 DECLARE
 V_NUMB NUMBER(5,1) NOT NULL := 50.42;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
50.4 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of NUMBER. Here we defined a maximum length of 5 bytes and a precision of 1 (precision works for the digits after the decimal, and precision must be smaller than or equal to the maximum length defined). We have assigned the value of V_NUMB to 50.42, and then called the declared variable to get the output with a text.

Program to use PLS _ INTEGER as a data type with maximum length and precision.

 DECLARE
 V_NUMB PLS _ INTEGER NOT NULL:= 50.42;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
50 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of PLS _ INTEGER. We have assigned the value of V_NUMB to 50.42 and then called the declared variable to get the output with a text.

Program to use BINARY_FLOAT as a data type

 DECLARE
 V_NUMB BINARY _ FLOAT NOT NULL:= 50.42;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
5.04199982E+001 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of BINARY _ FLOAT. We have assigned the value of V_NUMB to 50.42 and then called the declared variable to get the output with a text

Program to use DATE as a data type

 DECLARE
 V_NUMB DATE NOT NULL:= SYSOUT;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
18 – DEC - 20 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of DATE. We have assigned the value of V_NUMB to sysout (which will display the present date from the working computer), and then called the declared variable to get the output with a text.

Program to use DATE as a data type 

 DECLARE
 V_NUMB DATE NOT NULL := '19 – DECEMBER – 20 20 : 01 : 32';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
19 – DECEMBER – 20 20: 01: 32 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of DATE. We have assigned the value of V_NUMB, which display the date declare in the variable and then called the declared variable to get the output with a text.

Program to use TIMESTAMP as a data type with SYSTIMESTAMP parameter

 DECLARE
 V_NUMB TIMESTAMP NOT NULL:= SYSTIMESTAMP ;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
18 – DEC - 20 03 . 32 . 29 . 803467 PM BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of TIMESTAMP. We have assigned the value of V_NUMB to SYSTIMESTAMP ( which will display the present date from the working  computer), and then called the declared variable to get the output with a text.

Program to use TIMESTAMP WITH TIME ZONE as a data type with SYSTIMESTAMP parameter

 DECLARE
 V_NUMB TIMESTAMP WITH TIME ZONE NOT NULL:= SYSTIMESTAMP ;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
20 – DEC - 20 07 . 54 . 23 . 194995 AM + 00 : 00 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of TIMESTAMP WITH TIME ZONE. We have assigned the value of V_NUMB to SYSTIMESTAMP (which will display the present date from the working computer) and then called the declared variable to get the output with a text.

Program to use TIMESTAMP WITH TIME ZONE as a data type with SYSTIMESTAMP parameter and precision with TIMESTAMP

 DECLARE
 V_NUMB TIMESTAMP(3) WITH TIME ZONE NOT NULL:= SYSTIMESTAMP ;
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
20 – DEC - 20 07 . 54 . 23 . 194 AM + 00 : 00 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of TIMESTAMP WITH TIME ZONE. We have assigned the value of V_NUMB to SYSTIMESTAMP (which will display the present date from the working computer) and then called the declared variable to get the output with a text. Due to the precision of TIMESTAMP here in this example, we can only see 3 digit, whereas earlier we were able to see 6 digit because the TIMESTAMP default value is 6.

Program to use INTERVAL DAY TO SECOND as a data type

 DECLARE
 V_NUMB INTERVAL DAY(4) TO SECOND(2):= ' 24 02 : 05 : 21.012 ';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
+0024 02: 05: 21.01 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of INTERVAL DAY TO SECOND. We have assigned the value of V_NUMB (this data type is used to store and manipulate intervals between days, hours, minutes, and seconds. We can add precision to DAY and SECOND for the number of digits).

Program to use INTERVAL YEAR TO MONTH as a data type

 DECLARE
 V_NUMB INTERVAL YEAR TO MONTH := ' 12 - 3 ';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
+ 12 - 03 BEGINNER TO ADVANCE

Here in this program, we have declared a variable V_NUMB with a data type of INTERVAL YEAR TO MONTH. We have assigned the value of V_NUMB (this data type is used to store and manipulate intervals between years and months. We can add precision to YEAR for the number of digits, and the default value for precision is 2 for a year, and if we define the variable with more digits than precision, the compiler will give us an error).

Program to use INTERVAL YEAR TO MONTH as a data type with wrong precision

 DECLARE
 V_NUMB INTERVAL YEAR TO MONTH := ' 122 - 3 ';
 BEGIN
 DBMS_OUTPUT.PUT_LINE (V_NUMB || ' BEGINNER TO ADVANCE ');
 END;

Output

When we execute the code as mentioned above, the compiler will give the output -

 ORA-01873: the leading precision of the interval is too small ORA-06512: at line 2
 ORA-06512: at "SYS.DBMS_SQL", line 1721

Here in this program, we have declared a variable V_NUMB with a data type of INTERVAL YEAR TO MONTH. We have assigned the value of V_NUMB (this data type is used to store and manipulate intervals between years and months. We can add precision to YEAR for the number of digits, and the default value for precision is 2 for a year. If we define the variable with more digits than precision, the compiler will give us an error).