ejecucion de PACKAGE

Siegfried
26 de Octubre del 2004
Como puedo por medio del SQLplus ejecutar un store q se encuentra dentro de un package , cual seria el codigo
seria util q me proporcionen ejemplos , les estare muy agradecido
atte
Siegfried

maverick2kevolution
26 de Octubre del 2004
Hola:

Por el momento no tengo ejemplos, ya que reinstale mi maquina, pero si sabes como ejecutar un procedimiento cualquiera estas al otro lado, lo unico que tienes que hacer es poner el nombre del paquete.nombre de la funcion o procedimiento obviamente si tienes parametros debes ingresarlos tambien.

Cuando instale y me acuerde te envio un ejemplo.

Chao

raymond
26 de Octubre del 2004
Mira a continuacion te mando un ejemplo, de como se puede ejecutar un procedure dentro de un packages en SQLPlus.

Primero create una tabla:

CREATE TABLE employee
(
em_id NUMBER(3) PRIMARY KEY,
em_first_name VARCHAR2(30),
em_last_name VARCHAR2(30),
em_salary NUMBER(6),
em_start_date DATE
);

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (1,'Jason', 'Hales', 20000, '1 ABR 1985');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (2,'Tom', 'Thumb', 18000, '2 SEP 1994');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (3,'Jack', 'Beanstalk', 27000,'25 MAY 1998');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (4,'Snow', 'White', 22500, '18 ABR 1970');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (5,'Dick', 'Whittington', 22500, '19 ABR 1970');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (6,'Sleeping', 'Beauty', 12500, '1 JUN 1996');

INSERT INTO employee (em_id, em_first_name, em_last_name, em_salary, em_start_date)
VALUES (7,'Chicken', 'Licken', 25750, '11 MAY 1998');

COMMIT;
/

Luego, te creas el packages:

CREATE OR REPLACE PACKAGE Employee_Pkg
AS
/******************************************************************
** Se necesita declarar un data type 'IS TABLE' por cada tipo de
** columna de salida de los procedimientos incluidos en el paquete
*******************************************************************/
TYPE tblEMID IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER;
TYPE tblFirstName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE tblLastName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE tblSalary IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE tblStartDate IS TABLE OF DATE INDEX BY BINARY_INTEGER;

PROCEDURE GetEmployeeList(
o_emID OUT tblEMID,
o_FirstName OUT tblFirstName,
o_LastName OUT tblLastName);

PROCEDURE GetEmployeesAboveSalary(
i_MinimumSalary IN employee.em_salary%TYPE,
o_emID OUT tblEMID,
o_FirstName OUT tblFirstName,
o_LastName OUT tblLastName,
o_Salary OUT tblSalary,
o_StartDate OUT tblStartDate);

/*******************************************************************
* Recupera un único empleado usando argumentos estándar
*******************************************************************/
PROCEDURE GetEmployeeDetails(
i_emID IN employee.em_id%TYPE,
o_FirstName OUT employee.em_first_name%TYPE,
o_LastName OUT employee.em_last_name%TYPE,
o_Salary OUT employee.em_salary%TYPE,
o_StartDate OUT employee.em_start_date%TYPE);

END Employee_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Employee_Pkg
AS

PROCEDURE GetEmployeeList(
o_emID OUT tblEMID,
o_FirstName OUT tblFirstName,
o_LastName OUT tblLastName)
IS
CURSOR employee_cur IS
SELECT em_id,
em_first_name, em_last_name
FROM employee;

recCount NUMBER DEFAULT 0;
BEGIN
FOR EmployeeRec IN employee_cur LOOP

recCount:= recCount + 1;

o_emID(recCount):= EmployeeRec.em_id;
o_FirstName(recCount):= EmployeeRec.em_first_name;
o_LastName(recCount):= EmployeeRec.em_last_name;

END LOOP;

END GetEmployeeList;

PROCEDURE GetEmployeesAboveSalary(
i_MinimumSalary IN employee.em_salary%TYPE,
o_emID OUT tblEMID,
o_FirstName OUT tblFirstName,
o_LastName OUT tblLastName,
o_Salary OUT tblSalary,
o_StartDate OUT tblStartDate)
IS
CURSOR employee_cur (curMinSalary NUMBER) IS
SELECT em_id,
em_first_name, em_last_name,
em_salary, em_start_date
FROM employee
WHERE em_salary > curMinSalary;

recCount NUMBER DEFAULT 0;

BEGIN

FOR EmployeeRec IN employee_cur(i_MinimumSalary) LOOP

recCount:= recCount + 1;

o_emID(recCount):= EmployeeRec.em_id;
o_FirstName(recCount):= EmployeeRec.em_first_name;
o_LastName(recCount):= EmployeeRec.em_last_name;
o_Salary(recCount):= EmployeeRec.em_salary;
o_StartDate(recCount):= EmployeeRec.em_start_date;

END LOOP;

END GetEmployeesAboveSalary;

/*******************************************************************
* Retrieves a single employes details using standard arguments
*******************************************************************/
PROCEDURE GetEmployeeDetails(
i_emID IN employee.em_id%TYPE,
o_FirstName OUT employee.em_first_name%TYPE,
o_LastName OUT employee.em_last_name%TYPE,
o_Salary OUT employee.em_salary%TYPE,
o_StartDate OUT employee.em_start_date%TYPE)
IS
BEGIN
SELECT em_first_name,
em_last_name,
em_salary,
em_start_date
INTO o_FirstName,
o_LastName,
o_Salary,
o_StartDate
FROM employee
WHERE em_id = i_emID;
END GetEmployeeDetails;

END Employee_Pkg;
/

A continuacion, luego que ya tienes los objetos de la BD, debes escribir las siguientes lineas en SQLPlus:

SET AUTOPRINT OFF
SET SERVEROUTPUT ON

DECLARE
varaEMID Employee_Pkg.tblEMID;
varFirstName Employee_Pkg.tblFirstName;
varLastName Employee_Pkg.tblLastName;
varSalary Employee_Pkg.tblSalary;
varStartDate Employee_Pkg.tblStartDate;
varRecordCount NUMBER;
BEGIN
Employee_Pkg.GetEmployeesAboveSalary(10000, varaEMID, varFirstName, varLastName, varSalary, varStartDate );

/** Obtenemos el número de registros y desplegamos este valor **/
varRecordCount:= varaEMID.COUNT;
dbms_output.put_line('Registros Encontrados: ' || varRecordCount);

/** Ahora desplegamos los registros **/
FOR RecIndex IN 1..varRecordCount LOOP
dbms_output.put_line(varaEMID(RecIndex) || ', ' || varFirstName(RecIndex)
|| ', ' || varLastName(RecIndex) || ', ' || varSalary(RecIndex) || ', '
|| varStartDate(RecIndex));
END LOOP;

END;

Con esto bastaria.

Salu2

raymond