oracle plsql编程实例,关于oracle的pl/sql编程例子合辑-CSDN博客

---------------------------------过程介绍----------------------------

--创建一个表

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)

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)

--定义一个例外

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);