学习笔记：11pl/sql基础

11-1:只包含执行部分的pl/sql块
	set serveroutput on
	BEGIN
	  dbms_output.put_line('Hello,everyone!');
	END;
	/
11-2:包含定义部分和执行部分的pl/sql块
	set verify off
	DECLARE
	  v_ename VARCHAR2(5);
	BEGIN
	  SELECT ename INTO v_ename FROM emp
	  WHERE empno=&no;
	  dbms_output.put_line('雇员名:'||v_ename);
	END;
	/

11-3:包含定义部分,执行部分和异常处理部分的pl/sql块
	DECLARE
	  v_ename VARCHAR2(5);
	BEGIN
	  SELECT ename INTO v_ename FROM emp
	  WHERE empno=&no;
	  dbms_output.put_line('雇员名:'||v_ename);
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    dbms_output.put_line('请输入正确的雇员号！');
	END;
	/
11-5:命名块
	<<outer>>
	DECLARE
	  v_deptno NUMBER(2);
	  v_dname  VARCHAR2(10);
	BEGIN
	  <<inner>>
	  BEGIN
	    SELECT deptno INTO v_deptno FROM emp
	    WHERE lower(ename)=lower('&name');
	  END;--<<inner>>
	  SELECT dname INTO v_dname FROM dept
	  WHERE deptno=v_deptno;
	  dbms_output.put_line('部门名:'||v_dname);
	END; -- <<outer>>
	/


11-6:过程
	CREATE PROCEDURE update_sal(name VARCHAR2,newsal NUMBER)
	IS
	BEGIN
	  UPDATE emp SET sal=newsal
	  WHERE lower(ename)=lower(name);
	END;
	/
	exec update_sal('scott',2000)
11-7:函数
	CREATE FUNCTION annual_income(name VARCHAR2)
	RETURN NUMBER IS
	  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;
	/
	SELECT annual_income('scott') 年收入 FROM dual;

11-8:包
	CREATE PACKAGE emp_pkg IS
	  PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
	  FUNCTION annual_income(name VARCHAR2) RETURN NUMBER;
	END;
	/
	CREATE PACKAGE BODY emp_pkg IS
	  PROCEDURE update_sal(name VARCHAR2,newsal NUMBER)
	  IS
	  BEGIN
	    UPDATE emp SET sal=newsal
	    WHERE lower(ename)=lower(name);
	  END;
	  FUNCTION annual_income(name VARCHAR2) RETURN NUMBER
	  IS
	    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;
	/
	exec emp_pkg.update_sal('scott',1500)
	SELECT emp_pkg.annual_income('scott') 年收入 FROM dual;
11-9:触发器
	SELECT ename FROM emp WHERE deptno=10;
	CREATE TRIGGER update_cascade
	AFTER UPDATE OF deptno ON dept
	FOR EACH ROW
	BEGIN
	  UPDATE emp SET deptno=:new.deptno
	  WHERE deptno=:old.deptno;
	END;
	/


11-10:使用标量变量
	DECLARE
	  v_ename VARCHAR2(5);
	  v_sal   NUMBER(6,2);
	  c_tax_rate CONSTANT NUMBER(3,2):=0.03;
	  v_tax_sal NUMBER(6,2);
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&eno;
	  v_tax_sal:=v_sal*c_tax_rate;
	  dbms_output.put_line('雇员名:'||v_ename);
	  dbms_output.put_line('雇员工资:'||v_sal);
	  dbms_output.put_line('所得税:'||v_tax_sal);
	END;
	/


11-11:使用%type属性
	DECLARE
	  v_ename emp.ename%TYPE;
	  v_sal  emp.sal%TYPE;
	  c_tax_rate CONSTANT NUMBER(3,2):=0.03;
	  v_tax_sal v_sal%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&eno;
	  v_tax_sal:=v_sal*c_tax_rate;
	  dbms_output.put_line('雇员名:'||v_ename);
	  dbms_output.put_line('雇员工资:'||v_sal);
	  dbms_output.put_line('所得税:'||v_tax_sal);
	END;
	/


11-12:pl/sql纪录
	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=&eno;
	  dbms_output.put_line('姓名:'||emp_record.name);
	  dbms_output.put_line('工资:'||emp_record.salary);
	  dbms_output.put_line('岗位:'||emp_record.title);
	END;
	/
11-13:pl/sql表
	DECLARE
	  TYPE ename_table_type IS TABLE OF emp.ename%TYPE
	    INDEX BY BINARY_INTEGER;
	  ename_table ename_table_type;
	BEGIN
	  SELECT ename INTO ename_table(-1) FROM emp
	  WHERE empno=&eno;
	  dbms_output.put_line('雇员名:'||ename_table(-1));
	END;
	/
11-14:嵌套表
	CREATE OR REPLACE TYPE emp_type AS OBJECT(
	  name VARCHAR2(10),salary NUMBER(6,2),hiredate DATE);
	/
	 CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
	/
	CREATE TABLE department(
	  deptno NUMBER(2),dname VARCHAR2(10),employee emp_array
	) NESTED TABLE employee STORE AS employee;
11-15:varray
	CREATE TYPE article_type AS OBJECT (
	  title VARCHAR2(30),pubdate DATE);
	/
	CREATE TYPE article_array IS VARRAY(20) OF article_type;
	/
	CREATE TABLE author(
	  id NUMBER(6),name VARCHAR2(10),article article_array
	);


11-16:ref cursor
	DECLARE
	  TYPE c1 IS REF CURSOR;
	  dyn_cursor c1;
	  col1 VARCHAR2(20);
	  col2 VARCHAR2(20);
	BEGIN
	  OPEN dyn_cursor FOR SELECT &col1,&col2 FROM &tab WHERE　&con;
	  FETCH dyn_cursor INTO col1,col2;
	  dbms_output.put_line('col1:  '||col1);
	  dbms_output.put_line('col2:  '||col2);
	  CLOSE dyn_cursor;
	END;
	/


11-18:使用之类型定义标量
	DECLARE
	  SUBTYPE my_type IS VARCHAR2(20);
	  v_name my_type(10);
	BEGIN
	  SELECT ename INTO v_name FROM emp
	  WHERE empno=&eno;
	  dbms_output.put_line('姓名:'||v_name);
	END;
	/


11-20:在pl/sql表达式中使用序列
DECLARE
  v1 INT;
  v2 INT;
BEGIN
  v1:=empno_seq.currval;
  v2:=empno_seq.nextval;
  DBMS_OUTPUT.PUT_LINE('v1='||v1);
  DBMS_OUTPUT.PUT_LINE('v2='||v2);
END;
/