共计 10720 个字符，预计需要花费 27 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 Oracle 中 SQL 有哪些，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
100、————— 修改表结构，添加字段 ——————
alter table stu add(addr varchar2(29));
101、————— 删除字段 ————————–
alter table stu drop (addr);
102、————— 修改表字段的长度 ——————
alter table stu modify (addr varchar2(50));– 更改后的长度必须要能容纳原先的数据
103、—————- 删除约束条件 —————-
alter table stu drop constraint 约束名
104、———– 修改表结构添加约束条件 —————
alter table stu add constraint stu_class_fk foreign key (class) references class (id);
105、————— 数据字典表 —————-
desc dictionary;
– 数据字典表共有两个字段 table_name comments
–table_name 主要存放数据字典表的名字
–comments 主要是对这张数据字典表的描述
105、————— 查看当前用户下面所有的表、视图、约束 —– 数据字典表 user_tables—
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user-constraints;
106、————- 索引 ——————
create index idx_stu_email on stu (email);– 在 stu 这张表的 email 字段上建立一个索引：idx_stu_email
107、———- 删除索引 ——————
drop index index_stu_email;
108、——— 查看所有的索引 —————-
select index_name from user_indexes;
109、——— 创建视图 ——————-
create view v$stu as selesct id,name,age from stu;
视图的作用: 简化查询 保护我们的一些私有数据，通过视图也可以用来更新数据，但是我们一般不这么用 缺点：要对视图进行维护
110、———– 创建序列 ————
create sequence seq;– 创建序列
select seq.nextval from dual;– 查看 seq 序列的下一个值
drop sequence seq;– 删除序列
111、———— 数据库的三范式 ————–
（1）、要有主键，列不可分
（2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段
（3）、不能存在传递依赖
==============================================PL/SQL==========================
112、——————- 在客户端输出 helloworld——————————-
set serveroutput on;– 默认是 off，设成 on 是让 Oracle 可以在客户端输出数据
113、begin
dbms_output.put_line(helloworld
end;
/
114、—————-pl/sql 变量的赋值与输出 —-
declare
v_name varchar2(20);– 声明变量 v_name 变量的声明以 v_开头
begin
v_name := myname
dbms_output.put_line(v_name);
end;
/
115、———–pl/sql 对于异常的处理 (除数为 0)————-
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line(error
end;
/
116、———- 变量的声明 ———-
binary_integer: 整数，主要用来计数而不是用来表示字段类型 比 number 效率高
number: 数字类型
char: 定长字符串
varchar2：变长字符串
date：日期
long：字符串，最长 2GB
boolean：布尔类型，可以取值 true，false，null– 最好给一初值
117、———- 变量的声明，使用 %type 属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line(Test
end;
/
– 使用 %type 属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且 %type 属性，可以用于变量身上
118、—————Table 变量类型 (table 表示的是一个数组)——————-
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table type_table_empno;
begin
v_empnos(0) := 7345;
v_empnos(-1) :=9999;
dbms_output.put_line(v_empnos(-1));
end;
119、—————–Record 变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
begin
v_temp.deptno:=50;
v_temp.dname:= aaaa
v_temp.loc:= bj
dbms_output.put_line(v temp.deptno || || v temp.dname);
end;
120、———– 使用 %rowtype 声明 record 变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:= aaaa
v_temp.loc:= bj
dbms_output.put_line(v temp.deptno || || v temp.dname)
end;
121、————–sql%count 统计上一条 sql 语句更新的记录条数
122、————–sql 语句的运用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || || v_sal);
end;
123、 ——– pl/sql 语句的应用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output_line(v_emp.ename);
end;
124、————-pl/sql 语句的应用
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := aaa
v_loc dept.loc%type := bj
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;
125、—————–ddl 语言，数据定义语言
begin
execute immediate create table T (nnn varchar(30) default a )
end;
126、——————if else 的运用
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal 2000) then
dbms_output.put_line(low
elsif(v_sal 2000) then
dbms_output.put_line(middle
else
dbms_output.put_line(height
end if;
end;
127、——————- 循环 =====do while
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i =11);
end loop;
end;
128、———————while
declare
j binary_integer := 1;
begin
while j 11 loop
dbms_output.put_line(j);
j:=j+1;
end loop;
end;
129、———————for
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
130、———————– 异常 (1)
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 10;
exception
when too_many_rows then
dbms_output.put_line(太多记录了
when others then
dbms_output.put_line(error
end;
131、———————– 异常 (2)
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line(太多记录了
end;
132、———————- 创建序列
create sequence seq_errorlog_id start with 1 increment by 1;
133、———————– 错误处理 (用表记录：将系统日志存到数据库便于以后查看)
— 创建日志表:
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
commit;
end;
133———————PL/SQL 中的重点 cursor(游标) 和指针的概念差不多
declare
cursor c is
select * from emp; – 此处的语句不会立刻执行，而是当下面的 open c 的时候，才会真正执行
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename); – 这样会只输出一条数据 134 将使用循环的方法输出每一条记录
close c;
end;
134———————- 使用 do while 循环遍历游标中的每一个数据
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
(1) exit when (c%notfound); –notfound 是 oracle 中的关键字，作用是判断是否还有下一条数据
(2) dbms_output.put_line(v_emp.ename); –(1)(2) 的顺序不能颠倒，最后一条数据，不会出错，会把最后一条数据，再次的打印一遍
end loop;
close c;
end;
135————————while 循环，遍历游标
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
136————————–for 循环，遍历游标
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
137————————— 带参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno=v_deptno and job=v_job;
–v_temp c%rowtype; 此处不用声明变量类型
begin
for v_temp in c(30, click) loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
138—————————– 可更新的游标
declare
cursor c – 有点小错误
is
select * from emp2 for update;
-v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal 2000) then
update emp2 set sal = sal * 2 where current of c;
else if (v_temp.sal =5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
139———————————–procedure 存储过程 (带有名字的程序块)
create or replace procedure p
is– 这两句除了替代 declare，下面的语句全部都一样
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp2 set sal = sal +10 where current of c;
else if(v_emp.deptno =20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
– 执行存储过程的两种方法：
（1）exec p;(p 是存储过程的名称)
（2）
begin
p;
end;
/
140——————————- 带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
141———————- 调用 140
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
142—————— 删除存储过程
drop procedure p;
143———————— 创建函数计算个人所得税
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal 2000) then
return 0.10;
elsif(v_sal 2750) then
return 0.15;
else
return 0.20;
end if;
end;
—-144————————- 创建触发器（trigger） 触发器不能单独的存在，必须依附在某一张表上
– 创建触发器的依附表
create table emp2_log
(
ename varchar2(30) ,
eaction varchar2(20),
etime date
);
create or replace trigger trig
after insert or delete or update on emp2 —for each row 加上此句，每更新一行，触发一次，不加入则值触发一次
begin
if inserting then
insert into emp2_log values(USER, insert , sysdate);
elsif updating then
insert into emp2_log values(USER, update , sysdate);
elsif deleting then
insert into emp2_log values(USER, delete , sysdate);
end if;
end;
145——————————- 通过触发器更新数据
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =:NEW.deptno where deptno =: OLD.deptno;
end;
—— 只编译不显示的解决办法 set serveroutput on;
145——————————- 通过创建存储过程完成递归
create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) :=
begin
for i in 0..v_leave loop
v_preStr := v_preStr || ****
end loop;
for v_article in c loop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf = 0) then
p(v_article.id);
end if;
end loop;
end;
146——————————- 查看当前用户下有哪些表 —
– 首先，用这个用户登录然后使用语句：
select * from tab;
147—————————– 用 Oracle 进行分页！————–
– 因为 Oracle 中的隐含字段 rownum 不支持 所以：
select * from (
select rownum rn, t.* from (
select * from t_user where user_id root
) t where rownum 6
) where rn 3
148————————Oracle 下面的清屏命令 —————-
clear screen; 或者 cle scr;
149———– 将创建好的 guohailong 的这个用户的密码改为 abc————–
alter user guohailong identified by abc
– 当密码使用的是数字的时候可能会不行
– 使用在 10 Oracle 以上的正则表达式在 dual 表查询
with test1 as(
select ao name from dual union all
select yang from dual union all
select feng from dual )
select distinct regexp_replace(name, [0-9] , ) from test1
——————————————
with tab as (
select hong name from dual union all
select qi name from dual union all
select gong name from dual)
select translate(name, \\0123456789 , \\) from tab;
CREATE OR REPLACE PROCEDURE
calc(i_birth VARCHAR2) IS
s VARCHAR2(8);
o VARCHAR2(8);
PROCEDURE cc(num VARCHAR2, s OUT VARCHAR2) IS
BEGIN
FOR i
IN REVERSE 2 .. length(num) LOOP
s := s || substr(substr(num, i, 1) + substr(num, i – 1, 1), -1);
END LOOP;
SELECT REVERSE(s) INTO s FROM dual;
END;
BEGIN o := i_birth;
LOOP
cc(o, s);
o := s;
dbms_output.put_line(s);
EXIT WHEN length(o)
END LOOP;
END;
set serveroutput on;
exec calc(19880323
关于“Oracle 中 SQL 有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
