Oracle OCP笔记(16)SQL与PL/SQL对象
1.SQL语言16条命令:
数据操纵语言(Data Manipulation Language, DML)命令:
·SELECT
·INSERT
·UPDATE
·DELETE
·MERGE
数据定义语言(Data Definition Language, DDL)命令:
·CREATE
·ALTER
·DROP
·RENAME
·TRUNCATE
·COMMENT
数据控制语言(Data Control Language, DCL)命令:
·GRANT
·REVOKE
事务控制语言(Transaction Control Language, TCL)命令:
·COMMIT
·ROLLBACK
·SAVEPOINT
2.PL/SQL对象
匿名块
存储过程(Procedure)
函数(Function)
包(包头Package、包体Package Body)
触发器(Trigger)
视图(View)
(1)匿名块
DECLARE
-- 声明部分 (程序未用到可不写)
BEGIN
-- 执行部分 (必需)
EXCEPTION
-- 异常处理 (不处理异常可不写)
END;
(2).存储过程(Procedure)
CREATE OR REPLACE PROCEDURE procedure_name
[(argument [IN | OUT | IN OUT] type,…)]
{IS | AS }
-- 变量定义,cursor ,子过程等
BEGIN
-- procedure body;
EXCEPTION
-- 例外处理
END [procedure_name];
(3).函数(Function)
CREATE [OR REPLACE] FUNCTION function_name
[(argument [IN | OUT | IN OUT] type,…)]
RETURN return_type {IS | AS}
-- 变量定义
BEGIN
-- function body
EXCEPTION
-- 例外处理
END [function_name];
(4).包(Package)
Package header:
CREATE OR REPLACE PACKAGE Package_name IS/AS
PROCEDURE procedure_name(x IN NUMBER,y OUT VARCHAR2);
FUNCTION function_name RETURN VARCHAR2;
var VARCHAR2(100);
END;
Package Body:
CREATE OR REPLACE PACKAGE BODY Package_name IS/AS
PROCEDURE procedure_name(x IN NUMBER , y OUT VARCHAR2) AS
BEGIN
IF X > 100 THEN
Y := 'X>100';
ELSE
Y := 'X<=100';
END IF;
END;
FUNCTION function_name RETURN VARCHAR2 AS
BEGIN
VAR := 'HELLO';
RETURN VAR||'EVERYONE';
END;
END;
(5).触发器(Trigger)
CREATE OR REPLACE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE (of)| DELETE | DDL event]
ON [table | database | schema]
FOR EACH ROW
[WHEN (condition)]
DECLARE
-- 变量定义
BEGIN
-- :old.column_name
-- :new.column_name
-- if inserting then ...
EXCEPTION
END trigger_name;
只能通过触发事件来运行触发器.
DML触发器: INSERT、UPDATE、DELETE.
DDL触发器: CREATE、ALTER、DROP、TRUNCATE
数据库操作: SERVERERROR、LOGON、LOGOFF、STARTUP、SHUTDOWN
暂停: SUSPEND -- alter system suspend; alter system resume;
触发器级别: 语句级别,行级(FOR EACH ROW)
触发器内不能使用事务控制语句: Commit,Rollback,Savepoint,触发器主体调用的存储过程或函数中不能使用事务控制语句.
触发器主体可以使用: :NEW, :OLD
WHEN字句: 仅能用于行级触发器,触发器主体仅对满足WHEN字句指定条件的行进行处理.
触发器谓词: INSERTING,UPDATING,DELETING 值为TRUE/FALSE.