oracle笔记 (PL-SQL)

PL_sql( 面向过程的语言)
oracle 内部编写的存储过程;

---------------
--pl sql注释
1. "--" 单行
2. "/**/"多行

--定位到doc窗口输出
set serveroutput on;

[/code]
[code="SQL"]
---------------
begin
  dbms_output.put_line('HelloWorld!');
end;
/
-----------------
-----------------
begin
  dbms_output.put_line('HelloWorld!');
end;
/ 
-----------------
declare
  v_name varchar2(20);
begin 
  v_name :='myname';
  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;
------------------

[color=black][size=large][b]--变量声明规则[/b][/size][/color]
1. 变量名不能够使用保留字， 如from,select 等
2. 第一个字符必须是字母
3. 变量名最多包含30个字符
4. 不傲宇数据库的表或者列同名
5. 每一行只能声明一个变量


[b][size=large][color=black]--常用变量类型[/color][/size][/b]


1.binary_integer:整数，主要用来计数而不是用来表示字段类型
2.number:数字类型
3.char:定长字符串
4.varchar2:变长字符串
5.date:日期
6.long:长字符串，最长2GB
7.boolean:布尔类型，可以去为true、false和null值
当使用boolean的时候给他一个初始值 因为不给初始值时为null，运算会影响结果

[b][/b][color=black]
--变量声明[/color][size=large][/size]

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

--变量声明，使用%type属性 变量类型跟%type变
declare
  v_empno number(4);
  v_empno2 emp.empno%type;
  v_empno3 v_empno2%type;
begin
  dbms_output.put_line('Test');
end;

--复合变量 2种 table相当java array 、Record变量类型相当java 的类

--Table变量类型
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;

--Record变量类型
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;

--使用%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;

=========================================================================================
[b][/b][color=black]
pl sql使用sql
[/color][size=large][/size]

pl sql使用select必须有返回一个记录/有且只有返回一条记录
select必须有一个into

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

declare
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 7369;
  dbms_output.put_line(v_emp.ename);
end;
---------------------
pl sql: insert


declare
  v_deptno dept.deptno%type := 50;
  v_dname dept.dname%type := 'aaaa';
  v_loc dept.loc%type := 'bj';
begin 
  insert into dept2 values(v_deptno, v_dname, v_loc);
  commit;
end;
----------------------

pl sql: update

declare
  v_deptno emp2.deptno%type:=10;
  v_count number;
begin
  --update emp2 set sal = sal/2 where deptno = v_deptno;
  --select deptno into v_deptno from emp2 where empno =7369;
  select count(*) into v_count from emp2;
  --sql表示刚刚执行完的这条sql语句
  --rowcount 代表刚刚执行完sql影响的记录数
  dbms_output.put_line(sql%rowcount ||'条记录被影响');
  commit;
end;
------------------------


PLSQL 执行DDL
begin
  execute immediate 'create table T (nnn varchar2(20) default ''aa'')';
end;

drop table t;

-----------------------


[size=large][b][color=black]pl sql 控制结构(分支，判断，循环)[/color][/b][/size]
--pl sql 控制结构if语句
--取出7369的薪水，如果<1200,则输出'low',如果<2000则输出'imddle',否则'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('low');
  else
     dbms_output.put_line('high'); 
  end if;
end;

--pl sql 控制结构循环语句

--相当于java 的do{}while循环

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

--相当于java 的while循环

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

--相当于java 的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;

[size=large][color=black]
[b]--------异常处理----------[/b][/color][/size]

declare
  v_temp number(4);
begin
  select empno into v_temp from emp where deptno=10;
  --select empno into v_temp from emp where deptno=2222;
  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('errer');
end;

-- ORACLE数据库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;
    v_errmsg := SQLERRM;
    insert into errorlog values(seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
    commit;
end;