GBase8s
存储过程测试方法
-- '------------------
存储过程--------------------------------'
--
在交互式sql交互式工具中执行,创建表
CREATE TABLE COM_T2 (C1 INT,C2 varchar2(10));
INSERT INTO COM_T2 VALUES(10,'a');
INSERT INTO COM_T2 VALUES(20,'b');
INSERT INTO COM_T2 VALUES(30,'c');
COMMIT;
CREATE TABLE PRO_T3 (C1 INT,C2 VARCHAR2(10));
INSERT INTO PRO_T3 VALUES(10,'a');
INSERT INTO PRO_T3 VALUES(20,'b');
INSERT INTO PRO_T3 VALUES(30,'c');
INSERT INTO PRO_T3 VALUES(40,'d');
COMMIT;
CREATE TABLE PRO_T4 (C1 INT,C2 VARCHAR2(10));
COMMIT;
CREATE TABLE PRO_T5 (C1 INT,C2 VARCHAR2(10));
INSERT INTO PRO_T5 VALUES(10,'a');
INSERT INTO PRO_T5 VALUES(20,'b');
INSERT INTO PRO_T5 VALUES(30,'c');
INSERT INTO PRO_T5 VALUES(40,'d');
COMMIT;
CREATE TABLE PRO_T6 (C1 INT,C2 VARCHAR2(10));
COMMIT;
--
创建存储过程
CREATE OR REPLACE PROCEDURE PRO1(A INT)
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
DELETE FROM COM_T2;
SELECT c1,c2 INTO c1t2,c2t2 from pro_t3 where c1=a;
if c1t2<30 then
insert into COM_T2 values(c1t2,c2t2);
else
raise e;
end if;
EXCEPTION
when no_data_found then
insert into COM_T2 values(a,'no data');
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
CREATE OR REPLACE PROCEDURE PRO2(A INT)
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
DELETE FROM pro_t4;
SELECT c1,c2 INTO c1t2,c2t2 from pro_t3 where c1=a;
if c1t2<30 then
insert into pro_t4 values(c1t2,c2t2);
else
raise e;
end if;
EXCEPTION
when no_data_found then
insert into pro_t4 values(a,'no data');
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
CREATE OR REPLACE PROCEDURE PRO3(A INT)
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
DELETE FROM pro_t6;
SELECT c1,c2 INTO c1t2,c2t2 from pro_t5 where c1=a;
if c1t2<30 then
insert into pro_t6 values(c1t2,c2t2);
else
raise e;
end if;
EXCEPTION
when no_data_found then
insert into pro_t6 values(a,'no data');
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
--
调用存储过程并验证结果
call PRO1(10);
select * from COM_T2;
--
调用存储过程并验证结果
call PRO2(50);
select * from PRO_T4;
--
调用存储过程并验证结果
call PRO3(40);
--
清理环境:删除存储过程
DROP PROCEDURE PRO1;
DROP PROCEDURE PRO2;
DROP PROCEDURE PRO3;
drop table COM_T2;
drop table PRO_T3;
drop table PRO_T4;
drop table PRO_T5;
drop table PRO_T6;
exit
GBase8s
存储函数测试方法
-- '------------------
存储函数--------------------------------'
DROP TABLE FUNC_T1;
--
创建表
CREATE TABLE FUNC_T1 (C1 INT,C2 VARCHAR2(10));
INSERT INTO FUNC_T1 VALUES(10,'a');
INSERT INTO FUNC_T1 VALUES(20,'b');
INSERT INTO FUNC_T1 VALUES(30,'c');
INSERT INTO FUNC_T1 VALUES(40,'d');
COMMIT;
CREATE TABLE FUNC_T2 (C1 INT,C2 VARCHAR2(10));
INSERT INTO FUNC_T2 VALUES(10,'a');
INSERT INTO FUNC_T2 VALUES(20,'b');
INSERT INTO FUNC_T2 VALUES(30,'c');
INSERT INTO FUNC_T2 VALUES(40,'d');
COMMIT;
CREATE TABLE FUNC_T3 (C1 INT,C2 VARCHAR2(10));
INSERT INTO FUNC_T3 VALUES(10,'a');
INSERT INTO FUNC_T3 VALUES(20,'b');
INSERT INTO FUNC_T3 VALUES(30,'c');
INSERT INTO FUNC_T3 VALUES(40,'d');
COMMIT;
--
创建存储函数
CREATE OR REPLACE FUNCTION FUNC1(A INT)
RETURN VARCHAR2
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
SELECT c1,c2 INTO c1t2,c2t2 from FUNC_T1 where c1=a;
if c1t2<30 then
RETURN c2t2;
else
raise e;
end if;
EXCEPTION
when no_data_found then
RETURN 'no data';
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
CREATE OR REPLACE FUNCTION FUNC2(A INT)
RETURN VARCHAR2
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
SELECT c1,c2 INTO c1t2,c2t2 from FUNC_T2 where c1=a;
if c1t2<30 then
RETURN c2t2;
else
raise e;
end if;
EXCEPTION
when no_data_found then
RETURN 'no data';
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
CREATE OR REPLACE FUNCTION FUNC3(A INT)
RETURN VARCHAR2
IS
STR VARCHAR2(100);
c1t2 int;
c2t2 varchar2(10);
e exception;
BEGIN
SELECT c1,c2 INTO c1t2,c2t2 from FUNC_T3 where c1=a;
if c1t2<30 then
RETURN c2t2;
else
raise e;
end if;
EXCEPTION
when no_data_found then
RETURN 'no data';
when e then
raise_application_error(-20101,'found c1 is 40');
END;
/
--
在查询语句中调用存储函数
select func1(10) ;
--
在查询语句中调用存储函数
select func2(50);
--
在查询语句中调用存储函数,应该抛出异常
select func3(40) ;
--
删除存储函数执行成功
Drop function func1;
Drop function func2;
Drop function func3;
Drop table FUNC_T1;
Drop table FUNC_T2;
Drop table FUNC_T3;
exit
GBase8s
触发器测试方法
-- '------------------
触发器 --普通触发器--------------------------------'
--
建表、建触发器::
CREATE TABLE students(sno int,grade int);
Insert into students values(100,81);
Insert into students values(100,82);
Insert into students values(100,83);
Insert into students values(100,84);
Insert into students values(100,85);
Insert into students values(101,89);
Insert into students values(102,89);
Insert into students values(103,89);
Insert into students values(104,89);
Insert into students values(104,100);
Insert into students values(104,100);
Insert into students values(104,100);
commit;
CREATE OR REPLACE TRIGGER trig
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
:new.grade:=:old.grade;
END;
/
--
验证触发:
SELECT grade FROM students WHERE sno=101;
UPDATE students SET grade =100 WHERE sno=101;
SELECT grade FROM students WHERE sno=101;
--
清除测试数据和环境:
drop trigger trig;
drop table students ;
-- '------------------
触发器 --INSTEAD OF触发器--------------------------------'
--
建表、建视图,建基于视图操作的INSTEAD OF触发器::
CREATE TABLE T(A INT PRIMARY KEY, B VARCHAR(10));
INSERT INTO T VALUES(1,'AAA');
INSERT INTO T VALUES(2,'BBB');
INSERT INTO T VALUES(3,'BBB');
CREATE VIEW V AS SELECT A FROM T;
CREATE OR REPLACE TRIGGER TRG_INS
INSTEAD OF INSERT ON V
BEGIN
INSERT INTO T VALUES(5,'CC');
END;
/
--
验证触发:
INSERT INTO V VALUES(4);
SELECT * FROM V;
--
清除测试数据和环境:
drop TRIGGER TRG_INS;
drop table T;
drop view V;
-- '------------------
触发器 --DDL事件触发器--------------------------------'
--
创建DDL事件触发器:
CREATE OR REPLACE TRIGGER TRG_DDL
BEFORE CREATE
on SYSDBA.schema
BEGIN
PRINT('a');
END;
/
--
验证触发,执行建表.服务器端打印信息:a 即DDL事件触发器生效:
CREATE TABLE T(A INT PRIMARY KEY, B VARCHAR(10));
--
清除测试数据和环境:删除DDL事件触发器,删除表:
drop table T;
drop TRIGGER TRG_DDL;
-- '------------------
触发器 --系统事件触发器--------------------------------'
--
创建系统事件触发器:
create or replace trigger triggerlogin
after LOGON on database
BEGIN
print('login database');
END;
/
--
验证触发:新建一个到数据库服务器的连接
conn SYSDBA/SYSDBA
--
清除测试数据和环境:删除系统事件触发器
drop TRIGGER triggerlogin;
-- '------------------
触发器 --时间触发器--------------------------------'
--
创建时间触发器,:
CREATE OR REPLACE TRIGGER timer2
AFTER TIMER ON DATABASE
FOR EACH 1 MONTH DAY 28
FROM TIME '09:00' TO TIME '10:00' FOR EACH 1 MINUTE
BEGIN
PRINT 'HELLO WORLD';
END;
/
--
删除时间触发器
drop TRIGGER timer2 ;
exit
官网: