oracle中的sign函数详解

第一篇：oracle中的sign函数详解

sign

在Oracle/PLSQL中, sign 函数返回一个数字的正负标志.语法如下：sign(number)number 要测试标志的数字.If number < 0, then sign returns-1.If number = 0, then sign returns 0.If number > 0, then sign returns 1.应用于:  Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g 例如: sign(-23)would return-1 sign(0.001)would return-1 sign(0)would return 0 sign(0.001)would return 1 sign(23)would return 1 sig(23.601)would return 1

第二篇：Oracle创建函数和过程

ORACLE PL/SQL编程之六：把过程与函数说透(穷追猛打，把根儿都拔起!)

继上篇：ORACLE PL/SQL编程之八：把触发器说透 得到了大家的强力支持，感谢。接下来再下猛药，介绍下一篇，大家一定要支持与推荐呀~！我也才有动力写后面的。

本篇主要内容如下：

6.1 引言 6.2 创建函数 6.3 存储过程 6.3.1 创建过程 6.3.2 调用存储过程 6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 开发存储过程步骤 6.3.6 删除过程和函数 6.3.7 过程与函数的比较

6.1 引言

过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：

1． 创建存储过程和函数。2． 正确使用系统级的异常处理和用户定义的异常处理。

3． 建立和管理存储过程和函数。

6.2 创建函数

1.创建函数

语法如下：

CREATE [OR REPLACE] FUNCTION function_name(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],......[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])[ AUTHID DEFINER | CURRENT_USER ] RETURN return_type IS | AS

<类型.变量的声明部分> BEGIN

执行部分

RETURN expression EXCEPTION 异常处理部分 END function_name;

 IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。

 一般，只有在确认function_name函数

是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。

例1.获取某部门的工资总和：

--获取某部门的工资总和 CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER, Emp_count OUT NUMBER)RETURN NUMBER IS

V_sum NUMBER;BEGIN

SELECT SUM(SALARY), count(*)INTO V_sum, emp_count FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;RETURN v_sum;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END get_salary;

2.函数的调用

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

第一种参数传递格式：位置表示法。

即在调用时按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来进行传递。用这种方法进行调用，形参与实参的名称是相互独立，没有关系，强调次序才是重要的。

格式为：

argument_value1[,argument_value2 …]

例2：计算某部门的工资总和：

DECLARE

V_num NUMBER;V_sum NUMBER;BEGIN

V_sum :=get_salary(10, v_num);DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);END;

第二种参数传递格式：名称表示法。

即在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。这种方法，形参与实参的名称是相互独立的，没有关系，名称的对应关系才是最重要的，次序并不重要。

格式为：

argument => parameter [,…]

其中：argument 为形式参数，它必须与函数定

义时所声明的形式参数名称相同parameter 为实际参数。

在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。例3：计算某部门的工资总和：

DECLARE

V_num NUMBER;V_sum NUMBER;BEGIN

V_sum :=get_salary(emp_count => v_num, dept_no => 10);DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);END;

第三种参数传递格式：组合传递。

即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

例4：

CREATE OR REPLACE FUNCTION demo_fun(Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似 Age INTEGER, Sex VARCHAR2)RETURN VARCHAR2 AS

V_var VARCHAR2(32);BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;RETURN v_var;END;

DECLARE

Var VARCHAR(32);BEGIN

Var := demo_fun('user1', 30, sex => '男');DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40, sex => '男');DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);DBMS_OUTPUT.PUT_LINE(var);END;

无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

3.参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

例5：

CREATE OR REPLACE FUNCTION demo_fun(Name VARCHAR2, Age INTEGER, Sex VARCHAR2 DEFAULT '男')RETURN VARCHAR2 AS

V_var VARCHAR2(32);BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;RETURN v_var;END;

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

DECLARE

var VARCHAR(32);BEGIN

Var := demo_fun('user1', 30);DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40);DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);END;6.3 存储过程

6.3.1 创建过程

建立存储过程

在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.创建过程语法:

CREATE [OR REPLACE] PROCEDURE procedure_name([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],......[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])[ AUTHID DEFINER | CURRENT_USER ] { IS | AS } <声明部分> BEGIN

<执行部分> EXCEPTION <可选的异常错误处理程序> END procedure_name;

说明：相关参数说明参见函数的语法说明。

例6．用户连接登记记录；

CREATE TABLE logtable(userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

例7．删除指定员工记录；

CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN employees.employee_id%TYPE)AS

No_result EXCEPTION;BEGIN

DELETE FROM employees WHERE employee_id = v_empno;IF SQL%NOTFOUND THEN RAISE no_result;END IF;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');EXCEPTION WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END DelEmp;

例8．插入员工记录:

CREATE OR REPLACE PROCEDURE InsertEmp(v_empno in employees.employee_id%TYPE, v_firstname in employees.first_name%TYPE, v_lastname in employees.last_name%TYPE, v_deptno in employees.department_id%TYPE)AS

empno_remaining EXCEPTION;PRAGMA EXCEPTION_INIT(empno_remaining,-1);/*-1 是违反唯一约束条件的错误代码 */ BEGIN

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);DBMS_OUTPUT.PUT_LINE('温馨提示:插入数据记录成功!');EXCEPTION WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END InsertEmp;

例9．使用存储过程向 departments表中插入数据。CREATE OR REPLACE PROCEDURE insert_dept(v_dept_id IN departments.department_id%TYPE, v_dept_name IN departments.department_name%TYPE, v_mgr_id IN departments.manager_id%TYPE, v_loc_id IN departments.location_id%TYPE)IS

ept_null_error EXCEPTION;PRAGMA EXCEPTION_INIT(ept_null_error,-1400);ept_no_loc_id EXCEPTION;PRAGMA EXCEPTION_INIT(ept_no_loc_id,-2291);BEGIN

INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id);DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功');EXCEPTION WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复');WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空');WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR(-20002, '没有该地点');END insert_dept;

/*调用实例一: DECLARE ept_20000 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20000,-20000);ept_20001 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20001,-20001);ept_20002 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20002,-20002);BEGIN insert_dept(300, '部门300', 100, 2400);insert_dept(310, NULL, 100, 2400);insert_dept(310, '部门310', 100, 900);EXCEPTION WHEN ept_20000 THEN DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');WHEN ept_20001 THEN DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');WHEN ept_20002 THEN DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');END;

调用实例二： DECLARE ept_20000 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20000,-20000);ept_20001 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20001,-20001);ept_20002 EXCEPTION;PRAGMA EXCEPTION_INIT(ept_20002,-20002);BEGIN insert_dept(v_dept_name => '部门310', v_dept_id => 310, v_mgr_id => 100, v_loc_id => 2400);insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900);EXCEPTION WHEN ept_20000 THEN DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');WHEN ept_20001 THEN DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');WHEN ept_20002 THEN DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');END;*/

6.3.2 调用存储过程

存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：

EXEC[UTE] procedure_name(parameter1, parameter2…);

例10： EXECUTE logexecution;

例11：查询指定员工记录；

CREATE OR REPLACE PROCEDURE QueryEmp(v_empno IN employees.employee_id%TYPE, v_ename OUT employees.first_name%TYPE, v_sal OUT employees.salary%TYPE)AS BEGIN

SELECT last_name || last_name, salary INTO v_ename, v_sal FROM employees WHERE employee_id = v_empno;DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!');EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END QueryEmp;--调用

DECLARE

v1 employees.first_name%TYPE;v2 employees.salary%TYPE;BEGIN

QueryEmp(100, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);QueryEmp(103, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);QueryEmp(104, v1, v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工资:'||v2);END;

例12．计算指定部门的工资总和，并统计其中的职工数量。

CREATE OR REPLACE PROCEDURE proc_demo(dept_no NUMBER DEFAULT 10, sal_sum OUT NUMBER, emp_count OUT NUMBER)IS BEGIN

SELECT SUM(salary), COUNT(*)INTO sal_sum, emp_count FROM employees WHERE department_id = dept_no;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END proc_demo;

DECLARE V_num NUMBER;V_sum NUMBER(8, 2);BEGIN

Proc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);Proc_demo(sal_sum => v_sum, emp_count => v_num);DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);END;

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

例13：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；

DECLARE V_num NUMBER;V_sum NUMBER(8, 2);PROCEDURE proc_demo(Dept_no NUMBER DEFAULT 10, Sal_sum OUT NUMBER, Emp_count OUT NUMBER)IS BEGIN

SELECT SUM(salary), COUNT(*)INTO sal_sum, emp_count FROM employees WHERE department_id=dept_no;EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END proc_demo;--调用方法： BEGIN

Proc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);Proc_demo(sal_sum => v_sum, emp_count => v_num);DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);END;

6.3.3 AUTHID 过程中的AUTHID 指令可以告诉ORACLE，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行．

例14：建立过程，使用

Connect HR/qaz DROP TABLE logtable;CREATE table logtable(userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution AUTHID DEFINER IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT / AS SYSDBA GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;

CONNECT testuser1/userpwd1 INSERT INTO HR.LOGTABLE VALUES(USER, SYSDATE);EXECUTE HR.logexecution

AUTOID DEFINER； CONNECT HR/qaz SELECT * FROM HR.logtable;

例15：建立过程，使用

CONNECT HR/qaz

CREATE OR REPLACE PROCEDURE logexecution AUTHID CURRENT_USER IS BEGIN

INSERT INTO logtable(userid, logdate)VALUES(USER, SYSDATE);END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT testuser1/userpwd1 INSERT INTO HR.LOGTABLE VALUES(USER, SYSDATE);EXECUTE HR.logexecution

AUTOID CURRENT_USER；

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．

例16：建立过程，使用自动事务处理进行日志记录；

DROP TABLE logtable;

CREATE TABLE logtable(Username varchar2(20), Dassate_time date, Mege varchar2(60));

CREATE TABLE temp_table(N number);

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)AS PRAGMA AUTONOMOUS_TRANSACTION;BEGIN

INSERT INTO logtable VALUES(user, sysdate, p_message);COMMIT;END log_message;BEGIN

Log_message(‘About to insert into temp_table‘);

INSERT INTO temp_table VALUES(1);Log_message(‘Rollback to insert into temp_table‘);

ROLLBACK;END;

SELECT * FROM logtable;SELECT * FROM temp_table;

例17：建立过程，没有使用自动事务处理进行日志记录；

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)AS BEGIN

INSERT INTO logtable VALUES(user, sysdate, p_message);COMMIT;END log_message;BEGIN

Log_message('About to insert into temp_table');INSERT INTO temp_table VALUES(1);Log_message('Rollback to insert into temp_table');ROLLBACK;END;

SELECT * FROM logtable;SELECT * FROM temp_table;

6.3.5 开发存储过程步骤

开发存储过程、函数、包及触发器的步骤如下：

6.3.5.1 使用文字编辑处理软件编辑存储过程源码 使用文字编辑处理软件编辑存储过程源码，要用类似WORD 文字处理软件进行编辑时，要将源码存为文本格式。

6.3.5.2 在SQLPLUS或用调试工具将存储过程程序进行解释

在SQLPLUS或用调试工具将存储过程程序进行解释；

在SQL>下调试，可用START 或GET 等ORACLE命令来启动解释。如： SQL>START c:stat1.sql

如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。

6.3.5.3 调试源码直到正确

我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：

 使用 SHOW ERROR命令来提示源码的错误位置；

 使用 user_errors 数据字典来查看各存储过程的错

误位置。

6.3.5.4 授权执行权给相关的用户或角色

如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。

GRANT语法：

GRANT system_privilege | role TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object TO user | role | PUBLIC [WITH GRANT OPTION]

--例子：

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

6.3.5.5 与过程相关数据字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS, ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相关的权限:

CREATE ANY PROCEDURE DROP ANY PROCEDURE

在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。

DESC[RIBE] Procedure_name;

6.3.6 删除过程和函数

1．删除过程

可以使用DROP PROCEDURE命令对不需要的过程进行删除，语法如下：

DROP PROCEDURE [user.]Procudure_name;

2．删除函数

可以使用DROP FUNCTION 命令对不需要的函数进行删除，语法如下：

DROP FUNCTION [user.]Function_name;

--删除上面实例创建的存储过程与函数 DROP PROCEDURE logexecution;DROP PROCEDURE delemp;DROP PROCEDURE insertemp;DROP PROCEDURE fireemp;DROP PROCEDURE queryemp;DROP PROCEDURE proc_demo;DROP PROCEDURE log_message;DROP FUNCTION demo_fun;DROP FUNCTION get_salary;

6.3.7 过程与函数的比较

使用过程与函数具有如下优点：

1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。

2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。

3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。

4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。

5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。

6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。

过程与函数的相同功能有：

1、都使用IN模式的参数传入数据、OUT模式的参数返

回数据。

2、输入参数都可以接受默认值，都可以传值或传引导。

3、调用时的实际参数都可以使用位置表示法、名称表示

法或组合方法。

4、都有声明部分、执行部分和异常处理部分。

5、其管理过程都有创建、编译、授权、删除、显示依赖

关系等。

使用过程与函数的原则：

1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。

2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

第三篇：Oracle中的一些sql语句与函数总结

Oracle中的一些sql语句与函数总结

1.concat函数

concat(字串1，字串2)：将字符串

1、字符串2 连在一起；CONCAT()只允许两个参数；

换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||来一次串连

多个字串。

SQL Server中是:select id+’, ’+name from table1 where id=’1’ 结果就是：1,王华。

2.ltrim与rtrim用法

ltrim: 也就是按照y中的字符一个一个截掉x中的字符，并且是从左边开始执行的，只要遇

到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命

令才结束例如ltrim('abcdab','ab')结果并不是按‘ab’字符串来截断'abcdba'，而是分别按照字

母a和b分别去找，分别按不是a和b的字符处开始截取。ltrim函数是从匹配函数开始之

后出现在子串中任何字符都被屏蔽掉了;结果将是'cdab'。Rtrim用法与ltrim相反是从右边截

取。

3.MONTHS_BETWEEN函 MONTHS_BETWEEN(x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那

么MONTHS_BETWEEN()就返回一个负数。

第四篇：18位身份证和组织机构代码校验ORACLE函数

18位身份证和组织机构代码校验ORACLE函数 18位身份证和组织机构代码校验ORACLE函数

18位身份证标准在国家质量技术监督局于1999年7月1日实施的GB11643-1999《公民身份号码》中做了明确规定。

GB11643-1999《公民身份号码》为GB11643-1989《社会保障号码》的修订版，其中指出将原标准名称“社会保障号码”更名为“公民身份号码”，另外GB11643-1999《公民身份号码》从实施之日起代替GB11643-1989。

公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位校验码。其含义如下：

1.地址码：表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。

2.出生日期码：表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日分别用4位、2位、2位数字表示，之间不用分隔符。

3.顺序码：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

校验的计算方式：

1.对前17位数字本体码加权求和

公式为：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份证号码数字值，Wi表示第i位置上的加权因子，其各位对应的值依次为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.以11对计算结果取模

Y = mod(S, 11)

3.根据模的值得到对应的校验码

对应关系为：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校验码： 1 0 X 9 8 7 6 5 4 3 2

15位的身份证号

dddddd yymmdd xx p

18位的身份证号

dddddd yyyymmdd xx p y

其中dddddd为地址码（省地县三级）18位中的和15位中的不完全相同 yyyymmdd yymmdd 为出生年月日 xx顺号类编码 p性别

18位中末尾的y为校验码，在网上可以找到算法

（1）、前两个数字代表省份编码。如：32表示江苏省，34代表安徽省……（2）、第3、4位上的数字代表市级编码。如：01代表南京市，05代表苏州市……（3）、第5、6位上的数字代表区或县编码。如：02代表玄武区，11代表浦口区……

（4）、在第7-14位上行政区代码后的数字表示某个人的出生年月。提问：“为什么2月要用02来表示而不用2来表示呢？”（用0来占位可以统一位数。）

（5）、出生年、月、日后面的两个数字表示居住地所在的派出所编码。如阳沟街所在的号码是04。（6）、老身份证的最后一位，新身份证的倒数第二位数表示性别。提问：哪些数字表示男性？（1、3、5、7、9这样的单数表示男性）。哪些数字表示女性？（0、2、4、6、8这样的双数表示女性）。（7）、新身份证的最后一位是前面17个数字按一定的公式加减乘除得来的，作为个人信息编码，有的也用X来表示。目的是为了进一步防伪，至于计算公式，只有公安机关内部才知道

本资料从网上多处说明整理而来。

18位身份证标准在国家质量技术监督局于1999年7月1日实施的GB11643-1999《公民身份号码》中做了明确规定。

GB11643-1999《公民身份号码》为GB11643-1989《社会保障号码》的修订版，其中指出将原标准名称“社会保障号码”更名为“公民身份号码”，另外GB11643-1999《公民身份号码》从实施之日起代替GB11643-1989。

公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位校验码。其含义如下：

1.地址码：表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。

2.出生日期码：表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日分别用4位、2位、2位数字表示，之间不用分隔符。

3.顺序码：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

校验的计算方式：

1.对前17位数字本体码加权求和

公式为：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份证号码数字值，Wi表示第i位置上的加权因子，其各位对应的值依次为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.以11对计算结果取模

Y = mod(S, 11)

3.根据模的值得到对应的校验码

对应关系为：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校验码： 1 0 X 9 8 7 6 5 4 3 2

关于18位身份证号码尾数是“X”的解释

居民身份证的号码是按照国家的标准编制的，由18位组成：前六位为行政区划代码，第七至第十四位为出生日期码，第15至17位为顺序码，第18位为校验码。作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。但是我国的居民身份证在升位后，一些人的尾号变成了X，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。公安局的同志希望领到X身份证的同志正确理解这个数字的含义，查验身份证的机关和单位更应该清楚这一点。根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。生日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。顺序码表示同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。顺序码的奇数分给男性，偶数分给女性。校验码是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

★ 地址码含义

身份证前六位是地区代码，代码的解释规则如下： ABCDEF

A:国内区域 1 华北 三省二市 2 东北 三省 3 华东 六省一市 4 华南 六省 5 西南 四省一市 6 西北 五省 7 台湾 8 港澳

B:省代码

按照A划定的分区定义省代码，有直辖市的，直辖市列前，其余按离直辖市的距离排序，没有直辖市的，按离北京的远近排序。

11-15 京 津 冀 晋 蒙 21-23 辽 吉 黑

31-37 沪 苏 浙 皖 闽 赣 鲁 41-46 豫 鄂 湘 粤 桂 琼 50-54 渝 川 贵 云 藏 61-65 陕 甘 青 宁 新 81-82 港 澳

CD:城市代码

从01开始排，对于直辖市，CD=01表示市辖区，CD=02表示辖县； 省的城市代码从省会开始排，其余依92式机动车号牌的顺序排列，比如2101=沈阳 2102=大连……

只有地级城市有独立的城市代码，县级市没有。EF:市辖区、郊区、郊县、县级市代码 如果EF=00，指代这个城市，不特定区县；

对于非直辖市，如EF=01，指代市辖区（任意一个区），02开始指代特定的区。其中：

E=0代表市辖区，E=1代表郊区，E=2代表郊县，E=8代表县级市。

对于直辖市，从01开始就依次排区，没有市区和郊区的代码区分。

★ 校验码计算方法

下面举例说明校验码的计算方法。

15位的身份证编码首先把出生年扩展为4位，简单的就是增加一个19，但是这对于1900年出生的人不使用（这样的寿星不多了）

某男性公民身份号码本体码为***01，首先按照公式(1)计算：

∑(ai×Wi)mod 11……………………………………(1)

公式(1)中：

i----表示号码字符从右至左包括校验码在内的位置序号； ai----表示第i位置上的号码字符值；

Wi----示第i位置上的加权因子，其数值依据公式Wi=（2 的 i-1 次幂）(mod 11)计算得出。

i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 ai 3 4 0 5 2 4 1 9 8 0 0 1 0 1 0 0 1 a1 Wi 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1

ai×Wi 21 36 0 25 16 16 2 9 48 0 0 9 0 5 0 0 2 a1

根据公式(1)进行计算：

∑(ai×Wi)=（21+36+0+25+16+16+2+9+48++0+0+9+0+5+0+0+2)= 189

189 ÷ 11 = 17 + 2/11

∑(ai×Wi)mod 11 = 2

然后根据计算的结果，从下面的表中查出相应的校验码，其中X表示计算结果为10（男性为大写X；女性为小写x）：

∑(ai×WI)(mod 11)0 1 2 3 4 5 6 7 8 9 10 校验码字符值ai 1 0 X 9 8 7 6 5 4 3 2

根据上表，查出计算结果为2的校验码为所以该人员的公民身份号码应该为 ***01X。程序代码:

CREATE OR REPLACE FUNCTION f_Id_Verify(Pid VARCHAR2)RETURN VARCHAR2 IS RESULT VARCHAR2(58);TYPE Char_Tabletype IS TABLE OF VARCHAR2(1)NOT NULL INDEX BY BINARY_INTEGER;TYPE Num_Tabletype IS TABLE OF NUMBER NOT NULL INDEX BY BINARY_INTEGER;Tab_a Char_Tabletype;Tab_w Num_Tabletype;Tab_i Char_Tabletype;i NUMBER(2):= 0;Len NUMBER(2):= Length(Rtrim(Ltrim(Pid)));Sigma NUMBER(4):= 0;BEGIN Tab_i(0):= '1';Tab_i(1):= '0';Tab_i(2):= 'X';Tab_i(3):= '9';Tab_i(4):= '8';Tab_i(5):= '7';Tab_i(6):= '6';Tab_i(7):= '5';Tab_i(8):= '4';Tab_i(9):= '3';Tab_i(10):= '2';--Pid := TRIM(Pid);IF Len = 18 THEN FOR i IN 1..17 LOOP Tab_w(i):= MOD(Power(2,((191)), 11);Tab_a(i):= Substr(Pid, i, 1);Sigma := Sigma + Tab_w(i)* Tab_a(i);END LOOP;RESULT := Substr(Pid, 1, 17)|| Tab_i(MOD(Sigma, 11));END IF;IF RESULT = Pid THEN RETURN('正确');ELSE RETURN('错误,应该是：' || RESULT);END IF;EXCEPTION WHEN OTHERS THEN BEGIN RETURN('错误');Dbms_Output.Put_Line('发生了异常的错误');END;END f_Id_Verify;/

组织机构代码是每一个机关、社会团体、企事业单位在全国范围内唯一的、始终不变的法定代码标识。最新使用的组织机构代码在1997年颁布实施，由8位数字（或大写拉丁字母）本体代码和1位数字（或大写拉丁字母）校验码组成。本体代码采用系列（即分区段）顺序编码方法。校验码按下列公式计算： 8 C9 = 11MOD(∑Ci * Wi，11)…(2)FOR i IN 1..8 LOOP Tab_w(i):= MOD(Power(2,((101)), 11);--Tab_w(i):= MOD(Power(2,((101)), 11);就是加权算法值 Tab_a(i):= Substr(Pid, i, 1);Sigma := Sigma + Tab_w(i)* Tab_a(i);END LOOP;IF MOD(Sigma, 11)= 1 THEN RESULT := Substr(Pid, 1, 8)|| 'X';ELSE RESULT := Substr(Pid, 1, 8)|| To_Char(11-MOD(Sigma, 11));END IF;END IF;IF RESULT = Pid THEN RETURN('正确');ELSE RETURN('错误,应该是：' || RESULT);END IF;EXCEPTION WHEN OTHERS THEN BEGIN RETURN('错误');Dbms_Output.Put_Line('发生了异常的错误');END;END Jgid_Verify;

第五篇：oracle plsql 开窗函数over学习总结

连续求和与求总和的区别 D 为天，S 为销售业绩为每天计算销售总额。

SELECTSUM(s)OVER(ORDERBY d),SUM(s)OVER()

FROM(SELECT'A'“A”,1 D, 20 SFROM DUAL

UNIONALL

SELECT'A'“A”,2 D, 15 SFROM DUAL

UNIONALL

SELECT'A'“A”,3 D, 14 SFROM DUAL

UNIONALL

SELECT'A'“A”,4 D, 18 SFROM DUAL

UNIONALL

SELECT'A'“A”,5 D, 30 SFROM DUAL)；

各种求和举例 CREATE TABLETEST_ZHUXP(DEPTNOVARCHAR2(10), ENAME VARCHAR2(10), SAL VARCHAR2(10));--部门姓名薪水

SELECTtest_zhuxp.*,sum(sal)over(partitionbydeptnoorderbyename)部门连续求和,--各部门的薪水“连续”求和

sum(sal)over(partitionbydeptno)部门总和,--部门统计的总和，同一部门总和不变

100*round(sal/sum(sal)over(partitionbydeptno),4)“部门份额(%)”, sum(sal)over(orderbydeptnoDESC,ename)连续求和,--所有部门的薪水“连续”求和

sum(sal)over()总和--此处sum(sal)over()等同于sum(sal)，所有员工的薪水总和

100*round(sal/sum(sal)over(),4)“总份额(%)”

FROMtest_ZHUXP

注意求和后可以排序不影响结果

SELECT DEPTNO,ENAME,SAL,SUM(SAL)OVER(PARTITIONBY DEPTNO ORDERBY DEPTNO DESC, SAL DESC)部门连续求和,SUM(SAL)OVER(ORDERBY DEPTNO DESC, SAL DESC)公司连续求和

FROM TEST_ZHUXP

排序

1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

2.rank()和dense_rank()的区别是：

rank()是跳跃排序，有两个第二名时接下来就是第四名dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

SELECTt.*,RANK()OVER(PARTITIONBYCLASSORDERBY S DESC),dense_rank()OVER(PARTITIONBYCLASSORDERBY S DESC),ROW_NUMBER()OVER(PARTITIONBYCLASSORDERBY S DESC)

FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL

UNIONALL

SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL

UNIONALL

SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL

UNIONALL

SELECT'e' “NAME”,3 “CLASS”,100 “S” FROM DUAL

UNIONALL

SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL

UNIONALL

SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t

统计

和group by的区别是可以看到每一行数据的所有信息

注意加NAME后的区别

SELECTt.*,SUM(1)OVER(PARTITIONBYCLASSORDERBYCLASS/*NAME*/)

FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL

UNIONALL

SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL

UNIONALL

SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL

UNION ALL

SELECT'e' “NAME”,1 “CLASS”,100 “S” FROM DUAL

UNION ALL

SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL

UNION ALL

SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t

开窗函数

开窗函数

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

1：

over（orderby xxx）按照xxx排序进行累计，order by是个默认的开窗函数

over（partitionbyxxx）按照部门分区

2：

over（orderby salary rangebetween5precedingand5following）

每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

例如:对于以下列

aa

sum(aa)over（orderbyaarangebetween2precedingand2following）

得出的结果是

AASUM

214

214

214

318

418

522

618

722

就是说，对于aa=5的一行，sum为5-1<=aa<=5+2的和

对于aa=2来说，sum=1+2+2+2+3+4=14；

又如对于aa=9，9-1<=aa<=9+2只有9一个数，所以sum=9；

3：其它：

over（orderby salary rowsbetween2precedingand4following）

每行对应的数据窗口是之前2行，之后4行

4：下面三条语句等效：

over（orderby salary rowsbetweenunboundedprecedingandunboundedfollowing）每行对应的数据窗口是从第一行到最后一行，等效：

over（orderby salary

rangebetweenunboundedprecedingandunboundedfollowing）

等效over(partitionbynull)

任意删除重复行

在这个表中如果class与score相同，就考虑这行数据多余，删除多余行，就随便保留一行。

NAMECLASSSCORE

------------------------------

1.ff197

2.gg189

3.ll196

4.jj289

5.oo287

6.ii198

7.kk293

8.uu397

9.rr395

10.ee392

11.yy290

12.mm4100

13.nn498

14.pp198

15.fft197

16.ggt189

17.oot287

18.kkt293

19.ffff197

SQL> delete from c_score t where rowid in(select rowid from(select rowid ,row_number()over(partition by class,score order by class)dup_num from c_score)t where t.dup_num>1);