oracle 语法小结

ゝ一世哀愁。 2022-06-17 10:44 238阅读 0赞

\--ORACLE总结中

ALTER TABLE tb add cname datatype---增加新列  
go  
/\*删除列会占用系统资源尤其是在高峰时期会影响效率。这时可以将列的状态改为 UNUSED\*/  
ALTER TABLE tb drop column cname ---一次删除一列  
go  
ALTER TABLE tb drop (cname1,cname2..cnamen);--一次删除多列  
GO  
ALTER TABLE tb set unused (columnname1,columnname2..);--修改列的状态为 UNUSED  
go  
ALTER TABLE tb rename column cname to newcname---重命名列名  
go  
ALTER TABLE tb modify cname newdatatype--修改列的类型  
go  
ALTER TABLE tb modify cname NULL (NOT NULL)--修改列上为空或非空约束  
go  
ALTER TABLE tb DROP UNIQUE (cname)--删除列上的唯一约束性  
go  
ALTER TABLE tb DROP CONSTRAINT constraint\_primarykey\_name---删除列上的主键约束名  
GO  
create table tb (id integer,code varchar2(32) constraint constraint\_name  disable | enable---约束的两种状态 disable 禁用，enable 启用  
\--如果是disable 则即使操作与约束有冲突 也会成功执行操作，否则不可以。  
GO  
/\*重命名表名\*/  
ALTER TABLE tb rename to newtab  
\--or  
rename tb to newtab

\--将表移动到另一个表空间中  
ALTER TABLE tb MOVE TABLESPACE tablespace\_name;  
GO  
\--删除表并立即释放该表所占的空间资源 purge 和 删除与该表相关联的 视图、触发器、约束、索引等 constraints  
DROP TABLE tb cascade constraints purge;  
GO  
\--创建序列  
create | alter SEQUENCE sequence\_name  
\[start with n \]  
\[increment by n\]  
\[minvalue n | nominvalue\]\[maxvalue n | nomaxvalue\]  
\[cache cache\_n |nocache\]  
\[cycle |nocycle\]  
\[order |noorder\]  
\--序列中的两个伪例  Currval 和 Nextval  
\--删除序列  
DROP SEQUENCE Sequence\_name;

GO  
/\*  
SELECT \[DISTICT|ALL\]\{\*|column\[,column,...\]\}   
     INTO (variable\[,variable,...\] |record)   
     FROM \{table|(sub-query)\}\[alias\]   
     WHERE............   
    PL/SQL中SELECT语句只返回一行数据。如果超过一行数据，那么就要使用显式游标（对游标的讨论我们将在后面进行），  
    INTO子句中要有与SELECT子句中相同列数量的变量。INTO子句中也可以是记录变量。  
\*/  
\-----oracle中变量的赋值格式是 <变量名 :=值>  
\---GOTO  
lable1:-----必须带“:”  
 ---statement  
if condition  
 goto lable1  
end if

\--循环迭代  
(1)  
if (Condition)  
\---statement  
end if  
(2)  
if (Condition)  
then  
\---statement  
else  
\---statement  
end if  
(2.1)  
if (Condition)  
then  
\---statement  
elseif   then  
\---statement  
elseif   then  
\---statement  
end if  
(3)  
loop  
\---statement  
if (Condition)  
exit  
end if  
end loop  
(3.1)  
loop  
\---statement  
exit when (Condition)  
end loop  
(4)  
for Condition in 1..5 loop ----for只能是数值型循环  
\---statement  
end loop  
(4.1)  
for Condition in reverse 1..5 loop ----for只能是数值型循环(reverse倒序)  
\---statement  
end loop

\-----ORACLE定义变量并赋值  
(1)  
declare id number  
declare names varchar(20)  
select id,name into id,names from sysuser  
(2)  
/\*%TYPE属性   在PL/SQL中可以将变量和常量声明为内建或用户定义的数据类型，以引用一个列名，  
同时继承他的数据类型和大小。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，  
如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。  
不但列名可以使用%TYPE,而且变量、游标、记录，或声明的常量都可以使用%TYPE。这对于定义相同数据类型的变量非常有用  
\*/  
declare id sysuser.id%type  
select id into id from sysuser  
(3)  
declare t\_sysuser\_rowdata sysuser%rowtype  
declare names sysuser.name%rowtype  
select \* into t\_sysuser\_rowdata from sysuser where id=1  
\---得到记录中的具体的某个值  
names:=t\_sysuser\_rowdata.name

\----------------------------------ORACLE存储过程  
\--建立一个最简单的存储过程  
create or replace procedure test\_xg\_p1 is  
begin  
dbms\_output.put\_line('hello world!');  
end;  
\--建立一个带输入输出参数的存储过程：把输入的数据传给输出参数  
create or replace procedure test\_xg\_p2(a in number,x out number) is  
begin  
x:=a;  
end test\_xg\_p2;  
\--建立一个逻辑判断的存储过程，并包含输入输出参数：近似分数的登记判断  
create or replace procedure test\_xg\_p3(a in number,x out varchar2) is  
begin  
if a>=90 then  
   begin  
   x := 'A';  
   end;  
end if;  
if a<90 then  
   begin  
   x:='B';  
   end;  
end if;  
if a<80 then  
   begin  
   x:='C';  
   end;  
end if;  
if a<70 then  
   begin  
   x:='D';  
   end;  
end if;  
if a<60 then  
   begin  
   x:='E';  
   end;  
end if;  
end test\_xg\_p3;  
\--建立一个带循环逻辑的存储过程：近似累加函数  
create or replace procedure test\_xg\_p4(a in number,x out varchar2) is  
tempresult number(16);  
begin  
tempresult :=0;  
for tempa in 0..a loop  
    begin  
    tempresult := tempresult + tempa;  
    end;  
end loop;  
x:=tempresult;  
end test\_xg\_p4;  
\--建立一个能从数据库中特定表中返回数据的存储过程：  
create or replace procedure test\_xg\_p5(x out varchar2) is  
tempresult varchar2(1024);  
begin  
tempresult := 'start->';  
select hotelid||hotelname into tempresult from hotel where hotelid =10041764;  
x:=tempresult;  
end test\_xg\_p5;  
\--建立一个能使用游标的带循环的存储过程:  
create or replace procedure test\_xg\_p6(x out varchar2) is  
tempresult varchar2(10240);  
cursor cursor1 is select \* from hotel where hotelname like '北京%';  
begin  
tempresult := 'start->';  
for cursor\_result in cursor1 loop  
begin  
tempresult :=tempresult||cursor\_result.hotelid||cursor\_result.hotelname;  
end;  
end loop;  
x:=tempresult;  
end test\_xg\_p6;  
///  
(1)  
create or replace procedure procedurename  
(  
 id number,  
 names varchar,  
 classid number:=1,  
 depart varchar:=null,  
 city in varchar,           ------in    指明该参数是输入参数  
 postcode out varchar:=null, ------out   指明该参数是输出参数  
 age inout number=0         ------inout 指明该参数即是输入参数又是输出参数

)  
as---(或者is) as 和is 可以互换没有任何区别  
begin  
\---statement  
 EXCEPTION   
       WHEN NO\_DATA\_FOUND THEN   
       DBMS\_OUTPUT.PUT\_LINE('Employee Number Not Found!');  
end procedurename

\----ORACLE函数  
create or replace function functionname  
(  
parameter 1,  
parameter 2,  
parameter 3-------参数和存储过程一样可有可无，不用约束参数类型的具体定义，即不用知道参数的大小，只需标明是说明类型即可  
)  
returnparameter varchar  
as  
\---statement

return returnparameter;

\-----ORACLE游标  
/\*  
游标：

用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

分类：

静态游标：  
分为显式游标和隐式游标。

REF游标：  
是一种引用类型，类似于指针。

三 游标的属性

oracle 游标有4个属性：%ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT。

%ISOPEN判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false；

%FOUND %NOTFOUND判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false；

%ROWCOUNT返回当前位置为止游标读取的记录行数  
\*/  
\---oracle的游标变量必须在定义完游标后才能定义游标变量否则语法错误。  
(1)  
declare c\_cursor is select id,name from sysuser  
v\_id sysuser.id%rowtype,v\_name sysuser.name%rowtype  
begin  
 open c\_cursor  
 fetch c\_cursor into v\_id,v\_name  
 update sysuser set name:=v\_name where id=v\_id  
 close c\_cursor  
end  
(2)  
\--当使用LOOP ..END LOOP OR FOR ...IN (REVERSE)循环时，不用写明 open c\_cursor,fetch c\_cursor into v\_parameter,close c\_cursor.  
declare c\_cursor is select id,name from sysuser  
v\_id sysuser.id%rowtype,v\_name sysuser.name%rowtype  
begin  
 loop  
 --statement  
if condition  
 exit  
end if  
 end loop  
end

\---案例  
set serveroutput on;  
declare  
varno varchar2(20);  
varprice varchar2(20);

CURSOR mycur(vartype number) is  
select emp\_no,emp\_zc from cus\_emp\_basic  
where com\_no = vartype;  
begin

if mycur%isopen = false then  
open mycur(000627);---000627 （参数，高级游标中有参数）  
end if;

fetch mycur into varno,varprice;  
while mycur%found  
loop  
dbms\_output.put\_line(varno||','||varprice);  
if mycur%rowcount=2 then  
exit;  
end if;  
fetch mycur into varno,varprice;  
end loop;  
close mycur;  
end;  
\---案例

\---案例（自定义游标类型）PL/SQL记录的结构和C语言中的结构体类似，是由一组数据项构成的逻辑单元。

\--PL/SQL记录并不保存在数据库中，它与变量一样，保存在内存空间中，在使用记录时候，要首先定义记录结构，然后声明记录变量。  
\--可以把PL/SQL记录看作是一个用户自定义的数据类型。  
set serveroutput on;  
declare  
type person is record  
(  
empno cus\_emp\_basic.emp\_no%type,  
empzc cus\_emp\_basic.emp\_zc%type);  
person1 person;

cursor mycur(vartype number)is  
select emp\_no,emp\_zc from cus\_emp\_basic  
where com\_no=vartype;  
begin  
if mycur%isopen = false then  
open mycur(000627);  
end if;  
loop  
fetch mycur into person1;  
exit when mycur%notfound;  
dbms\_output.put\_line('员工编号:'||person1.empno||',地址:'||person1.empzc);  
end loop;  
close mycur;  
end;  
\---案例（自定义游标类型）

\------典型游标for 循环  
/\*典型游标for 循环  
游标for循环示显示游标的一种快捷使用方式，它使用for循环依次读取结果集中的行数据，  
当form循环开始时，游标自动打开（不需要open），  
每循环一次系统自动读取游标当前行的数据（不需要fetch)，当退出for循环时，游标被自动关闭（不需要使用close）。  
使用游标for循环的时候不能使用open语句，fetch语句和close语句，否则会产生错误。  
\*/  
set serveroutput on;  
declare  
cursor mycur(vartype number)is  
select emp\_no,emp\_zc from cus\_emp\_basic  
where com\_no=vartype;  
begin  
for person in mycur(000627) loop  
dbms\_output.put\_line('员工编号:'||person.emp\_no||',地址:'||person.emp\_zc);  
end loop;  
end;  
\------典型游标for 循环

\-----显式游标：

CURSOR 游标名 ( 参数 ) \[返回值类型\] IS  
  Select 语句

\----选项：参数和返回类型

set serveroutput on  
declare  
 cursor emp\_cur ( p\_deptid in number) is  
select \* from employees where department\_id = p\_deptid;

l\_emp employees%rowtype;  
begin  
 dbms\_output.put\_line('Getting employees from department 30');  
open emp\_cur(30);  
 loop  
  fetch emp\_cur into l\_emp;  
  exit when emp\_cur%notfound;  
  dbms\_output.put\_line('Employee id '|| l\_emp.employee\_id || ' is ');  
  dbms\_output.put\_line(l\_emp.first\_name || ' ' || l\_emp.last\_name);  
 end loop;  
 close emp\_cur;

dbms\_output.put\_line('Getting employees from department 90');  
open emp\_cur(90);  
 loop  
  fetch emp\_cur into l\_emp;  
  exit when emp\_cur%notfound;  
  dbms\_output.put\_line('Employee id '|| l\_emp.employee\_id || ' is ');  
  dbms\_output.put\_line(l\_emp.first\_name || ' ' || l\_emp.last\_name);  
 end loop;  
 close emp\_cur;  
end;  
\----选项：参数和返回类型  
\--------显式游标：  
游标属性：

%FOUND：--变量最后从游标中获取记录的时候，在结果集中找到了记录。  
%NOTFOUND：--变量最后从游标中获取记录的时候，在结果集中没有找到记录。  
%ROWCOUNT：--当前时刻已经从游标中获取的记录数量。  
%ISOPEN：--是否打开。

Declare  
 Cursor emps is  
 Select \* from employees where rownum<6 order by 1;  
   
 Emp employees%rowtype;  
 Row number :=1;  
Begin  
 Open emps;  
 Fetch emps into emp;  
   
 Loop  
  If emps%found then  
   Dbms\_output.put\_line('Looping over record '||row|| ' of ' || emps%rowcount);  
   Fetch emps into emp;  
   Row := row + 1;  
  Elsif emps%notfound then  
   Exit;  ---exit loop, not IF  
  End if;  
 End loop;  
   
 If emps%isopen then  
  Close emps;  
 End if;  
End;  
/

/\*  
显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

REF CURSOR游标：

动态游标，在运行的时候才能确定游标使用的查询。分类：  
强类型（限制）REF CURSOR，规定返回类型  
弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。  
\*/

TYPE ref\_cursor\_name IS REF CURSOR \[RETURN return\_type\]

Declare  
 Type refcur\_t is ref cursor;  
   
 Type emp\_refcur\_t is ref cursor return employee%rowtype;  
Begin  
 Null;  
End;  
/

强类型举例：

declare  
 --声明记录类型  
 type emp\_job\_rec is record(  
  employee\_id number,  
  employee\_name varchar2(50),  
  job\_title varchar2(30)  
 );  
 --声明REF CURSOR，返回值为该记录类型  
 type emp\_job\_refcur\_type is ref cursor  
  return emp\_job\_rec;  
 --定义REF CURSOR游标的变量  
 emp\_refcur emp\_job\_refcur\_type;

emp\_job emp\_job\_rec;  
begin  
 open emp\_refcur for  
  select e.employee\_id,  
    e.first\_name || ' ' ||e.last\_name "employee\_name",  
    j.job\_title  
  from employees e, jobs j  
  where e.job\_id = j.job\_id and rownum < 11 order by 1;

fetch emp\_refcur into emp\_job;  
 while emp\_refcur%found loop  
  dbms\_output.put\_line(emp\_job.employee\_name || '''i job is ');  
  dbms\_output.put\_line(emp\_job.job\_title);  
  fetch emp\_refcur into emp\_job;  
 end loop;  
end;