oracle学习笔记（基本语句）

来源：互联网发布：ps怎么修改淘宝主图编辑：程序博客网时间：2024/05/17 23:44

sys/sysdba

一基本操作

1 建表

create table stu5 ----建表语句

(

s# varchar(20) not null,

sname varchar(20) ,

addr varchar(100)

)

create table stu6 as select * from stu5 ----根据stu5生成stu6表

2 修改表名

alter table stu5 rename to stu10;

alter table stu10 rename to stu5;

3 添加字段

alter table stu5 add addr3 varchar(100); ---添加列

4 修改字段名

alter table stu5 rename column addr to addr2 ;--addr->addr2 修改字段名；

alter table stu5 rename column addr2 to addr---又把名称改回来了

5 修改字段类型

alter table stu5 modify addr varchar2(100) ---addr属性改为了varchar2(100)

6 插入

insert into stu5(s#,sname) values('s20140102','张三')

alter table stu5 add birth date;--添加日期型字段

insert into stu5(s#,birth) values('s20140104',to_date('2015-11-20','yyyy-mm-dd'))--插入日期型数值

insert into stu5(s#,birth) values('s20141105',to_date('2014-12-1 18:55:22','yyyy-mm-ddhh24:mi:ss'));

--插入日期型数值带时分秒

7 修改

update stu5 set sname='李伟' where s#='s20140102' ---修改某字段值

-----------------------

select * from stu4

create view v_stu4 ---创建视图

(学号,姓名,年龄,性别)

select s#,sname,sage,ssex from stu4

where s#>10110

select * from v_stu4

dropview v_stu4 ---删除视图

create view v_stu4

(学号,姓名,年龄,性别)

select s#,sname,sage,ssex from stu4

where s#>100110

select * from v_stu4 --视图 s#>100110的记录

二条件语句

1 if

declare

var_name1 varchar2(50);

var_name2 varchar2(50);

begin

var_name1:='east';

var_name2:='western';

if length(var_name1) <length(var_name2) then

dbms_output.put_line('字符串'||var_name1||'的长度比'||'字符串'||var_name2||'的长度小');

endif;

end; ----t 字符串east的长度比字符串western的长度小

2 if …….. else

declare

age int:=55;

begin

if age>56 then

dbms_output.put_line('您可以退休了');

else

dbms_output.put_line('现在还不可以退休');

endif;

end; ---t 现在还不可以退休

3 if …….. elsif………elsif……else

declare

month int :=10;

begin ---begin开始..........end 结束

ifmonth>=1andmonth<=3 then

dbms_output.put_line('现在是春季'); ---带分号

elsif month>=4andmonth<=6 then

dbms_output.put_line('现在是夏季'); ---带分号

elsifmonth >=7 and month<=9 then

dbms_output.put_line('现在是秋季') ;

elsif month >=10 and month <=12 then

dbms_output.put_line ('现在是冬季') ; ---带分号

else ---else前面有then，本身没有then

dbms_output.put_line ('对不起，输入错误！');

endif; ---带分号

end;

4 case

declare

season int:=3;

begin

case season

when1 then

dbms_output.put_line ('现在是第'||season||'季度包含1,2,3月份');

when2 then

dbms_output.put_line('现在是第'||season||'季度包含4,5,6月份');

when 3 then

dbms_output.put_line('现在是第'||season||'季度包含7,8,9月份');

when 4 then

dbms_output.put_line('现在是第'||season||'季度包含10,11,12月份');

end case;

end; ----t 现在是第3季度包含7,8,9月份

----------

declare

season int:=2;

info varchar2(50);

begin

case season

when1 then

info:='现在是第'||season||'季度包含1,2,3月份';

when2 then

info:='现在是第'||season||'季度包含4,5,6月份';

when 3 then

info:='现在是第'||season||'季度包含7,8,9月份';

when 4 then

info:='现在是第'||season||'季度包含10,11,12月份';

end case;

dbms_output.put_line(info);

end; ----t 现在是第2季度包含4,5,6月份 sys/sysdba

------------------

三循环语句

1 when

declare

i int:=0;

sums int:=0;

begin

loop

i:=i+1;

sums:=sums+i;

exit when i=100;

endloop;

dbms_output.put_line('前100个自然数的和是：'||sums);

end; ----t 前100个自然数的和是：5050

2 while

declare

i int:=0;

s int:=0;

begin

while i<100 loop

i:=i+1; -----这句一定别少了！！！！！！

s:=s+i;

endloop;

dbms_output.put_line('前100个自然数的和是：'||s);

end; ---t 前100个自然数的和是：5050

-----------

3 for

declare

s int:=0;

begin

for i in reverse 0..10 loop

ifmod(i,2)=0 then

s:=s+i;

end if;

end loop;

dbms_output.put_line('前10个自然数中的偶数和是：'||s);

end; ---前10个自然数中的偶数和是：30

------10 8 6 4 2

--------------------

declare

s int:=0;

begin

for i in 0..10 loop

ifmod(i,2)=0 then

s:=s+i;

end if;

end loop;

dbms_output.put_line('前10个自然数中的偶数和是：'||s);

end; ---t 前10个自然数中的偶数和是：30

--- 2 4 6 8 10

--------------------------

四存储过程

1 不带参数

create or replace procedure proc13 is

begin

insertinto stu2 values('200110','黄山',to_date('1995-5-8','yyyy-mm-dd'),'男');

commit;

dbms_output.put_line ('插入新纪录成功！');

end proc13; ------t 有分号

select * from stu2

begin

proc13; 插入新纪录成功！

end;

-----------

---查询学生总数

createor replace procedure cout_stu5

zongshu number(10);

begin

select count(*) into zongshu from stu5;

dbms_output.put_line('学生总数是：'||zongshu);

end;

begin

cout_stu5;

end; ---t 学生总数是：11

2 带输入参数

create or replace procedure proc2_insr

( var_s# in varchar2,

var_sname in varchar2,

var_addr in varchar2

) as --is 也行；

begin

insert into stu5(s#,sname,addr) values( var_s#,var_sname,var_addr);

commit;

dbms_output.put_line ('插入新纪录成功！'); ---括号中有单引号

end; ---定义完成

begin

proc2_insr('s20140109','张峰','河西大街');

end; -----t

select * from stu5

--------

3 带输入输出参数

---修改成绩并输出

create or replace procedure stu_yuwen_change

( var_s# in varchar2, ------全局变量不能有长度不设置输入输出类型则默认是in

yuwen_raisein number

)

var_sname varchar2(10); ------局部变量可以有长度且不需设置in out

var_yuwen number(10,3); ------不需要加圆括号

begin

select sname,yuwen into var_sname,var_yuwen from stu where s#=var_s#;

update stu set yuwen=yuwen+yuwen_raise where s#=var_s#;

dbms_output.put_line(var_sname||'同学现在的语文成绩是：'||to_char(var_yuwen+yuwen_raise)); --to_char(yuwen)则错误

end stu_yuwen_change; --------没有commit也会修改成功 ----输出函数中的变量必须是局部变量

----定义是 end 存储过程名

----

begin

stu_yuwen_change('100101',5); ---第一次执行是 +5

end; -------t 赵华同学现在的语文成绩是：110

----调用是 end; 不需要带存储过程名

5 函数

1 无参数

create or replace function stu5_cout

return number

cout number;

begin

select count(*) into cout from stu5;

return cout;

end stu5_cout;

declare i number;

begin

i:=stu5_cout();

dbms_output.put_line('学生总数是:'||to_char(i));

end; ---学生总数是:11

2 带参数

create or replace function get_avg_sal

(

num_depno varchar ----这个变量后面不能加分号

)

return number

num_avg_pay number ;

begin

select avg(sal)into num_avg_pay from emp3 where depno=num_depno;

return (round(num_avg_pay,2));

exception

when no_data_found then

dbms_output.put_line('该部门编号不存在！');

return (0);

end;

---

select * from emp3

declare

avg_pay number;

begin

avg_pay:=get_avg_sal('102');

dbms_output.put_line('平均工资是：'||avg_pay); --不是dbms_out.

--print '平均工资是：'||avg_pay;

end;

6 触发器

create or replace trigger tri_dept2

beforeinsert or update or delete

on lyzh.dept2

declare var_tag varchar2(10);

begin

if inserting then

var_tag:='插入';

elsif updating then

var_tag:='修改';

elsif deleting then

var_tag:='删除';

endif;

insert into lyzh.dept2_log values(var_tag,sysdate);

end tri_dept2; ----t

insert into lyzh.dept2 values('31','产品服务部','奥体新街')

delete from lyzh.dept2 where depno='23'

select * from lyzh.dept2 ----5r

select * from lyzh.dept2_log

0 0