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) < 2; END LOOP; END; set serveroutput on; exec calc('19880323');