SQL编程(一)

最近准备学习一下SQL编程，通过ORACLE10g pl/sql 编程，参照其写的一些实例，欢迎大家一起学习

准备工作：oracle10g,pl/sql developer 

创建表 emp 和 dept

CREATE OR REPLACE TABLE emp(

empno   VARCHAR2(32),

ename   VARCHAR2(32),

sal         NUMBER(7,2),

deptno  VARCHAR2(32),

constraint emp_key primary key(empno)

);

那么就开始了 直接上例子了：

--pl/sql实例
DECLARE--声明变量
v_ename VARCHAR(5);
BEGIN
SELECT ename INTO v_ename FROM emp--INTO为游标，必须要加
WHERE empno='&no';--一个输入no
dbms_output.put_line('输出'||v_ename);--dbms_output为系统表，\\为连接符
EXCEPTION --抛出例外 
 WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('输入有误');
END;
--命名块    跟匿名块差不多，多一个<<>>
<<outer>>
DECLARE
v_deptno VARCHAR2(5);
v_name   VARCHAR2(10);
BEGIN
<<inner>>
  BEGIN
    SELECT dno INTO v_deptno FROM emp 
    WHERE lower(ename)=lower('&name')
  END;
  SELECT dname INTO v_name FROM emp
  WHERE deptno = v_deptno;
  dbms_output.put_line('名称'||v_name);
END;
--创建过程
CREATE OR REPLACE PROCEDURE update_sals(name varchar2, newsal number)
AS
BEGIN
  UPDATE emp SET sal = newsal
  WHERE  lower(ename) = lower(name);
END;

--调用过程
call update_sals('fei',7000);
--创建函数  必须有一个RETURN 语句在头部
CREATE OR REPLACE FUNCTION annual_income(name varchar2)
RETURN NUMBER AS
   annual_salary  NUMBER(7,2);
BEGIN
  SELECT sal*12 + nvl(comm,0) INTO annual_salary from emp
  WHERE lower(ename) = lower(name);
  RETURN annual_salary;
END;
--调用函数
--SQL命令行中
SQL->var sal NUMBER
SQL->call annual_income('fei') INTO :sal;
SQL->print sal;
--创建包 逻辑组合相关的函数和过程
--包声明
CREATE PACKAGE pkg AS
 PROCEDURE updatesals(name VARCHAR2(32),sal NUMBER);
 FUNCTION annual_income(name Varchar2(32)) RETURN NUMBER;
--包体定义
CREATE PACKAGE BODY pkg AS
PROCEDURE update_sals(name varchar2, sal number)
AS
BEGIN
  UPDATE emp SET sal = newsal
  WHERE  lower(ename) = lower(name);
END;
FUNCTION annual_income(name varchar2)
RETURN NUMBER AS
   annual_salary  NUMBER(7,2);
BEGIN
  SELECT sal*12 + nvl(comm,0) INTO annual_salary from emp
  WHERE lower(ename) = lower(name);
  RETURN annual_salary;
END;
END;
--包调用 要加上包名
SQL->call pkg.updatesals('fei',10000);
--创建触发器  通过某些特定条件隐含的执行存储过程
CREATE TRIGGER update_emp 
  AFTER UPDATE OF sal ON sal FOR EACH ROW 
BEGIN 
  UPDATE emp set sal =:new.sal--new 现在的值
  WHERE sal =:old.sal;--old 以前的值
END;

--简单类型
DECLARE 
  v_ename emp.ename%TYPE;--在不知道ename什么类型的状况下，可以匹配该类型
  v_sal   emp.sal%TYPE;
  v_sal_tax   Constant NUMBER(6,2):=1.5;--\ default expr 定义常量
  v_sal_af v_sal%TYPE;
  
BEGIN
  SELECT ename , sal INTO v_ename,v_sal
  FROM emp WHERE empno=&no;
  v_sal_af := v_sal * v_sal_tax;
  dbms_output.put_line('工资'||v_sal_af);
END;

--复合类型之记录  类似于结构体
DECLARE 
TYPE emp_record_type IS RECORD (
name emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
 SELECT ename,sal,job INTO emp_record
 FROM emp Where empno='3';
 dbms_output.put_line('员工名'||emp_record.name);
 END;
 
 --复合类型之表 类似于数组，但下标没有上限和下限
 DECLARE 
 TYPE ename_table IS TABLE OF emp.ename%type
      INDEX BY BINARY_INTEGER;
      tabled ename_table ;
 BEGIN
  SELECT ename INTO tabled(-1) FROM emp
  WHERE empno = '2';
  dbms_output.put_line('雇员：'||tabled(-1));
END;
 
 
 --复合类型之嵌套表 下标不能为负，可以作为列属性
 
 CREATE OR REPLACE TYPE emp_type AS OBJECT(
 name VARCHAR2(10),salary NUMBER(6,2));
 
 CREATE OR REPLACE  TYPE emp_array IS  TABLE OF emp_type;
 
 CREATE TABLE unit(
 uno VARCHAR2(32),uname VARCHAR2(32),employee emp_array)
 NESTED TABLE employee STORE AS employee;--必须定义一个存储表
 
 --复合类型之VARRAY 它要限定元素个数
 
 CREATE TYPE art_type AS OBJECT(
 title VARCHAR2(30),publish DATE
 );
 CREATE TYPE art_array IS VARRAY(20) OF art_type;
 
 CREATE TABLE composition (
 id NUMBER(10),title VARCHAR2(32),art art_array
 );
 

持续更新中~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~