Oracle 10g中DEPT、EMP脚本以及过程和函数的建立和调用
分享这个脚本,主要是因为一本书《精通Oracle10g PL SQL编程》。在这本书中,离不开这两张表。
create table dept
(deptno number(2)constraint pkdept primary key,
dname varchar2(14),
loc varchar2(13));
代码
create table emp(empno number(4)constraint pkemp primary key,ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2)constraint fkdeptno references dept);两张表建立后,下面向表中添加数据
代码
insert into dept values(10,“accounting“,“new york“);insert into dept values(20,“research“,“dallas“);insert into dept values(30,“sales“,“chicago“);insert into dept values(40,“operations“,“boston“);代码
insert into emp values(7369,“smith“,“clerk“,7902,todate(“17-12-1980“,“dd-mm-yyyy“),800,null,20);insert into emp values(7499,“allen“,“salesman“,7698,todate(“20-2-1981“,“dd-mm-yyyy“),1600,300,30);insert into emp values(7521,“ward“,“salesman“,7698,todate(“22-2-1981“,“dd-mm-yyyy“),1250,500,30);insert into emp values(7566,“jones“,“manager“,7839,todate(“2-4-1981“,“dd-mm-yyyy“),2975,null,20);insert into emp values(7654,“martin“,“salesman“,7698,todate(“28-9-1981“,“dd-mm-yyyy“),1250,1400,30);insert into emp values(7698,“blake“,“manager“,7839,todate(“1-5-1981“,“dd-mm-yyyy“),2850,null,30);insert into emp values(7782,“clark“,“manager“,7839,todate(“9-6-1981“,“dd-mm-yyyy“),2450,null,10);insert into emp values(7788,“scott“,“analyst“,7566,todate(“13-7-87“,“dd-mm-rr“)-85,3000,null,20);insert into emp values(7839,“king“,“president“,null,todate(“17-11-1981“,“dd-mm-yyyy“),5000,null,10);insert into emp values(7844,“turner“,“salesman“,7698,todate(“8-9-1981“,“dd-mm-yyyy“),1500,0,30);insert into emp values(7876,“adams“,“clerk“,7788,todate(“13-7-87“,“dd-mm-rr“)-51,1100,null,20);insert into emp values(7900,“james“,“clerk“,7698,todate(“3-12-1981“,“dd-mm-yyyy“),950,null,30);insert into emp values(7902,“ford“,“analyst“,7566,todate(“3-12-1981“,“dd-mm-yyyy“),3000,null,20);insert into emp values(7934,“miller“,“clerk“,7782,todate(“23-1-1982“,“dd-mm-yyyy“),1300,null,10);现在基本的准备工作已经完成,想要学习Oracle开发知识就可以基于这两张表进行练习。今天想对我学习的过程和函数小结一下,那么它们的含义是什么呢？其中过程用于执行特定操作,函数则用于返回特定数据。
过程的优点：
1）只在创建时进行编译,提高数据库执行速度,减少网络通信量。
2）对数据库进行复杂操作时,可将此复杂操作用过程封装起来与数据库提供的事务处理结合一起使用。
3）可以重复使用,减少数据库开发人员的工作量。
4）安全性高,可设定只有某些用户才具有对指定过程的使用权。
5）分布式工作。应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。
过程语法：
create[or replace]procedure procedurename
(argument1[mode1]datatype1,argument2[mode2]datatype2,...)
is[as]
PL/SQL Block;
procedurename用于指定过程名称；argument1,argument2等则用于指定过程的参数；is或as用于开始一个PL/SQL块。当指定参数数据类型时,不能指定其长度。当建立过程时,既可以指定输入参数（in）,也可以指定输出参数（out）及输入输出参数（in out）。如果不指定参数模式,则默认为输入参数；如果要定义输出参数,那么需要指定out关键字；如果要定义输入输出参数,则需要指定in out关键字。
——建立过程,不带任何参数
create or replace procedure outtime isbegin dbmsoutput.putline(sysdate);end;call outtime();
在PL/SQL中调用exec outtime会出现这个Error
请在Oracle SQL*Plus中运行,或者使用命令行提示符。
——建立过程,带有in参数
代码
create or replace procedure addemployee(eno number,name varchar2,sal number,job varchar2default“clerk“,dno number)is eintegrity exception;pragma EXCEPTIONINIT(eintegrity,-2291);begin insert into emp(empno,ename,sal,job,deptno)values(eno,name,sal,job,dno);exception when DUPVALONINDEX then RAISEAPPLICATIONERROR(-20000,“雇员号不能重复“);when eintegrity then RAISEAPPLICATIONERROR(-20001,“部门号不存在“);end;——建立过程,带有out参数
代码
create or replace procedure queryemployee(eno number,name out varchar2,salary out number)isbegin select ename,sal into name,salary from emp where empno=eno;exception when NODATAFOUND then RAISEAPPLICATIONERROR(-20000,“该雇员不存在“);end;
——建立过程,带有in out参数
create or replace procedure comp(num1in out number,num2in out number)is
v1number;
v2number;
begin
v1：=num1/num2;
v2：=mod(num1,num2);
num1：=v1;
num2：=v2;
end;
——参数传递变量和数据
代码
create or replace procedure adddept(dno number,dname varchar2default null,loc varchar2default null)isbegin insert into dept values(dno,dname,loc);exception when DUPVALONINDEX then RAISEAPPLICATIONERROR(-20000,“部门号不能重复“);end;位置传递
exec adddept(50,“sales“,“new york“);exec adddept(60);exec adddept(70,“admin“);名称传递
exec adddept(dname=>“sales“,dno=>50);组合传递
exec adddept(60,dname=“sales“,loc=“new york“);函数语法：
create[or replace]function functionname
(argument1[mode1]datatype1,
argument2[mode2]datatype2,
...)
return datatype
is|as
PL/SQL Block;
functionname用于指定函数名称；argument1、argument2等则用于指定函数的参数,同过程一样,当指定参数数据类型时,不能指定其长度；return子句用于指定函数返回值的数据类型；is或as用于开始一个PL/SQL块。当建立函数时,在函数头部必须要带有return子句,在函数体内至少要包含一条return语句。另外,我们既可以指定输入参数（in）,也可以指定输出参数（out）及输入输出参数（in out）。
——建立函数,不带任何参数
create or replace function getuser return varchar2is vuser varchar2(100);begin select username into vuser from userusers;return vuser;end;使用变量接收函数返回值
在SQL语句中直接调用函数
select getuser from dual;——建立函数,带有in参数
代码
create or replace function getsal(name in varchar2)return number is vsal emp.sal%type;begin select sal into vsal from emp where upper(ename)=upper(name);return vsal;exception when nodatafound then raiseapplicationerror(-20000,“该雇员不存在“);end;——建立函数,带有out参数
代码
create or replace function getinfo(name varchar2,title out varchar2)return varchar2is deptname dept.dname%type;begin select a.job,b.dname into title,deptname from emp a,dept b where a.deptno=b.deptno and upper(a.ename)=upper(name);return deptname;exception when nodatafound then raiseapplicationerror(-20000,“该雇员不存在“);end;
——建立函数,带有in out参数
代码
create or replace function resu(num1number,num2in out number)return number is vresult number(6);vremaind number;begin vresult：=num1/num2;vremaind：=-mod(num1,num2);num2：=vremaind;return vresult;exception when zerodivide then raiseapplicationerror(-20000,“不能除0“);end;