DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `liangzi`$$

CREATE DEFINER=`admin`@`%` PROCEDURE `liangzi`(IN i INT)

xiaoliangzi:BEGIN

IF i=1 THEN

SELECT 1;

LEAVE xiaoliangzi;

SELECT 'BBS';

ELSEIF i=2 THEN

SELECT 2;

LEAVE xiaoliangzi;

SELECT 'BBS';

ELSE

SELECT 3;

SELECT 'BBS';

END IF;

SELECT 'BLOG.51CTO.COM';

END$$

DELIMITER ;

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

mysql> call liangzi(2);

+---+

| 2 |

+---+

| 2 |

+---+

1 row in set (0.00 sec)

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

mysql> call liangzi(3);

+---+

| 3 |

+---+

| 3 |

+---+

1 row in set (0.00 sec)

+-----+

| BBS |

+-----+

| BBS |

+-----+

1 row in set (0.00 sec)

+----------------+

| BLOG.51CTO.COM |

+----------------+

| BLOG.51CTO.COM |

+----------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

mysql> call liangzi(4);

+---+

| 3 |

+---+

| 3 |

+---+

1 row in set (0.00 sec)

+-----+

| BBS |

+-----+

| BBS |

+-----+

1 row in set (0.00 sec)

+----------------+

| BLOG.51CTO.COM |

+----------------+

| BLOG.51CTO.COM |

+----------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

使用SELECT …INTO语句为变量赋值

在MySQL存储过程中，可以使用SELECT …INTO语句对变量进行赋值，该语句在数据库中进行查询，并将得到的结果赋值给变量。SELECT …INTO语句的语法格式如下：

SELECT col_name[,...] INTO var_name[,...] table_expr

col_name：要从数据库中查询的列字段名；

var_name：变量名，列字段名按照在列清单和变量清单中的位置对应，将查询得到的值赋给对应位置的变量；

table_expr：SELECT语句中的其余部分，包括可选的FROM子句和WHERE子句。

需要注意的是，在使用SELECT …INTO语句时，变量名不能和数据表中的字段名不能相同，否则会出错。范例语句：

create procedure getMsg

()

Begin

declare v_title varchar(30);

declare v_content varchar(100);

select title,content into v_title,v_content from news where artId=333;

End

将变量值返回给调用者

在存储过程中定义的变量，经过一系列的处理之后，结果值可能需要返回给存储过程调用者。那么如何返回呢？方便的做法是使用SELECT语句将变量作为结果集返回，因此，在上面一段代码的基础上，加上一句：

create procedure getMsg

()

Begin

declare v_title varchar(30);

declare v_content varchar(100);

select title,content into v_title,v_content from news where artId=333;

select v_title,v_content;

End

【转载】Sqlserver存储过程中使用Select和Set给变量赋值

Sqlserver存储过程是时常使用到的一个数据库对象,在存储过程中会使用到Declare来定义存储过程变量,定义的存储过程变量可以通过Set或者Select等关键字方法来进行赋值操作,使用Set对存 ...

mysql 存储过程中使用动态sql语句

Mysql 5.0 以后,支持了动态sql语句,我们可以通过传递不同的参数得到我们想要的值 这里介绍两种在存储过程中的动态sql: set sql = (预处理的sql语句,可以是用concat拼接的 ...

存储过程中使用select……into

在MySQL存储过程中使用SELECT -INTO语句为变量赋值: 用来将查询返回的一行的各个列值保存到局部变量中. 要求: 查询的结果集中只能有1行. SELECT col_name[,...] I ...

mysql 存储过程中limit

1.mysql的高版本(5.5),存储过程中的limit可以使用变量,如下:select * from student limit iStart,iNum; 2.mysql的低版本(5.1),存储过程 ...

【转】MySQL存储过程中使用动态行转列

MySQL存储过程中使用动态行转列 最近做项目关于数据报表处理,然而数据库存储格式和报表展现形式不同,需要进行一下行转列的操作,在做上一个项目的时候也看了一下,但是后来换了读取方式,也就没深入研究这个 ...

mysql -- 存储过程中 declare 和 set 定义变量的区别

mysql存储过程中,定义变量有两种方式:1.使用set或select直接赋值,变量名以 @ 开头.例如:set @var=1;可以在一个会话的任何地方声明,作用域是整个会话,称为会话变量. 2.以 ...

MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别，退出存储过程方法

在MySQL存储过程的语句中有三个标准的循环方式:WHILE循环,LOOP循环以及REPEAT循环.还有一种非标准的循环方式:GOTO,不过这种循环方式最好别用,很容易引起程序的混乱,在这里就不错具体 ...

MYSQL存储过程中常使用的命令记录

MYSQL存储过程中常使用的命令记录 1.触发器trigger 查看:show triggers; 2.存储过程procedure 查看:show procedure status; 查看详细:sho ...

MYSQL存储过程中的IN、OUT和INOUT

MYSQL存储过程中的IN.OUT和INOUT,不能简单理解为一个方法的参数和返回值,而是面向整个过程上下文变量的. 一.MySQL 存储过程参数(in) 基本可以理解为传入function的参数,而 ...

随机推荐

JAVA_build_ant_FixCRLF

Description Adjusts a text file to local conventions. The set of files to be adjusted can be refined ...

hdoj 1247 Hat’s Words(字典树)

题目链接:http://acm.hdu.edu.cn/showproblem.php?pid=1247 思路分析:题目要求找出在输入字符串中的满足要求(该字符串由输入的字符串中的两个字符串拼接而成)的 ...

量化投资技术分析工具---ipython使用

量化投资实际上就是分析数据从而做出决策的过程python数据处理相关模块NumPy:数组批量计算pandas:灵活的表计算Matplotlib:数据可视化 学习目标:用NumPy+pandas+Mat ...

Beta冲刺4/7

目录 摘要 团队部分 个人部分 摘要 队名:小白吃 组长博客:hjj 作业博客:beta冲刺(4/7) 团队部分 后敬甲(组长) 过去两天完成了哪些任务 整理博客 ppt模板 接下来的计划 做好机动. ...

mysql 索引查询 、创建 create index 与 add index 的区别

1.索引查询 ------TABLE_SCHEMA  库名:TABLE  表名 ------AND UPPER(INDEX_NAME) != 'PRIMARY'  只查询索引,不需要主键 SELECT ...

GridView不执行RowCommand事件

web.config里把viewstate禁用了.如果是的话在页面里单独开起来就好了.

Java 图形化界面设计(GUI)实战练习(代码)

关于Java图形化界面设计,基础知识网上可搜,下面简单介绍一下重点概念,然后就由浅入深代码实例. 程序是为了方便用户使用的,Java引入图形化界面编程. 1.JFrame 是容器类 2.AWT 是抽象 ...

phpstorm 2017版代码提示功能开启解决方案

安装好phpstorm 2017之后 发现代码高亮和函数自动提示都失效了 在phpstorm底部面板的信息提示处发现有一条系统消息: 12:04:18 Power save mode is on Co ...

使用uGUI系统玩转标准俄罗斯方块

使用uGUI系统玩转标准俄罗斯方块 笔者使用的Unity3D版本是4.6b17.由于一些工作上的一些事情导致制作的进度被严重滞后.笔者实际用于开发俄罗斯方块的时间,大概也就2-3天吧. 开始前的准备 ...

SynchronizationContext应用

这个类的应用,官方的说明并不是很多,主要原因是因为微软又出了一些基于SynchronizationContext的类.比如:BackgroundWorker 大家写程序时经常碰到子线程调用UI线程的方 ...

Oracle的存储过程，是我们使用数据库应用开发的重要工具手段。在存储过程中，我们大部分应用场景都是使用DML语句进行数据增删改操作。本篇中，我们一起探讨一下数据定义语句DDL在存储过程中使用的细节和要点。

1、“借道而行”的DDL

从Oracle PL/SQL和存储过程程序开发原则上，应该是不鼓励在SP中使用DDL语句的。首先一个表现，就是Oracle在编译时就不允许直接在SP中使用DDL语句。下面我们使用Oracle 10gR2作为实验环境。

SQL> select * from v$version;

BANNER

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

Oracle Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

NLSRTL Version10.2.0.1.0 – Production

建立存储过程p_test_nc，进行简单的数据表创建。

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4   create table t (id number);

5 end P_TEST_NC;

6 /

Warning: Procedure created with compilation errors

SQL> select name, text from user_errors;

NAME      TEXT

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

P_TEST_NC PLS-00103:出现符号"CREATE"在需要下列之一时：

begin case declare exit

for goto if loop mod null pragma raise return select update

while with

<<

close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe

显然，在编译时Oracle就报错不允许存储过程创建。之后的实验drop和truncate table操作，也都是不允许直接在存储过程中书写DDL语句。说明起码使用直接的DDL语句，存储过程是不能编译通过的。

那么，有没有什么折中的方法呢？我们说是有的，就是借助“execute immediate”方法，“绕过”编译过程中对DDL的屏蔽。我们使用truncate table DDL语句实验。

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4   execute immediate'truncate table t';

5 end P_TEST_NC;

6 /

Procedure created

编译通过了，DDL语句以一个字符串的形式避开了编译时Oracle的语法检查，编译成功。那么，执行起来会不会报运行时错误呢？

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

执行成功，说明：在Oracle存储过程中，可以使用exectue immediate语句绕开编译时对DDL语句的检查，生成运行代码。

2、SP中DDL权限

任何程序编译执行都会伴随着语法语义的一系列检查。使用execute immediate虽然可以回避编译时检查，但是SQL语句还是面临着运行时检查的问题。下面看实验的例子。

--在scott用户下进行试验；

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4   execute immediate 'create table t(id number)';

5 end P_TEST_NC;

6 /

Procedure created–编译时通过；

SQL> exec p_test_nc;

begin p_test_nc; end;

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

在用户自己的schema下创建数据表，难道是不允许的吗？显然不是。

SQL> create table m (id number);

Table created

单独创建是允许的，说明是由于权限机制导致的问题。我们切换到sys用户上，提高scott用户权限。

Connected as SYS

--赋予最高创建数据表的系统权限；

SQL> grant create any table to scott;

Grant succeeded

切换回scott用户，继续实验。

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

SQL> select * from t;

ID

----------

执行成功！这个原因是什么呢？还是由于存储过程权限体系特点和DDL语句特点共同造成的。

在之前笔者的系列文章《所有者权限和调用者权限》(http://space.itpub.net/17203031/viewspace-692161)中，介绍了Oracle存储过程采用的两种权限体系方式和role权限在存储过程执行中的特殊性。

默认情况下，Oracle对存储过程是使用所有者权限，也就是说：如果用户B调用了用户A schema下的一个存储过程，其中使用的对象权限和系统权限，全部都是用户A的。如果用户A没有权限，用户B执行要报错。

同时，用户的角色权限在进入存储过程后，会被剥离掉，是不其效果的。

结合上面的实验，就好解释了：scott自身只拥有一个resource的角色权限，单独在SQL中使用没有问题。进入到SP之后，这个create table的权限就被剥离掉了。而该SP存在被其他用户调用生成数据表的可能。所以会在运行时报错权限不足。

当我们显示的赋予scott用户create any table/create table之后，系统权限就可以渗透到SP中起效果了。

这并不是解决该问题的唯一方法。此处我们可以使用调用者权限机制，改写SP代码。首先我们剔除掉scott的create any table权限。

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as SYS

SQL> revoke create any table from scott;

Revoke succeeded

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

SQL> exec p_test_nc;

begin p_test_nc; end;

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

我们改写代码为：

SQL> create or replace procedure P_TEST_NC

2 Authid Current_User

3 is

4 begin

5   execute immediate 'create table t (id number)';

6 end P_TEST_NC;

7 /

Procedure created

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

执行成功，这里使用“authid Current_user”将存储过程转化为调用者权限。每次调用存储过程，都是动态根据调用者的权限构成去判定是否有权限，这样就回避了该问题的出现。

总之：在使用DDL在存储过程中时，权限管理和使用的复杂度是在增加。

4、DDL对事务的提交影响

将DDL语句放置在存储过程中，潜在最大风险就是对事务管理的破坏。在Oracle中，如果调用一个DDL语句，潜藏效果就是将当前会话的未提交事务进行提交。这个过程显然是对原有的事务逻辑破坏。

SQL> create table m (id number);

Table created

SQL> select * from m;

ID

----------

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4   insert into m values (3);

5  execute immediate 'truncate table t';

6

7   rollback;

8 end P_TEST_NC;

9 /

Procedure created

--执行代码

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

--事务提交

SQL> select * from m;

ID

----------

3

从上面的例子上，我们可以清楚的看到现象。由于中间的truncate table操作，引起数据表m的插入操作被提交commit。而真正的事务逻辑可能是一个rollback。

所以，在SP中使用DDL命令，可能引起业务逻辑的不可控提交和数据不一致，这个风险在任何应用中是不可以允许的。

那么，有没有方法回避这个过程呢？经一个同事提醒，的确可以使用手段回避。

5、DDL与自治事务

自治事务(AUTONOMOUS_TRANSACTION)是保证在事务进行过程中一段独立的事务过程。如果在DDL操作外套入一个自治事务过程，是否就可以回避问题了。

SQL> select * from m;

ID

----------

SQL> create or replace procedure P_TEST_NC is

2   procedure p_inner_test

3   is

4  PRAGMA AUTONOMOUS_TRANSACTION;

5   begin

6     --调用ddl

7     execute immediate 'truncate table t';

8   end;

9 begin

10   insert into m values (3);

11   p_inner_test;

12

13   rollback;

14 end P_TEST_NC;

15 /

Procedure created

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

\

SQL> select * from m;

ID

----------

实验成功，通过自治事务的确可以回避DDL的事务问题。

6、结论

DDL在SP中，与常规的DML操作差异很大。这种差异不仅仅是语法上，更多的是权限、事务等更深层次复杂的差异。所以，从Oracle的角度看，尽量少在SP中使用DDL语句，避免出现不可控的问题。