---------------------------------过程介绍----------------------------
--创建一个表
create table panchangwu_test(name varchar2(30), passwd
varchar2(30));
--创建过程
create or replace procedure panchangwu_pro1 is
begin
--执行部分
insert into panchangwu_test values('韩顺平', 'm1234');
end;
--调用过程
exec 过程名(参数1, 参数2);
call 过程名(参数1, 参数2);
--调用出错时查看错误信息
show error;
------------------------------PL/SQL块--------------------------------
--实例一,只包含执行部分的PL/SQL块
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
end;
--实例二,包含定义部分和执行部分的PL/SQL块
declare
v_ename varchar2(20); --定义字符串变量
begin
select name into v_ename from students where
num=&no;
dbms_output.put_line('姓名:' || v_ename);
end;
--实例三,包含定义、执行和例外处理部分
declare
--定义变量
v_name varchar2(20);
v_age number(3);
begin
select name,age into v_name,v_age from students where
num=&no;
dbms_output.put_line('用户名是: '||v_name||'工资 '||v_age);
exception
when no_data_found then
dbms_output.put_line('朋友,你的编号输入有误!');
end;
--案例四,根据学号修改年龄
create or replace procedure sp_pro2(no number, new_age number)
is
begin
update students set age=new_age where num=no;
end;
--案例五,创建函数
create function sp_fun1(fname varchar2)
return number is v_age number(5,2);
begin
select age*10+nvl(1.5,0) into v_age from students where
name=fname;
return v_age;
end;
--调用
var myage number
call sp_fun1('aaa') into :myage;
--------------------------------------控制过程----------------------------------
--案例一
--编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000
--就给该雇员工资增加10%
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spName;
--判断
if v_sal<2000 then
update emp set sal=sal*1.2 where ename=spName;
end if;
end;
--案例二
--编写一个过程,可以输入一个雇员名,如果该雇员名的补助不是0
--就在原来的基础上增加100,如果是。就设为200
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_comm emp.comm%type;
begin
--执行
select sal into v_comm from emp where ename=spName;
--判断
if v_comm<>0 then
update emp set sal=sal+100 where ename=spName;
else
update emp set sal=200 where ename=spName;
end if;
--案例三
--编写一个过程,可以输入一个雇员的编号,如果职位为PRESIDENT,工资就加1000
--如果是MANAGER,就加500,其他职位加200
create or replace procedure sp_pro6(spNo number) is
--定义
v_job emp.job%type;
begin
--执行
select job into v_job from emp where
empno=&spNo;
if v_job='PRESIENT' then
update emp set sal=sal+1000 where empno=spNo;
else if v_job='MANAGER'
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
--执行exec sp_pro6()
create table users1(userNo number, userName varchar2(40));
create or replace procedure sp_pro6(spName varchar2) is
v_num number:=1;
begin
loop
insert into
users1 values(v_num, spName);
--判断是否要退出循环
exit when
v_num=10;
v_num:=v_num+1;
end loop;
end;
exec sp_pro6('你好');
select * from users1;
------------------------------有输出和输出的存储过程----------------------
--建book表
create table book
(bookId number, bookName varchar2(50), publishHouse
varchar2(50))
--编写过程
--in 代表输入参数,out表示输出参数,默认为in
create or replace procedure sp_pro7
(spBookId in number, spbookName in varchar2, sppublishHouse in
varchar2) is
begin
insert into book values(spbookId, spbookName,
sppublishHouse);
end;
select * from book;
exec sp_pro7(001, '还珠格格', '人民出版社');
--有输出和输出的存储过程
create or replace procedure sp_pro8(spno in number, spName out
varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
select * from emp;
select * from dept;
create view myview as select emp.ename, emp.sal, dept.dname from
emp, dept;
--------------------pl/sql记录类型和表类型的使用-----------------------------
--pl/sql记录类型类似c语言的结构
declare
--定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title
type emp_record_type is record(name emp.ename%type, salary
emp.sal%type,
title emp.job%type)
--定义了一个sp_record变量,这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename, sal, job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name||'工资是:'||sp_record.salary);
end;
--pl/sql表类型类似c语言的数组http://blog.csdn.net/yaba213/article/details/4443590
declare
--定义了一个pl/sql表类型sp_table_type,该类型是用于存放emp.ename%type
--index by binary_integer表示下标是整数
type sp_table_type is table of emp.ename%type index by
binary_integer;
--定义了一个sp_table变量,这个变量的类型是sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_table(0));
end;
---------------------------游标-------------------------------------------
--请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工的姓名和薪水
declare
--定义游标
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor和一个select结合
open test_cursor for select ename, sal from emp where
deptno=&no;
--循环取出
loop fetch
test_cursor into v_ename, v_sal;
--判断游标是否为空退出
exit when
test_cursor%notfound;
dbms_output.put_line('名字:'v_ename||'工资'||v_sal);
end loop;
--关闭游标
close test_cursor;
end;
---------------------------------oracle的分页--------------------
select t1.*, rownum rn from (select * from emp) t1
select t1.*, rownum rn from (select * from emp) t1 where
rownum<=10
--以下可以作为分页模板
select * from
(select t1.*, rownum rn from (select * from emp) t1 where
rownum<=10)
where rn >= 6;
----------------------oracle例外处理---------------------
--自定义例外
create or replace procedure sp_extest(myage number)
is
--定义一个例外
myex exception;
begin
update students set age=age+20 where age=myage
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('没有更新'||103);