config.sql
-- Clean up the database objects
DROP TRIGGER validateUser;
DROP PROCEDURE check_email;
DROP TABLE userinfo;
-- Create the table
CREATE TABLE userinfo(
username VARCHAR2(20) ,
password VARCHAR2(40),
email VARCHAR2(50) NOT NULL,
phone VARCHAR2(12) NOT NULL,
hobbies VARCHAR2(2000),
address VARCHAR2(2000)
)
-- Add PRIMARY KEY constraint
ALTER TABLE USERINFO ADD CONSTRAINT USERNAME_PRIMARY_KEY PRIMARY KEY (USERNAME)
-- This statement adds a check constraint which does a check on the password format.
-- This uses a regular expression to check if the password is alpha-numeric or not.
ALTER TABLE USERINFO ADD CONSTRAINT check_password CHECK (REGEXP_LIKE( password,'(([0-9][a-z]{1}|[a-z][0-9]{1}))' ))
-- Insert data into the userinfo table
INSERT INTO USERINFO ( USERNAME, PASSWORD, EMAIL, PHONE, HOBBIES,ADDRESS ) VALUES ( 'Jason', 'steve123', 'Jason@Jason.com', '515-123-4567', 'football, writing,Cricket', '2455 Rose Garden Rd, 15220, Pittsburgh, PA, US');
INSERT INTO USERINFO ( USERNAME, PASSWORD, EMAIL, PHONE, HOBBIES,ADDRESS ) VALUES ( 'Neena', 'neena88', 'Neena@Neena.com', '515-123-4568', 'foot ball, guitarist', '1812 Timberline Rd, 16601, Altoona , PA, US');
INSERT INTO USERINFO ( USERNAME, PASSWORD, EMAIL, PHONE, HOBBIES,ADDRESS ) VALUES ( 'Lex ', 'Lex001', 'Lex@email.com', '515-123-4569', 'reading', '2899 Grand Ave, 15225, Pittsburgh, PA, US');
INSERT INTO USERINFO ( USERNAME, PASSWORD, EMAIL, PHONE, HOBBIES,ADDRESS ) VALUES ( 'David', 'dav007', 'David@email.com', '590-423-4569', 'base ball, cricket, music', '1126 Pawlings Rd, 19403, Norristown, PA, US');
INSERT INTO USERINFO ( USERNAME, PASSWORD, EMAIL, PHONE, HOBBIES,ADDRESS ) VALUES ( 'John ', 'John222', 'John@Johnmail.com', '650-121-2019', ' baseball', '354 N Prince St, 17603, Lancaster, PA, US');
commit;
-- Procedure to check the email format. This accepts Email ids of
-- abc123@xyz.com format.
-- The ([a-z]+|[0-9]+)@[[:alpha:]]+[.]COM pattern is useful in
-- identifying if the email id is of xyz123@abc.com format.
CREATE OR REPLACE PROCEDURE check_email (email IN VARCHAR2 ) AS
isValid BOOLEAN;
BEGIN
IF ( Email IS NOT NULL ) THEN
isValid := REGEXP_LIKE( Email ,'([a-z]+|[0-9]+)@[[:alpha:]]+[.]COM','i' );
IF ( NOT isValid ) THEN
raise_application_error(-20101, 'E-Mail id should be of abc@xyz.com format');
END IF ;
ELSE
raise_application_error(-20102, 'E-Mail id can not be NULL');
END IF;
END;
-- Create a trigger that validates the phone number. The regular expression
-- ([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4} uses the POSIX representation
-- to check the correctness of phone numbers entered. [[:digit:]]{3} indicates
-- occurrence of any numeric value 3 times. It accepts values only of
-- type 123-123-1234
CREATE OR REPLACE TRIGGER validateUser BEFORE INSERT OR UPDATE ON userinfo FOR EACH ROW
DECLARE
isValid BOOLEAN;
BEGIN
IF ( :NEW.phone IS NOT NULL ) THEN
isValid := REGEXP_LIKE(:NEW.phone,'^([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4})$');
IF ( NOT isValid ) THEN
raise_application_error(-20110, 'Phone number should be of 123-123-1234 format');
END IF ;
ELSE
raise_application_error(-20111,'Phone number can not be NULL');
END IF;
-- Validate the email id entered.
check_email(:NEW.email);
END;
分享到:
相关推荐
Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g....
第一章 PL-SQL一览 ...第七章 PL-SQL与Oracle间交互 第八章 控制PL-SQL错误 第九章 PL-SQL子程序 第十章 PL-SQL包 第十一章 PL-SQL对象类型 第十二章 本地动态SQL 第十三章 PL-SQL应用程序性能调优
PL-SQL 帮助文件 PL-SQL 帮助文件 PL-SQL 帮助文件 PL-SQL 帮助文件 PL-SQL 帮助文件 PL-SQL 帮助文件
讲解数据库语言中SQL、T-SQL与PL-SQL的主要区别,及其相关的使用。
PL-SQL编程.ppt PL-SQL编程.pptPL-SQL编程.ppt
Allround_Automations_PL-SQL_Developer__Language_Packs_Downloadly.ir.rar
本⽂⽬录: 1. PL/SQL 程序设计简介 2. PL/SQL块结构和组成元素 3. PL/SQL流程控制语句 4. 把游标说透 5. 异常错误处理 6. 把过程与函数说透 7. 程序包的创建与应⽤ 8. 把触发器说透
Oracle PL-SQL语言初级教程.pdf Oracle PL-SQL Oracle PL-SQL
PL-SQL.zip里有密钥,PL-SQL方便用于对Oracle的开发!
pl-sql-dev-8-setup PL/SQL developer 的安装文件 另外还有它的注册机 和 简体中文包 都在我的资源中 我的刚解决 所以免费贴出来和大家分享
oracle的辅助开发工具, 内带注册码! 很好用哦. PL.SQL.Developer PL.SQL.Developer PL.SQL.Developer
BBSC PL-SQL 编程规范BBSC PL-SQL 编程规范BBSC PL-SQL 编程规范
Oracle-Pl-Sql 部分的ppt课件,可以作为plsql入门使用。
Oracle PL-SQL入门之慨述.doc Oracle PL-SQL语言基础.doc ORACLE 数据库开发经验总结.doc Oracle9i PL-SQL编程的经验小结.doc ORACLE函数大全.doc Oracle中使用PL-SQL操作COM对象.doc Toad资料.doc PL-SQL单行函数和...
11oracle的PL-SQL编程-块.过程 PPT 11oracle的PL-SQL编程-块.过程 PPT
pl-sql编程pl-sql编程pl-sql编程pl-sql编程pl-sql编程
PL-SQL案例.rar
大连华信教学课件Oracle-Pl-Sql.rar
教程名称:Oracle高级开发课程之PL-SQL编程入门视频课程目录:【】PL_SQL编程课程_-lesson3【】PL_SQL编程课程_lesson5-2(完结)【】PL_SQL编程课程_最佳入门教程-intr1【】PL_SQL编程课程_最佳入门教程-lesson1【】...