Herhangi bir tarih için haftanın hangi günü olduğunu bulan S.P.
Kod: Tümünü seç
CREATE PROCEDURE DOW (THEDATE DATE)
RETURNS (DOW INTEGER )
AS
/* WHAT : */
/* Returns the week day of THEDATE.*/
/* 1 = Monday, ... , 7 = Sunday*/
DECLARE VARIABLE REF_DATE DATE;
DECLARE VARIABLE DATE_COPY DATE;
DECLARE VARIABLE DIFF1 INTEGER;
DECLARE VARIABLE DIFF2 INTEGER;
DECLARE VARIABLE DAYSDIFF INTEGER;
BEGIN
/*Initialize*/
REF_DATE = "15.DEC.96"; /*A Sunday*/
DATE_COPY = :THEDATE;
DIFF1 = 1;
DIFF2 = 2;
/*Calc*/
WHILE (:DIFF1 <> :DIFF2) DO
BEGIN
DIFF1 = THEDATE - REF_DATE;
DAYSDIFF = :DIFF1 / 7;
DIFF2 = :DAYSDIFF * 7;
IF (:DIFF1 <> :DIFF2) THEN
THEDATE = :THEDATE + 1;
END
DOW = 7-(:THEDATE-:DATE_COPY);
SUSPEND;
ENDInvoice Number Creator - 1998/000001 şeklinde Fatura numarası oluşturan S.P.
The procedure in this file makes an invoice number from the data defined in the parameters. The invoice number contains prefix, and number part with leading characters.
----------------
This script file contains the following procedures:
FillChar - Returns a string filled with a given character.
InvNum - Returns an Invoice number as described below.
----------------
About Invoice numbers
---------------
Sometimes invoice numbers must be handled as strings but the string order differs from the integer order.
The procedure in this file makes an invoice number from the data defined in the parameters. The invoice number contains prefix, and number part with leading characters as described below.
Prefix - Contains the fix length prefix that is used at the beginning of every invoice number.
Number - The number to be generated.
MaxLen - The maximum length of the number excluded prefix.
LeadingChar - The number that appears before the number.
Inv_Num - The returning value.
example:
Prefix = "1998/" "1998/"
Number = 1 333
MaxLen = 6 6
LeadingChar = "0" "0"
Inv_Num = "1998/000001" "1998/000333"
---------------------------
Kod: Tümünü seç
*/
SET TERM # ;
CREATE PROCEDURE FillChar(Ch CHAR, Len INTEGER)
RETURNS (Str VARCHAR(255))
AS
DECLARE VARIABLE I INTEGER;
BEGIN
Str = "";
I = 0;
WHILE(I < Len) DO
BEGIN
Str = Str || Ch;
I = I +1;
END
END
#Kod: Tümünü seç
CREATE PROCEDURE Invoice_Number(
APrefix VARCHAR(20),
ALeadingChar CHAR,
AMaxLen INTEGER,
ANumber INTEGER)
RETURNS(Inv_Num VARCHAR(100))
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE Dummy VARCHAR(100);
DECLARE VARIABLE WorkInvNum CHAR(100);
BEGIN
/* Set the length of the leading characters */
I = 100-AMaxLen;
/* Create the string containing the leading characters with the specified size */
EXECUTE PROCEDURE FillChar("0", I) RETURNING_VALUES Dummy;
/* Make the invoice number without leading characters */
Inv_Num = ANumber;
/* Create the longest possible invoice number */
I = 1;
WHILE(I < 100) DO
BEGIN
/* Try to create an invoice number with this length *)
WorkInvNum = CAST(Dummy || Inv_Num AS CHAR(99));
/* Add a new leading character at the beginning of the invoice number */
Inv_Num = ALeadingChar || Inv_Num;
I = I +1;
/* In case of error the length is exceeded so the last invoice number was the right one */
WHEN ANY DO
BEGIN
Inv_Num = APrefix || Inv_Num;
EXIT;
END
END
END
#
SET TERM ; #Not: Küçük tablolarda daha iyi çalıştığı belirtiliyor.
This is an example of how you could provide FIRST N or LAST N record functionality using a stored proc. It's not the most efficient method -- so best for smaller tables -- but it works.
Kod: Tümünü seç
CREATE PROCEDURE GET_LAST (iHOW_MANY INTEGER)
RETURNS (vUSER_ID VARCHAR(3))
AS
DECLARE VARIABLE iREC_COUNT INTEGER;
DECLARE VARIABLE iCOUNT INTEGER;
DECLARE VARIABLE iSTART_FROM INTEGER;
BEGIN
/* How many records are there... */
SELECT COUNT(*)
FROM users
INTO :iREC_COUNT;
/* At what record "number" do we start returning values... */
IF (iHOW_MANY >= iREC_COUNT) THEN
iSTART_FROM = 1;
ELSE
iSTART_FROM = iREC_COUNT - iHOW_MANY + 1;
/* Initialize the "counter" */
iCOUNT = 0;
/* Do the query */
FOR SELECT USER_ID
FROM users
ORDER BY USER_ID
INTO :vUSER_ID
DO
BEGIN
iCOUNT = iCOUNT + 1;
IF (iCOUNT >= iSTART_FROM) THEN
BEGIN
SUSPEND;
END
END
ENDİlk N sayıdaki kayıdı getiren S.P.
Retrieving the first n records from a result set
There needs to be a way to retrieve a fixed number of records from a result set using SQL.
Solution:
One way to do this is to create a stored procedure that performs the query and keeps track of how many records it has fetched. After the limit has been reached, the procedure should end.
Here's a procedure that returns the employee number, first name, and last name from the records in the employee table. The parameter rows specifies how many records the procedure returns, starting with those who have last names at the beginning of the alphabet, or the whole result set, whichever is smaller.
Kod: Tümünü seç
create procedure getemp ( rows integer )
returns (emp_no smallint, firstname varchar(15), lastname varchar(20))
as
begin
if (rows < 1) then
exit;
for select emp_no, first_name, last_name from employee
order by Last_name
into :emp_no, :firstname, :lastname
do
begin
suspend;
rows = rows - 1;
if (rows < 1) then
exit;
end
endOğuz ÖZTÜRK
