Oracle PL_SQL语句总结

PL_SQL：procedure language，过程化SQL语言。

set serveroutput on;--在server端做输出，dbms_output.put_line('');
set serveroutput off;

最简单的语句块

begin
  dbms_output.put_line('HelloWorld!');--System.out.println();
end;
/
--HelloWorld!

简单的PL/SQL语句块

declare
  v_name varchar2(20);--声明变量
begin
  v_name := 'myname';--赋值
  dbms_output.put_line(v_name);
end;
/
--myname

语句块的组成

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;
/
--error

变量声明的规则
1.变量名不能够使用保留字，如from、select等
2.第一个字符必须是字母
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量

常用变量类型
1.binary_integer:整数，主要用来计数而不是用来表示字段类型,为了效率
2.number:数字类型（整数，小数）
3.char:定长字符串
4.varchar2:变长字符串
5.date:日期
6.long:长字符串，最长2GB
7.boolean:布尔类型，可以取值为true,false和null值

--变量声明

declare 
  v_temp number(1);
  v_count binary_integer := 0;
  v_sal number(7,2) := 4000.00;
  v_date date := sysdate;
  v_pi constant number(3,2) := 3.14;
  v_valid boolean := false;--null不能输出
  v_name varchar2(20) not null := 'MyName';
begin
  dbms_output.put_line('v_temp value:' || v_temp);
end;
/

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

declare
  v_empno number(4);
  v_empno2 emp.empno%type;--emp.empno的类型
  v_empno3 v_empno2%type;
begin
  dbms_output.put_line('Test');
end;
/

复合类型
--Table变量类型（类似Java里面的数组）

declare
  type type_table_emp_empno is table of emp.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;
/
--9999

--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 := 'aaaa';
  v_temp.loc := 'bj';
  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--50 aaaa

--使用%rowtype声明record变量

declare
  v_temp dept%rowtype;
begin
  v_temp.deptno := 50;
  v_temp.dname := 'aaaa';
  v_temp.loc := 'bj';
  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/

SQL语句的运用

declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  select ename,sal into v_ename,v_sal from emp where empno = 7369;--有且仅有一条
  dbms_output.put_line(v_ename || ' ' || v_sal);
end;
/
--SMITH 800

begin
  execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';
end;
/

if语句

--取出7369的薪水，如果<1200，则输出'low',如果<2000则输出'middle',否则'high'
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;
/
--low

循环
--do while

declare
  i binary_integer := 1;
begin
  loop
      dbms_output.put_line(i);
    i := i + 1;
    exit when(i >= 11);
  end loop;
end;
/*
1到10
*/

--while

declare
  j binary_integer := 1;
begin
  while j < 11 loop
      dbms_output.put_line(j);
    j := j + 1;
  end loop;
end;
/*
1到10
*/

--for

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;
/
--1到10，10到1

错误处理

declare
  v_temp number(4);
begin
  select empno into v_temp from emp where deptno = 10;
exception
  when too_many_rows then
    dbms_output.put_line('太多记录了');
  when others then
    dbms_output.put_line('error');
end;
/
--太多记录了

declare
  v_temp number(4);
begin
  select empno into v_temp from emp where empno = 2222;
exception
  when no_data_found then
    dbms_output.put_line('没数据');
end;
/
--没数据

 

错误日志记录，主要是DBA使用。

create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);

create sequence seq_errorlog_id start 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;
  commit;
exception
  when others then
    rollback;
    v_errcode := SQLCODE;--SQLCODE和SQLERRM是关键字
    v_errmsg := SQLERRM;
    insert into errorlog values(seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
    commit;
end;
/
--ORA-02292