PL/SQL块的组成
pl/sql语言以块为单位,块中可嵌套子块
一个基本的pl/sql由三部分组成
定义部分(declare)
可执行部分(begin)
异常处理部分(exception)
declare
v_deptno emp.deptno%type;
v_ename emp.ename%type;
v_empno number(4) not null:7369;
begin
select deptno,ename
into v_deptno,v_ename
from emp
where empno=v_empno;
dbms_output.put_line(v_ename++'is'||to_char(v_deptno)||'department');
end;
在pl/sql中操作数据
插入数据
begin
insert into emp(empno,ename,job,mgr,hredate,sal)
values(1000,'ljs','manager',1100,sysdate,4000);
end;
更新数据
declare
v_sal emp.sal%type:=800;
begin
update emp
set sal=sal+v_sal
where job='analyst';
end;
删除数据
declare
v_deptno emp.deptno%type:=10;
begin
delete from emp
where deptno=v_deptno;
end;
事务控制
begin
insert into temp(x,y) values(1,'aaa');
savepoint a;
insert into temp(2,'bbb');
savepoint b;
rollback to savepoint a;
commit;
end;
存储过程
命名的pl/sql块
能够接收参数
能够被重复调用
用于执行某项操作
存储在数据库中
IN参数事例
create or replace procedure rise_salary(p_id in emp.empno%type)
is
begin
update emp
set sal=sal*1.10
where empno=p_id;
end raise_salary;
/
exec raise_salary(7369);
OUT参数事例
create or replace procedure raise)sal
(p_ id emp.empno%type,
p_name out varchar2,
p_sal out number)
is
begin
select sal,ename into p_sal,p)name from emp
where empno=p_id;
end raise_sal;
declare
v_sal emp.sal%type;
v_name emp.ename%type;
begin
raise_sal(7369,v_sal,v_name);
dbms_output.put_line('雇员:''||v_name||'的薪水是'||v_sal);
end;