oracle基础

oracle快速入门：

     学了两天oracle总结出一些例子，可以让你很快进入oracle世界哦！

--声明一个变量
declare
  v_name varchar2(30) :='ph';
begin
  DBMS_OUTPUT.PUT_LINE('Hello,'||v_name||' and pl/sql!');
end;
/

 declare
  v_name varchar2(30) ;
  v_num  number(7,2);
  v_bool boolean:=true;
  v_integer binary_integer;
  v_long Long;
  v_int  int;
begin
  v_name:='varchar2';
  DBMS_OUTPUT.PUT_LINE(v_name);
  v_num:=1934.234;
  DBMS_OUTPUT.PUT_LINE(v_num);

   IF v_bool then
     DBMS_OUTPUT.PUT_LINE('true');
   end IF;

  v_integer :=-11;
    DBMS_OUTPUT.PUT_LINE(v_integer);

  v_long:='................................................';
  DBMS_OUTPUT.PUT_LINE(v_long);
 
  V_INT :=10;
  DBMS_OUTPUT.PUT_LINE(V_INT);
end;
/
例子二：
declare
  v_name emp.ename%type;
begin
  v_name:='type';
 
  DBMS_OUTPUT.PUT_LINE(v_name);
end;
/

--各种类型实例
 declare
  v_name varchar2(30) ;
  v_num  number(7,2);
  v_bool boolean:=true;
  v_integer binary_integer;
  v_long Long;
  v_int  int;
begin
  v_name:='varchar2';
  DBMS_OUTPUT.PUT_LINE(v_name);
  v_num:=1934.234;
  DBMS_OUTPUT.PUT_LINE(v_num);

   IF v_bool then
     DBMS_OUTPUT.PUT_LINE('true');
   end IF;

  v_integer :=-11;
    DBMS_OUTPUT.PUT_LINE(v_integer);

  v_long:='................................................';
  DBMS_OUTPUT.PUT_LINE(v_long);
 
  V_INT :=10;
  DBMS_OUTPUT.PUT_LINE(V_INT);
end;
/
--变量赋值例子二：
declare
  v_name emp.ename%type;
begin
  v_name:='type';
 
  DBMS_OUTPUT.PUT_LINE(v_name);
end;
/

--语句块中用 select into
declare
 v_name emp.ename%type;
begin
  select ename into v_name from emp where ename like 'SM%';
  DBMS_OUTPUT.PUT_LINE(v_name);
end;
/

--if else 例子
declare
v_num number(2):=&a;
begin
  if v_num=1 then
     begin
       dbms_output.put_line('你输出的是星期天！');
        dbms_output.put_line('谢谢参与！');
    end;
  elsif v_num=2 then
      dbms_output.put_line('你输出的是星期二！');
   elsif v_num=3 then
    dbms_output.put_line('你输出的是星期三！');
    end if;
   end;
/

--case 判断语句
declare
v_num number(2):=&a;
begin
   case v_num
     when 1 then
          dbms_output.put_line('你输出的是星期1！');
     when 2 then
          dbms_output.put_line('你输出的是星期2！');
      when 3 then
           dbms_output.put_line('你输出的是星期三！');
     end case;
   end;
/

--3.    Case 返回值问题 (装到一张临时表和赋值)
declare
v_num number(2):=&a;
v_result1 varchar(20);
v_result2 varchar(20);
begin
  select
     case
       when v_num=2 then '2'
       when v_num=3 then '3'
      end
      into v_result1 from dual;
      v_result2:=
      case
        when v_num=2 then'2'
        when v_num=3 then '3'
       end;
       dbms_output.put_line(v_result1);
       dbms_output.put_line(v_result2);
    end;
/

-- Decode的比较  (当输入的数字和第二个参数相等时，就输出第三个参数，如果不等，就输出第四个参数)
declare
   v_num number(2):=&a;
   v_result varchar2(20);
 begin
   select decode(v_num,2,'你输入的是2','你输入的不是2')into v_result from dual;
   dbms_output.put_line(v_result);
  end;
/    
 
-- loop  end loop基本   
declare
   v_num int:=&a;
 begin
   loop
     dbms_output.put_line('这是循环'||v_num);
     v_num :=v_num+1;
   exit  when  v_num>10;
   end loop;
end;
/
 --以一张emp表为例，算出工资
declare
  v_count int;
  v_num int:=1;
  v_value int:=0;
  v_sum int:=0;
begin
  select count(*) into v_count from emp;
  loop
  select sal into v_value from(select sal,ename,rownum rn from emp)a
                                                where rn=v_num;
  v_sum:=v_sum+v_value;
  v_num:=v_num+1;
  exit when v_num>v_count;
  end loop;
  dbms_output.put_line('总和:'||v_sum);
end;
/

--  while 条件 loop End loop;
declare
 v_num int:=&a;
  begin
    while v_num>2 loop
       dbms_output.put_line('while 循环前：'||v_num);
      v_num:=v_num-1;
      dbms_output.put_line('while 循环后：'||v_num);
     end loop;
   end;
/
--for FOR  隐式声明的变量 in(reverse)  lower..upper  LOOP  END LOOP(单层循环)

declare
 v_num int:=&a;
 begin
   for resutl in reverse  1..20 loop
     v_num:=v_num+1;
   dbms_output.put_line('第：'||v_num||'循环：');
   end loop;
 end ;
/

--for FOR  隐式声明的变量 in(reverse)  lower..upper  LOOP  END LOOP（双层循环输出倒三角形）
declare
   v_char varchar2(20);     
begin
    v_char:='';
   for v_num in reverse 1..20 loop --处理行
       
      for  v_num2     in 1..v_num  loop --处理列
          v_char:=v_char||'*';
      end loop;
          dbms_output.put_line(v_char);
          v_char:='';
      
   end loop;
     
end;
/

--自定义异常
declare
 v_num int:=&a;
 v_num2 int:=&b;
 v_result number(7,2);
begin
  v_result:=v_num/v_num2;
  dbms_output.put_line(v_result);
  exception
    when zero_divide then
       dbms_output.put_line('出错了，除数为0，请重新输入！');
     when no_data_found then
        dbms_output.put_line('未找到数据，请重新输入！');
     when value_error then
        dbms_output.put_line('出错了，数据转换出错，请重新输入！');
     when others then
        dbms_output.put_line('出错了，除数为0，请重新输入！');
  end;
/

--自定义异常  Raise(抛出)  异常变量;  Exception:异常 1.    声明异常 2.    抛出异常 3.    捕获异常
declare
   v_num int;
   ev_zero exception;
 begin
   v_num:=&a;
   if(v_num=0) then
         raise ev_zero;
    end if;
  exception
    when ev_zero then
       dbms_output.put_line('输入的数为0，请重新输入！');
    when others  then
       dbms_output.put_line('出错了，除数为0，请重新输入！');
  end;
/

--1    内层抛，内层捕获后，外层能否捕获？否
declare
   v_num int;
   ev_zero exception;
 begin
   begin
     v_num:=&a;
    if(v_num=0) then
       raise ev_zero;
    end if;
  exception
    when ev_zero then
       dbms_output.put_line('内层的异常处理，输入的数为0，请重新输入！');
     when others then
       dbms_output.put_line('内层的异常处理，出错了，除数为0，请重新输入！');
   end;
  exception
    when ev_zero then
       dbms_output.put_line('外层的异常处理，输入的数为0，请重新输入！');
     when others then
       dbms_output.put_line('外层的异常');
  end;
/

--2.    内层抛，内层不捕获，外层能否捕获？是

declare
  v_num int;
  ev_zero exception;
 begin
   begin
     v_num:=&a;
     if(v_num=0) then
           raise ev_zero;
        end if;
/*   exception
       when ev_zero then
         dbms_output.put_line('内层的异常处理，输入的数为0，请重新输入！');
       when others then
          dbms_output.put_line('内层的异常处理，出错了，除数为0，请重新输入！');

*/
end;
  exception
    when ev_zero then
      dbms_output.put_line('外层的异常处理，输入的数为0，请重新输入');
    when others then
      dbms_output.put_line('外层的异常处理');
end;
/

--3.    外层抛，内层是否能捕获？ 否
例子:
declare
   v_num  int ;
   ev_zero exception;--声明的异常
begin
 raise ev_zero;  
  begin
    v_num:=&a;
  exception --相当于C#中的CATCH部分
      when ev_zero then  --捕获异常
         DBMS_OUTPUT.PUT_LINE('内层的异常处理,输入的数为0，请重新输入！');
      when   others         then --包括了所有的异常
         DBMS_OUTPUT.PUT_LINE('内层的异常处理，出错了，除数为0，请重新输入！');
   end;
exception
     when ev_zero then  --捕获异常
         DBMS_OUTPUT.PUT_LINE('外层的异常处理，输入的数为0，请重新输入！');
     when others then
         DBMS_OUTPUT.PUT_LINE('外层的异常处理!');
end;
/

--捕获所有异常
declare
  v_num int;
  v_errcode int;
  v_errmsg  varchar2(50);
begin
  v_num:=&a;
  raise_application_error(-20000,'haha');
  v_num:=v_num/0;
 
exception
    when others then
       v_errcode:=sqlcode;
       v_errmsg:=sqlerrm;
       dbms_output.put_line('当前错误是：'||v_errcode||'  '||v_errmsg);
end;
/
--增删改
begin
  insert into tab_test values(3,'嘻嘻');
  delete from tab_test where id=1;
  insert into tab_test(name) values('gg');
commit;
  exception
    when no_data_found then
      dbms_output.put_line(sqlerrm||sqlcode);
    when   too_many_rows then
     dbms_output.put_line('返回函数太多！');
    when  zero_divide  then
      dbms_output.put_line('除数为0的异常！');
    when  others  then
      dbms_output.put_line(sqlerrm||sqlcode);
    rollback;
 end;
/

drop table tab_test;

create table tab_test(id int,name varchar2(50));
--------------------------------------------------------------------------------------
--存储过程   Create or replace procedure  存储过程名( 参数列表(用逗号隔开，in  out  in out))
   /*括号不能少，其中参数有精度
   As
  声明本地变量;
   Begin
    过程语句;
   End;
*/
--1、创建存储过程(带一个参数)
create or replace procedure up_first(v_lines varchar2)
as
begin
  dbms_output.put_line(v_lines);
end;
/

--1、调用
--语句块调用
declare
 v_num varchar2(50):='hhhh';
 begin
 up_first(v_num);
 end;
/
--直接调
exec up_first('ddddddd');
--2、创建存储过程（带三个参数）
create or replace procedure up_first
                             ( v_in in varchar2 ,v_out out varchar2,v_outin in out varchar2 )
as
begin
   DBMS_OUTPUT.PUT_LINE('输入参数： '||v_in);
   --v_out:='输出参数的值';
   DBMS_OUTPUT.PUT_LINE('输出参数： '||v_out);
   --v_outin:='输入出参数的值';
   DBMS_OUTPUT.PUT_LINE('输入出参数： '||v_outin);
end;
/


exec up_first('sjgjk','gsegfs','gegewg');
--2、调用上面的存储过程
declare
 v_outs varchar2(50):='hhahha';
 v_outins varchar2(50):='xixix';
 begin
   up_first('元宵节快乐！',v_outs,v_outins);
   dbms_output.put_line(' ');
   dbms_output.put_line('v_outs: '||v_outs);
   dbms_output.put_line('v_outins : '||v_outins);
end;
/

--3.用存储过程打出三角形（带两个参数）
create or replace procedure up_first
   ( v_lineNum in int, v_flag  out varchar2  )
as
   v_char varchar2(50):='';
begin
    if v_lineNum<=0    then
       raise_application_error(-20001,'输入的行数非正数！请重新输入！');
    end if;
   
    for  v_line in 1..v_lineNum   loop
          for  v_col   in 1..v_line   loop
                v_char:=v_char||'*';       
          end loop;
          DBMS_OUTPUT.PUT_LINE(v_char);
          v_char:='';
    end loop;
    v_flag:='Success';
exception
     when others then
          v_flag:='fail';
          dbms_output.put_line('错误信息：'||sqlcode||' '||sqlerrm);
end;
/


--3.调用上面的存储过程

declare
 v_outs int:=&a;
 v_outins varchar2(50):='xixix';
 begin
   up_first(v_outs,v_outins);
   dbms_output.put_line(' ');
   dbms_output.put_line('v_outs: '||v_outs);
   dbms_output.put_line('v_outins : '||v_outins);
end;
/


-------------------------------------------------------------------------
--创建所需要的表
  create table UserInfo
   (UserId  int not null primary key,
   UserName varchar2(50) not null, --真实姓名
   PassWord varchar2(50) not null, --密码
   UserRole number(2), --角色
    Gender number(1) not null check(Gender in(0,1)),  --性别
    PassQuestion varchar2(50) not null,  --密码提示问题
    PassAnswer  varchar2(50) not null,   --密码提示答案
    Email   varchar2(50) not null,  --电子邮箱
    TelNO   number(11) not null,    --电话号码
    Address varchar2(50),          --联系地址
    IdCardNO  number(18),          --身份证号
    Money   number,                --用户余额
    UserState number               --用户状态
  );
 

--创建函数
create or replace function fun_test(v_char varchar2)
return varchar2
is
begin
   return concat(v_char, '*')||'!';
end;
/
--调用函数
select fun_test('gdgsdg') from dual;

--一：检查用户是否存在的一个函数
create or replace function fun_checkUser(v_uname varchar2)
return number
is
  v_result number;
  v_count number;
begin
        select count(*) into v_count from userInfo where username=v_uname ;
        if v_count>0 then
            v_result:=1;--有用户
        else
            v_result:=0;--没有用户
        end if;
        
        return v_result;
        
        exception
             when  others then
                  v_result:=-1;--出现异常
        
        return v_result;
end;
/

--调用函数
select fun_checkUser('sgg') from dual;
-------------------------------------------------------------------------------------
--创建表
 create table UserInfo
   (UserId  int not null primary key,
   UserName varchar2(50) not null, --真实姓名
   PassWord varchar2(50) not null, --密码
   UserRole number(2), --角色
    Gender number(1) not null check(Gender in(0,1)),  --性别
    PassQuestion varchar2(50) not null,  --密码提示问题
    PassAnswer  varchar2(50) not null,   --密码提示答案
    Email   varchar2(50) not null,  --电子邮箱
    TelNO   number(11) not null,    --电话号码
    Address varchar2(50),          --联系地址
    IdCardNO  number(18),          --身份证号
    Money   number,                --用户余额
    UserState number               --用户状态
  );

--创建一个序列
create sequence seq_uifo start with 1 increment by 2;
declare
  v_num number;
  begin
    select seq_uifo.nextval into v_num from dual;
    dbms_output.put_line('next:'||v_num);
  end;
/
--调用

--------------------------------------------------------------------------------------------
---创建视图

create or replace view vw_emp_dept as
select e.empno,e.ename,e.job,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d where e.deptno=d.deptno and e.ename like '%L%'
with read only;

--更新视图
--使用where 子句去约束对视图的更新操作
create or replace view vw_emp as
select  e.empno,e.ename,e.job    from emp e where e.empno<7566
with check option;

--查看视图
select * from vw_emp;
----------------------------------------------------------------------------------------
--创建表
create table tab_tri(id number primary key,name varchar2(50));

create table tab_emp(id number primary key,name varchar2(50));

--创建触发器：
create or replace trigger tri_emp
before delete on tab_tri
for each row
 begin
   dbms_output.put_line(:old.id||:old.name);
 end;
/

-----------------------------------------------------------
--=========创建一个游标===============
declare
v_emp_row emp%rowtype;--int varchar2
--声明游标
cursor mycur is select * from emp;
v_count int:=0;
begin
--打开游标
 open mycur;
--把第一条数据取出来，放入变量中?不知道是否有数据
    fetch mycur into v_emp_row ;
 dbms_output.put_line('++人数+：'||'姓名'||'======== '||'工资'||' =========='||'奖金');  
   --判断上次游标移动是否有数据
  while  mycur%found  loop
        v_count:=v_count+1;
            dbms_output.put_line('第'||v_count||'个人：'||v_emp_row.ename||'======== '||v_emp_row.job||' =========='||v_emp_row.sal);
--让游标移动到下一行          
        fetch mycur into v_emp_row ;
    end loop;
--关闭游标
 close mycur;
end;
/

--========带参数的游标================
declare
v_emp_row emp%rowtype;
v_input number:=&a;
cursor mycur(v_num number) is select * from emp where empno>=v_num;
v_count int:=0;
begin
  open mycur(v_input);
   --3、提取数据
  fetch mycur into v_emp_row;
  while mycur%found loop
    v_count:=v_count+1;
        dbms_output.put_line('第'||v_count||'个人: '||v_emp_row.ename);
    fetch mycur into v_emp_row;
  end loop;
 close mycur;
end;
/