【Oracle】第14天 PL/SQL编程基础

语法框架

DECLARE 
	声明部分
BEGIN
	代码块
EXCEPTION
	异常处理
END;

变量与常量

变量语法：

变量名 数据类型[:=初始值]

补充：标识符命名规范
1）对象名可以有数字和特殊字符
2)长度不能超过30个字符
3）不能用数字开头
4）尽量不用关键字作为标识符

输出语句

DBMS_OUTPUT.PUT(字符串)
DBMS_OUTPUT.PUT_LINE(字符串)--输出后换行
DBMS_OUTPUT.NEW_LINE()--换行

赋值符号 “：=”

举例：

DECLARE 
	V VARCHAR2(30):='Oracle';
BEGIN
	DBMS_OUTPUT.PUT_LINE(V);
	V:='PL/SQL';
END;

→选中执行→output标签

常量语法

不可修改，在声明时必须包含常量名

常量名 CONSTANT 数据类型[:=常量值]

举例：

DECLARE 
	V CONSTANT VARCHAR2(30):='Oracle';
BEGIN
	DBMS_OUTPUT.PUT_LINE(V);
END;

输入语句：&

SELECT * FROM EMP WHERE DEPTNO =&部门编号;

→
执行之后出来对话框让你填写，你填写10号或20号之后就可以随你填写的内容条件执行（sqlplus同样适用）

• 输入数值：&变量名
• 输入字符串：‘&变量名’

PL/SQL的COMMAND WINDOW 与SQLPLUS一样

举例：

DECLARE 
	V VARCHAR2(30):='&变量值';
BEGIN
	DBMS_OUTPUT.PUT_LINE(V);
END;

SELECT INTO 语句

SELECT 列名,列名... INTO 变量,变量... FROM 表名 ;

注意：
SELECT INTO 语句每次只能查询一条数据，不能多不能少

数据类型

基础类型

NUMBER(38,1)

INTEGER

PLS_INTEGER

BINARY_INTEGER

FLOAT:浮点型（小数类型）

数据精度要求特别高时，改用VARCHAR2

VARCHAR2(4000) 最大4000个英文字符

CHAR(2000)

LONG 可以存放2g的内容，但是不建议使用

CLOB 存放大文本，当字符串使用
BLOB 存放大文件

DATE和TIMESTAMP区别在于精度

SELECT SYSDATE FROM DUAL;
SELECT LOCALIMESTAMP FROM DUAL;

BOOLEAN: 只有三个值：
TRUE/FALSE/NULL

复杂类型

记录类型变量RECORD

有多个属性，一个属性对应一个值，可以存放多个值

TYPE 类型名称 IS RECORD(
	属性名 数据类型,
	属性名 数据类型,
...)

举例：

DECLARE 
	TYPE  MTYPE IS RECORD(
	NAME VARRCHAR2(30),
	JOB VARCHAR2(50),
	SAL NUMBER(5)
	);
BEGIN;
END;

注意：记录类型变量不能直接打印，应该取其中一个属性打印

举例：查询7369的姓名，职位和工资

法一：

DECLARE 
	V_ENAME VARCHAR2(20);
	V_JOB VARCHAR2(30);
	V-SAL NUMBER(5);
	
BEGIN
	SELECT ENAME,JOB,SAL INTO V_NAME,V_JOB,V_SAL FROM EMP WHERE EMPNO=7369;
	DBMS_OUTPUT.PUT_LINE (V_ENAME);
	DBMS_OUTPUT.PUT_LINE(V_JOB);
	DBMS_OUTPUT.PUT_LINE(V_SAL);
END;

法二：
使用记录类型变量完成以上要求：

DECLARE 
	TYPE MTYPE IS RECORD (
	V_ENAME VARCHAR2(20);
	V_JOB VARCHAR2(30);
	V_SAL NUMBER(5);
	);
BEGIN
	SELECT ENAME,JOB,SAL INTO V FROM EMP WHERE EMPNO=7369;
	DBMS_OUTPUT.PUT_LINE (V.ENAME);
	DBMS_OUTPUT.PUT_LINE(V.JOB);
	DBMS_OUTPUT.PUT_LINE(V.SAL);
END;

&TYPE类型

%TYPE类型，取其他变量的类型，作为指定变量的类型

举例：
EMP.JOB%TYPE --取EMP 表的JOB列的单一的数据类型，作为一个新的类型

DECLARE
	V EMP.JOB%TYPE;
	N1 NUNBER(10) ;
	N2 N1%TYPE;
BEGIN;
END;

%ROWTYPE 类型

%ROWTYPE类型是%TYPE类型和记录类型的结合

举例：
写一个代码块，输入一个员工编号，查询并打印员工的信息

DECLARE 
	V EMP%ROWTYPE;
BEGIN
	SELECT * INTO V FROM EMP WHERE EMPNO=&ENO;
	DBMS_OUTPUT.PUT_LINE(V.ENAME||','||V.JOB||','||V.MGR||','||V.HIREDATE||','||V.SAL||','||V.COMM||','||V.DEPTNO);
END;

代码块部分详解

组成元素

1.SELECT INTO 语句
2.INSERT/UPDATE/DELETE 语句
3.EXECUTE IMMEDIATE 执行

语法：

EXECUTE IMMEDIATE SQL语句(字符串类型) [INTO 变量] [USING 值,值...]

解释：
其中INTO 子句和SELECT INTO 中的一样：用于将SELECT 查询结果保存在变量中，USING 子句给SQL语句传递参数

DECLARE 
BEGIN
	EXECUTE IMMEDIATE 'UPDATE EMP SET SAL = SAL+500';
	EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO=7369' INTO V_NAME ;
	DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

注意：
DDL语句只可以放在EXECUTE IMMEDIATE 语句中运行

举例：

DECLARE 
	V_EMPNO NUMBER(11):=7369;
	V_ENAME VARCHAR2(30):='&员工姓名';
	V_JOB VARCHAR2(20):='&职位';
	V_MGR EMP.MGR%TYPE:=&上级编号;
	V_HIREDATE EMP.HIREDATE%TYPE :=TO_DATE('&入职日期','YYYY/MM/DD');
	V_SAL EMP.SAL%TYPE:=&工资;
	V_COMM EMP.SAL%TYPE:=&佣金;
	V_DEPTNO EMP.DEPTNO%TYPE:=&部门编号;
BEGIN
	UPDATE EMP SET 	ENAME=V_ENAME,
					JOB=V_JOB,
					MGR=V_MGR,
					HIREDATE=V_HIREDATE,
					SAL=V_SAL,
					COMM=V_COMM,
					DEPTNO=V_DEPTNO
					WHERE EMPNO=V_EMPNO ;
END;

占位符：N

有:n的占位符，则必有USING 子句

删除所有的触发器：

SELECT 'DROP TRIGGER'||OBJECT_NAME||';' FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';

→
执行，再单击ALT选中多行， 再执行

举例：占位符的使用

DECLARE 
BEGIN
	EXECUTE IMMEDIATE 'DELETE FROM EMP WHERE EMPNO=:1' USING 7369;
END;

输出语句中单引号的转义

• oracle字符串中包含字符串的情况
  1.使用两个单引号表示一个单引号
  2.使用chr（）函数来获取一个单引号 chr（39）

举例：保存一个sql语句并执行

DECLARE 
	V_SQL VARCHAR2(200):='UPDATE EMP SET ENAME =''HELLO'',JOB :=''IT'',SAL=1234 WHERE EMPNO=7369;'
BEGIN 
	DBMS_OUTPUT.PUT_LINE(V_SQL);
END;

→

DECLARE 
	V_SQL VARCHAR2(200):='UPDATE EMP SET ENAME =''HELLO'',JOB :=''IT'',SAL=1234 WHERE EMPNO=7369;'
BEGIN 
	DBMS_OUTPUT.PUT_LINE(V_SQL);
	EXECUTE IMMEDIATE V_SQL;
END;

execute immediate语句和

直接执行的sql语句的区别

（1）ddl语句不能直接在plsql代码块中运行，但是可以放在 execute immediate中运行
（2）当sql语句操作的表，是使用 execute immediate动态创建的那么，只能在 execute immediate语句中执行
（3）当将表名放入变量中时，这时必须使用 execute immediate

举例：

DECLARE 
BEGIN 
	EXECUTE IMMEDIATE 'CREATE TABLE TTT3(ID NUMBER(11) ,NAME VARCHAR2(20))';
	EXECUTE IMMEDIATE 'INSERT INTO TTT3 VALUES (1,'PLSQL');';
END;

declare
   --声名一个变量变量，保存数据库中的表名
   v_tab varchar2(30):='EMP';
   
   --声名一个变量
   v_sal number(5):=500;
   --声名一个变量保存sql语句
   v_sql varchar2(200);
begin
   --update v_tab set sal=sal+v_sal; 不能运行
   v_sql:='update '||v_tab||' set sal=sal+'||v_sal;
   dbms_output.put_line(v_sql);
   execute immediate v_sql;
end;
update EMP set sal=sal+500;