plsql简单入门

一、PL/SQL简介

PL/SQL（Procedural Language/SQL）是甲骨文公司专有的SQL扩展语言，应用在甲骨文公司的Oracle数据库系统。一些的SQL数据库管理系统也提供了类似的扩展SQL语言。PL/SQL的的语法非常类似于Ada，而且像1980年代的Ada编译器一样，PL/SQL的运作系统使用Diana作为中介语言。

二、基本语法

测试表

2.1基本语法骨架

declare
	声明区
begin
	核心代码
end;

2.1.1 输出hello world

declare
	s varchar2(11) :='hello world';
begin
	dbms_output.put_line(s);
end;

2.1.2声明变量、赋值

1.声明格式： 变量名 数据类型及长度

i number(10)  := 10;
name varchar2(10) :=abc;
--根据emp表中的ename的数据类型来声明
name2 emp.ename%type;

2.sql赋值
||符号是字符连接符号

declare
	name emp.ename%type;
	msal emp.sal%type;
begin
	select ename,sal into name,msal from emp where empno=7369;
	dbms_output.put_line(name || '--' || msal);
end;

2.2流程控制

&inNum 读入一个数,名字随意

2.2.1 if判断

读入一个数，根据数值判断哪个年代

declare
	i number(2) :=&inNum;
begin
	if i<18 then 
		dbms_output.put_line('未成年');
	elsif i<40 then
		dbms_output.put_line('不惑之年');
	else
		dbms_output.put_line('老了老了');
	end if;
end;

2.2.2 while循环

打印1到10

declare
	i number(2) :=1;
begin
	while i<11 loop
		dbms_output.put_line(i);
		i := i+1;
	end loop;
end;

2.2.3 exit 循环

打印1到10

declare
	i number(2) :=1;
begin
    loop
    exit when i>10;
		dbms_output.put_line(i);
		i := i+1;
	end loop;
end;

2.2.3 for 循环

打印1到10

declare
begin
    for i in 1..10 loop
    	dbms_output.put_line(i);
    end loop;
end;

2.3 游标

可以记录多个对象，多行记录

declare 
	--无参游标
	cursor c1 is select * from emp;
	empRow emp%rowtype; --一行数据类型
	--带参游标
	cursor c2(dno emp.deptno%type) is select empno from emp where deptno=dno;
	eno emp.empno%type;
begin
	--无参遍历
	open c1;		
		loop
			fetch c1 into empRow;
			exit when c1%notfound;
			dbms_output.put_line(empRow.ename||'--'||empRow.sal);
		end loop;
	close c1;
	--带参遍历
	open c2(10);		
		loop
			fetch c2 into eno;
			exit when c2%notfound;
			dbms_output.put_line(eno);
		end loop;
	close c2;
end;

三、存储过程、函数

3.1存储过程

存储过程的就是提前编译好的一段pl/sql语言，放置在数据库中，可以直接被调用，这一段pl/sql一般都是固定步骤的业务
注意：参数和返回值的数据类型不能带长度

3.1.1 创建语法

create [or replace] procedure 过程名字（参数 in/out 数据类型）
as/is
begin 
	程序体
end;

3.1.2 例子

create or replace procedure findNameById(eno in emp.empno%type,sname out emp.ename%type)
is
begin 
	select ename into sname from emp where empno=eno;
end;

--调用
declare
	sname emp.ename%type;
begin
	findNameById(7782,sname);
	dbms_output.put_line(sname);
end;

3.2存储函数

3.2.1创建语法

create [or replace] function 函数名字（参数  数据类型） return 数据类型
as/is
begin 
	程序体
end;

3.2.2 例子

create or replace function findNameByEid(eno emp.empno%type) return emp.ename%type 
is
	sname emp.ename%type;
begin 
	select ename into sname from emp where empno=eno;
	return sname;
end;

--调用
declare
begin
	dbms_output.put_line(findNameByEid(7782));
end;

四、触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句
(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

4.1 作用及器类型

4.1.1 触发器可用于

• 数据确认
• 实施复杂的安全性检查
• 做审计，跟踪表上所做的数据操作等
• 数据的备份和同步

4.1.2 触发器的类型 :

语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器(FOR EACH ROW)：触发语句作用的每一条记录都被触发。在行级触 发器中使用old和new伪记录变量, 识别值的状态。

在触发器中触发语句与伪记录变量的值

4.2基本语法

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
declare
begin
PLSQL 块
End；

4.3 例子

实现主键自增长,user_k是自定义的序列，每次执行insert语句都会自动增长id了

create or replace trigger myAutoAdd
before
insert
on c##user
for each row
declare 
begin
	select user_k.nextval into :new.id from dual;
end;