oracle的PLSQL基础

来源：互联网发布：知乎毅伟商学院的一天编辑：程序博客网时间：2024/04/28 04:47
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, %rowtype2. 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;--游标egdeclare  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;
0 0