PLSQL基本语法介绍

---PL/SQL语法结构----
declare
 v_name varchar2(20):='刘亦菲';
 v_empno number:='123123';
 v_age constant number:='23';
 V_ename myemp.ename%type:='刘涛';
 begin
   dbms_output.put_line(v_name);
   select ename into v_ename from myemp where empno='7788';
   dbms_output.put_line(v_ename);
 end;
 ----条件语句------
 declare
 v_age number(3):=#
 begin
     if v_age<18 then
        dbms_output.put_line('未成年');
     elsif v_age>18 and v_age<60 then
        dbms_output.put_line('成年人');
     else
        dbms_output.put_line('老年人');
     end if;
 end;
 ---循环------
 declare
 i number(3):=1;
 begin
   loop
     if i>100 then exit; end if;
     dbms_output.put_line(i);
     i:=i+1;
   end loop;
 end;
 
 ---循环----while----
 declare
 n number(3):=1;
 begin
 while n<100
   loop
     dbms_output.put_line(n);
     n:=n+1;
   end loop;
 end;
 ---循环----for----
 declare
 i number(3):=1;
 begin
 for i in 1..100
   loop
     dbms_output.put_line(i);
   end loop;
 end;
 ---游标---cursor---
 declare
 cursor c_emp is select * from myemp;
 c_row myemp%rowtype;
 begin
   open c_emp;
   loop
     fetch c_emp into c_row;  
     exit when c_emp%notfound;
     dbms_output.put_line(c_row.ename||c_row.empno);
   end loop;
   close c_emp;
 end;
---游标---带参数的cursor------
 declare
 cursor c_myemp(dpno myemp.deptno%type) is select * from myemp where deptno=dpno;
 c_row myemp%rowtype;
 begin
   open c_myemp(30);
   loop
     fetch c_myemp into c_row;
     exit when c_myemp%notfound;
     update myemp set sal=sal+10000 where empno=c_row.empno;
   end loop;
   close c_myemp;
 end;
 
 select * from myemp where deptno='30';
 ---exception-----
 declare
 age number(3):=&age;
 m number(1):=&m;
 begin
   age:=age/m;
   exception
     when zero_divide then
       dbms_output.put_line('不能被0除');
 end;
 delcare
 
 
 declare
 ---procedure---存储过程--------
 create or replace procedure p_myemp(eno myemp.empno%type) is
 myename myemp.ename%type;
 begin
     select ename into myename from myemp where empno=eno;
     dbms_output.put_line(myename);
 end;
 call p_myemp(7788);
 
 begin
      p_myemp(7788);
 end;
 --procedure---带有out的存储过程----
 create or replace procedure p_out(eno myemp.empno%type,yearsal out number) as
 begin
   select sal*12+nvl(comm,0) into yearsal from myemp where empno=eno;
   dbms_output.put_line(yearsal);
 end;
 
 declare
 yearsal number;
 begin
    p_out(7788,yearsal);
 end;
 ----function---存储函数--
 create or replace function func_myemp(eno number) return number is
 year_sal number(7);
 begin
   select sal*12+nvl(comm,0) into year_sal from myemp where empno=eno;
   return year_sal;
 end;
 
 declare
 year_sal number;
 begin
   year_sal:=func_myemp(7788);
   dbms_output.put_line(year_sal);
 end;
 
 begin
   dbms_output.put_line(func_myemp(7788));
 end;
-----------字段的截取----
 select ename from myemp;
 select  substr(ename,length(ename)-2) from myemp;
 select  substr(ename,2) from myemp;
 select * from myemp;
 select t.*,rownum r from (select t1.*,rownum r from (select * from myemp order by sal) t1) t order by t.r desc;
 --------触发器---trigger------
 create or replace trigger myemp_trigger
 
 before
 insert or update or delete
 on myemp
 
 declare
 today varchar2(30);
 begin
   select to_char(sysdate,'yyyy-mm-dd') into today from dual;
   if today='2016-11-19' then
     raise_application_error(-20001,'今天不能操作员工表!');
   end if;
 end;
 
 insert into myemp(empno,ename,deptno) values(1111,'tom',30);
 ----触发器的另一个作用----数据备份-----------
 create table myemp_bak(
 bid number,
 oldsal number,
 newsal number,
 alterdate date,
 constraint primary_key primary key(bid)
 );
 
 create or replace trigger myemptrigger
 after
 update of sal
 on myemp
 for each row
 begin
   insert into myemp_bak values(mysequence.nextval,:old.sal,:new.sal,sysdate);
 end;
 
 update myemp set sal=sal+10000 where empno='7499';
 
 select * from myemp;
 select * from myemp_bak;
 delete from myemp_bak;
 -----数据恢复----------
 create table myemp_bak_bak as
 select * from myemp as of TIMESTAMP
 to_timestamp('20161119 202300','yyyymmdd hh24miss');
 
 select * from myemp_bak_bak;
 select * from myemp;
 drop table myemp_bak_bak;
 delete from myemp;
-------------练习题-------------
declare
emp_rec myemp%rowtype;
begin

select * into emp_rec from myemp where empno=7788;

dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);

end;
-----------数据恢复-------------------

insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 31600, 300, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 21250, 500, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 21250, 1400, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 22850, null, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 12450, null, 10);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 15000, null, 10);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 21500, 0, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 20950, null, 30);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into MYEMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 11300, null, 10);
commit;