oracle
v$controlfile 控制文件
v$datafile 数据文件
desc V$DATAFILE 查看
v$logfile 日志文件
DATABASE目录下参数文件，参数文件用于查找控制文件
sqlplus/nolog
connect sys/ss as sysdba
startup
startup mount 启动控制文件，不启动数据文件
alter database archivelog
alter database open
startup nomount启动实例，不打开控制文件
archive log list查看归档模式
shutdown immediate 一般所选（执行当前语句后断开连接）
shutdown 正常关闭（少用）
shutdown transactional
shutdown abort 强行关闭，可能造成文件破坏
sql> help index 查看帮助
sql>? set 查看SET命令
sql>SET SQLBLANKLINES ON 空格行
sql>select * from dept where deptno=&tt; --&tt为一个变量，之后可能其付值
sql>list 查看缓冲区的命令
sql>l2 4 查看2到4行
sql>l --list 简写
sql>change/n/m 把N换成M C为简写
sql>？ change 查看怎么使用CHANGE命令
sql>del 删除缓冲区命令
sql>del 2 3删2，3行
sql>@c:\执行TXT文件脚本
sql>get c:\ 写到缓冲区，不执行
sql>edit --编辑文件
sql>COLUMN
sql>COL deptno HEADING “编号” --给字段给个名称，是临时的
sql>describe
sql>desc dept 查看表字段
sql>COL DNAME FORMAT A10 给定10个字符
sql>COL deptno FORMAT A10 10个字符
sql>COL bytes FORMAT 999,999.999 对BYTES的值格式化
sql>BREAK ON 字段名，重复的不显示
sql>COMP
基本循环；
常见异常：
DUP_VAL_ON_INDEX --向有唯一约束的表中插入重复行
NO_DATA_FOUND --在一个SELECT INTO语句中无返回值
TOO_MANY_ROWS --SELECT INTO语句返回了多行
VALUE_ERROR --一个算法、转换、截断或大小约束发生错误
ZERO_DIVIDE --发生被零除
sql>declare
2 x number
e begin
4 x:=0;
5 loop
6 x:=x+1;
7 if x>=3 then --exit when x>=3;
8 exit;
9 end if;
10 dbms_output.put_line('内:x='||x); --输出；
11 end loop;
sql> declare
2 x number
e begin
4 x:=0;
5 while x<=3 loop
6 x:=x+1
7 dbms_output.put_line('x:='||x);
8 end loop;
sql>begin
2 for i in 1..5 loop
3 dbms_output.put_line('i=||i);
4 end loop;
异常处理
sql>declare
2 test varchar2(10);
3 begin
4 select name into test from deptment where id='tt';
5 dbms_output.put_line(test);
6 exception
7 when no_data_fond then
8 dbms_output.put_line('没有数据');
9 end;
自定义异常
sql>declare
2 tname varchar2(10);
3 e exception; --自定义异常；
4 begin
5 select name into tname from deptment where id='01';
6 if tname<>'B部门' then
7 raise e;
8 end if;
9 dbms_output.put_line(tname);
10 exception
11 when e then
12 dbms_output.put_line('错误，不是B部门');
13 end;
14 /
定义RECORD
declare
type myrecord is record( --定义一个RECORD
id varchar2(10), --emp.eid%type定义为与表ID字段的类型一样
name varchar2(10));
real_record myrecord; --声明一个RECORD变量
begin
select emp_id,emp_name into real_recordfrom emp where emp_id='110';
dbms_output.put_line(real_record.id||','||real_record.name);
declare --定义为与表的结构相同
myrec emp%rowtype;
begin
select eid.iname into myrec from emp where eid ='001';
dbms_output.put_line(myrec.eid||','||myrec.ename||','||myrec.sex);
end;
/
游标的属性
%FOUND
%ISOPEN
%NOTFOUND
%REWCOUNT
DECLARE
cursor mycur is
select * from books;
myrecord books%rowtype; -与表结构相同
begin
open mycur;
FETCH mycur into myrecord;
WHILE mycur%found loop
dbms_output.put_line(myrecord.books_id||','||myrecord.books_name);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
游标参数
DECLARE
CURSOR cur_para(id varchar2) is --参数不用指明长度
SELECT books_name from books WHERE books_id=id;
t_name books.books_name%type;
begin
open cur_para('001');
loop
FETHC cur_para into t_name;
EXIT WHEN cur_para%notfound;
dbms_oupput.put_line(t_name);
END LOOP;
CLOSE cur_para;
END;
/
DECLARE
CURSOR cur_para(id varchar2) is
select books_name FROM books WHERE books_id=id;
begin
DBMS_OUTPUT.PUT_LINE('*****结果集是********');
for cur in cur_para('001') loop
DBMS_OUTPUT.PUT_LINE(cur.books_name);
END LOOP;
END;
/
DECLARE
t_name books.books_name%type;
CURSOR CUR(id varchar2) is
SELECT books_name FROM books WHERE books_id=id;
BEGIN
IF cur%isopen THEN
DBMS_OUTPUT.PUT_LINE('游标已打开');
ELSE
OPEN cur('003');
END IF;
FETCH cur INTO t_name;
CLOSE cur;
DBMS_OUTPUT.PUT_LINE(t_name);
/
DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT name FROM deptment;
BEGIN
OPEN mycur;
LOOP;
FETCH mycur into t_name;
EXIT WHEN mycur%NOTFOUND or mycur%nofound is null;
DBMS_OUTPUT.PUT_LINE('游标MYCUR的ROWCOUNT是'||mycur%ROWCOUNT);
END LOOP;
END;
利用游标修改数据
DECLARE
CURSOR cur IS
SELECT name FROM deptment FOR UPDATE; 加上FOR UPDATE用于修改数据
text varchar2(10);
BEGIN
OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDATE deptment SET name=name||'_t' WHERE CURRENT OF CUR;
FETCH cur INTO text;
END LOOP;
CLOSE cur;
END;
/
隐式游标 不用声明，不用OPEN，不用CLOSE；
BEGIN
FOR cur IN(SELECT name FROM deptment) LOOP
DBMS_OUTPUT.PUT_LINE(cur.name);
ENE LOOP;
END;
/
存储过程
CREATE OR REPLACE PROCEDURE procedurename
(param1 in|out paramtype)
is|as
...
BEGIN
...
END；
CREATE OR REPLACE PROCEDURE myproc(id IN varchar2) --参数不给长度
IS|AS
name varchar2(10); 变量给出长度
BEGIN
SELECT books_name INTO name FROM books WHERE books_id=id;
DBMS_OUTPUT.PUT_LINE(name);
END myproc;
/
执行过程
DECLARE
tid varchar2(10);
BEGIN
tid:='001';
myproc(tid);
END;
/
BEGIN --参数为常量时
myproc('0001');
END;
/
用EXECUTE执行的条件：1，过程不带参数；2，有参数，是常量输入参数时；
EXECUTE myproc('0001');
输出参数：存储过程返回的
CREATE OR REPLACE PROCEDURE myproc2(id varchar2,name OUT varchar2)
IS
BEGIN
SELECT books_name INTO name FROM books WHERE books_id=id;
END;
/
执行：
DECLARE
tid varchar2(10);
tname varchar2(10);
BEGIN
tid:='0001';
myproc2(tid,tname);
end;
视图
CREATE OR REPLACE VIEW myview
AS
SELECT * FROM books WHERE PRICE>30
WHTH CHECK OPTION --当插入PRICE的值小于30时不能写入（只对视图作用，不对基表作用）；
/
注：当组成视图的表2个或2个以上时，不能同时对两个或两个基表进行插入等 更新操作；
当视图有SUM等函数时也不能更新
创建只读视图
CREATE OR REPLACE UIEW v_read
AS
SELECT eid,iname FROM emp
WITH READ ONLY;
SELECT text FROM user_views WHERE view_name='V_READ'; --查找当前用户只读的图
SELECT user FROM dual; 当前用户
同意词
CREATE SYNONYM dept FOR scott.dept; 专有同意词
DROP SYNONYM dept;
connect sys/ss as sysdba
CREATE PUBLIC SYNONYM dept for scott.dept;公共同意词;
建序列；
CREATE SEQUENCE myseq
start with 1
INCREMENT BY 1
ORDER
NOCYCLE; --不重复
CREATE TABLE auto(a number,b varchar2(10));
INSERT INTO auto VALUES(myseq.NEXTVAL,'DFD');
DESC DBA_SEQUENCES 查看序列
触发器
CREATE OR REPLACE TRIGGER del_deptid
AFTER DELETE ON deptment
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE id=:old.id;
END del_deptid;
/
CREATE OR REPLACE TRIGGER insert_dept
AFTER INSERT ON DEPTEMT
FOR EACH ROW
BEGIN
INSERT INTO emp(eid.ename.id) VALUES('191','QWERT',:new.id);
CREATE OR REPLACE TRIGGER updta_dept
AFTER UPDATA ON DEPTMENT
FOR EACH ROW
BEGIN
UPDATE emp SET id=:new.id WHERE id=:old.id;
END;
/
CREATE OR REPLACE TRIGGER books_delete
AFTER DELETE ON books
FOR EACH ROW
BEGIN
IF :old.books_id='001' THEN
RAISE_APPLICATION_ERROR(-20000,'不能删除'); 错误号为-20999到-20000；
END IF ;
END;
/
CREATE TABLE mylog(curr_user varchar2(100),curr_date date,act char(1));
CREATE OR REPLACE TRIGGER dnl_aa
AFTER INSERT OR DELETE OR UPDATA ON AA
BEGIN
IF INSERTING THEN
INSERT INTO mylog values(user,sysdate,'I');
ELSIF DELETE THEN
INSERT INTO mylog VALUES(user,sysdate,'D');
ELSE
INSERT INTO mylog VALUES(user,sysdate,'U');
END IF;
END;
/
SELECT curr_user,TO_CHAR(curr_date,'yyyy-mm-dd hh24:mi:ss') D FROM mylog;
前触发
CREATE OR REPLACE TRIGGER set_no
BEFORE INSERT ON auto
FOR EACH ROW
DECLARE
sn number(5);
BEGIN
SELECT myseq.nextval INTO sn FROM dual;
:NEW.a:=sn; --指定插入值
END;
/
替换触发器（向由多个基表组成的视图插入数据时用，只能用于视图，不能用于表）
CREATE OR REPLACE TRIGGER tr_v_e_d
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
INSERT INTO deptment VALUES(:new.id,:new.name); --分别插入两个基表
INSERT INTO emp(eid,ename,sex,id) VALUES(:new.eid,:new.ename,:new.sex,:new.id);
END;
表空间
CREATE TABLESPACE tabs
DATAFILE 'C:\tabs.dbf' SIZE 10M;
ALTER USER test DEFAULT TABLESPACE tabs;
GRANT UNLIMITED TABLESPACE,DBA TO test;
CREATE TABLE ty(t char(10),y varchar2(10) TABLESPACE tt;
归档启用
archive log list;
alter system set log_archive_start=true scope=spfile
shutdown immediate
startup mount
alter database archivelog
alter database open;
alter tablespace tt begin backup;
alter tablespace tt end backup;
alter system archive log current;
alter system switch logfile;
alter database datafile 6 offline drop;
浙公网安备 33010602011771号