PL/SQL----pl/sql块

最新推荐文章于 2024-07-25 15:54:19 发布

meng-jiao

最新推荐文章于 2024-07-25 15:54:19 发布

阅读量463

点赞数 2

分类专栏： pl/sql 文章标签： oracle plsql

本文链接：https://blog.csdn.net/weixin_43363860/article/details/117743271

版权

pl/sql 专栏收录该内容

5 篇文章 2 订阅

订阅专栏

PL/SQL


```sql
DECLARE
   ---声明部分

BEGIN
   ---执行部分  ---必选
   
  EXCEPTION
    ---异常处理部分
END;

/
DECLARE




```sql
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
  DBMS_OUTPUT.PUT('HELLO');
END;
/

BEGIN 
  DBMS_OUTPUT.PUT('HELLO');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('WMJ');
  DBMS_OUTPUT.NEW_LINE;
END;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
  DBMS_OUTPUT.PUT_LINE('员工SMITH的工资是800元每月');\
END;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE('员工SMITH的工资是800元每月');
END;




```sql
DECLARE
V_NAME1  VARCHAR2(100);
V_NAME2 VARCHAR2(100);
V_NAME3 NUMBER(10);
BEGIN
V_NAME1:='你好，李焕英！';
V_NAME2:='hello';
V_NAME3:='123';
  DBMS_OUTPUT.PUT_LINE(V_NAME1);
  DBMS_OUTPUT.PUT_LINE(V_NAME2);
  DBMS_OUTPUT.PUT_LINE(V_NAME3);
END;

/

BEGIN
  DBMS_OUTPUT.PUT_LINE('海绵宝宝：你好，派大星');
  DBMS_OUTPUT.PUT_LINE('派大星：你好，海绵宝宝');
END;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE('    *     *   ');
  DBMS_OUTPUT.PUT_LINE('  *^^^ * ^^^*   ');
  DBMS_OUTPUT.PUT_LINE(' *^^^^^^^^^^^*');
  DBMS_OUTPUT.PUT_LINE('  *^^^^^^^^^*   ');
  DBMS_OUTPUT.PUT_LINE('    *^^^^^*   ');
  DBMS_OUTPUT.PUT_LINE('       *   ');
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('     $$    $$      ');
  DBMS_OUTPUT.PUT_LINE('  $$     $     $$  ');
  DBMS_OUTPUT.PUT_LINE('$                 $');
  DBMS_OUTPUT.PUT_LINE('$                 $');
  DBMS_OUTPUT.PUT_LINE('  $$           $$  ');
  DBMS_OUTPUT.PUT_LINE('     $$     $$     ');
  DBMS_OUTPUT.PUT_LINE('         $         ');
END;

在PL/SQL程序中，DML操作可以直接运行。
DML操作需要在程序中运行COMMIT命令才能自动提交！

SELECT * FROM EMP;

CREATE TABLE EMP_0419 AS SELECT * FROM EMP;
---备份表emp_0419
SELECT * FROM EMP_0419;
--查询备份表emp_0419

---DML语句
BEGIN
  DELETE FROM EMP WHERE DEPTNO=10;
  COMMIT;
  ---清理emp表中10号部门的数据
  INSERT INTO EMP SELECT * FROM EMP_0419 WHERE DEPTNO=10;
  COMMIT;
  ---从备份表中把10号部门的数据局恢复
  UPDATE EMP SET SAL=SAL/2 WHERE DEPTNO=30;
  COMMIT;
  ---把30号部门的薪资调整为原来的2倍
  
END;

CREATE TABLE EMP1 AS SELECT * FROM EMP;
SELECT * FROM EMP1;
SELECT * FROM EMP  ;

SELECT *  FROM EMP_20210330;
MERGE INTO EMP A
USING  EMP_20210330 B
ON(A.EMPNO=B.EMPNO)
WHEN MATCHED THEN
  UPDATE
  SET A.ENAME=B.ENAME,A.JOB=B.JOB,
      A.MGR=B.MGR,A.HIREDATE=B.HIREDATE,
      A.SAL=B.SAL,A.COMM=B.COMM,
      A.DEPTNO=B.DEPTNO
WHEN NOT MATCHED THEN
  INSERT(A.ENAME,A.JOB,A.MGR,A.HIREDATE,A.SAL,A.COMM,A.DEPTNO)
  VALUES(B.ENAME,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.COMM,B.DEPTNO);

执行部分-DDL操作在PL/SQL中，DDL不能直接操作，需要借助EXECUTE命令来实现。 EXECUTE IMMEDIATE
‘SQL语句’ ；其中SQL语句可以是DML语句也可以是DDL语句
PL/SQL程序运行时，ORACLE会首先检查程序的合理性，如若有语法等问题，程序将不予执行。

---DDL语句
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE EMP2 AS SELECT * FROM EMP';
  ---备份emp表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP2';
  ---清空emp表
  EXECUTE IMMEDIATE 'INSERT INTO EMP2 SELECT * FROM  EMP WHERE DEPTNO =30';
 
  ---从备份表中恢复数据
  
END;
SELECT * FROM EMP2;

合法性检查是在程序运行之前，不是在程序运行过程中但是写在EXECUTE命令中的SQL，会避开最初的合法检查，
等到该EXECUTE命令真正运行时，会检查该EXECUTE命令携带的SQL的合法性

声明部分

声明基本都是由两部分组成：名称和类型，类似于建表语句中声明每一个字段，每个量要有自己的名字（也称标识符），同时还要有自己的属性（含数据类型与精度）。

声明部分-定义变量和常量

变量与常量都必须在声明部分完成声明，而后在执行部分中，变量与常量都只能被使用，但不能被定义。

DECLARE
   A NUMBER(4) := 30 ;      --部门编号
   
BEGIN
   UPDATE EMP_0419 SET SAL=100 WHERE DEPTNO=A;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门薪资已更新');
   A := 10;  ---赋值
   DELETE FROM EMP_0419 WHERE DEPTNO =A;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门数据已经删除');
   /*A=10；*/ ---判断（条件）
   A :=20;
   UPDATE EMP_0419 SET SAL=5 WHERE DEPTNO=A;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE(A||'部门薪资已更新');
END;


特殊数据类型：
1)%TYPE

DECLARE 
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  ---把查到的值ENAME放到V_ENAME中
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

DECLARE 
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME,SAL,HIREDATE INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  ---ERROR   不能把不同的数据类型放到一个里
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;
/
DECLARE

   V_EMPNO NUMBER(4);
   V_ENAME VARCHAR2(20);
   V_JOB EMP.JOB%TYPE;

BEGIN
  SELECT EMPNO,ENAME,JOB INTO V_EMPNO,V_ENAME,V_JOB FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.PUT_LINE(V_EMPNO||V_ENAME||V_JOB);
END;
/

2)TYPE...DECORD
/*RECORD :*/
DECLARE
  TYPE A IS RECORD
  (V_EMPNO NUMBER(4),
   V_ENAME VARCHAR2(20),
   V_JOB EMP.JOB%TYPE);
   V_EMP A;
BEGIN
  SELECT EMPNO,ENAME,JOB INTO V_EMP FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.PUT_LINE(V_EMP.V_EMPNO||V_EMP.V_ENAME||V_EMP.V_JOB);
END;

3)%ROWTYPE 
DECLARE
  V_EMP  EMP%ROWTYPE;

BEGIN
  SELECT * INTO V_EMP FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||V_EMP.ENAME);
END;

/

DECLARE
  V_SQL VARCHAR(2000);
  V_NAME VARCHAR2(20):=UPPER('&ENAME');
  NN EMP%ROWTYPE;
  
BEGIN
  V_SQL:='SELECT * FROM EMP WHERE  ENAME='''||V_NAME||'''';
  EXECUTE IMMEDIATE V_SQL INTO NN;
  DBMS_OUTPUT.PUT_LINE(NN.EMPNO||','||NN.ENAME||','||NN.JOB||','||NN.SAL||','||NN.MGR||','||NVL(NN.COMM,0)||','||TO_CHAR(NN.HIREDATE,'YYYY/MM/DD')||','||NN.DEPTNO);
  
END;

/

DECLARE 
--声明记录类型的变量，引用EMP表中的行记录
emp_info emp1%ROWTYPE;
BEGIN
  --从emp表中取出字段值赋给记录类型
  SELECT * INTO EMP_INFO FROM EMP1 WHERE EMPNO= 7369;
  --为记录类型的变量更新新的值
  EMP_INFO.EMPNO:=7888;
  EMP_INFO.ENAME:='李焕英';
  EMP_INFO.JOB:='职员';
  EMP_INFO.SAL:='500';
 --向数据库表中插入记录类型的值
 INSERT INTO EMP1 VALUES EMP_INFO;
 COMMIT;
EXCEPTION
  WHEN OTHERS
    THEN 
      NULL;
END;
SELECT * FROM EMP1;

变量的赋值：
变量和常量在声明部分声明，不能在执行部分声明
在执行部分被使用不能定义


DECLARE 
  A NUMBER :=10;    ---A赋值为10 
  B  CONSTANT NUMBER :=20;   ---常量B赋值为20
BEGIN
  DBMS_OUTPUT.PUT_LINE(A);
  DBMS_OUTPUT.PUT_LINE(B);
END;



DECLARE 
  V_DEPTNO NUMBER :=&请输入部门编号;
  --弹出窗口，键盘输入
BEGIN
  DELETE FROM EMP_0419 WHERE DEPTNO=V_DEPTNO;  
  DBMS_OUTPUT.PUT_LINE(V_DEPTNO||号部门已删除);
END;


DECLARE 
  V_ENAME VARCHAR2(20) :=&请输入员工姓名;
  --弹出窗口，键盘输入
BEGIN
  DELETE FROM EMP_0419 WHERE ENAME=UPPER(V_ENAME);
  ---如果不用upper（）则输入小写不会转化为大写
     ----小写不能被删除
  DBMS_OUTPUT.PUT_LINE(V_ENAME||员工已被删除);
END;

/

DECLARE 
  V_ENAME VARCHAR2(20) :=&请输入员工姓名;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  V_ENAME:=UPPER(V_ENAME)
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  DELETE FROM EMP_0419 WHERE ENAME=V_ENAME;
  
  DBMS_OUTPUT.PUT_LINE(V_ENAME||员工已被删除);
END;

DECLARE 
  A VARCHAR2(20);
  
BEGIN
 /*SELECT * INTO V_EMP FROM EMP WHERE EMPNO=7369;*/
 EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO=7369' INTO A;
 DBMS_OUTPUT.PUT_LINE(A);
END;
----EXECUTE IMMEDIATE ...INTO ..赋值
---把SELECT ENAME FROM EMP WHERE EMPNO=7369 的结果赋值给 A
   
 
 

DECLARE 
  A VARCHAR2(20);
  V_SQL VARCHAR2(2000);
  
BEGIN
 /*SELECT * INTO V_EMP FROM EMP WHERE EMPNO=7369;*/
 /*EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO=7369' INTO A;*/
 V_SQL := 'SELECT ENAME  FROM EMP WHERE EMPNO=7566';
 EXECUTE IMMEDIATE V_SQL INTO A;
 DBMS_OUTPUT.PUT_LINE(A);
END;

----在V_SQL  里面不能用into
 ----在V_SQL里是一个sql语句



DECLARE 
  V_ENAME VARCHAR2(10) :='SMITH';
  V_SAL   NUMBER :=1000;
  V_SQL   VARCHAR2(1000);
  C_DEPTNO CONSTANT NUMBER :=10;

BEGIN
  SELECT SAL INTO V_SAL FROM EMP WHERE ENAME=V_ENAME;
  DBMS_OUTPUT.put_line(V_ENAME||'的工资是'||V_SAL);
  V_ENAME:='SCOTT';
  V_SQL:='SELECT SAL FROM EMP WHERE ENAME ='''||V_ENAME||'''';
  EXECUTE IMMEDIATE V_SQL INTO V_SAL;
  DBMS_OUTPUT.put_line(V_ENAME||'的工资是'||V_SAL);
  SELECT SUM(SAL) INTO V_SAL FROM EMP WHERE DEPTNO = C_DEPTNO;
  DBMS_OUTPUT.put_line(C_DEPTNO||'部门的工资是'||V_SAL);
END;

-----ERROR


DECLARE
  V_SQL VARCHAR2(2000);----动态SQL
BEGIN
  V_SQL :='DELETE FROM EMP_0419 WHERE ENAME='JAMES' ';
  -----james名字的引号与v_sql的引号分别不清
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  
END;

SELECT * FROM EMP_0419;
DECLARE
  V_SQL VARCHAR2(2000);----动态SQL
BEGIN
  V_SQL := 'DELETE FROM EMP_0419 WHERE ENAME=''SMITH'' ';
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  
END;

SELECT * FROM EMP_0419;
DECLARE
  V_SQL VARCHAR2(2000);
  V_ENAME VARCHAR2(20) :='&请指定员工姓名';----动态SQL
BEGIN
  --V_SQL := 'DELETE FROM EMP_0419 WHERE ENAME=''V_ENAME'' ';---不会删除数据
  V_SQL := 'DELETE FROM EMP_0419 WHERE ENAME='''||V_ENAME||'''';
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  
END;

DELETE FROM EMP_0419 WHERE ENAME='
ALLEN
'

插入一条数据用executeimmediate

DECLARE
  V_SQL VARCHAR2(2000);

BEGIN
  V_SQL := 'INSERT INTO EMP_0419(EMPNO,ENAME,SAL) VALUES (1234,'||'''ZHANGSAN'''||',800)';
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE  V_SQL;
  COMMIT;
END;
/

SELECT * FROM EMP_0419 FOR UPDATE;

/
DECLARE
  V_SQL VARCHAR2(2000);

BEGIN
  V_SQL := 'INSERT INTO EMP_0419(EMPNO,ENAME,SAL) VALUES (1234,''ZHANGSAN'',800)';
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE  V_SQL;
  COMMIT;
END;

从键盘获取MAX/MIN/AVG/SUM中的一种返回所有员工对应的最大薪资/最小薪资/平均薪资/合计薪资
统计结果按四舍五入处理到整数位 eg：输入：MAX，返回：所有员工的最大薪资是5000元
输入：AVG，返回：所有员工的平均薪资是2073元

DECLARE
  V_SAL NUMBER;
  V_SQL VARCHAR2(2000);
  V_VALUE VARCHAR2(30) :='&请输入' ;---键盘获取MAX/MIN/AVG/SUM中的一种  
BEGIN
  
  IF V_VALUE = 'MAX' THEN
    V_SQL := 'SELECT MAX(SAL) FROM EMP';
    DBMS_OUTPUT.put_line(V_SQL);
    EXECUTE IMMEDIATE V_SQL INTO V_SAL;
    DBMS_OUTPUT.put_line('所有员工的最大薪资是'||V_SAL||'元');
   ELSIF V_VALUE ='MIN' THEN 
    V_SQL := 'SELECT MIN(SAL) FROM EMP';
    DBMS_OUTPUT.put_line(V_SQL);
    EXECUTE IMMEDIATE V_SQL INTO V_SAL;
    DBMS_OUTPUT.put_line('所有员工的最小薪资是'||V_SAL||'元');
   ELSIF V_VALUE ='AVG' THEN 
    V_SQL := 'SELECT AVG(SAL) FROM EMP';
    DBMS_OUTPUT.put_line(V_SQL);
    EXECUTE IMMEDIATE V_SQL INTO V_SAL;
    DBMS_OUTPUT.put_line('所有员工的平均薪资是'||V_SAL||'元');
   ELSIF V_VALUE ='SUM' THEN
    V_SQL := 'SELECT SUM(SAL) FROM EMP';
    DBMS_OUTPUT.put_line(V_SQL);
    EXECUTE IMMEDIATE V_SQL INTO V_SAL;
    DBMS_OUTPUT.put_line('所有员工的合计薪资是'||V_SAL||CHR(13)||'元');
   END IF;
 

END;


/

DECLARE
  V_SAL NUMBER;
  V_SQL VARCHAR2(2000);
  V_VALUE VARCHAR2(30) :='&请输入';----键盘获取MAX/MIN/AVG/SUM中的一种  
BEGIN
    
 IF V_VALUE IN('MAX','MIN','AVG','SUM') THEN
    V_SQL := 'SELECT '||V_VALUE||'(SAL) FROM EMP';
    DBMS_OUTPUT.put_line(V_SQL);
    EXECUTE IMMEDIATE V_SQL INTO V_SAL;
    DBMS_OUTPUT.put_line('所有员工的'||CASE WHEN V_VALUE ='MAX' THEN  '最大'
                                            WHEN V_VALUE ='MIN' THEN  '最小'
                                            WHEN V_VALUE ='AVG' THEN  '平均'
                                            WHEN V_VALUE ='SUM' THEN  '合计'
                                        END
    ||'薪资是'||ROUND(V_SAL)||CHR(13)||'元');   ----CHR(13)  换行
  ELSE
    DBMS_OUTPUT.put_line('请输入正确的类型');
  END IF;
 

END;

meng-jiao

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL----pl/sql块

PL/SQL```sqlDECLARE ---声明部分BEGIN ---执行部分 ---必选 EXCEPTION ---异常处理部分END;/DECLARE```sqlBEGIN DBMS_OUTPUT.PUT_LINE('HELLO WORLD'); DBMS_OUTPUT.PUT('HELLO');END;/BEGIN DBMS_OUTPUT.PUT('HELLO'); DBMS_OUTPUT.NEW_LINE;
复制链接

扫一扫

专栏目录