Oracle中SQL有哪些

这篇文章将为大家详细讲解有关oracle中sql有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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');  

关于“Oracle中SQL有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。