PlSql Functions and Procedures

Syntax of PL/SQL Procedure:

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

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

Example 1 of PL/SQL Procedure

create or replace procedure get_area
(Len in number, Wid in number, Area out number)
as
begin
Area := Len * Wid;
end;

SQL> variable area1 number;
SQL> execute get_area (10, 50, :area1);
PL/SQL procedure successfully completed.
SQL> print area1
AREA1
---------
500

Example 2 of PL/SQL Procedure

The following statement creates the procedure remove_emp in the schema hr. The PL/SQL is shown in italics:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
/


The remove_emp procedure removes a specified employee. When you call the procedure, you must specify the employee_id of the employee to be removed.

The procedure uses a DELETE statement to remove from the employees table the row of employee_id. [Via]

PL/SQL Function

PL/SQL Function

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

In PL/SQL a Function takes zero or more parameter values and returns one value.

Syntax of PL/SQL function:

FUNCTION name [(parameter[, parameter, …])] RETURN

datatype IS

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

Example 1 of PL/SQL Function:

Creating a Function: Examples The following statement creates the function Ask_Balance on the sample table cust_orders (the PL/SQL is in italics):

CREATE FUNCTION Ask_Balance(acc_num IN NUMBER) 

   RETURN NUMBER 

   IS acc_bal NUMBER(11,2);

   BEGIN 

      SELECT order_total 

      INTO acc_bal 

      FROM cust_orders 

      WHERE customer_id = acc_num; 

      RETURN(acc_bal); 

    END;

/

The Ask_Balance function returns the balance of a specified account.

When you call the function, you must specify the argument acc_num, the number of the account whose balance is sought. The datatype of acc_num is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the balance column from the row identified by the argument acc_num in the cust_orders table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created in the preceding example can be used in a SQL statement. For example:

SELECT Ask_Balance(165) FROM DUAL;

Ask_Balance(165)
------------
2519

[Source]

Example 2 of PL/SQL Function:

create or replace function find_area
(Len in number, Wid in number)
return number
as
varea number;
begin
varea := Len * Wid;
return varea;
end;

SQL> select find_area (10, 30) area from dual;

AREA
---------
300

PL/SQL tables

PL/SQL has two composite datatypes: TABLE and RECORD.
Objects of type TABLE are known as PL/SQL tables.
PL/SQL tables use a primary key to give you array-like access to rows. The number of rows in a PL/SQL table can increase dynamically.
The PL/SQL tables grows as new rows are added. PL/SQL tables can have one column and a primary key, neither of which can be named. The column can belong to any scalar type, but the primary key must belong to type BINARY_INTEGER.
A PL/SQL tables can consist of one simple datatype or be defined as a type of record and is sometimes referred to as an Index by table.
Rows in a PL/SQL table do not have to be contiguous.
Tables with simple datatypes can be populated as:
<variable>(<integer>) := <value>;
Tables with complex datatypes will need the columns populated individually as:
<variable>(<integer>).<column_name> := <value>;
Or from a cursor:
fetch <cursor_name> into <variable>(<integer>);

Example 1 of PL/SQL Table

Type my_first_table is table of varchar2(10)
Index by binary_integer;
Var_of_table my_first_table;
Var_of_table(1) := ‘hello world’;
Var_of_table(2) := ‘bye’;

Example 2 of PL/SQL Table

Type my_emp_table is table of emp%rowtype
Index by binary_integer:
Var_of_emp my_emp_table;
Var1_of_emp my_emp_table;
Var_of_emp(1).ename := ‘sachin’;
Var_of_emp(1).empno := 20;
Var_of_emp(1).sal := 3000;

To delete individual records from PL/SQL tables:

Var_of_emp .delete(1);

To remove all entries from a PL/SQL table:

Var_of_emp.delete;

Or

Var_of_emp := var1_of_emp

Where var1_of_emp does not contain any value, it is empty.

COUNT method can be used to return number of records in a PL/SQL Table.

Var_of_table.count

First, Next and Last methods of PL/SQL Tables.

First is for first index in the PL/SQL Tables.

Last is for last index in the PL/SQL Tables.

Next is for next index in the PL/SQL Tables.

Example showing First and Next method of PL/SQL tables

SQL> set serveroutput on

SQL> Declare
2
3 Type my_dept_table is table of varchar2(20)
4 Index by binary_integer;
5
6 Var_of_dept my_dept_table;
7 Index_value number;
8
9 Begin
10
11 For dept_rec in (select * from dept) loop
12 Var_of_dept(dept_rec.deptno) := dept_rec.dname;
13 End loop;
14
15 Index_value := var_of_dept.first;
16 Loop
17 Exit when index_value is null;
18 Dbms_output.put_line (index_value || ' ' ||var_of_dept(index_value));
19 Index_value := var_of_dept.next(index_value);
20 End loop;
21 End;
22 /

10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

PL/SQL procedure successfully completed.


Another example of PL/SQL Table

DECLARE

 TYPE last_name_type IS TABLE OF student.last_name%TYPE

  INDEX BY BINARY_INTEGER;

 last_name_tab last_name_type;

last_name_type TYPE is declared based on the column LAST_NAME

 of the STUDENT table. 

last_name_tab is the actual index-by table declared as 

LAST_NAME_TYPE.

DECLARE 

 CURSOR name_cur IS

  SELECT last_name

  FROM student

  WHERE rownum <=10;

   TYPE last_name_type IS TABLE OF student.last_name%TYPE 

      INDEX BY BINARY_INTEGER;

   last_name_tab last_name_type;

v_counter INTEGER := 0;

BEGIN

     For name_rec IN name_cur LOOP

 v_counter := v_counter + 1;

 last_name_tab(v_counter) := name_rec.last_name;

 DBMS_OUTPUT.PUT_LINE('last_name('||v_counter||'): '|| 

 last_name_tab(v_counter));

     END LOOP;

END;

OUTPUT will be like:

last_name(1): Roy

last_name(2): Brit