Oracle PL/SQL

来源：互联网发布：防身折叠式铁扇子淘宝编辑：程序博客网时间：2024/06/06 14:06

PL/SQL
oracle 方言
模块 block

结构
  声明 declare
  可执行 begin
  异常处理 exception
  结束 end

  declare
  begin
   ;
  end;
  /

  begin
   sasdas;
   sada;
   s;
  end;
  /

  dbms_output.put_line('asd');
        .put();
        .new_line;

三种打印的方式  set serveroutput on/off
  execute dbms_output.put_line('asd');
  call dbms_output.put_line('asd');
  begin
   dbms_output.put_line('asd');
  end;
  /

  数据类型
   binary_integer 整型二进制
   boolean true/false

  运算符
        java        plsql
        +-*/ ><        +-*/ ><
   比较     ==         =
   赋值     =         :=
         " "        ' '
        //          --
        !=          !=   <>
        %          mod(num1,num2)
                  and or not

变量
  declare
   v_num number(7):=18;
   v_name varchar2(20);
  begin
   v_name:='zhangsan';
   dbms_output.put_line(v_name||'   '||v_num);
  end;
  /

  create table testA(
  id number,
  name varchar2(20)
  );
  create table testB(
  id number,
  name varchar2(20)
  );
  insert into testA values(1,'handson1');
  insert into testA values(2,'handson2');
  insert into testA values(3,'handson3');

  declare
   v_id number;
   v_name varchar2(20);
  begin
   select id,name into v_id,v_name from testA where id=2;
   v_name:=v_name||'test';
   insert into testB(id,name) values(v_id,v_name);
   commit;
  end;
  /


  jdbc    1 select
     2 insert
     3 delete

     1 --> plsql{123}

  复杂数据类型
   %type   数据库中已经存在的表中期中一个字段的数据
       类型作为变量数据类型
   %rowtype

  declare
   v_id testA.id%type;
   v_name testA.name%type;
  begin
   select id,name into v_id,v_name from testA where id=2;
   v_name:=v_name||'test';
   insert into testB(id,name) values(v_id,v_name);
   commit;
  end;
  /

  declare
   v_test testA%rowtype;
  begin
   select * into v_test from testA where id=2;
   v_test.name:=v_test.name||'test';
   insert into testB(id,name) values(v_test.id,v_test.name);
   commit;
  end;
  /

判断循环
  if boolean then
   xxxxxxx;
  end if;

  if boolean then
   xxxx;
  else
   xxxxxx;
  end if;

  if boolean then
   xxxx;
  elsif boolean then
   xxx;
  else
   xxx;
  end if;


  declare
   v_num number:=&num;
  begin
   if v_num<=10 then
    dbms_output.put_line(v_num||'<10');
   elsif v_num<=20 then
    dbms_output.put_line(v_num||'<20');
   else
    dbms_output.put_line(v_num||'sorry');
   end if;
  end;
  /

循环
  loop

  declare
   v_test testA%rowtype;
   v_num number:=0;
  begin
   loop
    v_num:=v_num+1;--别忘了加自变量
    exit when v_num>3;
    select * into v_test from testA where id=v_num;
    dbms_output.put_line(v_test.id||' '||v_test.name);
   end loop;
  end;
  /

  while

  declare
   v_test testA%rowtype;
   v_num number:=0;
  begin
   while v_num<3 loop
    v_num:=v_num+1;
    select * into v_test from testA where id=v_num;
    dbms_output.put_line(v_test.id||' '||v_test.name);
   end loop;
  end;
  /

  for

  declare
   v_test testA%rowtype;
  begin
   for v_n in reverse 1..3 loop
    select * into v_test from testA where id=v_n;
    dbms_output.put_line(v_test.id||' '||v_test.name);
   end loop;
  end;
  /