Oracle速查语法:PL/SQL
来源:互联网 发布:我的世界pe创世神js 编辑:程序博客网 时间:2024/05/22 14:38
目录
- 基本结构
- 游标
- PL/SQL表
- PL/SQL 记录类型
- REF 游标
- 过程
- 函数
- 数据包
- 触发器
- 自定义对象
- 其他
.
PL/SQL 结构
DECLARE --声明部分 声明语句BEGIN --执行部分 执行语句EXCEPTION --异常处理部分 执行语句END;
变量声明
<变量名> 类型[:=初始值];
特殊类型 字段%type
示例: name emp.ename%type –表示name的类型和emp.ename的类型相同
表 %rowtype
示例:
test emp%rowtype –表示test的类型为emp表的行类型;也有 .empno; .ename; .sal ;等属性
常量声明
<变量名> CONSTANT 类型:=初始值; 示例: pi constant number(5,3):=3.14;
全局变量声明
VARIABLE <变量名> 类型;
示例: VARIABLE num number;
使用全局变量
:<变量名>
示例:
:num:=100;
i=:num;
查看全局变量的值
print <变量名>
示例: print num;
赋值运算符: :=
示例: num := 100;
使用SELECT <列名> INTO <变量名> FROM <表名> WHERE <条件>
注意select into 语句的返回结果只能为一行;
示例:test emp%rowtype;
select * into test from emp where empno=7788;
用户交互输入
<变量>:=’&变量’
示例:
num:=#
注意oracle的用户交互输入是先接受用户输入的所有值后在执行语句;
所以不能使用循环进行用户交互输入;
条件控制语句IF <条件1> THEN 语句[ELSIF <条件2> THEN 语句 . . .ELSIF <条件n> THEN 语句][ELSE 语句]END IF;
循环控制语句
1.LOOP
LOOP 语句; EXIT WHEN <条件>END LOOP;
2.WHILE LOOP
WHILE <条件>LOOP 语句;END LOOP;
3.FOR
FOR <循环变量> IN 下限..上限LOOP 语句;END LOOP;
NULL 语句null;表示没有操作;
注释使用
单行注释: –
多行注释:/* …….
……………*/
异常处理
EXCEPTION WHEN <异常类型> THEN 语句; WHEN OTHERS THEN 语句;END;
.
显示游标
定义:
CURSOR <游标名> IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];
[FOR UPDATE | FOR UPDATE OF 字段] –给游标加锁,既是在程序中有”UPDATE”,”INSERT”,”DELETE”语句对数据库操作时。
游标自动给指定的表或者字段加锁,防止同时有别的程序对指定的表或字段进行”UPDATE”,”INSERT”,”DELETE”操作.
在使用”DELETE”,”UPDATE”后还可以在程序中使用CURRENT OF <游标名> 子句引用当前行.
操作:
OPEN <游标名> --打开游标 FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,; 或者 FETCH <游标名> INTO 行对象; --取出游标当前位置的值 CLOSE <游标名> --关闭游标属性: %NOTFOUND --如果FETCH语句失败,则该属性为"TRUE",否则为"FALSE"; %FOUND --如果FETCH语句成果,则该属性为"TRUE",否则为"FALSE"; %ROWCOUNT --返回游标当前行的行数; %ISOPEN --如果游标是开的则返回"TRUE",否则为"FALSE";
使用:
LOOP循环 示例:DECLARE cursor c_1 is select * from emp; --定义游标 r c_1%rowtype; --定义一个行对象,用于获得游标的值BEGIN if c_1%isopen then CLOSE c_1; end if; OPEN c_1; --判断游标是否打开.如果开了将其关闭,然后在打开 dbms_output.put_line('行号 姓名 薪水'); LOOP FETCH c_1 INTO r; --取值 EXIT WHEN c_1%NOTFOUND; --如果游标没有取到值,退出循环. dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. END LOOP;END;
FOR循环
示例:
DECLARE cursor c_1 is select ename,sal from emp; --定义游标 BEGIN dbms_output.put_line('行号 姓名 薪水'); FOR i IN c_1 --for循环中的循环变量i为c_1%rowtype类型; LOOP dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示. END LOOP;END;
for循环使用游标是在循环开始前自动打开游标,并且自动取值到循环结束后,自动关闭游标.
游标加锁示例:
DECLARE cursor c_1 is select ename,sal from emp for update of sal; --定义游标对emp表的sal字段加锁. BEGIN dbms_output.put_line('行号 姓名 薪水'); FOR i IN c_1 --for循环中的循环变量i为c_1%rowtype类型; LOOP UPDATE EMP set sal=sal+100 WHERE CURRENT OF c_1; --表示对当前行的sal进行跟新. END LOOP; FOR i IN c_1 LOOP dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示. END LOOP;END;
代参数的游标
定义:
CURSOR <游标名>(参数列表) IS
DECLARE cursor c_1(name emp.ename%type) is select ename,sal from emp where ename=name; --定义游标 BEGIN dbms_output.put_line('行号 姓名 薪水'); FOR i IN c_1('&name') --for循环中的循环变量i为c_1%rowtype类型; LOOP dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示. END LOOP;END;
隐试游标
隐试游标游标是系统自动生成的。每执行一个DML语句就会产生一个隐试游标,起名字为SQL;
隐试游标不能进行”OPEN” ,”CLOSE”,”FETCH”这些操作;
属性:
%NOTFOUND --如果DML语句没有影响到任何一行时,则该属性为"TRUE",否则为"FALSE"; %FOUND --如果DML语句影响到一行或一行以上时,则该属性为"TRUE",否则为"FALSE"; %ROWCOUNT --返回游标当最后一行的行数;
个人认为隐试游标的作用是判断一个DML语句;
示例:
BEGIN DELETE FROM EMP WHERE empno=&a; IF SQL%NOTFOUND THEN dbms_output.put_line('empno不存在'); END IF; IF SQL%ROWCOUNT>0 THEN dbms_output.put_line('删除成功'); END IF;END;
a
PL/SQL表
pl/sql表只有两列,其中第一列为序号列为INTEGER类型,第二列为用户自定义列.
定义:
TYPE <类型名> IS TABLE OF <列的类型> [NOT NULL] INDEX BY BINARY_INTEGER;
<列的类型>可以为Oracle的数据类行以及用户自定义类型;
属性方法:
.count --返回pl/sql表的总行数.delect --删除pl/sql表的所有内容.delect(行数) --删除pl/sql表的指定的行.delct(开始行,结束行) --删除pl/sql表的多行.first --返回表的第一个INDEX;.next(行数) --这个行数的下一条的INDEX;.last --返回表的最后一个INDEX;
使用
示例:
DECLARE TYPE mytable IS TABLE OF VARCHAR2(20) index by binary_integer; --定义一个名为mytable的PL/sql表类型; cursor c_1 is select ename from emp; n number:=1; tab_1 mytable; --为mytable类型实例化一个tab_1对象;BEGIN for i in c_1 loop tab_1(n):=i.ename; --将得到的值输入pl/sql表 n:=n+1; end loop; n:=1; tab_1.delete(&要删除的行数); --删除pl/sql表的指定行 for i in tab_1.first..tab_1.count loop dbms_output.put_line(n||' '||tab_1(n)); --打印pl/sql表的内容 n:=tab_1.next(n); end loop;EXCEPTION WHEN NO_DATA_FOUND THEN --由于删除了一行,会发生异常,下面语句可以接着删除的行后显示 for i in n..tab_1.count+1 loop dbms_output.put_line(n||' '||tab_1(n)); n:=tab_1.next(n); end loop; END;
.
PL/SQL记录
pl/sql表只有一行,但是有多列。
定义:
TYPE <类型名> IS RECORD <列名1 类型1,列名2 类型2,...列名n 类型n,> [NOT NULL]
<列的类型>可以为Oracle的数据类行以及用户自定义类型;可以是记录类型的嵌套
使用
示例:
DECLARE TYPE myrecord IS RECORD(id emp.empno%type, name emp.ename%type,sal emp.sal%type); --定义一个名为myrecoed的PL/sql记录类型; rec_1 myrecord; --为myrecord类型实例化一个rec_1对象;BEGIN select empno,ename,sal into rec_1.id,rec_1.name,rec_1.sal from emp where empno=7788; --将得到的值输入pl/sql记录 dbms_output.put_line(rec_1.id||' '||rec_1.name||' '||rec_1.sal); --打印pl/sql记录的内容END;
结合使用PL/SQL表和PL/SQL记录
示例:
DECLARE CURSOR c_1 is select empno,ename,job,sal from emp; TYPE myrecord IS RECORD(empno emp.empno%type,ename emp.ename%type,job emp.job%type,sal emp.sal%type); --定义一个名为myrecoed的PL/sql记录类型; TYPE mytable IS TABLE OF myrecord index by binary_integer; --定义一个名为mytable的PL/sql表类型;字段类型为PL/sql记录类型; n number:=1; tab_1 mytable; --为mytable类型实例化一个tab_1对象;BEGIN --赋值 for i in c_1 loop tab_1(n).empno:=i.empno; tab_1(n).ename:=i.ename; tab_1(n).job:=i.job; tab_1(n).sal:=i.sal; n:=n+1; end loop; n:=1; --输出 for i in n..tab_1.count loop dbms_output.put_line(i||' '||tab_1(i).empno ||' '||tab_1(i).ename||' '||tab_1(i).job||' '||tab_1(i).sal); end loop;END;
.
强型REF游标
定义:TYPE <游标名> IS REF CURSOR RETURN<返回类型>;
操作:
OPEN <游标名> For <select 语句> --打开游标 FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,; 或者 FETCH <游标名> INTO 行对象; --取出游标当前位置的值 CLOSE <游标名> --关闭游标属性: %NOTFOUND --如果FETCH语句失败,则该属性为"TRUE",否则为"FALSE"; %FOUND --如果FETCH语句成果,则该属性为"TRUE",否则为"FALSE"; %ROWCOUNT --返回游标当前行的行数; %ISOPEN --如果游标是开的则返回"TRUE",否则为"FALSE";
使用:
示例:
DECLARE type c_type is ref cursor return emp%rowtype; --定义游标 c_1 c_type; --实例化这个游标类型 r emp%rowtype;BEGIN dbms_output.put_line('行号 姓名 薪水'); open c_1 for select * from emp; loop fetch c_1 into r; exit when c_1%notfound; dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. END LOOP;close c_1;END;
弱型REF游标
定义:TYPE <游标名> IS REF CURSOR;
OPEN <游标名> For <select 语句> --打开游标 FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,; 或者 FETCH <游标名> INTO 行对象; --取出游标当前位置的值 CLOSE <游标名> --关闭游标属性: %NOTFOUND --如果FETCH语句失败,则该属性为"TRUE",否则为"FALSE"; %FOUND --如果FETCH语句成果,则该属性为"TRUE",否则为"FALSE"; %ROWCOUNT --返回游标当前行的行数; %ISOPEN --如果游标是开的则返回"TRUE",否则为"FALSE";
示例:
set autoprint on;var c_1 refcursor;DECLARE n number;BEGIN n:=&请输入; if n=1 then open :c_1 for select * from emp; else open :c_1 for select * from dept; end if;END;
.
过程
定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS
[局部变量声明]
BEGIN
可执行语句
EXCEPTION
异常处理语句
END [<过程名>];
变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
操作以有的过程:在PL/SQL块中直接使用过程名;在程序外使用execute <过程名>[(参数列表)]
使用:
示例:
创建过程:
create or replace procedure p_1(n in out number) is r emp%rowtype;BEGIN dbms_output.put_line('姓名 薪水'); select * into r from emp where empno=n; dbms_output.put_line(r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. n:=r.sal; END;使用过程:declare n number;begin n:=&请输入员工号; p_1(n); dbms_output.put_line('n的值为 '||n);end;
删除过程:
DROP PROCEDURE <过程名>;
.
函数
定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS
[局部变量声明]
BEGIN
可执行语句
EXCEPTION
异常处理语句
END [<过程名>];
变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
使用:
示例:
创建函数:
create or replace function f_1(n number) return number is r emp%rowtype;BEGIN dbms_output.put_line('姓名 薪水'); select * into r from emp where empno=n; dbms_output.put_line(r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. return r.sal;END;使用函数:declare n number; m number;begin n:=&请输入员工号; m:=f_1(n); dbms_output.put_line('m的值为 '||m);end;
删除函数:
DROP FUNCTION <函数名>;
.
数据包
定义: 定义包的规范
CREATE [OR REPLACE] PACKAGE <数据包名> AS
–公共类型和对象声明
–子程序说明
END;
定义包的主体
CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS
–公共类型和对象声明
–子程序主体
BEGIN
-初始化语句
END;
使用:
示例:
创建数据包规范:
create or replace package pack_1 as n number; procedure p_1; FUNCTION f_1 RETURN number;end;
创建数据包主体:
create or replace package body pack_1 as procedure p_1 is r emp%rowtype; begin select * into r from emp where empno=7788; dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal); end; FUNCTION f_1 RETURN number is r emp%rowtype; begin select * into r from emp where empno=7788; return r.sal; end;end;
使用包:
declare n number;begin n:=&请输入员工号; pack_1.n:=n; pack_1.p_1; n:=pack_1.f_1; dbms_output.put_line('薪水为 '||n);end;
在包中使用REF游标
示例:
创建数据包规范:
create or replace package pack_2 as TYPE c_type is REF CURSOR; --建立一个ref游标类型 PROCEDURE p_1(c1 in out c_type); --过程的参数为ref游标类型;end;
创建数据包主体:
create or replace package body pack_2 as PROCEDURE p_1(c1 in out c_type) is begin open c1 for select * from emp; end;end;
使用包:
var c_1 refcursor;set autoprint on;execute pack_2.p_1(:c_1);
删除包:
DROP PACKAGE <包名>;
.
触发器
创建触发器:
CREATE [OR REPLACE] TRIGGER <触发器名> BEFORE|AFTER INSERT|DELETE|UPDATE [OF <列名>] ON <表名> [FOR EACH ROW] WHEN (<条件>) <pl/sql块>
关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发; 关键字"FOR EACH ROW"指定触发器每行触发一次. 关键字"OF <列名>" 不写表示对整个表的所有列. WHEN (<条件>)表达式的值必须为"TRUE".
特殊变量:
:new –为一个引用最新的列值;
:old –为一个引用以前的列值; 这些变量只有在使用了关键字 “FOR EACH ROW”时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;
使用RAISE_APPLICATION_ERROR
语法:RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);
抛出用户自定义错误.
如果参数为’TRUE’,则错误放在先前的堆栈上.
INSTEAD OF 触发器
INSTEAD OF 触发器主要针对视图(VIEW)将触发的dml语句替换成为触发器中的执行语句,而不执行dml语句.禁用某个触发器
ALTER TRIGGER <触发器名> DISABLE
重新启用触发器
ALTER TRIGGER <触发器名> ENABLE
禁用所有触发器
ALTER TRIGGER <触发器名> DISABLE ALL TRIGGERS
启用所有触发器
ALTER TRIGGER <触发器名> ENABLE ALL TRIGGERS
删除触发器
DROP TRIGGER <触发器名>
.
自定义对象
创建对象:
CREATE [OR REPLACE] TYPE <对象名> AS OBJECT(
属性1 类型
属性2 类型
方法1的规范(MEMBER PROCEDURE <过程名>方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型)
.
PRAGMA RESTRIC_REFERENCES(<方法名>,WNDS/RNDS/WNPS/RNPS);
关键字”PRAGMA RESTRIC_REFERENCES”通知ORACLE函数按以下模式之一操作;
WNDS-不能写入数据库状态;
RNDS-不能读出数据库状态;
WNPS-不能写入包状态;
RNDS-不能读出包状态;
创建对象主体:
CREATE [OR REPLACE] TYPE body <对象名> AS 方法1的规范(MEMBER PROCEDURE <过程名> is <PL/SQL块> 方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型 is <PL/SQL块> END;
使用MAP方法和ORDER方法
用于对自定义类型排序。每个类型只有一个MAP或ORDER方法。 格式:MAP MEMBER FUNCTION <函数名> RETURN 类型 ORDER MEMBER FUNCTION <函数名> RETURN NUMBER
创建对象表
CREATE TABLE <表名> OF <对象类型>
示例:
- 创建name 类型
create or replace type name_type as object( f_name varchar2(20), l_name varchar2(20), map member function name_map return varchar2); create or replace type body name_type as map member function name_map return varchar2 is --对f_name和l_name排序 begin return f_name||l_name; end; end;
2 创建address 类型
create or replace type address_type as object ( city varchar2(20), street varchar2(20), zip number, order member function address_order(other address_type) return number); create or replace type body address_type as order member function address_order(other address_type) return number is --对zip排序 begin return self.zip-other.zip; end; end;
3 创建stu对象
create or replace type stu_type as object ( stu_id number(5), stu_name name_type, stu_addr address_type, age number(3), birth date, map member function stu_map return number, member procedure update_age); create or replace type body stu_type as map member function stu_map return number is --对stu_id排序 begin return stu_id; end; member procedure update_age is --求年龄用现在时间-birth begin update student set age=to_char(sysdate,'yyyy')-to_char(birth,'yyyy') where stu_id=self.stu_id; end; end;
- 创建对象表
`create table student of stu_type(primary key(stu_id));`
5.向对象表插值
insert into student values(1,name_type('关','羽'),address_type('武汉','成都路',43000), null,sysdate-365*20);
6.使用对象的方法
delcare aa stu_type; begin select value(s) into aa from student s where stu_id=1; --value()将对象表的每一行转成行对象括号中必须为表的别名 aa.update_age(); end;
7.select stu_id,s.stu_name.f_name,s.stu_name.l_name from student s; –查看类型的值
8.select ref(s) from student s ; –ref()求出行对象的OID,括号中必须为表的别名;deref()将oid变成行队像;
.
其他
1.在PL/SQL中使用DDL
将sql语句赋给一个varchar2变量,在用execute immediate 这个varchar2变量即可;
示例:
declare str varchar2(200); begin str:='create table test(id number,name varchar2(20))'; --创建表 execute immediate str; str:='insert into test values(3,''c'')'; --向表里插数据 execute immediate str; end;
但是要队这个表插入数据也必须使用execute immediate 字符变量
2.判断表是否存在;
示例:
declare n tab.tname%type; begin select tname into n from tab where tname='&请输入表名'; dbms_output.put_line('此表以存在'); exception when no_data_found then dbms_output.put_line('还没有此表'); end;
2.查看以有的过程;
示例:
select object_name,object_type,status from user_objects where object_type='PROCEDURE';
结束
- Oracle速查语法:PL/SQL
- Oracle PL/SQL语法
- Oracle PL/SQL 语法
- PL/SQL基本语法(ORACLE)
- Oracle:PL/SQL基本语法
- Oracle的PL SQL语法
- oracle--PL/SQL基础语法
- oracle-PL/SQL基本语法
- Oracle PL/SQL语法格式
- ORACLE PL/SQL语法总结
- Oracle速查语法:基本SQL语言
- Oracle PL/SQL入门语法点
- 转:Oracle PL/SQL入门语法点
- Oracle PL/SQL入门语法点
- Oracle PL/SQL入门语法点
- Oracle PL/SQL入门语法点
- Oracle PL/SQL入门语法点
- Oracle PL/SQL入门语法点
- CentOS 7.2 部署邮件服务器(Postfix)
- 解释什么是字对齐什么是半字对齐
- 计算一个班的平均分
- 音频噪声抑制(5):含辅助观测数据的LMS噪声抑制
- 161104
- Oracle速查语法:PL/SQL
- Java学习笔记-继承
- 17. Letter Combinations of a Phone Number
- SharedPreferences存取特殊数据
- 单片机的最简单的几个编程
- C#_MouseDown事件没反应
- 1.模板方法 Template Method
- 【leetcode】83. Remove Duplicates from Sorted List
- Android 视图拖拽辅助类 ViewDragHelper 的使用