PLSQL基础教程-优快云博客

PLSQL:
sql:
select ename from emp where sal=(select max(sal) from emp);
plsql:
declare
v_max number;
v_name varchar2(10);
begin
select max(sal) into v_max from emp;
select ename into v_name from emp where sal=v_max;
dbms_output.put_line(v_name);
end;

注释：
-- 表示注释一行
/* */ 表示注释多行

sql:每执行一次都要进行编译
plsql：只需编译一次，以后执行只需调用
1.匿名的plsql
declare 声明
vari,constant,type 变量，常量，类型的定义
begin
sql...
plsql...
cursour..
if..then.. case.. for.. while.. loop..
exception.. 异常处理
end
2.有名的plsql
create or replace
is|as
begin
sql..
plsql..
...
end|end ;

1.变量的定义
V NUMBER；
V NUMBER:=2;
NUMBER()：数值型 integer：整型
char()：固定字符长度，在sql中最长2000，在plsql中可以达到32767
varchar2():可变长字符，最大可达到32767
lob：存储字符或二进制，最大可达到4GB，图像声音等都可以
clob：字符型
blob：二进制
nclob：国际语言字符类型，可以存储任何一个国家的语言
boolen：true,false
%type：用来匹配与表里面数据类型相同的字段
%rowtype：匹配表里面所有的字段
date：日期

SQL> set serveroutput on 打开输出

declare
v1 number:=1;
v2 number:=2;
begin
dbms_output.put_line('v1+v2='||(v1+v2));
end;
/

declare
ename varchar2(13);
begin
ename:=initcap('uplooking');
dbms_output.put_line(upper(ename)||'---'||substr(ename,3,4));
end;
/

declare
dd date;
begin
dd:=to_date('2010-09-03','yyyy-mm-dd');
dbms_output.put_line(floor(sysdate-dd));
dbms_output.put_line(floor(months_between(sysdate,dd)));
end;
/
中文环境：export NLS_LANG='simplified chinese_china.al32utf8'

常量
declare
v constant number:=4;
begin
dbms_output.put_line(v);
end;
/

7566员工的薪水
declare
v_sal number(7,2);
v_ename varchar2(10);
begin
select ename,sal into v_ename,v_sal from emp where empno=7566;
dbms_output.put_line(v_ename||' '||v_sal);
end;
/

declare
vename emp%rowtype;
begin
select * into vename from emp where empno=7566;
dbms_output.put_line(vename.empno||' '||vename.ename||' '||vename.job||' '||vename.sal);
end;
/

declare
vename emp.ename%type;
begin
select ename into vename from emp where empno=7566;
dbms_output.put_line(vename);
end;
/

select empno,job,sal,grade into v_empno,v_job,v_sal,v_level from emp join salgrade on (sal between losal and hisal) where empno=7566;

显示dept表的所有信息
使用循环找出多行
begin
for v_dept in (select * from dept) loop
dbms_output.put_line(v_dept.deptno||' '||lpad(v_dept.dname,15)||' '||v_dept.loc);
end loop;
end;

使用游标
declare
cursor c1 is select * from dept;
begin
for i in c1 loop
dbms_output.put_line(i.deptno||' '||lpad(i.dname,15)||' '||i.loc);
end loop;
end;

插入
declare
v_deptno dept1.deptno%type:=50;
v_dname dept1.dname%type:='what';
v_loc dept1.loc%type:='where';
begin
insert into dept1 values (v_deptno,v_dname,v_loc);
end;

begin
insert into dept1 select * from dept where deptno=10;
delete from dept1 where deptno=60;
dbms_output.put_line(sql%rowcount||' rows delete');
commit;
if sql%notfound then
dbms_output.put_line('not found');
end if;
end;

游标属性
sql%isopen:打开游标 bool
sql%rowcount:报告删除更新的列数
sql%found:列存在 bool
sql%notfound:列不存在 bool

2.条件判断
(1)if <> then
...
end if;
(2)if <> then
...
else
...
end if;
(3)if <> then
...
elsif <>
...
else
...
end if;

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7566;
if v_sal>2000 then
dbms_output.put_line(v_sal||'xiuxi');
elsif v_sal<2000 then
dbms_output.put_line(v_sal||'jiayou');
end if;
end;

case
case a case
when b then when then
when c then when then
end case; end case;

begin
for v_emp in (select * from emp) loop
case v_emp.deptno
when 10 then
dbms_output.put_line(v_emp.empno||' sal='||(v_emp.sal+100));
when 20 then
dbms_output.put_line(v_emp.empno||' sal='||(v_emp.sal+200));
when 30 then
dbms_output.put_line(v_emp.empno||' sal='||(v_emp.sal+300));
end case;
end loop;
end;

循环：
三大类循环
简单循环 loop...end loop; 遇到exit，exit when...时退出循环
while循环
while..loop
...
end loop;
for 循环
for i in <> loop
...
end loop;
loop：要指定退出条件
declare
v1 number(2):=0;
begin
loop
v1:=v1+1;
if v1>9 then exit when v1>9;
exit;
end if;
dbms_output.put_line(v1);
end loop;
end;

while:
declare
v1 number(2):=0;
begin
while v1<9 loop
v1:=v1+1;
dbms_output.put_line(v1);
end loop;
end;

for:
begin
for i in 1..9 loop
dbms_output.put_line(i);
end loop;
end;

反过来输入
begin
for i in reverse 1..9 loop
dbms_output.put_line(i);
end loop;
end;

游标
在PL/SQL块中执行select,insert,delete和update语句时，oracle会在内存中为其分配上下文区(context area),即缓冲区，游标是指向该区的一个指针，或是命名一个工作区(work area)，或是一种结构化数据类型。它为应用等量奇观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方法。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

游标分为显示游标和隐式游标
cursor <> is 显示
DML 隐式游标
定义/声明一个游标，就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
cursor c1 is select ...

1.打开游标: 就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN
语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
open cursor_name;
2.提取数据
格式：
FETCH cursor_name INTO {variable_list | record_variable };

执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

3.对记录进行处理
4.继续处理，直到活动集合中没有记录
5.关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。
格式：
close cursor_name;

游标属性
c1%isopen:打开游标 bool
c1%rowcount:报告删除更新的列数
c1%found:列存在 bool
c1%notfound:列不存在 bool

declare
v_ename emp.ename%type;
cursor c1 is select ename from emp where job='CLERK';
begin
open c1;
loop
fetch c1 into v_ename;
exit when c1%notfound;
dbms_output.put_line(v_ename);
end loop;
close c1;
end;

两种方法提取多列
declare
cursor c1 is select ename,sal from emp where deptno=10 and sal>2000;
v_c1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v_c1;
exit when c1%notfound;
dbms_output.put_line(c1%rowcount||' '||v_c1.ename||' '||v_c1.sal);
end loop;
close c1;
end;

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
cursor c1 is select ename,sal from emp where deptno=10 and sal>2000;
v_c1 c1%rowtype;
begin
open c1;
fetch c1 into v_ename,v_sal;
dbms_output.put_line(v_ename||' '||v_sal);
close c1;
end;

for循环自动打开关闭游标自动提取数据
declare
cursor c1 is select ename,sal from emp where deptno=10;
begin
for i in c1 loop
dbms_output.put_line(rpad(i.ename,10)||lpad(i.sal,11));
end loop;
end;

for update: 用于更改或删除
select .. for update [nowait];
where current of c1; 表示当前游标对应的数据行

declare
cursor c1 is select * from emp1 for update nowait;
--cursor c1 is select * from emp1 for update of sal;
v_c1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v_c1;
exit when c1%notfound;
if v_c1.deptno=20 and v_c1.job='CLERK' then
update emp1 set sal=6000 where current of c1;
end if;
end loop;
commit;
close c1;
end;

begin中使用DDL: 即使用动态SQL
begin
execute immediate 'create table tt(id number)';
end;

DML，DQL不建议使用动态SQL
DQL中使用：
declare
v_ename emp.ename%type;
begin
execute immediate 'select ename from emp where empno=7566' into v_ename;
dbms_output.put_line(v_ename);
end;

declare
v_dname varchar2(10):='TESTING';
v_loc varchar2(10):='CHENGDU';
begin
--execute immediate 'insert into dept values(:1,:2,:3)' using 80,v_dname,v_loc;
execute immediate 'delete from dept where deptno=80';
dbms_output.put_line(sql%rowcount||' rows change');
end;

异常处理：
由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.
预定义错误 oracle定义好的异常，24个，不许要用户定义
非预定义错误即其它oracle标准错误，需要用户定义，由系统引发
自定义错误用户定义，需要用户抛出用户
异常处理：
EXCEPTION
WHEN first_exception THEN


     WHEN second_exception THEN  
     WHEN OTHERS THEN  
 END;

 desc dba_source;
 select text from dba_source where name='STANDART' and text like '%EXCEPTION_INIT%';


 使用预定义的异常
 declare
 vsal number;
 begin
 select sal into vsal from emp1 where empno=50;
 dbms_output.put_line(vsal);
 exception
 when no_data_found  then
 dbms_output.put_line('no data found .');
 update emp1 set sal=9000 where deptno=10;
 end;


 使用非预定义的异常
 1. 在PL/SQL 块的定义部分定义异常情况：
  EXCEPTION;
 2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：PRAGMA EXCEPTION_INIT(, );
 3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

 declare
 v_deptno dept.deptno%type :=&deptno;
 deptno_remaing exception;
 pragma exception_init(deptno_remaing,-2292);
 begin
 delete from  dept where deptno=v_deptno;
 exception
 when deptno_remaing then
 dbms_output.put_line('wei fan le wei yi yue shu');
 when others then
 dbms_output.put_line(sqlcode||'  ---'||sqlerrm);
 end;


 用户自定义的异常
 定义异常，抛出异常，处理异常
 declare
 v1 number:=&1;
 begin
 if  length(v1) < 8 then
 raise_application_error(-20500,'length less than 8 .');
 else
 dbms_output.put_line(v1);
 end if;
 end;




 declare
 v_empno number :=&empno;
 no_result exception;
 begin
 update emp set sal=sal+100 where empno=v_empno;
 if sql%notfound then
 raise no_result;
 end if;
 exception
 when no_result then
 dbms_output.put_line('errou you data');
 when others then
 dbms_output.put_line(sqlcode||'---'||sqlerrm);
 end;




 ___________________________________________________________________
 循环练习
 declare
     cursor c1 is select ename,sal from emp where deptno=10;
     begin
 for i in c1 loop
     dbms_output.put_line(c1%rowcount||' '||i.ename||'  '||i.sal);
 end loop;
     end;


 declare
     cursor c1 is select ename,deptno,dname from emp natural join dept where job='CLERK';
 v_c1 c1%rowtype;
     begin
     open c1;
     fetch c1 into v_c1;
 while c1%found loop
     dbms_output.put_line(v_c1.ename||'  '||v_c1.deptno||'  '||v_c1.dname);
     fetch c1 into v_c1;
     end loop;
     close c1;
     end;
 /


 declare
     cursor c1 is select ename,deptno,dname,grade from emp natural join dept join salgrade on (sal between losal and hisal); 
     v_c1 c1%rowtype;
     begin
     open c1;
     fetch c1 into v_c1;
     while c1%found loop
     dbms_output.put_line(v_c1.ename||'  '||v_c1.deptno||'  '||v_c1.dname||'  '||v_c1.grade);
     fetch c1 into v_c1;
     end loop;
     close c1;
     end;
     /


     declare
     cursor c1 is select ename,deptno,dname,grade from emp natural join dept join salgrade on (sal between losal and hisal);
     v_c1 c1%rowtype;
     begin
     open c1;
     loop
     fetch c1 into v_c1;
 exit when c1%notfound;
     dbms_output.put_line(v_c1.ename||'  '||v_c1.deptno||'  '||v_c1.dname||'  '||v_c1.grade);
     end loop;
     close c1;
     end;
     /


 declare
     cursor c1 is select ename,deptno,dname,grade from emp natural join dept join salgrade on (sal between losal and hisal);
     begin
 for i in c1 loop
 dbms_output.put_line(i.ename||'  '||i.deptno||'  '||i.dname||'  '||i.grade);
     end loop;
 end;
     /


     declare
     v_ename emp.ename%type;
     begin
     execute immediate 'select ename from emp where empno=7566' into v_ename;
     dbms_output.put_line(v_ename);
     end;