精通Oracle10编程SQL(1-3)PLSQL基础

数据库  /  管理员 发布于 5年前   329

--只包含执行部分的PL/SQL块--set serveroutput offbegin  dbms_output.put_line('Hello,everyone!');end;select * from emp;--包含定义部分和执行部分的PL/SQL块declare   v_ename varchar2(5);begin   select ename into v_ename from emp   where empno=&no;   dbms_output.put_line('雇员名：'||v_ename);end;--包含定义部分、执行部分和例外处理部分的PL/SQL块declare   v_ename varchar2(5);begin   select ename into v_ename from emp   where empno=&no;   dbms_output.put_line('雇员名：'||v_ename);exception   when NO_DATA_FOUND THEN       dbms_output.put_line('请输入正确的雇员号!');end;select * from emp;--PL/SQL块分类-匿名块declare   v_avgsal number(6,2);begin   select avg(sal) into v_avgsal from emp   where deptno=&no;   dbms_output.put_line('平均工资：'||v_avgsal);end;select * from emp;select * from dept for update;--PL/SQL块分类-命名块(具有特定名称标识的PL/SQL块)--<<outer>>  --在PL/SQL Developer中不可执行declare   v_deptno number(2);   v_dname varchar2(10);begin   --<<inner>>  --在PL/SQL Developer中不可执行   begin       select deptno into v_deptno from emp where lower(ename) = lower('&name');   end; --<<inner>>   select dname into v_dname from dept where deptno = v_deptno;   dbms_output.put_line('部门名:'||v_dname);end; --<<outer>>--子程序-过程create procedure update_sal(name varchar2,newsal number)isbegin  update emp set sal = newsal where lower(ename)=lower(name);end;select * from emp;--exec update_sal('Test',40.5)select * from emp;--子程序-函数create function annual_income(name varchar2)return number is   annual_salary number(7,2);begin   select sal*12 + nvl(comm,0) into annual_salary   from emp where lower(ename)=lower(name);   return annual_salary;end;--调用函数declare    income number(6,2);begin    income:=annual_income('Test2');    dbms_output.put_line(income);end;--子程序-包create package emp_pkg IS  PROCEDURE update_sal(name varchar2,newsal number);  FUNCTION annual_income(name varchar2) return number;end;create package body emp_pkg IS  PROCEDURE update_sal(name varchar2,newsal number)  is  begin      update emp set sal=newsal where lower(ename)=lower(name);  end;  function annual_income(name varchar2) return number  is     annual_salary number(7,2);  begin     select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name);     return annual_salary;  end;end;select * from emp;call emp_pkg.update_sal('Test2',1500);--调用包中的函数declare    income number(6,2);begin    income:=emp_pkg.annual_income('Test2');    dbms_output.put_line(income);end;--触发器--触发器update_cascade用于实现级联更新，如果不建立该触发器，那么当更新dept表的deptno列数据时就会显示错误"ORA-02292:违反完整约束条件--(SCOTT.FK_DEPTNO)-已找到子记录日志";而在建立了该触发器之后，当更新deptno列时，就会级联更新emp表的deptno列的相关数据create OR REPLACE trigger update_cascade   after update of deptno ON dept   for each rowbegin   update emp set deptno=:new.deptno where deptno=:old.deptno;end;--使用标量变量DECLARE   v_ename VARCHAR2(5);   v_sal NUMBER(6,2);   c_tax_rate CONSTANT NUMBER(3,2):=0.03;   v_tax_sal NUMBER(6,2);BEGIN   select ename,sal into v_ename,v_sal from emp where empno=&no;   v_tax_sal:=v_sal*c_tax_rate;   dbms_output.put_line('雇员名：'||v_ename);   dbms_output.put_line('雇员工资：'||v_sal);   dbms_output.put_line('所得税：'||v_tax_sal);end;select * from emp;--使用%TYPE属性--变量v_ename,v_sal与EMP表的ename列、sal列的数据类型和长度完全一致，而变量v_tax_sal与变量v_sal的数据类型和长度完全一致。--这样，当ename列和sal列的类型和长度发生改变时，该PL/SQL块将不需要进行任何修改。DECLARE   v_ename emp.ename%TYPE;   v_sal emp.sal%TYPE;   c_tax_rate CONSTANT NUMBER(3,2):=0.03;   v_tax_sal v_sal%TYPE;BEGIN   select ename,sal into v_ename,v_sal from emp where empno=&eno;   v_tax_sal:=v_sal*c_tax_rate;   dbms_output.put_line('雇员名：'||v_ename);   dbms_output.put_line('雇员工资：'||v_sal);   dbms_output.put_line('所得税：'||v_tax_sal);end;select * from emp for update;--复合变量-PL/SQL记录--emp_record_type是PL/SQL记录类型，并且该PL/SQL记录类型包含了三个成员(name,salary,title);emp_record是记录变量;--emp_record.name则表示引用记录变量emp_record的成员name.declare   type emp_record_type is record(        name emp.ename%TYPE,        salary emp.sal%TYPE,        title emp.job%TYPE);   emp_record emp_record_type;begin   select ename,sal,job into emp_record from emp where empno=7788;   dbms_output.put_line('雇员名：'||emp_record.name);end;--复合变量-PL/SQL表declare   type ename_table_type is table of emp.ename%TYPE        INDEX BY BINARY_INTEGER;   ename_table ename_table_type;begin   select ename into ename_table(-1) from emp where empno=7788;   dbms_output.put_line('雇员名：'||ename_table(-1));end;--复合变量-嵌套表create or replace type emp_type as object(   name varchar2(10),salary number(6,2),   hiredate date);   create or replace type emp_array is table of emp_type;--使用嵌套表类型作为表列时，必须要为其指定专门的存储表，如下所示create table department(   deptno number(2),dname varchar2(10),   employee emp_array)nested table employee store as employee;--复合变量-VARRAY--VARRAY(变长数组)类似于嵌套表，它可以作为表列和对象类型属性的数据类型。--但需要注意，嵌套表的元素个数没有限制，而VARRAY的元素个数是有限制的。--注意：嵌套表列数据需要存储在专门的存储表中，而VARRAY数据则与其他数据一起存放在表段中。create type article_type as object(   title varchar2(30),pubdate date);create type article_array is varray(20) of article_type;create table author(   id number(6),name varchar2(10),article article_array);select * from emp;--参照变量-REF CURSORDECLARE  TYPE cl IS REF CURSOR;  emp_cursor cl;  v_ename emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  OPEN emp_cursor FOR     select ename,sal from emp where deptno = 3;  loop     fetch emp_cursor into v_ename,v_sal;     exit when emp_cursor%NOTFOUND;     dbms_output.put_line(v_ename);  end loop;  close emp_cursor;end;--参照变量-REF obj_typecreate or replace type home_type as object(   street varchar2(50),city varchar2(20),   state varchar2(20),zipcode varchar2(6),   owner varchar2(10));create table homes of home_type;insert into homes values('呼伦北路12号','呼和浩特','内蒙','010010','马鸣');insert into homes values('呼伦北路13号','呼和浩特','内蒙','010010','秦斌');commit;select * from homes--对象表homes存放着家庭所在地以及户主姓名。--为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。create table person(   id number(6) primary key,   name varchar2(10),addr ref home_type);select * from person;insert into person select 1,'马嗣',ref(p) from homes p where p.owner='马鸣';insert into person select 2,'马真',ref(p) from homes p where p.owner='马鸣';insert into person select 3,'王敏',ref(p) from homes p where p.owner='马鸣';commit;