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(数据模板库,含创建库时的缺省数据)
, ......
SQL里的EXISTS与in、not exists与not in
系统要求进行SQL优化,对效率比较低的SQL进行优化,使其运行效率更高,其中要求对SQL中的部分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 ......
表名:d_ClientInfo
语句作用:取出第100-120条数据
SELECT *
from (SELECT ROW_NUMBER() OVER (ORDER BY ClientID ASC) AS ROWID, * from d_ClientInfo) AS tmpTable
WHERE ROWID BETWEEN 100 AND 120
此函数会为数据表重新编号并新建数据列ROWID,不需要的屏蔽掉就OK了。 ......
1. 消除trigger的嵌套调用。最好不要用 EXEC sp_configure 'nested triggers', '0', 应该在trigger中使用判断语句, 例如:if not update (name) return。
2. 使用 not for replication 禁止在复制的时候触发trigger。
3. 创建publisher article的时候, 设置 copy user triggers为 true。
这样保证:trigger不会嵌套调 ......