OOP
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 || ' ' || 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 'create table test (id integer)'; --dynamic sql
end;
/
--------------------------------------------------------------------
--pass parameter to dynamic sql
declare
str varchar2(1000);
fname varchar2(100);
lname varchar2(100);
begin
str := 'select first_name, last_name from employees where employee_id = :emp_id';
execute immediate str
into fname, lname
using 201;
dbms_output.put_line(fname || ' ' || lname);
end;
/
-------------------------------------------------------------
select to_number('F123.456,78',
'L999G999D99',
'NLS_NUMERIC_CHARACTERS='',.'' ' || ' NLS_CURRENCY=''f'' ' ||
'NLS_ISO_CURRENCY=FRANCE')
FROM DUAL;
--123456.78
SELECT TO_CHAR(123456.78,
'L999G999D99',
'NLS_NUMERIC_CHARACTERS='',.'' ' || ' NLS_CURRENCY=''f'' ' ||
'NLS_ISO_CURRENCY=FRANCE')
FROM DUAL;
-- f123.456,78
-------------------------------------------------------------------------------
SELECT last_name,
employee_id,
manager_id,
LEVEL,
SYS_CONNECT_BY_PATH(last_name, '/') "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('first exception');
dbms_output.put_line(sqlcode);-- -259
raise;
end;
EXception
when my_exception then
dbms_output.put_line('second exception');
end;
---------------------------------------------------
DECLARE
past_due EXCEPTION;
acc