oracle学习 一

 

/*一、在输出框里显示输出HelloWorld*/
begin
       dbms_output.put_line('hello world!');
end;
/*二、异常语句*/
declare
       v_num number:=0;
begin
       v_num:=2/0;
       dbms_output.put_line(v_num);           
exception/*异常*/
       when others then
       dbms_output.put_line('error');      
end;   
/*三、声明变量*/
declare
v_name varchar2(20);
begin
       v_name:='xxx';
       dbms_output.put_line(v_name);
end;
/*四、循环*/
declare
v_temp number:=1;
begin
        loop
               dbms_output.put_line(v_temp);
               v_temp:=v_temp+1;
               exit when(v_temp=10);
        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;
/*查看当前用户下有多少个存储过程*/
select object_name from user_procedures;
/*查看表*/
select * from stuinfo;
/*删除数据*/
delete from stuinfo;
/*删除表*/
drop table stuinfo;
/*创建表*/
create table stu_user
(
       id number(4) primary key,
       uname varchar2(20),
       age number(3)
);
/*查看stu_user表*/
select * from stu_user;
/*创建存储过程*/
create or replace procedure tu_save
(
       id in number,
       uname in varchar2,
       age in number
)
as
begin
       insert into stu_user (id,uname,age) values(id,uname,age);
end tu_save;
/*调用存储过程*/
call tu_save(1,'snow',12);
call tu_save(2,'test',22);
call tu_save(3,'tool',56);
call tu_save(4,'oracle',21);
/*查询*/
select * from stu_user;
/*创建删除存储过程*/
create or replace procedure tu_del
(
       v_id in number
)
as
begin
       delete from stu_user where id=v_id;
       commit;
end;
/*调用删除存储过程*/
call tu_del(4);
/*删除存储过程*/
drop procedure tu_del;
/*删除语句*/
delete from stu_user where id=2;
/*创建更新存储过程*/
create or replace procedure tu_update
(
       v_id in number
)
as
begin
       update stu_user set age=33 where id=v_id;
       commit;
end;
/*调用更新存储过程*/
call tu_update (2);
/**/
begin
     tu_update(1);
end;
/*创建表*/
create table user_log
(
       uname varchar2(20),
       action varchar2(10),
       atime date
);
/*查看user_log表*/
select * from user_log;
/*创建触发器*/
create or replace trigger trig
       after insert or delete or update on stu_user for each row
begin
       if inserting then
            insert into user_log values(USER,'insert',sysdate);
       elsif updating then
            insert into user_log values(USER,'update',sysdate);  
       elsif deleting then
            insert into user_log values(USER,'delete',sysdate);     
       end if;     
end;
/*测试触发器*/
call tu_save(5,'dddd',23);
call tu_save(6,'525263652',23);
/*查看stu_user表*/
select * from stu_user;
/*添加字段*/
alter table stu_user add (email varchar2(50));
/*修改字段*/
alter table stu_user modify(email varchar2(100));
/*删除字段把数据一块删除了*/
alter table stu_user drop(email);
/*添加*/
insert into stu_user values(7,'dddddsdfsdfs',45,'[e-mail]dddddddd');
insert into stu_user values(8,'dddddsdfsdfs',45,'[e-mail]dddddddd');
/*删除数据*/
delete from stu_user;
/*创建序列*/
create sequence seq_id start with 1 increment by 2;
/*使用序列*/
insert into stu_user values(seq_id.nextval,'dddddsdfsdfs',45);
/*删除序列*/
drop sequence seq_id;
/*查看当前用户下有什么序列有多少个序列*/
select sequence_name from user_sequences;