A year is considered a leap year if the year is divisible by 4 but not divisible by 100 unless also divisible by 400.
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-' to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
--Or we can use Mod(year,4)
/*
V_REMAINDER1 := MOD(nYear,4);
V_REMAINDER2 := MOD(nYear,100);
V_REMAINDER3 := MOD(nYear,400);
IF ((V_REMAINDER1 = 0 AND V_REMAINDER2 <> 0 ) OR V_REMAINDER3 = 0) THEN
DBMS_OUTPUT.PUT_LINE(nYear ' is a leap year');
return true;
ELSE
DBMS_OUTPUT.PUT_LINE (nYear ' is not a leap year');
return false; */
Cheat sheet
http://www.yagc.ndo.co.uk/cheatsheets/plsql_cheatsheet.html
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
Friday, February 29, 2008
Wednesday, February 27, 2008
Oracle - PL/SQL samples
Variables and Constants
Declaring Variables
part_no NUMBER(4);
in_stock BOOLEAN;
Assigning Values to a Variable
tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id;
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal); -- assigns a new value to my_sal
Assigning a SQL Query Result to a PL/SQL Variable
DECLARE
emp_id employees.employee_id%TYPE := 100;
emp_name employees.last_name%TYPE;
wages NUMBER(7,2);
BEGIN
SELECT last_name, salary + (salary * nvl(commission_pct,0))
INTO emp_name, wages FROM employees
WHERE employee_id = emp_id;
dbms_output.put_line('Employee ' emp_name ' might make ' wages);
END;
/
Declaring Constants
credit_limit CONSTANT NUMBER := 5000.00;
Processing Queries with PL/SQL
FOR someone IN (SELECT * FROM employees)
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' someone.first_name);
DBMS_OUTPUT.PUT_LINE('Last name = ' someone.last_name);
END LOOP;
Declaring PL/SQL Variables
%TYPE attribute
my_title books.title%TYPE;
%ROWTYPE
DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
--You use dot notation to reference fields, as the following example shows:
my_deptno := dept_rec.deptno;
--declare a cursor that retrieves the last name, salary, hire date…
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE; -- declare record variable that represents
-- a row fetched from the emp table When you execute the statement
FETCH c1 INTO emp_rec;
Control Structures
Conditional Control
DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds');
END IF;
COMMIT;
END;
-- This CASE statement performs different actions based
-- on a set of conditional tests.
CASE
WHEN shape = 'square' THEN area := side * side;
WHEN shape = 'circle' THEN
BEGIN
area := pi * (radius * radius);
DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
END;
WHEN shape = 'rectangle' THEN area := length * width;
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' shape);
RAISE PROGRAM_ERROR;
END;
END CASE;
Iterative Control
LOOP
-- sequence of statements
END LOOP;
FOR num IN 1..500 LOOP
INSERT INTO roots VALUES (num, SQRT(num));
END LOOP;
DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, 'Not found'); COMMIT; END; --Loop LOOP ... total := total + salary; EXIT WHEN total > 25000; -- exit loop if condition is true
END LOOP;
-- control resumes here
Sequential Control
--The GOTO statement lets you branch to a label unconditionally.
IF rating > 90 THEN
GOTO calc_raise; -- branch to label
END IF;
...
<>
IF job_title = 'SALESMAN' THEN -- control resumes here
amount := commission * 0.25;
ELSE
amount := salary * 0.10;
END IF;
Writing Reusable PL/SQL Code
Subprograms
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
...
END award_bonus;
Packages
CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Data Abstraction
Collections
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays.
DECLARE
TYPE Staff IS TABLE OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN ... END;
BEGIN
staffer := new_hires('10-NOV-98')(5);
END;
/*Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL. */
Records
DECLARE
TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
date_held DATE,
duration TimeRec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));
Object Types
PL/SQL supports object-oriented programming through object types. An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that manipulate the attributes are known as methods.
Object types reduce complexity by breaking down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable.
Object-type definitions, and the code for the methods, are stored in the database. Instances of these object types can be stored in tables or used as variables inside PL/SQL code.
CREATE TYPE Bank_Account AS OBJECT (
acct_number INTEGER(5),
balance REAL,
status VARCHAR2(10),
MEMBER PROCEDURE open (amount IN REAL),
MEMBER PROCEDURE verify_acct (num IN INTEGER),
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL
);
Error Handling
/*PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.
Predefined exceptions are raised automatically for certain common error conditions involving variables or database operations. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically.
You can declare exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA- error messages. When you detect a user-defined error condition, you execute a RAISE statement. The following example computes the bonus earned by a salesperson. The bonus is based on salary and commission. If the commission is null, you raise the exception comm_missing. */
DECLARE
comm_missing EXCEPTION; -- declare exception
BEGIN
IF commission IS NULL THEN
RAISE comm_missing; -- raise exception
END IF;
bonus := (salary * 0.10) + (commission * 0.15);
EXCEPTION
WHEN comm_missing THEN ... -- process the exception
Declaring Variables
part_no NUMBER(4);
in_stock BOOLEAN;
Assigning Values to a Variable
tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id;
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal); -- assigns a new value to my_sal
Assigning a SQL Query Result to a PL/SQL Variable
DECLARE
emp_id employees.employee_id%TYPE := 100;
emp_name employees.last_name%TYPE;
wages NUMBER(7,2);
BEGIN
SELECT last_name, salary + (salary * nvl(commission_pct,0))
INTO emp_name, wages FROM employees
WHERE employee_id = emp_id;
dbms_output.put_line('Employee ' emp_name ' might make ' wages);
END;
/
Declaring Constants
credit_limit CONSTANT NUMBER := 5000.00;
Processing Queries with PL/SQL
FOR someone IN (SELECT * FROM employees)
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' someone.first_name);
DBMS_OUTPUT.PUT_LINE('Last name = ' someone.last_name);
END LOOP;
Declaring PL/SQL Variables
%TYPE attribute
my_title books.title%TYPE;
%ROWTYPE
DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
--You use dot notation to reference fields, as the following example shows:
my_deptno := dept_rec.deptno;
--declare a cursor that retrieves the last name, salary, hire date…
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE; -- declare record variable that represents
-- a row fetched from the emp table When you execute the statement
FETCH c1 INTO emp_rec;
Control Structures
Conditional Control
DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds');
END IF;
COMMIT;
END;
-- This CASE statement performs different actions based
-- on a set of conditional tests.
CASE
WHEN shape = 'square' THEN area := side * side;
WHEN shape = 'circle' THEN
BEGIN
area := pi * (radius * radius);
DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
END;
WHEN shape = 'rectangle' THEN area := length * width;
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' shape);
RAISE PROGRAM_ERROR;
END;
END CASE;
Iterative Control
LOOP
-- sequence of statements
END LOOP;
FOR num IN 1..500 LOOP
INSERT INTO roots VALUES (num, SQRT(num));
END LOOP;
DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, 'Not found'); COMMIT; END; --Loop LOOP ... total := total + salary; EXIT WHEN total > 25000; -- exit loop if condition is true
END LOOP;
-- control resumes here
Sequential Control
--The GOTO statement lets you branch to a label unconditionally.
IF rating > 90 THEN
GOTO calc_raise; -- branch to label
END IF;
...
<
IF job_title = 'SALESMAN' THEN -- control resumes here
amount := commission * 0.25;
ELSE
amount := salary * 0.10;
END IF;
Writing Reusable PL/SQL Code
Subprograms
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
...
END award_bonus;
Packages
CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Data Abstraction
Collections
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays.
DECLARE
TYPE Staff IS TABLE OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN ... END;
BEGIN
staffer := new_hires('10-NOV-98')(5);
END;
/*Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL. */
Records
DECLARE
TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
date_held DATE,
duration TimeRec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));
Object Types
PL/SQL supports object-oriented programming through object types. An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that manipulate the attributes are known as methods.
Object types reduce complexity by breaking down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable.
Object-type definitions, and the code for the methods, are stored in the database. Instances of these object types can be stored in tables or used as variables inside PL/SQL code.
CREATE TYPE Bank_Account AS OBJECT (
acct_number INTEGER(5),
balance REAL,
status VARCHAR2(10),
MEMBER PROCEDURE open (amount IN REAL),
MEMBER PROCEDURE verify_acct (num IN INTEGER),
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL
);
Error Handling
/*PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.
Predefined exceptions are raised automatically for certain common error conditions involving variables or database operations. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically.
You can declare exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA- error messages. When you detect a user-defined error condition, you execute a RAISE statement. The following example computes the bonus earned by a salesperson. The bonus is based on salary and commission. If the commission is null, you raise the exception comm_missing. */
DECLARE
comm_missing EXCEPTION; -- declare exception
BEGIN
IF commission IS NULL THEN
RAISE comm_missing; -- raise exception
END IF;
bonus := (salary * 0.10) + (commission * 0.15);
EXCEPTION
WHEN comm_missing THEN ... -- process the exception
Wednesday, December 26, 2007
Oracle - Useful functions
/Create Table using As
CREATE TABLE New_Table_Name AS SELECT * FROM Table_Name WHERE Column1 >100;
SQL> SELECT 5/2 FROM DUAL
2 /
5/2
---------
2.5
SQL> SELECT -5/2 FROM DUAL
2 /
-5/2
---------
-2.5
SQL> SELECT ABS(-5/2) FROM DUAL
2 /
ABS(-5/2)
---------
2.5
SQL> SELECT ROUND(5/2) FROM DUAL;
ROUND(5/2)
----------
3
SQL> SELECT ROUND(-5/2) FROM DUAL;
ROUND(-5/2)
-----------
-3
SQL> SELECT CEIL(5/2) FROM DUAL;
CEIL(5/2)
---------
3
SQL> SELECT FLOOR(5/2) FROM DUAL;
FLOOR(5/2)
----------
2
SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;
TO_CHAR(S
---------
18-DEC-07
SQL> SELECT TO_DATE('18-Dec-07') FROM DUAL;
TO_DATE('
---------
18-DEC-07
SQL> SELECT TO_NUMBER('143') FROM DUAL;
TO_NUMBER('143')
----------------
143
SQL> SELECT UPPER('bALA') FROM DUAL;
UPPER('BALA')
--------------------------------
BALA
SQL> SELECT LOWER('sANdyA') FROM DUAL;
LOWER('SANDYA')
--------------------------------
sandya
SQL> SELECT SUBSTR('Bala krishnan',6,5) FROM DUAL;
SUBSTR('BALAKRISHNAN',6,5)
--------------------------------
krish
SQL> SELECT SUBSTR('Bala Krishnan',6) FROM DUAL;
SUBSTR('BALAKRISHNAN',6)
--------------------------------
Krishnan
SQL> SELECT NVL(NULL,'0') FROM DUAL;
NVL(NULL,'0')
--------------------------------
0
SQL> SELECT DECODE(NULL,NULL,0,1) FROM DUAL;
DECODE(NULL,NULL,0,1)
---------------------
0
SQL> SELECT LPAD(5,10,'*') FROM DUAL;
LPAD(5,10,'*')
-----------------
*********5
SQL> SELECT RPAD(6,10,'*') FROM DUAL;
RPAD(6,10,'*')
-----------------
6*********
SQL> SELECT LEAST(1,5,2,7) , GREATEST(1,5,2,7) FROM DUAL;
O/P
----
1 7
CREATE TABLE New_Table_Name AS SELECT * FROM Table_Name WHERE Column1 >100;
SQL> SELECT 5/2 FROM DUAL
2 /
5/2
---------
2.5
SQL> SELECT -5/2 FROM DUAL
2 /
-5/2
---------
-2.5
SQL> SELECT ABS(-5/2) FROM DUAL
2 /
ABS(-5/2)
---------
2.5
SQL> SELECT ROUND(5/2) FROM DUAL;
ROUND(5/2)
----------
3
SQL> SELECT ROUND(-5/2) FROM DUAL;
ROUND(-5/2)
-----------
-3
SQL> SELECT CEIL(5/2) FROM DUAL;
CEIL(5/2)
---------
3
SQL> SELECT FLOOR(5/2) FROM DUAL;
FLOOR(5/2)
----------
2
SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;
TO_CHAR(S
---------
18-DEC-07
SQL> SELECT TO_DATE('18-Dec-07') FROM DUAL;
TO_DATE('
---------
18-DEC-07
SQL> SELECT TO_NUMBER('143') FROM DUAL;
TO_NUMBER('143')
----------------
143
SQL> SELECT UPPER('bALA') FROM DUAL;
UPPER('BALA')
--------------------------------
BALA
SQL> SELECT LOWER('sANdyA') FROM DUAL;
LOWER('SANDYA')
--------------------------------
sandya
SQL> SELECT SUBSTR('Bala krishnan',6,5) FROM DUAL;
SUBSTR('BALAKRISHNAN',6,5)
--------------------------------
krish
SQL> SELECT SUBSTR('Bala Krishnan',6) FROM DUAL;
SUBSTR('BALAKRISHNAN',6)
--------------------------------
Krishnan
SQL> SELECT NVL(NULL,'0') FROM DUAL;
NVL(NULL,'0')
--------------------------------
0
SQL> SELECT DECODE(NULL,NULL,0,1) FROM DUAL;
DECODE(NULL,NULL,0,1)
---------------------
0
SQL> SELECT LPAD(5,10,'*') FROM DUAL;
LPAD(5,10,'*')
-----------------
*********5
SQL> SELECT RPAD(6,10,'*') FROM DUAL;
RPAD(6,10,'*')
-----------------
6*********
SQL> SELECT LEAST(1,5,2,7) , GREATEST(1,5,2,7) FROM DUAL;
O/P
----
1 7
Saturday, December 22, 2007
Oracle some tricks
// Other than SQL statements are not allowed in Oracle procedures. Only trick is Execute immediate
Execute immediate “DDL Commnds such as truncae table and crete table”
//ISQLW editor – to stop the execution of the query which takes long time.
ALT + F, C
//Debug tips
SET SERVER OUTPUT ON SIZE 100000;
DBMS_OUTPUT.Put_line('This is Bala');
SET SERVER OUTPUT OFF;
//GUI Tool for Oracle
TOAD and its free
//To Clear screen
CL SCR
//concatenation operator
SELECT 'Bala ' ' Loves ' 'Sandya' FROM DUAL
//To go to command prompt from a set of statements
/ or .
//To run the last statement or Execute the statements
RUN
//DECODE - If matches then this else that...
SQL>SELECT DECODE('&INP','r','Red color','g','Green color','b','Blue color','Default color') FROM DUAL
Enter value for inp: g
DECODE('G','R','REDCOLOR','G'
--------------------------------
Green color
Enter value for inp: b
DECODE('B','R','REDCOLOR','G'
--------------------------------
Blue color
Enter value for inp: sd
DECODE('SD','R','REDCOLOR','G
--------------------------------
Default color
//NVL - If Null Value Then
SQL> SELECT NVL('&inp','Null text') FROM DUAL;
Enter value for inp:
NVL('','NULLTEXT')
--------------------------------
Null text
Enter value for inp: Bala
NVL('BALA','NULLTEXT')
--------------------------------
Bala
//Spool
SQL> SPOOL C:\BALA.SQLSQL> SELECT 10 FROM DUAL;
10--------- 10
SQL> SPOOL OFF
//NLS - Configure for National Language Support
http://www.exzilla.net/docs/nls/oracleNLS01.php
http://download.oracle.com/docs/cd/B19188_01/doc/B15921/nls.htm
http://download-uk.oracle.com/docs/cd/B14117_01/olap.101/b10339/x_monitor018.htm
Execute immediate “DDL Commnds such as truncae table and crete table”
//ISQLW editor – to stop the execution of the query which takes long time.
ALT + F, C
//Debug tips
SET SERVER OUTPUT ON SIZE 100000;
DBMS_OUTPUT.Put_line('This is Bala');
SET SERVER OUTPUT OFF;
//GUI Tool for Oracle
TOAD and its free
//To Clear screen
CL SCR
//concatenation operator
SELECT 'Bala ' ' Loves ' 'Sandya' FROM DUAL
//To go to command prompt from a set of statements
/ or .
//To run the last statement or Execute the statements
RUN
//DECODE - If matches then this else that...
SQL>SELECT DECODE('&INP','r','Red color','g','Green color','b','Blue color','Default color') FROM DUAL
Enter value for inp: g
DECODE('G','R','REDCOLOR','G'
--------------------------------
Green color
Enter value for inp: b
DECODE('B','R','REDCOLOR','G'
--------------------------------
Blue color
Enter value for inp: sd
DECODE('SD','R','REDCOLOR','G
--------------------------------
Default color
//NVL - If Null Value Then
SQL> SELECT NVL('&inp','Null text') FROM DUAL;
Enter value for inp:
NVL('','NULLTEXT')
--------------------------------
Null text
Enter value for inp: Bala
NVL('BALA','NULLTEXT')
--------------------------------
Bala
//Cursor
DECLARE CURSOR CURPF IS
SELECT * FROM Table1
VarCPF CURPF%ROWTYPE;
BEGIN
FOR VarCPF IN CURPF LOOP
UPDATE Table2 SET Col1 = VarCPF.col1
WHERE Col2 = VarCPF.col2;
END LOOP;
END;
TEST USER
=========
SCOTT
TIGER
DBTEST
LOGIN AS SYSADMIN
=================
sys
*****
DBASE AS SYSDBA
Create and Grant permission
======================
SQL> CREATE USER baladba IDENTIFIED BY ****;
User created.
SQL> GRANT RESOURCE TO baladba;
Grant succeeded.
SQL> GRANT DBA TO baladba;
Grant succeeded.
SQL> GRANT CONNECT TO baladba;
Grant succeeded.
SQL> SELECT * FROM TAB;
//EXECUTE IMMEDIATE
SSQL := ' SELECT SUM('C2.CLASS ;
ssql := ssql ') into :p1 from EMP_TAX where payyear = :p2 and emp_no = :p3 ';
EXECUTE IMMEDIATE SSQL into p1 USING '2007','1117';
//Identify min value from different arrays
SELECT MIN(HRA) INTO EXHRA FROM
(SELECT HRA_ITD HRA FROM DUAL
UNION ALL
SELECT HRA_ACT HRA FROM DUAL
UNION ALL
SELECT COREPAY2 HRA FROM DUAL);
//Spool
SQL> SPOOL C:\BALA.SQLSQL> SELECT 10 FROM DUAL;
10--------- 10
SQL> SPOOL OFF
//NLS - Configure for National Language Support
http://www.exzilla.net/docs/nls/oracleNLS01.php
http://download.oracle.com/docs/cd/B19188_01/doc/B15921/nls.htm
http://download-uk.oracle.com/docs/cd/B14117_01/olap.101/b10339/x_monitor018.htm
Subscribe to:
Posts (Atom)