ORACLE数据库 PL/SQL语句基础知识点 适合有SQL基础的人群。
禁止转载！
存储过程
声明部分：包括类型、变量和游标
执行部分：完成功能而编写的SQL语句或是PL/ SQL代码块
异常处理部分
创建存储过程基本语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN|OUT|IN OUT] datetype
[DEFAULT datavalue]
[,…])]
{IS|AS}
[variable type [,…]]
BEGIN
procedure_body
EXCEPTION
END [procedure_name];
删除过程基本语法
DROP PROCEDURE procedure_name;
调用过程基本方式
可以使用EXECUTE指令调用过程，也可以在PL/SQL代码块中直接调用过程(不加EXECUTE)。
eg.1通过存储过程查询不同部门的人员数。
CREATE OR REPLACE PROCEDURE pro_count_dept(var_deptno emp.deptno%type)
AS var_count NUMBER; //带参数的存储过程
BEGIN
SELECT count(*) INTO var_count FROM emp WHERE
deptno = var_deptno; //存储过程自带参数var_deptno DECLARE部分已经声明
DBMS_OUTPUT.PUT_LINE(‘部门号’||var_deptno||’的员工人数是’||var_count);
EXCEPTION WHEN NO_DATA_FOUND then
dbms_output.put_line(‘没有数据’);
END;
/
执行存储过程
法1.使用
EXECUTE pro_count_dept(10) //自带参数10
法2.PL/SQL代码块中直接调用过程
BEGIN
pro_count_dept(10) //自带参数10
END;
参数基本语法说明
IN： 向存储过程传递参数， 只能将实参的值传递给形参。
OUT： 从存储过程输出参数， 存储过程结束时形参的位会赋 给实参。
IN OUT： 具有前面两种模式的特性， 调用时， 实参的位传递给形参， 结束时， 形参的值传递给实参。
eg2.通过存储过程，利用雇员编号查询雇员雇用日期（out举例）
CREATE OR REPLACE PROCEDURE pro_query_hiredate
(var_empno empno%type,
var_hiredate out hiredate%type)
AS
BEGIN
SELECT hiredate INTO var_hiredate FROM emp WHERE empno = varempno;
END;
pro_query_hiredate(v_empno,v_hiredate)的调用过程：
DECLARE
v_empno empno%type
v_hiredate hiredate%type
BEGIN
v_empno:=&empno; //手动输入empno
pro_query_hiredate(v_empno,v_hiredate); //调用存储过程
dbms_output.put_line(‘编号为’||v_empno||’的员工雇佣日期是’|| to_char(v_hiredate,’yyyy-mm-dd’));
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘查无此人’);
END;
/
eg3.通过存储过程，交换两个数据的值（in out举例）
DECLARE OR REPLACE PROCEDURE pro_swap
(var_num1 IN OUT NUMBER,
var_num2 IN OUT NUMBER)
AS
temp number;
BEGIN
temp:=var_num1;
var_num1:=var_num2;
var_num2:=temp;
END;
pro_swap(var_num1,var_num2)的调用过程：
DECLARE
var_num1 number;
var_num2 number;
BEGIN
var_num1:=&var_num1;
var_num2:=&var_num2;
pro_swap(var_num1,var_num2);
dbms_output.put_line(‘new_num1值为’||v_num1||’;new_num2值为’||v_num2);
END;
/
函数
创建函数基本语法：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN|OUT|IN OUT] type [,…])]
RETURN type //函数的返回类型 不可缺少！！！
{IS|AS}
[variable type [,…]]
BEGIN
function_body
END function_name;
删除函数语法：
DROP FUNCTION function_name;
eg4.创建一个函数，以雇员编号查询雇员薪水
CREATE OR REPLACE FUNCTION ft_query_sal(var_empno empno%type)
RETURN NUMBER AS
//函数的返回类型是NUMBER
var_sal NUMBER;
BEGIN
SELECT sal
INTO var_sal
FROM emp WHERE empno=var_empno;
RETURN var_sal;
END;
函数ft_query_sal(v_empno)的调用过程：
DECLARE
v_empno empno%type;
v_sal sal%type;
BEGIN
v_empno:=&empno;
–使用声明变量接收函数返回值
v_sal:=ft_query_sal(v_empno);
dbms_output.put_line(‘编号为’||v_empno||’的员工薪水是’||To_char(v_sal));
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘查无此人’);
END;
/
包 ———-包是由存储在一起的相关对象组成的PL/SQL结构。用于逻辑组合相关的自定义类型、变量、游标、过程和函数。
——–包分为包头(包规范)和包体，缺一不可。
包规范基本语法：
CREATE [OR REPLACE] PACKAGE package_name
{IS|AS}
公用类型或变量常量的声明；
公用过程或函数的声明；
END package_name;
包主体基本语法：
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS|AS}
私有类型或变量常量的声明；
公有过程或函数的声明；
END package_name;
调用包中的函数和过程时，应该调用中使用包名：
语法：package_name.type_name;
eg5.将过程pro_count_dept、函数ft_query_sal放入包pa_mypack中，以便对代码进行管理和调用，如何实现？
1. 包规范的书写（类似于C语言中的.h文件）
CREATE OR REPLACE PACKAGE pa_mypack
AS
PROCEDURE pro_count_dept(var_deptno deptno%type);
FUNCTION ft_query_sal(var empno empno%type) RETURN NUMBER;
END pa_mypack;
2. 包体的书写（类似于C语言中的.c文件）
CREATE OR REPLACE PACKAGE BODY pa_mypack
AS
— 实现过程pro_count_dept
PROCEDURE pro_count_dept(var_deptno emp.deptno%type) AS var_count NUMBER;
BEGIN
SELECT count(*) INTO var_count FROM emp WHERE deptno = var_deptno;
dbms_output.put_line(‘部门号’||var_deptno||’的员工人数是’||var_count);
EXCEPTION
WHEN NO_DATA_FOUND then dbms_output.put_line(‘没有数据’);
END;
— 实现函数ft_query_sal
FUNCTION ft_query_sal(var_empno empno%type) RETURN NUMBER
AS var_sal number;
BEGIN
SELECT sal INTO var_sal FROM emp WHERE empno=var_empno;
RETURN var_sal;
END;
END pa_mypack;
调用pa_mypack：
DECLARE
v_empno emp.empno%type;
v_sal emp.sal%type;
v_deptno emp.deptno%type;
BEGIN
v_empno:=&empno;
v_deptno:=&deptno;
pro_count_dept(v_deptno);
v_sal:=
pa_mypack.ft_query_sal(v_empno);
dbms_output.put_line(‘编号为’||v_empno||’的员工薪水是’||To_char(v_sal));
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘无此人薪水信息’);
END;
/
查看过程，函数和包的有关信息：
DESC user_procedures;
SELECT * FROM user_procedure WHERE object_name = ‘PRO_QUERY_HIREDATE’;
查看过程或函数代码：
SELECT text FROM user_source WHERE name=’PRO_COUNT_DEPT’;
查看包代码：
SELECT text FROM user_source WHERE name=’PA_MYPACK’ AND type=’PACKAGE’;
触发器 —–是一种特殊的过程。当特定的事件发生时，触发器被自动执行。
建立DML触发器的基本语法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} trigger_event //trigger_event 有 insert、update、delete
ON table_name //table_name 就是被操作的那个表的名字
[REFERENCING [OLD AS old_name][NEW AS new_name]]
[WHEN trigger_condition]
[FOR EACH ROW]
[DECLARE]
BEGIN
trigger_body
END trigger_name;
eg6.记录下所有更改newemp表的用户名，操作类型和更改时间。
CREATE OR REPLACE TRIGGER tr_iud_newemp
BEFORE insert or update or delete
——插入、更新、删除前调用
ON newemp;
——-对newemp进行操作的时候
DECLARE
v_operate scott.log_iud_newemp.operate_name%type;
BEGIN
IF inserting then
v_operate:=’insert’;
END IF;
IF updating then
v_operate:=’update’;
END IF;
IF deleting then
v_operate:=’delete’;
END IF;
INSERT INTO log_iud_newemp VALUES (user,to_char(sysdate,’YYYY-MM-DD HH-MI-SS’),v_operate);
END;
/
触发器的禁用、启用与删除：
ALTER
TRIGGER my_trig
{DISABLE|ENABLE}
; //禁用/启用单个触发器
ALTER
TABLE trig_table
{DISABLE|ENABLE}
ALL TRIGGERS; //禁用/启用trig_table表的所有触发器
DROP
TRIGGER my_trig; //删除
查看触发器信息：
SELECT trigger_name,table_name FROM user_triggers;
行级触发器：
执行DML语句时，每操作一行，触发器就执行一次。
行级触发器使用WHEN条件，用于控制触发器的执行。
For each row
行级触发器引入两个标识符：:old和:new
:old表示原有的数据 :new表示新修改的数据
eg7.创建一个行级触发器，为表的主键列生成自增序列号
1、生成一个表。
CREATE TABLE employee(id number(10) primary key,name varchar2(20));
2、生成一个序列。
CREATE SEQUENCE id_seq;
3、创建行级触发器。
CREATE OR REPLACE TRIGGER tr_insert_employee_for_pk
BEFORE insert
on employee
for each row
BEGIN
SELECT id_seq.nextval into :new.id FROM dual;
END;
instead of 触发器：只能定义在视图上，用于执行一个替代操作来代替对视图的操作，对视图操作最终会转换为基本表的操作。
instead of 触发器必须是行级触发器。
创建基本语法：
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF trigger_event
ON view_name
[REFERENCING [OLD AS old_name][NEW AS new_name]]
[WHEN trigger_condition]
[FOR EACH ROW]
[DECLARE]
BEGIN
trigger_body
END trigger_name;
eg8.Instead of 触发器更新一个视图
–创建instead of触发器
CREATE OR REPLACE TRIGGER tr_ins_view_newemp_newdept
INSTEAD OF insert ON view_newemp_newdept
FOR EACH ROW
DECLARE
rowcat number;
BEGIN
SELECT count(*) into rowcat FROM newdept WHERE
deptno=:new.deptno;
IF rowcat=0 THEN
begin
INSERT INTO newdept(deptno) VALUES (:new.deptno);
INSERT INTO newemp (empno,ename,job,deptno) VALUES
(:new.empno,:new.ename,:new.job,:new.deptno);
end;
ELSE
INSERT INTO newemp (empno,ename,job,deptno)VALUES(:new.empno,:new.ename,:new.job,:new.deptno);
END IF;
END;
/
系统触发器：
系统触发器的激活基于两种不同的事件：
DDL事件： CREATE、 ALTER、 DROP、TRUNCATE，…
数据库事件：数据库服务器的启动/关闭事件、用户的登录/断开事件、服务器错误
显示错误:查看出现的错误,可以使用SHOW ERRORS命令，这只适用于当前只编译了一个存储过程的情况。
SHOW ERRORS
在编译了多个存储过程或函数的情况下，可以把SHOW ERRORS更为细化:
SHOW ERRORS PROCEDURE proc_name; //存储过程
SHOW ERRORS FUNCTION my_func; //函数
SHOW ERRORS TRIGGER my_trig; //触发器
SHOW ERRORS PACKAGE my_pack //包