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
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
Monday, February 11, 2008
Creating another website in IIS
By default windows XP disables multiple websites under root i.e. IIS.
Follow the following steps to create another website in IIS.
Go the AdminScripts folder in the Inetpub Folder. The Inetpub will be created at the time of installing IIS. The path is C: ? Inetpub ? AdminScripts.
open command prompt as said and follow the steps.
Type cd C:\Inetpub\AdminScripts . This command causes the location will moved to AdminScripts folder.
Type adsutil.vbs enum w3svc /p command to get the list of websites in IIS. This will show the Default Web Site i.e. w3svc/1.
Type adsutil.vbs create_vserv W3SVC/2. This will create another website in IIS.
Type adsutil.vbs copy W3SVC/1 W3SVC/2, to copy the contents of default website to the newly created website.
Open the IIS to verify it.
To delete the newly created website use adsutil.vbs delete W3SVC/2 command.
To start and stop the IIS use net start w3svc and to stop IIS use net stop w3svc.
Follow the following steps to create another website in IIS.
Go the AdminScripts folder in the Inetpub Folder. The Inetpub will be created at the time of installing IIS. The path is C: ? Inetpub ? AdminScripts.
open command prompt as said and follow the steps.
Type cd C:\Inetpub\AdminScripts . This command causes the location will moved to AdminScripts folder.
Type adsutil.vbs enum w3svc /p command to get the list of websites in IIS. This will show the Default Web Site i.e. w3svc/1.
Type adsutil.vbs create_vserv W3SVC/2. This will create another website in IIS.
Type adsutil.vbs copy W3SVC/1 W3SVC/2, to copy the contents of default website to the newly created website.
Open the IIS to verify it.
To delete the newly created website use adsutil.vbs delete W3SVC/2 command.
To start and stop the IIS use net start w3svc and to stop IIS use net stop w3svc.
Subscribe to:
Posts (Atom)