PL/Sql 学习笔记---（一）-优快云博客

本文介绍了PL/SQL的基础结构，包括声明、执行和异常处理三部分，并详细讲解了如何定义变量、变量的数据类型及变量赋值的三种方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PL/SQL Block Structure可以由三个基本部分组成：声明(declare)部分[非必须]、执行(begin...end)部分[必须]、异常处理(Exception)部分[非必须]。
[DECLARE
-- declarations]
BEGIN
-- statements
[EXCEPTION
-- handlers]
END;

1.定义变量

变量的名字，大小写不区分的。

lastname
LastName -- same as lastname
LASTNAME -- same as lastname and LastName

变量可以是任何SQL数据类型，比如说CHAR,DATE,NUMBER,或者BOOLEAN,PLS_INTEGER.
DECLEAR
part_no NUMBER(6);
part_name VARCHAR2(20);
in_stock BOOLEAN;
part_price NUMBER(6,2);

为变量赋值的方法有三种。
第一种，用:=的方式。
DECLARE
wages         NUMBER;
hours_worked NUMBER :=40;
hourly_salary NUMBER :=22.50;
bonus         NUMBER :=150;
BEGIN
wages :=(hours_worked*hourly_salary)+bonus;
END;
第二种，by selecting (or fetching) database values into 变量
DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) :=100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
    WHERE employee_id=emp_id;
END;

第三种，通过传递参数给subprogram,然后在subprogram内部给变量赋值。
DECLARE
new_sal NUMBER(8,2);
emp_id NUMBER(6) :=126;
PROCEDURE adjust_salary(emp_id NUMBER, sal IN OUT NUMBER) IS
emp_job VARCHAR2(10);
avg_sal NUMBER(8,2);
BEGIN
   SELECT job_id INTO emp_job FROM employees WHERE employee_id = emp_id;
   SELECT AVG(salary) INTO avg_sal FROM employees WHERE job_id = emp_job;
   DBMS_OUTPUT.PUT_LINE('The average salary for ' ||emp_job|| ' employees: ' || TO_CHAR(avg_sal));
   sal := (sal+avg_sal)/2;
END;
BEGIN
SELECT AVG(salary) INTO new_sal FROM employees;
DBMS_OUTPUT.PUT_LINE('The average salary for all employees:'||TO_CHAR(new_sal));
adjust_salary(emp_id,new_sal);
DBMS_OUTPUT.PUT_LINE ('The adjusted salary for employee ' || TO_CHAR(emp_id) || ' is ' || TO_CHAR(new_sal));
END;