--初次打开会话要运行的（否则就不能输出数据）
SET SERVEROUTPUT ON
--变量赋值（赋值符号default、:=） / 代表执行
DECLARE
sname VARCHAR(10) := '张三';
BEGIN
dbms_output.put_line(sname);
END;
/
------------------------
--声明常量
DECLARE
pi CONSTANT NUMBER := 3.14;
r NUMBER DEFAULT 3;
area NUMBER;
BEGIN
area := pi*r*r;
dbms_output.put_line(area);
END;
/
------------------------
--宿主常量
VAR emp_name VARCHAR(30);
BEGIN
SELECT ename INTO :emp_name FROM emp WHERE empno=7499;
end;
/
print emp emp_name;
------------------------
--属性数据类型
--%rowtype 表示引入数据库中的一行作为数据类型（实体对象）
--%type 表示引用某个变量或者数据库的列的类型作为变量的数据类型（引用类型）
--%ROWTYPE（实体）
DECLARE
mydept dept%ROWTYPE;
BEGIN
SELECT * INTO mydept FROM dept WHERE deptno=10;
dbms_output.put_line(mydept.dname||'----'||mydept.loc);
END;
/
-------------------
--%TYPE（引用类）
DECLARE
e_job emp.job%TYPE;
my_job varchar2(10);
your_job my_job%TYPE;
BEGIN
SELECT job INTO e_job FROM emp WHERE empno=7934;
my_job := e_job;
your_job := e_job;
dbms_output.put_line('emp:'||e_job||' '||'my:my_'||my_job||' '||'your:yout_'||your_job);
END;
-------------------
--PL/SQL条件控制和循环控制
--if then
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp WHERE empno=7369;
IF newSal<1500 THEN
UPDATE emp SET comm=1000 WHERE empno=7369;
END IF;
COMMIT;
END;
-------------------
--if then elsif
DECLARE
newSal emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM emp WHERE empno=7369;
IF newSal>1500 THEN
UPDATE emp SET comm=1000 WHERE empno=7369;
ELSIF newSal<1500 THEN
UPDATE emp SET comm=500 WHERE empno=7369;
ELSE
UPDATE emp SET comm=400 WHERE empno=7369;
END IF;
COMMIT;
END;
-------------------
--case
DECLARE
v_grade CHAR(1):=UPPER('&p_grade');
BEGIN
CASE
WHEN v_grade='A' THEN
dbms_output.put_line('Excellent');
WHEN v_grade='B' THEN
dbms_output.put_line('Very Good');
WHEN v_grade='C' THEN
dbms_output.put_line('Good');
ELSE
dbms_output.put_line('No such grade');
END CASE;
END;
--case[selector]
DECLARE
v_grade CHAR(1):=UPPER('&p_grade');
BEGIN
CASE v_grade
WHEN 'A' THEN
dbms_output.put_line('Excellent');
WHEN 'B' THEN
dbms_output.put_line('Very Good');
WHEN 'C' THEN
dbms_output.put_line('Good');
ELSE
dbms_output.put_line('No such grade');
END CASE;
END;
-------------------
DECLARE
v_grade CHAR(1):=UPPER('&p_grade'); --&：输入
p_grade varchar2(10);
BEGIN
p_grade :=
CASE v_grade
WHEN 'A' THEN
'Excellent'
WHEN 'B' THEN
'Very Good'
WHEN 'C' THEN
'Good'
ELSE
'No such grade'
END;
dbms_output.put_line('Grade:'||v_grade||',the result is'||p_grade);
END;
-------------------------------
--decode
DECLARE
v_grade CHAR(1) := UPPER('&p_grade');
p_grade VARCHAR2(20);
BEGIN
SELECT DECODE(v_grade,'A','Excellent','B','Very Good','C','Good','No such grade') INTO p_grade
FROM dual;
dbms_output.put_line(p_grade);
END;
-------------------------------
--循环结构
DECLARE
pnum NUMBER := 1;
BEGIN
WHILE pnum < 10 LOOP
dbms_output.put(pnum);
pnum := pnum + 1;
END LOOP;
END;
--------------
DECLARE
pnum NUMBER DEFAULT 1;
BEGIN
LOOP
EXIT WHEN pnum > 10;
dbms_output.put_line(pnum);
pnum := pnum + 1;
END LOOP;
END;
-------------
--范围循环
DECLARE
BEGIN
FOR pnum IN 1..5 LOOP
dbms_output.put_line(pnum);
END LOOP;
END;
---------------
--游标loop
DECLARE
--定义一个实体类用于打印相关字段
temp_emp emp%ROWTYPE;
--类似于Java中的ResultSet
CURSOR mycursor IS SELECT * FROM emp WHERE sal>1600;
BEGIN
--打开游标
OPEN mycursor;
--遍历ResultSet
LOOP
FETCH mycursor INTO temp_emp;
EXIT WHEN mycursor%NOTFOUND;
dbms_output.put_line(temp_emp.empno||'----'||temp_emp.ename);
END LOOP;
END;
----------------
--异常（exception）
--(1)系统例外no_data_found
DECLARE
pname emp.ename%TYPE;
BEGIN
SELECT ename INTO pname FROM emp WHERE empno=1234;
EXCEPTION
WHEN no_data_found THEN dbms_output.put_line('没有找到该员工');
WHEN OTHERS THEN dbms_output.put_line('其他意外');
END;
-------------------------
--(2)系统例外too_many_rows
DECLARE
pname emp.ename%TYPE;
BEGIN
SELECT ename INTO pname FROM emp WHERE deptno=10;
EXCEPTION
WHEN too_many_rows THEN dbms_output.put_line('匹配了多行');
WHEN OTHERS THEN dbms_output.put_line('其他例外');
END;
-------------------------
--(3)系统例外zero_divide
DECLARE
pnum NUMBER;
BEGIN
pnum := 1/0;
EXCEPTION
WHEN zero_divide THEN dbms_output.put_line('0不能做除数');
WHEN OTHERS THEN dbms_output.put_line('其他例外');
END;
------------------------
--(4)系统例外value_error 算数或类型转换错误
DECLARE
pnum NUMBER;
BEGIN
pnum := 'asd';
EXCEPTION
WHEN value_error THEN dbms_output.put_line('算数或转换错误');
WHEN OTHERS THEN dbms_output.put_line('其他例外');
END;
------------------------
--(5)自定义例外 可以当成一个变量 可抛出
DECLARE
CURSOR cemp IS SELECT ename FROM emp WHERE deptno=500;
pname emp.ename%TYPE;
no_emp_found EXCEPTION;
BEGIN
OPEN cemp;
FETCH cemp INTO pname;
IF cemp%NOTFOUND THEN
RAISE no_emp_found;
END IF;
CLOSE cemp;--(不用担心没有关闭光标，系统会启动一个进程pmon（progress monitor来关闭它）
EXCEPTION
WHEN no_emp_found THEN dbms_output.put_line('没有找到该部门的员工');
WHEN OTHERS THEN dbms_output.put_line('其他例外');
END;
---------------------------------------
--创建存储过程例子：
CREATE OR REPLACE PROCEDURE proc1(
p_para1 VARCHAR2,
p_para2 OUT VARCHAR2,
p_para3 IN OUT VARCHAR2
)AS
v_name VARCHAR2(20);
BEGIN
v_name := '张三';
p_para3 := v_name;
dbms_output.put_line('p_para3:'||p_para3);
END;
-----------------------
--简单存储过程例子
CREATE OR REPLACE PROCEDURE findEmpJob(myempno IN NUMBER,myename OUT varchar2,myjob OUT varchar2)
AS
BEGIN
SELECT ename,job INTO myename,myjob FROM emp WHERE empno=myempno;
END;
-----------------------
--在初次打开PL/SQL时要运行以下这行代码
set serveroutput on
--返回结果集
--创建一个存储过程包
CREATE OR REPLACE PACKAGE mypack
IS
TYPE mycursor IS REF CURSOR;
END mypack;
--创建存储过程方法
CREATE OR REPLACE PROCEDURE findEmpJob(
myempno IN NUMBER,
myename OUT varchar2,
myjob OUT VARCHAR2,
other_name OUT mypack.mycursor
)
AS
BEGIN
SELECT ename,job INTO myename,myjob FROM emp WHERE empno=myempno;
OPEN other_name FOR
SELECT ename,job FROM emp WHERE empno IN(
SELECT empno FROM emp
MINUS
SELECT empno FROM emp WHERE empno=myempno
);
END;
SELECT * FROM emp;
SELECT * FROM dept;
-----------------------------------------
--执行动态SQL
--execute immediate
DECLARE
sql_stmt varchar2(200);
table_name VARCHAR2(20);
BEGIN
table_name:= 'stu_201704';
sql_stmt:= 'create table '||table_name ||'(';
sql_stmt:= sql_stmt ||'id int primary key,';
sql_stmt:= sql_stmt ||'stu_name varchar2(20)';
sql_stmt:= sql_stmt ||')';
dbms_output.put_line(sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
END;
--需求
/*
1.查询部门
2.遍历部门结果
a.生成动态create table sql
b.统计对应此部门的收入(工资+奖金)
3.执行动态create sql
4.执行动态insert
5.commit
*/
-- 动态SQL——查询，建表，统计，插入
declare
--定义游标，遍历所有部门
cursor mydept_cur is select * from dept where deptno in (select deptno from emp);
--定义部门实体类
mydept dept%rowtype;
--创建表的sql，需要动态执行
create_str varchar2(100);
--插入表的sql，需要动态执行
insert_str varchar2(200);
--定义查询平均收入
query_avg_income_str varchar2(100);
income_str varchar2(50);
begin
create_str:= 'create table dept_income(';
insert_str:= 'insert into dept_income values (';
query_avg_income_str:= 'select avg(sal)+avg(nvl(comm,0)) from emp where deptno=:1';
--打开游标
open mydept_cur;
loop
-- while (rs.next()) {
--}
--模仿rs.next(),做2件事
fetch mydept_cur into mydept;
exit when mydept_cur%notfound;
-- 追加中间的字段以及类型
create_str:= create_str || mydept.dname||' number,';
execute immediate query_avg_income_str into income_str using mydept.deptno;
insert_str:= insert_str|| income_str||',';
end loop;
--关闭游标
close mydept_cur;
create_str:= create_str||'dt varchar2(200))';
insert_str:= insert_str|| '''201703'')' ; --中间那对引号是表示转义字符
--输出语句
--dbms_output.put_line(create_str);
--dbms_output.put_line(insert_str);
--动态执行语句
execute immediate create_str;
execute immediate insert_str;
--DML语句需要提交
commit;
end;
-----------------------------
--动态PL/SQL捕获异常例子：
declare
--定义游标，遍历所有部门
cursor mydept_cur is select * from dept where deptno in (select deptno from emp);
--定义部门实体类
mydept dept%rowtype;
--创建表的sql，需要动态执行
create_str varchar2(100);
--插入表的sql，需要动态执行
insert_str varchar2(200);
--定义查询平均收入
query_avg_income_str varchar2(100);
income_str varchar2(50);
begin
create_str:= 'create table dept_income(';
insert_str:= 'insert into dept_income values (';
query_avg_income_str:= 'select avg(sal)+avg(nvl(comm,0)) from emp where deptno=:1';
--打开游标
open mydept_cur;
loop
-- while (rs.next()) {
--}
--模仿rs.next(),做2件事
fetch mydept_cur into mydept;
exit when mydept_cur%notfound;
-- 追加中间的字段以及类型
create_str:= create_str || mydept.dname||' date,';
execute immediate query_avg_income_str into income_str using mydept.deptno;
insert_str:= insert_str|| income_str||',';
end loop;
--关闭游标
close mydept_cur;
create_str:= create_str||'dt varchar2(200))';
insert_str:= insert_str|| '''201703'')' ; --中间那对引号是表示转义字符
--输出语句
--dbms_output.put_line(create_str);
--dbms_output.put_line(insert_str);
--动态执行语句
execute immediate create_str;
execute immediate insert_str;
--DML语句需要提交
commit;
--异常捕获
exception
--when 异常名字 then
when others then
dbms_output.put_line('捕获到其它异常:'||SQLCODE||' '||SQLERRM);
end;
这里是有关PL/SQL的基础代码：
谢谢各位博友的支持！
如需转载请注明出处：http://www.cnblogs.com/ZRJ-boke/p/6600623.html