PL/SQL Block Structure Database Management System

PL/SQL Block Structure Database Management System

PL/SQL BLOCK STRUCTURE

DECLARE (Optional)

Variables, cursors, user-defined exceptions

BEGIN (Mandatory)

-SQL statements

-PL/SQL statements

EXCEPTION (Optional)

Actions to perform when errors occur

END; (Mandatory)

 

Declaring PL/SQL Variables

Syntax:

Examples:

identifier [CONSTANT] datatype (NOT NULL)

[:= | DEFAULT expr];

DECLARE

var_hiredate DATE;

var_deptno NUMBER(2) NOT NULL := 10;

var_location VARCHAR2(13) := ‘Uttrakhand’;

c_comm CONSTANT NUMBER := 1500;

Declaring PL/SQL Variables

PL/SQL BLOCK STRUCTURE Database Management System : You must declare all PL/SQL identifiers in the declaration section before referencing them in the PL/SQL block. You have the option to assign an initial value to a variable. You do not need to assign a value to a variable in order to declare it. If you refer to other variables in a declaration, you must be sure to declare them separately in a previous statement.

In the syntax:

identifier is the name of the variable.

CONSTANT Constrains the variable so that its value cannot change; constants must be initialized.

Data type is a scalar, composite, reference, or LOB data type. NOT NULL constrains the variable so that it must contain a value. (NOT NULL variables must be initialized.) expr is any PL/SQL expression that can be a literal expression, another variable, or an expression involving operators and functions.

Naming Rules

Two objects can have the same name, provided that they are defined in different blocks. Where they coexist, only the object declared in the current block can be used.

You should not choose the same name (identifier) for a variable as the name of table columns used in the block. If PL/SQL variables occur in SQL statements and have the same name as a Column, the Oracle server assumes that it is the column that is being referenced.

Consider adopting a naming Convention for various objects that are declared in the DECLARE section of the PL/SQL block.

DECLARE

hire_date date;

BEGIN

Scalar Variable Declarations

DECLARE

Value:=qty*price;

job VARCHAR2(9);

count BINARY INTEGER := 0;

total_salNUMBER(9,2):= 0;

orderdate DATE := SYSDATE +7;

c_tax rate CONSTANT NUMBER(3,2) = 8.25;

valid BOOLEANNOT NULL := TRUE;

Example

Select item code, qty into item_code, avail qty from item Table

Where item code=’1000;

The %TYPE Attribute

When you declare PL/SQL variables to hold column values, you must ensure that the variable is of the correct data type and precision. If it is not, a PL/SQL error will occur during execution.

Rather than hard coding the data type and precision of a variable, you can use the %TYPE attribute to declare a variable according to another previously declared variable or database column. The %TYPE attribute is most often used when the value Stored in the variable will be derived from a table in the database. To use the attribute in place of the data type that is required in the variable declaration, prefix it with the database table and column name. If referring to a previously declared variable, prefix the variable name to the attribute.

PL/SQL determines the data type and size of the variable when the block is compiled so that such variables are always Compatible with the Column that is used to populate it. This is a definite advantage for writing and maintaining code, because there is no need to be concerned with Column data type changes made at the database level. You can also declare a variable according to another previously declared variable by prefixing the variable name to the attribute.

Example:

Prod_Code item.item_code%Type;

(Product code will have the same datatype as item_code column in the item Table Any change in the definition of item_code column in the item table will be reflected in Prod_code also.

%ROWTYPE is used to declare a variable to be a record having same structure as a row in the table.

Example

Item_code item table’% ROWTYPE;

Bind Variables

A bind variable is a variable that you declare in a host environment. Bind variables can be used to pass run-time values, either number or character, into or out of one or more PL/SQL programs. The PL/SQL programs use bind variables as they would use any other variable. You can reference variables declared in the host or calling environment in PL/SQL statements, unless the statement is in a procedure, function, or package. This includes host language variables declared in precompiler programs, screen fields in Oracle Developer Forms applications, and SOL*Plus bind variables.

Creating Bind Variables

To declare a bind variable in the SOL*Plus environment, use the command VARIABLE. For example, you declare a variable of type NUMBER and VARCHAR2 as follows:
VARIABLE return code NUMBER
VARIABLE return msg VARCHAR2030)
Both SOL and SOL*Plus can reference the bind variable, and SOL*Plus can display its value through the SQL*Plus PRINT Command.

Displaying Bind Variables

To display the current value of bind variables in the SQL*Plus environment, use the PRINT command. However, PRINT cannot be used inside a PL/SQL block because it is an SOL*Plus command. The following example illustrates a PRINT command:
VARIABLE g_n NUMBER

PRINT gn
You can reference host variables in PL/SQL programs. These variables should be preceded by a colon.
VARIABLE RESULT NUMBER
An example of using a host variable in a PL/SQL block:
BEGIN SELECT (SAL*12) + NVL(COMMISSION_PCT,0) INTO : RESULT
FROM emp WHERE emp_id=40;
END;
/
PRINTRESULT
Using Bind Variables
To reference a bind variable in PL/SQL, you must prefix its name with a colon (:).
Example:
VARIABLEg_sal NUMBER
BEGIN
SELECT sa –
INTO:gsal
FROM emp
WHERE empid=178;
END;
/
PRINT g sal

Printing Bind Variables

In SQL*Plus you can display the value of the bind variable using the PRINT command.

Referencing Non-PL/SQL Variables

To reference host variables, you must prefix the references with a colon (:) to distinguish them from declared PL/SQL variables.

Example:

This example computes the monthly sal, based upon the annual sal supplied by the user. This script contains both SQL*Plus commands as well as a complete PL/SQL block.

VARIABLE g_monthly_sal NUMBER

DEFINE p_annual _sal = 50000

DECLARE

var_sal NUMBER(9,2) :=&p_annual_sal;

BEGIN

:g_monthly_sal := var_sal/12;

END;

/

PRINT g_monthly_sal

The DEFINE Command specifies a user variable and assigns it a CHAR value. Even though you enter the number 50000, SQL*Plus assigns a CHAR value top_annual_sal consisting of the characters, 5,0,0,0 and 0.

DBMS_OUTPUT.PUT_LINE

  • An Oracle-supplied packaged procedure
  • An alternative for displaying data from a PL/SQL block
  • Must be enabled in SQL*Plus with SET SERVER OUTPUT ON

DEFINE p_annual_sal=60000

DECLARE

var_sal NUMBER (9,2):=&p_annual sal;

BEGIN

var_sal := var_sal/12;

DBMS_OUTPUT.PUT_LINE (“The monthly sal is ‘ ||TO_CHAR(var_sal));

END;

/

You have seen that you can declare a host variable, reference it in a PL/SQL block, and then display its contents in SQL*Plus using the PRINT command. Another option for displaying information from a PL/SOL block is DBMS OUTPUT.PUT LINE. DBMS OUTPUT is an Oracle-supplied package, and PUT LINE is a procedure within that package. Within a PL/SQL block, reference DBMS OUTPUT, PUT LINE and, in parentheses, specify the String that you want to print to the Screen. The package must first be enabled in your SOL*Plus session. To do this, execute the SQL Plus SET SERVEROUTPUT ON command.

The example on the slide computes the monthly sal and prints it to the screen, using DBMS OUTPUT, PUT LINE. The output is shown below:

The monthly salary is 5000

PL/SQL procedure successfully completed.

.

Executing Statements and PL/SQL Blocks DECLARE

var _variable VARCHAR2(5);

BEGIN

SELECT column_name

INTO var_variable

FROM table_name;

162 introduction to DBMS

EXCEPTION

WHEN exception_name THEN

END;

Executing Statements and PL/SQL Blocks

  • Place a semicolon (;) at the end of a SQL statement or PL/SQL control Statement.
  • When the block is executed successfully, without unhandled errors or compile errors, the message output should be as follows:

PL/SQL procedure successfully completed.

  • Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons.
  • END and all other PL/SQL statements require a semicolon to terminate the statement.
  • You can string Statements together on the same line, but this method is not recommended for clarity or editing.

Note: In PL/SQL, an error is called an exception.