oracle--PL/SQL基础 Procedure Language & Structured Query Language

来源：互联网发布：linux vi修改文件编辑：程序博客网时间：2024/05/22 15:30

--PL/SQL的基本组成
--声明部分
DECLARE
--执行部分
BEGIN
--异常处理部分
EXCEPTION
END;

--(匿名块）从键盘接收2个数，计算之和
DECLARE
num1 NUMBER;
num2 NUMBER;
calResult NUMBER;
BEGIN
num1:=&n1;
num2:=&n2;
calResult:=num1+num2;
dbms_output.put_line(num1||'+'||num2||'='||calResult);
END;

--(匿名块）输出当前部门的总人数
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(1) INTO v_count FROM emp WHERE deptno=&deptno;
dbms_output.put_line('总人数：'||v_count);
END;

--子程序（存储过程）带参数的
--将某个部门员工的工资普提10%
CREATE OR REPLACE PROCEDURE update_emp(dno NUMBER) IS
BEGIN
UPDATE emp SET sal=sal*1.1 WHERE deptno=dno;
COMMIT;

END;

BEGIN
--在程序块中进行调研
update_emp(20);
--单行注释
/*
多行注释
*/
END;

CREATE OR REPLACE PROCEDURE emp_sal(dno NUMBER) IS
e_sal NUMBER;
BEGIN

SELECT sal INTO e_sal FROM emp WHERE deptno=dno;

dbms_output.put_line(e_sal);

END;

SELECT * FROM emp WHERE deptno=20;

--查询并打印指定的员工信息
DECLARE
v_name VARCHAR2(10);
v_salary NUMBER;
v_hiredate DATE;
BEGIN
SELECT ename,sal,hiredate INTO v_name,v_salary,v_hiredate FROM emp WHERE empno=&empno;
dbms_output.put_line('雇员名称：'||v_name);
dbms_output.put_line('薪水：'||v_salary);
dbms_output.put_line('入职时间：'||v_hiredate);
END;
--%TYPE可以自动根据列或者其他变量的类型和长度定义新变量
DECLARE
v_name emp.ename%TYPE;
v_salary emp.sal%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
SELECT ename,sal,hiredate INTO v_name,v_salary,v_hiredate FROM emp WHERE empno=&empno;
dbms_output.put_line('雇员名称：'||v_name);
dbms_output.put_line('薪水：'||v_salary);
dbms_output.put_line('入职时间：'||v_hiredate);
END;

--记录类型
--%ROWTYPE(变量的类型与表中整行记录完全相同）
DECLARE
emp_record emp%ROWTYPE;
BEGIN
SELECT * INTO emp_record FROM emp WHERE empno=&empno;
dbms_output.put_line('雇员名称：'||emp_record.ename);
dbms_output.put_line('薪水：'||emp_record.sal);
dbms_output.put_line('入职时间：'||emp_record.hiredate);
END;
--自定义类型
DECLARE
--类型的定义
TYPE emp_record_type IS RECORD(
v_name emp.ename%TYPE,
v_salary emp.sal%TYPE,
v_hiredate emp.hiredate%TYPE
);
--变量的声明
emp_record emp_record_type;
BEGIN
SELECT ename,sal,hiredate INTO emp_record FROM emp WHERE empno=&empno;
dbms_output.put_line('雇员名称：'||emp_record.v_name);
dbms_output.put_line('薪水：'||emp_record.v_salary);
dbms_output.put_line('入职时间：'||emp_record.v_hiredate);
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('该员工不存在');
END;

--集合类型
--一维表（类似一维数组）
DECLARE
TYPE empno_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
empno_table empno_table_type;

TYPE dname_table_type IS TABLE OF dept.dname%TYPE INDEX BY BINARY_INTEGER;
dname_table dname_table_type;
BEGIN
empno_table(1):=3;
empno_table(2):=4;
empno_table(200):=4;
empno_table(-200):=4;

dbms_output.put_line('1：'||empno_table(1));
dbms_output.put_line('2：'||empno_table(2));
dbms_output.put_line('200：'||empno_table(200));
dbms_output.put_line('-200：'||empno_table(-200));

SELECT dname INTO dname_table(-1) FROM dept WHERE deptno=&deptno;
dbms_output.put_line('dname_table(-1)：'||dname_table(-1));
END;

--二维表
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
BEGIN
SELECT * INTO dept_table(10) FROM dept WHERE deptno=&deptno;
dbms_output.put_line('dept_table(10).dname：'||dept_table(10).dname);
dbms_output.put_line('dept_table(10).loc：'||dept_table(10).loc);
END;

--可变数组
DECLARE
TYPE dept_varray_type IS VARRAY(10) OF dept.dname%TYPE NOT NULL;
dept_varray dept_varray_type:=dept_varray_type('1','2','3','4','5');
BEGIN
dbms_output.put_line('dept_varray(1)：'||dept_varray(1));
SELECT dname INTO dept_varray(2) FROM dept WHERE deptno=60;
dbms_output.put_line('dept_varray(2)：'||dept_varray(2));
--集合里面常用的方法
dbms_output.put_line(dept_varray.count);
dbms_output.put_line(dept_varray.limit);
dbms_output.put_line(dept_varray.first);
dbms_output.put_line(dept_varray.last);
dbms_output.put_line(dept_varray.prior(2));
dbms_output.put_line(dept_varray.next(2));
END;

SELECT NULL+1 FROM dual;
SELECT NULL||1 FROM dual;

--if elsif end if
--从键盘获取2个数，判断大小
DECLARE
v_num1 NUMBER;
v_num2 NUMBER;
BEGIN
v_num1:=&n1;
v_num2:=&n2;

IF v_num1>v_num2 THEN
dbms_output.put_line(v_num1||'>'||v_num2);
ELSE
dbms_output.put_line(v_num1||'<'||v_num2);
END IF;
--方式1
IF v_num1>5 THEN
dbms_output.put_line(v_num1||'>5');
ELSE
IF v_num1>3 THEN
dbms_output.put_line(v_num1||'>3');
END IF;
END IF;
--方式2
IF v_num1>5 THEN
dbms_output.put_line(v_num1||'>5');
ELSIF v_num1>3 THEN
dbms_output.put_line(v_num1||'>3');
END IF;
END;

-- CASE WHEN语句
DECLARE
v_grade CHAR(1):=UPPER('&grade');
v_result VARCHAR2(50);
BEGIN
CASE v_grade
WHEN 'A' THEN v_result:='相当的靠谱';
WHEN 'B' THEN v_result:='有点靠谱';
WHEN 'C' THEN v_result:='靠谱';
ELSE v_result:='相当的不靠谱';
END CASE;
dbms_output.put_line('你的成绩:'||v_result);
END;

--循环
--无条件循环
LOOP
EXIT WHEN
END LOOP;

--从1+2+....结果为10的时候退出循环
DECLARE
v_count NUMBER:=0;
i NUMBER:=1;
BEGIN
LOOP
v_count:=v_count+i;
i:=i+1;
EXIT WHEN v_count=10;
END LOOP;
dbms_output.put_line('i='||i);
dbms_output.put_line('v_count='||v_count);
END;

--while循环
WHILE <条件> LOOP

END LOOP;

DECLARE
v_count NUMBER:=0;
i NUMBER:=1;
BEGIN
WHILE v_count<10 LOOP
v_count:=v_count+i;
i:=i+1;
END LOOP;
dbms_output.put_line('i='||i);
dbms_output.put_line('v_count='||v_count);
END;

--for 循环
DECLARE
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line('i='||i);
END LOOP;
END;

DECLARE
BEGIN
FOR i IN REVERSE 1..10 LOOP
dbms_output.put_line('i='||i);
END LOOP;
END;

--不建议使用goto语句
DECLARE
v_count NUMBER:=1;
BEGIN
LOOP
v_count:=v_count+1;
IF v_count>=10 THEN
GOTO end_loop;
END IF;
END LOOP;
<<end_loop>>
dbms_output.put_line('v_count='||v_count);
END;

0 0