我的oracle\sqlserver笔记
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;如果发现用户名是小写，则在使用到这个用户名时要加双引号，否则会自动变为大写。