oracle 语法小结

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