Oracle 10g中DEPT、EMP脚本以及过程和函数的建立和调用

分享这个脚本，主要是因为一本书《精通Oracle 10g PL SQL编程》。在这本书中，离不开这两张表。

 

 

create table dept (deptno number ( 2 ) constraint pk_dept primary key , dname varchar2 ( 14 ) , loc varchar2 ( 13 ) ) ;

 

 

 

代码

create table emp (empno number ( 4 ) constraint pk_emp 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 fk_deptno 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 ,to_date( ' 17-12-1980 ' , ' dd-mm-yyyy ' ), 800 , null , 20 ); insert into emp values ( 7499 , ' allen ' , ' salesman ' , 7698 ,to_date( ' 20-2-1981 ' , ' dd-mm-yyyy ' ), 1600 , 300 , 30 ); insert into emp values ( 7521 , ' ward ' , ' salesman ' , 7698 ,to_date( ' 22-2-1981 ' , ' dd-mm-yyyy ' ), 1250 , 500 , 30 ); insert into emp values ( 7566 , ' jones ' , ' manager ' , 7839 ,to_date( ' 2-4-1981 ' , ' dd-mm-yyyy ' ), 2975 , null , 20 ); insert into emp values ( 7654 , ' martin ' , ' salesman ' , 7698 ,to_date( ' 28-9-1981 ' , ' dd-mm-yyyy ' ), 1250 , 1400 , 30 ); insert into emp values ( 7698 , ' blake ' , ' manager ' , 7839 ,to_date( ' 1-5-1981 ' , ' dd-mm-yyyy ' ), 2850 , null , 30 ); insert into emp values ( 7782 , ' clark ' , ' manager ' , 7839 ,to_date( ' 9-6-1981 ' , ' dd-mm-yyyy ' ), 2450 , null , 10 ); insert into emp values ( 7788 , ' scott ' , ' analyst ' , 7566 ,to_date( ' 13-7-87 ' , ' dd-mm-rr ' ) - 85 , 3000 , null , 20 ); insert into emp values ( 7839 , ' king ' , ' president ' , null ,to_date( ' 17-11-1981 ' , ' dd-mm-yyyy ' ), 5000 , null , 10 ); insert into emp values ( 7844 , ' turner ' , ' salesman ' , 7698 ,to_date( ' 8-9-1981 ' , ' dd-mm-yyyy ' ), 1500 , 0 , 30 ); insert into emp values ( 7876 , ' adams ' , ' clerk ' , 7788 ,to_date( ' 13-7-87 ' , ' dd-mm-rr ' ) - 51 , 1100 , null , 20 ); insert into emp values ( 7900 , ' james ' , ' clerk ' , 7698 ,to_date( ' 3-12-1981 ' , ' dd-mm-yyyy ' ), 950 , null , 30 ); insert into emp values ( 7902 , ' ford ' , ' analyst ' , 7566 ,to_date( ' 3-12-1981 ' , ' dd-mm-yyyy ' ), 3000 , null , 20 ); insert into emp values ( 7934 , ' miller ' , ' clerk ' , 7782 ,to_date( ' 23-1-1982 ' , ' dd-mm-yyyy ' ), 1300 , null , 10 );

 

 

现在基本的准备工作已经完成，想要学习Oracle开发知识就可以基于这两张表进行练习。今天想对我学习的过程和函数小结一下，那么它们的含义是什么呢？其中过程用于执行特定操作，函数则用于返回特定数据。

 

过程的优点：

1）只在创建时进行编译，提高数据库执行速度，减少网络通信量。

2）对数据库进行复杂操作时，可将此复杂操作用过程封装起来与数据库提供的事务处理结合一起使用。

3）可以重复使用，减少数据库开发人员的工作量。

4）安全性高，可设定只有某些用户才具有对指定过程的使用权。

5）分布式工作。应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

 

过程语法：

create [or replace] procedure procedure_name

    (argument1 [mode1] datatype1, argument2 [mode2] datatype2,...)

is[as]

PL/SQL Block;

procedure_name用于指定过程名称；argument1，argument2等则用于指定过程的参数；is或as用于开始一个 PL/SQL块。当指定参数数据类型时，不能指定其长度。当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）及输入输出参数（in out）。如果不指定参数模式，则默认为输入参数；如果要定义输出参数，那么需要指定out关键字；如果要定义输入输出参数，则需要指定in out关键字。

 

－－建立过程，不带任何参数

 

create or replace procedure out_time is begin dbms_output.put_line(sysdate); end ;

 

 

call out_time();

在PL/SQL中调用exec out_time会出现这个Error

请在Oracle SQL*Plus中运行，或者使用命令行提示符。

 

－－建立过程，带有in参数

 

代码

create or replace procedure add_employee(eno number , name varchar2 , sal number , job varchar2 default ' clerk ' , dno number ) is e_integrity exception; pragma EXCEPTION_INIT(e_integrity, - 2291 ); begin insert into emp (empno, ename, sal, job, deptno) values (eno, name, sal, job, dno); exception when DUP_VAL_ON_INDEX then RAISE_APPLICATION_ERROR( - 20000 , ' 雇员号不能重复 ' ); when e_integrity then RAISE_APPLICATION_ERROR( - 20001 , ' 部门号不存在 ' ); end ;

 

－－建立过程，带有out参数

 

代码

create or replace procedure query_employee(eno number , name out varchar2 , salary out number ) is begin select ename, sal into name, salary from emp where empno = eno; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR( - 20000 , ' 该雇员不存在 ' ); end ;

 

 

 

－－建立过程，带有in out参数

 

create or replace procedure comp(num1 in out number , num2 in out number ) is v1 number ; v2 number ; begin v1 : = num1 / num2; v2 : = mod(num1, num2); num1 : = v1; num2 : = v2; end ;

 

 

 

－－参数传递变量和数据

 

代码

create or replace procedure add_dept(dno number , dname varchar2 default null , loc varchar2 default null ) is begin insert into dept values (dno, dname, loc); exception when DUP_VAL_ON_INDEX then RAISE_APPLICATION_ERROR( - 20000 , ' 部门号不能重复 ' ); end ;

 

位置传递

 

 

exec add_dept( 50 , ' sales ' , ' new york ' ); exec add_dept( 60 ); exec add_dept( 70 , ' admin ' );

 

名称传递

 

 

exec add_dept(dname => ' sales ' ,dno => 50 );

 

组合传递

 

 

exec add_dept( 60 ,dname = ' sales ' ,loc = ' new york ' );

 

 

函数语法：

create [or replace] function function_name

         (argument1 [mode1] datatype1,

          argument2 [mode2] datatype2,

          ...)

return datatype

is | as

PL/SQL Block;

function_name用于指定函数名称；argument1、argument2等则用于指定函数的参数，同过程一样，当指定参数数据类型 时，不能指定其长度；return子句用于指定函数返回值的数据类型；is或as用于开始一个PL/SQL块。当建立函数时，在函数头部必须要带有 return子句，在函数体内至少要包含一条return语句。另外，我们既可以指定输入参数（in），也可以指定输出参数（out）及输入输出参数 （in out）。

 

－－建立函数，不带任何参数

 

create or replace function get_user return varchar2 is v_user varchar2 ( 100 ); begin select username into v_user from user_users; return v_user; end ;

 

使用变量接收函数返回值

 

在SQL语句中直接调用函数

 

select get_user from dual;

 

 

－－建立函数，带有in参数

 

代码

create or replace function get_sal(name in varchar2 ) return number is v_sal emp.sal % type; begin select sal into v_sal from emp where upper (ename) = upper (name); return v_sal; exception when no_data_found then raise_application_error( - 20000 , ' 该雇员不存在 ' ); end ;

 

 

－－建立函数，带有out参数

 

代码

create or replace function get_info(name varchar2 , title out varchar2 ) return varchar2 is 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 no_data_found then raise_application_error( - 20000 , ' 该雇员不存在 ' ); end ;

 

 

 

－－建立函数，带有in out参数

 

代码

create or replace function resu(num1 number , num2 in out number ) return number is v_result number ( 6 ); v_remaind number ; begin v_result : = num1 / num2; v_remaind : = - mod(num1, num2); num2 : = v_remaind; return v_result; exception when zero_divide then raise_application_error( - 20000 , ' 不能除0 ' ); end ;