-----------------写plsql代码段，把当前用户下所有约束都删除
declare
cursor cur is select constraint_name,table_name from user_constraints;
str varchar(200);
begin
for i in cur loop
str:= ' alter table '||i.table_name|| ' drop constraint ' ||i.constraint_name;
execute immediate str;
end loop;
end;
/
-----------------------打印所有选课的学生名，年龄，系
-----------------打印年龄为16的学生。
declare
v_sname student.sname%type;
v_ssex student.ssex%type;
v_sage student.sage%type;
begin
select sname,ssex,sage into v_sname,v_ssex,v_sage
from student
where sage=16;
dbms_output.put_line(v_sname||v_ssex||v_sage);
end;
/
---------------异常处理
错误分两种:编译时错误和运行时错误
运行时错误叫异常
declare
begin
exception
when 异常名 then
语句;
when 异常名 then
语句;
.....
when others then
语句;
end
-------预定义异常
oracle把某些错误编号命名
too_many_rows 返回行数过多（-01422）
no_data_found 无数据返回（-01403）
declare
v_sname student.sname%type;
v_ssex student.ssex%type;
v_sage student.sage%type;
begin
select sname,ssex,sage into v_sname,v_ssex,v_sage
from student
where sage=16;
dbms_output.put_line(v_sname||v_ssex||v_sage);
exception
when too_many_rows then
dbms_output.put_line('返回的数据过多，请使用游标');
when no_data_found then
dbms_output.put_line('无数据返回');
when others then
dbms_output.put_line('其他未知错误');
end;
/
------非预定义异常
pragma exception_init(异常名,错误号)---将错误编号和异常名绑定
SQL> create table temp
2 (id number,
3 name varchar2(20) not null,
4 sex varchar2(20),
5 primary key(id),
6 check(sex in ('男','女'))
7 );
表已创建。
ORA-02290: 违反检查约束条件 (ZOU.SYS_C005490)
ORA-01400: 无法将 NULL 插入 ("ZOU"."TEMP"."NAME")
ORA-00001: 违反唯一约束条件 (ZOU.SYS_C005491)
declare
e_check exception;
e_null exception;
e_unique exception;
pragma exception_init(e_check,-02290);
pragma exception_init(e_null,-01400);
pragma exception_init(e_unique,-00001);
begin
insert into temp
values(1,'张三','男');
exception
when e_check then
dbms_output.put_line('哥们只能插入男女');
when e_null then
dbms_output.put_line('id和姓名不能为null');
when e_unique then
dbms_output.put_line('id重复了');
when others then
dbms_output.put_line('其他错误');
end;
/
------自定义异常
定义异常-----抛出异常-----处理异常
declare
str varchar2(200):='大学里有搞对象的，在食堂互相喂饭，有不正当行为';
e_error exception; -----定义异常
begin
if str like '%搞对象%' or str like '%喂饭%' then
raise e_error;-------抛出异常
else
dbms_output.put_line('这学生还行，至少没有生活错误');
end if;
exception----------处理异常
when e_error then
dbms_output.put_line('这学生应该开除了，在学校处对象，相当近亲结婚没有发展');
when others then
dbms_output.put_line('其他错误');
end;
/
--------查询King的工资，如果超出20000则视为自定义异常，打印工资过高
-----------------------存储过程
在数据库端，经过编译的plsql命名段
---------------------把所有男生信息打印出来
declare
cursor cur is select sname,ssex,sage from student where ssex='男';
v_stu cur%rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur%notfound;
dbms_output.put_line(v_stu.sname||v_stu.ssex||v_stu.sage);
end loop;
close cur;
end;
/
--------------不带参数的存储过程
create or replace procedure p1
as
cursor cur is select sname,ssex,sage from student where ssex='男';
v_stu cur%rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur%notfound;
dbms_output.put_line(v_stu.sname||v_stu.ssex||v_stu.sage);
end loop;
close cur;
end;
/
exec p1;
SQL> show error;---显示错误信息
-------------------把工资最高的前三个人的姓名，工资，雇佣日期打印出来
create or replace procedure p2
as
cursor cur is select first_name,salary,hire_date
from(select * from employees order by salary desc)
where rownum<4;
v_emp cur%rowtype;
begin
open cur;
loop
fetch cur into v_emp;
exit when cur%notfound;
dbms_output.put_line(v_emp.first_name||v_emp.salary||v_emp.hire_date);
end loop;
close cur;
end;
/
exec p2;
--------------------带参数的存储过程
create or replace procedure p3(v_ssex student.ssex%type)
as
cursor cur is select sname,ssex,sage from student where ssex=v_ssex;
v_stu cur%rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur%notfound;
dbms_output.put_line(v_stu.sname||v_stu.ssex||v_stu.sage);
end loop;
close cur;
end;
/
exec p3('男');
exec p3('女');
-------------------输入n，实现把前n个人的姓名，工资，雇佣日期打印出来
create or replace procedure p4(n number)
as
cursor cur is select first_name,salary,hire_date
from(select * from employees order by salary desc)
where rownum<=n;
v_emp cur%rowtype;
v_n number;
v_error exception;----定义异常
begin
select count(*) into v_n from employees;
if n<=0 or n>v_n then
raise v_error;--------抛出异常
end if;
open cur;
loop
fetch cur into v_emp;
exit when cur%notfound;
dbms_output.put_line(v_emp.first_name||v_emp.salary||v_emp.hire_date);
end loop;
close cur;
exception
when v_error then
dbms_output.put_line('你输入有误');
when others then
dbms_output.put_line('其他错误');
end;
/
exec p4(3);
---------------------
create or replace procedure p5(v_sdept student.sdept%type,v_ssex student.ssex%type)
as
cursor cur is select sname from student where sdept=v_sdept and ssex=v_ssex;
v_sname student.sname%type;
begin
open cur;
loop
fetch cur into v_sname;
exit when cur%notfound;
dbms_output.put_line(v_sname);
end loop;
close cur;
end;
/
exec p5('计算机','男');
exec p5(v_ssex=>'男',v_sdept=>'计算机');
注意:存储过程在定义参数的时候只写类型，不能写长度
----------------------存储过程可以实现插入
create or replace procedure p6(v_sno student.sno%type,
v_sname student.sname%type,
v_ssex student.ssex%type,
v_sage student.sage%type,
v_sdept student.sdept%type)
as
begin
insert into student
values(v_sno,v_sname,v_ssex,v_sage,v_sdept);
commit;
end;
/
exec p6('1','张三','男',12,'计算机');
--------------通过过程p7实现输入学号，删除学生，如果没有打印没有这个人
create or replace procedure p7(v_sno student.sno%type)
as
begin
delete from student where sno=v_sno;
if sql%rowcount=0 then
dbms_output.put_line('查无此人，请重新输入');
else
dbms_output.put_line('删除成功');
end if;
end;
/
exec p7('1');
create or replace procedure p7(v_sno student.sno%type)
as
begin
delete from student where sno=v_sno;
if sql%notfound then
dbms_output.put_line('查无此人，请重新输入');
else
dbms_output.put_line('删除成功');
end if;
end;
/
---------------------带输出参数的存储过程
create or replace procedure p12(v_sno sc.sno%type,v_n out number)
as
begin
select count(*) into v_n from sc where sno=v_sno;
end;
/
-----------输入学号，打印是否可以入党
入党条件
选课数必须大于3
create or replace procedure p11(v_sno sc.sno%type)
as
n number;
begin
p12(v_sno, n );
if n>3 then
dbms_output.put_line('可以入党');
else
dbms_output.put_line('不可以入党');
end if;
end;
/
exec p11('2000012');
--------------------以下代码逻辑全对，就是在参数列表中的varchar2应该改成student.sno%type
create or replace procedure ed(v_sno varchar2)
as
cursor cur is select * from student where sno=v_sno;
v_stu cur%rowtype;
v_error exception;
begin
open cur;
fetch cur into v_stu;
while cur%notfound loop
raise v_error;
end loop;
delete from student where sno=v_sno;
close cur;
exception
when v_error then
dbms_output.put_line('没有此人');
when others then
dbms_output.put_line('其他错误');
end;
/
-----------------编写函数，计算平方
注意:函数的参数列表只写类型，return后面只写返回类型，不能写长度
create or replace function f1(n number)
return number
as
m number;
begin
m:=n*n;
return m;
end;
/
select f1(2) from dual;
------------------输入员工号，返回年薪
create or replace function f2(v_id employees.employee_id%type)
return number
as
m number;
begin
select salary*12+salary*12*nvl(commission_pct,0) into m
from employees
where employee_id=v_id;
return m;
end;
/
SQL> select first_name,salary*12+salary*12*nvl(commission_pct,0)
2 from employees;
SQL> select first_name,f2(employee_id)
2 from employees;
---------------------输入学号，返回姓名
create or replace function f2(v_sno student.sno%type)
return student.sname%type
as
v_sname student.sname%type;
begin
select sname into v_sname from student where sno=v_sno;
return v_sname;
end;
/
SQL> select sname,cno,grade
2 from student,sc
3 where student.sno=sc.sno;
SNAME CNO GRADE
-------- ---- ----------
王林 1024 80
王林 1136 78
王林 1137 70
王林 1156 80
葛波 1024 88
葛波 1136 90
葛波 1156 88
张大民 1156 89
顾芳 1137 77
顾芳 1156 93
姜凡 1137 89
已选择11行。
SQL> column F2(SNO) format a10;---设置F2(SNO)显示宽度为10个字符
SQL> select f2(sno),cno,grade
2 from sc;
F2(SNO) CNO GRADE
---------- ---- ----------
王林 1024 80
王林 1136 78
王林 1137 70
王林 1156 80
葛波 1024 88
葛波 1136 90
葛波 1156 88
张大民 1156 89
顾芳 1137 77
顾芳 1156 93
姜凡 1137 89
已选择11行。
