Increible

ORACLE DATABASE 10g FUNDAMENTOS DE PL/SQL

-- CAP1 -- Introducción de pl/sql

Un bloque anónimo:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(' Hello World ');
END;

-- CAP2 -- Declaración de variables.

Manejo de variables:

SET SERVEROUTPUT ON
DECLARE
today DATE:=SYSDATE;
tomorrow today%TYPE;
BEGIN
tomorrow:=today +1;
DBMS_OUTPUT.PUT_LINE(' HelloWorld ');
DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| today);
DBMS_OUTPUT.PUT_LINE('TOMORROW IS : ' || tomorrow);
END;

Creando variables bind:

VARIABLE basic_percent NUMBER
VARIABLE pf_percent NUMBER

SET SERVEROUTPUT ON
DECLARE
today DATE:=SYSDATE;
tomorrow today%TYPE;
BEGIN
:basic_percent:=45;
:pf_percent:=12;

tomorrow:=today +1;
DBMS_OUTPUT.PUT_LINE(' HelloWorld ');
DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| today);
DBMS_OUTPUT.PUT_LINE('TOMORROW IS : ' || tomorrow);
END;
/
PRINT basic_percent
PRINT pf_percent

-- CAP3 – secuencias ejecutadas

--VARIABLE basic_percent NUMBER
--VARIABLE pf_percent NUMBER
SET SERVEROUTPUT ON
DECLARE
today DATE:=SYSDATE;
tomorrow today%TYPE;
fname VARCHAR2(15);
emp_sal NUMBER(10);
BEGIN/*:basic_percent:=45;
:pf_percent:=12; */
SELECT first_name, salary INTO fname, emp_sal
FROM employees WHERE employee_id=110;
tomorrow:=today +1;
DBMS_OUTPUT.PUT_LINE(' Hello '|| fname);
/*DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| today);
DBMS_OUTPUT.PUT_LINE('TOMORROW IS : ' || tomorrow); */
DBMS_OUTPUT.PUT_LINE('YOUR SALARY IS : '||emp_sal);DBMS_OUTPUT.PUT_LINE('YOUR CONTRIBUTION TOWARDS PF:
'||emp_sal*:basic_percent/100*:pf_percent/100);
END;
/

El siguiente ejemplo se realiza en dos partes

--declaración de variables bind
VARIABLE basic_percent NUMBER
VARIABLE pf_percent NUMBER
-- Se incluye el PROMP para capturar el empleado.
ACCEPT empno PROMPT 'Please enter your employee number: '
-- como ejemplo se capturo 101
Pleaseenter your employee number: 101

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
empno NUMBER(6):=&empno; --modificación de la declaración que se capturo
fname VARCHAR2(15);
emp_sal NUMBER(10);
BEGIN
:basic_percent:=45;
:pf_percent:=12;

--Modificación de la sentencia SELECT incluyendo la sustitución de la variable empno
SELECT first_name, salary INTO fname, emp_salFROM employees WHERE employee_id=empno;

DBMS_OUTPUT.PUT_LINE(' Hello '|| fname);
DBMS_OUTPUT.PUT_LINE('YOUR SALARY IS : '||emp_sal);
DBMS_OUTPUT.PUT_LINE('YOUR CONTRIBUTION TOWARDS PF:
'||emp_sal*:basic_percent/100*:pf_percent/100);
END;
/

--CAP 4-- Interactuado con el servidor de Oracle.

Ejemplo de insert:

BEGIN
INSERT INTO employees
(employee_id,first_name, last_name, email,
hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
'RCORES',sysdate, 'AD_ASST', 4000);
END;

Ejemplo de actualizar:

DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'ST_CLERK';
END;

Ejemplo de eliminar:

DECLARE
deptnoemployees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;

Ejemplo de merge

DECLARE
empno employees.employee_id%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = c.empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,c.email = e.email,
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.department_id);
END;
/

-- CAP5 -- trabajando con estructuras de control

Ejemplo de la sentencia IF
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSIF myage < 20...