oracle-plsql理解和操作

1、plsql的概念

PL/SQL(Procedural Language/SQL)是一种过程化语言，属于第三代语言，它与C、C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中，使PL/SQL成为一个功能强大的事务处理语言。

2、plsql的分类

块

• 无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。
• 命名块（named）：是带有名称的匿名块，这个名称就是标签。
• 子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可在其它程序中调用它们。

存储过程

存储过程(Stored Procedure)是在大型数据库系统中， 一组为了完成特定功能的SQL 语句集，存储在数据库中， 经过第一次编译后再次调用不需要再次编译，用户通过 指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

触发器

•  触发器 （Trigger）：当数据库发生操作时，会触发一 些事件，从而自动执行相应的程序。

程序包

• 程序包（package）：存储在数据库中的一组子程序、 变量定义。在包中的子程序可以被其它程序包或子程序调用。

3、plsql的规范

标识符

• 标识符名不能超过30字符；
• 第一个字符必须为字母；
• 不区分大小写；
• 不能用’-‘(减号);
• 不能是SQL保留字。

4、plsql的操作

4.1 块操作

格式：

例子：有两张表（课程和选课记录表），要求删除课程和对应课程记录

级联删除课程，要求删除课程后，将本门课程的选课记录也要
删除，并在控制台输出通知。
declare 
v_cid_old NUMBER(10):=101;
begin
delete from cselect where cid=v_cid_old;
update course set cid_flag=0 where cid=v_cid_old;
delete from course where cid=v_cid_old;
commit;
DBMS_OUTPUT.PUT_LINE('已成功'||v_cid_old||'课程删除');
end;
/

4.2 存储过程

（注：在这个基础上是触发器，函数，程序包）

格式：

create or replace procedure 过程名

（过程参数1 类型（长度） .........过程参数n）

 is

变量定义

 begin sql语句

 end;/

调用：

• exec add_my_dept(14, 'NNN' , 'NNN')
• call add_my_dept(17, 'NNN2' , 'NNN2')

存储，级联删除课程，要求删除课程后，将本门课程的选课记录也要
删除，并在控制台输出通知。create or replace procedure
pro_cs(cid_old number)
is
begin
delete from cselect where cid=cid_old;
update course set cid_flag=0 where cid=cid_old;
delete from course where cid=cid_old;
commit;
DBMS_OUTPUT.PUT_LINE('已成功'||cid_old||'课程删除');
end;
/

--自定义函数（输出名字的时候，中间用星号表示）create or replace function strx(str in varchar2)
return varchar2
is
ret varchar2(20);
beginret:=SUBSTR(str,1, 1) || RPAD('*', LENGTH(str)-2, '*') || substr(str,-1,1);dbms_output.put_line(ret);
return ret;
end;
/
--调用
select strx(ename) from emp;

create table backup_emp as select * from emp  where 1=2;
删除的数据，保存在backup_emp中
create or replace trigger backup_emp
before delete
on O3.emp
for each row
begininsert into backup_emp(deptno,ename,job,mgr,HIREDATE)values(:old.deptno,:old.ENAME,:old.job,:old.mgr,sysdate); 
end;
/ 

--系统触发器
创建备份表
create table log_event
(username varchar2(10),address varchar2(20),logon_date timestamp,logoff_date timestamp
);创建登入触发器
create or replace trigger tr_logon
after logon on database
begininsert into log_event(username,address,logon_date)values (ora_login_user,ora_client_ip_address,systimestamp);
end;
/

--视图触发器create view view_emp_dept as(
select d.deptno,d.dname,e.empno,e.ename  
from dept d,emp e 
where d.deptno=e.deptno
);创建视图触发器
create or replace trigger tr_view
instead of insert on view_emp_dept
for each row
begininsert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
end;
/

1. 创建包规范
sql
CREATE OR REPLACE PACKAGE package_name
AS-- 公共变量、常量声明-- 存储过程声明PROCEDURE procedure_name(parameters);-- 函数声明FUNCTION function_name(parameters) RETURN datatype;
END package_name;
2. 创建包体
sql
CREATE OR REPLACE PACKAGE BODY package_name
AS-- 私有变量、常量声明-- 存储过程实现PROCEDURE procedure_name(parameters) ISBEGIN-- 实现代码END procedure_name;-- 函数实现FUNCTION function_name(parameters) RETURN datatype ISBEGIN-- 实现代码END function_name;END package_name;

1. 建包引用
create or replace package my_package
as
procedure add_my_dept2(V_deptno
number,V_dname varchar2,V_loc varchar2);
procedure del_my_dept3(V_deptno number);
end;2. 建包体
create or replace package body my_package
as
procedure add_my_dept2(V_deptno number,V_dname varchar2,V_loc varchar2)
is
begin
insert into my_dept values(V_deptno,V_dname,V_loc);
commit;
end add_my_dept2;---结束add_my_dept2procedure del_my_dept3(V_deptno number)
is
begin
delete from my_dept where deptno= V_deptno;
commit;
end del_my_dept3;---结束del_my_dept3
end;
/