PACKAGE MUESTRA BUSCAR DATOS

CREATE OR REPLACE PROCEDURE get_emp_name (
   emp_number   IN  NUMBER,
   hire_date    OUT VARCHAR2,
   emp_name     OUT VARCHAR2) AS
BEGIN
   SELECT ename, to_char(hiredate, 'DD-MON-YY')
      INTO emp_name, hire_date
      FROM emp
      WHERE empno = emp_number;
END;

When get_emp_name is compiled on BOSTON_SERVER, its signature, as well as its timestamp, is recorded.

Suppose that on another server in California, some PL/SQL code calls get_emp_name identifying it using a DBlink called BOSTON_SERVER, as follows:

CREATE OR REPLACE PROCEDURE print_ename (emp_number IN NUMBER) AS
   hire_date    VARCHAR2(12);
   ename        VARCHAR2(10);
BEGIN
   get_emp_name@BOSTON_SERVER(emp_number, hire_date, ename);
   dbms_output.put_line(ename);
   dbms_output.put_line(hire_date);
END;

PACKAGE CON ERRORES SQL

CREATE TABLE PAISES

(

  CODPA NUMBER(3, 0) NOT NULL

, NOMPA VARCHAR2(20 BYTE)

, CONPA VARCHAR2(10 BYTE)

, POBLPA VARCHAR2(20 BYTE)

);

CREATE OR REPLACE PACKAGE BODY buscar_pais AS 

  

   PROCEDURE buscar_por_cod(CODPA.id%TYPE) IS

   busqueda paises.NOMMPA%TYPE;

   BEGIN

      SELECT NOMPA INTO  busqueda

      FROM paises

      where id = NOMPA;

      dbms_output.put_line('Paises: '|| busqueda);

   END buscar_por_cod;

END buscar_pais 

AREA DEL TRIANGULO

create or replace function area_triangulo(base real,altura real)
return real as area real;
begin
area:=base*altura/2;
return area;
exception
when zero_divide then
dbms_output.put_line('No se puede dividir por Zero');
return area;
end;

PACK SUMA RESTA MULTIPLICAR DIVIDIR SQL ORACLE

PACK EN ORACLE SQL PARA SUMAR, RESTAR, MULTIPLICAR, DIVIDIR




create or replace function suma(a real,b real)
return real as salida real;
begin
salida:=a+b;
return salida;
exception when others then
dbms_output.put_line('ERROR NO SE PUEDE!!');
return salida;
end;

create or replace function resta(a real,b real)
return real as salida real;
begin
salida:=a-b;
return salida;
exception when others then
dbms_output.put_line('ERROR NO SE PUEDE!!');
return salida;
end;

create or replace function mult(a real,b real)
return real as salida real;
begin
salida:=a*b;
return salida;
exception when others then
dbms_output.put_line('ERROR NO SE PUEDE!!');
return salida;
end;

create or replace function div(a real,b real)
return real as salida real;
begin
salida:=a/b;
return salida;
exception
when zero_divide then
dbms_output.put_line('No se puede dividir por CERO');
return salida;
end;

create or replace function raiz(a real)
return real as salida real;
begin
salida:=sqrt(a);
return salida;
end;


create or replace procedure calc(a real, b real)
as
begin
dbms_output.put_line('suma: ' ||suma(a,b));
dbms_output.put_line('resta: '||resta(a,b));
dbms_output.put_line('mult: ' ||mult(a,b));
dbms_output.put_line('div: ' ||div(a,b));
dbms_output.put_line('Raiz ' ||raiz(a));
end;

begin calc (4,5);end;

Declarar Cursores

declare cursor cpaises
is
select codpa,nompa,conpa from paises;
copa number(3);
nopa varchar2(20);
contpa varchar2(10);
begin
  open cpaises;
  loop
   fetch cpaises into copa,nopa,contpa;
   exit when cpaises%notfound;
   dbms_output.put_line(cpaises%rowcount||' pais '||nopa);
  end loop;
  close cpaises;
end;