Procedures in pl/sql, PL-SQL Programming

Assignment Help:

Procedures 

The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below:

PROCEDURE name [(parameter[, parameter, ...])] IS

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

Where the parameter stand for the following syntax:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name

[{:= | DEFAULT} expression]

The datatype of a parameter cannot be constrained. For illustration, the declaration below of acct_id is illegal as the datatype CHAR is size-constrained:

PROCEDURE reconcile (acct_id CHAR(5)) IS ... - illegal

Though, you can use the workaround below to size-constrain parameter types ultimately:

DECLARE

temp CHAR(5);

SUBTYPE Char5 IS temp%TYPE;

PROCEDURE reconcile (acct_id Char5) IS ...

The procedure has 2 parts: the specification and the body. The procedure specification starts with the keyword PROCEDURE and ends with the procedure name or the parameter list. The Parameter declarations are elective. The Procedures that take no parameters are written without the parentheses.

The procedure body starts with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has 3 parts: a declarative section, an executable section, & an optional exception-handling section.

The declarative section contains the local declarations, that are placed between the keywords IS and BEGIN. The keywords DECLARE, that introduces the declarations in an anonymous PL/SQL block, and is not used. The executable section contains statements that are placed between the keywords BEGIN & EXCEPTION (or END). At least one statement should appear in the executable section of a procedure. The NULL statements meet this necessity. The exception-handling section contains the exception handlers that are placed between the keywords EXCEPTION and END. Consider the procedure raise_salary that increases the salary of an employee by a given amount:

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS

current_salary REAL;

salary_missing EXCEPTION;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;

ELSE

UPDATE emp SET sal = sal + amount

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO emp_audit VALUES (emp_id, 'No such number');

WHEN salary_missing THEN

INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END raise_salary;

Whenever called, this procedure accepts an employee number and the salary increase sum. It uses the employee number to select the present salary from the emp database table. When the employee number is not found or if the current salary is null, an exception is raised. Or else, the salary is updated.

The procedure is known as the PL/SQL statement. For illustration, you might call the procedure raise_salary as shown below:

DECLARE

emp_id NUMBER;

amount REAL;

BEGIN

...

raise_salary(emp_id, amount);


Related Discussions:- Procedures in pl/sql

Sqls counterpart of the key words, SQLs counterpart of the key words: ...

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA...

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Controlling cursor variables, Controlling Cursor Variables You use 3 s...

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

"not enforced" table constraints - sql, "Not Enforced" Table Constraints ...

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Using aliases-declarations in sql, Using Aliases The Select-list items f...

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

I need data entry conversion project, Project Description: This is stage...

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S

Predefined exceptions, Predefined Exceptions The internal exception is ...

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Query optimization, 1.( /5 marks) Suppose that a B+-tree index with the sea...

1.( /5 marks) Suppose that a B+-tree index with the search key (dept_name, building) is available on relation department. What would be the best way to handle the following selecti

Character types in pl/sql, Character Types The Character types allow yo...

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd