Suggested Pages

Sunday, July 22, 2012

PL/SQL Create a Package with Functions - Tutorial

In this post i'll show the syntax to write a function in PL/SQL.

CREATE TABLE

table.sql

create table employee (
   id numeric,
   name varchar2(30),
   salary numeric,
   CONSTRAINT impiegati_pk PRIMARY KEY (id)
);



CREATE PACKAGE SPECIFICATION

PackEmployee.pks
DROP PACKAGE myschema.pkg_mypackage;

CREATE OR REPLACE PACKAGE myschema.pkg_mypackage
IS

FUNCTION get_num_employee_by_name (employee_name in varchar2) RETURN number;

END pkg_mypackage;



CREATE PACKAGE BODY

PackEmployee.pkb

CREATE OR REPLACE PACKAGE BODY myschema.pkg_mypackage
IS

FUNCTION get_num_employee_by_name (employee_name in varchar2)
 RETURN number
 IS
      return_value   NUMBER := 0;
      strSQL         varchar2(3000) := '';
  
 BEGIN

     strSQL := strSQL ||'  SELECT count(imp.id)';

     strSQL := strSQL ||'  FROM employee emp ';

     strSQL := strSQL ||'  WHERE emp.name = '''|| employee_name ||'''';
      
 EXECUTE IMMEDIATE strSQL INTO return_value;

return return_value;

 END get_num_employee_by_name;


END pkg_mypackage;




INVOKE FUNCTION OF PACKAGE

invoke.sql

select myschema.pkg_mypackage.get_num_employee_by_name(‘Simone’)
from dual

No comments :

Post a Comment

Suggested Pages