oracle的PLSQL基础

1. PL/SQL
   1.1 PL/SQL中的类型
       a）标量类型(数字，字符，日期，布尔)
        类型名       SQL中的范围        PL/SQL中的范围
         char           1...2000             1...32767
         varchar2       1...4000             1...32767
         raw            1...2000             1...32767
         long           1...2G               1...32760
         long raw       1...2G               1...32760

       b）LOB（CLOB,BLOB,NCLOB,BFILE）
       c) %type, %rowtype

2. PL/SQL(块)语法

   declare
     -- 声明部分
   begin
      -- 可执行部分
   exception 
      -- 异常部分
   end;


   declare
      name varchar2(20) := '张三';
      age number;
   begin
     name := '&inputname';
     age := &inputage;

     dbms_output.put_line('Hello ' || age || '岁的' || name ||  '!!!');
     end;

3. 给变量赋值
   :=

   select ... into ... from ... where ...
   ----------------------------------------------------------------------
   declare
          name varchar2(20);
          job varchar2(20);
   begin
          select ename,job into name,job from emp where empno = 7788;

          dbms_output.put_line('name is ' || name || ',job is ' || job);
     end;

   -- 属性类型
   %type   %rowtype

   declare
        name emp.ename%type;  -- 和emp.ename的类型和长度一致
        a_row emp%rowtype;
   begin
        select ename into name from emp where empno = 7369;
        -- rowtype需要查询出*来赋值
        select * into a_row from emp where empno = 7788;

        dbms_output.put_line(name || 'hello world');
        dbms_output.put_line('name:'|| a_row.ename ||  ',job:' || a_row.job ||  ',sal:' || a_row.sal );
     end;

4. 分支
   4.1 if
       declare
          age number(8);
       begin
          age := &age;

          if age < 18 then
            dbms_output.put_line('小于18');
          elsif age < 30 then
            dbms_output.put_line('18到30');
          else
            dbms_output.put_line('大于30');
          end if;
       end;

    4.2 case
       -- 用法一: switch
        declare
           monthValue number;
        begin
           monthValue := &mv;
           case monthValue
             when 1 then
               dbms_output.put_line('1月');
             when 2 then
               dbms_output.put_line('2月');
             when 3 then
               dbms_output.put_line('3月');
             when 4 then
               dbms_output.put_line('4月');
             when 5 then
               dbms_output.put_line('5月');
             when 6 then
               dbms_output.put_line('6月');
             else
               dbms_output.put_line('其他月份');
           end case;
         end;

       -- 用法二: if else
         declare
            age number(8);
         begin
            age := &age;

            case
                when age < 18 then
                  dbms_output.put_line('小于18');
                when age < 30 then
                  dbms_output.put_line('18到30');
                else
                  dbms_output.put_line('大于30');
            end case;
         end;

5. 循环
   loop循环
   declare
     x number := 0;
   begin
     loop exit when x >= 100;
        x := x + 1;
        dbms_output.put_line('x=' || x);
        /*
        if x >= 100 then
          exit;
        end if;
        */
     end loop;
   end;

   while循环
   declare
     x number := 0;
   begin
     while x < 100 loop
       x := x + 1;
       dbms_output.put_line('x=' || x);
     end loop;
   end;


   for循环
   begin
     for i in 10..20 loop -- for的临时变量值可以不用在声明块中定义
       dbms_output.put_line('i=' || i);
     end loop;
   end;

   -- 九九乘法表
   begin
     for i in 1..9 loop
        for j in 1..i loop
          dbms_output.put('  ' || j || '*' || i || '=' || (i*j));
        end loop;
        dbms_output.put_line('');
     end loop;
   end;

6. 顺序控制
   goto 无条件跳转
   null 空语句

7. 动态sql
   declare
      table_name varchar2(20);
      tn_ddl varchar2(200);
   begin
     table_name := '&tn';
     tn_ddl :=  'create table ' || table_name || ' ( id varchar2(32) primary key, name varchar2(20) )';
     execute immediate tn_ddl;
   end;

   declare
       tn varchar2(20); -- 查询的目标表
       cl1 varchar2(20); -- 查询的目标列
       cl2 varchar2(20); -- 查询的目标列
       wcl varchar2(20); -- 条件列
       wva varchar2(20); -- 条件值

       rv1 varchar2(20); -- 查询结果
       rv2 varchar2(20); -- 查询结果

       query_sql varchar2(200);
   begin
       tn := '&表名';
       cl1 := '&列名1';
       cl2 := '&列名2';
       wcl := '&条件列';
       wva := '&条件值';

      -- select ename,job from emp where empno = :wva
      query_sql := 'select ' || cl1 || ',' || cl2 || ' from ' ||  tn || ' where ' || wcl || ' = :wva';
      dbms_output.put_line(query_sql);
      execute immediate query_sql into rv1,rv2 using wva;

      dbms_output.put_line('ename: ' || rv1 || ' ,job:' || rv2);
   end; 

8. 异常处理
   抛出异常
   declare
     -- 自定义异常一定要在declare块中先声明
     my_exception exception;
     day_flag varchar2(10);
     i number;
   begin
     select to_char(sysdate, 'dd') into day_flag from dual;

     -- 触发预定义异常
     i := 10/0;

     if day_flag < 15 then
       -- 抛出自定义异常
       raise my_exception;
     end if;

   -- 所有在程序中可能会发生的异常都可以在exception块中进行处理
   exception
     when my_exception then
       dbms_output.put_line('哈哈,抓住你了...');
     when zero_divide then
       dbms_output.put_line('哈哈,又抓住你了...');
   end;


select * from user_tables;
select * from user_indexes;
select * from user_views;
select * from user_sequences;
select * from user_synonyms;


--游标eg

declare
  cursor c_emp is select ename,sal from emp;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  open c_emp;
  loop 
  fetch c_emp into v_ename,v_sal;
  exit when c_emp%notfound;
  dbms_output.put_line(v_ename||' : '||v_sal);
  end loop;
  close c_emp;
end;


declare
  cursor c_emp(dno number) is select ename,sal from emp where deptno=dno;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  v_count number;
  v_dno number;
begin
  v_dno:=&v_dno;
  select count(*) into v_count from emp where deptno=v_dno;
  dbms_output.put_line('count:'||v_count);
  open c_emp(v_dno);
    loop 
    fetch c_emp into v_ename,v_sal;
    exit when c_emp%notfound;
      dbms_output.put_line(v_ename||' : '||v_sal);
    end loop;
  close c_emp;
end;