谈谈HINT　/+parallel(t,4)/在SQL调优中的重要作用

2023-11-12 来源：花图问答

/*+parallel(t,4)*/在大表查询等操作中能够起到良好的效果,基于并行查询要启动并行进程、分配任务与系统资源、合并结果集，这些都是比较消耗资源,但我们为能够减少执行事务的时间使用parallel HINT还是值得的,尤其在ODS系统中报表统计等方面更有使用意义.一般而言主要在如下情况使用parallel HINT1.表的数据量很大,超过一千万; 2.数据库主机是多个CPU;3.系统的当前负载较低;

简单的测试如下,效果很明显的:

SQL> select /*+parallel(t,4)*/count(*) from t; COUNT(*)---------- 30245882已用时间: 00: 01: 32.04Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5411 Card=1) 1 0 SORT (AGGREGATE) 2 1 SORT* (AGGREGATE) :Q351880 00 3 2 TABLE ACCESS* (FULL) OF ‘t‘ (Cost=5411 Car :Q351880 d=2822493) 00 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F ROM (SELECT /*+ NO_EXPAND ROWID(A2) 3 PARALLEL_COMBINED_WITH_PARENT

SQL> select count(*) from t; COUNT(*)---------- 30245882已用时间: 00: 04: 34.02Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF ‘t‘

谈谈HINT　/*+parallel(t,4)*/在SQL调优中的重要作用

标签：table 多个测试 pre serial count 2.0 tab card

小编还为您整理了以下内容，可能对您也有帮助：

sql中有一些*/+ /*之类的符号是什么意思？

Oracle中这种东西叫做hint，是一种优化SQL的工具。不同的hint会导致不同的优化模式。

例如一句SQL可能这样写：

select a.id, a.name

from a, b

where a.date=b.date

假如这个语句执行起来很慢。我们就可以用hint指定某一种优化模式，使运行速度加快。比如说指定选用a表的a_indx这个索引，及b表的b_indx这个索引，可以写作

select /*+ index(a a_indx) index(b b_indx) */

a.id, a.name

from a, b

where a.date = b.date

这样就实现了对SQL的优化。除了index这个hint以外，还有all_rows, first_rows，等等。

这个网站提供一个教程：http://oracle.chinaitlab.com/optimize/14380.html

请教一个sql使用parallel hint的问题

测试了第一种方式:
设置了index的parallel degree后
在9.2.0.1和9.2.0.8下
select count(1) from xx_test;如果使用这个语句的话,则不会并发(10g后就会并发)
select /*+ parallel(t,4) count(1) from xx_test t;可以并发查询，但是需要在enable parallel query之后可以并行.

在10.2.0.1数据库下
select count(1) from xx_test;
select /*+ parallel(t,4) count(1) from xx_test t;
上面两个都会并行查询,前提都是要enable parallel query.

对于使用parallel_index hint的第二种方法
我在10g中测试没有问题，但是在9i中我使用下面的语句却不会并行
select /*+ parallel_index(xx_test,4) */ count(1) from xx_test;

第三种方法在两个版本中都没有问题
select /*+ parallel(t,4)full(t) */

请教一个sql使用parallel hint的问题

数据库中这个parallel函数是什么意思啊？例如这行代码： SELECT /*+ Parallel(t,8) */ * FROM a;

parallel不是函数，/*

*/在Oracle中是hint，这句sql意思是强行启用并行模式来执行当前SQL，按理是数字越大，执行效率越高，但与CPU个数有关

parallel(t,4) parallel(4)

parallel(4) 是对所有表都开4个并行，比如你那张表left join了其他三个表，那么总共就有4个表开并行4X4=16 个进程如果配置不够大可能会崩.
parallel(t,4)就是单独对比较慢的表开进程

parallel(t,4) parallel(4)

oracle中在编写存储过程启动多线程的问题？

楼上holly_866指出的方向是对的，但是拼写错误。。

ORACLE的多线程体现在DML上在操作时，如果见到/* +*/ （平时写备注、评论块的/**/符号中有加号，那么则表明了使用Oracle Hint. /*+ parallel(表名,并发数)*/ (有时候写作Append parallel,或者有时候直接写Append) .

这里的并发数可以省略，也可以自己规定。如果是省略了，那么它的设置是DBA完成的。参数可以在V$parameter这个view里找到。

如果单纯从开发的角度看：

ORACLE多线程可以提高某些语句查询的速度（不是一定的，取决于你的核，和服务器, 我原本有一些材料可以图示进程数和速度的关系，可惜一时找不到，如果需要可以再联系）。具体使用时，做几个测试看看速率提高多少。比如我以前做数据仓库时，一个测试要用大概27分钟， 2进程大概是23分钟。 4进程开提高到了快22（21分50多秒）分钟。再提高进程数其实作用就递减了。

从数据库整体来看：

多线程并不是优化了你的查询速率，而是使用了更多数据库的资源（其他用户或者进程的资源）换来你的语句速率的提高。联系一下你的DBA，因为很有可能你用了多进程后，从DBA的EM上会发现你资源在某时间段内用的很高，甚至会给出警告。找DBA给你调一下进程数，或者给你建议。还是同样的上次数据仓库的例子。我用8进程时， DBA和我一起在看EM，出现了一些资源占用太多的情况，那么他要么就要调整空间，要么就要讨论下是否值得为了短短的几分钟而降低数据库的其他运行效率。如果非要需要，那么看是否可以将这样的数据更新放在晚上，或者数据库比较空闲的时间段。

上面讲的都是概念，如果你想看详细一点的运算方法，可以找书看，我这里的资料可能需要整理，也不方便，建议自己找一下。不需要看的太多，一般了解则可。

ORACLE里的hint(二)

前面已经对Hint有了大概的介绍，已经了解到Hint可以影响优化器对于执行计划的选择，但这种影响不是强制性的，优化器在某些情况下可能会忽略目标SQL中的Hint：

1 使用的Hint有语法或者拼写错误

一旦使用的Hint中有语法或者拼写错误，Oracle就会忽略该Hint，看几个示例SQL：

select /*+ ind(emp pk_emp) */* from emp;

select /*+ index(emp pk_emp */* from emp;

select /* + index(emp pk_emp) */* from emp;

select */*+ index(emp pk_emp) */ from emp;

select /*+ index(scott.emp pk_emp) */* from emp;

select /*+ index(emp pk_emp) */* from emp e;

select /*+ index(emp emp_pk) */* from emp;

select /*+ full(t2) */ t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from detp t where t2.loc='CHICAGO');

实际上，上述8条SQL中的Hint都是无效的，它们都会被Oracle忽略。

1是因为关键字应该是"index"而不是"ind"

2是因为漏掉了一个右括号

3是因为Hint中第一个*和+之间出现了空格

4是因为Hint出现的位置不对，它应该出现在*前面

5是因为emp表前面带上了SCHEME名称

6是因为没有emp表的别名

7是因为索引名称写错了

8是因为Hint跨了Query Block。Hint生效的范围公限于它本身所在的Query Block，如果将某个Hint生将范围扩展到它所在的Query Block之外而又没在该Hint中指定其生效的Query Block名称的话，Oracle就会忽略该Hint。

2 使用的Hint无效

即使语法是正确的，但如果由于某种原因导致Oracle认为这个Hint无效，则Oracle还是会忽略该Hint。

看几个实例

scott@TEST>set autotrace traceonly

scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where loc='CHICAGO';

Execution Plan

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

Plan hash value: 492093765

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10 | 300 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 300 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_DEPT_LOC | 4 | | 1 (0)| 00:00:01 |

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

从上面的输出可以看出，上面的SQL的执行计划走的是对索引IDX_DEPT_LOC的索引范围扫描，说明Hint生效了，但是如果把where条件替换为与索引IDX_DEPT_LOC毫不相关的deptno=30，再来看执行情况

scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where deptno=30;

Execution Plan

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

Plan hash value: 2852011669

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |

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

从上面的输出可以看出，执行计划走的是对主键PK_DEPT的INDEX UNIQUE SCAN，面不是Hint里的IDX_DEPT_LOC。这就说明Hint在这个SQL失效了。

即使不改where条件，如果把索引IDX_DEPT_LOC删除，这个Hint也会失效，对于这个失效原因，就举这个例子了。

3 使用的Hint自相矛盾

如果使用的组合Hint是自相矛盾的，则这些自相矛盾的Hint都会被Oracle忽略。但Oracle只会将自相矛盾的Hint全部忽略掉，但如果使用的组合Hint中还有其他有效的Hint，则这些有效Hint不受影响。

看一个使用自相矛盾Hint的实例，先执行单个Hint的SQL

scott@TEST>select /*+ index_ffs(dept pk_dept)*/ deptno from dept;

4 rows selected.

Execution Plan

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

Plan hash value: 3383998547

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 4 | 12 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 |

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

从上面的输出可以看出单独使用上面的两个Hint都能被Oracle生效，但如果这两个Hint合并到一起使用就不是那么回事了

4 使用的Hint受到了查询转换的干扰

有时候，查询转换也会导致相关的Hint失效，即Hint被Oracle忽略还可能是因为受到了查询转换的干扰。

下面来看一个因为使用了查询转换而导致相关Hint被Oracle忽略掉的实例。

创建一个测试表jobs

scott@TEST>create table jobs as select empno,job from emp;

Table created.

构造一个SQL

select /*+ ordered cardinality(e 100) */

e.ename, j.job, e.sal, v.avg_sal

from emp e,

jobs j,

(select /*+ merge */

e.deptno, avg(e.sal) avg_sal

from emp e, dept d

where d.loc = 'chicago'

and d.deptno = e.deptno

group by e.deptno) v

where e.empno = j.empno

and e.deptno = v.deptno

and e.sal > v.avg_sal

order by e.ename;

上面的SQL是两个表(EMP和JOBS)和内嵌视图V关联的SQL，其中内嵌视图V又是由表EMP和DEPT关联后得到的。在此SQL中使用了三个Hint，其中merge用于让内嵌视图V做视图合并，ordered表示上述SQL在执行时表EMP、JOBS和内嵌视图V的连接顺序应该和它们在该SQL的SQL文本中出现的顺序一致，即它们应该是按照从左至右的顺序依次做表连接。

如果上述三个Hint都生效的话，那目标SQL的执行计划中应该不会出现关键字“VIEW”(表示做了视图合并，体现了Merge Hint的作用)，表EMP、JOBS和内嵌视图V的连接应该会变成表EMP、JOBS和内嵌视图V所对应的基表EMP和DEPT的连接，且连接的先后顺序应该是EMP->JOBS->内嵌视图V所对应的基表EMP和DEPT(体现了Ordered Hint的作用)，外围查询中表EMP的扫描结果所对应的Cardinality的值应该是100(体现了Cardinality Hint的作用)。

现在看一下实际情况，执行上面的SQL:

1 scott@TEST>select /*+ ordered cardinality(e 100) */

2 e.ename, j.job, e.sal, v.avg_sal

3 from emp e,

4 jobs j,

5 (select /*+ merge */

6 e.deptno, avg(e.sal) avg_sal

7 from emp e, dept d

8 where d.loc = 'chicago'

9 and d.deptno = e.deptno

10 group by e.deptno) v

11 where e.empno = j.empno

12 and e.deptno = v.deptno

13 and e.sal > v.avg_sal

14 order by e.ename;

no rows selected

Execution Plan

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

Plan hash value: 930847561

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 156 | 19656 | 15 (20)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | SORT GROUP BY | | 156 | 19656 | 15 (20)| 00:00:01 |

|* 3 | HASH JOIN | | 156 | 19656 | 14 (15)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 |

|* 5 | HASH JOIN | | 467 | 53705 | 10 (10)| 00:00:01 |

| 6 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 |

|* 7 | HASH JOIN | | 100 | 8900 | 7 (15)| 00:00:01 |

| 8 | TABLE ACCESS FULL| EMP | 100 | 5800 | 3 (0)| 00:00:01 |

| 9 | TABLE ACCESS FULL| JOBS | 14 | 434 | 3 (0)| 00:00:01 |

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

从上面的执行计划可以看出，确实没有出现关键字“VIEW”，表EMP的扫描结果所对应的Cardinality的值确实是100，但连接顺序不是上面提到的顺序，而是先选择的表DEPT。这说明上述三个Hint中的Merge Hint和Cardinality Hint生效了，但Ordered Hint被Oracle忽略了。这是因为受到了查询转换的干扰(对内嵌视图V做视图合并是一种查询转换)。

为了证明上述SQL的Ordered Hint被Oracle忽略是因为受到了查询转换的干扰，现在将内嵌视图V中的merge替换为no_merge(不让内嵌视图做视图合并)，再次执行该SQL：

1 scott@TEST>select /*+ ordered cardinality(e 100) */ e.ename, j.job, e.sal, v.avg_sal

from emp e, jobs j, (select /*+ no_merge */ e.deptno, avg(e.sal) avg_sal from emp e, dept d where d.loc = 'chicago' and d.deptno = e.deptno group by e.deptno) v where e.empno = j.empno and e.deptno = v.deptno and e.sal > v.avg_sal order by e.ename;

no rows selected

Execution Plan

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

Plan hash value: 2898000699

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 8 | 728 | 14 (22)| 00:00:01 |

| 1 | SORT ORDER BY | | 8 | 728 | 14 (22)| 00:00:01 |

|* 2 | HASH JOIN | | 8 | 728 | 13 (16)| 00:00:01 |

|* 3 | HASH JOIN | | 100 | 6500 | 7 (15)| 00:00:01 |

| 4 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 |

| 6 | VIEW | | 5 | 130 | 6 (17)| 00:00:01 |

| 7 | HASH GROUP BY | | 5 | 185 | 6 (17)| 00:00:01 |

| 8 | MERGE JOIN | | 5 | 185 | 6 (17)| 00:00:01 |

|* 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 |

| 10 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 11 | SORT JOIN | | 14 | 364 | 4 (25)| 00:00:01 |

| 12 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 |

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

从上面的执行计划中可以看出，出现了“VIEW”关键字，说明没有做视图合并，表EMP对就的Cardinality为100，连接顺序与前面预想的一致，这说明在禁掉了查询转换后之前被忽略的Ordered Hint又生效了。

5 使用的Hint受到了保留关键字的干扰

Oracle在解析Hint时，是按照从左到右的顺序进行的，如果遇到的词是Oracle的保留关键字，则Oracle将忽略这个词以及之后的所有词；如果遇到词既不是关键字也不是Hint，就忽略该词；如果遇到的词是有效的Hint，那么Oracle就会保留该Hing。

正是由于上述Oracle解析Hint的原则，保留关键字也可能导致相关的Hint失效。

以上介绍了5种Hint被Oracle忽略的情况，在实例使用过程中一定要注意使用方法，使用正确有效的Hint来提升SQL执行效率，避免Hint被Oracle忽略

ORACLE里的hint(二)

pl-sql中ordered什么场合使用

在执行sql优化的时候使用。
oracle的sql优化基本上可以说有一个目标，两种方式。
一个目标就是说：要减少查询所需要的逻辑读次数，如果概念不懂可以查询网络；
两种方式可以概括为：
1，增加适当的索引；
2，通过搜集统计信息改变执行计划，或者手工使用hints影响oracle对执行计划的选择；

这里你提到的ordered就是hint的一种，它的作用是，指定按照表在from后面出现的先后顺序对表进行关联，方式是将ordered关键词放在hints的专用格式中 /*+ ordered */
比如说，对于下面的sql来说
select * from t1,t2
where t1.id = t2.id
我们假设oracle当前的默认执行计划是t1为驱动表，t2为被探测表，
select
nest loop
t1
t2
那么我们可以通过hint，ordered强制让oracle用t2作为驱动表，方式如下
select /*+ ordered */ *
from t2,t1
where t1.id = t2.id

这是的执行计划将是
select
nested loop
t2
t1

-------------------------------
ORDERED提示强制Oracle按照From子句中表出现的顺序进行表连接。

通过ordered提示，可以避免CBO SQL解析过程中的表连接评估，从而避免Oracle产生错误的执行计划，或者强制Oracle按照我们指定的方式执行。

在很多时候，当我们清楚地了解数据结构和数据分布之后，就可以通过ORDERED提示来提高SQL性能。

通过以下例子我们来说明一下Ordered提示的作用.

1.不加Hints时SQL的执行计划

SQL> set autotrace trace explainSQL> SELECT COUNT (*) 2 FROM t_small, t_max, t_middle 3 WHERE t_small.object_id = t_middle.object_id 4 AND t_middle.object_id = t_max.object_id;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=194 Card=400 Bytes=4800) 3 2 HASH JOIN (Cost=42 Card=100 Bytes=800) 4 3 TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400) 5 3 TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788) 6 2 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)

我们可以通过10053事件跟踪一下该SQL的解析:

SQL> alter session set events='10053 trace name context forever,level 1';Session altered.SQL> explain plan for 2 SELECT COUNT (*) 3 FROM t_small, t_max, t_middle 4 WHERE t_small.object_id = t_middle.object_id 5 AND t_middle.object_id = t_max.object_id; Explained.

查看Trace文件可以看到,Oracle需要进行3! (6)次表连接顺序的评估:

bash-2.03$ cat testora9_ora_10862.trc |grep "Join order"Join order[1]: T_SMALL [T_SMALL] T_MIDDLE [T_MIDDLE] T_MAX [T_MAX] Join order[2]: T_SMALL [T_SMALL] T_MAX [T_MAX] T_MIDDLE [T_MIDDLE] Join order[3]: T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL] T_MAX [T_MAX] Join order[4]: T_MIDDLE [T_MIDDLE] T_MAX [T_MAX] T_SMALL [T_SMALL] Join order[5]: T_MAX [T_MAX] T_SMALL [T_SMALL] T_MIDDLE [T_MIDDLE] Join order[6]: T_MAX [T_MAX] T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL]

2.当我们使用Ordered提示之后

SQL的执行计划如下(from子句后的表顺序作了调整):

SQL> SELECT /*+ ordered */ COUNT (*) 2 FROM t_middle, t_small, t_max 3 WHERE t_small.object_id = t_middle.object_id 4 AND t_middle.object_id = t_max.object_id; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=197 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=197 Card=400 Bytes=4800) 3 2 HASH JOIN (Cost=45 Card=100 Bytes=800) 4 3 TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788) 5 3 TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400) 6 2 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)

再看10053的跟踪Trace文件:

bash-2.03$ grep "Join order" testora9_ora_10918.trcJoin order[1]: T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL] T_MAX [T_MAX]

Oracle只需要按照表在From子句中的出现顺序进行连接,从而按照我们的意图进行解析或执行.

这就是Ordered提示的基本作用,本例只是一个示范说明,后者的执行计划使得Cost激增,在实际应用中,我们当然是不希望看到此类增长的.

pl-sql中ordered什么场合使用

SQL 中UPDATE用法

Update是一个数据库SQL语法用语，用途是更新表中原有数据，单独使用时使用where匹配字段。

语法为：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例如：Update table_name Set column_name = new_value Where column_name = some_value

扩展资料

update使用注意事项：

1、sp_updatestats可以更新统计信息到最新。

2、低内存会导致未被客户端连接的查询计划被清除。

3、修改表结构，修改索引后，查询计划会被清除，可以再修改后运行几遍查询。

4、使用update时候，order by 会影响查询速度，where中使用函数则会调用筛选器进行扫描，扫描表要尽量避免。

参考资料来源：百度百科—update

oracle 中sql语句怎么加多个强制索引

使用hint技术,表别名+索引名
select/*+
INDEX(pa IDX_PAGREE_1)
INDEX(pi IDX_PITEM_5)
INDEX(pd IDX_PRODUCTS_3)
*/ *
from table1 c,
table2 pa,
table3 pi,
table4 pd
where pa.customerid = c.customerid
and pi.purchaseagreementid = pa.id
and pi.proctid = pd.id
and pd.statusid=3601;

oracle 中sql语句怎么加多个强制索引

SQL SERVER里面如何在存储过程里面获取另一个存储过程所返回的表的数据?

首先需要知道“另一个存储过程”的结果集的所有列的类型。

假设“另一个存储过程”的名字是sp1，没有参数，返回的结果集共3列，全部为int型，那么“存储过程”里添加一个与结果集列数相同的临时表或表变量用于接收“另一个存储过程”的结果集

如下

CREATE PROCEDURE sp2

DECLARE @t table(a int,b int,c int)

INSERT INTO @t(a,b,c)

EXEC sp1

SELECT * FROM @t

使用SQLSERVER存储过程可以很大的提高程序运行速度，简化编程维护难度，现已得到广泛应用。

创建存储过程

和数据表一样，在使用之前需要创建存储过程，它的简明语法是：

引用:

Create PROC 存储过程名称

[参数列表（多个以“,”分隔）]

SQL 语句

例：

引用:

Create PROC upGetUserName

@intUserId INT,

@ostrUserName NVARCHAR(20) OUTPUT -- 要输出的参数

BEGIN

-- 将uName的值赋给 @ostrUserName 变量，即要输出的参数

Select @ostrUserName=uName FROM uUser Where uId=@intUserId

END

其中 Create PROC 语句（完整语句为Create PROCEDURE）的意思就是告诉SQL SERVER，现在需要建立一个存储过程，upGetUserName 就是存储过程名称，@intUserId 和 @ostrUserName 分别是该存储过程的两个参数，注意，在SQL SERVER中，所有用户定义的变量都以“@”开头，OUTPUT关键字表示这个参数是用来输出的，AS之后就是存储过程内容了。只要将以上代码在“查询分析器”里执行一次，SQL SERVER就会在当前数据库中创建一个名为“upGetUserName”的存储过程。你可以打开“企业管理器”，选择当前操作的数据库，然后在左边的树型列表中选择“存储过程”，此时就可以在右边的列表中看到你刚刚创建的存储过程了（如果没有，刷新一下即可）。

二、存储过程的调用

之前已经创建了一个名为“upGetUserName”的存储过程，从字面理解该存储过程的功能是用来取得某一个用户的名称。存储过程建立好了，接下来就是要在应用程序里调用了，下面看一下在ASP程序里的调用。

引用:

Dim adoComm

’// 创建一个对象，我们用来调用存储过程

Set adoComm = CreateObject("ADODB.Command")

With adoComm

’// 设置连接，设 adoConn 为已经连接的 ADODB.Connection 对象

.ActiveConnection = adoConn

’// 类型为存储过程，adCmdStoredProc = 4

.CommandType = 4

’// 存储过程名称

.CommandText = "upGetUserName"

’// 设置用户编号

.Parameters.Item("@intUserId").Value = 1

’// 执行存储过程

.Execute

’// 取得从存储过程返回的用户名称

Response.Write "用户名：" & .Parameters.Item("@ostrUserName").Value

End With

’// 释放对象

Set adoComm = Nothing

通过以上两步，已经可以创建和使用简单的存储过程了。下面来看一个稍微复杂点的存储过程，以进一步了解存储过程的应用。

三、存储过程的实际应用

用户登录在ASP项目中经常会使用到，但使用存储过程来做验证可能不多，那么做例子，写一个简单的用户登录验证的存储过程。

引用:

Create PROC upUserLogin

@strLoginName NVARCHAR(20),

@strLoginPwd NVARCHAR(20),

@blnReturn BIT OUTPUT

-- 定义一个临时用来保存密码的变量

DECLARE @strPwd NVARCHAR(20)

BEGIN

-- 从表中查询当前用户的密码，赋值给 @strPwd 变量，下面要对他进行比较

Select @strPwd=uLoginPwd FROM uUser Where uLoginName=@strLoginName

IF @strLoginPwd = @strPwd

BEGIN

SET @blnReturn = 1

-- 更新用户最后登录时间

Update uUser SET uLastLogin=GETDATE() Where uLoginName=@strLoginName

END

ELSE

SET @blnReturn = 0

END

用户登录的存储过程建立好了。注意，在一个区域内如果有多条语句时，必需使用BEGIN...END关键字。

引用:

Dim adoComm

’// 创建一个对象，我们用来调用存储过程

Set adoComm = CreateObject("ADODB.Command")

With adoComm

’// 设置连接，设 adoConn 为已经连接的 ADODB.Connection 对象

.ActiveConnection = adoConn

’// 类型为存储过程，adCmdStoredProc = 4

.CommandType = 4

’// 存储过程名称

.CommandText = "upUserLogin"

’// 设置登录名称

.Parameters.Item("@strLoginName").Value = "***"

’// 设置登录密码

.Parameters.Item("@strLoginPwd").Value = "123456"

’// 执行存储过程

.Execute

’// 判断是否登录成功

If .Parameters.Item("@blnReturn").Value = 1 Then

Response.Write "恭喜你，登录成功！"

Else

Response.Write "不是吧，好像错了哦。。。"

End If

End With

’// 释放对象

Set adoComm = Nothing

通过以上的步骤，简单用户登录验证过程也做完了，现在只要把它整合到程序中就可以实现简单的用户登录验证了，关于其他细节就由你自己来处理了。

上面介绍的两个存储过程都是只返回一个值的，下面我们来看一个返回一个记录集的存储过程。

引用:

Create PROC upGetUserInfos

@intUserGroup INT

BEGIN

-- 从数据库中抽取符合条件的数据

Select uName,uGroup,uLastLogin FROM uUser Where uGroup=@intUserGroup

-- 插入一列合计

UNION

Select ’合计人数:’,COUNT(uGroup),NULL FROM uUser Where uGroup=@intUserGroup

END

现在我们来看一下ASP程序的调用。

引用:

Dim adoComm

Dim adoRt

’// 创建一个对象，我们用来调用存储过程

Set adoComm = CreateObject("ADODB.Command")

Set adoRs = CreateObject("ADODB.Recordset")

With adoComm

’// 设置连接，设 adoConn 为已经连接的 ADODB.Connection 对象

.ActiveConnection = adoConn

’// 类型为存储过程，adCmdStoredProc = 4

.CommandType = 4

’// 存储过程名称

.CommandText = "upGetUserInfos"

’// 设置用户组

.Parameters.Item("@intUserGroup").Value = 1

’// 执行存储过程，和以上几个例子不同，这里使用RecordSet的Open方法

adoRs.Open adoComm

’// 显示第一个值

Response.write adoRs.Fields(0).Value

End With

’// 释放对象

Set adoRs = Nothing

Set adoComm = Nothing

显示全文

全部频道

谈谈HINT /*+parallel(t,4)*/在SQL调优中的重要作用

谈谈HINT　/+parallel(t,4)/在SQL调优中的重要作用