ORACLE 过程、函数、包的区别与联系
一、过程与函数区别
1.过程可以有0~N个返回参数，通过OUT or IN OUT参数返回；函数有且仅有1个返回值，通过return 语句返回。
2.调用过程时，可做为单独的语句执行；调用函数时，函数必须把返回结果赋值给一个变量。
3.在SQL语句中，可以不能调用过程；在SQL语句中，可以直接调用函数。
二、过程与函数
过程[procedure]及函数[function]这两种方案对象均由SQL 语句及PL/SQL 语言构件组合而成，存储于数据库中，运行时作为一个整体，用于解决某个问题，或完成一组相关的操作。调用者可以在调用过程及函数时为其提供参数，参数可以专用于输入值，专用于输出值，或同时用于输入及输出值。在过程与函数中，用户既可以发挥SQL 的灵活性与易用性，也能够发挥结构化编成语言的过程控制能力。过程与函数基本类似，唯一区别在于函数总会向调用者返回一个值，而过程无此特性。
三、存储过程在以下方面具有优势：
1.利用定义者权限过程[definer's rights procedure]确保数据安全
使用存储过程有助于确保数据安全。具备数据库对象访问权限的用户可以定义存储过程或函数，其他用户通过已定义的过程访问数据，从而达到限制用户对数据库操作的目的。
例如，现有一个过程能够更新某数据表，管理员不必授予用户直接访问数据表的权限，而是授予用户访问此过程的权限。当用户调用此过程时，过程以其拥有者的权限运行。具备执行此过程权限（但不具备查询，更新，及删除底层表数据权限）的用户可以调用过程对表进行操作，但不能以其他方式操作表数据。
2.通过调用者权限过程[invoker's rights procedure]使用调用者的权限及方案上下文
调用者权限过程可以继承其调用者的权限及方案上下文。即调用者权限过程不与特定的用户或方案绑定，每次执行时利用当前用户的权限操作当前用户的方案对象。应用程序开发者可以通过调用者权限过程使应用逻辑集中化，即便底层数据分散于不同的用户方案中。
例如，以经理身份登录的用户运行针对employees表的更新过程时可以修改薪水数据，而以职员身份登录的用户运行同样的的更新过程时只能修改地址数据。
3.提升性能
?与向Oracle 提交SQL 语句或PL/SQL 块代码相比，使用存储过程时通过网络传输的数据量较小，因为存储过程只有在定义时需要被传输到服务器，而使用时只需进行调用。
?数据库以编译后的形式保存过程，因此执行期间无需进行编译。
?如果过程已经被加载到系统全局区[system global area，SGA]的共享池[shared pool]内，则过程可以直接执行，而不必从磁盘获取。
4.内存分配
存储过程可以利用Oracle 的共享内存特性，多个用户执行同一个过程时只需将一份过程副本加载到内存中。通过在多个用户间共享相同的代码，能够显著地减少应用程序所需的Oracle 内存。
5.提高开发生产率
利用存储过程能够提高开发生产率。在一组公用的存储过程的基础上开发应用程序，能够避免冗余代码从而提高开发生产率。
例如，开发者可以编写存储过程，分别对employees表内的员工数据进行插入，更新及删除操作。任何应用程序都可以调用这些过程，而无需重写SQL 语句就可以完成相应的工作。如果数据管理的方式发生变化，只需修改存储过程，而不必修改调用过程的应用程序。
6.完整性
存储过程能够提高应用程序所处理数据时的一致性与完整性。由于应用程序是在一组公用存储过程的基础上开发的，因此能够减少提交代码发生错误的可能性。
例如，用户可以对过程或函数进行测试，确保其返回准确的结果，一旦验证则可以在其他应用程序中重复使用而无需再次测试。如果过程所引用的数据结构发生变化，只需修改存储过程，而不必修改调用过程的应用程序。
四、过程开发指南
以下是设计存储过程时需要遵循的原则：
?在一个存储过程中应该只实现一个单一的任务。不要定义执行多个子任务的大型存储过程，如果在多个过程中实现了相同的子任务将造成不必要的代码重复。
?不要定义过程来实现Oracle 已经提供的功能。例如，不要定义过程来强制实现简单的数据完整性规则，此类功能只需要声明数据完整性约束即可实现。
五、包
包[package]是由一组相关的过程，函数，及其使用的游标，变量等构成的程序单元，存储于数据库中供用户使用。与独立的过程及函数类似，包过程与包函数也可以由应用程序及用户显式地调用。
包在创建时分为两部分：包规范与包体。包规范[package specification]用于声明所有公有程序结构，而包体则用于对所有程序结构进行定义（包括公有及私有）。将包分为两部分具有以下优势：
?可与使开发周期更灵活。用户可以首先声明包规范而不定义包体，此时已声明的公有过程已经可以被其他过程引用。
?用户可以分别修改一个过程在包体内的定义及在包规范中的声明。只要在包规范中的过程声明没有发生变化，引用了包中被修改过程的对象就不会被标志为无效状态[invalid]。因此避免了不必要的重新编译工作。
六、包的优势
使用包具有以下优势：
?封装相关的过程与变量
使用存储包[stored package]用户可以将存储过程，变量，数据类型等程序结构封装
[encapsulate]（或称为组合）为一个命名的程序单元并存储在数据库中。这使开发过程更易管理。此外，将过程封装还能使权限管理更简单。当授予用户访问包的权限后，用户就可以访问包内的所有程序结构。
?声明公有或私有的过程，变量，常量及游标
在定义包时，用户可以决定过程，变量，常量及游标是公有或私有的。公有的程序结构意味着其可以被用户直接使用。而私有程序结构对用户是不可见的。
例如，一个包中包含10 个过程。用户可以将其中的 3 个声明为公有的，包用户可以直接
使用。其余过程声明为私有，只能被包内的过程访问。注意，不要将公有及私有包程序结构与授权给PUBLIC相混淆。
?性能更佳
当包内的某个过程第一次被调用时，整个包将被加载到内存中。这个加载操作只需执行一次，而对于分散的独立过程[standalone procedure]则需要执行多次操作。因此，当包内的其他过程被调用时其编译代码已经存在于内存中，而无需进行磁盘I/O 操作。
包体可以被修改并重新编译而不会影响包规范。因此，引用（总是根据包规范进行引用）了包内被修改程序结构的方案对象无需重新编译，除非包规范也作了修改。使用包可以减少不必要的重新编译操作，从而减少了对数据库整体性能的影响。
oracle自定义函数和存储过程
oracle自定义函数和存储过程 oracle自定义函数和存储过程（转）2008-07-23 10:43--过程(PROCEDURE)--------------------------------------------------// --创建表 CREATE TABLE user_info ( id VARCHAR2(4), name VARCHAR2(15), pwd VARCHAR2(15), address VARCHAR2(30) ); --插入数据 INSERT INTO user_info VALUES('u001','zhao','zhao','shanghai'); --如要经常执行插入，Oracle每次都要进行编译，并判断语法正确性，因此执行速度可想而知， --所以我们要创建一个过程来实现 CREATE OR REPLACE PROCEDURE AddNewUser ( n_id user_info.id%TYPE, n_name user_https://www.360docs.net/doc/e47152284.html,%TYPE, n_pwd user_info.pwd%TYPE, n_address user_info.address%TYPE ) AS BEGIN --向表中插入数据 INSERT INTO user_info(id,name,pwd,address) VALUES(n_id,n_name,n_pwd,n_address); END AddNewUser; / --下面我们利用PL/SQL匿名块调用该过程 DECLARE --描述新用户的变量 v_id user_info.id%TYPE := 'u002'; v_name user_https://www.360docs.net/doc/e47152284.html,%TYPE := 'wish'; v_pwd user_info.pwd%TYPE := 'history'; v_add user_info.address%TYPE := 'shanghai'; BEGIN --调用过程，添加wish用户到数据库
Oracle 常见函数(一)——数值函数
Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;
/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。
eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;
oracle存储过程讲解及实例
存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体。 行3: BEGIN关键词表明PL/SQL体的开始。 行4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句; 行5: END关键词表明PL/SQL体的结束
存储过程创建语法： create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; --vs_msg VARCHAR2(4000); 变量2 类型（值范围）; Begin Select count(*) into 变量1 from 表A where列名 =param1； If (判断条件) then Select 列名into 变量2 from 表A where列名 =param1； Dbms_output。Put_line(‘打印信息’); Elsif (判断条件) then Dbms_output。Put_line(‘打印信息’); Else Raise 异常名（NO_DATA_FOUND）; End if; Exception When others then Rollback;
End; 注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出 类型可以使用任意Oracle中的合法类型。 2，变量带取值范围，后面接分号 3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录 4，用select 。。。into。。。给变量赋值 5，在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) ,
oracle-存储过程练习题
1．创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限。 CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATA TEMPOARY TABLESPACE TEMPDATA; GRANT CONNECT , CREATE TABLE , RESOURCE TO KAIFA 2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。 概要表（CCB_GYB）信息如下： --RMB 人民币 --CNY 本位币 --USD 外币折美元 如果币种为RMB，则取出人民币余额作为本期余额；为CNY，则取本位币余额；为USD 则取外币折美元余额。 请编写一个函数GetCurrBal( qrp_rq IN VARCHAR2, --报表日期 qrp_code IN VARCHAR2--币种 ) CREATE OR REPLACE FUNCTION GetCurrBal( Vqrp_rq Date , --报表日期 Vqrp_code VARCHAR2--币种 ) RETURN NUMBER IS VAMOUNT NUMBER ; VDATE Date; BEGIN SELECT ACCOUNTING_DATE INTO VDATE FROM CCB_GYB Where ACCOUNTING_DATE = Vqrp_rq; IF Vqrp_code = 'RMB'THEN SELECT RMB_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= 'RMB' AND ACCOUNTING_DATE= VDATE; ELSE IF Vqrp_code = 'CNY'THEN SELECT CNY_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= 'CNY';
oracle中常用函数大全
oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;
Oracle常用函数及使用案例(珍藏版)
Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.
oracle--存储过程--存储函数--触发器
存储过程和存储函数： 指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。语法： create [or replace] PROCEDURE 过程名(参数列表) AS PLSQL子程序体； 存储过程实例： 存储过程的调用： 方法一： set serveroutput on begin raisesalary(7369); end; / 方法二： set serveroutput on exec raisesalary(7369);
函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。 语法： CREATE [OR REPLACE] FUNCTION 函数名(参数列表) RETURN 函数值类型 AS PLSQL子程序体； 示例： 函数的调用： declare v_sal number; begin v_sal:=queryEmpSalary(7934); dbms_output.put_line('salary is:' || v_sal); end; / 在java语言中调用存储过程： 存储过程：
什么时候使用存储过程/存储函数？ 如果只有一个返回值，用存储函数； 否则，就用存储过程。 在out参数中使用游标： 声明包结构： 创建包体：
在Java语言中访问游标类型的out参数： 触发器： 数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。 触发器的类型 语句级触发器 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。 行级触发器（FOR EACH ROW） 触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。 创建触发器： CREATE [or REPLACE] TRIGGER 触发器名 {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF 列名]} ON 表名 [FOR EACH ROW [WHEN(条件) ] ] PLSQL 块
oracle数据库之常用的函数练习
/*此文章可以作为sql脚本直接运行，某些函数限于oracle数据库！前面建表和数据插入可以不看，直接看后面红色部分的代码，前面的表只是提供一些数据供大家练习，不用自己再去建表了，真正的内容在后面*/ --人员表 create table person( personID number , pname varchar2(20) constraint NN_pname not null, psex char(2) , pbirth date , constraint PK_personID primary key (personID) , constraint CK_psex check (psex='男' or psex='女') ); --增加数据 create sequence seq_personID ; insert into person values (seq_personID.Nextval,'david','男',to_date('1990-09-10','yyyy-mm- dd')); insert into person values (seq_personID.Nextval,'ggyy','男',to_date('1991-10-10','yyyy-mm-dd')); insert into person values (seq_personID.Nextval,'朱 刀','男',to_date('1970-10-10','yy-mm-dd'));
insert into person values (seq_personID.Nextval,'泥 巴','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'憨 坨','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'李静 芳','女',to_date('1990-09-10','yyyy-mm-dd')); insert into person (personID,pname,psex) values (seq_personID.Nextval,'胖子','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'小成成','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'乐姐','女'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'靓崽波','男'); --登录账号表 create table loginzh( userID number , personID number , zhanghao varchar2(20) , passwd varchar2(20) , logintime date , loginIP varchar2(15) , constraint PK_userID primary key (userID) , constraint FK_personID foreign key (personID) references person (personID) ); --为这个表加点数据 create sequence seq_userID ; insert into loginzh values (seq_userID.Nextval,1,'111111','123456',sysdate,'127.0.0.1'); --sysdate为系统当前时间
oracle SQL里常用的时间函数
oracle SQL里常用的时间函数,经典推荐 相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。那在oracle 中应该怎么来写sql语句呢，这个时候Oracle的日期函数会给我们很多帮助。 常用日期型函数 1。Sysdate 当前日期和时间 SQL> Select sysdate from dual; SYSDATE ---------- 21-6月-05 2。Last_day 本月最后一天 SQL> Select last_day(sysdate) from dual; LAST_DAY(S ---------- 30-6月-05 3。Add_months(d,n) 当前日期d后推n个月
用于从一个日期值增加或减少一些月份 date_value:=add_months(date_value,number_of_months) SQL> Select add_months(sysdate,2) from dual; ADD_MONTHS ---------- 21-8月-05 4。Months_between(f,s) 日期f和s间相差月数 SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))fro m dual; MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-D D')) ---------------------------------------------------------- -4.6966741 5。NEXT_DAY(d, day_of_week) 返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。
oracle存储过程函数汇总().docx
oracle存储过程函数汇总() Character function return character value These functions all received is the character parameter type group (except CHR) and returns the character value? In addition to the special instructions, the function returns VARCHAR2 most numerical types? The restrictions on the return type of the character function are the same as those for the basic database type? The maximum value of character variable storage: The VARCHAR2 value is limited to 2000 characters (ORACLE 8 to 4000 characters) The CHAR value is limited to 255 characters (0RACLE8 2000) The long type is 2GB The Clob type is 4GB 1,CHR Syntax: Chr (x) Function: return in the database character set with numerical equivalence with the character of X. CHR and ASCII are a pair of inverse functions? After CHR conversion character after ASCII conversion and obtained the original word
oracle常用函数习题及答案
--1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk?); --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')); --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(…manager?) and job<>upper(…clerk?) and sal>=2000) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm>0; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate= last_day(hiredate); --10、找出早于25年之前受雇的雇员
Oracle存储过程基本结构
1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct wher e xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF; 4.while 循环 WHILE V_TEST=1 LOOP BEGIN
XXXX END; END LOOP; 5.变量赋值 V_TEST := 123; 6.用for in 使用cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP;
END; 7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHE RE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER; 8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 关于oracle存储过程的若干问题备忘 1.在oracle中，数据表别名不能加as，如：
oracle常用函数介绍及其使用
Oracle函数 Oracle SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数： 1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结 果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中 的整数列）。常用的单行函数有： 字符函数：对字符串操作。 数字函数：对数字进行计算，返回一个数字。 转换函数：可以将一种数据类型转换为另外一种数据类型。 日期函数：对日期和时间进行处理。 2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x) 返回结果集中x列的总合。 目录大纲 Oracle函数 (1) ?字符函数 (2) ?数字函数 (3) ?日期函数 (4) ?转换函数 (6) ?其他单行函数 (8) ?聚合函数 (9)
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。 表1 字符函数 表2 字符函数示例
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。 表3 数字函数 说明： 1.ROUND(X[,Y])，四舍五入。 在缺省y时，默认y=0；比如：ROUND(3.56)=4。 y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。 2.TRUNC(x[,y])，直接截取，不四舍五入。 在缺省y时，默认y=0；比如：TRUNC (3.56)=3。 y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
Oracle函数大全
附录Ⅱ Oracle11g SQL函数 函数名 返回 类型 说明 字符串函数 ASCII(s) 数值 返回s首位字母的ASCII码 CHR(i) 字符 返回数值i的ASCII字符 CONCAT(s1,s2) 字符 将s2连接到字符串s1的后面 INITCAP(s) 字符 将每个单词首位字母大写其它字母小写 INSTR(s1,s2[,i[,j]]) 数值 返回s2在s1中第i位开始第j次出现的位置 INSTRB(s1,s2[,i[,j]]) 数值 与INSTR(s)函数相同，但按字节计算 LENGTH(s) 数值 返回s的长度。 LENGTHb(s) 数值 与LENGTH(s)相同，但按字节计算。 lower(s) 字符 返回s的小写字符 LPAD(s1,i[,s2]) 字符 在s1的左侧用s2字符串补足到总长度i LTRIM(s1,s2) 字符 循环去掉在s2中存在的s1左边字符 RPAD(s1,i[,s2]) 字符 在s1的右侧用s2字符串补足到总长度i RTRIM(s1,s2) 字符 循环去掉在s2中存在的s1右边字符 REPLACE(s1,s2[,s3]) 字符 用s3替换出现在s1中的s2 REVERSE(s) 字符 返回s倒排的字符串 SUBSTR(s,i[,j]) 字符 从s的第i位开始截得长度j的子字符串 SUBSTRB(s,i[,j]) 字符 与SUBSTR相同，但i,j按字节计算。 SOUNDEX(s) 返回与s发音相似的词 TRANSLATE(s1,s2,s3) 字符 将s1中与s2相同的字符以s3代替 TRIM(s) 字符 删除s的首部和尾部空格 UPPER(s) 字符 返回s的大写 正则表达式函数 REGEXP_LIKE() 布尔 功能与LIKE的功能相似 REGEXP_INSTR() 数值 功能与INSTR的功能相似 REGEXP_SUBSTR() 字符 功能与SUBSTR的功能相似 REGEXP_REPLACE() 字符 功能与REPLACE的功能相似 数字函数 ABS(i) 数值 返回i的绝对值 ACOS(i) 数值 反余玄函数，返回-1到1之间的数 ASIN(i) 数值 反正玄函数，返回-1到1之间的数 ATAN(i) 数值 反正切函数，返回i的反正切值
Oracle常用函数
数值函数
日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数
5.高级查询（多表联合查询） 例子： SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排位，不论值是否相等 RANK具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的 例子： SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;
Oracle 存储过程procedure,函数function用法,以及package用法
Oracle 存储过程procedure，函数function用法，以及package用法使用scott登录normal，简单的操作emp表实现存储过程，函数，包 定义存储过程： create or replace procedure get_user_name(userid in emp.empno%type) is xx emp.ename%type; begin select ename into xx from emp where empno=userid; dbms_output.put_line(xx); end; 定义函数： create or replace function get_user_sal(userid in emp.empno%type) return number is salary emp.sal%type; begin select sal into salary from emp where empno=userid; return salary; exception when NO_data_founD then dbms_output.put_line('NOT FOUND!'); end get_user_sal; 定义包头： create or replace package ex is procedure get_user_name(userid in emp.empno%type) ; function get_user_sal(userid in emp.empno%type) return number; end ex; 定义包体： create or replace package body ex is procedure get_user_name(userid in emp.empno%type) is xx emp.ename%type; begin select ename into xx from emp where empno=userid; dbms_output.put_line(xx); end; function get_user_sal(userid in emp.empno%type) return number
AAA-Oracle常用及非常用函数详解
Oracle常用及非常用函数详解 转载自：https://www.360docs.net/doc/e47152284.html,/blog/192292 感于总有些网友提出一些非常基础的问题，比如有没有实现某某功能的函数啊，某某函数是做什么用的啊，格式是什么等等，同时也感受到自己对oracle函数认识的不足，于是集中月余时间专注于oracle函数，小有心得不敢私藏，发之与诸公共享。 本文并不准备介绍全部的oracle函数，当前情势下，俺也还没这个时间，需要学习的东西太多了，要把多数时间花在学习经常能用上的技术方面:)，所以如果是准备深入了解所有oracle函数的朋友，还是去关注：Oracle SQL Reference官方文档更靠谱一些。 本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思的一些函数。分二类介绍，分别是： 著名函数篇－经常用到的函数 非著名函数篇－即虽然很少用到，但某些情况下却很实用 注：N表示数字型，C表示字符型，D表示日期型，[ ]表示内中参数可被忽略，fmt表示格式。 单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。 (一).数值型函数(Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。 1、MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。 例如：SELECT MOD(24,5) FROM DUAL; 2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。 例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。 例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 返回的CHAR类型值长度不超过2000字节； 返回的VCHAR2类型值长度不超过4000字节； 如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。
oracle存储过程 语法 函数 总结
oracle存储过程语法函数总结 对于oracle 存储过程是很优秀的一种脚本语言。下面是一些整理： 一，Plsql 调试存储过程： 1、在oracle的配置和移植工具àNET MANAGER中配置连接 2、在plsql中新建SQL窗口，编写存储过程 3、在plsql中新建测试窗口，在begin end之间调用 4、查看编译错误：在命令窗口中show errors procedure procedure_name 或者编辑的方式打开存储过程，在编译时会有错误提示 一，Plsql 调试存储过程： 1、在oracle的配置和移植工具àNET MANAGER中配置连接 2、在plsql中新建SQL窗口，编写存储过程 3、在plsql中新建测试窗口，在begin end之间调用
4、查看编译错误：在命令窗口中show errors procedure procedure_name 或者编辑的方式打开存储过程，在编译时会有错误提示 二，oracle存储过程基本语法 1、 oracle存储过程结构： CREATE OR REPLACE PROCEDURE 存储过程名 ( 输入输出参数 ) IS 变量定义 BEGIN 代码; END 存储过程名; 2、 if语句： If 逻辑表达式then
End; End if; If 逻辑表达式then Begin End; Else Begin End; End if; If 逻辑表达式then Begin End; Elseif 逻辑表达式then Begin
Else Begin End; End if; 3、 For循环 For in loop … End loop; E.g： CREATE OR REPLACE PROCEDURE TEST is BEGIN for i in 1..100 LOOP DBMS_OUTPUT.put_line(i);
ORACLE常用数值函数、转换函数、字符串函数介绍
(一)本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思 的一些函数。分二类介绍，分别是： 著名函数篇－经常用到的函数 非著名函数篇－即虽然很少用到，但某些情况下却很实用 注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt 表示格式。 单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。 (一).数值型函数(Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, A TAN, and A TAN2支持30位小数点。 1、MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。 例如：SELECT MOD(24,5) FROM DUAL; 2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果 n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。 例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省 设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。 例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 返回的CHAR类型值长度不超过2000字节；λ 返回的VCHAR2类型值长度不超过4000字节；λ 如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。 返回的CLOB类型值长度不超过4G；λ 对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。 1、LOWER(c) 将指定字符串内字符变为小写，支持CHAR,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型