PLSQL程序块中的无名块

PLsql —过程化语言

程序块
plsql是Oracle默认的核心语言
是SQL的进阶。
plsql的核心是循环 判断 变量

程序块：

无名块

没有名字，只能执行一次，不能保存
无名块的结构

DECLARE       --1.声明部分(可选)
			  --声明变量，变量类型，游标
BEGIN         --2.执行部分(核心)
			  --能直接使用dml语句
			  --能直接使用tcl语句（commit，rollback）
			  --可以使用select into 语句
			  --不能直接使用ddl语句，可以通过动态sql来实现ddl语句
			  --循环，判断
  EXCEPTION   --3.异常处理部分(可选)
END;          --4.结束部分 

变量

.
变量是用来接受可变动的值的
声明变量:

DECLARE
	变量名1 变量类型;
	变量名2 变量类型;	--程序块中，每完成一句话都要有分号
BEGIN
END;

给变量赋值：
1.在BEGIN后面用 赋值符号 := 给变量赋值
2.在DECLARE后面用赋值符号 := 给变量赋值
例题，打印输出你好CSDN
3.利用 select into 语句给变量赋值

DECLARE
	V_a varchar2(20);
	V_b varchar2(20) :='HELLO www'
BEGIN 
	V_a :='你好CSDN';
 	dbms_output.put_line(V_a);
 	dbms_output.put_line(V_b);
END;

练习：打印输出hello world

DECLARE
	V_hellow varchar2(20);
BEGIN
dbms_output.put_line(V_hellow);
END;

变量名命名规范：
1.要见名知意
2.必须以字母开头，不要用除了_以外的特殊符号

利用 select into 语句给变量赋值

DECLARE 
	变量1 变量类型;
BEGIN
	SELECT 列名1 INTO 变量1 FROM 表名 WHERE 条件
	--将SQL查询语句的结果交给变量，注意结果只能是一行结果
	--变量的个数，顺序，属性要和列一致
END;

例题：打印输出7788的员工姓名和岗位

DECLARE 
  E_name varchar2(20);
  E_job varchar2(20);
BEGIN
  SELECT ename,job
    INTO E_name,E_job
    FROM emp
    where empno = 7788;
  dbms_output.put_line(E_name||E_job);
END;

练习：
1.打印输出10号部门平均工资

DECLARE 
  avg_sal NUMBER;
BEGIN 
  SELECT AVG(sal) INTO avg_sal FROM emp WHERE deptno = 10 GROUP BY deptno;
  dbms_output.put_line(avg_sal);
END;

2.打印输出7788 的员工的姓名岗位薪资部门编号部门名称以及对应的部门平均工资

DECLARE
  E_name VARCHAR2(20);
  E_job VARCHAR2(20);
  E_sal NUMBER;
  E_deptno NUMBER;
  E_D_name VARCHAR2(20);
  E_D_avgsal NUMBER;
BEGIN 
  SELECT A.ENAME, A.JOB, A.SAL, A.DEPTNO, B.DNAME, C.A
  INTO E_NAME, E_JOB, E_SAL, E_DEPTNO, E_D_NAME, E_D_AVGSAL
  FROM EMP A
  LEFT JOIN DEPT B
    ON A.DEPTNO = B.DEPTNO
  LEFT JOIN (SELECT AVG(SAL) A, DEPTNO FROM EMP GROUP BY DEPTNO) C
    ON A.DEPTNO = C.DEPTNO
    WHERE a.empno = 7788;
   dbms_output.put_line(E_NAME|| E_JOB||E_SAL||E_DEPTNO||E_D_NAME|| E_D_AVGSAL);
END;

打印输出

1.换行输出：

dbms_output.put_line(1个参数)

2.不换行输出：

dbms_output.put(1个参数)

注意：不换行输出结果后面一定要跟一个换行输出

手动输入

&
例题：手动输入一个员工编号 输出该员工姓名

DELCARE
	V_ename varchar2(20);
	V_empno number :'&请输入';      --最好加上单引号
								   --提示词不能一样
BEGIN 
	
END;

练习：输入一个部门编号返回该部门的工资最高的员工的姓名

DECLARE
  V_NAME VARCHAR2(20);
  D_deptno NUMBER :='&请输入';
BEGIN
  SELECT ENAME
    INTO V_NAME
    FROM EMP
   WHERE SAL = (SELECT MAX(SAL) FROM EMP 
                      WHERE DEPTNO = D_DEPTNO) 
                       AND deptno = D_DEPTNO;
dbms_output.put_line(V_NAME);
END;

练习：输入一个员工编号，打印输入该员工所在部门平均工资以及该员工姓名和岗位以及部门名称

DECLARE
  V_empno NUMBER :='&请输入';
  V_avgsal NUMBER;
  V_ename VARCHAR2(20);
  V_job VARCHAR2(20);
  V_dname VARCHAR(20);
BEGIN
  SELECT c.al,a.ename,a.job,b.dname INTO V_avgsal,V_ename,V_job,V_dname 
  FROM EMP A
  LEFT JOIN DEPT B
    ON A.DEPTNO = B.DEPTNO
  LEFT JOIN (SELECT AVG(SAL) Al, DEPTNO FROM EMP GROUP BY DEPTNO) C
    ON A.DEPTNO = C.DEPTNO
  WHERE empno = V_empno;
   dbms_output.put_line(V_avgsal||'-'||V_ename||'-'||V_job||'-'||V_dname);      
END;

变量类型

引用型变量类型

%TYPE

是一个引用型变量类型，可以引用某张表的某个列的属性，也可以引用已经定义好的变量类型
优点：
1.可以不用知道引用的列的属性
2.可以跟随引用的列的属性变化而变化。
例题：
查询7788员工的姓名，岗位，薪资，入职日期以及部门编号

DECLARE 
	V_ename emp.ename%TYPE;      --引用emp表的ename字段的属性  表名.列名%TYPE
	V_job emp,job%TYPE;
	V_sal emp.sal%TYPE;
	V_hiredate emp.hiredate%TYPE;
	
	V_deptno V_sal%TYPE          --引用已经定义好的变量类型，变量%TYPE
BEGIN
	select ename,job,sal,hiredate,deptno 
		into V_ename,V_job,V_sal,V_hiredate,V_deptno
		from emp where empno =7788;
   dbms_output.put_line(...)
END;

练习：输入一个员工编号打印输出该员工的员工信息以及该员工对应的工资等级

DECLARE
  V_empno NUMBER:='&请输入';

  V_ename emp.ename%TYPE;
  V_job emp.job%TYPE;
  V_hiredate emp.hiredate%TYPE;
  V_sal emp.sal%TYPE;
  V_comm emp.comm%TYPE;
  V_deptno emp.deptno%TYPE;
  V_MGR emp.MGR%TYPE;
  V_grade salgrade.grade%TYPE;

BEGIN

SELECT A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO, A.MGR, B.GRADE
  INTO 
       V_ENAME,
       V_JOB,
       V_HIREDATE,
       V_SAL,
       V_COMM,
       V_DEPTNO,
       V_MGR,
       V_GRADE
  FROM EMP A
  LEFT JOIN SALGRADE B
    ON A.SAL BETWEEN B.LOSAL AND B.HISAL
    WHERE a.empno = V_empno;
dbms_output.put_line( V_ENAME||'-'||
       V_JOB||'-'||
       V_HIREDATE||'-'||
       V_SAL||'-'||
       V_COMM||'-'||
       V_DEPTNO
       ||'-'||
       V_MGR||'-'||
       V_GRADE);      
END;

%ROWTYPE

是一个引用型变量类型，可以引用每张表的一行的字段属性
优点：
1.可以不用知道引用的列的属性
2.可以跟随引用的列的属性变化而变化。
例题：查询7788号员工的姓名行尾薪资部门编号以及入职日期

DECLARE
	V_a emp%ROWTYPE;         ---引用emp表一行的字段属性--表名%ROWTYPE
						--V_a   是一个复合变量，很多小变量的集合，此时包含了V_a.empno,V_a.ename,V_a.job....
BEGIN
	select ename,job,sal,deptno,hiredate
		into V_a.ename,V_a.job,V_a.sal,V_a.deptno,V_a.hiredate
			FROM emp
				WHERE empno = 7788;
dbms_output.put_line(V_a.ename||V_a.job||V_a.sal||V_a.deptno||V_a.hiredate);
END;

练习：打印输出史密斯的员工信息以及对应的部门平均工资

DECLARE
       V_A emp%ROWTYPE;
       V_avgsal emp.sal%TYPE;
BEGIN
SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO, B.AA
  INTO V_A.EMPNO,
       V_A.ENAME,
       V_A.JOB,
       V_A.HIREDATE,
       V_A.SAL,
       V_A.COMM,
       V_A.DEPTNO,
       V_AVGSAL
  FROM EMP A
  LEFT JOIN (SELECT AVG(SAL) AA, DEPTNO FROM EMP GROUP BY DEPTNO) B
    ON A.DEPTNO = B.DEPTNO
 WHERE A.ENAME = 'SMITH';
  dbms_output.put_line( V_a.EMPNO||'-'||
       V_a.ENAME||'-'||
       V_a.job||'-'||
       V_a.HIREDATE||'-'||
       V_a.SAL||'-'||
       V_a.COMM||'-'||
       V_a.DEPTNO
       ||'-'||
       V_avgsal);    
END;

记录型变量类型

RECORD
语法：

DECLARE 
 TYPE 类型名 IS RECODE(变量1 变量类型,变量2 变量类型.....);   --1.声明类型，复合类型
 变量 类型名                                               --2.声明变量  复合变量， 变量.变量1,变量.变量2,

练习：输入一个员工编号打印输出该员工的员工信息以及该员工对应的工资等级

DECLARE
  TYPE C1 IS RECORD(
    E_EMPNO    EMP.EMPNO%TYPE,
    E_NAME     EMP.ENAME%TYPE,
    E_JOB      EMP.JOB%TYPE,
    E_HIREDATE EMP.HIREDATE%TYPE,
    E_SAL      EMP.SAL%TYPE,
    E_COMM     EMP.COMM%TYPE,
    E_DEPTNO   EMP.DEPTNO%TYPE,
    E_MGR      EMP.MGR%TYPE,
    E_GRADE    SALGRADE.GRADE%TYPE);
  V_A     C1;
  V_EMPNO EMP.EMPNO%TYPE := '&请输入';
BEGIN
  SELECT A.EMPNO,
         A.ENAME,
         A.JOB,
         A.HIREDATE,
         A.SAL,
         A.COMM,
         A.DEPTNO,
         A.MGR,
         B.GRADE
    INTO V_A.E_EMPNO,
         V_A.E_NAME,
         V_A.E_JOB,
         V_A.E_HIREDATE,
         V_A.E_SAL,
         V_A.E_COMM,
         V_A.E_DEPTNO,
         V_A.E_MGR,
         V_A.E_GRADE
  
    FROM EMP A
    LEFT JOIN SALGRADE B
      ON SAL BETWEEN LOSAL AND HISAL
   WHERE A.EMPNO = V_EMPNO;
  DBMS_OUTPUT.PUT_LINE(V_A.E_EMPNO || '-' || V_A.E_NAME || '-' ||
                       V_A.E_JOB || '-' || V_A.E_HIREDATE || '-' ||
                       V_A.E_SAL || '-' || V_A.E_COMM || '-' ||
                       V_A.E_DEPTNO || '-' || V_A.E_MGR || '-' ||
                       V_A.E_GRADE);

END;

在程序块下的增删改

RETURNING INTO

增加数据

INSERT

DECLARE
BEGIN
	INSERT INTO emp(empno,ename) VALUES(1,'小明');
END;

RETURNING
是将dml语句影响的值返回交给变量
RETURNING 列名1,列名2 … INTO 变量1,变量2

DECLARE
	V_name varchar2(20);
	V_empno number;
BEGIN
	INSERT INTO emp(empno,ename) VALUES(1,'小明')
	RETURNING empno,ename into V_empno,V_name;
END;

插入时，RETURNING接受的是插入的值

修改数据

DECLARE
	V_sal number;
BEGIN
	UPDATE emp set sal = 666 
		where empno = 7788
	RETURNING sal into V_sal;
END;

在修改时，RETURNING INTO 是将修改后的值交给变量。

删除数据

DECLARE
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  DELETE FROM EMP WHERE EMPNO = 1 
  	RETURNING ENAME INTO V_ENAME;
END;

在删除时，RETURNING INTO 是将删除的值交给变量。