我的oracle\sqlserver笔记

要使用dbms_output.putline()输出就需要设置serveroutput参数为on
set serveroutput on;  


函数  （必须有返回值）
CREATE OR REPLACE FUNCTION fun_getSal(eno NUMBER)
RETURN NUMBER   --返回类型
AS
v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = eno;
  RETURN v_sal;
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20012,'该雇员不存在');
END fun_getSal;






SQL> create or replace function f_na_job(name String)//用varchar2编译报错
  2  return String
  3  as
  4  j emp.job%type;
  5  begin
  6    select job into j from emp where ename=name;
  7    return j;
  8  end f_na_job;
  9  /


函数已创建。


SQL> declare
  2  a emp.job%TYPE;
  3  begin
  4    a:=f_na_job('SMITH');
  5  end;
  6  /


PL/SQL 过程已成功完成。






declare 
j varchar2(32);  //用String 又显示范围的错
begin
    j:=f_getjob('SMITH');
    dbms_output.put_line('SMITH job :'||j);
end;
/
字符串尽量用单引号，不然可能导致编译出错。
查询已创建存储过程
select  name from user_source where  type='PROCEDURE';
查询已创建函数
select object_name from user_objects 
where object_type='FUNCTION';


函数语句
select text from user_source where name='函数';


调用函数


DECLARE 
  v_sal NUMBER;
  emp_20012 EXCEPTION;
  PRAGMA EXCEPTION_INIT(emp_20012,-20012);
BEGIN
  v_sal:=fun_getsal(7788);
  dbms_output.put_line('sal:'||v_sal);
  EXCEPTION
    WHEN emp_20012 THEN
      dbms_output.put_line('该雇员不存在');
END;




declare 
   v_sal number;
   e   exception;
   pragma exception_init(e,-20012);
begin
   v_sal:=fun_sa('SMITH');
   dbms_output.put_line('sal:'||v_sal);
end;
/




SQL> create or replace function upd(
  2  id number,
  3  name String,
  4  job String)
  5  return number
  6  as
  7  s emp.sal%type;
  8  begin
  9  insert into emp(empno,ename,job) values (id,name,job);
 10  select sal into s from emp where empno = id;
 11  return s;
 12  end upd;
 13  /


函数已创建。




SQL> declare
  2  sa emp.sal%type;
  3  begin
  4  sa:= upd(7777,'jack','sdhjk');
  5  end;
  6  /


PL/SQL 过程已成功完成。




存储过程


SQL> create or replace procedure upin(
  2  id in emp.empno%type,
  3  name out emp.ename%type,
  4  job out emp.job%type,
  5  m in number,
  6  s out emp.sal%type)
  7  as                          //as 不可少
  8  begin
  9  update emp set sal = sal + m where empno=id;
 10  select ename,job,sal into name,job,s from emp where empno = id;
 11  end upin;
 12  /










过程已创建。


SQL>
SQL> variable name varchar2(10);
SQL> variable job varchar2(10);
SQL> variable s number;
SQL> exec upin(7369,:name,:job,50,:s);








select a.empno,a.ename,a.job from (select empno,ename,job,sal from emp where ename = 'SMITH') a;
括号内将查询到的信息存到a中


group by
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
select deptno,sum(sal) from emp 
where deptno!='10' group by deptno
having sum(sal)>8000;




SQL> select ename 名字,job 工作,sal 工资 from emp where empno in (select empno from emp where sal>2000);


名字       工作            工资
---------- --------- ----------
JONES      MANAGER         2975
BLAKE      MANAGER         2450
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000


 
游标


隐式游标sql




SQL> begin
  2  update emp set sal=sal+10;
  3  if sql%found then
  4  dbms_output.put_line('这次一共更新了'||sql%rowcount);
  5  else
  6  dbms_output.put_line('一行也没有更新');
  7  end if ;
  8  end;
  9  /
这次一共更新了15


PL/SQL 过程已成功完成。


在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS




SQL> declare
  2  name emp.ename%type;
  3  begin
  4  select ename into name from emp;
  5  if sql%found then
  6  dbms_output.put_line('查询到'||sql%rowcount);
  7  else
  8  dbms_output.put_line('未查询到数据');
  9  end if;
 10  exception
 11  when too_many_rows then
 12   dbms_output.put_line('多于一行');
 13  when no_data_found then
 14   dbms_output.put_line('未找到匹配行');
 15  end;
 16  /
多于一行


PL/SQL 过程已成功完成。


显式游标 
声明游标、打开游标、使用游标读取记录、关闭游标


SQL> declare
  2  nm emp.ename%type;
  3  cursor cursor_name is select ename from emp;
  4  begin
  5  open cursor_name;
  6  fetch cursor_name into nm;
  7  while cursor_name%found
  8  loop
  9  dbms_output.put_line('name:'||nm);
 10  fetch cursor_name into nm;
 11  end loop;
 12  close cursor_name;
 13  end;
 14  /
name:SMITH
name:ALLEN
name:WARD
name:JONES






declare
  no emp.empno%type;
  nm emp.ename%type;
  job emp.job%type;
  cursor emp_cur is select ename,empno,job from emp ;
begin
  open emp_cur;
fetch emp_cur into nm,no,job;
if emp_cur%notfound then
 dbms_output.put_line('输出失败');
end if;


while emp_cur%found
loop
 dbms_output.put_line('编号是:'||no||'名字是:'||nm);
if nm='SMITH' then
dbms_output.put_line('this is smith');
end if;
fetch emp_cur into nm,no,job;
end loop;
end;
/




 declare
   no emp.empno%type;
   nm emp.ename%type;
   job emp.job%type;
 cursor emp_cur is select ename,empno,job from emp order by ename;
 begin
 open emp_cur;
 fetch emp_cur into nm,no,job;
 while emp_cur%found
 loop
 dbms_output.put_line('xingming');
  dbms_output.put_line('xingming:'||nm);
 fetch emp_cur into nm,no,job;
 end loop;
 close emp_cur;
 end;
 /
-----------------------------------------------------------------------------------------------------
SQLSERVER
生成代码1
ALTER PROCEDURE [dbo].[dd]
  @RCode AS varchar(50) 
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'

set ANSI_WARNINGS  OFF
--定义变量
DECLARE
 --start--
@d VARCHAR(50),
@code VARCHAR(50);

 --end--

 --赋值--


--赋值end--

 --定义游标--
declare cur_s cursor fast_forward for 
select ID,SUBSTRING(RegionCode, 1, 4) as RegionCode from
Sys_EnterInfoNewAndOldCode_copy1 
where NewEntCode='' or NewEntCode is null and RegionCode = @RCode
--定义游标end--

open cur_s; --打开游标

FETCH NEXT FROM cur_s into @d,@code;

while @@fetch_status = 0
--循环
BEGIN
DECLARE
@newCode VARCHAR(50), --新编码
@randomChar VARCHAR(10);--随机字符
 
set @randomChar = char(65+CEILING(rand()*25))
select @newCode = @code  + @randomChar
update Sys_EnterInfoNewAndOldCode_copy1
set NewEntCode = @newCode
where id = @d
 
FETCH NEXT FROM
  cur_s
into @d,@code;
end
CLOSE cur_s;
 
DEALLOCATE cur_s;
 
set ANSI_WARNINGS ON
END


生成代码2
create PROCEDURE TEST
   @RCode varchar(50)
AS
Begin

--定义变量--
--start--
DECLARE
  @lie VARCHAR(50),
@code varchar(50);
 
--end--
 
--设置变量--
 
 
--end--
 
--定义游标--
declare cur_sys CURSOR fast_forward for
select ID,SUBSTRING(RegionCode, 1, 5) 
from Sys_EnterInfoNewAndOldCode_copy1
where NewEntCode ='' or NewEntCode is null and RegionCode = @RCode;

--定义游标结束--
 
--打开游标
open cur_sys;
--将游标指向第一条数据--
FETCH FROM cur_sys into @lie,@code;
--循环开始--
while @@fetch_status=0
   begin
 DECLARE
 @newCode varchar(50),
@randomChar varchar(50);
----------------------------
--设置变量--
select @randomChar = Char(65+rand()*25),
@newCode = @code +  @randomChar;

update Sys_EnterInfoNewAndOldCode_copy1 
set NewEntCode = @newCode where
id = @lie;

FETCH NEXT FROM cur_sys into @lie,@code;


end
CLOSE cur_sys
DEALLOCATE cur_sys
 






END


生成编码3
ALTER PROCEDURE [dbo].[test2]
  @Rcode varchar(50)
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
--定义变量--
--start--
declare
  @d varchar(50), --存储当前的ID
@code1 varchar(50), --存储截取的RegionCode字段
@code2 varchar(50), --存储截取的OrganizationCode的字段
@randomInt int ,--存储变化的数字字段
@randomChar varchar(2),--存储随机的字符
@newCode varchar(50); --存取新编码
--end--
--设置变量值--
set 
  @randomInt = 100000;
 
--定义游标--

  declare cur_entCode CURSOR fast_forward for
select ID , SUBSTRING(RegionCode,1 ,4) as RegionCode ,SUBSTRING(OrganizationCode, 1,4)
as OrganizationCode from Sys_EnterInfoNewAndOldCode_copy1 
where NewEntCode = '' or NewEntCode is null and RegionCode = @RCode;
--定义游标结束--
--打开游标--
open cur_entCode;


--从游标读取第一条记录--
fetch next from cur_entCode into @d,@code1,@code2;
--循环开始--
while @@fetch_status=0
  --循环体--
begin
  select @randomChar = Char(65+rand()*25),
@newCode = @code1+@code2+SUBSTRING(CONVERT(VARCHAR,@randomInt),1,6)+@randomChar;
 
update Sys_EnterInfoNewAndOldCode_copy1 
set NewEntCode = @newCode 
where id = @d;

  set @randomInt = @randomInt + 1;



fetch next from cur_entCode into @d,@code1,@code2;
end
close cur_entCode
DEALLOCATE cur_entCode


END


生成编码4
ALTER PROCEDURE [dbo].[test4]
  @ReceiveCode AS VARCHAR(50)
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
--定义变量--
--定义变量开始--

declare
    @ids varchar(50), --接收当前的行id
@code varchar(50), --接收新编码的前几个字符
@newC varchar(50),--接收新编码
 
@randChar varchar(50),  --一个随机变化的字符
@randInt  int,  --中间变化的数字字符
  @addInt int , --每次的增量
@maxNum int;
--定义变量结束--
--变量赋值--
 
set @addInt = 1 
set @maxNum = 10000
 
 --变量赋值结束--

--定义游标--
--定义游标开始--
declare cur_new_ cursor for 
select ID,SUBSTRING(RegionCode, 1, 6) as RegionCode 
from Sys_EnterInfoNewAndOldCode_copy1 
where NewEntCode='' or NewEntCode is null and RegionCode = @ReceiveCode
--定义游标结束--

--查询数据库中是否存在新编码--
select top 1 @randInt =convert(int,SUBSTRING(NewEntCode, 7, 5))
from Sys_EnterInfoNewAndOldCode_copy1 WHERE RegionCode = @ReceiveCode
ORDER BY NewEntCode desc
--end--
IF @randInt !=null BEGIN
set @maxNum = @maxNum + @randInt
END






--打开游标--
open cur_new_;
--end--

--读取第一条记录--
fetch next from cur_new_ into @ids,@code

--end--

--循环--
while @@fetch_status=0
 begin
  set @randChar = Char(65+rand()*25) --取随机字符--

 
 select  @newC = @code +SUBSTRING( convert(varchar,@maxNum ), 2, 5) + @randChar --组合新编码
 
update Sys_EnterInfoNewAndOldCode_copy1
set NewEntCode = @newC 
where ID = @ids 
 
set @maxNum = @maxNum+@addInt
 
fetch next from cur_new_ into @ids,@code
end
 
close cur_new_
DEALLOCATE cur_new_
 
 


END




触发器


禁用所有的外键约束
alter table score NOCHECK CONSTRAINT all
启用所有的外键约束
alter table score check constraint all






CREATE TRIGGER trustudent
on stu for update
AS
if update(S#)
begin
  update score
set S# = i.S#
from score sc,inserted i,deleted d
where sc.S# = d.S#
end
end




sqlserver 创建函数


ALTER  function funn(@id varchar(50))
returns real
as


begin
declare @nm varchar;
  select @nm =  Name from stu where S# = @id;


return @nm
end






where 1=2 是让记录集为空，只需要得到表的各属性的数据类型即可。






oracle导入dmp到数据库


创建用户
create user username identified by password;
grant create session,create table,create view,create sequence,unlimited tablespace to username;
grant dba to username;


可能需要dba权限
登入到system grant dba to usrename;
创建了用户无法登陆 ，可能没有给予登陆权限


从一个用户导入到另一个用户
imp sss/sss file = E:\chenzhou1107.dmp fromuser = CHENZHOU touser = sss  log=E:\12.log
导出
exp sss/sss owner=sss  file = D:\chenzhou1.dmp log=chenzhou1.log
oracle 导出问题


在11g版本中为了节省空间，oracle中的表如果没有插入过数据，就不会被分配到segment，在导出时，这些没有被分配到segment的表就不会被导出。
查看这些表的方法：pl/sql 报告->project->tables->回车 num_rows =0的那些表


解决方法：方法1.插入一行，再删除掉
          方法2，设置deferred_segment_creation 参数  默认是true，改成false后无论是空表还是非空表，都会分配到segment
                修改的语句是alter system set deferred_segment_creation=false scope=both; 
需注意的是：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。
如需导出之前的空表，只能用第一种方法。
把查询结果导出，执行导出的语句，强行修改segment值，然后再导出即可导出空表，
 方法3，select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
                把查询结果导出，执行导出的语句，强行修改segment值，然后再导出即可导出空表




创建数据库是另一个错误， select username from dba_users;如果发现用户名是小写，则在使用到这个用户名时要加双引号，否则会自动变为大写。