oracle10g plsql编程1

select max(id) from student;
--1......批量添加Student中的数据
declare   
i int := 14;
begin
   while i <= 100 loop
   insert into student(id,name,birthday) values(i,'陈超阳'||i,sysdate);
   i := i+1;
   commit;
   end loop;
end;
--单行注释*******************************

commit;
--第二......totalNumber是count(*)的别名
select count(*) totalNumber from student; 

/*第三：
多行注释
*/
select * from student;

--第四：boolean值
declare
  bDone boolean;
begin
  bDone := false;
  while not bDone loop
  Null;
  end loop;
end;

--第五：%type可以和表中的某一列的类型保持一致,select into:把查询出来的东西复制给变量
declare
  student_id        student.id%type := 45;
  student_name      student.name%type;
  student_birthday  student.birthday%type;
begin
  select id,name,birthday into student_id,student_name,student_birthday from student where id = student_id;
  DBMS_OUTPUT.put_line (student_id);
  DBMS_OUTPUT.PUT_LINE (student_name);
  DBMS_OUTPUT.PUT_LINE (student_birthday);
end;
--******************************************
select * from student;

--第六：变量的作用范围是在你所引用的程序单元

--第七：插入、删除数据 to_date ,%type知识点
declare
  v_id        student.id%type := 101;
  v_name      student.name%type := 'chenchaoyang';
  v_birthday  student.birthday%type := to_date('1987.02.13','yyyy.mm.dd');
begin
  insert into student values(v_id,v_name,v_birthday);
  commit;
end;
select * from student where name like '%chenchaoyang%';
--*******************************************


--第八：if then elsif then else语句  &语句 
declare
  v_studentIdTemp student.id%type  := &studentId;
  v_comment varchar2(50);
  v_studentId student.id%type;
begin
  select id into v_studentId from student where id = v_studentIdTemp;
  if v_studentId < 30 then
      v_comment := 'id小于30';
  elsif v_studentId < 50 then
      v_comment := 'id大于30小于50';
  else
      v_comment := 'id大于50了';
  end if;
  DBMS_OUTPUT.put_line(v_comment);
end;
--*********************************************


select * from hr.employees;
select * from emp;
select count(*) from emp;  --14
select sum(sal) from emp;  --29025
select avg(sal) from emp;  --2073.21428571429
select max(sal) from emp;  --5000
select min(sal) from emp;  --800

select * from dept;
update dept set dname = '人事部' where deptno = 10;
update dept set dname = '软件一部' where deptno = 20;
update dept set dname = '软件二部' where deptno = 30;
update dept set dname = '软件实施部' where deptno = 40;
commit;
select * from emp;
--第九：左连接查询
select emp.empno,emp.ename,emp.sal,dept.dname from emp left join dept on  emp.deptno = dept.deptno where dept.deptno = 10;
--**********************


select * from emp;
--//第十：循环1（至少指定一次循环）
declare
   i int := 0;
begin
   loop
     i := i+1;
     dbms_output.put_line(i);
     exit when i >100;
   end loop;
end;
--*********************************


--第十一：while循环（可以不执行一次）
declare
   x number := 1;
begin
   while x <= 10 loop
      x := x+1;
      dbms_output.put_line('x当前的值：'||x);
   end loop;
end;


--第十二是：数字循环(for 紧跟的是变量）
begin
  for ccy in 1..10 loop
    dbms_output.put_line('ccy 的当前值是：'||ccy);
  end loop;
end;

--第十三：数字循环例子
create table temp_table(num_col number);
commit;

declare
   v_counter number := 10;
begin
   insert into temp_table(num_col) values(v_counter);
   for v_counter in 20..50 loop
     insert into temp_table(num_col) values(v_counter);
   end loop;
   for v_counter in reverse 20..25 loop   --???????????????
     insert into temp_table(num_col) values(v_counter);
   end loop;
end;
drop table temp_table;


--第十四：数组
/*
VARRAY(varying array的简写)和其他任何程序语言中的数组都相似
：存储确定数目的元素，但是它们具有可伸缩性。它们有一个最大的容量，
当超过这个容量后将不再可扩展。它们的下标是从1开始的有序数字。
也有许多方法用来操作数组中的各项。对方法的调用可通过在VARRAY变量名后添加相应的方法来实现。
*/
select max(id) from student;
--1......批量添加Student中的数据
declare   
i int := 14;
begin
   while i <= 100 loop
   insert into student(id,name,birthday) values(i,'陈超阳'||i,sysdate);
   i := i+1;
   commit;
   end loop;
end;
--单行注释*******************************

commit;
--第二......totalNumber是count(*)的别名
select count(*) totalNumber from student; 

/*第三：
多行注释
*/
select * from student;

--第四：boolean值
declare
  bDone boolean;
begin
  bDone := false;
  while not bDone loop
  Null;
  end loop;
end;

--第五：%type可以和表中的某一列的类型保持一致,select into:把查询出来的东西复制给变量
declare
  student_id        student.id%type := 45;
  student_name      student.name%type;
  student_birthday  student.birthday%type;
begin
  select id,name,birthday into student_id,student_name,student_birthday from student where id = student_id;
  DBMS_OUTPUT.put_line (student_id);
  DBMS_OUTPUT.PUT_LINE (student_name);
  DBMS_OUTPUT.PUT_LINE (student_birthday);
end;
--******************************************
select * from student;

--第六：变量的作用范围是在你所引用的程序单元

--第七：插入、删除数据 to_date ,%type知识点
declare
  v_id        student.id%type := 101;
  v_name      student.name%type := 'chenchaoyang';
  v_birthday  student.birthday%type := to_date('1987.02.13','yyyy.mm.dd');
begin
  insert into student values(v_id,v_name,v_birthday);
  commit;
end;
select * from student where name like '%chenchaoyang%';
--*******************************************


--第八：if then elsif then else语句  &语句 
declare
  v_studentIdTemp student.id%type  := &studentId;
  v_comment varchar2(50);
  v_studentId student.id%type;
begin
  select id into v_studentId from student where id = v_studentIdTemp;
  if v_studentId < 30 then
      v_comment := 'id小于30';
  elsif v_studentId < 50 then
      v_comment := 'id大于30小于50';
  else
      v_comment := 'id大于50了';
  end if;
  DBMS_OUTPUT.put_line(v_comment);
end;
--*********************************************


select * from hr.employees;
select * from emp;
select count(*) from emp;  --14
select sum(sal) from emp;  --29025
select avg(sal) from emp;  --2073.21428571429
select max(sal) from emp;  --5000
select min(sal) from emp;  --800

select * from dept;
update dept set dname = '人事部' where deptno = 10;
update dept set dname = '软件一部' where deptno = 20;
update dept set dname = '软件二部' where deptno = 30;
update dept set dname = '软件实施部' where deptno = 40;
commit;
select * from emp;
--第九：左连接查询
select emp.empno,emp.ename,emp.sal,dept.dname from emp left join dept on  emp.deptno = dept.deptno where dept.deptno = 10;
--**********************


select * from emp;
--//第十：循环1（至少指定一次循环）
declare
   i int := 0;
begin
   loop
     i := i+1;
     dbms_output.put_line(i);
     exit when i >100;
   end loop;
end;
--*********************************


--第十一：while循环（可以不执行一次）
declare
   x number := 1;
begin
   while x <= 10 loop
      x := x+1;
      dbms_output.put_line('x当前的值：'||x);
   end loop;
end;


--第十二是：数字循环(for 紧跟的是变量）
begin
  for ccy in 1..10 loop
    dbms_output.put_line('ccy 的当前值是：'||ccy);
  end loop;
end;

--第十三：数字循环例子
create table temp_table(num_col number);
commit;

declare
   v_counter number := 10;
begin
   insert into temp_table(num_col) values(v_counter);
   for v_counter in 20..50 loop
     insert into temp_table(num_col) values(v_counter);
   end loop;
   for v_counter in reverse 20..25 loop   --???????????????
     insert into temp_table(num_col) values(v_counter);
   end loop;
end;
drop table temp_table;


--第十四：数组
/*
VARRAY(varying array的简写)和其他任何程序语言中的数组都相似
：存储确定数目的元素，但是它们具有可伸缩性。它们有一个最大的容量，
当超过这个容量后将不再可扩展。它们的下标是从1开始的有序数字。
也有许多方法用来操作数组中的各项。对方法的调用可通过在VARRAY变量名后添加相应的方法来实现。
*/

--第十五：标号和goto
/*
在以下地方使用是不合法的，编译时会出错误。 跳转到非执行语句前面。 跳转到子块中。
跳转到循环语句中。 跳转到条件语句中。 从异常处理部分跳转到执行。 从条件语句的一部分跳转到另一部分。
*/
declare
   v_counter  number := 1;
begin
   loop
      dbms_output.put_line('v_counter的当前值为：'||v_counter);
      v_counter := v_counter + 1;
   if v_counter > 10 then
      goto labeloffloop;
   end if;
   end loop;
   <<labeloffloop>>
      dbms_output.put_line('v_counter跳出循环后的的当前值为：'||v_counter);
end;
--执行结果
/*
v_counter的当前值为：1
v_counter的当前值为：2
v_counter的当前值为：3
v_counter的当前值为：4
v_counter的当前值为：5
v_counter的当前值为：6
v_counter的当前值为：7
v_counter的当前值为：8
v_counter的当前值为：9
v_counter的当前值为：10
v_counter跳出循环后的的当前值为：11
**********************************************************************
*/


--第十六：null语句
/*
在PL/SQL 程序中，NULL语句是一个可执行语句，
可以用 null 语句来说明“不用做任何事情”的意思，
相当于一个占位符或不执行任何操作的空语句，
可以使某些语句变得有意义，提高程序的可读性，
保证其他语句结构的完整性和正确性。如： 
*/


--第十七：游标使用
/*
在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，
ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。
游标是指向该区的一个指针，或是命名一个工作区（Work Area），
或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多
行数据查询结果集中的每一行数据分别进行单独处理的方法，
是设计嵌入式SQL语句的应用程序的常用编程方式。
对于不同的SQL语句，游标的使用情况不同：
非查询语句            隐式的
结果是单行的查询语句  隐式的或显示的
结果是多行的查询语句  显示的
*/

--第十八：显式游标处理
/*
CURSOR cursor_name[(parameter[, parameter]…)]
       [RETURN datatype] 
IS select_statement;
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的
[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择
，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。
一般是记录数据类型或带“%ROWTYPE”的数据。
PL/SQL 程序不能用OPEN 语句重复打开一个游标。
提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。 
格式： FETCH cursor_name INTO {variable_list | record_variable };
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一
行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次
执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数
据行，以便确定是否给对应的变量赋了值。
对该记录进行处理；
继续处理，直到活动集合中没有记录；
关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，
并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN
语句重新打开。
格式：CLOSE cursor_name;
注：定义的游标不能有INTO 子句。
*/
declare
  cursor c_cursor is select empno,ename from emp where rownum < 11;
  v_ename emp.ename%type;
  v_empno emp.empno%type;
begin
  open c_cursor;
  fetch c_cursor into v_empno,v_ename;
  while c_cursor%found  loop
    dbms_output.put_line(v_empno || '-----' || v_ename);
    fetch c_cursor into v_empno,v_ename;
  end loop;
  close c_cursor;
end;
/*输出结果
7369-----SMITH
7499-----ALLEN
7521-----WARD
7566-----JONES
7654-----MARTIN
7698-----BLAKE
7782-----CLARK
7788-----SCOTT
7839-----KING
7844-----TURNER
*/


select * from emp; --empno,ename,job,mgr,hiredate,sal,comm,deptno
--//--第十九：游标参数的传递方法

declare 
  v_ename    emp.ename%type;
  v_sal      emp.sal%type;
  v_hiredate emp.hiredate%type;
  cursor c1 is select ename,sal,hiredate from emp where empno < 7839;
  cursor c2(emp_no number default 10) is select ename,sal,hiredate from emp where empno < emp_no;
begin
  open c1;
  loop
    fetch c1 into v_ename,v_sal,v_hiredate;
    exit when c1%notfound;
      dbms_output.put_line(v_ename||'--------'||v_sal||'-------'||v_hiredate);
  end loop;
  close c1;

  open c2(7934);   --这个便是游标的传递参数
  loop
     fetch c2 into v_ename,v_sal,v_hiredate;   
     exit when c2%notfound;  
     dbms_output.put_line(v_ename||'--------'||v_sal||'-------'||v_hiredate);
  end loop;
end;
/*运行结果
SMITH--------800-------17-12月-80
ALLEN--------1600-------20-2月 -81
WARD--------1250-------22-2月 -81
JONES--------2975-------02-4月 -81
MARTIN--------1250-------28-9月 -81
BLAKE--------2850-------01-5月 -81
CLARK--------2450-------09-6月 -81
SCOTT--------3000-------19-4月 -87
SMITH--------800-------17-12月-80
ALLEN--------1600-------20-2月 -81
WARD--------1250-------22-2月 -81
JONES--------2975-------02-4月 -81
MARTIN--------1250-------28-9月 -81
BLAKE--------2850-------01-5月 -81
CLARK--------2450-------09-6月 -81
SCOTT--------3000-------19-4月 -87
KING--------5000-------17-11月-81
TURNER--------1500-------08-9月 -81
ADAMS--------1100-------23-5月 -87
JAMES--------950-------03-12月-81
FORD--------3000-------03-12月-81
*/


--//第二十：游标属性
/*
Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为false;
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
*/
--例子：给工资低于1200 的员工增加工资50。
declare 
  v_empno   emp.empno%type;
  v_ename   emp.ename%type;
  v_sal     emp.sal%type;
  cursor c is select empno,sal from emp;
begin
  open c;
  loop
     fetch c into v_empno,v_sal;
     exit when c%notfound;
     if v_sal <= 1500 then
         update emp set sal = sal+1000 where empno = v_empno;
         dbms_output.put_line('编码为 '|| v_empno || '工资已经更新');
      end if;
      dbms_output.put_line('记录数: ' || c%rowcount); --c%rowcount表示游标已经打开的记录的条数
   end loop;
   close c;
end;    
--*******************************************

--//第二十一：没有参数没有返回值的游标
declare
  v_name emp.ename%type;
  v_job  emp.job%type;
  cursor c   --声明没有参数、没有返回值的游标
  is
       select ename,job from emp where empno = 7521;
begin
  open c;  --打开游标
  loop
     fetch c into v_name,v_job;  --提取游标
     if c%found  then
       dbms_output.put_line(v_name || '的岗位是：' || v_job);
     else
        dbms_output.put_line('已经处理完毕结果集了'); 
        exit;
     end if;
  end loop;
  close c;  --关闭游标
end;
/*打印结果：
WARD的岗位是：SALESMAN
已经处理完毕结果集了
************************************************/


--//第二十二：有参数没有返回值的游标
declare
  v_name    emp.ename%type;
  v_hdate   emp.hiredate%type;
  cursor cc(myename varchar2,myejob varchar2)
  is
    select ename,hiredate from emp where ename = myename and hiredate = myejob;
begin
  open cc('陈超阳','CLERK');
  loop
    fetch cc into v_name,v_hdate;
    if cc%found then
       dbms_output.put_line(v_name || '的雇佣日期是：' || v_hdate);
    else
       dbms_output.put_line('已经处理完结果集了');   
       exit;
    end if;
   end loop;
   close cc;
end;


--//第二十三：两个字段拼接成一个字段
select ename||job from emp;
--*******************************************

--//第二十四：游标的for循环
/*
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句
的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所
提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合
中的所有数据行后结束循环，并自动关闭游标。
格式：
  FOR index_variable IN cursor_name[(value[, value]…)] LOOP
     -- 游标数据处理代码
  end loop;
  其中：
  index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返
  回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，
  index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择
  列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些
  ：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。
*/
declare
  cursor c is select ename,sal,deptno from emp;
begin
  --隐含打开游标
  for v_temp in c loop
  --隐含执行一个fetch语句
      dbms_output.put_line(v_temp.ename || '----' || to_char(v_temp.sal) || to_char(v_temp.deptno) );
  --隐含检测c%notfound
  end loop;
  --隐含关闭游标
end;
--****************************************************************************************


--//第二十五：当所声明游标有参数时，通过游标for循环语句为其赋值
declare 
  cursor c(empno_temp number default 10)
  is
    select empno,ename,sal from emp where empno  <= empno_temp;
begin
  dbms_output.put_line('当前dept no参数值为：位置');
  for v_temp in c(7934) loop
   dbms_output.put_line(v_temp.empno || '----' || v_temp.ename || '----' || v_temp.sal);
  end loop;
end;
--*****************************************************************************************


--//第二十六：处理隐式游标
/*
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，
如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建
的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如
定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游
标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无
关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%
注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。
*/
--例子：删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门。
declare
   v_deptno dept.deptno%type := &p_deptno;
begin
   delete from emp where deptno = v_deptno;
   if SQL%notfound then
     delete from dept where deptno = v_deptno;
   end if;
end;
--******************************************************


--//第二十七：通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。
declare 
  v_rows number;
begin
  --更新数据
  update emp set sal = sal + 1000 where deptno = 10;
  --获取默认游标的属性值
  v_rows := SQL%ROWCOUNT;
  DBMS_OUTPUT.put_line('更新了' || v_rows || '个数据');
  --回退更新，以便使数据库的数据表示原样
  rollback;
end;
--*******************************************************


--//第二十八：关于 NO_DATA_FOUND 和 %NOTFOUND的区别
/*
SELECT … INTO 语句触发 NO_DATA_FOUND；
当一个显式游标的WHERE子句未找到时触发%NOTFOUND；
当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要
用 %NOTFOUND 或%FOUND 来确定循环的退出条件，
游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须
使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择
的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，
直到您的事务处理提交或回退为止。
SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话
释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话
锁定，则OPEN立即返回并给出：
ORA-0054 ：resource busy and acquire with nowait specified.
如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用
WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行
*/
--实例：13：从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为 1500；