pl/sql知识点总结

概念

  • pl/sql(procedural language/sql)是oracle专有的在sql上扩展的一门语言,不仅可以写sql语言,还可以定义变量和常量。
  • pl/sql编写的存储过程,函数等可以提高效率,省去了传统程序与数据库交互时的编译时间。(例如:传统java程序在访问数据库带着sql语句访问,sql语句需要编译后才能被数据库识别)

编写

存储过程

  • SQL>

    create or replace procedure sp_pro1 is

    2 begin

    3 insert into test values(2,’wangxin’,’男’,to_date(‘1994-03-19’,’yyyy-mm-dd’));

    4 end;

    5 /

    Procedure created

  • 调用:exec 过程名(参数1,参数2,…)

  • 调用:call 过程名 (参数1,参数2,…)

变量

  • 定义变量时以V做前缀,例如:v_sal;
  • 定义常量时以c做前缀;
  • 定义游标以_cursor作为后缀
  • 定义例外时用e做前缀

pl/sql块

  • 三个部分:

    declear

    定义部分

    begin

    处理部分

    exception

    例外处理部分

    end;


  • set serveroutput on/off:设置控制台输出是否打开,选择关闭则控制台不会显示出数据。

示例程序:

declare
v_userid int;//定义一个变量v_userid
begin 
select userid into v_userid from test where username=&name;//选择userid列,并将得到的值放入到v_userid中,&name:代表用户手动输入一个值,保存到name中,并替代name。
dbms_output.put_line('用户名'|| v_userid);// “||” 代表字符串连接符的作用,dbms_output是系统自带的包,类似java的包名.类名。put_line是系统自带的存储过程。
end;
/

如果出现异常情况:例如,没有符合条件的记录,那么,就会发生异常,此时要对异常进行捕获。

begin
xxx;
exception
when 异常名字 then 进行何种操作;
end;

函数

create function func1(usid int) return varchar2 is uname varchar2(20);//usid为传入参数名字,return varchar2表示返回值类型,is是关键字,uname varchar2(20),代表返回值具体接收变量
begin
select username into uname from test where userid=usid;
return uname;//执行后需要有的返回值,跟java语法类似。
end;
/

create package 包名 is
procedure  过程名(参数1,参数2,...);
....
function  函数名(参数1,参数2,...)return number(这里指表明返回值,不是一定写number);
....


create package body 包名 is
procedure 过程名(参数) is
begin
xxxxx
end;
...
function 函数名(参数)return 类型 is 变量名 类型;
begin
xxxx
return 变量;
end;
end;

pl/sql记录类型(类似于结构体)

declare 
type 类型名 is record(name 类型,xxx 类型,...);//;这里定义一个新类型,就像结构体中定义一个
//新的类型一样。
新变量名 类型名//定义一个新的变量,类型是自己设定的这个类型。
begin
xxx操作
end;

pl/sql表(相当于数组)

declare
type 新类型名 is table of 类型名 index by binary_integer;//因为table相当于数组,数组中类型固定,所以,of 类型名将统一设定一个类型,后面的index by xxxx是对下标的限定,这里表示下标为整数,可正可负。
变量名 新类型名;
begin
xxx操作
end;

pl/sql参照变量

游标变量 ref cursor

declare
type 新类型名 is ref cursor;
新变量 新类型名
接收变量1 emp.ename%type;
接收变量2 emp.sal%type;
begin
//这里需要将游标和select结合
open 新变量 for select ename,sal from emp where deptno=&no;
loop //循环执行语句
fetch 新变量 into 接收变量1,接收变量2;//fetch是从游标变量取,此时游标变量相当于resultset这个类了,开始指向第一个元组。
xxxxx//对数据进行其他处理 
exit when test_cursor%notfound;//结束循环条件
end loop;
end;

特殊东东

表名.列名%type:有时列里面的值长度是不固定的,在外部无法判断,直接写varchar2(num)这样的话也有可能出错,于是,就写这种方式,可以

赋值号

:=是赋值号

绑定变量

sql>var 变量名 类型;
sql>exec :变量名 :=值;//变量名前面的:不能少,这个才代表他是绑定变量
sql>success(这是提示信息,省略简写);

也可以这样写

  • sql>var 变量名 类型;
  • sql>begin
  • sql>:变量名:=值;
  • sql>end;
  • sql>/;
  • sql>succcess

以上提到的只是赋值成功,要想看到值,需要这样

  • sql>print 变量名;

条件语句

1.
编写一个过程,可以输入一个雇员名,如果雇员工资低于2000,就给该雇员工资增加10%;
create procedure sp_pro1(empname varchar2) is
salary emp.sal%type;
begin
select sal into salary from emp where ename=epname;
if salary<2000 then 
update emp
set salary=salary*1.1
where ename=epname;
end if;
end;
2.
编写一个存储过程,可以输入一个雇员名,如果该雇员的补助不是0,就在原来的基础上增加100,如果补助是0,那么久把补助设置为200.
create procedure sp_pro2(epname varchar2) is
newbuzhu emp.buzhu%type;
begin
select buzhu into newbuzhu from emp
where ename=epname;
if newbuzhu!=0
then update emp set buzhu=buzhu+100
where ename=epname;
else update emp set buzhu=200
where enmae=epname; 
end if;
end;
3.
如果有多个条件的话,else if 应该写成 
elsif,这样才对,然后接着是then。

循环语句

1. loop   end loop;
编写一个过程,可输入用户名,并循环添加10个用户到users表里面,用户编号从1开始增加。
create procedure sp_pro3(epname varchar2) is
no number:=1;
begin
loop
insert into users values(no,epname);
no:=no+1;
exit when no=11;
end loop;
end;
2. while 条件 loop  xxxx操作 end loop;

create procedure sp_pro4(epname varchar2) is
no number:=1;
begin
while no<=10 loop
insert into users values(no,epname);
no:=no+1;
end loop;
end;
3. for循环;
create procedure sp_pro5(epname varchar2) is
no number:=1;
begin
for i in 1..10 loop
xxxx
end loop;
end;

顺序控制语句

1.null,主要是起到增强可读性的作用。
例如java中这样
if(xxx)
{
    xxx;
}
else
{
}
else里面没有任何东西,显得可读性差,于是,这plsql里面
if xxx then
else null;
end if;
增强可读性

返回结果集的存储过程

1.首先创建一个包(包用来存放定义的游标类型,因为这个类型需要被存储起来,因此包起到了一个存储的作用)
Create or replace package testpackage is    
type test_cursor is ref cursor
end;
2.创建过程
create procedure sp_pro6(spno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spno;
end;

编写分页过程

1.编写一个存储过程,要求输入表名,每页显示记录数,当前页,返回总记录数,总页数,和返回的结果集。

create procedure sp_pro7(tableName varchar2,pageCount number,currentPage number,totalCount out number,totalPage out number,total_cursor out testpackage.test_cursor) is
begin 
open total_cursor for select * from (select t1.*,rownum from (select * from emp)t1 where rownum>((currentPage-1)*pageCount))t2 where t2.rownum<=(currentPage* pageCount);
select count(*) into totalCount from emp;
if mod(totalCOunt,pageCount)=0
then totalPage:=totalCount/pageCount;
else totalPage:=totalCount/pageCount+1;
end if;
close total_cursor;
end;
//说明:open游标后要记得关闭,否则在其他地方使用相同游标会报错,原因是你打开它就相当于获取了使用权,有点类似于synchronized一样,一次只能一个在使用,所以要记得关闭。

游标,这个游标不是刚才的游标类型

为什么说预编译的sql会效率高,因为预编译的结构会注册hash-bucket,这叫做父游标,存储sql的文本,然而即使有时sql文本相同,但是仍然还有一些其他因素不同,导致sql执行重新编译,于是这父游标下面会有子游标记录着其他那些表信息,执行计划等等。如果找到父,子游标都相同的sql执行,那么就不必重新编译了。

执行计划

执行计划是查询优化器根据sql语句和当前表结构以及内部数据多少等信息自动生成的一条查询计划。

例外处理(异常)

1.预定义例外:系统自带,在编写plsql语句时的例外
2.非预定义例外:处理预定义例外不能处理的,例如用户登录时出错,一般不是plsql语法级别的的。
3.自定义例外:处理与oracle系统错误无关的错误
如何自定义一个例外?
在声明部分定义
例外名 exception,例如:myexc exception;
这样就定义了一个例外。
例外需要使用条件判断语句进行判断,才能知道是否满足抛出例外的条件。
抛出语句是:raise myexc;
然后在后面声明exception when exc then xxxxx;
举例:
create procedure xxx() is
myexc exception;
begin
xxx
if sql%notfound then raise exc;
else null;
end if;
exception when exc then xxx;
end;

四大游标参数

一般只要进行Dml语句,即增删改查,那么就会语句的属性值就会保存在四个游标中,这四个游标分别是:
sql%found,当增删改查都正确执行时,即增加语句执行,有一行被改变,删除语句执行,有一行被改变,etc,它得罪值是true,反之,值为false
sql%notfound,它的值相反。
sql%rowcount,可以理解为返回元组行数,增删改返回值都是0,只有select会不止为0.
sql%isopen,只有上面提到的那种显示的声明游标打开之后才会为true,隐式游标只有上面三个参数。默认的sql%isopen为false,所以并不怎么使用,因为值固定,无意义。

视图

1.视图是由表产生的,是虚拟的,
2.视图不能创建索引。
3.视图增加效率
4.提高安全性
下面来创建视图
create (or replace)view viewname is 
select * from emp where salry>10000;

使用视图与表操作是一样的,也可以进行增删改查(crud);

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
实际项目实践过程中,经常会遇到一些形如“ORA-XXXX”的Oracle错误提示,此时,我们便会打开百度、Google,搜索相关技术解决方案。本系列博文的目的就在于,对常见的Oracle错误提示进行总结,不断完善。 ORA-12560 ORA-12560: TNS: protocol adapter error(SQLPLUS) ORA-27101: shared memory realm does not exist(PL/SQL Developer) (1)原因分析 在path环境变量中Oracle_
调试经验: 1. 编译包时,如果出现编译错误(PL/SQL developer中包名出现红叉),可通过以下语句查看: SELECT   name,line, POSITION, text     FROM user_errors    WHERE NAME = 'INITTEST' ORDER BY SEQUENCE; 2. 上述语句中,引号中的包名(如,INITTEST)一定要大写,否则查不到 另外,PL/SQL中的包分为包规范(包的定义)和包体(包的实现)两部分,是PL/SQL编程的基本单元。 虽然名

DB2数据库的学习及总结 - 2016-07-23 19:07:12

                                                                                                                                                   db2命令 给db2用赋权的命令 db2 grant dbadm on database to user dsp #导出数据库结构会生成一个faldb20160224.sql文件  db2look -d udep -
对于PL/SQL这种基本上面向过程的语言来讲,子程序(过程和函数)就是其核心所在了。 整个解决方案可通过组织、调用由过程和函数构成的模块来实现。 在“Divide and conquer”(分而治之)的编程世界中,子程序就是程序员手中的兵器,用的越熟练越好。 --代码 13.1 创建过程示例CREATE OR REPLACE PROCEDURE newdept ( p_deptno dept.deptno%TYPE, --部门编号 p_dname dept.dname%TYPE, --部门名称 p_loc
调试nocopy功能时,为了看到实际效果,设置了一个比较大的循环次数,结果悲剧了: 运行了近1个小时没出结果,电脑死机(任务管理器都打不开);无奈只能强行关机,开机时间又特别长,一度让我以为系统崩溃。 看来,PL/SQL developer下调试这种暴力计算的程序风险很高啊,我在Eclipse下调试Java程序时从来没碰到过这种造成电脑死机的情况。 不过,这章的内容实用性很强,死机也值了(有点心疼电脑了 ) -代码13.11 使用%Type定义形式参数CREATE OR REPLACE PROCEDURE
Less - 54 Challenge-1 Test Sourse Code Solution Less - 55 Challenge-2 Test Sourse Code Solution Less - 56 Challenge-3 Test Sourse Code Solution Less - 57 Challenge-4 Test Sourse Code Solution Less - 58 Challenge-5 Test Sourse Code Solution Less - 59 Chall

CentOS7安装配置hadoop2.7.2 - 2016-07-23 18:07:08

linux 虚拟机配置 系统配置: 虚拟机:一个master(Master.Hadoop),两个slave(Slave1.Hadoop, Slave2.Hadoop) 网络设置:我使用的是桥接的方式 内存:每个虚拟机配置4G内存 分区:手动配置 软件选择:最小安装,注意选择开发工具 用户设置:密码都设置为:hadoophadoop, 不创建任何用户,操作时使用root直接进行 安装完后各个虚拟机的ip配置(参考用) 主机 ip地址 master.hadoop 192.168.200.30 slave1.h
最近写的项目中有用到数据库,写了不少蛋疼的sql语句,每次都是好几行代码,而且每次都是重复的没有一点技术含量的代码,虽然也有不少基于sqlite的封装,不过用起来还是感觉不够面向对象! 为了不再写重复的代码,花了几天时间,基于SQLite3简单封装了下,实现了一行代码解决增删改查等常用的功能!并没有太过高深的知识,主要用了runtime和KVC: 首先我们创建个大家都熟悉的Person类,并声明两个属性,下面将以类此展开分析 @interface Person : NSObject @property (

MySQL原来也有内存数据库 - 2016-07-23 18:07:20

以前只知道MySQL可以做分库分表,支持多种数据库引擎。这次听了来自台湾的MySQL专家Ivan Tu的讲座,有两点印象深刻,一是MySQL具有丰富的高可用方案,二是MySQL也有内存数据库,即MySQL Cluster CGE。Ivan形象的将内存数据库比喻成浅浅的盘子,而传统的MySQL磁盘存储方式比喻成深深的瓶子。 另外,也探讨了MySQL作为开源数据库,和Oracle数据库的关系。MySQL并行发展企业版和社区版,企业版增加了很多企业级的特性,如安全,备份和恢复,监控,以及CGE(电信运营商版)。
CleverCode研究了一下,怎么kill掉慢的sql语句。 1 单个kill mysql show processlist; mysql kill 251; #批量kill 1)查找Lockd语句 mysql -uroot -p123456 -h 192.1.20.101 -e "show processlist" | grep -i 'Locked' locked_log.txt 2)组合kill for line in `cat locked_log.txt | awk '{print $1}'`