Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

PL/SQLÀý×Ó2

create or replace procedure c
(
v_deptno  in emp.deptno%type,
v_max out emp.sal%type
)
as
begin
select max(sal+nvl(comm,0)) into v_max from emp where deptno=v_deptno;
end;
create or replace procedure cc
(
v_empno  in emp.empno%type,
v_sal out emp.sal%type,
v_comm out emp.comm%type,
v_tol out emp.sal%type
)
as
begin
select sal,nvl(comm,0),(sal+nvl(comm,0)) into v_sal,v_comm,v_tol from emp where
empno=v_empno;
end;
create or replace procedure upsal
(
v_deptno in number,
v_sal in number
)
as
begin
update emp set sal=v_sal where deptno=v_deptno;
end;
create or replace procedure somsal
(
v_empno in number,
v_sal in number
)
as
begin
update emp set sal=v_sal where empno=v_empno;
end;
create or replace function maxsal
(
v_deptno in number
)
return number
is
v_maxsal number;
begin
select max(sal) into v_maxsal from emp where deptno=v_deptno;
return v_maxsal;
end;
create or replace procedure cursor
(v_cursor out mypack.mycursor
)
is
begin
open v_cursor for select * from emp ;
end ;
create or replace function selalldep
(a out number,
c in number
)
return number
as
b number;
begin
select count(empno),sum(sal+nvl(comm,0)) into a,b from emp
where deptno=c;
return b;
end;
create or replace package mypack is
type mycursor is ref cursor;
end ;
declare
i number(3):=1;
j number(3):=2;
begin
loop
insert into t_user values(i,'a'||i,'a'||i,1,to_date('2007-01-01','yyyy-mm-dd'),1,i,'sdd');
insert into t_user values(j,'a'||j,'a'||j,1,to_date('2007-01-01','yyyy-mm-dd'),2,j,'sdd');
i:=i+2;
j:=j+2;
exit when i>600 and j>600;
end loop;
end;


Ïà¹ØÎĵµ£º

SQL Server Êý¾Ý¿â

a.Êý¾Ý¿â»ù´¡
      i.¸ÅÄ±í¼°Ïà¹Ø²Ù×÷¶ÔÏóµÄ¼¯ºÏ£¨Ö÷Êý¾ÝÎļþmdf£¬¶þ¼¶Êý¾ÝÎļþndf£¬ÈÕÖ¾ldf£©
      ii.×é³É£ºÏµÍ³£º master(¼Ç¼ÁËËùÓеķþÎñÆ÷ϵͳÐÅÏ¢)
,model£¨¼Ç¼ÁËËùÓÐÁÙʱ¼Ç¼£¬Á´½Ó¶Ï¿ªÊ±£¬Êý¾Ý¶ªÊ§£©
Tempdb£¨Êý¾ÝÄ£°å¿â£¬º¬´´½¨¿âʱµÄȱʡÊý¾Ý£©
, ......

PL/SQLÖÐRef CursorµÄÓ¦ÓÃ

PL/SQL×÷Ϊoracle¶ÔÓÚ´«Í³SQLÓï·¨µÄÀ©Õ¹,ÔÚ¿çϵͳÊÂÎñ´¦ÀíÒÔ¼°¶ÔÊý¾Ý¿â´óÅúÁ¿Êý¾Ý´¦Àí·½Ãæ×÷ÓÃÏÔÖø;¶øµ±Ç°Ö÷Á÷µÄMVC¿ò¼Ü¶ÔDAO²ãµÄ·â×°¶¼ÊÇÒ»ÌõSQLÓï¾ä·µ»ØÒ»¸ö¼Ç¼¼¯;µ±Êý¾ÝµÄ¹ØÏµ±È½Ï¸´ÔÓ»òÊý¾ÝÁ¿±È½Ï´óµÄʱºò,ÓëÊý¾Ý¿âµÄ½»»¥¹ýÓÚÆµ·±½«Ôö´óÄÚÍøµÄÊý¾Ý´«ÊäÁ¿ÓëÊý¾Ý¿âµÄIO.Õë¶Ô´ËÀิÔÓµÄÊý¾Ý´¦Àí,¿ÉÒÔ²ÉÓÃPL/SQL³ÌÐò°üÀ´Êµ ......

SQL³£ÓÃÈÕÆÚʱ¼ä´¦Àíº¯Êý

×î½üÔÚÔÚÒ»µçÁ¦ÏµÍ³£¬ÀïÃæÓõ½±¨±í£¬¾­³£ÐèÒª¶ÔSQLÈÕÆÚ½øÐвÙ×÷¡£ÏÖÔÚ½«Ò»Ð©³£ÓõÄSQLÈÕÆÚ²Ù×÷º¯Êý¼ÇÏÂ
/**//**//**//* datepart()º¯ÊýµÄʹÓà                     ¡¡¡¡
* datepart()º¯Êý¿ÉÒÔ·½±ãµÄÈ¡µ ......

SQLÀïµÄEXISTSÓëin¡¢not existsÓënot in

SQLÀïµÄEXISTSÓëin¡¢not existsÓënot in
ϵͳҪÇó½øÐÐSQLÓÅ»¯£¬¶ÔЧÂʱȽϵ͵ÄSQL½øÐÐÓÅ»¯£¬Ê¹ÆäÔËÐÐЧÂʸü¸ß£¬ÆäÖÐÒªÇó¶ÔSQLÖеIJ¿·Öin/not inÐÞ¸ÄΪexists/not exists
Ð޸ķ½·¨ÈçÏ£º
inµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE ......

SQL¿ç·þÎñÆ÷²éѯ

--ÔÚ²éѯ·ÖÎöÆ÷ÖÐ,ÔÚServer·þÎñÆ÷Öд´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','·þÎñÆ÷Ãû'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'Óû§Ãû','ÃÜÂë'
Go
--ʹÓÃ
select * from srv_lnk.Êý¾Ý¿âÃû.dbo.±íÃû
--¶Ï¿ª
exec sp_dropserver 'srv_lnk','droplogins' ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ