首页 > 数据库 > SQL Server >

PLSQL练习

2011-09-21

declare tabName varchar(128); a number;begin select table_name into tabName from user_tables where rownum = 1; dbms_output.put_line(tabName); if ('aa' = 'Aa') t...

declare
tabName varchar(128);
a number;
begin
select table_name into tabName from user_tables where rownum = 1;
dbms_output.put_line(tabName);
if ('aa' = 'Aa') then
a := 5;
else
a := 6;
end if;
dbms_output.put_line(a);
end;
/*
ICOL$
6
*/

-----------------------------------------------------------------

begin
dbms_output.put_line(0.95F); --float
dbms_output.put_line(0.95D); --double
dbms_output.put_line(0.95);
dbms_output.put_line(trunc(25.176, 1));
dbms_output.put_line(trunc(25.176, -1));
dbms_output.put_line(round(25.176, 1));
dbms_output.put_line(round(25.176, -1));
dbms_output.put_line(ceil(25.176));
dbms_output.put_line(floor(25.176));
end;
/*
9.49999988E-001
9.4999999999999996E-001
.95
25.1
20
25.2
30
26
25
*/

-------------------------------------------------------------------

declare
-- tabName varchar(128); -- no need
num number;
begin
for tabName in (select table_name from user_tables) loop
dbms_output.put_line(tabName.table_name);
end loop;
for num in 1 .. 5 loop
dbms_output.put_line(num);
end loop;
end;

----------------------------------------------------------------------

select ascii(' '), initcap('xu weigui'), ltrim('aaaadfdfd', 'a') from dual;

select interval '40' year from dual;

select interval '40-3' year to month from dual;
----------------------------------------------------------------------

declare
a interval year to month;
begin
a := numtoyminterval(10.5, 'year');
dbms_output.put_line(a);
end;

/*
+10-06
*/
----------------------------------------------------
declare
type mytype is table of varchar2(100) index by binary_integer;
mytable mytype;

begin
mytable(1) := 'xuweigui';
mytable(123) := 'Andy';
dbms_output.put_line(mytable(1));
dbms_output.put_line(mytable(123));
end;

--------------------------------------------------------------
declare
type mytype is table of varchar2(100);
mytable mytype := mytype('a');
mytable2 mytype :=mytype(5,4,3,2,1); --Until you initialize it, a nested table or varray is atomically null;

begin
if mytable is null then
null;
elsif mytable.count =0 then
null;
end if;

dbms_output.put_line('mytable init');
for i in mytable.first .. mytable.last loop
dbms_output.put_line(mytable(i));
end loop;

mytable.extend(5);
dbms_output.put_line('mytable.extend(5)');
for i in mytable.first .. mytable.last loop
dbms_output.put_line(mytable(i));
end loop;

mytable(1) := 'xuweigui';
mytable(6) := 'Andy';
dbms_output.put_line('mytable assign value');
for i in mytable.first .. mytable.last loop
dbms_output.put_line(mytable(i));
end loop;

dbms_output.put_line('mytable2 init');
for i in mytable2.first .. mytable2.last loop
dbms_output.put_line(mytable2(i));
end loop;

mytable2.extend(20);
dbms_output.put_line('mytable2 extend(20)');
for i in mytable2.first .. mytable2.last loop
dbms_output.put_line(mytable2(i));
end loop;

exception
when collection_is_null then
dbms_output.put_line('exception');
end;


-------------------------------------------------------------------
DECLARE
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list;
lname employees.last_name%TYPE;
fname employees.first_name%TYPE;
BEGIN
staff := staff_list(100, 114, 115, 120, 122);
FOR i IN staff.FIRST..staff.LAST LOOP
SELECT last_name, first_name INTO lname, fname FROM employees
WHERE employees.employee_id = staff(i);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );
END LOOP;
END;
/
--------------------------------------------------------------
declare
rCnt integer;
begin
select count(*) into rCnt from employees;
dbms_output.put_line(sql%rowcount);
end;

------------------------------------------

declare
--type curType is ref cursor;
cursor mycursor is
select * from employees;
begin
--open mycursor for select * from employees;
for empRec in mycursor loop
dbms_output.put_line(empRec.first_name || ' ' || empRec.last_name);
end loop;
dbms_output.put_line('-----------------------------------------------');
for empRec in (select * from employees where employee_id < 120) loop
dbms_output.put_line(empRec.first_name || &#39; &#39; || empRec.last_name);
end loop;
end;
---------------------------------------------
select count(case
when salary < 2000 then
1
else
null
end) count1,
count(case
when salary between 2001 and 4000 then
1
else
null
end) count2,
count(case
when salary > 4000 then
1
else
null
end) count3
from employees;
-----------------------------------------------------------

begin
create table test(id integer); --error
end;
/

begin execute immediate &#39;create table test (id integer)&#39;; --dynamic sql
end;
/

--------------------------------------------------------------------
--pass parameter to dynamic sql
declare
str varchar2(1000);
fname varchar2(100);
lname varchar2(100);
begin
str := &#39;select first_name, last_name from employees where employee_id = :emp_id&#39;;
execute immediate str
into fname, lname
using 201;
dbms_output.put_line(fname || &#39; &#39; || lname);
end;
/
-------------------------------------------------------------
select to_number(&#39;F123.456,78&#39;,
&#39;L999G999D99&#39;,
&#39;NLS_NUMERIC_CHARACTERS=&#39;&#39;,.&#39;&#39; &#39; || &#39; NLS_CURRENCY=&#39;&#39;f&#39;&#39; &#39; ||
&#39;NLS_ISO_CURRENCY=FRANCE&#39;)
FROM DUAL;
--123456.78
SELECT TO_CHAR(123456.78,
&#39;L999G999D99&#39;,
&#39;NLS_NUMERIC_CHARACTERS=&#39;&#39;,.&#39;&#39; &#39; || &#39; NLS_CURRENCY=&#39;&#39;f&#39;&#39; &#39; ||
&#39;NLS_ISO_CURRENCY=FRANCE&#39;)
FROM DUAL;
-- f123.456,78

-------------------------------------------------------------------------------
SELECT last_name,
employee_id,
manager_id,
LEVEL,
SYS_CONNECT_BY_PATH(last_name, &#39;/&#39;) "Path"
FROM employees
--START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
--------------------------------------------------------------------
declare
my_exception exception;
pragma exception_init(my_exception, -259);--should between -20999, -20000
begin
begin
raise MY_EXCEPTION;
EXception
when my_exception then
dbms_output.put_line(&#39;first exception&#39;);
dbms_output.put_line(sqlcode);-- -259
raise;
end;
EXception
when my_exception then
dbms_output.put_line(&#39;second exception&#39;);
end;
---------------------------------------------------
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
due_date DATE := SYSDATE - 1;
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date
THEN
RAISE past_due;
END IF;
END;
EXCEPTION
WHEN past_due
THEN
DBMS_OUTPUT.put_line(&#39;Handling PAST_DUE exception.&#39;);
WHEN OTHERS
THEN
--go this path
DBMS_OUTPUT.put_line(&#39;Could not recognize PAST_DUE_EXCEPTION in this scope.&#39;);
END;


DECLARE
past_due EXCEPTION;
pragma exception_init(past_due, -259);
acct_num NUMBER;
BEGIN
DECLARE
past_due EXCEPTION;
pragma exception_init(past_due, -259);
acct_num NUMBER;
due_date DATE := SYSDATE - 1;
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date
THEN
RAISE past_due;
END IF;
END;
EXCEPTION
WHEN past_due
THEN
--go this path
DBMS_OUTPUT.put_line(&#39;Handling PAST_DUE exception.&#39;);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(&#39;Could not recognize PAST_DUE_EXCEPTION in this scope.&#39;);
END;
----------------------------------------------------------------
begin
raise_application_error(-20111, &#39;error message&#39;);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
----------------------------------------------------------------
create or replace package my_package is
num number := 5;
one number :=1;
function f return number;
procedure print;
function add (first number, second number default 1) return number; --second has a default value of 1
end my_package;

create or replace package body my_package is
i number := 3;
function f return number is
begin
return num;
end f;
procedure print is
begin
dbms_output.put_line(f());
end print;
function add (first number, second number) return number is
begin
return first + second;
end add;
end my_package;

begin
dbms_output.put_line(my_package.add(5));
dbms_output.put_line(my_package.add(5, 5));
dbms_output.put_line(my_package.add(second => 5, first=>12));
my_package.print();
dbms_output.put_line(my_package.num);
end;
----------------------------------------------------------------------
create table emp as select * from employees;

create table tab (id number, oldsal number);

create or replace trigger tri
before update on emp
for each row
declare
progma autonomous_transaction;
begin
insert into tab values (:old.employee_id, :old.salary);
commit;
end tri;


update emp set salary = salary;
rollback;--although rollback here, data is inserted into tab in trigger

select * from tab;
---------------------------------

begin
for i in 1..5 loop
dbms_output.put_line(i);
if i=3 then
exit;--exit the loop
end if;
end loop;
dbms_output.put_line(&#39;exit loop&#39;);
end;
------------------------------

begin
for j in 1..2 loop
for i in 1..5 loop
dbms_output.put_line(&#39;innner loop &#39; || i);
if i=3 then
exit;--exit the inner loop
end if;
end loop;
dbms_output.put_line(&#39;outer loop &#39; || j);
end loop;
dbms_output.put_line(&#39;end&#39;);
end;

--------------------------------------
declare
type myrecord_type is record(
first_name employees.first_name%type,
last_name employees.last_name%type);
TYPE mycur_type IS REF CURSOR RETURN myrecord_type;
cur mycur_type;
person myrecord_type;
begin
if 1 = 2 then
open cur for
select first_name, last_name from employees where employee_id = 198;
else
open cur for
select first_name, last_name from employees where employee_id = 199;
end if;
loop
fetch cur
into person;
exit when cur%NOTFOUND;
dbms_output.put_line(person.first_name || &#39; &#39; || person.last_name);
end loop;
close cur;
end;


---------------------------------
alter session set plsql_ccflags = &#39;to_debug:true&#39;

declare
$if $$to_debug $then
cursor cur is select first_name, last_name from employees where employee_id = 198;
$else
cursor cur is select first_name, last_name from employees where employee_id = 199;
$end
begin
for person in cur loop
dbms_output.put_line(person.first_name || &#39; &#39; || person.last_name);
end loop;
end;
/*
Example Using ALTER PROCEDURE to Set PLSQL_CCFLAGS
ALTER PROCEDURE circle_area COMPILE PLSQL_CCFLAGS = &#39;my_debug:TRUE&#39;
REUSE SETTINGS;
*/

-----------------------------------------
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- sum a bunch of products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE(&#39;The sum of products equals: &#39; || TO_CHAR(s));
END;
------------------------------------------------------
--cursor with parameter
DECLARE
CURSOR c1(job VARCHAR2, max_wage NUMBER) IS
SELECT *
FROM employees
WHERE job_id = job
AND salary > max_wage;
BEGIN
FOR person IN c1(&#39;CLERK&#39;, 3000) LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE(&#39;Name = &#39; || person.last_name || &#39;, salary = &#39; ||
person.salary || &#39;, Job Id = &#39; || person.job_id);
END LOOP;
END;

作者“红豆加奶”

相关文章
最新文章
热点推荐