精通Oracle10编程SQL(1-3)PLSQL基础
数据库  /  管理员 发布于 5年前   331
--只包含执行部分的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;
122 在
学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..123 在
Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号