oracle_PL_SQL

SQL语言中无分支、循环，PL（ProcedureLanguage）中有分支、循环

 

--设置环境变量，dbms_output.put_line输出信息显示在屏幕上

set serveroutput on;

 

--最简单的语句块(执行：回车/回车)

begin

       dbms_output.put_line('Hello World!');

end;

 

--简单的PL/SQL语句块

declare

  v_name varchar2(20);

begin

  v_name := 'zheng';

  dbms_output.put_line(v_name);

end;

 

---语句块的组成

declare

  v_num number := 0;

begin

  v_num := 2/v_num;

  dbms_output.put_line(v_num);

exception

  when others then

    dbms_output.put_line('error');

end;

 

变量声明的规则：

    ☆变量名不能使用关键字,如from

    ☆第一个字符必须是字母

    ☆变量名最多包含30个字符

    ☆不要与数据库的表或者列同名

    ☆每一行只能声明一个变量

变量的类型：

    ☆binary_integer 证书，主要用来计数而不是用来表示字段类型

    ☆number 数字类型

    ☆char 定长字符串

    ☆varchar2 变长字符串

    ☆date 日期

    ☆long: 长字符串，最长2GB

    ☆boolean 布尔类型，可取值true、false、null，其值不能直接输出，可在判断语句中用

 

--变量声明

declare

  v_temp number(1);

  v_count binary_integer := 0;

  v_sal number(7,2) := 4231.50;

  v_date date := sysdate;

  v_pi constant number(3,2) := 3.14;

  v_valid boolean := false;

  v_name varchar2(10) not null := 'Myname';

begin

  dbms_output.put_line('v_count value:' || v_count);

  dbms_output.put_line('v_name value: ' ||v_name);

end;

 

--变量声明，使用%type属性

declare

  v_empno number(4);

  v_empno2 emp.empno%type;

  v_empno3 v_empno2%type;

begin

  dbms_output.put_line('Test');

end;

 

复合变量：Table、Record

--Table变量类型,相当于java中的数组

declare

  type type_table_emp_empno is table ofemp.empno%type index by binary_integer;

  v_empnos type_table_emp_empno;

begin

  v_empnos(0) := 7369;

  v_empnos(2) := 7839;

  v_empnos(-1) := 9999;

  dbms_output.put_line(v_empnos(-1));

end;

--Record变量类型，相当于java中的类

declare

  type type_record_dept is record

    (

        deptno dept.deptno%type,

      dname dept.dname%type,

        loc dept.loc%type

    );    

  v_temp type_record_dept;

begin

  v_temp.deptno := 50;

  v_temp.dname := 'Tesing';

  v_temp.loc := 'beijing';

  dbms_output.put_line(v_temp.deptno || ' ' ||v_temp.dname || ' ' || v_temp.loc);

end;

--使用%rowtype声明record变量

declare

  v_temp dept%rowtype;

begin

  v_temp.deptno := 50;

  v_temp.dname := 'Tesing';

  v_temp.loc := 'beijing';

  dbms_output.put_line(v_temp.deptno || ' ' ||v_temp.dname || ' ' || v_temp.loc);

end;

--SQL语句的运用

--select语句必须有into，且必须返回一条记录

declare

  v_ename emp.ename%type;

  v_sal emp.sal%type;

begin

  select ename,sal into v_ename, v_sal from empwhere empno = 9999;

  dbms_output.put_line(v_ename || ' ' ||v_sal);

end;

declare

  v_temp dept%rowtype;

begin

  select * into v_temp from dept where deptno =10;

  dbms_output.put_line(v_temp.deptno ||v_temp.dname);

end;

--insert

declare

  v_deptno dept.deptno%type := 50;

  v_dname dept.dname%type := 'Testing';

  v_loc dept.loc%type := 'Beijing';

begin

  insert into dept values(v_deptno, v_dname,v_loc);

  commit;

end;

--update(sql%rowcount表示刚执行的SQL语句影响的记录数)

declare

  v_loc dept.loc%type := 'Nanyang';

  v_count number;

begin

  update dept set loc = v_loc where deptno =50;

  select count(*) into v_count from dept;

  dbms_output.put_line(sql%rowcount || '条记录被影响');

  commit;

end;

--delete

declare

  v_deptno dept.deptno%type := 50;

begin

  delete from dept where deptno = v_deptno;

  commit;

end;

--PL/SQL执行DDL语句

begin

  execute immediate 'create table T(namevarchar2(20) default ''aaa'')';

end;

--if语句

declare

  v_sal emp.sal%type;

begin

  select sal into v_sal from emp where empno =7369;

  if(v_sal < 1200) then

    dbms_output.put_line('low');

  elsif(v_sal < 2000) then

    dbms_output.put_line('middle');

  else

    dbms_output.put_line('high');

  end if;

end;

--循环

declare

  i binary_integer := 1;

begin

  loop

    dbms_output.put_line(i);

       i := i + 1;

       exit when(i >= 11);

  end loop;

end;

declare

  j binary_integer := 1;

begin

  while j < 11 loop

    dbms_output.put_line(j);

       j := j + 1;

       end loop;

end;

begin

  for k in 1..10 loop

    dbms_output.put_line(k);

  end loop;

  for k in reverse 1..10 loop

    dbms_output.put_line(k);

  end loop;

end;

--错误处理

declare

  v_temp number(4);

begin

  select empno into v_temp from emp wheredeptno = 100;

exception

  when too_many_rows then

    dbms_output.put_line('太多记录了');

  when no_data_found then

    dbms_output.put_line('没数据');

  when others then

    dbms_output.put_line('error');

end;

--SQLCODE异常代码 SQLERRM错误信息

select * from dept;

create table errorlog

(

  id number primary key,

  errcode number,

  errmsg varchar2(1024),

  errdate date

);

create sequence seqstart with 1 increment by 1;

declare

  v_deptno dept.deptno%type := 10;

  v_errcode number;

  v_errmsg varchar2(1024);

begin

  delete from dept where deptno = v_deptno;

exception

  when others then

    rollback;

       v_errcode := SQLCODE;

       v_errmsg := SQLERRM;

       insert into errorlogvalues(seq.nextval,v_errcode,v_errmsg,sysdate);

       commit;

end;

--游标 指向结果集的一个指针 isopen notfound found rowcount

declare

  cursor c is

    select * from emp;

  v_emp c%rowtype;

  --或者v_empemp%rowtype;

begin

  open c;

  loop

    fetch c into v_emp;

       exit when(c%notfound);

    dbms_output.put_line(v_emp.ename);

  end loop;

  close c;

end;

--for自动打开游标，结束后自动关闭游标

declare

  cursor c is

    select * from emp;

begin

  for v_emp in c loop

    dbms_output.put_line(v_emp.ename);

  end loop;

end;

--带参数的游标

declare

  cursor c(v_deptno emp.deptno%type, v_jobemp.job%type)

  is select ename,sal from emp where deptno =v_deptno and job = v_job;

begin

  for v_temp in c(30,'CLERK') loop

    dbms_output.put_line(v_temp.ename);

  end loop;

end;

--可更新的游标

declare

  cursor c

  is select * from emp2 for update;

begin

  for v_temp in c loop

    if(v_temp.sal < 2000) then

        update emp2 set sal = 999 where current of c;

       elsif(v_temp.sal = 5000) then

        delete from emp2 where current of c;

       end if;

  end loop;

  commit;

end;