Главная страница Случайная страница
КАТЕГОРИИ:
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Приложение Б. Примеры PL/SQL программ.
|
|
* неименованный блок
DECLARE
n NUMBER;
BEGIN
n: =7369;
delete from emp1 where empno = n;
commit;
END;
* старый способ грузить курсоры
declare
CURSOR gemp IS select * from emp;
c gemp%rowtype;
BEGIN
OPEN gemp;
LOOP
FETCH gemp INTO c;
DBMS_OUTPUT.PUT_LINE(c.ENAME);
EXIT WHEN gemp%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(gemp%ROWCOUNT);
CLOSE gemp;
END;
/
* то же самое, но по-новому
declare
CURSOR gemp IS select * from emp;
BEGIN
for c in gemp loop
DBMS_OUTPUT.PUT_LINE(c.ENAME);
end loop;
END;
* пример с FOR UPDATE
declare
CURSOR gemp IS select * from emp FOR UPDATE;
BEGIN
for c in gemp loop
UPDATE emp set Ename = UPPER(ename) where
current of gemp;
DBMS_OUTPUT.PUT_LINE(c.ENAME);
end loop;
COMMIT;
END;
* массив
declare
TYPE
enametab IS TABLE of emp.ename%type
INDEX BY BINARY_INTEGER;
ent enametab;
n number: =0;
s emp.ename%type;
BEGIN
for c in (select ename from emp) LOOP
ent(n): =c.ename;
n: =n+1;
END LOOP;
n: =1;
s: =ent.first;
WHILE ent.exists(n) LOOP
DBMS_OUTPUT.PUT_LINE(ent(n));
n: = n + 1;
s: =ent.next(n);
END LOOP;
ent.delete(1);
ent.delete;
END;
* применение ROWCOUNT
begin
update emp set ename = 'sesd' where empno = 1234;
DBMS_OUTPUT.PUT_LINE('Rows updated'||SQL%ROWCOUNT);
end;
/
* исключения
declare
n number;
begin
select empno INTO n from emp;
EXcEPTION
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('This error! ');
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Some error! ');
end;
* обрабатываем ошибки сами
declare
n number;
er EXCEPTION;
PRAGMA EXCEPTION_INIT(er, -6502); /* номер ошибки*/
begin
n: = 'ew23';
select empno INTO n from emp;
EXcEPTION when er then
DBMS_OUTPUT.PUT_LINE('This error! ');
end;
/
--создание процедуры
create or replace procedure sas1
(p_empno IN NUMBER DEFAULT 0,
p_newname IN OUT emp.ename%type,
p_err OUT VARCHAR2)
IS
numrows NUMBER;
errmess VARCHAR2(100);
BEGIN
select count(*) into numrows from emp where empno = p_empno;
if numrows = 0 then
raise_application_error(-20001, 'No employe!!! ');
end if;
update emp set ename = p_newname where
empno = p_empno;
commit;
EXCEPTION when OTHERS then
p_err: = SQLERRM;
END;
/
* вызов процедуры
declare
ename emp.ename%type;
err VARCHAR2(1000);
BEGIN
ename: = 'ssssssssss';
sas1(22, p_newname=> ename, p_err=> err);
DBMS_OUTPUT.PUT_LINE(err);
END;
/
* объявление функции
CREATE OR REPLACE FUNCTION men_22(p_empno emp.empno%type)
RETURN VARCHAR2
AS
vRetValue emp.ename%type;
BEGIN
select ename into vRetValue from scott.emp where empno = p_empno;
RETURN vRetValue;
EXCEPTION when NO_DATA_FOUND then
RETURN 'Unknown emp';
END;
/
* заголовок пакета
CREATE OR REPLACE PACKAGE pack_garr as
some_pkg_var emp.ename%type: = 0;
FUNCTION men_22(p_empno emp.empno%type)
RETURN VARCHAR2;
procedure sas1
(p_empno IN NUMBER DEFAULT 0,
p_newname IN OUT emp.ename%type,
p_err OUT VARCHAR2);
END pack_garr;
/
* тело пакета
CREATE OR REPLACE package body pack_garr AS
FUNCTION men_22(p_empno emp.empno%type)
RETURN VARCHAR2
AS
vRetValue emp.ename%type;
BEGIN
select ename into vRetValue from scott.emp where empno = p_empno;
RETURN vRetValue;
EXCEPTION when NO_DATA_FOUND then
RETURN 'Unknown emp';
END men_22;
procedure sas1
(p_empno IN NUMBER DEFAULT 0,
p_newname IN OUT emp.ename%type,
p_err OUT VARCHAR2)
IS
numrows NUMBER;
errmess VARCHAR2(100);
BEGIN
select count(*) into numrows from emp where empno = p_empno;
if numrows = 0 then
raise_application_error(-20001, 'No employe!!! ');
end if;
update emp set ename = p_newname where
empno = p_empno;
commit;
EXCEPTION when OTHERS then
p_err: = SQLERRM;
END sas1;
END;
/
* динамический SQL
declare
s VARCHAR2(300): = 'CREATE TABLE emp_garry as select ename from emp';
begin
execute immediate s;
end;
/
* DSQL, но посложнее
declare
s VARCHAR2(1000);
n number;
rec emp%rowtype;
vname emp.ename%type;
begin
n: = 7902;
s: = 'select * from emp where empno =: 1';
execute immediate s into rec using n;
dbms_output.put_line(rec.ename);
execute immediate s into vname using n;
dbms_output.put_line(vname);
end;
/
* триггер
create or replace trigger emp_01
before
update on scott.emp
for each row
declare
numrows NUMBER;
begin
IF UPDATING THEN
insert into scott.emp1(empno, ename) values (: old.empno,: old.ename);
if: new.ename is null then
raise_application_error(-20001, 'Ooops');
end if;
ELSIF INSERTING THEN
insert into scott.emp1(empno, ename) values (: old.empno,: old.ename);
if: new.ename is null then
: new.ename: = 'default';
end if;
END IF;
end;
/
* триггер на событие пользователя
CREATE OR REPLACE TRIGGER SYS_ON_CREATE_EVENT
AFTER CREATE ON SCHEMA
declare
BEGIN
INSERT INTO SCOTT.emp1(ename) values('Some created');
END;
/