Oracle_SQL语句

精选转载

zzpvedem 2014-11-03 20:14:11 博主文章分类：数据库

------------------------Oracle 的SQL 语法--------------------------

=======================第（1,2）章结合语句==========================

》1：创建表空间

create tablespace 表空间名
datafile'存放文件的路径_空间名_.dbf' size 30m;

》2: 分配权限

grant 权限| 角色 to 用户名

》3：创建用户

create user 登陆名 identified by 密码
grant connect , resource to 登陆名

》4：创建唯一索引

create unique index index_列_empon on 表名 (列);

》5：创建序列

create sequence 自定义表名 start with 1 increment by 1;

》6: 查看当前序列的值

select seql.currval from 表名

》7：创建私有同义词

create synonym e(自己定义的一个) for 表名;

》8：创建共有同义词

create public synonym e for 表名;

》9：给某个用户访问的权限，可以访问该同义词

grant select on e to 用户名;

》10：创建大写函数索引

Create index index_ename on 表名 (upper(列))；

》间隔分区

create table 表名

partition by range (列)
interval (numtoyminterval(3,'month'))
(partition p1 values less than (to_date ('2013-04-01','yyyy-mm-dd')))
as
select * from 表名

=====================第（3）章的 SQL 语句=======================================

PL/SQL 块可执行部分其中变量语法如下：

》1：声明变量

declare
v_name varchar2(20)；

》2：声明和赋值
declare
v_Names varchar2(20):='战三'；

》3：循环控制

---------------(1)Loop循环语法：

Loop
EXIT Where <条件语句> ---条件满足，退出
END Loop;

-------------（2）WHILE 循环语法：

WHILK <布尔表达式> Loop
要执行的语法
END Loop；

》4：输出语句
dbms_ output.put_line ('您要输出的内容 , ZZZ.....想睡觉了.....');

》5: 游标声明,打开,提取,关闭 (例子)

declare

cursor em_cursor is

select 列 from 表名 where (条件);

--声明两个变量
v_name employee name%type;
v_sal employee%type;

begin
open em_cursor ;--打开游标

loop
fetch em_cursor into v_name,v_sal --提取游标（提取行）

exit when em_cursor%notfound; --判断游标是否为空,就退出

dbms_output.put_line(v_name||v_sal); --输出两个变量

endloop; --循环提取

close em_cursor; --关闭游标

end;

》6：循环游标取数据

for i in 游标名 loop

dbms_output.put_line('用户信息：'||i.userid||'-------'||i.username);（直接 i 不可取 i为对象）

end loop;

=========================第（4）章的 SQL 语句=======================================

》1：通过游标修改数据

declare
cursor userinfo is select * from t_user for update;
v_name varchar2(50);
begin
for i in userinfo loop
v_name :=i.username;
update t_user set username='王五' where current of userinfo;
dbms_output.put_line('用户信息：'||i.userid||'-------'||i.username);
end loop;
commit;
end;

》2：创建存储过程
create or replace procedure usertest
is
cursor userinfo is select * from t_user;
begin
for i in userinfo loop
dbms_output.put_line('用户信息：'||i.userid||'-------'||i.username);
end loop;
end;

》3：调用存储过程
exec usertest;
call usertest();
begin
usertest;
end;

》4：存储过程修改数据
create or replace procedure usertest(v_name varchar2)
is

begin
update t_user set username=v_name where userid=1;
if v_name is null then raise
commit;
exception
when others then rollback;
end;

exec usertest('zhangsan');

》5：过程中的参数
--在过程中，in修饰的参数，不允许在过程中给它赋值

create or replace procedure usertest(
v_name in varchar2,
v_pwd out varchar2,
v_show in out varchar2
)is
v_hello varchar2(50);
begin
-- v_name := 'name';
v_pwd := 'pwd';
v_show := 'show';
v_hello := 'hello';
dbms_output.put_line('信息：'||v_name||'-------'||v_pwd||'-------'||v_show||'-------'||v_hello);
end;

declare
vp varchar2(50);
vs varchar2(50);
begin
vs := 'cccccc';
usertest('aaaaaaa',vp,vs);
end;

》5：存储过程的语法
create or replace procedure 过程的名字
(
参数1 in/out 没有长度的类型，
参数2 in/out 没有长度的类型
) is
过程中需要用的参数名可以指定长度的类型；
begin
过程的内容
end;

》6：变量的类型的另外一种写法
--in传递的参数，长度由外部控制
--out传递的参数，长度由内部控制
create or replace procedure usertest(
v_name in t_user.username%type,
v_num in t_user.userid%type

)is
v_hello t_user.username%type;
begin
v_hello := 'hello';
dbms_output.put_line('信息：'||v_name||'-------'||v_num||'-------'||v_hello);
end;

declare
vp varchar2(50);
vs number;
begin
vs :=11111111111111111111;
usertest('aaaaaaa',vs);
end;

》7：定义外部变量
var vnum number;
var vid number;
var vname varchar2(50);
exec :vnum:=23;
exec :vid:=4;
exec :vname:='wangwu';
exec userinfo(:vid,:vname,:vnum);

create or replace procedure userinfo(
vid t_user.userid%type,
vname t_user.username%type,
vnum out t_user.userid%type
)is
begin
vnum :=vid;
dbms_output.put_line('信息:'||vid||'-------'||vname||'--------'||vnum);
end;

》 8：创建函数
create or replace function funuser
return varchar2 is
v_id number(8);
uname t_user.username%type;
begin
v_id := 3;
select username into uname from t_user where userid=v_id;
return uname;
end;

declare
vname varchar2(50);
begin
vname :=funuser;
dbms_output.put_line('vname:'||vname);
end;

call funuser() into :vname;
print vname;

》 9：创建包的规范
create or replace package user_pkg
is
procedure userselect(
v_id t_user.userid%type,
v_name t_user.username%type
);
function getname return varchar2;

end;

》10：创建包的内容
create or replace package body user_pkg
is
procedure userselect(v_id t_user.userid%type,v_name t_user.username%type)
is
vn varchar2(50);
begin
update t_user set username=v_name where userid=v_id;
commit;
dbms_output.put_line('v_name:'||v_name);
end;
function getname return varchar2
is
vname varchar2(50);
begin
select username into vname from t_user where userid=3;
return vname;
end;
end;

----exec 为调用存储过程
exec user_pkg.userselect(3,'lisi');

call user_pkg.getname() into :vname;
print vname;

※ 11：创建视图
create or replace view empdept
as
select e.empno,e.ename,e.job,e.mgr,e.sal,e.hiredate,e.comm,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno
with read only （这为把值设为只读）
;

update empdept set ename='world' where empno=7369;

create or replace view deptview
as
select * from emp where deptno=20
with check option
;

update deptview set ename='world' where empno=7369;
update emp set deptno=10 where empno=7369;

》 12 ：创建数据库链

create database link mylink
connect to tt identified by tt
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jiahua)
)
)';

select * from company@mylink;

》13: 创建触发器

--当dept表（deptno=10)的loc的deptno改为BJ的时候，修改相关的emp表的ename=BJ
create or replace trigger updateemp
after update of loc on dept for each row
begin
update emp set ename='BJ' where deptno=10;
end;