PL/SQL基础 - 喝咖啡的企鹅

-块（编程）：过程（存储过程）、函数、触发器、包

--块结构：DECLEARR定义（可选）、BEGIN执行（必须）、EXCEPTIONN例外（可选）

--建立测试表

SQL> CREATE TABLE TPT (

2 tid NUMBER(6) PRIMARY KEY,

3 name CHAR(8) NOT NULL UNIQUE,

4 code CHAR(8));

SQL> DESC empx;

Name Type Nullable Default Comments

-------- ------------ -------- ------- --------

EMPNO NUMBER(4)

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2)

---------------分割线------------------

--建立过程

SQL> CREATE OR REPLACE PROCEDURE tp01 IS

2 BEGIN

3 INSERT INTO tpt VALUES(00001,'T01','p01');

4 END;

5 /

--查看错误

SQL> SHOW ERROR;

No errors for PROCEDURE SCOTT.TP01

--打开页面输出显示功能

SET SERVEROUTPUT ON;

--执行过程

SQL> EXEC tp01;

PL/SQL procedure successfully completed

---------------分割线------------------

--简单块

BEGIN

DBMS_OUTPUT.put_line('hello,oracle');

END;

--带定义块

SQL> DECLARE

2 v_ename VARCHAR2(5);

3 v_empno NUMBER(7);

4 BEGIN

--& 符号提示控制台输入

5 SELECT ename,empno INTO v_ename,v_empno FROM emp WHERE empno=&NO;

6 DBMS_OUTPUT.PUT_LINE('ename is:'||v_ename||';and empno is:'||v_empno);

7 EXCEPTION --声明异常

8 WHEN no_data_found THEN

9 DBMS_OUTPUT.PUT_LINE('no this empno');--异常处理

10 END;

11 /

no this empno

PL/SQL procedure successfully completed

ename is:SMITH

PL/SQL procedure successfully completed

---------------分割线------------------

--函数创建及调用关键词 FUNCTION

SQL> CREATE FUNCTION tf01(tfname VARCHAR2) RETURN

2 NUMBER IS annusal NUMBER(7,2);

3 BEGIN

4 SELECT (sal+nvl(comm,0))*12 INTO annusal FROM empx WHERE ename=tfname;

5 RETURN annusal;

6 END;

7 /

Function created

SQL> VAR annusal NUMBER;

SQL> CALL tf01('SCOTT') INTO:annusal; --INTO:有把结果注入的味道

Method called

annusal

---------

39996

---------------分割线------------------

--包的建立

SQL> CREATE PACKAGE test_package IS --仅是对包做一个声明

2 PROCEDURE update_sal(uname VARCHAR2,usal NUMBER);

3 FUNCTION annual_income(uname VARCHAR2) RETURN NUMBER;

4 END;

5 /

Package created

--包体的实现

SQL> CREATE OR REPLACE PACKAGE BODY test_package IS

2 PROCEDURE update_sal(uname VARCHAR2,usal NUMBER) IS

3 BEGIN

4 UPDATE empx SET sal=usal WHERE ename=uname;

5 END;

6 FUNCTION annual_income(uname VARCHAR2) RETURN NUMBER

7 IS annusal NUMBER;

8 BEGIN

9 SELECT (sal+nvl(comm,0))*12 INTO annusal FROM empx WHERE ename=uname;

10 RETURN annusal;

11 END;

12 END;

13 /

Package body created

--包的调用相当于java方法的调用

SQL> CALL test_package.update_sal('SCOTT',3500);

Method called

SQL> var annusal NUMBER;

SQL> CALL test_package.annual_income('SCOTT') INTO:annusal;

Method called

annusal

---------

42000

---------------分割线------------------

--PL/SQL记录（相当于单行数组）

SQL> DECLARE

2 TYPE empx_tp1 IS RECORD(tname empx.ename%TYPE,tsal empx.sal%TYPE,tjob empx.job%

TYPE);

3 tm_record empx_tp1;

4 BEGIN

5 SELECT ename,sal,job INTO tm_record FROM empx WHERE empno=7788;

6 dbms_output.put_line('name:'||tm_record.tname);

7 END;

8 /

name:SCOTT

PL/SQL procedure successfully completed

--PL/SQL 表类型（相当于单列数组）

SQL> DECLARE

2 TYPE test_table IS TABLE OF empx.ename%TYPE INDEX BY BINARY_INTEGER;

3 tm_name test_table;

4 BEGIN

5 SELECT ename INTO tm_name(0) FROM empx WHERE empno=7788;

6 dbms_output.put_line('name:'||tm_name(0));

7 END;

8 /

name:SCOTT

PL/SQL procedure successfully completed

---------------分割线------------------

--游标的建立与使用引用多行多列数据

SQL> DECLARE

2 TYPE test_cursor IS REF CURSOR;

3 tc test_cursor;

4 v_name empx.ename%type;

5 v_sal empx.sal%type;

6 BEGIN

7 OPEN tc FOR SELECT ename,sal FROM empx WHERE deptno=&dno;

8 LOOP

9 FETCH tc INTO v_name,v_sal;

10 EXIT WHEN tc%NOTFOUND; --tc%NOTFOUND tc取到空

11 dbms_output.put_line('name:'||v_name ||',sal:'|| v_sal);

12 END LOOP;

13 END;

14 /

name:JONES,sal:2975

name:FORD,sal:3000

name:SMITH,sal:1200

name:SCOTT,sal:3500

name:ADAMS,sal:1100

PL/SQL procedure successfully completed

---------------分割线------------------

--IF、WHILEE及GOTO

SQL> CREATE OR REPLACE PROCEDURE tp06 IS

2 v_num NUMBER:=1;

3 BEGIN

4 WHILE v_num<=10 LOOP

5 dbms_output.put_line('The Number is '||v_num);

6 v_num:=v_num+1;

7 IF v_num>8 THEN GOTO end_loop; --转到标签

8 END IF;

9 END LOOP;

10 <<end_loop>> --标签

11 dbms_output.put_line('End.');

12 END;

13 /

Procedure created

SQL> exec tp06;

The Number is 1

The Number is 2

The Number is 3

The Number is 4

The Number is 5

The Number is 6

The Number is 7

The Number is 8

End.

PL/SQL procedure successfully completed

---------------分割线------------------

SQL> CREATE TABLE bookcase(

2 bid number,

3 bname varchar2(20),

4 author varchar2(20),

5 price number(5,2),

6 publisher varchar2(20)

7 );

Table created

SQL>

SQL> CREATE OR REPLACE PROCEDURE savebook(bid IN NUMBER,bname IN VARCHAR2,author IN

VARCHAR2,price IN NUMBER,publisher IN VARCHAR2) IS --IN关键词要导入的参数省却默认

2 BEGIN

3 INSERT INTO bookcase VALUES(bid,bname,author,price,publisher);

4 END;

5 /

Procedure created

SQL> CREATE OR REPLACE PROCEDURE getBnameByBid(gbid IN NUMBER,gbname OUT VARCHAR2) IS

2 BEGIN --OUT关键词要导出的参数必须用OUTT声明

3 SELECT bname INTO gbname FROM bookcase WHERE bid=gbid;

4 END;

5 /

Procedure created

--用 PACKAGE包保存多行多列数据

SQL> CREATE OR REPLACE PACKAGE quesult AS TYPE result_cursor IS REF CURSOR;

2 END quesult;

3 /

Package created

SQL> CREATE OR REPLACE PROCEDURE tp08(dno IN NUMBER,rc OUT quesult.result_cursor) IS

2 BEGIN

3 OPEN rc FOR SELECT * FROM empx WHERE deptno=dno;

4 END;

5 /

Procedure created

CREATE OR REPLACE PROCEDURE queble --查询分页过程

(tame IN VARCHAR2,

psize IN NUMBER,

cno IN NUMBER,

rcount OUT NUMBER,

pcount OUT NUMBER,

rc OUT quesult.result_cursor) IS

v_pd NUMBER(5):= psize*(cno-1)+1;

v_pu NUMBER(5):= psize*cno;

v_sql VARCHAR(500);

BEGIN

--v_sql:='SELECT * FROM (SELECT t.*,ROWNUM rn FROM (SELECT * FROM '||tame||') t WHERE ROWNUM<='||v_pu||') WHERE rn>='||v_pd;

v_sql:='SELECT * FROM '||tame||' WHERE ROWID IN (SELECT B.rid FROM (SELECT ROWNUM rn,A.rid FROM (SELECT ROWID rid FROM '||tame||') A WHERE ROWNUM<='||v_pu||') B WHERE B.rn>='||v_pd||')';

--以上两种方法均可实现分页

OPEN rc FOR v_sql;

v_sql:='SELECT COUNT(*) FROM '||tame;

EXECUTE IMMEDIATE v_sql INTO rcount;

v_sql:='SELECT CEIL(COUNT(*)/'|| psize ||')FROM '||tame;

EXECUTE IMMEDIATE v_sql INTO pcount;

--CLOSE qs; --java调用的时候不支持关闭

END;

//分页查询代码的java调用
2

public void fenye(String tame, int ps, int cp) {
3

Connection conn = null;
4

CallableStatement cs = null;
5

ResultSet rs = null;
6

try {
7

conn = OracleUtil.getConnection();
8

String sql = "{call queble(?,?,?,?,?,?)}";
9

cs = conn.prepareCall(sql);
10

cs.setString(1, tame);
11

cs.setInt(2, ps);
12

cs.setInt(3, cp);
13

cs.registerOutParameter(4, OracleTypes.NUMBER);
14

cs.registerOutParameter(5, OracleTypes.NUMBER);
15

cs.registerOutParameter(6, OracleTypes.CURSOR);
16

cs.execute();
17

rs = (ResultSet) cs.getObject(6);
18

while(rs.next()){
19

System.out.printf("rc:%5d,pc:%5s\t",cs.getInt(4),cs.getInt(5));
20

System.out.printf("name:%10s,\tjob:%10s,\tsal:%6.2f\n", rs.getString(2), rs.getString(3), rs.getFloat(6));
21

}
22

} catch (Exception e) {
23

try {
24

conn.rollback();
25

} catch (SQLException se) {
26

se.printStackTrace();
27

}
28

e.printStackTrace();
29

} finally {
30

colse(rs,null,cs,conn);
31

}
32

}

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。