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;
Ïà¹ØÎĵµ£º
a.Êý¾Ý¿â»ù´¡
i.¸ÅÄ±í¼°Ïà¹Ø²Ù×÷¶ÔÏóµÄ¼¯ºÏ£¨Ö÷Êý¾ÝÎļþmdf£¬¶þ¼¶Êý¾ÝÎļþndf£¬ÈÕÖ¾ldf£©
ii.×é³É£ºÏµÍ³£º master(¼Ç¼ÁËËùÓеķþÎñÆ÷ϵͳÐÅÏ¢)
,model£¨¼Ç¼ÁËËùÓÐÁÙʱ¼Ç¼£¬Á´½Ó¶Ï¿ªÊ±£¬Êý¾Ý¶ªÊ§£©
Tempdb£¨Êý¾ÝÄ£°å¿â£¬º¬´´½¨¿âʱµÄȱʡÊý¾Ý£©
, ......
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà ......
oracle³£ÓþµäSQL²éѯ
³£ÓÃSQL²éѯ£º
1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ ......
×î½üÔÚÔÚÒ»µçÁ¦ÏµÍ³£¬ÀïÃæÓõ½±¨±í£¬¾³£ÐèÒª¶ÔSQLÈÕÆÚ½øÐвÙ×÷¡£ÏÖÔÚ½«Ò»Ð©³£ÓõÄSQLÈÕÆÚ²Ù×÷º¯Êý¼ÇÏÂ
/**//**//**//* datepart()º¯ÊýµÄʹÓà ¡¡¡¡
* datepart()º¯Êý¿ÉÒÔ·½±ãµÄÈ¡µ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE da ......