Let’s take a look at PL/SQL Data Type Conversion Functions

Read the rest of this entry »

Let’s take a look at PL/SQL Assignment Statements

Assignment Statements

• Assignment operator: : =

• Variable being assigned to a new value is on left side of assignment operator

• New value is on right side of operator
student_name : = ‘John Miller’;
student_name : = current_student;

Displaying PL/SQL Program
Output in SQL*Plus

• Command to output data from a PL/SQL program in SQL * Plus:
DBMS_OUTPUT.PUT_LINE ( ‘ output string ’ ) ;
DBMS_OUTPUT.PUT_LINE ( ‘ Current Output : ’ ) ;
Executing a PL/SQL Program
in SQL*Plus

• Copy program code from Notepad to SQL*Plus

• Type / to execute

Here’s a simple PL/SQL program

• This program adds two variables and prints the total
• –Three sections are demonstrated in this program with indentations for clarity
•
• DECLARE
• Var1 number := 35;
• Var2 number := 60;
• –Var3 will be used to save the result
• var3 number;
• BEGIN
• Var3 := var1 + var2;
• DBMS_OUTPUT.PUT_LINE(‘ The result of addition is ‘);
• dbms_output.put_line( var3 );
• End;

Arithmetic Operators Used in Statements

Let us discuss about Running PL/SQL Blocks in SQL*PLUS

• In PL/SQL mode semicolon (;) can be used multiple times in single program
• Editing of instruction same as in the SQL statements
• End; appears as the termination of the program
• / can initiate execution of the program

Comment Statements

• Block of comments are delimited with /* */
• /* < comment that spans more than one line of code > */
• Single comment line starts with 2 hyphens
– comment on a single line

Displaying PL/SQL Output in SQL*Plus

• Normally PL/SQL is used with other Oracle utilities such as forms or reports
• PL/SQL in SQL*Plus
• Command to activate memory buffer in SQL*Plus to enable output from PL/SQL programs:
SQL > SET SERVEROUTPUT ON