ORACLE PL/SQL基础编程

--初次打开会话要运行的（否则就不能输出数据）

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;

 

转载：http://www.cnblogs.com/ZRJ-boke/p/6600623.html