PLSQL语法

/*
  PL/SQL : Oracle内部语言
         SQLServer的是:TSQL
  Procedural Language/SQL 是oracle在标准的SQL语言上的扩展,PL/SQL不仅允许嵌入SQL语言,还可以
  定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得
  更加的强大.
 
  优点:提高应用程序的运行性能,模块化的设计思想,减少网络传输量,提高安全性
        
  SQL语言:有两种SQL1992/SQL1996  没有循环控制结构
 
  PL/SQL块由三个部分组成:定义部分,执行部分,例外处理部分
  declare
       定义部分 -- 定义常量,变量,游标,例外,复杂数据类型
  begin
       执行部分 -- 要执行的PL/SQL语句和SQL语句
  exception
       例外部分 -- 处理运行的各种错误
  end;
 
  调用过程
  exec 过程名
  call 过程名               
*/

--匿名块

--简单打印一句话

--打开显示
set serveroutput on ;

begin
  dbms_output.put_line('Hello PL/SQL!');
end ;
/

/*
    变量声明的规则
    1 变量名不能够使用保留字
    2 第一个字符必须是字母
    3 变量名最多包含30个字符
    4 不要与数据库的表或者列同名
    5 每一行只能声明一个变量
   
    常用的变量类型
    1 binary_integer : 整数,主要用来计数而不是用来表示字段类型
    2 number: 数字字符
    3 char : 定长字符串
    4 varchar2 : 变长字符串
    5 data : 日期
    6 long : 长字符串最长2GB
    7 boolean : 布尔类型,可以取值true|false和null值
   
*/

--声明变量
declare v_name varchar2(20) ;

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;
 v_name varchar2(20) not null :='accp' ;
begin
 dbms_output.put_line('v_temp value :'||v_temp);
end;

--根据表中字段类型声明变量类型的
declare
 v_empno number(4);
 v_empno2 emp.empno%type;
 v_empno3 v_empno2%type;
begin
 dbms_output.put_line('test');
end;

/*
  复杂变量
  Table变量类型 : 集合  (表示集合时要自己定义的一个新的数据类型)
  Record变量类型 : 类 (代表实体,一条记录)
*/
--定义Table变量类型
--Table 变量类型(数组)
declare
 type type_newname is table of emp.empno%type index by binary_integer; --声明一个数组类型
  v_empnos type_newname ;  --用数组类型定义变量
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  :='aaa';
 v_temp.loc    :='bj';
 dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

--使用%rowtype 声明record变量
declare
 v_temp dept%rowtype;   --直接用一张表的行来定义record,表结构变化时record也在变
begin
 v_temp.deptno :=50;
 v_temp.dname  :='aaa';
 v_temp.loc    :='bj';
 dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end ;

/*
    PL/SQL中的SQL语句
    select 语句返回有且只有一条记录
*/
--select语句
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_temp emp%rowtype;
begin
 select * into v_emp from emp where empno=7369;
 dbms_output.put_line(v_temp.ename);
end;

--insert 语句
declare
 v_deptno dept.deptno%type :=10;
 v_dname dept.dname%type :='aaa';
 v_loc dept.loc%type :='beijing' ;
begin
 insert into dept2 values(v_deptno,v_dname,v_loc);
 commit;
end;

--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 ;
 dbms_output.put_line(sql%rowcount||'条记被影响');
 commit ;
end;

--DDL语句
begin
  execute immediate 'create table t_temp(nnn varchar2(20) default "aaa")' ;
end ;

--语句块
declare
 v_name varchar2(20);
begin
 v_name := 'jack' ;
 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;

--分支语句
--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;

--循环  (相当于do...while)
declare
 i binary_integer :=1;
begin
 loop
  dbms_output.put_line(i);
   i := i+1 ;
   exit when(i>=11);
 end loop;
end;

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

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;

--错误处理
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;

--错误记录
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;

select * from errorlog;