/*

  *====================================== basic sql ==========================================

  */

 -- Merge --

 merge into copy_emp as ac

 using employees e

 on (c.employee_id = employee_id)

 when matched then

     update set

     c.first_name = e.first_name

     c.last_name = e.last_name

     c.department_id = e.department_id

 when not matched then

     insert values(e.employee_id, e.first_name, e.last_name, e.department_id)

 -- alter table --

 alter table dep80

 add(job_id varchar(8))

 alter table dep80

 modify(job_id varchar(30))

 alter table dep80

 drop(job_id)

 alter table dep80

 set unused(job_id)

 -- reaname --

 rename dept to detail_dept

 -- truncate --

 truncat table detial_dept

 -- comment --

 comment on table dept IS 'department information'

 comment on dept.deptno IS 'department number'

 -- constraint , not null, unique, primary key, foreign key, check --

 create table employees (

     employee_id        NUMBER(6),

     job_id            VARCHAR2(10) NOT NULL,

     constraint emp_id_pk PRIMARY KEY(employee_id)

 )

 alter table employees

     add constraint emp_manager_fk FOREIGN KEY(manager_id)

     REFERENCES employee(employee_id)

 alter table employees

     drop constraint emp_manager_fk [cascade]

 alter table employees

     disable constraint emp_manager_fk[cascade]

 alter table employees

     enable constraint emp_manager_fk

 alter table employees

     drop (column1, column2) [cascade constraints]

 -- view --

 create or replace view emp_view as select deptno, dname, loc from dept

 drop view emp_view

 -- sequence --

 create sequence seq_emp

 increment by 1

 start with 0

 maxvalue 20000000

 minvalue 0

 nocycle

 nocache

 -- index --

 create index ind_emp on dept(deptno, dname)

 -- synonym --

 create public synonym dept for scott.dept

 -- 2种方法: 例如 object 在 hr 下, scott 想要访问 hr 下的 table department.

 -- 1. 在 scott 模式下, create synonym department for hr.department

 -- 2. 在 hr 模式下, create synonym scott.department for department , 注意这种含义是 hr 用户代替 scott

 -- 用户在 scott 模式下创建了别名, scott.department 这个表示是在 scott模式下, 所以在 scott 模式下就直接

 -- 有了这样一个别名.

 -- create user --

 create user leon_master identified by leon

 alter user leon_master identified by master ( 修改密码 )

 -- grant privilege --

 grant dba to leon_master

 grant create session, create table, create sequence to scott ( to role )

 grant manager(role) to leon_master

 grant update(deptname, location_id) on departments to scott, manager

 -- role --

 create role manager;

 grant create session, create table, create sequence to manager;

 -- revoke --

 revoke create session from scott

 revoke update(deptname, location_id) on department from scott, manager

 -- database link --

 create public database link mes20

 connect to <被链接数据库用户名> identified by <被链接数据库密码>

 using 'ASDF'

 /*

 tnsnames >>>

 ASDF, ASDF2 =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.25)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.22)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.26)(PORT = 1521))

     (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = ASDF)

     )

   )

 */

 -- relation sub query --

 select column1, column2

 FORM table1 outer

 where column1 operator ( select column1 from table2 where expr1 = outer.exp2);

 select column1, column2

 from table1 outer

 where exists ( select column1 from table2 where expr1 = outer.exp2);

 update table1 als_1

 set column = ( select expression from table2 als_2 where als_1.column = als_2.column);

 delete from table1 als_1

 where column operator ( select expression from table2 als_2 where als_1.column = als_2.column);

 -- Hierarchical retrieve --

 select substr(sys_connect_by_path(ENAME,'->'), 3) EMPLOYEE from EMP

 connect by prior EMPNO = MGR

 start with ename = '小董'

 -- 首先这个查询有一个 connect by prior EMPNO=MGR, 这是一个连接条件, 从 start with 开始找到第一行数据,

 -- 然后根据连接条件来进行. 向后遍历. 翻译过来就是, 先从小董开始, 然后小董的 empno 作为父层次 = manager

 -- 向后依次遍历.

 /*

  * =========================================== plsql =========================================

  */

 -- 系统绑定变量 可以直接在 sqlplus 环境中定义

 variable g_salary NUMBER

 -- 注意此变量不同于一般意义的plsql变量, 这个是在环境中定义的类似环境变量

 -- PL/SQL 定义变量

 declare    v_depno number(2) not null := 100;

 declare c_comn constant number := 1400;

 declare v_location varchar(20) := 'hello world';

 declare v_name employees.lastname%TYPE;

 declare v_balance number(10,2);

 declare v_min_balance v_balance%TYPE :=10; -- 参照上一个定义 v_balance

 -- declare v_home = q'[tom's home]' -- 注意是以双引号内的形式, 实际环境中, 不需要双引号

 declare l_right_now DATE NOT NULL DEFAULT SYSDATE;

 -- 在 PL/SQL 中 sql 部分不需要冒号 :

 declare v_bonus NUMBER(6);

 begin

     select salary * 0.01

     INTO v_bouns

     FROM employees

     WHERE emp_id = '';

 end;

 -- 游标 --

 declare cursor cursor_name IS

     select_statement;

 open cursor_name

 fetch cursor_name into variable1, varibal2

 close cursor_name

 -- 以上4步骤, 分别为定义游标, 打开游标, 取出游标, 关闭游标

 -- 游标属性 --

 cursor_name%isopen

 cursor_name%notfound

 cursor_name%found

 cursor_name%rowcount

 -- 隐式游标举例 --

 Function book_title (isbn_in IN books.isbn%TYPE)

 return books.title%type

 IS

     return_value    book.title%type;

 BEGIN

     SELECT     title

     into    return_value

     from    books

    where    isbn = isbn_in;

     return    return_value;

     exception

         when no_date_found

         then

             return null;

         when too_many_rows

         then

             errpkg.record_and_stop ('Data integrity error for:' || isbn_in);

             raise;

 END;

 -- 显示游标举例 --

 Function jealousy_level (

     NAME_IN    IN    friends.NAME%TYPE)    RETURN    NUMBER

 AS

     cursor jealousy_cur

     IS

         select location from friends

         where name = UPPER(name_in);

     jealousy_rec    jealousy_cur%ROWTYPE;

     retval    NUMBER;

 BEGIN

     OPEN jealousy_cur;

     fetch jealousy_cur into    jealousy_rec;

     IF jealousy_cur%found then

         if jealousy_rec.location = 'PUERTO RICO' THEN

             retval := 10;

         elsif jealousy_rec.location = 'CHICAGO' THEN

             retval := 1;

         end if;

     end if;

     close jealousy_cur;

     return retval;

 EXCEPTION

     when others then

         if jealousy_cur%isopen then

             close jealousy_cur;

         end if;

 END;

 -- 游标变量 --

 TYPE cursor_type_name IS REF CURSOR [RETURN return type];

 TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;  -- 强类型

 TYPE generic_curtype IS REF CURSOR;                            -- 弱类型

 -- 从 oracle 9i 开始, 数据库已经替我们定义好了一个弱类型, 直接用就可以了

 declare my_cursor SYS_RECURSOR;

 -- 声明游标变量的方法

 cursor_name cursor_type_name;

 -- 打开游标变量

 open cursor_name FOR select_statement;

 -- 从游标变量获取数据, 同静态游标一样

 FETCH cursor_variable_name INTO record_name;

 FETCH cursor_variable_name INTO varibale_name, variable_name...;

 -- 游标表达式

 CURSOR( subquery )    

 -- 参数化 游标 --  参数的类型只能是 in 类型

 declare

     v_department    classes.department%type;

     v_course        classes.course%type;

     cursor    c_classes is

     select * from classes

     where department = v_department

     and course = v_course;

 declare

     cursor c_classes(p_department classes.department%type, p_course classes.course%type) is

     select * from classes

     where department = v_department

     and course = v_course;

 open c_classes('HIS', 101);

 -- 游标中的 for update 语句, 加锁, 默认的游标是不会加锁的 --

 -- 如果此时该表已经被加锁, 那么这个游标将无限期的被挂起, 直到该表所已经解开, 而 NOWAIT 语句表示不等挂起

 -- 如果发现这个表已经被加锁, 直接会提示一个错误, 个人感觉 NOWAIT 好一点

 declare

     v_NumCredits    classes.num_credites%type;

     cursor c_RegisteredStudents IS

     select * from students

     where id in ( select student_id

                     from registered_students

                     where department = ’HIS‘

                     and course = 101)

     for update of current_credits;  -- one column

 begin

     for v_studentInfo in c_RegisteredStudents

     loop

         select num_credits

         into v_NumCredits

         from classes

         where department = 'HIS'

         and course = 101;

         update students

             set current_credits = current_credits + v_NumCredits

             where current of c_RegisteredStudents  -- 修改当前行的 column 值

     end loop;

 end;

 -- 注意 commit; 操作会释放锁, 所以如果你将 commit 写在 for 循环里的话, 那么再没有完成的情况下就释放锁,

 -- 会出现错误提示 ORA-1002： fetch out of sequence

 declare

     cursor c_AllStudents IS

     select * from students

     for update;        -- 注意, 这只有 for update, 所以所有的列都被加锁

     v_StudentInfo    c_AllStudents%ROWTYPE;

 begin

     open    c_AllStudents;

     FETCH c_allStudents INTO v_StudentInfo;

     -- 如果这时候使用 commit; 那么后边的记录就没有办法进行了, 所以推荐 close cursor 以后再 commit;

 end;

 -- 当然, 如果你非想在 for 循环里使用 commit, 做法是, 现将游标全部提取出来, 然后通过 for 循环进行修改

 declare

     v_NumCredits    classes.num_credites%type;

     cursor c_RegisteredStudents IS

     select * from students

     where id in ( select student_id

                     from registered_students

                     where department = ’HIS‘

                     and course = 101)

     for update of current_credits;  -- one column

 begin

     for v_studentInfo in c_RegisteredStudents

     loop

         select num_credits

         into v_NumCredits

         from classes

         where department = 'HIS'

         and course = 101;

         update students

             set current_credits = current_credits + v_NumCredits

             where current of c_RegisteredStudents  -- 修改当前行的 column 值

         commit;  -- 注意此时已经通过 for 循环将所有游标提取到 v_studentInfo里, 并且不能使用

                  -- where current of

     end loop;

 end;

 -- If statement --

 IF salary >= 10000 AND salary <=20000

 THEN

     give_bonus(employee_id, 1500);

 ELSIF salary > 20000 AND salary <= 40000

 THEN

     give_bonus(employee_id, 10000);

 ELSIF salary > 40000

 THEN

     give_bouns(employee_id, 400);

 END IF;

 -- case statement --

 CASE employee_type

 WHEN 'S' THEN

     award_salary_bonus(employee_id);

 WHEN 'H' THEN

     award_hourly_bonus(employee_id);

 WHEN 'C' THEN

     award_commissioned_bonus(employee_id);

 ELSE

     RAISE invalid_employee_type;

 END CASE;

 CASE TRUE

 WHEN salary >= 10000 AND salary <= 20000 THEN

     give_bouns(employee_id, 1500);

 WHEN salary > 20000 AND salary < 40000 THEN

     give_bouns(employee_id, 1000);

 ELSE

     give_bouns(employee_id, 0);

 END CASE;

 -- loop --

 -- basic loop --

 PROCEDURE display_multiple_years (

     start_year_in IN PLS_INTEGER,

     end_year_in IN PLS_INTEGER

 )

 IS

     l_current_year PLS_INTEGER := start_year_in;

 BEGIN

     LOOP

         EXIT WHEN l_current_year > end_year_in;

         display_total_sales(l_current_year);

         l_current_year := l_current_year + 1;

     END LOOP;

 END display_muliple_years;

 -- for loop --

 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)

 IS

 BEGIN

     FOR l_current_year IN start_year_in .. end_year_in

     LOOP

         display_total_sales(l_current_year);

     END LOOP;

 END display_multiple_years;

 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)

 IS

 BEGIN

     FOR l_current_year IN (

         select * from sales_date

         where year between start_year_in and end_year_in)

     LOOP

         display_total_sales(l_current_year);

     END LOOP;

 END display_multiple_years;

 -- 游标参考 for loop --

 -- 一般形式的游标 --

 declare

     cursor occupancy_cur IS

     select pet_id, room_number

     from occupancy where occupid_dt = trunc(sysdate);

     occupancy_rec occupancy_cur%rowtype;

 begin

     open occupancy_cur;

     loop

         fetch occupancy_cur into occupancy_rec;

         exit when occupancy_cur%notfound;

         update_bill(occupancy_rec.pet_id, occupancy_rec.room_id);

     end loop;

     close occupancy_cur;

 end;

 -- For loop 形式游标 --

 declare

     cursor occupancy_cur IS

     select pet_id, room_number

     from occupancy where occupid_dt = trunc(sysdate);

 begin

     for occupancy_rac in occupacy_cur        -- occupancy 不用定义

     loop

         update_bill(occupancy_rec.pet_id, occupancy_rec.room_id);

     end loop;

 end;

 -- while loop --

 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)

 IS

     l_current_year PLS_INTEGER := start_year_in;

 BEGIN

     while (l_current_year <= end_year_in)

     LOOP

         display_total_sales(l_current_year);

         l_current_year := l_current_year + 1;

     END LOOP;

 END display_multiple_years;

 -- --------------------------Exception ------------------------------------------------------------

 -- EXCEPTION --

 -- 1.抛出异常, 用户错误, 系统错误, 内存泄露等等问题都会抛出异常

 --   抛出方式, raise exception_name, raise package_name.exception_name, raise;

 --     raise_application_error 也可以抛出异常, 并且可以写自己bussiness的逻辑信息

 procedure raise_by_language(code_in IN PLS_INTEGER)

 IS

     l_message error_table.error_string%TYPE;

 BEGIN

         SELECT error_string

         INTO    l_message

         FROM    error_table

         where    error_number = code_in

         and     string_language = USERNV('LANG');

         RAISE_APPLICATION_ERROR(code_in, l_message);    -- 抛出异常

 END;

 -- 2. 捕获异常

 --      异常种类, oracle定义好有名字的异常, 一共20个, 没名字的异常, 还有就是用户定义异常

 --      捕获异常后, 程序不会返回到抛出异常处, 执行完捕获异常, 看是向下还是停止.

 --       当处理没有命名的异常时, 可以通过以下语句来定义名称

 declare

     e_emps_remaining    EXCEPTION

     PRAGMA    EXCEPTION_INIT(e_emps_remaining, -2292);

 begin

     -- do something

     exception

         when e_emps_remaining then

             statement1

 end;

 -- 3. 内置函数

 SQLCODE -- 返回一个错误代码, 如果没有错误, 返回 0

 SQLERRM -- 返回错误代码对应的官方错误信息

 DBMS_UTILITY.FORMAT_ERROR_BACKTRACK -- 10g 以后开始饮用, 返回一个格式化文本串(可以返回抛出异常行号)

 -- ---------------------------------End Exception -------------------------------------------------

 -- --------------------- 动态 sql -----------------------------------------------------------------

 -- ---------------------- End 动态 sql ------------------------------------------------------------