西部数码助手网站后台管理,濮阳创建网站公司,做三国mod的网站,称多网站建设View Code 存储过程如同一门程序设计语言#xff0c;同样包含了数据类型、流程控制、输入和输出和它自己的函数库。--------------------基本语法--------------------一.创建存储过程
create procedure sp_name()
begin
.........
end二.调用存储过程
1.基本语法#xff1a… View Code 存储过程如同一门程序设计语言同样包含了数据类型、流程控制、输入和输出和它自己的函数库。--------------------基本语法--------------------一.创建存储过程
create procedure sp_name()
begin
.........
end二.调用存储过程
1.基本语法call sp_name()
注意存储过程名称后面必须加括号哪怕该存储过程没有参数传递三.删除存储过程
1.基本语法
drop procedure sp_name//2.注意事项
(1)不能在一个存储过程中删除另一个存储过程只能调用另一个存储过程四.其他常用命令1.show procedure status
显示数据库中所有存储的存储过程基本信息包括所属数据库存储过程名称创建时间等2.show create procedure sp_name
显示某一个mysql存储过程的详细信息--------------------数据类型及运算符--------------------
一、基本数据类型
略二、变量自定义变量DECLARE a INT ; SET a100; 可用以下语句代替DECLARE a INT DEFAULT 100;变量分为用户变量和系统变量系统变量又分为会话和全局级变量用户变量用户变量名一般以开头滥用用户变量会导致程序难以理解及管理1、 在mysql客户端使用用户变量
mysql SELECT Hello World into x;
mysql SELECT x;mysql SET yGoodbye Cruel World;
mysql select y;mysql SET z123;
mysql select z;2、 在存储过程中使用用户变量mysql CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(greeting, World);
mysql SET greetingHello;
mysql CALL GreetWorld( );3、 在存储过程间传递全局范围的用户变量
mysql CREATE PROCEDURE p1( ) SET last_procedurep1;
mysql CREATE PROCEDURE p2( ) SELECT CONCAT(Last procedure was ,last_procedure);
mysql CALL p1( );
mysql CALL p2( );三、运算符
1.算术运算符加 SET var122; 4
- 减 SET var23-2; 1
* 乘 SET var33*2; 6
/ 除 SET var410/3; 3.3333
DIV 整除 SET var510 DIV 3; 3
% 取模 SET var610%3 ; 12.比较运算符大于 12 False小于 21 False小于等于 22 True大于等于 32 True
BETWEEN 在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN 在集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True等于 23 False
, ! 不等于 23 False严格比较两个NULL值是否相等 NULLNULL True
LIKE 简单模式匹配 Guy Harrison LIKE Guy% True
REGEXP 正则式匹配 Guy Harrison REGEXP [Gg]reg False
IS NULL 为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True3.逻辑运算符4.位运算符
| 或与左移位右移位
~ 非(单目运算按位取反)注释mysql存储过程可使用两种风格的注释
双横杠--该风格一般用于单行注释
c风格/* 注释内容 */ 一般用于多行注释--------------------流程控制--------------------
一、顺序结构
二、分支结构
if
case三、循环结构
for循环
while循环
loop循环
repeat until循环注
区块定义常用
begin
......
end;
也可以给区块起别名如
lable:begin
...........
end lable;
可以用leave lable;跳出区块执行区块以后的代码begin和end如同C语言中的{ 和 }。--------------------输入和输出--------------------mysql存储过程的参数用在存储过程的定义共有三种参数类型,IN,OUT,INOUT
Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])IN 输入参数
表示该参数的值必须在调用存储过程时指定在存储过程中修改该参数的值不能被返回为默认值OUT 输出参数
该值可在存储过程内部被改变并可返回INOUT 输入输出参数
调用时指定并且可被改变和返回IN参数例子
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
SELECT p_in; --查询输入参数
SET p_in2; --修改
select p_in;--查看修改后的值
END;执行结果:
mysql set p_in1
mysql call sp_demo_in_parameter(p_in)
略
mysql select p_in;
略
以上可以看出p_in虽然在存储过程中被修改但并不影响p_id的值OUT参数例子
创建:
mysql CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
BEGIN
SELECT p_out;/*查看输出参数*/
SET p_out2;/*修改参数值*/
SELECT p_out;/*看看有否变化*/
END;执行结果:
mysql SET p_out1
mysql CALL sp_demo_out_parameter(p_out)
略mysql SELECT p_out;
略INOUT参数例子
mysql CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout2;
SELECT p_inout;
END;执行结果
set p_inout1
call sp_demo_inout_parameter(p_inout) //
略
select p_inout;
略附函数库
mysql存储过程基本函数包括字符串类型数值类型日期类型一、字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,… ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注mysql中处理字符串时默认第一个字符下标为1即参数position必须大于等于1
mysql select substring(’abcd’,0,2);
———————–
| substring(’abcd’,0,2) |
———————–
| |
———————–
1 row in set (0.00 sec)mysql select substring(’abcd’,1,2);
———————–
| substring(’abcd’,1,2) |
———————–
| ab |
———————–
1 row in set (0.02 sec)TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格二、数值类型ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注HEX()中可传入字符串则返回其ASC-11码如HEX(’DEF’)返回4142143
也可以传入十进制整数返回其十六进制编码如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注返回类型并非均为整数如(1)默认变为整形值
mysql select round(1.23);
————-
| round(1.23) |
————-
| 1 |
————-
1 row in set (0.00 sec)mysql select round(1.56);
————-
| round(1.56) |
————-
| 2 |
————-
1 row in set (0.00 sec)(2)可以设定小数位数返回浮点型数据mysql select round(1.567,2);
—————-
| round(1.567,2) |
—————-
| 1.57 |
—————-
1 row in set (0.00 sec)SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方三、日期类型ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分注可用在INTERVAL中的类型DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中datatype为mysql的数据类型如:INT, FLOAT, DATE, VARCHAR(length)例DECLARE l_int INT unsigned default 4000000;
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;
DECLARE l_date DATE DEFAULT 1999-12-31;
DECLARE l_datetime DATETIME DEFAULT 1999-12-31 23:59:59;
DECLARE l_varchar VARCHAR(255) DEFAULT This will not be padded;SQL Server版语法为了方便说明数据库使用SQL Server的示例数据库Northwind和pubs如果SQL Server中没有的话可以按下面的方法安装 1下载SQL2000SampleDb.msi,下载地址是 http://www.microsoft.com/downloads/details.aspx?FamilyId06616212-0356-46A0-8DA2-EEBC53A68034displaylangen 2安装后到默认目录C:\SQL Server 2000 Sample Databases 有instnwnd.sql instpubs.sql两个文件 3在sql server中运行这两个sql 就可以创建你Northwind和pubs数据库。 下面开始学T-SQL的语法 一.注释 -- 单行注释从这到本行结束为注释,类似C,c#中// /* … */ 多行注释类似CC#中/* … */ 二.变量int, smallint, tinyint, decimal,float,real, money ,smallmoney, text ,image, char, varchar。。。。。。 语法 DECLARE { {local_variable data_type} } [,...n] 例如 declare ID int --申明一个名为ID的变量类型为int型 三.在SQL Server窗口中打印出变量的值 语法 PRINT any ASCII text | local_variable | FUNCTION | string_expr 四.变量赋值 例如 --从数据表中取出第一行数据的ID,赋值给变量id然后打印出来 Declare ID int Set ID (select top(1) categoryID from categories) Print ID 在SQL中我们不能像代码那样直接给变量赋值例如id 1如果要达到这样的功能可以这样写 Declare ID int Set ID (select 1) -- 类似 ID1 Select id1 -- 类似 ID1 Print ID 五.变量运算(,-,*,/,……) 以下必要时候省略变量申明 Set ID (select 15) --类似 ID15 Set ID(select 1-ID) --类似 ID1-ID 六.比较操作符 ? (greater than). ? (less than). ? (equals). ? (less than or equal to). ? (greater than or equal to). ? ! (not equal to). ? (not equal to). ? ! (not less than). ? ! (not greater than). 没什么说的 七.语句块Begin … end 将多条语句作为一个块类似与CC#中的{ } 例如 Begin Set ID1 (select 1) Set ID2 (select 2) End 八.If if…else… 语法 IF Boolean_expression {sql_statement | statement_block} [ELSE {sql_statement | statement_block}] 例如 If id is not null Print ‘id is not null if ID 1 begin Set ID (select 1 1) end else begin set ID(select 12) end 上面的例子用到了比较操作符语句块和IF的语法。 九.执行其他存储过程 EXEC 例如 EXEC dbo.[Sales by Year] Beginning_Date’1/01/90’, Ending_Date’1/01/08’ 十.事务 语法 BEGIN TRAN[SACTION] [transaction_name | tran_name_variable] 例如 BEGIN TRAN -- 做某些操作例如Insert into … if error 0 BEGIN ROLLBACK TRAN END else BEGIN COMMIT TRAN END 十一.游标 我们可以在存储过程中用Select语句取出每一行数据进行操作这就需要用到游标。 语法 DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,...n]]] 例如 DECLARE au_id varchar(11), au_fname varchar(20) –申明变量 --申明一个游标 DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname FROM authors --打开游标 OPEN authors_cursor --取出值 FETCH NEXT FROM authors_cursor INTO au_id, au_fname --循环取出游标的值 WHILE FETCH_STATUS 0 BEGIN Print au_id Print au_fname Print ‘ ’ FETCH NEXT FROM authors_cursor INTO au_id, au_fname END CLOSE authors_cursor –关闭游标 DEALLOCATE authors_cursor --释放游标 我觉得上面的是存储过程常用的一些东东如果要更深入的了解更详细的帮助请参考SQL Server的帮助文档例子我自己做了一个,没有问题,你可以看一下 use Northwind go create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As Begin Declare tmp int Set Code Declare #cur_orders cursor for Select OrderID From Orders where OrderIDstartOrderID and OrderIDEndOrderID for read only Open #cur_Orders fetch next from #cur_orders into tmp while fetch_Status0 Begin Set CodeCode-convert(varchar(8),tmp) fetch next from #cur_orders into tmp End close #cur_Orders Deallocate #cur_Orders return End go 续2 String retnull; try{ Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver); String url jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseNameNorthwind; String usersa; String password; Connection conn DriverManager.getConnection(url,user,password); CallableStatement stmtconn.prepareCall(exec test ?,?,?); stmt.setInt(1,10248); stmt.setInt(2,10284); stmt.registerOutParameter(3,Types.VARCHAR); stmt.setString(3,ret); stmt.execute(); System.out.println(stmt.getString(3)); stmt.close(); stmtnull; conn.close(); }catch(ClassNotFoundException e){ e.printStackTrace(); }catch(SQLException e){ e.printStackTrace(); } 上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题 -- 新建一个表 Create table tmpOrders ( OrderID int, CustomerID nchar(5) ) --把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了 insert into tmpOrders Select distinct orderID,tmp from Orders create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As Begin Declare newOrderID int Declare newCustomerID nchar(5) Declare DummyInt int Declare DummyChar nchar(5) Set Code /* 1:1 temp table/formal table is synchronized tmpOrders --- Orders fetch from Orders, update tmpOrders */ -- for temp table Declare #cur_tmpOrders Cursor for select OrderID,CustomerID From tmpOrders where OrderIDstartOrderID and OrderIDEndOrderID for update --for formal table Declare #cur_orders cursor for Select OrderID,CustomerID From Orders where OrderIDstartOrderID and OrderIDEndOrderID for read only Open #cur_Orders Open #cur_tmpOrders fetch next from #cur_tmpOrders into DummyInt,dummyChar --Important!!! fetch next from #cur_orders into NewOrderID,NewCustomerID while fetch_Status0 Begin --Set CodeCode-convert(varchar(8),NewOrderID) --update tempOrders use corresponding Orders data Update tmpOrders set customerIDnewCustomerID where current of #cur_tmpOrders --pay attention to sequence of cursor fetch action! fetch next from #cur_tmpOrders into DummyInt,dummyChar if fetch_Status0 break; -- 没有行了 fetch next from #cur_orders into newOrderID,NewCustomerID End close #cur_Orders close #cur_tmpOrders Deallocate #cur_Orders Deallocate #cur_tmpOrders Set CodeOk return End 程序如下 try{ Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver); String url jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseNameNorthwind; String usersa; String password; Connection conn DriverManager.getConnection(url,user,password); CallableStatement stmtconn.prepareCall(exec test ?,?,?); stmt.setInt(1,10248); stmt.setInt(2,10284); stmt.registerOutParameter(3,Types.VARCHAR,1000); stmt.setString(3,ret); stmt.executeUpdate(); System.out.println(stmt.getString(3)); stmt.close(); stmtnull; conn.close(); connnull; }catch(ClassNotFoundException e){ e.printStackTrace(); }catch(SQLException e){ e.printStackTrace(); } 转载于:https://www.cnblogs.com/mcxiaochao/archive/2012/12/18/2823320.html
