主题：[推荐]关于Oracle中SQL语法的部分整理资料之二

1 DML
1、1 insert
基本使用
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000006','姚华',0,TO_DATE('12-5月 -1983','dd-Mon-
yyyy'),1.77);

select * into table1 from student

create table table1 as select * from student
drop table table1

insert into table1 select * from student(必须事先具有表结构)

1、2 update
update student set height=1.6 where sex=0

1、3 delete
delete from student where sex=1

2 存储过程
2、1 一般存储过程
create or replace procedure spgetcount as
begin
  select count(*) from student;
end spgetcount;

show errors;

ALTER TABLE "SYSTEM"."STUDENT" RENAME COLUMN "NUMBER" TO "SID"

create or replace procedure spgetcount as
rcount number;
begin
  select count(*) into rcount from student;
  DBMS_OUTPUT.PUT_LINE(rcount);
end spgetcount;

调用
set serveroutput on;
begin
   spgetcount;
end;

2、2 带有参数的存储过程
ALTER TABLE "SYSTEM"."GRADE" RENAME COLUMN "NUMBER" TO "SID"

create or replace procedure spgetavg(sname varchar2) as
avgvalue number;
begin
  select avg(grade) into avgvalue from student natural join grade where name=sname;
  DBMS_OUTPUT.PUT_LINE(avgvalue);
end spgetavg;

set serveroutput on;
begin
   spgetavg('赵怡春');
end;

create or replace procedure spgetavg(sname varchar2,result out number) as
avgvalue number;
begin
  select avg(grade) into avgvalue from student natural join grade where name=sname;
  result:=avgvalue;
end spgetavg;

set serveroutput on;
declare
   resultgrade number;
begin
   spgetavg('赵怡春',resultgrade);
   DBMS_OUTPUT.PUT_LINE(resultgrade);
end;

3 控制语句
set serveroutput on size 1000000;
declare
a number:=-2;
begin
if a<0 then
  a:=-a;
else
  a:=a;
end if;
dbms_output.put_line(a);
exception
when others then
raise_application_error(-20010,'err code : ' || sqlcode || 'desc : ' || sqlerrm);
end;

SET SERVEROUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('打印三角形');
  FOR i IN 1..9 LOOP
    FOR j IN 1..i LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP for_j;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP for_i;
END;

DECLARE
  l_num   NUMBER;
  counter NUMBER;
BEGIN
  counter:=1;
  WHILE counter <= 10
  LOOP
    l_num := ABS((DBMS_RANDOM.RANDOM MOD 100)) + 1;
    DBMS_OUTPUT.PUT_LINE(l_num);
    counter := counter + 1;
  END LOOP;
END;

4 游标
declare
   name varchar2(8);
   sex number;
   rs SYS_REFCURSOR;
begin
open rs for select name,sex from student;
loop
fetch rs into name,sex;
exit when rs%NOTFOUND;
if sex=1 then
  DBMS_OUTPUT.PUT_LINE(name || ' 男');
else
  DBMS_OUTPUT.PUT_LINE(name || ' 女');
end if;
end loop;
end;

5 函数
create or replace function getgender
(sex number)
return varchar
is
begin
declare
gender varchar2(2);
begin
if sex=1 then
  gender:='男';
else
  gender:='女';
end if;
return gender;
end;
end;

select name,getgender(sex) from student

select name 姓名,case when sex=1 then '男' else '女' end 性别 from student