Oracle使用可视化界面(PL/SQL)操作

目录

 

 

1.PLSQL编程基础

1.1 PL/SQL简介

1.2 PL/SQL的优点

1.3 PL/SQL体系结构

1.4 PL/SQL编程结构

1.4.1 变量和常量

1.4.2 赋值

1.4.3 输入与输出

1.4.4 拼接

1.4.5 注释

1.5 数据类型

1.5.1 常用标量类型

1.5.2 LOB类型

1.5.3 属性类型

1.6 PL/SQL控制语句

1.6.1  IF语句

1.6.2 CASE语句

1.6.3 LOOP语句

1.6.4 WHILE语句

1.6.5 FOR语句

1.7 PL/SQL异常处理

1.7.1 预定义异常

1.7.2 自定义异常

1.7.3  RAISE_APPLICATION_ERROR()函数


 

1.PLSQL编程基础

1.1 PL/SQL简介

PL/SQL (Procedural Language)是过程语言,与结构化查询语言(SQL)结合而成的编程语言,是对SQL的扩展。它支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构,可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑,与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性。

简单的说:PL/SQL是PL+SQL的组合(即过程语言+结构化查询语言),是SQL的扩充,SQL能做的,PL/SQL绝大多数都能做。。

1.2 PL/SQL的优点

1.支持SQL:数据操纵命令,事务控制命令,游标控制,SQL函数和SQL运算符;
2.支持面向对象编程;
3.可移植性,可运行在任何操作系统上;
4.经过编译执行,性能佳;
5.与SQL紧密集成,简化数据处理,支持SQL数据类型,支持NULL值,支持%type和%rowtype属性类型(oracle中最有意思的);
6.安全性

1.3 PL/SQL体系结构

1.4 PL/SQL编程结构

1.4.1 变量和常量

声明变量:
变量名 类型;
icode VARCHAR2(6);
示例:
DECLARE
/* 声明变量,注意以;号结束 */
	icode VARCHAR2(6);
	p_catg VARCHAR2(20);
	p_rate NUMBER;
	c_rate CONSTANT NUMBER := 0.10;
BEGIN
	...
	icode := 'i205';		--为变量赋值
	SELECT p_category, itemrate * c_rate
	INTO  p_catg, p_rate FROM itemfile 
WHERE itemcode = icode;
	...
END;

1.4.2 赋值

(1)使用 := 赋值
c_rate NUMBER := 0.10;

(2)使用SELECT INTO 语句赋值
SELECT 列1, 列2…… INTO 变量1,变量2…… FROM 表 WHERE 条件

1.4.3 输入与输出

输入:符‘&’
DECLARE
sid NUMBER(5);
BEGIN
	sid:=&请输入;
DBMS_OUTPUT.PUT_LINE(sid);
END;
提示:
sid:=&请输入;		--表示录入的是NUMBER整型
sid:='&请输入';	 --表示录入的是VARCHAR2字符串类型。

1.4.4 拼接

拼接符号:||
DBMS_OUTPUT.PUT_LINE('Hello'||'     '||'Word!');

1.4.5 注释

-- 行注释
/*  */多行注释

1.5 数据类型

1.5.1 常用标量类型

字符	CHAR、VARCHAR2、NVARCHAR2、LONG
数字	NUMBER、DECIAML、FLOAT、INTEGER
日期时间	DATE、TIMESTAMP
布尔型	BOOLEAN(用于存TRUE、FLASE、NULL)

1.5.2 LOB类型

用于存储大文本、图片、视频、声音等非结构化数据。最大可存储4GB数据。
BLOB	存大型二进制对象
CLOB	存大型字符数据
NCLOB	存大型UNICODE字符
BFILE	存大型二进制对象文件

1.5.3 属性类型

%TYPE 列类型

提供某个变量或某个列的数据类型。

示例1:
在emp表中求7369的入职日期(不知道入职日期这列是什么数据类型)
DECLARE
sid NUMBER := 7369;
shiredate emp.hiredate%TYPE;  --声明个变量,它的类型与emp表中hiredate的列同一种类型
BEGIN
	SELECT hiredate INTO shiredate FROM emp WHERE empno=sid;
	DBMS_OUTPUT.PUT_LINE(shiredate);
END;

示例2:
求7369的姓名、工作信息(在未知列类型的情况下)
DECLARE
sid NUMBER;
sname emp.ename%TYPE;
sjob emp.job%TYPE;
BEGIN
	sid:=7369;
	SELECT ename, job INTO sname, sjob FROM emp WHERE empno=sid;
	DBMS_OUTPUT.PUT_LINE(sname||'   '||sjob);
END;

%ROWTYPE 行类型

若表中不知类型的列太多,使用%TYPE必定会写很多代码。这里为简便,我们使用%ROWTYPE.
即某变量的类型是某表行的类型(包括多列),即称对象名。

语法:
对象变量 表名%ROWTYPE;

示例:
查询7369的所有信息(在未知所有列类型的情况下)
DECLARE
sid number;
er emp%ROWTYPE;		--er可以看成是对象名
BEGIN
	sid:=7369;
	SELECT * INTO er FROM emp WHERE empno=sid;
	DBMS_OUTPUT.PUT_LINE(er.ename||'   '||er.job);	--输出er对象中的ename
END;

1.6 PL/SQL控制语句

PL/SQL支持的流程控制语句:
条件控制:IF语句、CASE语句
循环控制:LOOP循环、WHILE循环、FOR循环
顺序控制:GOTO语句、NULL语句(什么也不做的语句)

1.6.1  IF语句

CREATE OR REPLACE FUNCTION FU_EMP
RETURN NUMBER
AS
DECLARE
  SLAS NUMBER;
BEGIN
SELECT SAL INTO SALS FROM EMP WHERE EMPNO = 7369;
  IF SALS>1000 THEN
RETURN SALS;
ELSIF SALS<1000 THEN
RETURN SALS;
END IF;
END;

1.6.2 CASE语句

DECLARE 

BEGIN
 UPDATE EMP SAL(
 CASE
  WHEN SAL BETWEEN 500 AND 600 THEN SAL+100
  WHEN SAL BETWEEN 700 AND 800 THEN SAL+200
  ELSE SAL+300
 END 
) WHERE EMPNO = 7369;
END;

1.6.3 LOOP语句

- - 求从1到100之和
DECLARE 
 NUMS NUMBER :=0;
 N NUMBER;
BEGIN
LOOP
  NUMS := NUMS+1;
N:=N+NUMS;
EXIT WHEN NUMS=100;
END LOOP;
DBMS_OUTPUT.PUT_LINE(N);  --拿到外面输出 免得得到很多个答案
END;

1.6.4 WHILE语句

- - 求从1到100之和
DECLARE
NUMS NUMBER :=0;
  N NUMBER;
BEGIN
  WHILE NUMS = 100 LOOP
   NUMS := NUMS +1;
   N := NUMS +NUMS ;
END LOOP;
DBMS_OUTPUT.PUT_LINE(N); 
END;

1.6.5 FOR语句

- - 求从1到100之和

DECLARE
  N NUMBER;
BEGIN
  FOR I IN 1..100 LOOP
   N := N+I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(N); 
END;

1.7 PL/SQL异常处理

发生异常后,语句将停止执行,控制权转移到PL/SQL块的异常处理部分。

异常处理有两种类型:

1.7.1 预定义异常

当PL/SQL程序违反ORACLE规则或超越系统限制时隐式引发。

DECLARE
ordernum VARCHAR2(5);
BEGIN
SELECT empno INTO ordernum FROM emp;
EXCEPTION
   WHEN TOO_MANY_ROWS THEN --预定义异常,即当出现行太多异常时
    DBMS_OUTPUT.PUT_LINE ('返回多行'); --执行输出语句
END;

PL/SQL提供的预定义异常汇总:

ACCESS_INTO_NULL

在未初化对象时出现

CASE_NOT_FOUND

在CASE语句中的选项与用户输入的数据不匹配时出现

 COLLECTION_IS_NULL

在给尚未初始化的表或数组赋值时出现

DUP_VAL_ON_INDEX

在用户试图将重复的值存在使用唯一索引的数据库列中时出现

INVALID_CURSOR

在执行非法游标运算(如打开一个尚未打开的游标)时出现

INVALID_NUMBER

在将字符串转换为数字时出现

LOGIN_DENIED

在输入的用户名或密码无效时出现

NO_DATA_FOUND

在表中不存在的请求的行时出现,此外,当程序引用已经删除的元素时

STORAGE_ERROR

在内存损坏或PL/SQL耗尽内存时出现

TOO_MANY_ROWS

在执行SELECT INTO语句后返回多行时出现

VALUE_ERROR

在产生大小限制错误时出现

ZERO_DIVIDE

以零作除数时出现

OTHERS

针对所有异常

CURSOR_ALREADY_OPEN

在用户试图打开已经打开的游标时出现

1.7.2 自定义异常

用户可以在PL/SQL块的声明部分定义异常,自定义的异常通过RAISE语句显式引发。
DECLARE 
	invalidCATEGORY  EXCEPTION;		--声明异常对象
	category  VARCHAR2(10);
BEGIN
	category := '&Category';
	IF category NOT IN ('附件','顶盖','备件')  THEN		--如输入的不是附件或顶盖或备件
    	RAISE invalidCATEGORY;				--抛出invalidCATEGORY异常
  	ELSE
    	DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  	END IF;

EXCEPTION
  	WHEN  invalidCATEGORY  THEN			--当抛出invalidCATEGORY异常时,执行输出
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');	
END;

--自己写的
DECLARE 
  INPUTE VARCHAR2(20);
  EXCE EXCEPTION;
BEGIN
  INPUTE := '&请输入';
  IF INPUTE  IN('A','B','C') THEN
    DBMS_OUTPUT.PUT_LINE('你输入的是:'||INPUTE);
    ELSE
      RAISE  EXCE;
  END IF;

EXCEPTION WHEN EXCE THEN 
  DBMS_OUTPUT.PUT_LINE('你输入的东西本地没有');
END;
带多个异常体
EXCEPTION 
	WHEN invalidCATEGORY1 THEN
	DBMS_OUTPUT.PUT_LINE('错误信息1');
	WHEN too_many_rows THEN
	DBMS_OUTPUT.PUT_LINE('行太多');
	……

1.7.3  RAISE_APPLICATION_ERROR()函数

作用:把异常信息返回给调用的客户端。

基本语法:
RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);

示例:
RAISE_APPLICATION_ERROR(-20001,'错误提示信息');

解释:
-20001是错误代码,分配给用户自定义的错误代码范围在-20000~-20999,共一千个。
‘错误提示信息’是VARCHAR2类型,最长为2000字节。
实例
·  create or replace trigger t_temp_age_check  
·  before insert on temp_age  
·    for each row  
·      begin  
·        if :new.age < 18  
·          then  
·            raise_application_error(-20001,'age must at least 18 years old');  
·          end if;  
·      end;  

1.-- 客户端程序  
2.declare  
3.  no_baby_allowed exception;  
4.  pragma exception_init(no_baby_allowed,-20001);  
5.  begin  
6.    insert into temp_age(age_id,age) values(1,20);  
7.    insert into temp_age(age_id,age) values(2,17);  
8.    insert into temp_age(age_id,age) values(3,18);  
9.    exception  
10.      when no_baby_allowed  
11.        then  
12.          dbms_output.put_line(sqlerrm);  
13.  end;  
客户端程序执行输出结果: 
ORA-20001: age must at least 18 years old
ORA-06512: 在 "LCAM_DEVELOP.T_TEMP_AGE_CHECK", line 4
ORA-04088: 触发器 'LCAM_DEVELOP.T_TEMP_AGE_CHECK' 执行过程中出错

 

没有更多推荐了,返回首页