Oracle编程

CREATE TABLE DEP(
    id int not null primary key,
    name varchar2(50) not null unique
);

CREATE SEQUENCE seq_test;

INSERT INTO DEP VALUES(seq_test.nextval, '开发部');

COMMIT;

SELECT * FROM DEP;

DECLARE
  v_name dep.name%type;
BEGIN
  SELECT name INTO v_name FROM DEP WHERE id=&id; --输入一个数作为条件
  dbms_output.put_line(v_name);
EXCEPTION    --异常
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('没有数据');
   WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('太多的数据');
   WHEN others THEN
      dbms_output.put_line('有异常发生');
END;
--过程
CREATE OR REPLACE PROCEDURE findDepNameById(v_id int, v_name out varchar2)
AS
   v_temp_name varchar2(50);
BEGIN
   SELECT name INTO v_temp_name FROM DEP WHERE id=v_id;
   v_name := v_temp_name;
EXCEPTION
   WHEN others THEN
      dbms_output.put_line('有异常发生');
END;

DECLARE
   v_name varchar2(50);
BEGIN
   findDepNameById(1, v_name);   --调用存储过程
   dbms_output.put_line(v_name);  --打印
END;

--函数
CREATE OR REPLACE FUNCTION funFindNameById(v_id int)
   RETURN varchar2  --函数有返回值
AS
   v_name varchar2(50);
BEGIN
   SELECT name INTO v_name FROM DEP WHERE id=v_id;
   return v_name;
END;

DECLARE
  v_name varchar2(50);
BEGIN
  v_name := funFindNameById(1);  --调用函数
  dbms_output.put_line(v_name || '  NEW'); --打印返回结果
END;

declare
a VARCHAR2(30);
b CHAR(10);
c NUMBER(10,2);
d date;
e boolean;
begin
 a := '12345';
 b := '2';
 c := 3.11;
 d := sysdate;
 e := true;
 dbms_output.put_line(a||'!');
 dbms_output.put_line(b||'!');
 dbms_output.put_line(c);
 dbms_output.put_line(d);
 if(e)then
     dbms_output.put_line('ok');
 end if;
end;

TYPE dep_record IS RECORD(
    id dep.id%type,
    name dep.name%type
);

DECLARE
  TYPE dep_record IS RECORD(
    id dep.id%type,
    name dep.name%type
  );
  v_record dep_record;
BEGIN
  SELECT * INTO v_record FROM dep WHERE id=1;
  dbms_output.put_line(v_record.id || '   ' || v_record.name);
END;

 

DECLARE
  v_username VARCHAR2(20);
BEGIN
  SELECT u_username INTO v_username FROM tuser where u_id=&no;
  dbms_output.put_line('UserName:' || v_username);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('请输入正确的uID');
END;

--测试过程
CREATE sequence seq_test;
CREATE PROCEDURE insert_dept(dept_name varchar) is
BEGIN
  --往dept表中插入一个部门，seq_test是一个序列
  INSERT INTO dept values(seq_test.nextval, dept_name);
END;
call insert_dept('开发部');
--查询结果
SELECT * FROM dept;

--测试函数
CREATE FUNCTION findDeptNameById(v_id int) RETURN VARCHAR2 is
  v_dept_name VARCHAR2(20);
BEGIN
  SELECT dept_name INTO v_dept_name FROM dept WHERE dept_id=v_id;
  RETURN v_dept_name;
END;
--调用函数
call dbms_output.put_line(findDeptNameById(1));
DECLARE
  v_result VARCHAR2(20);
BEGIN
  v_result := findDeptNameById(1);
  dbms_output.put_line(v_result);
END;

--创建包
CREATE OR REPLACE PACKAGE dept_pkg IS
  TYPE dept_table_name_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE insert_sql(dname dept.dept_name%TYPE);
  FUNCTION findDeptNameById(dId dept.dept_id%TYPE) RETURN dept.dept_name%TYPE;
END;
--创建包体
CREATE OR REPLACE PACKAGE BODY dept_pkg IS
  PROCEDURE insert_sql(dname dept.dept_name%TYPE) IS
  BEGIN
    INSERT INTO dept VALUES(seq_test.nextval, dname);
  END;
 
  FUNCTION findDeptNameById(dId dept.dept_id%TYPE) RETURN dept.dept_name%TYPE IS
    v_result dept.dept_name%TYPE;
  BEGIN
    SELECT dept_name INTO v_result FROM dept WHERE dept_id=dId;
    RETURN v_result;
  END;
END;
--调用包的过程和函数
call dept_pkg.insert_sql('市场部');
call dbms_output.put_line(dept_pkg.findDeptNameById(2));

--创建触发器
CREATE TRIGGER insert_dept_trg AFTER INSERT ON dept
BEGIN
  dbms_output.put_line('插入了一条记录');
END;
--测试触发器
call dept_pkg.insert_sql('行政部');
--定义Record数据类型
TYPE dept_record_type IS RECORD(
  dId dept.dept_id%TYPE,
  dName dept.dept_name%TYPE
);
--定义一个自定义类型dept_record_type的变量
v_dept_record dept_record_type;

DECLARE
  --定义Record数据类型
  TYPE dept_record_type IS RECORD(
    dId dept.dept_id%TYPE,
    dName dept.dept_name%TYPE
  );
  --定义一个自定义类型dept_record_type的变量
  v_dept_record dept_record_type;
  --用%ROWTYPE定义记录变量
  v_dept_record2 dept%ROWTYPE;
BEGIN
  SELECT * INTO v_dept_record FROM dept WHERE dept_id=1;
  dbms_output.put_line(v_dept_record.dName || TO_CHAR(v_dept_record.dId));
 
  SELECT * INTO v_dept_record2 FROM dept WHERE dept_id=1;
  dbms_output.put_line(v_dept_record2.dept_id || TO_CHAR(v_dept_record2.dept_name));
END;

DECLARE
  --定义表类型
  TYPE dept_table_name_type IS TABLE OF dept.dept_name%TYPE INDEX BY BINARY_INTEGER;
  --定义自定义表类型的变量
  v_dept_type_name dept_table_name_type;
BEGIN
  --给表类型变量的-1和0下标位置赋值
  SELECT dept_name INTO v_dept_type_name(-1) FROM dept WHERE dept_id=1;
  SELECT dept_name INTO v_dept_type_name(0) FROM dept WHERE dept_id=2;
  --获取表类型变量中存储的数据
  dbms_output.put_line(v_dept_type_name(-1));
  dbms_output.put_line(v_dept_type_name(0));
END;
SELECT * FROM dept;

--返回表类型的存储过程
CREATE OR REPLACE PROCEDURE testTableType(out_param out dept_pkg.dept_table_name_type) IS
  v_dept_name dept.dept_name%TYPE;
BEGIN
  SELECT dept_name INTO v_dept_name FROM dept WHERE dept_id=1;
  --要方便Java调用，下标必须从1开始
  out_param(1) := v_dept_name;
  SELECT dept_name INTO v_dept_name FROM dept WHERE dept_id=2;
  out_param(2) := v_dept_name;
END;

DECLARE
  v_result dept_pkg.dept_table_name_type;
BEGIN
  testTableType(v_result);
  dbms_output.put_line(v_result(0));
END;

--定义一个author_type自定义对象类型
CREATE OR REPLACE TYPE author_type AS OBJECT(
  firstName VARCHAR(20),
  secondName VARCHAR(20),
  sex CHAR(4),
  age int
);
--定义一个author_type嵌套表类型
CREATE OR REPLACE TYPE author_array IS TABLE OF author_type;
--将嵌套表类型运用到表列
CREATE TABLE book(
  id INT NOT NULL PRIMARY KEY,
  b_name VARCHAR2(20),
  author author_array
)NESTED TABLE author STORE AS author_table;
DECLARE
BEGIN
  --往book表中插入一个数据
  INSERT INTO book VALUES(seq_test.nextval, 'JAVA技术',
    author_array(
      author_type('Noble','Yang','男',31),
      author_type('Steve','Yang','男',10)
    )
  );
END;
select * from book;
--基本类型的表嵌套类型
CREATE OR REPLACE TYPE add_array IS TABLE OF VARCHAR2(20);
CREATE TABLE bookUser(
  id INT NOT NULL PRIMARY KEY,
  user_name VARCHAR(20) unique not null,
  password VARCHAR(20),
  address add_array
)NESTED TABLE address STORE AS address_table;
INSERT INTO bookUser VALUES(
  seq_test.nextval, 'zhangsan','23',add_array('湖南','广东','海南')
);
SELECT * FROM bookUser;
--建立ARRAY类型
CREATE TYPE au_array_type IS VARRAY(20) OF author_type;
--建立一个author列时au_array_type类型的表
CREATE TABLE book_array(
  id INT NOT NULL PRIMARY KEY,
  b_name VARCHAR2(20),
  author au_array_type
);
INSERT INTO book_array VALUES(
  seq_test.nextval, 'Oracle数据库',
  au_array_type(
    author_type('Noble','Yang','男',31),
    author_type('Steve','Yang','男',10)
  )
);
COMMIT;
SELECT * FROM book_array;
DECLARE
 v_author au_array_type;
BEGIN
 SELECT author INTO v_author FROM book_array WHERE id=23;
 --测试COUNT方法
 dbms_output.put_line(TO_CHAR(v_author.COUNT()));
END;

--创建一个存放作者对象的表
CREATE TABLE authors OF author_type;
--插入数据
INSERT INTO authors VALUES('ANDY','Yang','男', 10);
INSERT INTO authors VALUES('Noble','Yang','男', 30);
COMMIT;
--创建一个表，里面包含一个REF引用字段
CREATE TABLE book_ref_author(
  id int not null primary key,
  book_name varchar2(30),
  author REF author_type
);
--往表中插入一条记录，author字段里面的值是authors表中对象的地址
INSERT INTO book_ref_author
  SELECT seq_test.nextval, 'Java', ref(a)
  FROM authors a where lower(a.firstname)='andy';
commit;
SELECT * from book_ref_author;

DECLARE
  v_author_array ref author_type;
BEGIN
  SELECT author INTO v_author_array FROM book_ref_author WHERE id=41;
  dbms_output.put_line(v_author_array.firstName);
END;

SELECT value(o) FROM authors o;
SELECT REF(o) FROM authors o;
SELECT id, book_name, DEREF(author) FROM book_ref_author;

--测试LOB类型建立表
CREATE TABLE book_info(
  id int NOT NULL PRIMARY KEY,
  bookName VARCHAR2(50) NOT NULL,
  remark   CLOB,
  img      BLOB,
  bookZip  BFILE
);
SELECT * FROM book_info;
DELETE FROM book_info;
--建立目录,注意名称用双引号，值用单引号
CREATE DIRECTORY "bookZip" AS 'c:/data/temp';
SELECT * FROM dba_directories;

SELECT * FROM book_info ORDER BY ID FOR UPDATE;
COMMIT;