精华内容
下载资源
问答
  • 存储过程的创建与调用

    千次阅读 2020-05-21 13:54:47
    存储过程的含义 主要事例 **含义:**存储过程是一个预编译的SQL语句,优点是允许模块化的设计,成功创建后可多次调用。...1:不带参数的存储过程的创建和调用 –创建存储查询数据 create proc cp_select_book.

    含义:存储过程是一个预编译的SQL语句,优点是允许模块化的设计,成功创建后可多次调用。

    优点:进行多次查询时,存储过程比SQL语句执行要快。

    调用的方法:EXEC 存储过程名称 参数

    具体事例:

    两个表,分明为books,booktypes
    在这里插入图片描述表中字段:id,name,remark,typeid,typename
    在这里插入图片描述
    表中字段:id,name,nums

    1:不带参数的存储过程的创建和调用
    –创建存储查询数据

    create proc cp_select_book_byName
    as
      select * from books
    
    ``--调用存储过程
      exec cp_select_book_byName
    `
    

    2:带输入参数的存储过程的创建和调用

    go
    create proc cp_select_book_byNamecs
    (
     @name varchar(50)
    )
    as
     select * from books where name like '%'+@name+'%'
    
    --调用
     exec cp_select_book_byNamecs 'm'
    

    在这里插入图片描述

    3:带输出参数的存储过程的创建和调用
    –根据名称查询,返回记录,同时返回记录数

    create proc cp_select_byNameExt(
        @name varchar(50),
    	@roccordRows int out
    
    )
    as
      select * from books where name like '%'+@name+'%'
      select @roccordRows=COUNT(*) from books
    --调用
    --定义一个变量,接收返回参数@roccordRows的值
    declare @rs int 
    exec cp_select_byNameExt'三国',@rs out
    print '查询的记录数是:'+convert(varchar(50),@rs)
    
    

    在这里插入图片描述
    在这里插入图片描述

    4:简单的分页的存储过程的创建和调用
    –根据名称,页码,每页显示条数 输入参数
    –返回查询条件的总记录数 输出参数
    –显示查询结果

    create proc cp_select_count(
      @name varchar(50),
      @pageint int,
      @pageSize int,
      @roccordRows int out
    )
    as
    select top (@pageSize) * from books 
    where id not in(
    select top (@pageSize*(@pageint-1)) id 
    from books where name like '%'+@name+'%'
    order by id
    )
    and name like  '%'+@name+'%' order by id 
    select @roccordRows=COUNT(*) from books where name like  '%'+@name+'%'
    
    --调用
    declare @rs int 
    exec cp_select_count'三国',1,5,@rs out
    print '查询的记录数是:'+convert(varchar(50),@rs)
    
    

    在这里插入图片描述
    在这里插入图片描述

    删除语句:
    drop proc 存储过程名称

    展开全文
  • mysql中常用函数与存储过程的创建

    千次阅读 2020-07-11 16:48:29
    mysql中常用函数与存储过程的创建常用函数汇总数学函数字符串函数日期和时间函数条件判断函数系统函数加密函数其他函数自定义函数自定义变量的声明和赋值基本语法实例存储过程事务基本语法实例函数和存储过程的区别 ...


    mysql作为一款开源的免费关系型数据库,用户基础非常庞大,本文列出了mysql常用函数以及自定义函数和存储过程的创建与使用,方便大家随时查看使用这些函数,可以极大地提高用户对于数据库的管理效率,从而更加灵活地满足不同用户的需求。

    常用函数汇总

    • 概念:
      相当于java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
      • 隐藏了实现细节
      • 提高代码的可重用性
    • 使用:
      select 函数名(实参列表)【from 表】 【】中内容可省略

    数学函数

    1. mod(x,y) 取余 :y=0时返回x的值
    SELECT MOD(10,3);        # 输出 1
    SELECT MOD(10,-3);        # 输出 1
    
    1. ceil(float) 进1: 向上取整,返回>=该参数的最小整数
    SELECT CEIL(1.5);        # 输出  2
    SELECT CEIL(-1.5);        # 输出 -1
    
    1. floor(float) 舍1:向下取整,返回<=该参数的最大整数
    SELECT FLOOR(1.5);        # 输出  1
    SELECT FLOOR(-1.5);        # 输出 -2
    
    1. round(float[,n]) n小数精度,四舍五入
    SELECT round(1.5)        # 输出  2
    SELECT round(-1.5)        # 输出 -2 该四舍五入计算方式为:绝对值四舍五入加负号
    
    1. truncate(num,n) 截取num的小数为n位
    SELECT TRUNCATE(3.1415926,2);        # 输出 3.14
    
    1. ABS(x) :返回x的绝对值
    2. PI():返回圆周率π,默认显示6位小数
    3. SQRT(x) :返回非负数的x的二次方根
    4. SIGN(x):返回参数x的符号,-1表示负数,0表示0,1表示正数
    5. POW(x,y)和POWER(x,y):返回x的y次乘方的值
    6. EXP(x):返回e的x乘方后的值
    7. LOG(x):返回x的自然对数,x相对于基数e的对数
    8. LOG10(x):返回x的基数为10的对数
    9. RADIANS(x):返回x由角度转化为弧度的值
    10. DEGREES(x):返回x由弧度转化为角度的值
    11. SIN(x)、ASIN(x):前者返回x的正弦,其中x为给定的弧度值;后者返回x的反正弦值,x为正弦
    12. COS(x)、ACOS(x):前者返回x的余弦,其中x为给定的弧度值;后者返回x的反余弦值,x为余弦
    13. TAN(x)、ATAN(x):前者返回x的正切,其中x为给定的弧度值;后者返回x的反正切值,x为正切
    14. COT(x):返回给定弧度值x的余切

    字符串函数

    1. length(varchar):返回字符串的字节长度(utf-8 一个汉字为3个字节,gbk为2个字节)
    SELECT LENGTH('cbuc')    # 输出 4
    SELECT LENGTH('蔡不菜cbuc')   # 输出13
    
    1. char_length(varchar):返回字符串的长度
    SELECT CHAR_LENGTH('cbuc')    # 输出 4
    SELECT CHAR_LENGTH('蔡不菜cbuc')   # 输出7
    
    1. concat(str1,str2,…) :拼接字符串
    SELECT CONCAT('C','_','BUC')   # 输出 C_BUC
    
    1. concat_ws(joinStr,str1,str2,…) :连接字符串(返回多个字符串拼接之后的字符串,每个字符串之间有一个x)
    2. lower(str)/upper(str):大小写转换
    SELECT UPPER('cbuc')    # 输出 CBUC
    SELECT LOWER('CBUC')   # 输出 cbuc
    
    1. left(str1,n)/right(str1,n)/mid(str1,startPos,n):截取字符串(前者返回字符串str1从最左边开始的n个字符,中间返回字符串str1从最右边开始的n个字符,后者返回字符串str1从starPos位置(包含)开始截取数量为n的字符串)
    2. substr / substring:裁剪字符串
    substr(str,pos)       # str:要裁剪的字符串 , pos:要裁剪的长度
    substr(str,pos,len)   # str:要裁剪的字符串 , pos/len:从哪个位置开始裁剪几位
    # substring同理
    
    1. instr(str1,deststr) :返回deststr在str1中第一次出现的位置(从1开始,不存在返回0)
    SELECT INSTR('蔡不菜','蔡')        # 输出 1 (mysql是从1开始算位数)
    
    1. reverse(str):字符串反转
    2. trim():字符串去除字符,ltrim(s)、rtrim(s):前者返回字符串s,其左边所有空格被删除;后者返回字符串s,其右边所有空格被删除
    SELECT TRIM('  cbuc  ')                 # 输出 cbuc
    SELECT TRIM('a' from 'aaaacbucaaaa')    #输出 cbuc
    
    1. lpad/rpad(str1,n,str2) :左右填充字符char,str1.length>len,被截取,否则填充
    SELECT LPAD('cbuc',6,'*')            # 输出 **cbuc
    SELECT RPAD('cbuc',6,'*')            # 输出 cbuc**
    
    1. replace(s,s1,s2):返回一个字符串,用字符串s2替代字符串s中所有的字符串s1
    SELECT REPLACE('小菜爱睡觉','睡觉','吃饭')        # 输出 小菜爱吃饭
    
    1. insert(s1,x,len,s2):返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符
    2. repeats,n): 返回一个由重复字符串s组成的字符串,字符串s的数目等于n
    3. space(n):返回一个由n个空格组成的字符串
    4. strcmp(s1,s2):若s1和s2中所有的字符串都相同,则返回0;根据当前分类次序,第一个参数小于第二个则返回-1,其他情况返回1

    日期和时间函数

    1. 返回当前系统时间: now() (“YYYY-MM-DD HH:MM:SS”)/curdate() (“YYYY-MM-DD”) /curtime() (“HH:MM:SS”)
    now:返回当前系统日期+时间
    SELECT NOW()               # 输出 2020-02-16 11:43:21
    curdate:返回当前系统日期,不包含时间
    SELECT CURDATE()        # 输出 2020-02-16
    curtime:返回当前时间,不包含日期
    SELECT CURTIME()        # 输出 11:45:35
    
    1. DAYOFWEEK(date):表示返回日期date是星期几,记住:星期天=1,星期一=2,… 星期六=7
    2. WEEKDAY(date):表示返回date是在一周中的序号,西方日历中通常一周的开始是星期天,并且以0开始计数,所以,记住:0=星期一,1=星期二, … 5=星期六
    3. DAYOFMONTH(date):表示返回date是当月的第几天,1号就返回1,… ,31号就返回31
    4. DAYOFYEAR(date):表示返回date是当年的第几天,01.01返回1,… ,12.31就返回365
    5. MONTH(date):表示返回date是当年的第几月,1月就返回1,… ,12月就返回12
    6. DAYNAME(date):表示返回date是周几的英文全称名字
    7. MONTHNAME(date):表示返回date的是当年第几月的英文名字
    8. QUARTER(date):表示返回date的是当年的第几个季度,返回1,2,3,4
    9. WEEK(date,index):该函数返回date在一年当中的第几周,date(01.03)是周日,默认是以为周日作为一周的第一天,函数在此处返回1可以有两种理解:1. 第一周返回0,第二周返回1,… ,2. 以当年的完整周开始计数,第一周返回1,第二周返回2,… ,最后一周返回53
    10. YEAR(date):表示返回date的4位数年份。要注意的是:如果年份只有两位数,那么自动补全的机制是以默认时间1970.01.01为界限的,>= 70 的补全 19,< 70 的补全 20
    11. HOUR(time):返回该date或者time的hour值,值范围(0-23)
    12. MINUTE(time):返回该time的minute值,值范围(0-59)
    13. SECOND(time):返回该time的minute值,值范围(0-59)
    14. PERIOD_ADD(month,add):该函数返回对month做增减的操作结果,month的格式为yyMM或者yyyyMM,返回的都是yyyyMM格式的结果,add可以传负值
    15. PERIOD_DIFF(monthStart,monthEnd):该函数返回monthStart - monthEnd的间隔月数
    16. DATE_ADD(date,INTERVAL number type),同 ADDDATE():DATE_ADD()和ADDDATE()返回对date操作的结果
      • date的格式可以是“15-12-31”,可以是“15-12-31 23:59:59”,也可以是“2015-12-31 23:59:59”,如果参数date是date格式,则返回date格式结果,如果参数date是datetime格式,则返回datetime格式结果
      • type格式:
        SECOND 秒 SECONDS
        MINUTE 分钟 MINUTES
        HOUR 时间 HOURS
        DAY 天 DAYS
        MONTH 月 MONTHS
        YEAR 年 YEARS
        MINUTE_SECOND 分钟和秒 “MINUTES:SECONDS”
        HOUR_MINUTE 小时和分钟 “HOURS:MINUTES”
        DAY_HOUR 天和小时 “DAYS HOURS”
        YEAR_MONTH 年和月 “YEARS-MONTHS”
        HOUR_SECOND 小时, 分钟, “HOURS:MINUTES:SECONDS”
        DAY_MINUTE 天, 小时, 分钟 “DAYS HOURS:MINUTES”
        DAY_SECOND 天, 小时, 分钟, 秒 “DAYS HOURS:MINUTES:SECONDS”
      • 另外,如果不用函数,也可以考虑用操作符“+”,“-”。
    17. DATE_SUB(date,INTERVAL number type),同 SUBDATE():用法和DATE_ADD()与ADDDATE()类似,一个是加,一个是减,用时参照16点,具体用法请参考DATE_ADD()与ADDDATE()。
    18. TO_DAYS(date):返回西元0年至日期date是总共多少天
    19. FROM_DAYS(date):返回西元0年至今多少天的DATE值
    20. DATE_FORMAT(date,format):根据参数对date进行格式化。
      format的格式都列出来:
      %M 月名字(January……December)
      %W 星期名字(Sunday……Saturday)
      %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
      %Y 年, 数字, 4 位
      %y 年, 数字, 2 位
      %a 缩写的星期名字(Sun……Sat)
      %d 月份中的天数, 数字(00……31)
      %e 月份中的天数, 数字(0……31)
      %m 月, 数字(01……12)
      %c 月, 数字(1……12)
      %b 缩写的月份名字(Jan……Dec)
      %j 一年中的天数(001……366)
      %H 小时(00……23)
      %k 小时(0……23)
      %h 小时(01……12)
      %I 小时(01……12)
      %l 小时(1……12)
      %i 分钟, 数字(00……59)
      %r 时间,12 小时(hh:mm:ss [AP]M)
      %T 时间,24 小时(hh:mm:ss)
      %S 秒(00……59)
      %s 秒(00……59)
      %p AM或PM
      %w 一个星期中的天数(0=Sunday ……6=Saturday )
      %U 星期(0……52), 这里星期天是星期的第一天
      %u 星期(0……52), 这里星期一是星期的第一天
      %% 字符% )
    21. TIME_FORMAT(time,format):
      具体用法和DATE_FORMAT()类似,但TIME_FORMAT只处理小时. 分钟和秒(其余符号产生一个NULL值或0)

    条件判断函数

    备注:[… ] 代表可选操作,下面与java进行对比

    1. 运算符
      • if(condition,v1,v2)
        +condition=true,执行v1,否则执行v2
      • ifnull(v1,v2)
        相当于if(v1=null,v2,v1)
      • case expr when v1 then r1 [when v2 then v2…] [else rn] end case
        相当于switch-case
      • if condition1 then statement_list1 [elseif condition2 then statement_list2] […][else statement_list3] end if
        相当于多重if
    2. 循环
      • [begin_label:] loop statement_list end loop [end_label]
        通常需在statement_list中指定退出条件,否则为死循环。
      • [begin_label:] while condition do statement_list end while [end_label]
        相当于do-while
      • [begin_label:] repeat statement_list until condition end repeart [end_label]
        相当于while
    3. 退出语句
      • LEAVE label
        相当于break
      • ITERATE label
        相当于continue

    系统函数

    1. version() 查看mysql版本号
    2. connection_id() 查看当前用户的连接数
    3. user()/current_user()/system_user()/session_user() 查看当前被mysql服务器验证的用户名和主机的组合,一般这几个函数的返回值是相同的
    4. charset(str) 查看字符串str使用的字符集
    5. collation() 查看字符串排列方式

    加密函数

    1. password(field/str)
      从原明文密码str计算并返回加密后的字符串密码,注意这个函数的加密是单向的(不可逆),因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
    2. md5(concat(‘prefix’,field/str,‘suffix’))
      为字符串算出一个MD5 128比特校验和,改值以32位十六进制数字的二进制字符串形式返回
    3. encode/decode(str,pass_str) 使用pswd_str作为密码,加密/解密str

    其他函数

    1. cast(field/val as type)类型转换
      type类型受限:
      signed/unsigned [integer]
      decimal
    2. conv(n,from_base,to_base)
      不同进制数之间的转换,返回值为数值n的字符串表示,由from_base进制转换为to_base进制
    3. format(x,n)
      将数字x格式化,并以四舍五入的方式保留小数点后n位,结果以字符串形式返回

    自定义函数

    自定义变量的声明和赋值

    在学习自定义函数和存储过程的前提,必须掌握自定义变量的创建,下面我先简述一下mysql中两种自定义变量的声明和复制。

    • 方法一(只能在方法体中使用)
      • 声明:declare varname DATA_TYPE [default value];
      • 赋值:select … into varname;
    • 方法二
      • 声明:set @varname = value[,…];
      • 赋值:select @varname:=FIELD/count(1)…from…where…

    基本语法

    自定义函数 (user-defined function UDF)就是用一个象ABS() 或 CONCAT()这样的固有(内建)函数一样作用的新函数去扩展MySQL。所以UDF是对MySQL功能的一个扩展。

    1. 显示函数
      show function status;
    2. 显示创建函数信息
      show create function FUNC_NAME;
    3. 创建语法结构
      create function func([PARAMS PARAMS_TYPE,…]) returns RETURN_TYPE
      begin

      return…
      end;
    4. 删除函数
      drop function [if exists] FUNC_NAME;

    实例

    1. 执行addSubject()函数向subjectinfo表增加一条记录
    delimiter //
    
    drop function if exists addSubject;
    //
    
    create function addSubject(_proId int,_subName varchar(20),_classHours int) returns int 
    begin
    	declare num int default 0;
    	insert into subjectinfo(proId,subName,classHours) values
    	(_proId,_subName,_classHours);
    	select count(id) from subjectinfo where subName=_subName into num;
    	return num;
    end;
    //
    
    set @proId=3,@subName='elasticsearch',@classHours=88,@rtn=0;
    select @rtn:=addSubject(@proId,@subName,@classHours);
    select @rtn;
    //
    
    1. 运行结果
    Query OK, 0 rows affected (0.00 sec)
    
    +-----------------------------------------------+
    | @rtn:=addSubject(@proId,@subName,@classHours) |
    +-----------------------------------------------+
    |                                             1 |
    +-----------------------------------------------+
    1 row in set (0.01 sec)
    
    +------+
    | @rtn |
    +------+
    |    1 |
    +------+
    1 row in set (0.01 sec)
    
    1. 查询subjectinfo表记录
    mysql> select * from subjectinfo;
    +----+-------+----------------------+------------+
    | id | proId | subName              | classHours |
    +----+-------+----------------------+------------+
    |  1 |     1 | KB Java逻辑          |         34 |
    |  2 |     1 | KB JavaOOP           |         72 |
    |  3 |     1 | KB Java高级特性      |         57 |
    |  4 |     1 | KB HTML              |         43 |
    |  5 |     1 | KB CSS               |         36 |
    |  6 |     1 | KB JS                |         64 |
    |  7 |     1 | KB JQuery            |         43 |
    |  8 |     1 | KB MySql             |         59 |
    |  9 |     1 | KB JDBC              |         48 |
    | 10 |     3 | mathsearch           |         88 |
    +----+-------+----------------------+------------+
    49 rows in set (0.00 sec)
    

    存储过程

    mysql中存储过程往往和事务分不开的,首先我们先了解一下事务。

    事务

    • 事务简介
    1. 定义:一个最小的不可再分的工作单元;通常一个事务对应一个完整的业务(例如银行账户开户业务,该业务就是一个最小的工作单元)。一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。事务只和DML语句有关,或者说DML语句才有事务。这个和业务逻辑有关,业务逻辑不同,DML语句的个数不同
    2. 事务四大特征(ACID)
      原子性(A):事务是最小单位,不可再分
      一致性(C):事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
      隔离性(I):事务A和事务B之间具有隔离性
      持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
    3. 关于事务的一些术语
      开启事务:Start Transaction
      事务结束:End Transaction
      提交事务:Commit Transaction
      回滚事务:Rollback Transaction
    4. 事务开启和结束的标志
      • 开启标志:
        任何一条DML语句(insert、update、delete)执行,标志事务的开启
      • 结束标志(提交或者回滚):
        • 提交:成功的结束,将所有的DML语句操作历史记录和底层硬盘数据来一次同步
        • 回滚:失败的结束,将所有的DML语句操作历史记录全部清空
    • 事务基本语法
    1. set autocommit=0; 开启事务
    2. mysql事务锁的分类
      1. select …; 快照查询,无锁
      2. select … lock in share mode; 共享锁,在非修改模式下互不影响
      3. select … for update; 排他锁,只要有其他的共享或排他锁未释放就无法执行
      4. insert/delete/update … 行级排他锁
    3. commit/rollback; 提交/回滚事务,保证业务的完整性

    基本语法

    1. 显示事务
      show procedure status;
    2. 显示创建事务信息
      show create procedure PROC_NAME;
    3. 创建语法结构
      create procedure proname([in PARAMS PARAMS_TYPE,…,on PARAMS PARAMS_TYPE…])
      begin
      [start transaction;]

      [commit/rollback;]

    end;

    1. 调用事务
      call proname();
    2. 删除事务
      drop procedure [if exists] PROC_NAME;

    实例

    1. 执行proAddEmpclass,向empclassinfo新增记录
    delimiter //
    
    drop procedure proAddEmpclass;
    //
    
    create procedure proAddEmpclass(in _empId int,in _classId int,out rtn int)
    begin
    	declare err int default 0;
    	-- continue 继续	exit 退出
    	declare continue handler for sqlexception set err=1;
    	-- 启动事务
    	start transaction;
    	
    	insert into empclassinfo(empId,classId,beginDate) values(_empId,_classId,curdate());
    	set rtn=err;
    	
    	if err=0 then commit;
    	else rollback;
    	end if;
    end;
    //
    
    set @empId=11,@classId=6,@rtn=0;
    call proAddEmpclass(@empId,@classId,@rtn);
    select @rtn;
    //
    
    1. mysql执行结构
    mysql> set @empId=11,@classId=6,@rtn=0;
        -> call proAddEmpclass(@empId,@classId,@rtn);
        -> select @rtn;
        -> //
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    +------+
    | @rtn |
    +------+
    |    0 |
    +------+
    1 row in set (0.01 sec)
    
    
    1. empclassinfo结构
    select * from empclassinfo//
    +----+---------+-------+------------+
    | id | classId | empId | beginDate  |
    +----+---------+-------+------------+
    |  1 |       1 |     1 | 2020-05-11 |
    |  2 |       1 |     2 | 2020-08-15 |
    |  3 |       2 |     3 | 2020-02-22 |
    |  4 |       3 |     2 | 2020-04-16 |
    |  5 |       4 |     5 | 2020-04-21 |
    |  6 |       5 |     4 | 2019-11-25 |
    |  7 |       6 |     2 | 2019-10-08 |
    |  8 |       1 |     6 | 2020-05-11 |
    |  9 |       2 |     6 | 2020-02-22 |
    | 10 |       3 |     7 | 2020-04-16 |
    | 11 |       4 |     7 | 2020-04-21 |
    | 12 |       5 |     8 | 2019-11-25 |
    | 13 |       6 |     8 | 2019-10-08 |
    | 14 |       1 |     9 | 2020-05-11 |
    | 15 |       2 |    10 | 2020-02-22 |
    | 16 |       3 |     9 | 2020-04-16 |
    | 17 |       4 |    11 | 2020-04-21 |
    | 18 |       5 |    11 | 2019-11-25 |
    | 19 |       6 |    10 | 2019-10-08 |
    | 21 |       6 |    11 | 2020-07-06 |
    | 23 |       6 |    11 | 2020-07-06 |
    | 24 |       6 |    11 | 2020-07-08 |
    | 25 |       6 |    11 | 2020-07-08 |
    | 26 |       6 |    11 | 2020-07-11 |
    +----+---------+-------+------------+
    24 rows in set (0.00 sec)
    

    函数和存储过程的区别

    1. 存储过程可以返回多个值,函数只能有一个返回值。
      函数是可以嵌入在sql中使用的,可以在select中调用,而存储过程要让sql的query 可以执行, 需要把 mysql_real_connect 的最后一个参数设置为CLIENT_MULTI_STATEMENTS。
      函数限制比较多,比如不能用临时表,只能用表变量.还有一些函数都不可用等等.而存储过程的限制相对就比较少。
    2. 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。
      对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类,存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。
      存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。
      存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
    展开全文
  • 用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。 二、为什么要用存储过程呢? 存储过程真的...

     一、什么是存储过程:
    存储过程
    是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,
    用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
    二、为什么要用存储过程呢
    存储过程真的那么重要吗,它到底有什么好处呢?存储过程说白了就是一堆SQL 的合并。中间加了点逻辑控制。
    1.存储过程处理比较复杂的业务时比较实用。具体分为两个方面:
    (一)响应时间上来说有
    优势:如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话,就只有一次。存储过程可以给我们带来运行效率提高的好处;
    (二)、从安全上使用了存储过程的系统更加稳定:程序容易出现BUG 不稳定,而存储过程,只要数据库不出现问题,基本上是不会出现什么问题的。
    2.数据量小的项目不用存储过程也可以正常运作。
    三、那么什么时候才需要用存储过程?
    存储过程不仅仅适用于大型项目,对于中小型项目,使用存储过程也是非常有必要的。

    第一:存储过程因为SQL语句已经预编绎过了,因此运行的速度比较快。
    第二:存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
    第三:存储过程运行比较稳定,不会有太多的错误。只要一次成功,以后都会按这个程序运行。
    第四:存储过程主要是在服务器上运行,减少对客户机的压力。
    第五:存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。
    第六:存储过程可以在单个存储过程中执行一系列 SQL 语句。
    第七:存储过程可以从自己的存储过程内引用其它存储过程,这可以简化一系列复杂语句。

    其实存储过程还可以控制权限,比如一个表不直接允许用户直接访问,但要求允许用户访问和修改其中一个或多个字段,那就可以通过一个存储过程来实现并允许该用户使用该存储过程。

    还有,如果多条SQL语句执行过程中,过程环节返回了数据作为后面环节的输入数据,如果直接通过SQL语句执行,势必导致大量的数据通过网络返回到客户机,并在客户机运算;如果封装在存储过程中,则将运算放在服务器进行,不但减少了客户机的压力,同时也减少了网络流量,提高了执行的效率。

    缺点如下:
    1.可维护性比较差。
    2.可读性差。

    个人认为在开发系统时存储过程不要滥用,用多了后期维护就比较麻烦了。

    简单入门案例:

    
    --oracle 存储过程 如下
    -- 1.创建表
    create table t1(id number,tname varchar2(30));
    --基本格式  语法
    CREATE procedure 存储过程名  AS
      BEGIN
        --执行的内容 
    END;
    
    
    --1、创建无参存储过程  通过 储过程添加数据
    CREATE procedure insert_t1 as
    begin
      insert into t1 values(1,'javacto.taobao.com');
    end;
    
    --1.调用无能存储过程
    call insert_t1();
    --3.查询t1表数据 
    select * from t1;
    
    
    
    --4创建带参的 存储过程  通过 储过程添加数据
    CREATE procedure insert_t2(id number,tname varchar2) as
    begin
      insert into t1 values(id,tname);
    end;
    --调用 有能的 存储过程 
    call insert_t2(3,'javacto.ke.qq.com');

     

    展开全文
  • –oracle 带out参数的存储过程的创建与调用 今天刚接触了oracle存储过程中的参数,对于out与in out 这两种形式的参数类型,我比较困惑。在此尝试去理清楚一下。 在测试的过程中,我发现是自己想复杂了。 proc的...

    –oracle 带out参数的存储过程的创建与调用
    今天刚接触了oracle存储过程中的参数,对于out与in out 这两种形式的参数类型,我比较困惑。在此尝试去理清楚一下。
    在测试的过程中,我发现是自己想复杂了。 proc的参数有三种形式,in / out / in out 。(1)in 形式的可以接受存储过程调用时传入的参数。若存储过程定义过程中给出了参数值,则实参接收的赋值的优先顺序为:存储过程内>存储过程外的调用值。(2)out形式的不可以接受存储过程调用时传入的参数,只接受在存储过程内对其的赋值。最重要的是,其值不接受存储过程之外对它的赋值。当存储过程经过调用后,实参值会根据形式值发生改变。[调用完后可以实参值可更改](3)那in out 形式的参数故名思意就是结合这两者的特点。
    不用想太复杂了……

    create or replace procedure p_test
    (i_in in integer , 
    e_name   OUT VARCHAR2, 
    c_NAME   OUT VARCHAR2) 
    is 
    begin 
      e_name := 'e';
       insert into test2 values (i_in,e_name ,c_NAME);  
       COMMIT;
    end;
    
    declare 
      v_id integer; v_e_name varchar2(100);v_c_name varchar2(100);
    begin 
      v_id := 1002;
      v_e_name := 'outer proc';
      v_c_name := 'cccc';    
      delete from test2 ;  commit;  
      delete from test4 ;  commit;   
      
      insert into test4 select 'before p_test --:'||v_e_name, 'before p_test --:'||v_c_name ,v_id       from dual ;   
      commit;
      
      p_test(v_id,v_e_name,v_c_name);  
      
      insert into test4 select 'when p_test --:'||v_e_name, 'when p_test --:'||v_c_name ,v_id       from dual ;
      commit;
      
      
      v_id := 1003;
      v_e_name := 'proc!!';
      v_c_name := 'cccc!!!';  
        
      insert into test4 select 'after charge --:'||v_e_name, 'after charge --:'||v_c_name ,v_id       from dual ;
      commit;  
      
    end;

    从这两个表的结果就可以验证以上的说法啦。

    select * from test2;  
    select * from test4;   

    在这里插入图片描述
    在这里插入图片描述

    接下来我们将out型的参数改为in out 型。其他语句不变。

    create or replace procedure p_test
    (i_in in integer , 
    e_name  in  OUT VARCHAR2, 
    c_NAME  in  OUT VARCHAR2) 
    is 
    begin 
      e_name := 'e';
       insert into test2 values (i_in,e_name ,c_NAME);  
       COMMIT;
    end;

    ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191117021005231.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NDAzODYz,size_16,color_FFFFFF,t_70在这里插入图片描述
    在这里插入图片描述

    展开全文
  • postgres存储过程的创建及其返回类型

    千次阅读 2017-04-12 13:19:45
    postgres存储过程的创建及其返回类型
  • 实验十 存储过程的创建与使用 一、实验目的 本实验的目的是使学生进一步掌握SQL Server存储过程的创建及使用方法,加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。 二、实验准备 结合课堂教学...
  • 创建一个临时的存储过程只需要给存储过程名之前加上一个“#”即可,全局的存储过程是给存储过程名前加上两个“#”即可 临时存储过程创建成功之后当前存储过程中执行可以成功,如果新建查询时执行此临时存储过程,...
  • mysql,sqlserver存储过程的创建及执行

    千次阅读 2018-07-30 14:19:13
    mysql,sqlserver存储过程的创建及执行 sqlserver: 创建不带参数的存储过程: CREATE PROCEDURE 'ProName' AS SELECT * FROM [dbo].[TABLE_1] go 创建带参数的存储过程: CREATE PROCEDURE 'ProName' @...
  • MYSQL中存储过程的创建,调用及语法

    千次阅读 2019-01-18 23:44:30
    MYSQL中存储过程的创建,调用及语法 转载自这篇文章 MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能,如果没有存储...
  • MySQL知识之存储过程的创建
  • 本文简单记录下oracle数据库中,如何用plsql编程、以及存储过程的创建和使用。 相关的概念我就不列举了,大家不清楚的可以自行搜索,本篇主要列举实际的sql语法。 那plsql编程,大家可以使用windows的命令行,也...
  • 数据库存储过程的创建

    千次阅读 2017-02-20 10:40:37
    在平时操作数据库时候,我们使用存储过程频率是相当高,对于很多新手来说,存储过程还是相对老说比较抽象一个概念,今天我们就来了简单解一下什么事存储过程,  首先,存储过程和函数在实现上是相差不大...
  • 存储过程的创建和使用

    千次阅读 2018-08-07 17:50:08
    mysql工作中用的比较多了,但是总觉得做为2年半的...1.存储过程的语法结构 create procedure 存储过程名称(参数);//可以无参,也可以带参数,和java的方法名差不多 有参数的设置:(in|out|inout 参数名称 数据类...
  • mysql存储过程的创建,删除,调用及其他常用命令 一.创建存储过程 1.基本语法:create procedure sp_name() begin ……… end 2.参数传递 二.调用存储过程 1.基本语法:call sp_name() 注意:存储过程名称...
  • oracle 存储过程 的创建以及使用

    千次阅读 2018-12-11 11:11:31
    1.概念  存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的... create [or replace] procedure 存储过程的名称(参数名 in|out|in out 参数类型,参数名 in|out|in out 参数类型)  is | as  ...
  • Oracle 存储过程的创建,调用和删除

    千次阅读 2019-08-31 22:40:17
    创建存储过程的语法: create or replaceprocedure <procedure_name> [(<parameter list>)] as|is <local variable statements>--创建过程,可指定运行过程需传递的参数 begin <...
  • 存储过程只在创建时编译,在调用时无需编译,而一般的SQL语句在每一次的调用...存储过程的语法如下 create procedure sp_name @[参数名] [类型],@[参数名] [类型] [with recompile][with encryption][wit
  • 创建存储过程 create or replace procedure procedure_name is .... 调用 execute procedure_name(argument_list); 编译 如果存储过程包含对象发生改变,可能使存储过程变得无效,这时,需要对存储过程进行重新...
  • 1、创建不带参数的存储过程 例子:查询学生总数 --查询存储过程 IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL  DROP procedure PROC_SELECT_STUDENTS_COUNT; GO CREATE ...
  • 创建 use StudentManager go if exists(select * from sysobjects where name='usp_ScoreQuery4') drop procedure usp_Score...create procedure usp_ScoreQuery4 --创建带参数的存储过程 @AbsentCount int...
  • 存储过程的创建及使用(sqlserver 2005)

    千次阅读 2009-11-10 13:49:00
    存储过程的创建及使用(sqlserver 2005) 创建存储过程。存储过程是已保存的 Transact-SQL 语句集合,或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用,可接收并返回用户提供的参数。可以创建过程供...
  • oracle存储过程的创建和游标的使用

    千次阅读 2019-01-31 17:02:50
    (在创建的时候insertTable不能写成insertTable()) create or replace procedure insertTable is begin  --新建游标suts  Declare cursor suts is select * from sys_user_temp;  begin  --开始循环 ...
  • 创建一个名为 Query_student 的存储过程,该存储过程的功能是根据学号查询学生表中某一学生的学号、姓名、性别及出生日期。 create proc Query_student @snum varchar(10) as select snum,sname,ssex,2020-sage as ...
  • 2.存储过程的优点 3.存储过程的定义及使用 4.存储过程的分类 4.1 系统存储过程 4.1.1 系统存储过程的介绍 4.1.2 系统存储过程明细 4.1.3 系统存储过程的调用 4.2 带参数的存储过程 4.3 有输出返回的存储过程...
  • 存储过程 概念: ... 由于存储过程是已经编译好代码,所以执行时候不必再次进行编译,从而提高了程序运行效率。 优点:  1、执行速度快。  2、模块化程序设计。  3、减少网络通信量。
  • SQL关于触发器及存储过程的创建

    千次阅读 2019-05-21 17:13:00
    触发器:链接1,链接2 使用T-SQL语句来创建触发器 基本语句如下﹕ create trigger trigger_name on {table_name | view_name} {for | After | Instead of } [ insert, update,del...
  • 存储过程的创建与管理

    千次阅读 2011-10-11 12:33:01
    创建一个学生成绩登记表,插入一些记录,编写一个统计不及格学生数的存储过程,要求采用循环语句和条件判断语句实现实现

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,947
精华内容 13,978
关键字:

存储过程的创建