SQL基础案例

--1.将2010-12-06转换成Dec-06-2010的格式.

select to_char(to_date('2010-12-06','yyyy-mm-dd'),'MON-dd-yyyy','nls_date_language=American') from dual;

--2.PL/SQL中常用round函数,trunc函数,instr函数,substr函数的区别.
Select round(5.335,2),trunc(5.335,2),instr('abcdbcd','bc',1,2),substr('abcddc',3,2) from dual;

--4.如何删除重复的记录.

--5.返回今天星期几(中文和英文分别显示)
--''simplified chinese'',加单引号，是因为两个单词中间有空格，每个单引号前面的单引号'是转义符。
select to_char(sysdate,'yyyy-mm-dd day hh12 mi ss','nls_date_language=''simplified chinese''') from dual;
select to_char(sysdate,'yyyy-mm-dd day hh12 mi ss','nls_date_language=American') from dual;

--6.返回某月最后一天
select last_day(sysdate) from dual; --本月最后一天
select last_day(add_months(to_date(sysdate),-1)) from dual; --上月最后一天
select last_day(add_months(to_date(sysdate),1)) from dual; --下月最后一天

--7.给当前日期加上两年；
select add_months(sysdate,24) from dual;

--8.使用函数实现，输入5.5,分别得到6和5;
--第二个参数表示，保留小数点后几位。

select round(5.5,0),trunc(5.5,0) from dual;

---

PL/SQL案例

-- 创建一个空表，创建过程，向表中添加记录。
create table pro1test (name varchar2(20),age number(2));

create or replace procedure pro1 is
begin
insert into pro1test values('张三',20);
end;

--创建过程，从表中删除记录。
create or replace procedure pro2 is
begin
delete from pro1test where name='张三';
end;

--块的基本结构：定义部分、执行部分、例外处理部分

--只包含一个执行过程的块,打印输出：你好，世界！
begin
dbms_output.put_line('你好，世界！');
end;

--包含定义和执行部分的块，根据员工编号，输出工号、姓名和工资。
declare
v_no emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select empno,ename,sal into v_no,v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('工号：'||v_no||' 姓名：'||v_ename||' 工资：'||v_sal);
end;

--包含定义、执行、例外部分的块，根据员工编号，输出工号、姓名和工资，编号输入错误时提示：此员工编号不存在！
declare
v_no emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select empno,ename,sal into v_no,v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('工号：'||v_no||' 姓名：'||v_ename||' 工资：'||v_sal);
exception
when no_data_found then
dbms_output.put_line('此员工编号不存在！');
end;

--案例4：创建一个过程用于修改工资，可输入员工姓名、新工资。
create or replace procedure pro3(name in emp.ename%type,newsal in emp.sal%type) is
begin
update emp set sal=newsal where ename=name;
end;

--案例5：创建函数，输入雇员姓名，返回其年薪。
create or replace function fun1(name in varchar2) return number is yearsal emp.sal%type;
begin
select sal12+nvl(comm,0)12 into yearsal from emp where ename=name;
return yearsal;
end;

--创建一个包，输入雇员姓名，返回其年薪。

--创建包规范pck1
create or replace package pck1 is
procedure update_sal(name varchar2,newsal number);
function yearsal(name varchar2) return number;
end;

--创建包体pck1
create or replace package body pck1 is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function yearsal(name varchar2) return number is
yearsal number(7,2);
begin
select sal12+nvl(comm,0)12 into yearsal from emp where ename=name;
return yearsal;
end;
end;

--调用包pck1
--输入姓名和新工资，修改工资
call pck1.update_sal('SCOTT',100);
--输入姓名，查询年薪
var yearsal number;
call pck1.yearsal('SCOTT') into:yearsal;

--案例：输入员工号，显示姓名，工资，所得税（税率0.03）。
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax number;
c_taxrate number(3,2):=0.03;
begin
select ename,sal,(sal+comm)*c_taxrate into v_ename,v_sal,v_tax from emp where empno=&no;
dbms_output.put_line('姓名'||v_ename||'工资'||v_sal||'所得税'||v_tax);
end;