oracle存储过程语句例子3---基础

--1.
declare
x integer;
begin
x:=4**3;
dbms_output.put_line(x);
end;
--2.
create table student(
stuid number(4) primary key,
stuname varchar2(10) not null,
stunickname varchar2(20) unique not null,
stusex varchar2(2) check(stusex='男'or stusex='女'),
stuage number(4) check(stuage between 18 and 40)
);
insert into student values(1001,'刘德华','华仔','男',40);
insert into student values(1002,'周杰伦','周总','男',32);
insert into student values(1003,'范冰冰','范爷','女',35);
insert into student values(1004,'刘德华','华哥','男',40);
select *from student;

--(1)
declare
studentname varchar2(10); --最好规定字符长度，否则老好报错
begin
select stuname into studentname from student where stuid = 1001;
dbms_output.put_line('要查找学生的名字为：' || studentname);
end;
--(2)
declare
studentnickname varchar2(10);
begin
select stunickname
into studentnickname
from student
where stuname = '刘德华';
dbms_output.put_line('要查找的学生的昵称是：' || studentnickname);
exception
when no_data_found then
dbms_output.put_line('要查找的学生不存在！');
when too_many_rows then
dbms_output.put_line('查出多个学生的信息，请通过查找学生的唯一学号来查学生信息');
when others then
dbms_output.put_line('查询时出错！');
end;
--(3) %type
declare
studentname student.stuname%type;
begin
select stuname into studentname from student where stuid = 1002;
dbms_output.put_line('要查找学生的名字为：' || studentname);
end;
--(4) %rowtype
declare
student_row student%rowtype;
begin
select * into student_row from student where stuid = 1003;
dbms_output.put_line('学号:' || student_row.stuid || '，姓名:' ||
student_row.stuname || '，昵称:' ||
student_row.stunickname || '，性别:' ||
student_row.stusex || '，年龄:' || student_row.stuage);
exception
when no_data_found then
dbms_output.put_line('要查找的学生不存在！');
when others then
dbms_output.put_line('查询时出错！');
end;
--(5)
declare
stu_row student%rowtype;
begin
select * into stu_row from student where stuid = '1001';
dbms_output.put_line('学号:' || stu_row.stuid || '，姓名:' || stu_row.stuname ||
'，昵称:' || stu_row.stunickname || '，性别:' ||
stu_row.stusex || '，年龄:' || stu_row.stuage);
if stu_row.stuname = '刘德华' then
dbms_output.put_line('该学生是大明星！');
end if;
end;
--(6)
declare
stu_row student%rowtype;
begin
select * into stu_row from student where stuid='1002';
dbms_output.put_line('学号:' || stu_row.stuid || '，姓名:' ||
stu_row.stuname || '，昵称:' ||
stu_row.stunickname || '，性别:' ||

stu_row.stusex || '，年龄:' || stu_row.stuage);
if stu_row.stuname ='刘德华' then
dbms_output.put_line('该学生是大明星！');
elsif stu_row.stuname='范冰冰' then
dbms_output.put_line('该学生是范爷大明星！');
else
dbms_output.put_line('该学生是未知大明星！');
end if;
end;
--(7)&
declare
yuju varchar2(64); --注意1：字符给个长度
begin
select &yuju into yuju from dual;
dbms_output.put_line('你输入的话为:'||yuju); --注意2：输入时记得加引号
end;
--(8)(记得写end if;)
declare
score number; --即：数字类型可以不规定长度，也可以给定长度如：score number(4)
begin
select &成绩 into score from dual;
dbms_output.put_line('成绩为:' || score);
if score >= 90 then
dbms_output.put_line('优秀');
elsif score >= 80 then
dbms_output.put_line('良好');
elsif score >= 60 then
dbms_output.put_line('及格');
else
dbms_output.put_line('不及格');
end if;
end;
--(9)
declare (注意：有声明的这种情况，记得写dengji='A'，case后不跟东西)
dengji varchar2(3);
begin
select &等级 into dengji from dual;
dbms_output.put_line('您输入的等级是:' || dengji);
case
when dengji='A' then
dbms_output.put_line('优秀');
when dengji='B' then
dbms_output.put_line('良好');
when dengji='C' then
dbms_output.put_line('中等');
when dengji='D' then
dbms_output.put_line('差');
else
dbms_output.put_line('没有您输入的等级！');
end case;
end;
--(10) (没声明的情况)
begin
case '&等级' --&等级 两边加引号，则输入时记得不加引号，如A
when 'A' then
dbms_output.put_line('优秀');
when 'B' then
dbms_output.put_line('良好');
when 'C' then
dbms_output.put_line('中等');
when 'D' then
dbms_output.put_line('差');
else
dbms_output.put_line('没有您输入的等级！');
end case;
end;
--(10)
begin
case &等级 ----&等级 两边不加引号，则输入时记得加引号，如'A'
when 'A' then
dbms_output.put_line('优秀');
end case;
end;
--case小总结
--记得写end case;
--&等级 两边不加引号，则输入时记得加引号，如'A'
--&等级 两边加引号，则输入时记得不加引号，如A
declare
score number(4);
begin
select &成绩 into score from dual;
case
when score >= 90 then
dbms_output.put_line('优秀');
when score >= 80 then
dbms_output.put_line('良好');
when score >= 60 then
dbms_output.put_line('中等');
else
dbms_output.put_line('差');
end case;
end;
--11 (loop)
declare
i number(8);
begin
i := 1; --注意：这样写要记得放在loop外面
loop
if i <= 10 then
dbms_output.put_line(i);
i:=i+1;
else
exit;
end if;
end loop

;
end;


declare
i number := 1;
begin
loop
if i <= 10 then
dbms_output.put_line(i);
i:=i+1;
else
exit;
end if;
end loop;
end;

declare
i number := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
if i > 10 then
exit;
end if; --记得写end if;呀！！！！
end loop;
end;
--12(while) (while i <= 10 loop)
declare
i number := 1;
begin
while i <= 10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
--13(for) (for i in 1 .. 10 loop)
declare
i number;
begin
for i in 1 .. 10 loop
dbms_output.put_line(i); --下面千万不要再写 i := i + 1;
end loop;
end;

--不声明
begin
for i in 1 .. 10 loop
dbms_output.put_line(i);
end loop;
end;

--14(for)(reverse)
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
--15(goto)
insert into student values(1005,'董卿','董主持','女',40);
select *from student;

declare
studentname student.stuname%type;
begin
select stuname into studentname from student where stuid = &stuid;
if studentname = '刘德华' then
goto mingxing;
elsif studentname = '董卿' then --注意：此处要用elsif不能用if
goto zhuchi;
end if;
<>
dbms_output.put_line('电影明星');
<>
dbms_output.put_line('主持明星');
end;
--16 存储过程
--空参存储过程
create or replace procedure insertstu is
begin
insert into student values(1006,'刘同','同同','男',28);
end;

begin
insertstu();
end;
select *from student;
--带参存储过程
create or replace procedure updatestuname(studnetid integer) is
begin
update student set stuname='刘德华2' where stuid=studnetid;
end;

begin
updatestuname(1004);
end;
select *from student;
--带输出参数的存储过程
create or replace procedure getstuname(studentid in integer,
studentname out varchar2) is
begin
select stuname into studentname from student where stuid = studentid;
end;

declare
xname varchar2(64);
begin
getstuname(1006, xname);
dbms_output.put_line('要查找学生的名字为:' || xname);
end;
--in/out模式的参数
create or replace procedure square(n in out integer) is
begin
n:=n**2;
end;

declare
x integer;
begin
x := 10;
square(x);
dbms_output.put_line('10的平方是:' || x);
end;
--17.自定义函数
create or replace function f_add(x integer, y integer) return integer is
s integer; --注意：这一句不要少了
begin
s := x + y;
return s;
end;

declare
ssum integer;
begin
ssum := f_add(3, 5);
dbms_output.put_line('和为:' || ssum);
end;

begin
dbms_output.put_line('3与5的和为:' || f_add(3, 5));
end;

--包、包体(别写错了，要多看 创建包与实现包的代码别弄混了)
--创建包
create or replace package demo1 as
function f_ji(

x integer, y integer) return integer;
end demo1;
--实现包（包体）
create or replace package body demo1 as
function f_ji(x integer,y integer) return integer is
ji integer;
begin
ji:=x*y;
return ji;
end f_ji;
end demo1;
--测试包
declare
ji integer;
begin
ji:=demo1.f_ji(3,5);
dbms_output.put_line('3与5的积是:'||ji);
end;
--注意：创建包，包体，实现包的代码，别混淆了